参考

http://imysql.com/tag/gtid

http://mysqllover.com/?p=594


Mysql 基于GTID的主从复制及切换


一、主从复制配置

两个mysql服务的my.cnf 中相关内容配置

[mysqld]

#从复制数据库表设置

replicate-wild-ignore-table = mysql.%,information_schema.%,innodb.%,innodb_log.%,performance_schema.%,test.%,tmp.% #复制时忽略数据库及表

#my.cnf 开启gtid 

log_slave_updates # 表示即可以当从也可以当主

gtid_mode = on

enforce-gtid-consistency = true


10.123.2.128 my.cnf

[mysqld]

server_id=10128 # 所有 mysql服务保持各不相同


10.123.2.165 my.cnf

[mysqld]

server_id=10165 # 所有 mysql服务保持各不相同


# 验证gtid已开启

mysql> SHOW VARIABLES like '%gtid%';

+--------------------------+-----------+

| Variable_name            | Value     |

+--------------------------+-----------+

| enforce_gtid_consistency | ON        |

| gtid_executed            |           |

| gtid_mode                | ON        |

| gtid_next                | AUTOMATIC |

| gtid_owned               |           |

| gtid_purged              |           |

+--------------------------+-----------+


# 查看当前mysql服务的uuid,gtid的主从同步,绝大多数功能基于server_uuid

mysql> SHOW VARIABLES like '%server_uuid%';



ip: 10.235.2.128    

server_uuid:    aad2226a-6ed3-11e4-9ae0-080027cdcda2 


ip: 10.235.2.165    

server_uuid:    68a061ee-b013-11e4-845a-080027a36fd3



分别在10.235.2.128、10.235.2.165两个 mysql服务中添加 zabbix_repl用户同步权限

mysql> grant Replication client,replication slave  on *.* to 'zabbix_repl'@'%' identified by '123456';flush privileges;



10.235.2.165 的mysql服务中设置主mysql信息,其中的master_auto_position=1 参数会自动判断从主的什么位置同步数据

mysql> stop slave;

mysql> change master to MASTER_HOST='10.235.2.128',master_user='zabbix_repl',master_password='123456',MASTER_PORT = 3306,MASTER_CONNECT_RETRY = 60,master_auto_position=1;

mysql> start slave;


mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.235.2.128

                  Master_User: zabbix_repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 551

               Relay_Log_File: relay-log.000002

                Relay_Log_Pos: 761

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

            ...

            ...

            ...

             Master_Server_Id: 10128

                  Master_UUID: aad2226a-6ed3-11e4-9ae0-080027cdcda2 # 主数据库的server_uuid

             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: aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-2     # 记录了relay日志从Master获取了binlog日志的位置的gtid,由master_uuid:事务id组成

            Executed_Gtid_Set: 68a061ee-b013-11e4-845a-080027a36fd3:1-3,    # 本机binlog执行位置的gtid,由 server_uuid:事务id 组成

aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-2    # 主服务上binlog执行位置的gtid,由 master_uuid:事务id 组成

                Auto_Position: 1

                

                

在10.235.2.128的主mysql中添加数据测试

mysql> create database test2;

mysql> show master status \G

*************************** 1. row ***************************

             File: mysql-bin.000001

         Position: 1014

     Binlog_Do_DB: 

 Binlog_Ignore_DB: 

Executed_Gtid_Set: aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-4


再次查看10.235.2.165从mysql同步状态,同步正常

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| innodb             |

| innodb_log         |

| mysql              |

| performance_schema |

| test               |

| test2              |  # 新添加的测试库

| testdb1            |

| tmp                |

+--------------------+

mysql> show slave status \G

*************************** 1. row ***************************

              ...

              ...

            Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              ...

              ...

            Master_UUID: aad2226a-6ed3-11e4-9ae0-080027cdcda2

             ...

             ...

           Retrieved_Gtid_Set: aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-4

            Executed_Gtid_Set: 68a061ee-b013-11e4-845a-080027a36fd3:1-3,

aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-4

                Auto_Position: 1


二、主从切换


在10.235.2.165主机

mysql> stop slave;


在10.235.2.128主机

mysql> stop slave;

mysql> change master to MASTER_HOST='10.235.2.165',master_user='zabbix_repl',master_password='123456',MASTER_PORT = 3306,MASTER_CONNECT_RETRY = 60,master_auto_position=1;

mysql> start slave;

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.235.2.165

                  Master_User: zabbix_repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 1529

               Relay_Log_File: relay-log.000002

                Relay_Log_Pos: 888

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              ...

              ...

                  Master_UUID: 68a061ee-b013-11e4-845a-080027a36fd3

                  ...

                  ...

           Retrieved_Gtid_Set: 68a061ee-b013-11e4-845a-080027a36fd3:1-3

            Executed_Gtid_Set: 68a061ee-b013-11e4-845a-080027a36fd3:1-3 # 一般与master mysql 10.235.2.165mysql的gtid相同

                Auto_Position: 1


mysql> 


在10.235.2.165主机

mysql> show master status \G

*************************** 1. row ***************************

             File: mysql-bin.000001

         Position: 1529

     Binlog_Do_DB: 

 Binlog_Ignore_DB: 

Executed_Gtid_Set: 68a061ee-b013-11e4-845a-080027a36fd3:1-3,    # 10.235.2.165 mysql的gtid

aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-4

在10.235.2.165测试删除数据

mysql> drop database test2;

Query OK, 0 rows affected (0.12 sec)



在10.235.2.128查看数据已经同步(已经没有数据库test2)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| innodb             |

| innodb_log         |

| mysql              |

| performance_schema |

| test               |

| testdb1            |

| tmp                |

+--------------------+


到这里主机切换已经完成


三、主主复制


原理:

1、双方节点都得有创建具有复制权限的用户    # 参考一、主从复制 添加权限用户

2、双方都得启用中继日志和二进制日志        

3、为保证具有自动增长功能的字段能正确生成ID,需要配置两个节点分别使用偶数或奇数ID号

4、都要把对方配置为自己的主节点            # 参考一、主从复制 change master to ... 


在my.cnf添加下面相关配置

10.235.2.165 my.cnf配置

[mysqld]

auto-increment-offset = 1       #自增id为奇数

auto-increment-increment = 2    #自增id的增量

log_slave_updates # 表示即可以当从也可以当主


10.235.2.128 my.cnf配置

[mysqld]

auto-increment-offset = 2       #自增id为偶数

auto-increment-increment = 2    #自增id的增量

log_slave_updates # 表示即可以当从也可以当主


四、GTID主从复制遇到的异常情况


错误1:

当备库复制出错时,传统的跳过错误的方法是设置sql_slave_skip_counter,然后再START SLAVE。

但如果打开了GTID,就会设置失败:


mysql> set global sql_slave_skip_counter = 1;


ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction



提示的错误信息告诉我们,可以通过生成一个空事务来跳过错误的事务。


我们手动产生一个备库复制错误:


Last_SQL_Error: Error ‘Unknown table ‘test.t1” on query. Default database: ‘test’. Query: ‘DROP TABLE `t1` /* generated by server */’


查看binlog中,该DDL对应的GTID为7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131


在备库上执行:

mysql> STOP SLAVE;


Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION GTID_NEXT = '7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131';

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 SESSION GTID_NEXT = AUTOMATIC;


Query OK, 0 rows affected (0.00 sec)


mysql> START SLAVE;


再查看show slave status,就会发现错误事务已经被跳过了。这种方法的原理很简单,空事务产生的GTID加入到GTID_EXECUTED中,这相当于告诉备库,这个GTID对应的事务已经执行了。