一.GTID innobackupex備份實現主從同步

1)master備份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --parallel=4 /backup

2)拷貝到slave上，并prepare和copy backup

innobackupex --defaults-file=/etc/my.cnf --apply-log --user=root --password=123456 --port=3306 /backup/2018-04-23_17-04-55

rm -rf /mysqlData/data/*

rm -rf /mysqlData/logs/undolog/*

innobackupex --defaults-file=/etc/my.cnf --copy-back --user=root --password=123456 --port=3306 /backup/2018-04-23_17-04-55

chown -R mysql:mysql /mysqlData/

/etc/init.d/mysqld start

3)從備份目錄的文件xtrabackup_info中獲取GTID信息

binlog_pos = filename 'binlog.000176', position '38885756', GTID of the last change '73c029dc-2034-11e8-90a5-005056a365b6:1-594908,

856d79f8-2038-11e8-b511-005056a330bb:1-3,

b658767f-2044-11e8-951f-005056a330bb:1-17477471'

4)master中的GTID信息

root@slave01 10:20: [(none)]> show master status\G

*************************** 1. row ***************************

File: binlog.000176

Position: 159643240

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-594908,

856d79f8-2038-11e8-b511-005056a330bb:1-3,

b658767f-2044-11e8-951f-005056a330bb:1-17577902

1 row in set (0.00 sec)

5)設置GTID

reset slave all;

reset master;

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

SET @@SESSION.SQL_LOG_BIN= 0;

SET @@GLOBAL.GTID_PURGED='73c029dc-2034-11e8-90a5-005056a365b6:1-594908,856d79f8-2038-11e8-b511-005056a330bb:1-3,b658767f-2044-11e8-951f-005056a330bb:1-17477471';

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

6)設置主從命令并啟動復制

CHANGE MASTER TO

MASTER_HOST='172.16.3.153',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='Rep12#3@',

master_auto_position=1;

#

start slave;

7)查看從庫的信息master信息

root@slave02 10:24: [(none)]> show master status\G

*************************** 1. row ***************************

File: binlog.000001

Position: 4936475

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-594908,

856d79f8-2038-11e8-b511-005056a330bb:1-3,

b658767f-2044-11e8-951f-005056a330bb:1-17480536

1 row in set (0.00 sec)

二.GTID mysqldump新建運行中的slave從庫

1)主庫先備份

mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -S=/data/my3306/run/mysql.sock --single-transaction --master-data=2 -A > /home/backup/all.sql

2)在備份文件all.sql中有GTID信息

-- GTID state at the beginning of the backup

--

#

SET @@GLOBAL.GTID_PURGED='73c029dc-2034-11e8-90a5-005056a365b6:1-594908,

856d79f8-2038-11e8-b511-005056a330bb:1-3,

b658767f-2044-11e8-951f-005056a330bb:1-17573387';

3)查看master上的GTID信息

root@slave01 09:24: [(none)]> show master status\G

*************************** 1. row ***************************

File: binlog.000176

Position: 155181848

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-594908,

856d79f8-2038-11e8-b511-005056a330bb:1-3,

b658767f-2044-11e8-951f-005056a330bb:1-17573561

1 row in set (0.00 sec)

這里已經執行過的GTID和備份all.sql中是不同的mysql

4)在一個將要成為slave的數據庫上作恢復

reset slave all;

reset master;

source /backup/all.sql

5)恢復完畢后再作一次reset slave;

Last_Errno: 1872

Last_Error: Slave failed to initialize relay log info structure from the repository

若是不reset slave，會報1872錯誤sql

6)主從命令

CHANGE MASTER TO

MASTER_HOST='172.16.3.153',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='Rep12#3@',

master_auto_position=1;

7)啟動復制start slave;

8)查看復制信息

Retrieved_Gtid_Set: b658767f-2044-11e8-951f-005056a330bb:17573388-17575621

Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-594908,

856d79f8-2038-11e8-b511-005056a330bb:1-3,

b658767f-2044-11e8-951f-005056a330bb:1-17574404

總結

以上是生活随笔為你收集整理的mysql5.7 xtrabackup_MySQL 5.7 基于GTID建立运行主库的从库-xtrabackup+mysqldump的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。