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，如需轉載請自行聯系原作者