MySQL5.6.xGTID主从复制详细配置指南

本文环境:

主库&＃xff1a;CentOS6.7 x64 192.168.0.65 mysql-5.6.29

备库&＃xff1a;CentOS6.7 x64 192.168.0.66 mysql-5.6.29

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

1. mysql主服务器配置

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

# vi /etc/my.cnf

[mysqld]

binlog-format                &＃61; ROW

log-bin                      &＃61; master-bin

log-bin-index                &＃61; master-bin.index

log-slave-updates            &＃61; true

gtid-mode                    &＃61; on

enforce-gtid-consistency     &＃61; true

master-info-repository       &＃61; TABLE

relay-log-info-repository    &＃61; TABLE

sync-master-info             &＃61; 1

slave-parallel-workers       &＃61; 2

binlog-checksum              &＃61; CRC32

master-verify-checksum       &＃61; 1

slave-sql-verify-checksum    &＃61; 1

binlog-rows-query-log_events &＃61; 1

report-host                  &＃61; 192.168.1.120

server-id                    &＃61; 1

重启数据库:

# service mysqld restart

查看gtid信息:

mysql> show global variables like &＃39;%GTID%&＃39;;

&＃43;---------------------------------&＃43;----------------------------------------&＃43;

| Variable_name                   | Value                                  |

&＃43;---------------------------------&＃43;----------------------------------------&＃43;

| binlog_gtid_simple_recovery     | OFF                                    |

| enforce_gtid_consistency        | ON                                     |

| gtid_executed                   |                                        |

| gtid_mode                       | ON                                     |

| gtid_owned                      |                                        |

| gtid_purged                     |                                        |

| simplified_binlog_gtid_recovery | OFF                                    |

&＃43;---------------------------------&＃43;----------------------------------------&＃43;

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

grant replication slave on *.* to &＃39;repl&＃39;&＃64;&＃39;%&＃39; identified by &＃39;123456&＃39;;

flush privileges;

3. mysql从服务器配置

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

# vi /etc/my.cnf

[mysqld]

binlog-format                &＃61; ROW

log-bin                      &＃61; mysql-bin

relay-log                    &＃61; slave-relay-bin

relay-log-index              &＃61; slave-relay-bin.index

log-slave-updates            &＃61; true

gtid-mode                    &＃61; on

enforce-gtid-consistency     &＃61; true

master-info-repository       &＃61; TABLE

relay-log-info-repository    &＃61; TABLE

sync-master-info             &＃61; 1

slave-parallel-workers       &＃61; 2

binlog-checksum              &＃61; CRC32

master-verify-checksum       &＃61; 1

slave-sql-verify-checksum    &＃61; 1

binlog-rows-query-log_events &＃61; 1

report-host                  &＃61; 192.168.1.121

server-id                    &＃61; 11

重启数据库:

# service mysqld restart

查看gtid状态:

mysql> show global variables like &＃39;%GTID%&＃39;;

&＃43;---------------------------------&＃43;----------------------------------------&＃43;

| Variable_name                   | Value                                  |

&＃43;---------------------------------&＃43;----------------------------------------&＃43;

| binlog_gtid_simple_recovery     | OFF                                    |

| enforce_gtid_consistency        | ON                                     |

| gtid_executed                   |                                        |

| gtid_mode                       | ON                                     |

| gtid_owned                      |                                        |

| gtid_purged                     |                                        |

| simplified_binlog_gtid_recovery | OFF                                    |

&＃43;---------------------------------&＃43;----------------------------------------&＃43;

7 rows in set (0.00 sec)

4. 连接主Mysql,配置主从

(1) 连接主数据库

mysql>

CHANGE MASTER TO

MASTER_HOST&＃61;&＃39;192.168.0.65&＃39;,

MASTER_PORT&＃61;3306,

MASTER_USER&＃61;&＃39;repl&＃39;,

MASTER_PASSWORD&＃61;&＃39;123456&＃39;,

MASTER_AUTO_POSITION&＃61;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&＃xff0c;说明从库运行正常。

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;

&＃43;--------------------&＃43;

| Database           |

&＃43;--------------------&＃43;

| information_schema |

| abc                |

| mydb               |

| mysql              |

| performance_schema |

&＃43;--------------------&＃43;

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&＃xff0c;从主库添加如上GTID相关配置文件&＃xff0c;重启数据库后&＃xff0c;主库锁表&＃xff0c;备份数据库。

mysql> flush tables with read lock;

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

mysql> stop slave;

mysql> reset slave;

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

mysql>

CHANGE MASTER TO

MASTER_HOST&＃61;&＃39;192.168.0.65&＃39;,

MASTER_PORT&＃61;3306,

MASTER_USER&＃61;&＃39;repl&＃39;,

MASTER_PASSWORD&＃61;&＃39;123456&＃39;,

MASTER_AUTO_POSITION&＃61;1;

4&＃xff0c;主库

mysql> unlock tables;

5&＃xff0c;从库启动并测试同步情况

三、小结

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