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
時點(diǎn)說明
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連接本地報錯:1130-host?...?is?not?allowed?to?connect?t
這篇文章主要給大家介紹了關(guān)于Mysql連接本地報錯:1130-host?...?is?not?allowed?to?connect?to?this?MySQL?server的解決方法,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03
MySQL INNER JOIN 的底層實現(xiàn)原理分析
這篇文章主要介紹了MySQL INNER JOIN 的底層實現(xiàn)原理,INNER JOIN的工作分為篩選和連接兩個步驟，連接時可以使用多種算法，通過本文，我們深入了解了MySQL中INNER JOIN的底層實現(xiàn)原理，需要的朋友可以參考下2023-06-06
這篇文章主要介紹了MySQL執(zhí)行事務(wù)的語法與流程的相關(guān)資料，文中通過圖文介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-01-01
mysql5.7使用binlog 恢復(fù)數(shù)據(jù)的方法
MySQL的binlog日志是MySQL日志中非常重要的一種日志,記錄了數(shù)據(jù)庫所有的DML操作，那么怎樣通過binlog 恢復(fù)數(shù)據(jù)，本文就詳細(xì)的來介紹一下2021-06-06
子查詢是在查詢語句里面再嵌套一個查詢，這是因為我們在提取數(shù)據(jù)的時候有很多不知道的數(shù)據(jù)產(chǎn)生了依賴關(guān)系。本文為大家總結(jié)了一下MySQL數(shù)據(jù)庫子查詢語法規(guī)則，感興趣的可以了解一下2022-08-08
一文教你快速生成MySQL數(shù)據(jù)庫關(guān)系圖
我們經(jīng)常會用到一些表的數(shù)據(jù)庫關(guān)系圖,下面這篇文章主要給大家介紹了關(guān)于生成MySQL數(shù)據(jù)庫關(guān)系圖的相關(guān)資料,文中通過圖文以及實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06
本篇文章是對關(guān)于mysql不重復(fù)字段值求和進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
CentOS 6.2 安裝 MySQL 5.7.28的教程(mysql 筆記)
本文通過圖文并茂的形式給大家介紹了CentOS 6.2 安裝 MySQL 5.7.28的教程，非常不錯，具有一定的參考借鑒價值，需要的朋友參考下吧2019-11-11
分頁技術(shù)原理與實現(xiàn)之分頁的意義及方法（一）
這篇文章主要介紹了分頁技術(shù)原理與實現(xiàn)第一篇：為什么要進(jìn)行分頁及怎么分頁，感興趣的小伙伴們可以參考一下2016-06-06
這篇文章主要介紹了MySQL中的SUM函數(shù)使用教程,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識,需要的朋友可以參考下2015-05-05