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

浏览数：35 / 时间：2015年06月12日

参考

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)

mysql> SET SESSION GTID_NEXT = AUTOMATIC;