mysql gtid模式主从复制搭建

配置前提

要在MySQL 5.6中使用复制功能，其服务配置段[mysqld]中于少应该定义如下选项：
- binlog-format：二进制日志的格式，有row、statement和mixed几种类型；
- 需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
- log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求；
- master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；
- sync-master-info：启用之可确保无信息丢失；
- slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；
- binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；
- binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；
- log-bin：启用二进制日志，这是保证复制功能的基本前提；
- server-id：同一个复制拓扑中的所有服务器的id号必须惟一；

主库（192.168.18.22）配置

# The following options will be passed to all MySQL clients
[client]
#客户端默认连接字集集,若编译安装时已指定则不用填写
#character-set-server = utf8  
#客户端连接通信端口
port = 3306    
#客户端通信的用户密码端口等信息保存文件
socket = /data/mysql/tmp/mysql.sock   
default-character-set=utf8
[mysql]
no-auto-rehash
default-character-set = utf8
# The MySQL server
[mysqld]
# 指定服务器id，主从配置必须配置，且两台机器不能一致
server_id = 22
user = mysql
#mysql服务端监听端口
port  = 3306    
#mysql数据库存放目录
datadir = /data/mysql/data 
#socket = /data/mysql/tmp/mysql.sock  
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
#配置mysql的内存大小，一般数据库服务器的80%
innodb_buffer_pool_size = 1g
#服务端pid进程文件,若丢失则重启Mysql重新生成,若重启失败,
#则可能由于mysqld进程未杀死,用pkill mysql后则能重启成功Mysql
pid-file =/data/mysql/mysqld.pid
#指定错误日志目录
log-error=/data/mysql/logs
#不同步 test 数据库，如果有多个数据库，需要多写几行
#binlog-ignore-db=test 

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M  
explicit_defaults_for_timestamp=true
lower_case_table_names=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
performance_schema_max_table_instances=400
table_definition_cache=400
table_open_cache=256
max_allowed_packet = 32M
# 设置默认引擎
default-storage-engine=INNODB
#设置默认字符
character-set-server=utf8 
collation-server=utf8_general_ci
#设置最大链接数
max_connections=500
connect_timeout = 60
interactive_timeout = 28800
wait_timeout = 28800
back_log = 500
event_scheduler = ON
skip_name_resolve = ON;


###########binlog##########
#开启二进制日志
log-bin = /data/mysql/logs/mysql-bin
binlog_format = row
max_binlog_size = 128M
binlog_cache_size = 2M
expire-logs-days = 5
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=4
#rpl_semi_sync_master_enabled = 1

slow_query_log = 1
slow_query_log_file = /data/mysql/logs/mysql.slow
long_query_time = 1

log_error = /data/mysql/logs/error.log
max_connections = 3000
max_connect_errors = 32767
log_bin_trust_function_creators = 1
transaction_isolation = READ-COMMITTED

从库（192.168.18.23）配置

# The following options will be passed to all MySQL clients
[client]
#客户端默认连接字集集,若编译安装时已指定则不用填写
#character-set-server = utf8  
#客户端连接通信端口
port = 3306    
#客户端通信的用户密码端口等信息保存文件
socket = /data/mysql/tmp/mysql.sock   
default-character-set=utf8
[mysql]
no-auto-rehash
default-character-set = utf8
# The MySQL server
[mysqld]
# 指定服务器id
server_id = 23
user = mysql
#mysql服务端监听端口
port  = 3306    
#mysql数据库存放目录
datadir = /data/mysql/data 
#socket = /data/mysql/tmp/mysql.sock  
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
#配置mysql的内存大小，一般数据库服务器的80%
innodb_buffer_pool_size = 1g
#服务端pid进程文件,若丢失则重启Mysql重新生成,若重启失败,
#则可能由于mysqld进程未杀死,用pkill mysql后则能重启成功Mysql
pid-file =/data/mysql/mysqld.pid
#指定错误日志目录
log-error=/data/mysql/logs

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M  
explicit_defaults_for_timestamp=true
lower_case_table_names=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
performance_schema_max_table_instances=400
table_definition_cache=400
table_open_cache=256
max_allowed_packet = 32M
# 设置默认引擎
default-storage-engine=INNODB
#设置默认字符
character-set-server=utf8 
collation-server=utf8_general_ci
#设置最大链接数
max_connections=500
connect_timeout = 60
interactive_timeout = 28800
wait_timeout = 28800
back_log = 500
event_scheduler = ON
skip_name_resolve = ON;


###########binlog##########
log-bin = /data/mysql/logs/mysql-bin
binlog_format = row
max_binlog_size = 128M
binlog_cache_size = 2M
expire-logs-days = 5
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=4
rpl_semi_sync_slave_enabled = 1
skip-slave-start

slow_query_log = 1
slow_query_log_file = /data/mysql/logs/mysql.slow
long_query_time = 2

log-error = /data/mysql/logs/error.log
max_connections = 3000
max_connect_errors = 10000
log_bin_trust_function_creators = 1
transaction_isolation = READ-COMMITTED

重启主从数据

service mysql restart

在主库上创建复制用户

mysql> GRANT REPLICATION SLAVE ON *.* TO synuser@192.168.18.23 IDENTIFIED BY 'jfcf123';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

启动从库上的复制线程

mysql> CHANGE MASTER TO MASTER_HOST='192.168.18.22', MASTER_USER='synuser', MASTER_PASSWORD='jfcf123', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.47 sec)
#启动从库
mysql> start slave ;
Query OK, 0 rows affected, 1 warning (0.19 sec)

查看

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.18.22
                  Master_User: synuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 381420
               Relay_Log_File: mysqld-relay-bin.000010
                Relay_Log_Pos: 52298
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes   #IO线程已正常运行
            Slave_SQL_Running: Yes   #SQL线程已正常运行
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 381420
              Relay_Log_Space: 227688
              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: 22
                  Master_UUID: 52c9b667-c917-11e7-b778-fcaa146b4365
             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: 52c9b667-c917-11e7-b778-fcaa146b4365:1-5:7-915
            Executed_Gtid_Set: 52c9b667-c917-11e7-b778-fcaa146b4365:1-915,
f46c6bc2-c920-11e7-b7b7-74d435654fa6:1
                Auto_Position: 1
1 row in set (0.00 sec)

跳过指定事务

mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)

mysql> set gtid_next="52c9b667-c917-11e7-b778-fcaa146b4365:6";
Query OK, 0 rows affected (0.00 sec)

mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next="AUTOMATIC";
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.17 sec)