GTID情形中手动修复主从故障一例(Error 1146)

 

 

 

Preface

Preface

Preface

 

 

 

    In my last test of pt-heartbeat,both
of master and slave were out of disk.And the mysql client was hang.In
order to resolve the issue,I’ve tryed to fix the replicaiton environment
without using mysqldump to reconfigure the slave.Let’s see the
details.

    Today I’m gonna test how to rescue a
dropped table from binlog server based on a full Xtrabackup backup
set.

    I got an replication error 1236 when I
modified the password of a user without starting slave threads of
replication.Further more,the user was absent in slave.below is the whole operation how I solve
the issue.

 

 

 

Procedure

Framework

Procedure

 

 

 

I
dropped test tables in database “sysbench” to release the disk space on
master.

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm1 192.168.56.100/3306 master CentOS 7.0 5.7.21 on row
zlm2 192.168.56.101/3306 slave CentOS 7.0 5.7.21 on row
zlm3 192.168.56.102/3306 binlog server CentOS 7.0 5.7.21 on row

I
modified the user password on master at first.

 1 [root@zlm2 08:56:51 /data/mysql/mysql3306/logs]
 2 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=1 --table-size=10000000 --mysql-storage-engine=innodb cleanup
 3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 Dropping table 'sbtest1'...
 6 
 7 (zlm@192.168.1.101 3306)[(none)]>use sysbench;
 8 Reading table information for completion of table and column names
 9 You can turn off this feature to get a quicker startup with -A
10 
11 Database changed
12 (zlm@192.168.1.101 3306)[sysbench]>show tables;
13 +--------------------+
14 | Tables_in_sysbench |
15 +--------------------+
16 | hb                 |
17 | sbtest2            |
18 | sbtest3            |
19 | sbtest4            |
20 | sbtest5            |
21 +--------------------+
22 5 rows in set (0.00 sec)
23 
24 //Only sbtest1 was deleted.It's not enough.
25 
26 [root@zlm2 08:59:04 ~/sysbench-1.0/src/lua]
27 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=5 --table-size=10000000 --mysql-storage-engine=innodb cleanup
28 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
29 
30 Dropping table 'sbtest1'...
31 Dropping table 'sbtest2'...
32 Dropping table 'sbtest3'...
33 Dropping table 'sbtest4'...
34 Dropping table 'sbtest5'...
35 
36 [root@zlm2 08:59:09 ~/sysbench-1.0/src/lua]
37 #df -h
38 Filesystem               Size  Used Avail Use% Mounted on
39 /dev/mapper/centos-root  8.4G  6.9G  1.5G  83% / //I'd got 27% free space.
40 devtmpfs                 488M     0  488M   0% /dev
41 tmpfs                    497M     0  497M   0% /dev/shm
42 tmpfs                    497M  6.6M  491M   2% /run
43 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
44 /dev/sda1                497M  118M  379M  24% /boot
45 none                      87G   80G  6.6G  93% /vagrant
46 
47 (zlm@192.168.1.101 3306)[(none)]>drop database sysbench;
48 Query OK, 1 row affected (0.04 sec)
49 
50 //Further more,I dropped the "sysbench".

 

1 root@localhost:mysql3306.sock [(none)]>alter user zlm@'192.168.56.%' identified by 'zlmzlm';
2 Query OK, 0 rows affected (0.00 sec)

 

Precedure

 

The
slave hung still and disk space was full.

 

**I
then started the slave replication on zlm3.**

 1 [root@zlm3 08:55:17 ~]
 2 #df -h
 3 Filesystem               Size  Used Avail Use% Mounted on
 4 /dev/mapper/centos-root  8.4G  8.4G   20K 100% /
 5 devtmpfs                 488M     0  488M   0% /dev
 6 tmpfs                    497M     0  497M   0% /dev/shm
 7 tmpfs                    497M  6.5M  491M   2% /run
 8 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
 9 /dev/sda1                497M  118M  379M  24% /boot
