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相当了解后再用于生产环境。