Percona XtraBackup工具提供了一種在系統運行時執行MySQL資料熱備份的方法，
Percona XtraBackup在事務系統上執行聯機非阻塞，緊密壓縮，高度安全的完整備份，因此在計劃的維護時段內，應用程式仍保持完全可用，
環境準備：
master:192.168.0.106:3306
slave:192.168.0.105:3306
datadir: /data/mysql/mysql3306/data
cnfpath: /data/mysql/mysql3306/my3306.cnf
XtraBackup 安裝，兩臺機器都安裝：
下載地址：https://www.percona.com/downloads/Percona-XtraBackup-LATEST/
TheMaster$ wget https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0-7/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm
TheMaster$ yum install -y percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm
1、在Master制作一個備份
TheMaster$ mkdir xtrabackup_backupfiles
TheMaster$ xtrabackup --defaults-file=/data/mysql/mysql3306/my3306.cnf -S /tmp/mysql3306.sock -uroot -p'' --backup --target-dir=./xtrabackup_backupfiles
...
completed OK!
為了讓快照是一致的:需要prepare備份資料：
TheMaster$ xtrabackup -S /tmp/mysql3306.sock -uroot -p --prepare --target-dir=./xtrabackup_backupfiles/
...
completed OK!
undo log目錄需要處理下，默認會解壓到xtrabackup_backupfiles目錄下，在my3306.cnf中定義在/data/mysql/mysql3306/data/undolog/下，所以：
TheMaster$ cd xtrabackup_backupfiles && mkdir undolog && mv undo_* undolog/
2、復制備份資料到Slave上
使用rsync或scp將資料從Master復制到Slave，如果直接將資料同步到slave的資料目錄，建議在這之前先停止mysqld，
TheMaster$ rsync -avpP -e ssh ./xtrabackup_backupfiles/ 192.168.0.105:/data/mysql/mysql3306/data/
復制資料前，您可以備份原始或先前安裝的MySQL datadir（注意：在移動mysqld的內容或將快照移動到其datadir之前，請確保mysqld已關閉，）：
TheSlave$ mv /data/mysql/mysql3306/data /data/mysql/mysql3306/data_bak
復制資料后，確保MySQL有權限訪問
TheSlave$ chown -R mysql:mysql /data/mysql/mysql3306/data
3、創建Master上配置復制賬號
TheMaster|mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'repl';
測驗連接
TheSlave$ mysql --host=192.168.0.106 --user=repl --password=repl
mysql> SHOW GRANTS;
4、配置Slave上的MySQL服務器
首先將my3306.cnf從TheMaster復制到TheSlave：
TheSlave$ scp [email protected]:/data/mysql/mysql3306/my3306.cnf /data/mysql/mysql3306/
TheSlave$ chown -R mysql:mysql /data/mysql/mysql3306/data/my3306.cnf
修改TheSlave上的my3306.cnf：
server-id=1053306 # 建議ip最后一位+埠號
啟動TheSlave 上的 mysqld
TheSlave$ /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
觀察error.log是否有錯誤
TheSlave$ more /data/mysql/mysql3306/data/error.log
5、啟動復制
TheSlave$ cat /data/mysql/mysql3306/data/xtrabackup_binlog_info mysql-bin.000012 195 e8f74dde-ed8e-11e9-8ebb-000c29f5c092:1-3
執行CHANGE MASTER陳述句在MySQL命令列：
TheSlave|mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.106', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_AUTO_POSITION=1;
開始復制：
TheSlave|mysql> START SLAVE;
6、檢查
TheSlave|mysql> SHOW SLAVE STATUS \G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Seconds_Behind_Master: 0 ...
看到IO和SQL執行緒處于運行中 （Yes）表示復制正常，
root@localhost 22:42:41 [(none)]> show slave status\G ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 16 Current database: *** NONE *** *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.106 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000016 Read_Master_Log_Pos: 383 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 557 Relay_Master_Log_File: mysql-bin.000016 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 383 Relay_Log_Space: 769 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1063306 Master_UUID: e8f74dde-ed8e-11e9-8ebb-000c29f5c092 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: e8f74dde-ed8e-11e9-8ebb-000c29f5c092:4 Executed_Gtid_Set: e8f74dde-ed8e-11e9-8ebb-000c29f5c092:1-4 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/234751.html
標籤：其他
上一篇：Redis 復制程序詳解