10 none                      87G   80G  6.6G  93% /vagrant
11 
12 (zlm@192.168.1.102 3306)[(none)]>show slave status\G
13 ^C^C -- query aborted
14 
15 
16 ^Z
17 [6]+  Stopped                 mysql
18 
19 [root@zlm3 08:55:58 ~]
20 #pkill mysqld
21 
22 [root@zlm3 08:56:04 ~]
23 #./mysqld.sh 
24 
25 [root@zlm3 08:56:07 ~]
26 #mysql
27 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.102' (111)
28 
29 [root@zlm3 09:00:35 ~]
30 #cd /data/mysql/mysql3306/data
31 
32 [root@zlm3 09:00:46 /data/mysql/mysql3306/data]
33 #cat error.log |tail -n 30
34 2018-07-19T08:57:02.581937+01:00 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 17039436409
35 2018-07-19T08:57:02.581958+01:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 17039436418
36 2018-07-19T08:57:02.581963+01:00 0 [Note] InnoDB: Database was not shutdown normally!
37 2018-07-19T08:57:02.581965+01:00 0 [Note] InnoDB: Starting crash recovery.
38 2018-07-19T08:57:02.696292+01:00 0 [Note] InnoDB: Transaction 31700139 was in the XA prepared state.
39 2018-07-19T08:57:02.700688+01:00 0 [Note] InnoDB: Transaction 31700139 was in the XA prepared state.
40 2018-07-19T08:57:02.700814+01:00 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 0 row operations to undo
41 2018-07-19T08:57:02.700821+01:00 0 [Note] InnoDB: Trx id counter is 31700480
42 2018-07-19T08:57:02.701719+01:00 0 [Note] InnoDB: Last MySQL binlog file position 0 99139927, file name mysql-bin.000016
43 2018-07-19T08:57:02.805965+01:00 0 [Note] InnoDB: Ignoring tablespace `zlm`.`sbtest2` because the DISCARD flag is set .
44 2018-07-19T08:57:02.806462+01:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
45 2018-07-19T08:57:02.807316+01:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
46 2018-07-19T08:57:02.807568+01:00 0 [Note] InnoDB: Starting in background the rollback of uncommitted transactions
47 2018-07-19T08:57:02.807594+01:00 0 [Note] InnoDB: Rollback of non-prepared transactions completed
48 2018-07-19T08:57:02.871396+01:00 0 [Warning] InnoDB: Retry attempts for writing partial data failed.
49 2018-07-19T08:57:02.871423+01:00 0 [ERROR] InnoDB: Write to file ./ibtmp1failed at offset 11534336, 1048576 bytes should have been written, only 0 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
50 2018-07-19T08:57:02.871441+01:00 0 [ERROR] InnoDB: Error number 28 means 'No space left on device'
51 2018-07-19T08:57:02.871446+01:00 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
52 2018-07-19T08:57:02.871451+01:00 0 [ERROR] InnoDB: Could not set the file size of './ibtmp1'. Probably out of disk space
53 2018-07-19T08:57:02.871456+01:00 0 [ERROR] InnoDB: Unable to create the shared innodb_temporary
54 2018-07-19T08:57:02.871459+01:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
55 2018-07-19T08:57:03.273011+01:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
56 2018-07-19T08:57:03.273029+01:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
57 2018-07-19T08:57:03.273033+01:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
58 2018-07-19T08:57:03.273037+01:00 0 [ERROR] Failed to initialize builtin plugins.
59 2018-07-19T08:57:03.273040+01:00 0 [ERROR] Aborting
60 
61 2018-07-19T08:57:03.273046+01:00 0 [Note] Binlog end
62 2018-07-19T08:57:03.273389+01:00 0 [Note] mysqld: Shutdown complete
63 
64 //The mysqld process could not run again because of no free disk space.

Step
1: Create binlog server.

 1 #mysql -uroot -p -S /var/lib/mysql/mysql.sock
 2 Enter password: 
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 3
 5 Server version: 5.7.21-log MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 root@localhost:mysql.sock [(none)]>start slave;
16 Query OK, 0 rows affected (0.00 sec)
17 
18 root@localhost:mysql.sock [(none)]>show slave status\G
19 *************************** 1. row ***************************
20                Slave_IO_State: 
21                   Master_Host: 192.168.56.100
22                   Master_User: repl
23                   Master_Port: 3306
24                 Connect_Retry: 60
25               Master_Log_File: mysql-bin.000047
26           Read_Master_Log_Pos: 194
27                Relay_Log_File: relay-bin.000027
28                 Relay_Log_Pos: 4
29         Relay_Master_Log_File: mysql-bin.000047
30              Slave_IO_Running: No //The IO_Thread is not running on account of Error 1236
31             Slave_SQL_Running: Yes
32               Replicate_Do_DB: 
33           Replicate_Ignore_DB: 
34            Replicate_Do_Table: 
35        Replicate_Ignore_Table: 
36       Replicate_Wild_Do_Table: 
37   Replicate_Wild_Ignore_Table: 
38                    Last_Errno: 0
39                    Last_Error: 
40                  Skip_Counter: 0
41           Exec_Master_Log_Pos: 194
42               Relay_Log_Space: 532
43               Until_Condition: None
44                Until_Log_File: 
45                 Until_Log_Pos: 0
46            Master_SSL_Allowed: No
47            Master_SSL_CA_File: 
48            Master_SSL_CA_Path: 
49               Master_SSL_Cert: 
50             Master_SSL_Cipher: 
51                Master_SSL_Key: 
52         Seconds_Behind_Master: NULL
53 Master_SSL_Verify_Server_Cert: No
54                 Last_IO_Errno: 1236
55                 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.'
56                Last_SQL_Errno: 0
57                Last_SQL_Error: 
58   Replicate_Ignore_Server_Ids: 
59              Master_Server_Id: 1003306
60                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
61              Master_Info_File: /data/mysql/mysql3306/data/master.info
62                     SQL_Delay: 0
63           SQL_Remaining_Delay: NULL
64       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
65            Master_Retry_Count: 86400
66                   Master_Bind: 
67       Last_IO_Error_Timestamp: 180721 09:05:50
68      Last_SQL_Error_Timestamp: 
69                Master_SSL_Crl: 
70            Master_SSL_Crlpath: 
71            Retrieved_Gtid_Set: 
72             Executed_Gtid_Set: 27af30ca-6800-11e8-ad7e-080027de0e0e:1-7,
73 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-2700058 //This is the id of maximum transaction executed on slave.
74                 Auto_Position: 1
75          Replicate_Rewrite_DB: 
76                  Channel_Name: 
77            Master_TLS_Version: 
78 1 row in set (0.00 sec)

 

 

 

**I
decided to drop all the binlogs on slave to release the disk
space.**

Check
the position on master 

