利用mysql全备 +binlog server恢复方法之伪装master
单实例试验
一、试验环境
10.72.7.40
实例 mysql3306为要恢复的对象，mysql3306的全备+binlog server（目录/data/mysql/mysql3306/backup）
实例mysql3307为伪装master
实例mysql3308为伪装master的slave，在其上恢复数据
1、mysql3306全备
innobackupex --defaults-file=/data/mysql/mysql3306/mysql3306.cnf -S /tmp/mysql3306.sock -uroot -phch123 /root/backup
2、mysql3306的binlog server
root@localhost:mysql3306.sock [zst1]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) cd /data/mysql/mysql3306/backup [root@bogon backup]# nohup mysqlbinlog --raw --read-from-remote-server --host=10.72.7.40 --port= --user=root --password=hch123 --stop-never mysql-bin. &
3、查看3306的数据
root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) 模拟数据写入 root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) select user,host from mysql.user;
Query OK, rows affected (0.09 sec)
Records: Duplicates: Warnings: root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) select user,host from mysql.user;
Query OK, rows affected (0.14 sec)
Records: Duplicates: Warnings: root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) root@localhost:mysql3306.sock [zst1]>select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| d20b918a-96c9-11e8-aae4-000c2969aede |
+--------------------------------------+
row in set (0.00 sec)
查看目前binlog位置
root@localhost:mysql3306.sock [zst1]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| mysql-bin. | | | | 959b9f31-75ef-11e8-97de-000c2969aede:-,
d20b918a-96c9-11e8-aae4-000c2969aede:- |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
row in set (0.00 sec)
模拟误操作
root@localhost:mysql3306.sock [zst1]>truncate table tb1;
Query OK, rows affected (0.08 sec) root@localhost:mysql3306.sock [zst1]>select * from tb1;
Empty set (0.00 sec) root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
继续写入数据
root@localhost:mysql3306.sock [zst1]>insert into tb1(c1, c2) select user,host from mysql.user;
Query OK, rows affected (0.09 sec)
Records: Duplicates: Warnings: root@localhost:mysql3306.sock [zst1]>select count(*) from tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
刷新binlog
root@localhost:mysql3306.sock [zst1]>flush logs;
Query OK, rows affected (0.11 sec)
查看binlog信息
root@localhost:mysql3306.sock [zst1]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) root@localhost:mysql3306.sock [zst1]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| mysql-bin. | | | | 959b9f31-75ef-11e8-97de-000c2969aede:-,
d20b918a-96c9-11e8-aae4-000c2969aede:- |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
row in set (0.00 sec)
二、查看恢复位置
解析binlog
[root@bogon data]# mysqlbinlog -v --base64-output=decode-rows mysql-bin. > .sql
恢复的位置为 mysql-bin.000005 1248，d20b918a-96c9-11e8-aae4-000c2969aede:18
三、创建伪装master 3308
1、初始化实例3308、启动并修改密码，省略……
2、查看3308 uuid信息
[root@bogon backup]# mysql -S /tmp/mysql3308.sock -uroot -phch123 查看uuid root@localhost:mysql3308.sock [(none)]>select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 8db05acd-a0f1-11e8-ad63-000c2969aede |
+--------------------------------------+
row in set (0.00 sec) root@localhost:mysql3308.sock [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec)
3、将3306的binlog复制到3308上
先关闭3308实例
root@localhost:mysql3308.sock [(none)]>shutdown;
Query OK, rows affected (0.00 sec) root@localhost:mysql3308.sock [(none)]>exit
删除3308的binlog
[root@bogon logs]# pwd
/data/mysql/mysql3308/logs
[root@bogon logs]# ll -thr
total 12K
-rw-r-----. mysql mysql Aug : mysql-bin.
-rw-r-----. mysql mysql Aug : mysql-bin.index
-rw-r-----. mysql mysql Aug : mysql-bin.
[root@bogon logs]# cat mysql-bin.index
/data/mysql/mysql3308/logs/mysql-bin.
/data/mysql/mysql3308/logs/mysql-bin.
拷贝3306binlog server的binlog至3308
[root@bogon logs]# cp /data/mysql/mysql3306/backup/* ./
[root@bogon logs]# ll -thr
total 16K
-rw-------. 1 root root 279 Aug 15 21:53 nohup.out
-rw-r-----. 1 root root 234 Aug 15 21:53 mysql-bin.000006
-rw-r-----. 1 root root 2.0K Aug 15 21:53 mysql-bin.000005
-rw-r-----. 1 root root 234 Aug 15 21:53 mysql-bin.000004
生成mysql-bin.index
[root@bogon logs]# ls /data/mysql/mysql3308/logs/mysql-bin.* > mysql-bin.index
[root@bogon logs]# cat mysql-bin.index
/data/mysql/mysql3308/logs/mysql-bin.
/data/mysql/mysql3308/logs/mysql-bin.
/data/mysql/mysql3308/logs/mysql-bin.
[root@bogon logs]# ll -thr
total 20K
-rw-------. root root Aug : nohup.out
-rw-r-----. root root Aug : mysql-bin.
-rw-r-----. root root .0K Aug : mysql-bin.
-rw-r-----. root root Aug : mysql-bin.
-rw-r--r--. root root Aug : mysql-bin.index
[root@bogon logs]# chown mysql. *
[root@bogon logs]# ll -thr
total 20K
-rw-------. mysql mysql Aug : nohup.out
-rw-r-----. mysql mysql Aug : mysql-bin.
-rw-r-----. mysql mysql .0K Aug : mysql-bin.
-rw-r-----. mysql mysql Aug : mysql-bin.
-rw-r--r--. mysql mysql Aug : mysql-bin.index [root@bogon logs]# rm -rf nohup.out
启动3308实例
[root@bogon backup]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/mysql3308.cnf & [root@bogon backup]# mysql -S /tmp/mysql3308.sock -uroot -phch123 root@localhost:mysql3308.sock [(none)]>select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 8db05acd-a0f1-11e8-ad63-000c2969aede |
+--------------------------------------+
row in set (0.00 sec)
可以看到binlog已经识别出来了
root@localhost:mysql3308.sock [(none)]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.00 sec) root@localhost:mysql3308.sock [(none)]>show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin. | | | | 8db05acd-a0f1-11e8-ad63-000c2969aede:-,
959b9f31-75ef-11e8-97de-000c2969aede:-,
d20b918a-96c9-11e8-aae4-000c2969aede:- |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------+
row in set (0.00 sec)
可以看到Executed_Gtid_Set中多了8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,需要在slave 中gtid purged掉,否则会报1236错误(因为刚才新建账号产生的log已经被删除了)
四、创建伪装master3308的slave3309
1、用3306的全备恢复
[root@bogon --15_21--]# pwd
/root/backup/--15_21--
[root@bogon --15_21--]# innobackupex --apply-log /root/backup/--15_21--
关闭3309
[root@bogon --15_21--]#mysql -S /tmp/mysql3309.sock -uroot -p
root@localhost:mysql3309.sock [(none)]>shutdown;
先备份3309的datadir，并清除里面的文件
[root@bogon data]# pwd
/data/mysql/mysql3309/data
[root@bogon mysql3309]# cp -a data/ data_bak
[root@bogon mysql3309]# cd data
[root@bogon data]# rm -rf *
将还原文件拷贝过来
[root@bogon data]# cp -r /root/backup/--15_21--/* /data/mysql/mysql3309/data/
修改权限
[root@bogon data]# chown -R mysql. * [root@bogon data]# ll -thr
total 421M
-rw-r-----. mysql mysql Aug : backup-my.cnf
drwxr-x---. mysql mysql Aug : hch
drwxr-x---. mysql mysql Aug : hch1
-rw-r-----. mysql mysql Aug : ib_buffer_pool
-rw-r-----. mysql mysql 100M Aug : ibdata1
-rw-r-----. mysql mysql 100M Aug : ib_logfile0
-rw-r-----. mysql mysql 100M Aug : ib_logfile1
-rw-r-----. mysql mysql 100M Aug : ib_logfile2
-rw-r-----. mysql mysql 12M Aug : ibtmp1
drwxr-x---. mysql mysql .0K Aug : mysql
drwxr-x---. mysql mysql .0K Aug : performance_schema
drwxr-x---. mysql mysql .0K Aug : sys
drwxr-x---. mysql mysql Aug : test
drwxr-x---. mysql mysql Aug : userdb
drwxr-x---. mysql mysql .0K Aug : wubx
-rw-r--r--. mysql mysql Aug : xtrabackup_binlog_pos_innodb
-rw-r-----. mysql mysql Aug : xtrabackup_binlog_info
-rw-r-----. mysql mysql Aug : xtrabackup_info
-rw-r-----. mysql mysql Aug : xtrabackup_checkpoints
-rw-r-----. mysql mysql 8.0M Aug : xtrabackup_logfile
-rw-r--r--. mysql mysql Aug : xtrabackup_master_key_id
drwxr-x---. mysql mysql Aug : zst
drwxr-x---. mysql mysql Aug : zst1
查看备份的binlog位置为959b9f31-75ef-11e8-97de-000c2969aede:1-61970,d20b918a-96c9-11e8-aae4-000c2969aede:1-15
启动3309实例
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3309/mysql3309.cnf & [root@bogon data]# mysql -S /tmp/mysql3309.sock -uroot -phch123 root@localhost:mysql3309.sock [(none)]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin. | | | | 61cfd125-a0f2-11e8-b8bc-000c2969aede:-,
959b9f31-75ef-11e8-97de-000c2969aede:-,
d20b918a-96c9-11e8-aae4-000c2969aede:- |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+
row in set (0.00 sec)
发现又多了个61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2，不太清楚是哪个gtid，查看3307的uuid也不是这个，为了影响实验效果，下面的gtid_purged也把这个加入进去了
查看数据
root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
设置gtid_purged，别忘记加入3308改密码的gtid，并且加入了61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2
root@localhost:mysql3309.sock [(none)]>reset master;
Query OK, rows affected (0.03 sec) root@localhost:mysql3309.sock [(none)]>set global gtid_purged='959b9f31-75ef-11e8-97de-000c2969aede:1-61970,d20b918a-96c9-11e8-aae4-000c2969aede:1-15,8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2';
Query OK, rows affected (0.00 sec) root@localhost:mysql3309.sock [(none)]>change master to master_host='10.72.7.40', master_port=, master_user='hch', master_password='hch123', master_auto_position=;
Query OK, rows affected, warnings (0.02 sec) root@localhost:mysql3309.sock [(none)]>start slave sql_thread until sql_before_gtids='d20b918a-96c9-11e8-aae4-000c2969aede:18';
Query OK, rows affected (0.01 sec) root@localhost:mysql3309.sock [(none)]>show slave status\G;
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.72.7.40
Master_User: hch
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: bogon-relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 61cfd125-a0f2-11e8-b8bc-000c2969aede:-,
8db05acd-a0f1-11e8-ad63-000c2969aede:-,
959b9f31-75ef-11e8-97de-000c2969aede:-,
d20b918a-96c9-11e8-aae4-000c2969aede:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) ERROR:
No query specified root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.01 sec) root@localhost:mysql3309.sock [(none)]>start slave io_thread;
Query OK, rows affected (0.00 sec) root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
发现此时数据已经恢复了。
问题
报错ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
原因：设置gtid_purged，没有加入了61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2
root@localhost:mysql3309.sock [(none)]>set global gtid_purged='959b9f31-75ef-11e8-97de-000c2969aede:1-61970,d20b918a-96c9-11e8-aae4-000c2969aede:1-15,8db05acd-a0f1-11e8-ad63-000c2969aede:1-3';
Query OK, 0 rows affected (0.02 sec)
root@localhost:mysql3309.sock [(none)]>change master to master_host='10.72.7.40', master_port=3308, master_user='hch', master_password='hch123', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.09 sec)
root@localhost:mysql3309.sock [(none)]>start slave io_thread;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
root@localhost:mysql3309.sock [(none)]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.72.7.40
Master_User: hch
Master_Port: 3308
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: bogon-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1872
Last_Error: Slave failed to initialize relay log info structure from the repository
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1872
Last_SQL_Error: Slave failed to initialize relay log info structure from the repository
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180815 22:43:54
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,
959b9f31-75ef-11e8-97de-000c2969aede:1-61970,
d20b918a-96c9-11e8-aae4-000c2969aede:1-15
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
解决方法：
1、设置gtid_purged加入了61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2
2、reset slave all，重新设置主从
reset slave all
change master to master_host='10.72.7.40', master_port=3308, master_user='hch', master_password='hch123', master_auto_position=1;
start slave sql_thread until sql_before_gtids='d20b918a-96c9-11e8-aae4-000c2969aede:18';
具体操作过程如下：
root@localhost:mysql3309.sock [(none)]>reset master;
Query OK, 0 rows affected (0.03 sec)
root@localhost:mysql3309.sock [(none)]>set global gtid_purged='959b9f31-75ef-11e8-97de-000c2969aede:1-61970,d20b918a-96c9-11e8-aae4-000c2969aede:1-15,8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2';
Query OK, 0 rows affected (0.00 sec)
root@localhost:mysql3309.sock [(none)]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.72.7.40
Master_User: hch
Master_Port: 3308
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: bogon-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1872
Last_Error: Slave failed to initialize relay log info structure from the repository
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1872
Last_SQL_Error: Slave failed to initialize relay log info structure from the repository
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180815 22:43:54
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2,
8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,
959b9f31-75ef-11e8-97de-000c2969aede:1-61970,
d20b918a-96c9-11e8-aae4-000c2969aede:1-15
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
root@localhost:mysql3309.sock [(none)]>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost:mysql3309.sock [(none)]>start slave sql_thread until sql_before_gtids='d20b918a-96c9-11e8-aae4-000c2969aede:18';
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
root@localhost:mysql3309.sock [(none)]>reset slave all;
Query OK, 0 rows affected (0.03 sec)
root@localhost:mysql3309.sock [(none)]>change master to master_host='10.72.7.40', master_port=3308, master_user='hch', master_password='hch123', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
root@localhost:mysql3309.sock [(none)]>start slave sql_thread until sql_before_gtids='d20b918a-96c9-11e8-aae4-000c2969aede:18';
Query OK, 0 rows affected (0.01 sec)
root@localhost:mysql3309.sock [(none)]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.72.7.40
Master_User: hch
Master_Port: 3308
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: bogon-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
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: 0
Relay_Log_Space: 154
Until_Condition: SQL_BEFORE_GTIDS
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: 0
Master_UUID:
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:
Executed_Gtid_Set: 61cfd125-a0f2-11e8-b8bc-000c2969aede:1-2,
8db05acd-a0f1-11e8-ad63-000c2969aede:1-3,
959b9f31-75ef-11e8-97de-000c2969aede:1-61970,
d20b918a-96c9-11e8-aae4-000c2969aede:1-15
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1;
+----------+
| count(*) |
+----------+
| 35 |
+----------+
1 row in set (0.01 sec)
root@localhost:mysql3309.sock [(none)]>start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
root@localhost:mysql3309.sock [(none)]>select count(*) from zst1.tb1;
+----------+
| count(*) |
+----------+
| 53 |
+----------+
1 row in set (0.00 sec)
参考
利用binlogserver恢复单表实验【转】 - paul_hch - 博客园 https://www.cnblogs.com/paul8339/p/9378269.html
通过全备+binlog_server同步恢复被drop的库或表 - 2森林 - 博客园 https://www.cnblogs.com/2woods/p/9394625.html
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository - 西橙 - 博客园 https://www.cnblogs.com/Bccd/p/5856716.html
利用伪master主机来增量恢复mysql - CSDN博客 https://blog.csdn.net/zengxuewen2045/article/details/51465078
多台机器试验
主机10.72.16.50的MySQL 3306实例
伪装master 10.72.16.50的3307实例
伪装master的slave 10.72.16.112 3306实例
一、创建试验环境
10.72.16.50的MySQL 3306实例执行
1、备份数据库
innobackupex --defaults-file=/etc/my.cnf -uroot -phch123 /root/test
2、模拟误删除
mysql> use hch;
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> select * from tb1;
+----+-------+
| id | cname |
+----+-------+
| | php |
| | java |
| | go |
+----+-------+
rows in set (0.03 sec) mysql> insert into tb1(cname) values('test');
Query OK, row affected (0.06 sec) mysql> insert into tb1(cname) values('test1');
Query OK, row affected (0.00 sec) mysql> insert into tb1(cname) values('test2');
Query OK, row affected (0.00 sec) mysql> insert into tb1(cname) values('test3');
Query OK, row affected (0.00 sec) mysql> insert into tb1(cname) values('test4');
Query OK, row affected (0.00 sec) mysql> select * from tb1;
+----+-------+
| id | cname |
+----+-------+
| | php |
| | java |
| | go |
| | test |
| | test1 |
| | test2 |
| | test3 |
| | test4 |
+----+-------+
rows in set (0.00 sec) mysql> insert into tb1(cname) values('test5');
Query OK, row affected (0.00 sec) mysql> insert into tb1(cname) values('test6');
Query OK, row affected (0.00 sec) mysql> insert into tb1(cname) values('test7');
Query OK, row affected (0.00 sec) mysql> insert into tb1(cname) values('test8');
Query OK, row affected (0.01 sec) mysql> select * from tb1;
+----+-------+
| id | cname |
+----+-------+
| | php |
| | java |
| | go |
| | test |
| | test1 |
| | test2 |
| | test3 |
| | test4 |
| | test5 |
| | test6 |
| | test7 |
| | test8 |
+----+-------+
rows in set (0.00 sec) 执行误操作
mysql> truncate table tb1;
Query OK, 0 rows affected (0.15 sec) 查看binlog位置
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 2731
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,
671bac73-a032-11e8-a493-000c29bf3444:1-11,
909c25b1-7f67-11e8-9b9d-000c29bf3444:1,
ee639e4e-358a-11e7-87fa-000c29466957:1-753
1 row in set (0.00 sec) ERROR:
No query specified 刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.08 sec) mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 671bac73-a032-11e8-a493-000c29bf3444 |
+--------------------------------------+
1 row in set (0.02 sec) mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 | 234 | | | 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,
671bac73-a032-11e8-a493-000c29bf3444:1-11,
909c25b1-7f67-11e8-9b9d-000c29bf3444:1,
ee639e4e-358a-11e7-87fa-000c29466957:1-753 |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3、查看误操作binlog日志位置
mysqlbinlog -v --base64-output=decode-rows mysql-bin. > .sql
位置为mysql-bin.000002的2582，gtid为671bac73-a032-11e8-a493-000c29bf3444:11
二、在10.72.16.50上创建3307的伪装master
1、初始化3307
[root@hchtest3 data]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf --initialize [root@hchtest3 data]# cat error.log
--15T09::.046760Z [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
--15T09::.749883Z [Warning] InnoDB: New log files created, LSN=
--15T09::.164271Z [Warning] InnoDB: Creating foreign key constraint system tables.
--15T09::.572772Z [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a68949d2-a06c-11e8-bba3-000c29bf3444.
--15T09::.581852Z [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
--15T09::.586219Z [Note] A temporary password is generated for root@localhost: /IHqjytdouM
2、启动3307
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &
登录修改密码并关闭
[root@hchtest3 data]# mysql -S /tmp/mysql3307.sock -uroot -p mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
rows in set (0.03 sec) mysql> alter user user() identified by 'hch123'; 创建复制账号
mysql> grant all privileges on *.* to hch@'%' identified by 'hch123'; mysql> flush privileges; mysql> shutdown;
Query OK, rows affected (0.02 sec)
3、拷贝50的3306 binlog至3307 datadir下
rm -rf /data/mysql/mysql3307/data/mysql-bin.* cp -a /data/mysql/mysql3306/backup/* /data/mysql/mysql3306/data/ 创建mysql-bin.index [root@hchtest3 data]# ls /data/mysql/mysql3307/data/mysql-bin.* > mysql-bin.index [root@hchtest3 data]# cat mysql-bin.index
/data/mysql/mysql3307/data/mysql-bin.000001
/data/mysql/mysql3307/data/mysql-bin.000002
/data/mysql/mysql3307/data/mysql-bin.000003 修改权限
chown mysql. mysql-bin.*
4、启动伪装master 3307实例
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &
5、查看binlog日志
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+ mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
| mysql-bin. | | | | 671bac73-a032-11e8-a493-000c29bf3444:-,
909c25b1-7f67-11e8-9b9d-000c29bf3444:,
a68949d2-a06c-11e8-bba3-000c29bf3444: |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
row in set (0.03 sec) mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| a68949d2-a06c-11e8-bba3-000c29bf3444 |
+--------------------------------------+
row in set (0.00 sec) 查看binlog位置发现多了a68949d2-a06c-11e8-bba3-000c29bf3444:，这个为3307初始化后添加密码操作产生的gtid，设置伪装master从库时gtid_purged需要添加这个否则会1236报错
三、设置伪装master 10.72.16.50 3307的slave 10.72.16.112 3306
1、用10.72.16.50 3306的全备还原
innobackupex --apply-log /root/test/--15_15--
2、查看binlog日志备份位置
[root@hchtest3 --15_15--]# cat xtrabackup_binlog_info
mysql-bin. 22dc7409-2cd5-11e7-888c-000c29bf3444:-,
671bac73-a032-11e8-a493-000c29bf3444:,
909c25b1-7f67-11e8-9b9d-000c29bf3444:,
ee639e4e-358a-11e7-87fa-000c29466957:-
[root@hchtest3 --15_15--]# cat xtrabackup_info
uuid = 7b379338-a060-11e8-8a15-000c29bf3444
name =
tool_name = innobackupex
tool_command = --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -phch123 /root/test
tool_version = 2.4.
ibbackup_version = 2.4.
server_version = 5.7.-log
start_time = -- ::
end_time = -- ::
lock_time =
binlog_pos = filename 'mysql-bin.000002', position '234', GTID of the last change '22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,
671bac73-a032-11e8-a493-000c29bf3444:1,
909c25b1-7f67-11e8-9b9d-000c29bf3444:1,
ee639e4e-358a-11e7-87fa-000c29466957:1-753'
位置为
mysql-bin. 22dc7409-2cd5-11e7-888c-000c29bf3444:-,
671bac73-a032-11e8-a493-000c29bf3444:,
909c25b1-7f67-11e8-9b9d-000c29bf3444:,
ee639e4e-358a-11e7-87fa-000c29466957:-
拷贝10.72.16.112的3306 datadir下，之前要停止10.72.16.112的3306，并清除datadir目录。
[root@hchtest3 --15_15--]# scp -r * root@10.72.16.112:/usr/local/mysql/data/
3、启动slave并设置同步
[root@hchtest4 script]# service mysql status
MySQL running () [ OK ] mysql> reset master;
Query OK, rows affected (0.00 sec)
此处要加入50的3307的gtid
mysql> SET @@GLOBAL.GTID_PURGED='22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,671bac73-a032-11e8-a493-000c29bf3444:1,909c25b1-7f67-11e8-9b9d-000c29bf3444:1,ee639e4e-358a-11e7-87fa-000c29466957:1-753,a68949d2-a06c-11e8-bba3-000c29bf3444:1'; mysql> change master to master_host='10.72.16.50', master_port=, master_user='hch', master_password='hch123', master_auto_position=;
Query OK, rows affected, warnings (0.01 sec) mysql> start slave sql_thread until sql_before_gtids='671bac73-a032-11e8-a493-000c29bf3444:11';
Query OK, rows affected (0.03 sec) mysql> show slave status\G;
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.72.16.50
Master_User: hch
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: mysql-relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:-,
671bac73-a032-11e8-a493-000c29bf3444:,
909c25b1-7f67-11e8-9b9d-000c29bf3444:,
a68949d2-a06c-11e8-bba3-000c29bf3444:,
ee639e4e-358a-11e7-87fa-000c29466957:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) ERROR:
No query specified mysql> start slave io_thread;
Query OK, rows affected (0.00 sec) mysql> show slave status\G;
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.72.16.50
Master_User: hch
Master_Port:
Connect_Retry:
Master_Log_File: mysql-bin.
Read_Master_Log_Pos:
Relay_Log_File: mysql-relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File: mysql-bin.
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: a68949d2-a06c-11e8-bba3-000c29bf3444
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 671bac73-a032-11e8-a493-000c29bf3444:-,
a68949d2-a06c-11e8-bba3-000c29bf3444:-
Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:-,
671bac73-a032-11e8-a493-000c29bf3444:-,
909c25b1-7f67-11e8-9b9d-000c29bf3444:,
a68949d2-a06c-11e8-bba3-000c29bf3444:,
ee639e4e-358a-11e7-87fa-000c29466957:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) ERROR:
No query specified
查看发现数据已经恢复
mysql> select * from hch.tb1;
+----+-------+
| id | cname |
+----+-------+
| | php |
| | java |
| | go |
| | test |
| | test1 |
| | test2 |
| | test3 |
| | test4 |
| | test5 |
| | test6 |
| | test7 |
| | test8 |
+----+-------+
rows in set (0.02 sec)
问题
slave设置时报错1236
mysql> start slave io_thread;
Query OK, rows affected (0.00 sec) mysql> show slave status\G;
*************************** . row ***************************
Slave_IO_State:
Master_Host: 10.72.16.50
Master_User: hch
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: mysql-relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: SQL_BEFORE_GTIDS
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error: Got fatal error from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: a68949d2-a06c-11e8-bba3-000c29bf3444
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp: ::
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:-,
671bac73-a032-11e8-a493-000c29bf3444:,
909c25b1-7f67-11e8-9b9d-000c29bf3444:,
ee639e4e-358a-11e7-87fa-000c29466957:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) ERROR:
No query specified
原因为set gtid_purged时未加入伪装master 10.72.16.50 3307 的gtid信息a68949d2-a06c-11e8-bba3-000c29bf3444:1
解决方法如下：
mysql> reset slave all;
Query OK, rows affected (0.05 sec) mysql> SET @@GLOBAL.GTID_PURGED='22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,671bac73-a032-11e8-a493-000c29bf3444:1,909c25b1-7f67-11e8-9b9d-000c29bf3444:1,ee639e4e-358a-11e7-87fa-000c29466957:1-753,a68949d2-a06c-11e8-bba3-000c29bf3444:1';
ERROR (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysql> reset master;
Query OK, rows affected (0.00 sec) mysql> SET @@GLOBAL.GTID_PURGED='22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,671bac73-a032-11e8-a493-000c29bf3444:1,909c25b1-7f67-11e8-9b9d-000c29bf3444:1,ee639e4e-358a-11e7-87fa-000c29466957:1-753,a68949d2-a06c-11e8-bba3-000c29bf3444:1';
Query OK, rows affected (0.00 sec)