MySQL 5.6引入的GTID(Global Transaction IDs)使得其复制功能的配置、监控及管理变得更加易于实现,且更加健壮。

要在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号必须惟一;

一、Mysql基于GTID的主从复制实战

1、准备工作

hostnamectl set-hostname www.mysql61.com

cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.130.61 www.mysql61.com

192.168.130.62 www.mysql62.com

192.168.130.63 www.mysql63.com

关防火墙、selinux

firewall-cmd –state

systemctl stop firewalld

firewall-cmd –state

systemctl disable firewalld

vim /etc/selinux/config 

setenforce 0

修改UUID

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

vim /mydata/data/auto.cnf

2、配置master节点:

[mysqld]

binlog-format=ROW

log-bin=/mydata/binlog/log-bin

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

server-id=61

report-port=3306

port=3306

datadir=/mydata/data

socket=/tmp/mysql.sock

report-host=www.mysql61.com

mysql

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘repluser’@’192.168.130.%’ IDENTIFIED BY ‘replpass’;

FLUSH PRIVILEGES;

3、配置slave节点:

[mysqld]

binlog-format=ROW

log-bin=/mydata/binlog/log-bin

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

server-id=62

report-port=3306

port=3306

datadir=/mydata/data

socket=/tmp/mysql.sock

report-host=www.mysql62.com

4、为备节点提供初始数据集

锁定主表,备份主节点上的数据,将其还原至从节点;如果没有启用GTID,在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置,以便后面启动slave节点时使用。

master节点

mysql

CREATE DATABASE mydb;

mysqldump –all-databases –lock-all-tables –flush-logs –master-data=2 > all.sql

scp all.sql root@192.168.130.62:/tmp

slave节点

mysql < /tmp/all.sql

head -30 /tmp/all.sql

— CHANGE MASTER TO MASTER_LOG_FILE=’log-bin.000016′, MASTER_LOG_POS=194;

5、启动从节点的复制线程

如果启用了GTID功能,则使用如下命令:

CHANGE MASTER TO MASTER_HOST=’www.mysql61.com’, MASTER_USER=’repluser’, MASTER_PASSWORD=’replpass’, MASTER_AUTO_POSITION=1;

没启用GTID,需要使用如下命令:

CHANGE MASTER TO MASTER_HOST=’192.168.130.61′,MASTER_USER=’repluser’,MASTER_PASSWORD=’replpass’,MASTER_LOG_FILE=’log-bin.000014′,MASTER_LOG_POS=154;

master节点

START SLAVE;

SHOW SLAVE STATUS\G;

show processlist;

slave节点

show slave hosts;

mysql < hellodb.sql

MariaDB GTID:

文档中应用MariaDB-10,需要做的修改:

1、不支持的参数:

gtid-mode=on 

enforce-gtid-consistency=true

2、修改的参数:

slave-parallel-workers参数修改为slave-parallel-threads

3、连接至主服务使用的命令:

一个新的参数:MASTER_USER_GTID={current_pos|slave_pos|no}

CHANGE MASTER TO master_host=”127.0.0.1″, master_port=3310, master_user=”root”, master_use_gtid=current_pos;