**I
decided to set gtid_purged variable to a certain value.So I executed
“reset master;” to remove the value of
“Executed_Gtid_Set”.**

 1 [root@zlm3 09:01:34 /data/mysql/mysql3306]
 2 #cd logs
 3 
 4 [root@zlm3 09:01:35 /data/mysql/mysql3306/logs]
 5 #ls -l
 6 total 2691436
 7 -rw-r----- 1 mysql mysql   4333745 Jul 13 11:07 mysql-bin.000001
 8 -rw-r----- 1 mysql mysql       653 Jul 16 09:28 mysql-bin.000002
 9 -rw-r----- 1 mysql mysql 268435663 Jul 16 10:03 mysql-bin.000003
10 -rw-r----- 1 mysql mysql 268435798 Jul 16 10:22 mysql-bin.000004
11 -rw-r----- 1 mysql mysql 268435798 Jul 16 10:32 mysql-bin.000005
12 -rw-r----- 1 mysql mysql 268435798 Jul 16 10:48 mysql-bin.000006
13 -rw-r----- 1 mysql mysql 268435798 Jul 16 11:29 mysql-bin.000007
14 -rw-r----- 1 mysql mysql  35214498 Jul 16 11:59 mysql-bin.000008
15 -rw-r----- 1 mysql mysql       249 Jul 16 11:59 mysql-bin.000009
16 -rw-r----- 1 mysql mysql 199908536 Jul 18 11:27 mysql-bin.000010
17 -rw-r----- 1 mysql mysql       249 Jul 18 11:27 mysql-bin.000011
18 -rw-r----- 1 mysql mysql 268474318 Jul 19 04:20 mysql-bin.000012
19 -rw-r----- 1 mysql mysql 268817026 Jul 19 04:24 mysql-bin.000013
20 -rw-r----- 1 mysql mysql 268802986 Jul 19 04:28 mysql-bin.000014
21 -rw-r----- 1 mysql mysql 268826152 Jul 19 04:32 mysql-bin.000015
22 -rw-r----- 1 mysql mysql  99422208 Jul 19 08:56 mysql-bin.000016
23 -rw-r----- 1 mysql mysql       704 Jul 19 04:32 mysql-bin.index
24 
25 [root@zlm3 09:01:36 /data/mysql/mysql3306/logs]
26 #rm -f *
27 
28 [root@zlm3 09:01:38 /data/mysql/mysql3306/logs]
29 #ls -l
30 total 0
31 
32 [root@zlm3 09:01:48 ~]
33 #df -h
34 Filesystem               Size  Used Avail Use% Mounted on
35 /dev/mapper/centos-root  8.4G  4.5G  4.0G  53% / //The free disk space had been reduced to 47%.
36 devtmpfs                 488M     0  488M   0% /dev
37 tmpfs                    497M     0  497M   0% /dev/shm
38 tmpfs                    497M  6.5M  491M   2% /run
39 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
40 /dev/sda1                497M  118M  379M  24% /boot
41 none                      87G   80G  6.6G  93% /vagrant
1 zlm@192.168.56.100:3306 [sysbench]>show master status;
2 +------------------+----------+--------------+------------------+-------------------------------------------------+
3 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
4 +------------------+----------+--------------+------------------+-------------------------------------------------+
5 | mysql-bin.000098 |      363 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
6 +------------------+----------+--------------+------------------+-------------------------------------------------+
7 1 row in set (0.00 sec)
 1 root@localhost:mysql.sock [(none)]>reset master;
 2 Query OK, 0 rows affected (0.05 sec)
 3 
 4 root@localhost:mysql.sock [(none)]>show slave status\G
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: 192.168.56.100
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000047
12           Read_Master_Log_Pos: 194
13                Relay_Log_File: relay-bin.000027
14                 Relay_Log_Pos: 4
15         Relay_Master_Log_File: mysql-bin.000047
16              Slave_IO_Running: No
17             Slave_SQL_Running: Yes
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 194
28               Relay_Log_Space: 532
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 1236
41                 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.'
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1003306
46                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
47              Master_Info_File: /data/mysql/mysql3306/data/master.info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 180721 09:11:00
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: //The value has been cleared after operation of "reset master;".
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

 

 

 

**Ran the
mysqld again and dropped the database “sysbench” on slave.**

**Make
binlog server begin to receive binlog.**

**In
order to know which value of “gtid_purged” I should set,I analyzed the
binlog on master.**

 1 [root@zlm3 09:01:42 /data/mysql/mysql3306/logs]
 2 #sh /root/mysqld.sh
 3 
 4 [root@zlm3 09:01:51 /data/mysql/mysql3306/logs]
 5 #ps aux|grep mysqld
 6 mysql     4346  7.0 17.8 1109984 181576 pts/0  Sl   09:01   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
 7 root      4380  0.0  0.0 112640   960 pts/0    R+   09:01   0:00 grep --color=auto mysqld
 8 
 9 [root@zlm3 09:01:55 /data/mysql/mysql3306/logs]
10 #mysql
11 Welcome to the MySQL monitor.  Commands end with ; or \g.
12 Your MySQL connection id is 2
13 Server version: 5.7.21-log MySQL Community Server (GPL)
14 
15 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
16 
17 Oracle is a registered trademark of Oracle Corporation and/or its
18 affiliates. Other names may be trademarks of their respective
19 owners.
20 
21 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
22 
23 (zlm@192.168.1.102 3306)[(none)]>drop database sysbench;
24 Query OK, 6 rows affected (0.11 sec)
 1 [root@zlm3 16:25:01 /data]
 2 #mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098 &
 3 [1] 4375
 4 mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
 5 
 6 [root@zlm3 16:26:24 /data]
 7 #ls -l
 8 total 4
 9 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
