mysql gtid 主从配置_mysql5.6.x GTID主从复制配置

本文环境:

主库：CentOS6.7 x64 192.168.0.65 mysql-5.6.29

备库：CentOS6.7 x64 192.168.0.66 mysql-5.6.29

一、配置Mysql5.6 GTID主从(本节配置适合主从都是空库的情况)

1. mysql主服务器配置

说明: 基于GTID的主从复制需要在配置文件中添加如下内容。

# vi /etc/my.cnf

[mysqld]

binlog-format                = ROW

log-bin                      = master-bin

log-bin-index                = master-bin.index

log-slave-updates            = true

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-host                  = 192.168.1.120

server-id                    = 1

重启数据库:

# service mysqld restart

查看gtid信息:

mysql> show global variables like '%GTID%';

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

| Variable_name                   | Value                                  |

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

| binlog_gtid_simple_recovery     | OFF                                    |

| enforce_gtid_consistency        | ON                                     |

| gtid_executed                   |                                        |

| gtid_mode                       | ON                                     |

| gtid_owned                      |                                        |

| gtid_purged                     |                                        |

| simplified_binlog_gtid_recovery | OFF                                    |

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

2. 主服务器配置同步复制帐号

grant replication slave on *.* to 'repl'@'%' identified by '123456';

flush privileges;

3. mysql从服务器配置

说明: 默认只要server-id不相同即可。

# vi /etc/my.cnf

[mysqld]

binlog-format                = ROW

log-bin                      = mysql-bin

relay-log                    = slave-relay-bin

relay-log-index              = slave-relay-bin.index

log-slave-updates            = true

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-host                  = 192.168.1.121

server-id                    = 11

重启数据库:

# service mysqld restart

查看gtid状态:

mysql> show global variables like '%GTID%';

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

| Variable_name                   | Value                                  |

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

| binlog_gtid_simple_recovery     | OFF                                    |

| enforce_gtid_consistency        | ON                                     |

| gtid_executed                   |                                        |

| gtid_mode                       | ON                                     |

| gtid_owned                      |                                        |

| gtid_purged                     |                                        |

| simplified_binlog_gtid_recovery | OFF                                    |

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

7 rows in set (0.00 sec)

4. 连接主Mysql,配置主从

(1) 连接主数据库

mysql>

CHANGE MASTER TO

MASTER_HOST='192.168.0.65',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='123456',

MASTER_AUTO_POSITION=1;

(2) 启动从同步进程

mysql> start slave;

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.65

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000007

Read_Master_Log_Pos: 290

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 502

Relay_Master_Log_File: master-bin.000007

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: mysql.%

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 290

Relay_Log_Space: 706

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: c8bb22a1-024e-11e6-a1e8-000c29225fa0

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:

Executed_Gtid_Set:

Auto_Position: 1

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

#查看如下两个参数为YES，说明从库运行正常。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5. 验证同步情况

(1) 主数据库创建一个数据库

mysql> create database abc;

Query OK, 1 row affected (0.02 sec)

mysql> show master status\G;

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

File: master-bin.000007

Position: 290

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: c8bb22a1-024e-11e6-a1e8-000c29225fa0:1

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

(2) 从数据库查看同步情况

mysql> show databases;

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

| Database           |

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

| information_schema |

| abc                |

| mydb               |

| mysql              |

| performance_schema |

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

5 rows in set (0.01 sec)

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.65

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000007

Read_Master_Log_Pos: 290

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 502

Relay_Master_Log_File: master-bin.000007

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: mysql.%

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 290

Relay_Log_Space: 706

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: c8bb22a1-024e-11e6-a1e8-000c29225fa0

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: c8bb22a1-024e-11e6-a1e8-000c29225fa0:1

Executed_Gtid_Set: c8bb22a1-024e-11e6-a1e8-000c29225fa0:1

Auto_Position: 1

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>

二、 配置Mysql5.6 GTID主从 (主从异步模式转成GTID模式)方式二

原环境本身已经是异步主从同步模式。

1，从主库添加如上GTID相关配置文件，重启数据库后，主库锁表，备份数据库。

mysql> flush tables with read lock;

2，从库操重新配置从库，清除掉原来的主库配置信息, 或者采用导入数据库。

mysql> stop slave;

mysql> reset slave;

3，在保证数据同步的情况下从库重新连接主库同步。

mysql>

CHANGE MASTER TO

MASTER_HOST='192.168.0.65',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='123456',

MASTER_AUTO_POSITION=1;

4，主库

mysql> unlock tables;

5，从库启动并测试同步情况

三、小结

可以在GTID的基础上配置半自动同步复制,可以查看相关文档，实际环境中需要对GTID相当了解后再用于生产环境。