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

说明: 基于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                                    |    

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

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

flush privileges;

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

log-bin                      = mysql-bin    

relay-log                    = slave-relay-bin    

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

report-host                  = 192.168.1.121    

server-id                    = 11

查看gtid状态:    

7 rows in set (0.00 sec)

mysql>   

CHANGE MASTER TO    

MASTER_HOST='192.168.0.65',    

MASTER_PORT=3306,    

MASTER_USER='repl',    

MASTER_PASSWORD='123456',    

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: 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

mysql> create database abc;   

Query OK, 1 row affected (0.02 sec)

mysql> show master status\G;   

             File: master-bin.000007    

         Position: 290    

     Binlog_Do_DB:     

Binlog_Ignore_DB:     

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

mysql> show databases;   

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

| Database           |    

| information_schema |    

| abc                |    

| mydb               |    

| mysql              |    

| performance_schema |    

5 rows in set (0.01 sec)

mysql> show slave status\G;   

            Slave_SQL_Running: Yes    

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

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

No query specified    

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

mysql> flush tables with read lock;

mysql> stop slave;   

mysql> reset slave;

mysql> unlock tables;

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

本文转自 koumm 51CTO博客，原文链接：http://blog.51cto.com/koumm/1767786，如需转载请自行联系原作者