mysql使用xtrbackup+relaylog增量恢復(fù)注意事項
實驗說明
本次實驗mysql5.7.19.使用了GTID，row格式的binlog
參數(shù)說明
[mysql] password=root@1234 [mysqld] server_id=1 log_bin gtid_mode=1 binlog_format=row basedir=/usr/local/mysql datadir=/home/mysql/data
時點說明
bin.000001 1-154 | 創(chuàng)建test表 |
bin.000001 154 | xtrabackup --backup |
bin.000001 154-end | 創(chuàng)建test_dml表,test表數(shù)據(jù) |
bin.000002 | test表插入數(shù)據(jù) |
xtrabackup備份恢復(fù)
備份
[root@mysql57-1 tmp]# xtrabackup --user=root --password=root@1234 --socket=/tmp/mysql.sock --backup --target-dir=/tmp/bak .... 230508 15:48:12 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '2568864' xtrabackup: Stopping log copying thread. .230508 15:48:12 >> log scanned up to (2568873) 230508 15:48:13 Executing UNLOCK TABLES 230508 15:48:13 All tables unlocked 230508 15:48:13 [00] Copying ib_buffer_pool to /tmp/bak/ib_buffer_pool 230508 15:48:13 [00] ...done 230508 15:48:13 Backup created in directory '/tmp/bak/' MySQL binlog position: filename 'mysql57-1-bin.000001', position '154' 230508 15:48:13 [00] Writing /tmp/bak/backup-my.cnf 230508 15:48:13 [00] ...done 230508 15:48:13 [00] Writing /tmp/bak/xtrabackup_info 230508 15:48:13 [00] ...done xtrabackup: Transaction log of lsn (2568864) to (2568873) was copied. 230508 15:48:13 completed OK!
prepare恢復(fù)
[root@mysql57-1 bak]# xtrabackup --prepare --target-dir=/tmp/bak InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: 5.7.40 started; log sequence number 2569237 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2569256 230511 16:54:37 completed OK!
復(fù)制回原目錄
cp -r /tmp/bak /home/mysql/data chown -R mysql:mysql /home/mysql/data
relaylog增量恢復(fù)初始
修改server_id
做為relay log來讀取binlog，需要server id不能一致。
重啟sever會自動啟動slave sql thread。我個人嘗試start slave sql_thread不能成功，但是重啟實例可以。
vi /etc/my.cnf server_id=2
初始化master
主要是通過該命令將relaylog的相關(guān)初始化出來。因為使用的GTID，不需要指定pos
CHANGE MASTER TO master_host='1',master_password='1',master_user='1',master_log_file='1',master_log_pos=4;
拷貝binlog為relaylog
binlog和relaylog的格式完全相同，只需要將名字處理成relaylog的樣式即可
如需要恢復(fù)多個，拷貝多個過去即可，我這里的演示只拷貝了一個binlog
cp mysql57-1-bin.000001 /home/mysql/data/mysql57-1-relay-bin.000001 chown mysql:mysql /home/mysql/data/mysql57-1-relay-bin.00000*
修改index
INDEX同步修改
vi mysql57-1-relay-bin.index ./mysql57-1-relay-bin.000001
重啟數(shù)據(jù)庫
/etc/init.d/mysql restart
可以看到日志中io thread 錯誤，sql thread正常
2023-05-11T13:31:30.297886Z 1 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider
using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2023-05-11T13:31:30.297994Z 1 [ERROR] Slave I/O for channel '': error connecting to master '1@1:3306' - retry-time: 60 retries: 1, Error_code: 2003
2023-05-11T13:31:30.299542Z 2 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2023-05-11T13:31:30.299616Z 2 [Note] Slave SQL thread for channel '' initialized, starting replication in log '1' at position 4, relay log './mysql57-1-relay-bin.000001' position: 4
2023-05-11T13:31:30.304018Z 0 [Note] Event Scheduler: Loaded 0 events
2023-05-11T13:31:30.304223Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.19-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
查看數(shù)據(jù)確認(rèn)
mysql> show tables; +---------------+ | Tables_in_ddd | +---------------+ | ddl_test | | dml_test | | pitr | | test | | ttt | +---------------+ 5 rows in set (0.00 sec) mysql> select * from test; +---+---------------------+ | x | y | +---+---------------------+ | 1 | 2023-05-08 15:51:37 | | 2 | 2023-05-08 15:51:39 | | 3 | 2023-05-08 15:51:42 | | 4 | 2023-05-08 15:51:45 | | 5 | 2023-05-08 15:51:49 | +---+---------------------+ 5 rows in set (0.00 sec) mysql> select * from dml_test; Empty set (0.00 sec)
relaylog增量恢復(fù)接續(xù)
接續(xù)的時候發(fā)現(xiàn)relay-log.info里記錄的是binlog的名稱，這個流程沒有指定過pos。懷疑是relaylog內(nèi)指定的下一個relaylog名稱。
拷貝binlog為relaylog
cp mysql57-1-bin.000002 /home/mysql /data/mysql57-1-relay-bin.000002 chown mysql:mysql /data/mysql57-1-relay-bin.000002
修改index
INDEX同步修改
vi mysql57-1-relay-bin.index ./mysql57-1-relay-bin.000002
重啟數(shù)據(jù)庫
/etc/init.d/mysql restart
查看數(shù)據(jù)確認(rèn)
mysql> use ddd; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------+ | Tables_in_ddd | +---------------+ | ddl_test | | dml_test | | pitr | | test | | ttt | +---------------+ 5 rows in set (0.00 sec) mysql> select * from dml_test; +------+------+ | x | y | +------+------+ | 1 | 123 | | 1 | qwe | | 1 | ttt | +------+------+ 3 rows in set (0.00 sec)
slave信息清理
恢復(fù)流程完成后，清理slave信息，避免報錯影響
注：reset slave不會清除同步信息。
reset slave all;
到此這篇關(guān)于mysql使用xtrbackup+relaylog增量恢復(fù)的文章就介紹到這了,更多相關(guān)mysql增量恢復(fù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL如何添加數(shù)據(jù)insert命令問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-11-11
mysql的docker容器如何設(shè)置默認(rèn)的數(shù)據(jù)庫技巧詳解
這篇文章主要為大家介紹了mysql的docker容器如何設(shè)置默認(rèn)的數(shù)據(jù)庫技巧詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2023-10-10
在寫報表功能時遇到一個需要根據(jù)用戶id分組查詢最新一條錢包明細(xì)數(shù)據(jù)的需求,下面這篇文章主要給大家總結(jié)介紹了關(guān)于Mysql分組查詢?nèi)∽钚碌膸追N方案,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-05-05
這篇文章主要介紹了mysql 計算函數(shù)，函數(shù)沒有SQL的可移植性強 能運行在多個系統(tǒng)上的代碼稱為可移植的（portable）。相對來說，多數(shù)SQL語句是可移植的，在SQL實現(xiàn)之間有差異時，這些差異通常不那么難處理，下面來看看文章的具體內(nèi)容吧2021-10-10
關(guān)于MYSQL 遠(yuǎn)程登錄的授權(quán)方法 命令
gearman + mysql方式實現(xiàn)持久化操作示例
MySql總彈出mySqlInstallerConsole窗口的解決方法