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

xian123

　　参考
　　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'@'%'>　　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:>
　　>
　　>　　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>　　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:>
　　>
　　>　　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对应的事务已经执行了。