Mysql5.7 主从 Replication Using GTID
目录
请双击移步 Docker搭建MySQL主从
Note: pre
请移步 Docker搭建MySQL主从
你可以使用基于语句的或基于行的复制与 GTID ，但是，为了获得最佳效果，我们建议你使用基于行（ROW）的格式。 其它一些需要注意的点 enforce_gtid_consistency 强制 GTID 一致性, 启用后以下命令无法再使用。 create table … select … mysql> create table test2 select * from test1; ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT. 因为实际上是两个独立事件，所以只能将其拆分。先建立表，然后再把数据插入到表中。 事务内部不能创建临时表 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> create temporary table test2(id int); ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and 同一事务中不能同时更新事务表与非事务表(MyISAM)，建议都选择 Innodb 作为默认的数据库引擎。 master ip 192.168.0.201 slave ip ip 192.168.0.202 and 192.168.0.203 mysql5.7.18 已打包为rpm
有关数据导出，主从库数据同步 请见Mysql5.6主从 replication同步
主从配置开始
修改MySQL主配置文件
配置 MySQL 基于GTID的复制，主要是需要在 MySQL 服务器的主配置文件 [mysqld] 段中添加以下内容：
[mysqld] gtid-mode = ON enforce-gtid-consistency = ON log_bin=ON #--高可用切换，最好设置ON binlog_format = row #gtid server-id = 1 # 这上面几个是一定要 MySQL5.7.13 or higher log-slave-updates=ON #在 MySQL 5.6 版本时，基于 GTID 的复制中 log-slave-updates 选项是必须的。但是其增大了从服务器的IO负载, 而在 MySQL 5.7 中该选项已经不是必须项 #enforce-gtid-consistency #（可选）--高可用切换，最好设置ON #gtid
修改MySQL slave配置文件
[mysqld] gtid_mode = ON enforce_gtid_consistency = ON #gtid server-id = 2 #这上面几个是一定要的 #听说可以关闭从库的binlog 节约开销 还没试过呢 20190514pm read-only #log-slave-updates = 1 #5.7这个可以不要了 #log-slave-updates = ON #gtid report-port = 3306 report-host = 192.168.2.212#masterip #只同步指定的库 多个库写多个行 还有指定表的 #on slave replicate-do-db=lxtx_market replicate-do-db=lxtx_scheduler
https://www.cnblogs.com/new-journey/p/11319527.html
重启MySQL服务器
这个有空看看
2.配置完后动手
step 1: 让所有server处于同一个点
mysql> SET @@global.read_only = ON;
step 2: 关闭所有MySQL
shell> mysqladmin -uusername -p shutdown
step 3: 重启所有MySQL，并开启GTID
shell> mysqld --gtid-mode=ON --log-bin --enforce-gtid-consistency &
当然，在my.cnf中配置好最佳
step 4: change master
mysql> CHANGE MASTER TO
> MASTER_HOST = host,
> MASTER_PORT = port,
> MASTER_USER = user,
> MASTER_PASSWORD = password,
> MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;
step 5: 让master 可读可写
mysql> SET @@global.read_only = OFF;
创建具有复制权限的用户
基于 GTID 的复制会自动地将没有在从库执行过的事务重放，所以不要在其它从库上建立相同的账号。 如果建立了相同的账户，有可能造成复制链路的错误。
# 在MySQL主服务器上创建 # grant replication slave on *.* to 'repl'@'192.168.2.%' identified by '000000'; #grant replication slave,reload,super on *.* to 'slave'@'192.168..12' identified by '123456'; # good 这个比较好 这两个选择一个 grant replication slave on *.* to 'repl'@'192.168.0.202' identified by 'opslove'; grant replication slave on *.* to 'repl'@'192.168.0.203' identified by 'opslove'; flush privileges; #从IP 填写错误 导致 connecting master
查看主库与从库的GTID是否开启
mysql> show variables like "%gtid%"; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+-----------+ 8 rows in set (0.00 sec) mysql> show variables like '%gtid_next%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | gtid_next | AUTOMATIC | +---------------+-----------+ 1 row in set (0.01 sec)
简单说下几个常用参数的作用
a) gtid_executed
在当前实例上执行过的 GTID 集合，实际上包含了所有记录到 binlog 中的事务。设置 set sql_log_bin=0 后执行的事务不会生成 binlog 事件，也不会被记录到 gtid_executed 中。执行 RESET MASTER 可以将该变量置空。
b) gtid_purged
binlog 不可能永远驻留在服务上，需要定期进行清理(通过 expire_logs_days 可以控制定期清理间隔)，否则迟早它会把磁盘用尽。
gtid_purged 用于记录本机上已经执行过，但是已经被清除了的 binlog 事务集合。它是 gtid_executed 的子集。只有 gtid_executed 为空时才能手动设置该变量，此时会同时更新 gtid_executed 为和 gtid_purged 相同的值。
gtid_executed 为空意味着要么之前没有启动过基于 GTID 的复制，要么执行过 RESET MASTER。执行 RESET MASTER 时同样也会把 gtid_purged 置空，即始终保持 gtid_purged 是 gtid_executed 的子集。
c) gtid_next
会话级变量，指示如何产生下一个GTID。可能的取值如下:
第一个：AUTOMATIC
自动生成下一个 GTID，实现上是分配一个当前实例上尚未执行过的序号最小的 GTID。
第二个：ANONYMOUS
设置后执行事务不会产生GTID。
第三个：显式指定的GTID
可以指定任意形式合法的 GTID 值，但不能是当前 gtid_executed 中的已经包含的 GTID，否则下次执行事务时会报错。
查看服务器server_uuid
mysql> show global variables like '%uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 05a0b627-9b9d-11e8-9f72-5e295b78c544 | +---------------+--------------------------------------+
查看主服务器状态
mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000005 | 2138 | | | 05a0b627-9b9d-11e8-9f72-5e295b78c544:1-8 | +------------------+----------+--------------+------------------+------------------------------------------+ MySQL [(none)]> show master status; +-----------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-----------+----------+--------------+------------------+-------------------+ | ON.000001 | 154 | | | | +-----------+----------+--------------+------------------+-------------------+
启动gtid复制：从库上操作 202 203
#从库连接至主库 on slave ip 202 203; master ip 201 CHANGE MASTER TO MASTER_HOST='192.168.0.201',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='opslove',MASTER_AUTO_POSITION=1; #在从服务器上启动复制 mysql> STOP SLAVE; mysql> START SLAVE; 启动成功后查看SLAVE的状态 mysql> SHOW SLAVE STATUS\G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... 确认 Slave_IO_Running 和 Slave_SQL_Running 两个参数都为 Yes 状态 #在主服务器查看从库连接的主机信息 mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 3 | | 3306 | 1 | 0ed8b3d1-9b9e-11e8-ae4d-de88e619140a | | 2 | | 3306 | 1 | 4c4a094b-9b9d-11e8-a86f-f2a1ef986626 | +-----------+------+------+-----------+--------------------------------------+
测试GTID主从复制
在主库(master)实例创建一些数据 mysql> create database mytest; Query OK, 1 row affected (0.06 sec) mysql> use mytest; Database changed mysql> CREATE TABLE `test1` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL); Query OK, 0 rows affected (0.15 sec) insert into test1 values(1,1); mysql> insert into test1 values(1,1); Query OK, 1 row affected (0.02 sec) 在从库1, 2 分别检查数据是否成功复制 select * from mytest.test1; 检查从服务器状态
trouble shooting
机器断电后 Slave_SQL_Running: No
问题:
机器断电后 Slave_SQL_Running: No
show slave status\G
Slave_SQL_Running: No
Retrieved_Gtid_Set: 05a0b627-9b9d-11e8-9f72-5e295b78c544:10-217
Executed_Gtid_Set: 05a0b627-9b9d-11e8-9f72-5e295b78c544:1-10
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
解决办法:
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
mysql> SET GTID_NEXT="05a0b627-9b9d-11e8-9f72-5e295b78c544:11";
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.03 sec)
mysql> SET GTID_NEXT="AUTOMATIC";
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000019
Read_Master_Log_Pos: 2589
Relay_Log_File: mq01-relay-bin.000002
Relay_Log_Pos: 136319
Relay_Master_Log_File: mysql-bin.000018
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
https://www.52os.net/articles/injecting-empty-transactions-repair-mysql-5-6-gtid-replication.html
另一个slave 问题 断电后 全是NO
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
#Slave_IO_Running: No
查看日志 Slave failed to initialize relay log info structure from the repository
解决:
mysql> reset slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.201',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='opslove',MASTER_AUTO_POSITION=1;
mysql> start slave;
Slave_SQL_Running: No 请看上面
Slave_IO_Running: NO 有可能是 id没配置好 两台服务器有个默认的为100的id没注释掉
slave时报错Slave failed to initialize relay log info structure from the repository
如何修复GTID复制错误
https://www.hi-linux.com/posts/47176.html
Slave_SQL_Running: No mysql同步故障解决方法
mysql 主从备库重起初始化relay log 失败的处理
要根本 see also 再优化一下 主从
see also
MySQL5.7.18 基于GTID的复制搭建 （on Cent6.5
维笔记36 mysql的一主多从模型（原始主从复制，基于GTID主从复制）
