mysql使用gtid主从复制

最新推荐文章于 2024-02-17 14:33:55 发布

久违的太阳

最新推荐文章于 2024-02-17 14:33:55 发布

阅读量1k

点赞数

分类专栏： mysql主从复制文章标签： mysql gtid

本文链接：https://blog.csdn.net/su377486/article/details/50494726

版权

mysql主从复制专栏收录该内容

4 篇文章 0 订阅

订阅专栏

环境：

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

3.gtid模式改为传统模式

首先在my.cnf中注释掉gtid-mode=ON和enforce-gtid-consistency=ON并且重启mysql

然后使用语句change master to master_auto_potision=0;

最后使用：

slave> CHANGE MASTER TO MASTER_HOST='192.168.56.201',

-> MASTER_USER='rpl',

-> MASTER_PASSWORD='rpl',

-> MASTER_LOG_FILE='master-bin.000003',

-> MASTER_LOG_POS=1174;

久违的太阳

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql使用gtid主从复制

环境：master：192.168.56.201slave：192.168.56.2111、配置主从节点的服务配置文件1.1、配置master节点：[mysqld]binlog-format=ROWserver_id =1log_slave_updates=1#binlog-do-db = brent#binlog-ignore-db = mysql
复制链接

扫一扫