10 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
11 -rw-r----- 1 root  root  363 Jul 29 16:26 mysql-bin.000098
 1 zlm@192.168.56.100:3306 [(none)]>show master status;
 2 +------------------+----------+--------------+------------------+-------------------------------------------------+
 3 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 4 +------------------+----------+--------------+------------------+-------------------------------------------------+
 5 | mysql-bin.000090 |      440 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715627 |
 6 +------------------+----------+--------------+------------------+-------------------------------------------------+
 7 1 row in set (0.00 sec)
 8 
 9 zlm@192.168.56.100:3306 [(none)]>flush logs;
10 Query OK, 0 rows affected (0.04 sec)
11 
12 zlm@192.168.56.100:3306 [(none)]>show master status;
13 +------------------+----------+--------------+------------------+-------------------------------------------------+
14 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
15 +------------------+----------+--------------+------------------+-------------------------------------------------+
16 | mysql-bin.000091 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715627 |
17 +------------------+----------+--------------+------------------+-------------------------------------------------+
18 1 row in set (0.00 sec)
19 
20 [root@zlm1 09:16:13 /data/mysql/mysql3306/logs]
21 #mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000090 > /root/1.log
22 [root@zlm1 09:16:36 /data/mysql/mysql3306/logs]
23 #cd
24 
25 [root@zlm1 09:16:37 ~]
26 #ls -l|grep 1.log
27 -rw-r--r--   1 root root    1743 Jul 21 09:16 1.log
28 [root@zlm1 09:17:23 ~]
29 #less 1.log
30 ...
31 
32 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
33 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
34 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
35 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
36 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
37 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
38 DELIMITER /*!*/;
39 # at 4
40 #180721  9:01:29 server id 1003306  end_log_pos 123 CRC32 0x6e71f8f2    Start: binlog v 4, server v 5.7.21-log created 180721  9:01:29 at startup
41 ROLLBACK/*!*/;
42 # at 123
43 #180721  9:01:33 server id 1003306  end_log_pos 194 CRC32 0x074cfb08    Previous-GTIDs
44 # 2a4b3562-2ab6-11e8-be7a-080027de0e0e:5-12715626
45 # at 194
46 #180721  9:02:53 server id 1003306  end_log_pos 259 CRC32 0x9125f364    GTID    last_committed=0        sequence_number=1       rbr_only=no
47 SET @@SESSION.GTID_NEXT= '2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715627'/*!*/;
48 # at 259
49 #180721  9:02:53 server id 1003306  end_log_pos 440 CRC32 0x7a5353bf    Query   thread_id=3     exec_time=0     error_code=0
50 SET TIMESTAMP=1532156573/*!*/;
51 SET @@session.pseudo_thread_id=3/*!*/;
52 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
53 SET @@session.sql_mode=1436549152/*!*/;
54 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
55 /*!\C utf8 *//*!*/;
56 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
57 SET @@session.lc_time_names=0/*!*/;
58 SET @@session.collation_database=DEFAULT/*!*/;
59 ALTER USER 'zlm'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*512FB3FAA6F522E351929CAC70AD8EBB0F5901B6'
60 /*!*/;
61 # at 440
62 #180721  9:11:46 server id 1003306  end_log_pos 487 CRC32 0x0882ea24    Rotate to mysql-bin.000091  pos: 4
63 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
64 DELIMITER ;
65 # End of log file
66 
67 //I've known that the operation of altering password is in the uuid:12715627.Therefore,I could set gtid_purged to the previous gtid(uuid:5-12715626).

 

 

 

Started the
replication threads of 
slave.**

**Flush
two logs on master.**

*I
tried to set “gtid_purged” to the right value according to the content
of binlog on master.*

 1 (zlm@192.168.1.102 3306)[(none)]>start slave;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[(none)]>show slave status\G
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000019
12           Read_Master_Log_Pos: 155765801
13                Relay_Log_File: relay-bin.000068
14                 Relay_Log_Pos: 398
15         Relay_Master_Log_File: mysql-bin.000019
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 1146
25                    Last_Error: Error executing row event: 'Table 'sysbench.sbtest1' doesn't exist'
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 99148246
28               Relay_Log_Space: 56618146
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 1146
43                Last_SQL_Error: Error executing row event: 'Table 'sysbench.sbtest1' doesn't exist' //Since the database had been droppted.This error was notable.
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 180719 09:02:07
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3714549-3730021 //It was stuck on transaction 3714549(which contained error).
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3714548,
59 5c77c31b-4add-11e8-81e2-080027de0e0e:1-168
60                 Auto_Position: 1
61          Replicate_Rewrite_DB: 
62                  Channel_Name: 
63            Master_TLS_Version: 
64 1 row in set (0.00 sec)
65 
66 [root@zlm3 09:10:42 ~]
67 #perror 1146
68 MySQL error code 1146 (ER_NO_SUCH_TABLE): Table '%-.192s.%-.192s' doesn't exist
69 
70 //Error 1146 indicated the absence of table "sbtest1" in "sysbench" database.
71 //Obviously,the slave was replaying the operations relevant to this table on master.The table even the database had been dropped.
72 //How could I do next step?Do I have to generate a new mysqldump file and reconfigure the slave again?
73 //There's One thing I'm rather sure that there were no other transactions generated in the whole course except the operations on "sysbench" database.
74 //Since I'd drop "sysbentch" database on both master and slave.Maybe I can fix the issue easily.
 1 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 2 Query OK, 0 rows affected (0.06 sec)
 3 
 4 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 5 Query OK, 0 rows affected (0.01 sec)
 6 
 7 zlm@192.168.56.100:3306 [sysbench]>show master status;
 8 +------------------+----------+--------------+------------------+-------------------------------------------------+
 9 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
