mysql使用xtrbackup+relaylog增量恢復(fù)注意事項(xiàng)
實(shí)驗(yàn)說(shuō)明
本次實(shí)驗(yàn)mysql5.7.19.使用了GTID，row格式的binlog
參數(shù)說(shuō)明
[mysql] password=root@1234 [mysqld] server_id=1 log_bin gtid_mode=1 binlog_format=row basedir=/usr/local/mysql datadir=/home/mysql/data
時(shí)點(diǎn)說(shuō)明
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來(lái)讀取binlog，需要server id不能一致。
重啟sever會(huì)自動(dòng)啟動(dòng)slave sql thread。我個(gè)人嘗試start slave sql_thread不能成功，但是重啟實(shí)例可以。
vi /etc/my.cnf server_id=2
初始化master
主要是通過(guò)該命令將relaylog的相關(guān)初始化出來(lái)。因?yàn)槭褂玫腉TID，不需要指定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ù)多個(gè)，拷貝多個(gè)過(guò)去即可，我這里的演示只拷貝了一個(gè)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ù)庫(kù)
/etc/init.d/mysql restart
可以看到日志中io thread 錯(cuò)誤，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ù)的時(shí)候發(fā)現(xiàn)relay-log.info里記錄的是binlog的名稱，這個(gè)流程沒(méi)有指定過(guò)pos。懷疑是relaylog內(nèi)指定的下一個(gè)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ù)庫(kù)
/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信息，避免報(bào)錯(cuò)影響
注：reset slave不會(huì)清除同步信息。
reset slave all;
到此這篇關(guān)于mysql使用xtrbackup+relaylog增量恢復(fù)的文章就介紹到這了,更多相關(guān)mysql增量恢復(fù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Mysql連接本地報(bào)錯(cuò):1130-host?...?is?not?allowed?to?connect?t
這篇文章主要給大家介紹了關(guān)于Mysql連接本地報(bào)錯(cuò):1130-host?...?is?not?allowed?to?connect?to?this?MySQL?server的解決方法,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03
MySQL INNER JOIN 的底層實(shí)現(xiàn)原理分析
這篇文章主要介紹了MySQL INNER JOIN 的底層實(shí)現(xiàn)原理,INNER JOIN的工作分為篩選和連接兩個(gè)步驟，連接時(shí)可以使用多種算法，通過(guò)本文，我們深入了解了MySQL中INNER JOIN的底層實(shí)現(xiàn)原理，需要的朋友可以參考下2023-06-06
MySQL執(zhí)行事務(wù)的語(yǔ)法與流程詳解
這篇文章主要介紹了MySQL執(zhí)行事務(wù)的語(yǔ)法與流程的相關(guān)資料，文中通過(guò)圖文介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-01-01
mysql5.7使用binlog 恢復(fù)數(shù)據(jù)的方法
MySQL的binlog日志是MySQL日志中非常重要的一種日志,記錄了數(shù)據(jù)庫(kù)所有的DML操作，那么怎樣通過(guò)binlog 恢復(fù)數(shù)據(jù)，本文就詳細(xì)的來(lái)介紹一下2021-06-06
子查詢是在查詢語(yǔ)句里面再嵌套一個(gè)查詢，這是因?yàn)槲覀冊(cè)谔崛?shù)據(jù)的時(shí)候有很多不知道的數(shù)據(jù)產(chǎn)生了依賴關(guān)系。本文為大家總結(jié)了一下MySQL數(shù)據(jù)庫(kù)子查詢語(yǔ)法規(guī)則，感興趣的可以了解一下2022-08-08
一文教你快速生成MySQL數(shù)據(jù)庫(kù)關(guān)系圖
我們經(jīng)常會(huì)用到一些表的數(shù)據(jù)庫(kù)關(guān)系圖,下面這篇文章主要給大家介紹了關(guān)于生成MySQL數(shù)據(jù)庫(kù)關(guān)系圖的相關(guān)資料,文中通過(guò)圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06
本篇文章是對(duì)關(guān)于mysql不重復(fù)字段值求和進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
CentOS 6.2 安裝 MySQL 5.7.28的教程(mysql 筆記)
本文通過(guò)圖文并茂的形式給大家介紹了CentOS 6.2 安裝 MySQL 5.7.28的教程，非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友參考下吧2019-11-11
分頁(yè)技術(shù)原理與實(shí)現(xiàn)之分頁(yè)的意義及方法（一）
這篇文章主要介紹了分頁(yè)技術(shù)原理與實(shí)現(xiàn)第一篇：為什么要進(jìn)行分頁(yè)及怎么分頁(yè)，感興趣的小伙伴們可以參考一下2016-06-06
這篇文章主要介紹了MySQL中的SUM函數(shù)使用教程,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識(shí),需要的朋友可以參考下2015-05-05