环境说明

主机: Centos6.5
MySQL: 5.7.18
架构: 一主一从

为了节省机器，直接在多实例基础上搭建
Master: 
    IP: localhsot 
    port: 3306 

Slave:
    IP: localhsot 
    port: 3307 

原理图

image-20230427161645678

相关配置参数(my.cnf)

master:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
server_id=3306
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=table 
relay-log-info-repository=table 
log_slave_updates
log_bin=mysql-bin
binlog_format=row
binlog_row_image = full
expire_logs_days=0
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

slave:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
server_id=3307
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=table 
relay-log-info-repository=table 
log_slave_updates
log_bin=mysql-bin
binlog_format=row
binlog_row_image = full
expire_logs_days=0
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

重启服务

1
mysqld_multi relaod (单实例使用:service mysqld restart)

创建复制账号

在master上，

1
2
3
4
5
mysql> create user rpl@'%' identified by 'rpl';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to rpl@'%' ;
Query OK, 0 rows affected (0.01 sec)

将slave指向master

在slave上，

1
2
3
4
5
6
7
mysql>change master to \
 master_host='localhost',\ 
 master_port=3306, \
 master_user='rpl', \
 master_password='rpl', \
 master_auto_position=1 ;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

warning可以忽略

1
2
3
4
5
6
7
8
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

启动slave

在slave上，

1
2
mysql> start slave ;
Query OK, 0 rows affected (0.03 sec)

查看状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 6188
               Relay_Log_File: mysql-relay-bin.000007
                Relay_Log_Pos: 6401
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes  #yes说明已经同步
            Slave_SQL_Running: Yes  #yes说明已经同步
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql,information_schema,performance_schema
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 6188
              Relay_Log_Space: 6861
              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: 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: 3306
                  Master_UUID: d54fe35a-2a7c-11e7-b24b-000c29217b03
             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: d54fe35a-2a7c-11e7-b24b-000c29217b03:1-24
            Executed_Gtid_Set: d54fe35a-2a7c-11e7-b24b-000c29217b03:1-24,
f0c7d668-2a7c-11e7-b3c3-000c29217b03:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

相关问题：

1.创建账号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> grant replication slave on *.* to rpl@'%' identified by 'rpl';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> 
mysql> 
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


提示 grant 方式创建账号已经不推荐了，所以我们上面没用这种方式创建。

2.Slave误提交

情景：误操作，在slave上进行了删除操作，使得复制不同步，报错了。

在slave上查看状态，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 6977
               Relay_Log_File: mysql-relay-bin.000007
                Relay_Log_Pos: 6664
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql,information_schema,performance_schema
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
                   Last_Errno: 1032
                   Last_Error: Could not execute Delete_rows event on table t100.t4; Can't find record in 't4', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000006, end_log_pos 6683
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 6451
              Relay_Log_Space: 7650
              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: 1032
               Last_SQL_Error: Could not execute Delete_rows event on table t100.t4; Can't find record in 't4', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000006, end_log_pos 6683
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3306
                  Master_UUID: d54fe35a-2a7c-11e7-b24b-000c29217b03
             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: 170427 03:00:31
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: d54fe35a-2a7c-11e7-b24b-000c29217b03:1-27 #接收到的来自master的列表
            Executed_Gtid_Set: d54fe35a-2a7c-11e7-b24b-000c29217b03:1-25,
f0c7d668-2a7c-11e7-b3c3-000c29217b03:1-4 #已经执行的列表
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.03 sec)

从Retrieved_Gtid_Set 和 Executed_Gtid_Set 可以发现，slave执行到 sequence_number=25 的位置，在下一个位置报错了，也就是 sequence_number=26 没有执行。

我们可以使用 gtid_purged 逃过这个事务。

1
2
3
4
5
6
7
mysql> stop slave;
mysql> reset master;
mysql> set @@global.gtid_purged="d54fe35a-2a7c-11e7-b24b-000c29217b03:1-26"; 
Query OK, 0 rows affected (0.00 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.06 sec)

问题解决，具体原因可以查看中继日志和binlog。