多台机器试验
主机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 |
+----+-------+
| 1 | php |
| 2 | java |
| 3 | go |
+----+-------+
3 rows in set (0.03 sec)
mysql> insert into tb1(cname) values('test');
Query OK, 1 row affected (0.06 sec)
mysql> insert into tb1(cname) values('test1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1(cname) values('test2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1(cname) values('test3');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1(cname) values('test4');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+----+-------+
| id | cname |
+----+-------+
| 1 | php |
| 2 | java |
| 3 | go |
| 4 | test |
| 5 | test1 |
| 6 | test2 |
| 7 | test3 |
| 8 | test4 |
+----+-------+
8 rows in set (0.00 sec)
mysql> insert into tb1(cname) values('test5');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1(cname) values('test6');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1(cname) values('test7');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1(cname) values('test8');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb1;
+----+-------+
| id | cname |
+----+-------+
| 1 | php |
| 2 | java |
| 3 | go |
| 4 | test |
| 5 | test1 |
| 6 | test2 |
| 7 | test3 |
| 8 | test4 |
| 9 | test5 |
| 10 | test6 |
| 11 | test7 |
| 12 | test8 |
+----+-------+
12 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.000002 > 2.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
2018-08-15T09:21:50.046760Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-15T09:21:53.749883Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-15T09:21:54.164271Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-15T09:21:54.572772Z 0 [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.
2018-08-15T09:21:54.581852Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-15T09:21:54.586219Z 1 [Note] A temporary password is generated for root@localhost: 8/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 |
+--------------------+
4 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, 0 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.000001 | 493 |
| mysql-bin.000002 | 2778 |
| mysql-bin.000003 | 234 |
| mysql-bin.000004 | 234 |
+------------------+-----------+
mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000004 | 234 | | | 671bac73-a032-11e8-a493-000c29bf3444:1-11,
909c25b1-7f67-11e8-9b9d-000c29bf3444:1,
a68949d2-a06c-11e8-bba3-000c29bf3444:1 |
+------------------+----------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| a68949d2-a06c-11e8-bba3-000c29bf3444 |
+--------------------------------------+
1 row in set (0.00 sec)
查看binlog位置发现多了a68949d2-a06c-11e8-bba3-000c29bf3444:1，这个为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/2018-08-15_15-54-24
2、查看binlog日志备份位置
[root@hchtest3 2018-08-15_15-54-24]# cat xtrabackup_binlog_info
mysql-bin.000002 234 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
[root@hchtest3 2018-08-15_15-54-24]# 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.12
ibbackup_version = 2.4.12
server_version = 5.7.17-log
start_time = 2018-08-15 15:54:29
end_time = 2018-08-15 15:54:47
lock_time = 0
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.000002 234 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
拷贝10.72.16.112的3306 datadir下，之前要停止10.72.16.112的3306，并清除datadir目录。
[root@hchtest3 2018-08-15_15-54-24]# scp -r * [email protected]:/usr/local/mysql/data/
3、启动slave并设置同步
[root@hchtest4 script]# service mysql status
MySQL running (16977) [ OK ]
mysql> reset master;
Query OK, 0 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=3307, master_user='hch', master_password='hch123', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave sql_thread until sql_before_gtids='671bac73-a032-11e8-a493-000c29bf3444:11';
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.72.16.50
Master_User: hch
Master_Port: 3307
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-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: 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,
671bac73-a032-11e8-a493-000c29bf3444:1,
909c25b1-7f67-11e8-9b9d-000c29bf3444:1,
a68949d2-a06c-11e8-bba3-000c29bf3444:1,
ee639e4e-358a-11e7-87fa-000c29466957:1-753
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.72.16.50
Master_User: hch
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 665
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 2715
Relay_Master_Log_File: mysql-bin.000002
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: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2582
Relay_Log_Space: 4716
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: NULL
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: 503307
Master_UUID: a68949d2-a06c-11e8-bba3-000c29bf3444
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:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 671bac73-a032-11e8-a493-000c29bf3444:2-11,
a68949d2-a06c-11e8-bba3-000c29bf3444:2-3
Executed_Gtid_Set: 22dc7409-2cd5-11e7-888c-000c29bf3444:1-984109,
671bac73-a032-11e8-a493-000c29bf3444:1-10,
909c25b1-7f67-11e8-9b9d-000c29bf3444:1,
a68949d2-a06c-11e8-bba3-000c29bf3444:1,
ee639e4e-358a-11e7-87fa-000c29466957:1-753
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
查看发现数据已经恢复
mysql> select * from hch.tb1;
+----+-------+
| id | cname |
+----+-------+
| 1 | php |
| 2 | java |
| 3 | go |
| 4 | test |
| 5 | test1 |
| 6 | test2 |
| 7 | test3 |
| 8 | test4 |
| 9 | test5 |
| 10 | test6 |
| 11 | test7 |
| 12 | test8 |
+----+-------+
12 rows in set (0.02 sec)
问题
slave设置时报错1236
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.72.16.50
Master_User: hch
Master_Port: 3307
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-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: 1236
Last_IO_Error: Got fatal error 1236 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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 503307
Master_UUID: a68949d2-a06c-11e8-bba3-000c29bf3444
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: 180815 18:02:12
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 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
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 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, 0 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 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
mysql> reset master;
Query OK, 0 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, 0 rows affected (0.00 sec)