mysql使用gtid主从复制

本文主要是介绍mysql使用gtid主从复制，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

环境：

master：192.168.56.201

slave：192.168.56.211

1、配置主从节点的服务配置文件

1.1、配置master节点：

   [mysqld]
binlog-format=ROW
server_id =1
log_slave_updates=1
#binlog-do-db = brent
#binlog-ignore-db = mysql
#replicate_ignore_db=mysql
#replicate_do_table=brent.t1
#replicate_ignore_table=brent.t2
#######GTID#######
gtid-mode = on 
enforce-gtid-consistency = true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=192.168.56.201

1.2、配置slave节点：

   [mysqld]
binlog-format=ROW
#################REPLICATE###############
server_id =11
log_slave_updates=1
#binlog-do-db = brent
#binlog-ignore-db = mysql
#replicate_ignore_db=mysql
#replicate_do_table=brent.t1
#replicate_ignore_table=brent.t2
#######GTID#######
gtid-mode = on 
enforce-gtid-consistency = true
master-info-repository=TABLE
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=192.168.56.211

2、创建复制用户

在master端创建复制用户

   mysql> GRANT REPLICATION SLAVE ON *.* TO rpl@'%‘ IDENTIFIED BY 'rpl';

3、为备节点提供初始数据集

锁定主表，备份主节点上的数据，将其还原至从节点；如果没有启用GTID，在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置，以便后面启动slave节点时使用。

我这里使用的mysqldump命令：

mysqldump --lock-all-tables --all-databases >suq.sql

再将数据导入到slave节点

cat suq.sql |mysql

4、启动从节点的复制线程

如果启用了GTID功能，则使用如下命令：

mysql> CHANGE MASTER TO MASTER_HOST='master.magedu.com', MASTER_USER='rpl', MASTER_PASSWORD='rpl', MASTER_AUTO_POSITION=1;

############################GTID管理###########################

1.gtid中将relay_log_info_repository=table、master_info_repository=table设置为table后，会将master.info和relay.info中的数据保存在mysql的表中，

分别为：

mysql.slave_master_info

mysql.slave_relay_info

mysql.slave_worker_info

2.gtid模式中报错，不能使用skip counter跳过事务，必须将事务ID设置为空值，具体如下：

使用show slave status查看当前状态：

                  Last_SQL_Error: Worker 1 failed executing transaction 'ca6ccc10-b5c5-11e5-aed5-08002775af00:911' at master log master-bin.000006, end_log_pos 280262; Could not execute Delete_rows event on table suq.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log FIRST, end_log_pos 280262
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: ca6ccc10-b5c5-11e5-aed5-08002775af00
             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: 160108 15:27:58
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: ca6ccc10-b5c5-11e5-aed5-08002775af00:331-911
            Executed_Gtid_Set: 22e602cb-b5c8-11e5-aee4-08002775af00:1-2,
ca6ccc10-b5c5-11e5-aed5-08002775af00:1-910

retrieved_gtid_set：表示接受到的事务

executed_gtid_set：表示已经完成的事务，这里有多行表是曾经或者现在有多个主，分别对应其中的uuid和事务

其中1-910已经完成，911事务报错

这时跳过这条事务：

   mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set gtid_next='ca6ccc10-b5c5-11e5-aed5-08002775af00:911';
Query OK, 0 rows affected (0.00 sec)
 
mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> set gtid_next="automatic";
Query OK, 0 rows affected (0.00 sec)
 
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

slave端已经正常：

   mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.201
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000006
          Read_Master_Log_Pos: 280293
               Relay_Log_File: relay-bin.000005
                Relay_Log_Pos: 451
        Relay_Master_Log_File: master-bin.000006
             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: 280293
              Relay_Log_Space: 1226
              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: ca6ccc10-b5c5-11e5-aed5-08002775af00
             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 the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: ca6ccc10-b5c5-11e5-aed5-08002775af00:331-911
            Executed_Gtid_Set: 22e602cb-b5c8-11e5-aee4-08002775af00:1-2,
ca6ccc10-b5c5-11e5-aed5-08002775af00:1-911
                Auto_Position: 1