MySql ocp认证之主从复制(三)

搭建从节点

创建主从同步用户

在主节点上创建同步用户，并授予复制权限

mysql> create user 'mysql-slave'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

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

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8

修改配置文件

主节点：
[mysqld]
server_id=1
log_bin=/usr/local/mysql/mysql-bin
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=1

从节点：
[mysqld]
server_id=2
log_bin=/usr/local/mysql-slave/mysql-bin
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=1

第一个参数表示在主库上给每一个事务一个id

重启数据库

从库开启主从复制

在从库上执行：
reset slave;

change master to 
master_host='localhost',
master_user='mysql-slave',
master_password='123456',
master_port=3306,
master_auto_position=1;
1
2
3
4
5
6

另一种方式：

change master to 
master_host='localhost',
master_user='mysql-slave',
master_password='123456',
master_port=3306,
master_auto_position=0,
master_log_file = 'mysql-bin.000003', 
master_log_pos=6580;
1
2
3
4
5
6
7
8

开启slave的同步复制：
start slave;
查看同步状态：
show slave status \G;
如下，显示Slave_IO_Running进程和Slave_SQL_Running两个进程启动，且Slave_IO_State为等待master发送事件时即为成功。

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: mysql-slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: VM-24-5-centos-relay-bin.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            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: 154
              Relay_Log_Space: 583
              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: 1
                  Master_UUID: af8af064-26d1-11ed-8838-5254009245d5
             Master_Info_File: /usr/local/mysql-slave/data/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: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 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

主从复制问题解决

问题：

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: localhost
                  Master_User: mysql-slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: VM-24-5-centos-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: 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: 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. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is 'f823971e-2749-11ed-9559-5254009245d5:1-4', and the missing transactions are 'af8af064-26d1-11ed-8838-5254009245d5:1-3'.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: af8af064-26d1-11ed-8838-5254009245d5
             Master_Info_File: /usr/local/mysql-slave/data/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: 220829 11:28:12
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: f823971e-2749-11ed-9559-5254009245d5:1-4
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 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

解决方法：
主节点执行： reset master;
从节点执行：start slave;

问题二：
从节点执行了变更，需要跳过某个gtid：

STOP SLAVE;
SET GTID_NEXT='aaa-bbb-ccc-ddd-eee:3';
BEGIN; 
COMMIT;
SET GTID_NEXT='AUTOMATIC' ;
START SLAVE;
1
2
3
4
5
6