Ubuntu16.04 Mysql主从同步 GTID模式

目录

GTID原理

GTID优缺点

主从同步配置

一些命令

一些错误及解决方案

---

GTID原理

基于GTID的复制是MySQL 5.6后新增的复制方式.
GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID.
在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致.
而基于GTID的复制中, 从库会告知主库已经执行的事务的GTID的值, 然后主库会将所有未执行的事务的GTID的列表返回给从库. 并且可以保证同一个事务只在指定的从库执行一次.GTID是由server_uuid和事物id组成，格式为：GTID=server_uuid:transaction_id。server_uuid是在数据库启动过程中自动生成，每台机器的server-uuid不一样。uuid存放在数据目录的auto.conf文件中，而transaction_id就是事务提交时系统顺序分配的一个不会重复的序列号。

GTID优缺点

优点

1）GTID相对于行复制数据安全性更高，故障切换更简单。
2) 简单的实现 failover，不用以前那样在需要找 log_file 和 log_pos。
3) 更简单的搭建主从复制，确保每个事务只会被执行一次。
4) 比传统的复制更加安全，一个 GTID 在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
5）GTID是连续的没有空洞的，保证数据的一致性，零丢失
6）GTID 用来代替classic的复制方法，不再使用 binlog+pos 开启复制。而是使用 master_auto_postion=1 的方式自动匹配 GTID 断点进行复制。
7) GTID 的引入，让每一个事务在集群事务的海洋中有了秩序，使得 DBA 在运维中做集群变迁时更加方便
8) 根据 GTID 可以快速的确定事务最初是在哪个实例上提交的。

缺点

1）主从库的表存储引擎必须是一致的
主从库的表存储引擎不一致，就会导致数据不一致。如果主从库的存储引擎不一致，例如一个是事务存储引擎，一个是非事务存储引擎，则会导致事务和 GTID 之间一对一的关系被破坏，结果就会导致基于 GTID 的复制不能正确运行；
master：对一个innodb表做一个多sql更新的事物，效果是产生一个GTID。
slave：假设对应的表是MYISAM引擎，执行这个GTID的第一个语句后就会报错，因为非事务引擎一个sql就是一个事务。
当从库报错时简单的stop slave; start slave;就能够忽略错误。但是这个时候主从的一致性已经出现问题,需要手工的把slave差的数据补上,这里要将引擎调整为一样的，slave也改为事务引擎。

2）不允许一个SQL同时更新一个事务引擎和非事务引擎的表
事务中混合多个存储引擎，就会产生多个 GTID。当使用 GTID 时，如果在同一个事务中，更新包括了非事务引擎（如 MyISAM）和事务引擎（如 InnoDB）表的操作，就会导致多个 GTID 分配给了同一个事务。

3）在一个复制组中，必须要求统一开启GTID或是关闭GTID;

4）不支持create table….select 语句复制（主库直接报错）;
create table xxx as select的语句，其实会被拆分为两部分，create语句和insert语句，但是如果想一次搞定，MySQL会抛出如下的错误。
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
create table xxx as select 的方式可以拆分成两部分，如下。
create table xxxx like data_mgr;insert into xxxx select *from data_mgr;

5）对于create temporary table 和 drop temporary table语句不支持;
使用GTID复制模式时，不支持create temporary table 和 drop temporary table。但是在autocommit=1的情况下可以创建临时表，Master端创建临时表不产生GTID信息，所以不会同步到slave，但是在删除临时表的时候会产生GTID会导致，主从中断.

6）不支持sal_slave_skip_counter.
mysql在主从复制时如果要跳过报错，可以采取以下方式跳过SQL（event）组成的事务，但GTID不支持以下方式。
set global SQL_SLAVE_SKIP_COUNTER=1;start slave sql_thread;

主从同步配置

配置环境

主库master 虚拟机ubuntu 01 msyql5.7

从库slave 虚拟机ubuntu 02 mysql5.7

尽量锁库，防止配置主从时有数据遗漏

-- 全库表锁定，不能写入，但可以读
FLUSH TABLES WITH READ LOCK;
-- 全库表解锁
UNLOCK TABLES;

从库先停止slave 并重置master（未配置无需操作）

stop slave;
reset slave;
reset master;

主从库 创建同步用户，并授权访问要同步的数据库

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

主库配置/etc/mysql/my.cnf

[mysqld]

server-id = 1					# 服务器唯一标识，要唯一
log_bin=master-binlog			# binlog二进制日志(如果是绝对路径,必须指定索引文件)
#log-bin = /var/log/mysql/mysql-bin.log  # 开启MySQL二进制日志
#log_bin_index = /var/log/mysql/mysql-bin.index  # binlog文件的索引文件
binlog_do_db = db4				# 允许指定库记录二进制日志(指定多个需添加多行)
#binlog_ignore_db = db4         # 不允许指定库记录二进制日志，与binlog_do_db相斥(指定多个需添加多行)

gtid-mode = on                  # 启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency = on	# 强制GTID的一致性
log-slave-updates = 1           # slave更新是否记入日志
binlog_format = row				# 强烈建议，其他格式可能造成数据不一致

从库配置/etc/mysql/my.cnf

[mysqld]

server-id = 2					# 服务器唯一标识，要唯一
log_bin=master-binlog			# binlog二进制日志(如果是绝对路径,必须指定索引文件)
#log-bin = /var/log/mysql/mysql-bin.log  # 开启MySQL二进制日志
#log_bin_index = /var/log/mysql/mysql-bin.index  # binlog文件的索引文件
binlog_do_db = db4				# 允许指定库记录二进制日志(指定多个需添加多行)
#binlog_ignore_db = db4         # 不允许指定库记录二进制日志，与binlog_do_db相斥(指定多个需添加多行)

gtid-mode = on                  # 启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency = on	# 强制GTID的一致性
log-slave-updates = 1           # slave更新是否记入日志
binlog_format = row				# 强烈建议，其他格式可能造成数据不一致

查看gtid状态（gtid_mode: ON表示已开启）

show global variables like '%GTID%';

主库导出数据库（新库无需做此步骤）

mysqldump -uroot -p'111222' --master-data=2 --single-transaction -R --triggers --databases db4 > db4.sql

从库导入数据库（新库无需做此步骤，ps：分区表直接导入会报错）

mysql -uroot -p'111222' < db4.sql

从库登录mysql执行

CHANGE MASTER TO MASTER_HOST='192.168.1.128', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_AUTO_POSITION=1;

开启slave服务

start slave;

关闭slave服务

stop slave;

查看复制状态（slave服务需开启，Slave_IO_Running和Slave_SQL_Running都为yes才表示同步成功）

show slave status\G

一些命令

stop slave;

reset slave all;

一些错误及解决方案

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

mysql内执行

reset master;

Slave_IO_Running：Connecting； Slave_SQL_Running：Yes的问题

参考：Mysql主从同步时Slave_IO_Running：Connecting ; Slave_SQL_Running：Yes的情况故障排除_mbytes的博客-CSDN博客

Last_IO_Errno: 1236

mysqlG基于TID模式同步报错 （Last_IO_Errno: 1236） - h_s - 博客园

Last_Errno: 1062

MySQL Last_SQL_Errno: 1062----经典错误，主键冲突 - 一个苦逼的运维人 - 博客园

OK.