10 +------------------+----------+--------------+------------------+-------------------------------------------------+
11 | mysql-bin.000100 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
12 +------------------+----------+--------------+------------------+-------------------------------------------------+
13 1 row in set (0.00 sec)
  1 root@localhost:mysql.sock [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:5-12715626';
  2 Query OK, 0 rows affected (0.00 sec)
  3 
  4 root@localhost:mysql.sock [(none)]>start slave io_thread;
  5 Query OK, 0 rows affected (0.01 sec)
  6 
  7 root@localhost:mysql.sock [(none)]>show slave status\G
  8 *************************** 1. row ***************************
  9                Slave_IO_State: 
 10                   Master_Host: 192.168.56.100
 11                   Master_User: repl
 12                   Master_Port: 3306
 13                 Connect_Retry: 60
 14               Master_Log_File: mysql-bin.000047
 15           Read_Master_Log_Pos: 194
 16                Relay_Log_File: relay-bin.000027
 17                 Relay_Log_Pos: 4
 18         Relay_Master_Log_File: mysql-bin.000047
 19              Slave_IO_Running: No //It was not running again.
 20             Slave_SQL_Running: Yes
 21               Replicate_Do_DB: 
 22           Replicate_Ignore_DB: 
 23            Replicate_Do_Table: 
 24        Replicate_Ignore_Table: 
 25       Replicate_Wild_Do_Table: 
 26   Replicate_Wild_Ignore_Table: 
 27                    Last_Errno: 0
 28                    Last_Error: 
 29                  Skip_Counter: 0
 30           Exec_Master_Log_Pos: 194
 31               Relay_Log_Space: 532
 32               Until_Condition: None
 33                Until_Log_File: 
 34                 Until_Log_Pos: 0
 35            Master_SSL_Allowed: No
 36            Master_SSL_CA_File: 
 37            Master_SSL_CA_Path: 
 38               Master_SSL_Cert: 
 39             Master_SSL_Cipher: 
 40                Master_SSL_Key: 
 41         Seconds_Behind_Master: NULL
 42 Master_SSL_Verify_Server_Cert: No
 43                 Last_IO_Errno: 1236
 44                 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.'
 45                Last_SQL_Errno: 0
 46                Last_SQL_Error: 
 47   Replicate_Ignore_Server_Ids: 
 48              Master_Server_Id: 1003306
 49                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
 50              Master_Info_File: /data/mysql/mysql3306/data/master.info
 51                     SQL_Delay: 0
 52           SQL_Remaining_Delay: NULL
 53       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
 54            Master_Retry_Count: 86400
 55                   Master_Bind: 
 56       Last_IO_Error_Timestamp: 180721 09:20:25
 57      Last_SQL_Error_Timestamp: 
 58                Master_SSL_Crl: 
 59            Master_SSL_Crlpath: 
 60            Retrieved_Gtid_Set: 
 61             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:5-12715626
 62                 Auto_Position: 1
 63          Replicate_Rewrite_DB: 
 64                  Channel_Name: 
 65            Master_TLS_Version: 
 66 1 row in set (0.00 sec)
 67 
 68 root@localhost:mysql.sock [(none)]>reset master;
 69 Query OK, 0 rows affected (0.03 sec)
 70 
 71 root@localhost:mysql.sock [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715626'; //I began with the first gtid this time.
 72 Query OK, 0 rows affected (0.00 sec)
 73 
 74 root@localhost:mysql.sock [(none)]>stop slave;
 75 Query OK, 0 rows affected (0.00 sec)
 76 
 77 root@localhost:mysql.sock [(none)]>start slave;
 78 Query OK, 0 rows affected (0.00 sec)
 79 
 80 root@localhost:mysql.sock [(none)]>show slave status\G
 81 *************************** 1. row ***************************
 82                Slave_IO_State: Waiting for master to send event
 83                   Master_Host: 192.168.56.100
 84                   Master_User: repl
 85                   Master_Port: 3306
 86                 Connect_Retry: 60
 87               Master_Log_File: mysql-bin.000091
 88           Read_Master_Log_Pos: 194
 89                Relay_Log_File: relay-bin.000028
 90                 Relay_Log_Pos: 367
 91         Relay_Master_Log_File: mysql-bin.000090
 92              Slave_IO_Running: Yes //IO_Thread could work normally.
 93             Slave_SQL_Running: No //SQL_Thread was down instead.
 94               Replicate_Do_DB: 
 95           Replicate_Ignore_DB: 
 96            Replicate_Do_Table: 
 97        Replicate_Ignore_Table: 
 98       Replicate_Wild_Do_Table: 
 99   Replicate_Wild_Ignore_Table: 
100                    Last_Errno: 1396 //A new arrival error 1396 occurred.
101                    Last_Error: Error 'Operation ALTER USER failed for 'zlm'@'192.168.56.%'' on query. Default database: ''. Query: 'ALTER USER 'zlm'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*512FB3FAA6F522E351929CAC70AD8EBB0F5901B6''
102                  Skip_Counter: 0
103           Exec_Master_Log_Pos: 194
104               Relay_Log_Space: 1556
105               Until_Condition: None
106                Until_Log_File: 
107                 Until_Log_Pos: 0
108            Master_SSL_Allowed: No
109            Master_SSL_CA_File: 
110            Master_SSL_CA_Path: 
111               Master_SSL_Cert: 
112             Master_SSL_Cipher: 
113                Master_SSL_Key: 
114         Seconds_Behind_Master: NULL
115 Master_SSL_Verify_Server_Cert: No
116                 Last_IO_Errno: 0
117                 Last_IO_Error: 
118                Last_SQL_Errno: 1396
119                Last_SQL_Error: Error 'Operation ALTER USER failed for 'zlm'@'192.168.56.%'' on query. Default database: ''. Query: 'ALTER USER 'zlm'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*512FB3FAA6F522E351929CAC70AD8EBB0F5901B6''
120   Replicate_Ignore_Server_Ids: 
121              Master_Server_Id: 1003306
122                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
123              Master_Info_File: /data/mysql/mysql3306/data/master.info
124                     SQL_Delay: 0
125           SQL_Remaining_Delay: NULL
126       Slave_SQL_Running_State: 
127            Master_Retry_Count: 86400
128                   Master_Bind: 
129       Last_IO_Error_Timestamp: 
130      Last_SQL_Error_Timestamp: 180721 09:22:48
131                Master_SSL_Crl: 
132            Master_SSL_Crlpath: 
133            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715627
134             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715626
135                 Auto_Position: 1
136          Replicate_Rewrite_DB: 
137                  Channel_Name: 
138            Master_TLS_Version: 
139 1 row in set (0.00 sec)
140 
141 root@localhost:mysql.sock [(none)]>ALTER USER 'zlm'@'192.168.56.%' IDENTIFIED WITH 'mysql_native_password' AS '*512FB3FAA6F522E351929CAC70AD8EBB0F5901B6'; //I couldn't executed it in mysql cli either.
142 ERROR 1396 (HY000): Operation ALTER USER failed for 'zlm'@'192.168.56.%'
143 root@localhost:mysql.sock [(none)]>

 

 

 

Checked the
Executed_Gtid_Set on master.

**Check
whether the newly generated binlogs are successfully transmited to
binlog server.**

*Then,I
checked the user on both master and slave.I found that there was no
“zlm” user on slave at all.*

1 (zlm@192.168.1.101 3306)[(none)]>show master status;
2 +------------------+-----------+--------------+------------------+------------------------------------------------+
3 | File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
4 +------------------+-----------+--------------+------------------+------------------------------------------------+
5 | mysql-bin.000019 | 155765801 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021 |
6 +------------------+-----------+--------------+------------------+------------------------------------------------+
7 1 row in set (0.00 sec)
8 
9 //The executed gtid was upto "3730021".
1 [root@zlm3 16:26:27 /data]
2 #ls -l
3 total 12
4 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
5 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
6 -rw-r----- 1 root  root  410 Jul 29 16:27 mysql-bin.000098
7 -rw-r----- 1 root  root  241 Jul 29 16:27 mysql-bin.000099
8 -rw-r----- 1 root  root  194 Jul 29 16:27 mysql-bin.000100
 1 //Master
 2 root@localhost:mysql3306.sock [(none)]>select user,host from mysql.user;
 3 +---------------+--------------+
 4 | user          | host         |
 5 +---------------+--------------+
 6 | repl          | 192.168.56.% |
 7 | zlm           | 192.168.56.% |
 8 | bkuser        | localhost    |
 9 | mysql.session | localhost    |
10 | mysql.sys     | localhost    |
11 | root          | localhost    |
12 +---------------+--------------+
13 6 rows in set (0.00 sec)
14 
15 //Slave
16 root@localhost:mysql.sock [(none)]>select user,host from mysql.user;
17 +---------------+--------------+
18 | user          | host         |
19 +---------------+--------------+
20 | repl          | 192.168.56.% |
21 | bkuser        | localhost    |
22 | mysql.session | localhost    |
23 | mysql.sys     | localhost    |
24 | root          | localhost    |
25 +---------------+--------------+
26 5 rows in set (0.00 sec)
27 
28 root@localhost:mysql.sock [(none)]>create user zlm@'192.168.56.%'; //I created the absent user "zlm" on slave.
29 Query OK, 0 rows affected (0.00 sec)
30 
31 root@localhost:mysql.sock [(none)]>start slave sql_thread;
32 Query OK, 0 rows affected (0.01 sec)
33 
34 root@localhost:mysql.sock [(none)]>show slave status\G
35 *************************** 1. row ***************************
36                Slave_IO_State: Waiting for master to send event
37                   Master_Host: 192.168.56.100
38                   Master_User: repl
39                   Master_Port: 3306
40                 Connect_Retry: 60
41               Master_Log_File: mysql-bin.000091
42           Read_Master_Log_Pos: 194
43                Relay_Log_File: relay-bin.000030
44                 Relay_Log_Pos: 407
45         Relay_Master_Log_File: mysql-bin.000091
46              Slave_IO_Running: Yes
47             Slave_SQL_Running: Yes //It was ok.
48               Replicate_Do_DB: 
49           Replicate_Ignore_DB: 
50            Replicate_Do_Table: 
51        Replicate_Ignore_Table: 
52       Replicate_Wild_Do_Table: 
53   Replicate_Wild_Ignore_Table: 
54                    Last_Errno: 0
55                    Last_Error: 
56                  Skip_Counter: 0
57           Exec_Master_Log_Pos: 194
58               Relay_Log_Space: 695
59               Until_Condition: None
60                Until_Log_File: 
61                 Until_Log_Pos: 0
62            Master_SSL_Allowed: No
63            Master_SSL_CA_File: 
64            Master_SSL_CA_Path: 
65               Master_SSL_Cert: 
66             Master_SSL_Cipher: 
67                Master_SSL_Key: 
68         Seconds_Behind_Master: 0
69 Master_SSL_Verify_Server_Cert: No
70                 Last_IO_Errno: 0
71                 Last_IO_Error: 
72                Last_SQL_Errno: 0
73                Last_SQL_Error: 
74   Replicate_Ignore_Server_Ids: 
75              Master_Server_Id: 1003306
76                   Master_UUID: 2a4b3562-2ab6-11e8-be7a-080027de0e0e
77              Master_Info_File: /data/mysql/mysql3306/data/master.info
78                     SQL_Delay: 0
79           SQL_Remaining_Delay: NULL
80       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
81            Master_Retry_Count: 86400
82                   Master_Bind: 
83       Last_IO_Error_Timestamp: 
84      Last_SQL_Error_Timestamp: 
85                Master_SSL_Crl: 
86            Master_SSL_Crlpath: 
87            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715627
88             Executed_Gtid_Set: 27af30ca-6800-11e8-ad7e-080027de0e0e:1, //This gtid was generated by the statment of "create user ... ".
89 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715627 //The executed gtid was equal with the master.
90                 Auto_Position: 1
91          Replicate_Rewrite_DB: 
92                  Channel_Name: 
93            Master_TLS_Version: 
94 1 row in set (0.00 sec)

 

 

 

Tryed
to fix the replica of master.

Step
2: Destroy the table.

Summary

  1 (zlm@192.168.1.102 3306)[(none)]>show slave status\G
  2 *************************** 1. row ***************************
  3                Slave_IO_State: Waiting for master to send event
  4                   Master_Host: 192.168.1.101
  5                   Master_User: repl
  6                   Master_Port: 3306
  7                 Connect_Retry: 60
  8               Master_Log_File: mysql-bin.000019
  9           Read_Master_Log_Pos: 155765801
 10                Relay_Log_File: relay-bin.000068
 11                 Relay_Log_Pos: 398
 12         Relay_Master_Log_File: mysql-bin.000019
 13              Slave_IO_Running: Yes
 14             Slave_SQL_Running: No
 15               Replicate_Do_DB: 
 16           Replicate_Ignore_DB: 
 17            Replicate_Do_Table: 
 18        Replicate_Ignore_Table: 
 19       Replicate_Wild_Do_Table: 
 20   Replicate_Wild_Ignore_Table: 
 21                    Last_Errno: 1146
 22                    Last_Error: Error executing row event: 'Table 'sysbench.sbtest1' doesn't exist'
 23                  Skip_Counter: 0
 24           Exec_Master_Log_Pos: 99148246
 25               Relay_Log_Space: 56618627
 26               Until_Condition: None
 27                Until_Log_File: 
 28                 Until_Log_Pos: 0
 29            Master_SSL_Allowed: No
 30            Master_SSL_CA_File: 
 31            Master_SSL_CA_Path: 
 32               Master_SSL_Cert: 
 33             Master_SSL_Cipher: 
 34                Master_SSL_Key: 
 35         Seconds_Behind_Master: NULL
 36 Master_SSL_Verify_Server_Cert: No
 37                 Last_IO_Errno: 0
 38                 Last_IO_Error: 
 39                Last_SQL_Errno: 1146
 40                Last_SQL_Error: Error executing row event: 'Table 'sysbench.sbtest1' doesn't exist'
 41   Replicate_Ignore_Server_Ids: 
 42              Master_Server_Id: 1013306
 43                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
 44              Master_Info_File: mysql.slave_master_info
 45                     SQL_Delay: 0
 46           SQL_Remaining_Delay: NULL
 47       Slave_SQL_Running_State: 
 48            Master_Retry_Count: 86400
 49                   Master_Bind: 
 50       Last_IO_Error_Timestamp: 
 51      Last_SQL_Error_Timestamp: 180719 09:02:57
 52                Master_SSL_Crl: 
 53            Master_SSL_Crlpath: 
 54            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3714549-3730021
 55             Executed_Gtid_Set: 
 56                 Auto_Position: 1
 57          Replicate_Rewrite_DB: 
 58                  Channel_Name: 
 59            Master_TLS_Version: 
 60 1 row in set (0.00 sec)
 61 
 62 (zlm@192.168.1.102 3306)[(none)]>reset master;
 63 Query OK, 0 rows affected (0.02 sec)
 64 
 65 (zlm@192.168.1.102 3306)[(none)]>set @@global.gtid_purged='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021';
 66 Query OK, 0 rows affected (0.00 sec)
 67 
 68 //On account of surely knowing there were no other transactions at all.I set the "gtid_purged" variable to the value of "gtid_executed" on master.
 69 //It means I guised that all the transactions generated on master had been replayed on slave already.The slave could retrieve new GTID at the moment.
 70 
 71 (zlm@192.168.1.102 3306)[(none)]>start slave sql_thread;
 72 Query OK, 0 rows affected (0.02 sec)
 73 
 74 (zlm@192.168.1.102 3306)[(none)]>show slave status\G
 75 *************************** 1. row ***************************
 76                Slave_IO_State: Waiting for master to send event
 77                   Master_Host: 192.168.1.101
 78                   Master_User: repl
 79                   Master_Port: 3306
 80                 Connect_Retry: 60
 81               Master_Log_File: mysql-bin.000019
 82           Read_Master_Log_Pos: 155765801
 83                Relay_Log_File: relay-bin.000069
 84                 Relay_Log_Pos: 438
 85         Relay_Master_Log_File: mysql-bin.000019
 86              Slave_IO_Running: Yes
 87             Slave_SQL_Running: Yes //The sql_thread became "Yes".
 88               Replicate_Do_DB: 
 89           Replicate_Ignore_DB: 
 90            Replicate_Do_Table: 
 91        Replicate_Ignore_Table: 
 92       Replicate_Wild_Do_Table: 
 93   Replicate_Wild_Ignore_Table: 
 94                    Last_Errno: 0
 95                    Last_Error: 
 96                  Skip_Counter: 0
 97           Exec_Master_Log_Pos: 155765801
 98               Relay_Log_Space: 56618434
 99               Until_Condition: None
100                Until_Log_File: 
101                 Until_Log_Pos: 0
102            Master_SSL_Allowed: No
103            Master_SSL_CA_File: 
104            Master_SSL_CA_Path: 
105               Master_SSL_Cert: 
106             Master_SSL_Cipher: 
107                Master_SSL_Key: 
108         Seconds_Behind_Master: 0
109 Master_SSL_Verify_Server_Cert: No
110                 Last_IO_Errno: 0
111                 Last_IO_Error: 
112                Last_SQL_Errno: 0
113                Last_SQL_Error: 
114   Replicate_Ignore_Server_Ids: 
115              Master_Server_Id: 1013306
116                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
117              Master_Info_File: mysql.slave_master_info
118                     SQL_Delay: 0
119           SQL_Remaining_Delay: NULL
120       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
121            Master_Retry_Count: 86400
122                   Master_Bind: 
123       Last_IO_Error_Timestamp: 
124      Last_SQL_Error_Timestamp: 
125                Master_SSL_Crl: 
126            Master_SSL_Crlpath: 
127            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3714549-3730021
128             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730021 //The slave had skipped those GTID(which contained error 1146) of master and waited for newer GTID.The replica had been fixed up.
129                 Auto_Position: 1
130          Replicate_Rewrite_DB: 
131                  Channel_Name: 
132            Master_TLS_Version: 
133 1 row in set (0.00 sec)

 

  • Error
    1236 is common phenomenon in our replication of MySQL which always
    due to lack of specific binlog files.
  • Before
    you execute a statement on master.You’d better start your slave
    replication at first.Obviously it means our test
    environment.
  • If you
    were not sure which transaction was stuck.Analyze the binlog file is
    always a good idea to solve these kind of issues.
  • In my
    case,I know clearly about what I’ve operated in master.So I can
    easily execute the same operation on slave.But the product
    environment is complecated and it’s not recommended to do
    so.

 

Check
target table on master.

 

 Summary

 1 zlm@192.168.56.100:3306 [sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 | sbtest5            |
10 | sbtest6            |
11 +--------------------+
12 6 rows in set (0.00 sec)
13 
14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest6;
15 +----------+
16 | count(*) |
17 +----------+
18 |        0 |
19 +----------+
20 1 row in set (0.00 sec)
21 
22 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(1,1,'a','b');
23 Query OK, 1 row affected (0.00 sec)
24 
25 zlm@192.168.56.100:3306 [sysbench]>select * from sbtest6;
26 +----+---+---+-----+
27 | id | k | c | pad |
28 +----+---+---+-----+
29 |  1 | 1 | a | b   |
30 +----+---+---+-----+
31 1 row in set (0.00 sec)
  • The
    variable “gtid_purged” cannot be set if “gtid_executed” is not
    empty.
  • Caution,”reset
    master” can only be used on slave.Keep in mind that don’t do it on
    master anytime.
  • This
    case can be followed only in test environment ’cause you cannot
    guarantee whether all the transactions are really replayed on
    slave.

 

 

Generate
Xtrabackup backup set.

 1 [root@zlm1 16:32:14 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd /data/backup
 3 180729 16:32:20 innobackupex: Error: extra argument found -pPassw0rd
 4 180729 16:32:20 innobackupex: Error: extra argument found /data/backup
 5 
 6 [root@zlm1 16:32:20 ~]
 7 #innobackupex -v
 8 innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)
 9 
10 [root@zlm1 16:32:26 ~]
11 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
12 180729 16:32:33 innobackupex: Starting the backup operation
13 ...
14 
15 180729 16:32:53 Backup created in directory '/data/backup/2018-07-29_16-32-33'
16 MySQL binlog position: filename 'mysql-bin.000100', position '476', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694'
17 180729 16:32:53 [00] Writing backup-my.cnf
18 180729 16:32:53 [00]        ...done
19 180729 16:32:53 [00] Writing xtrabackup_info
20 180729 16:32:53 [00]        ...done
21 xtrabackup: Transaction log of lsn (1719676169) to (1719676178) was copied.
22 180729 16:32:53 completed OK!