Evan：/* 请移步 Docker搭建MySQL主从 */

2021-08-18T11:53:57Z

请移步 Docker搭建MySQL主从

新页面

=请双击移步 [[Docker搭建MySQL主从]]=

=Note: pre=
请移步 [[Docker搭建MySQL主从]]

<pre>
你可以使用基于语句的或基于行的复制与 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

</pre>
[https://dbaplus.cn/news-11-857-1.html 与MySQL传统复制相比，GTID有哪些独特的复制姿势]

有关数据导出，主从库数据同步请见[[Mysql5.6主从 replication同步]]

=主从配置开始=

==修改MySQL主配置文件==
配置 MySQL 基于GTID的复制，主要是需要在 MySQL 服务器的主配置文件 [mysqld] 段中添加以下内容：

<pre>
[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

</pre>

==修改MySQL slave配置文件==
<pre>
[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
</pre>
[https://blog.csdn.net/weixin_30603633/article/details/101862950 mysql主从仅同步特定库]

https://www.cnblogs.com/new-journey/p/11319527.html

==重启MySQL服务器==
== 这个有空看看==
<pre>

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; </pre>

==创建具有复制权限的用户==
基于 GTID 的复制会自动地将没有在从库执行过的事务重放，所以不要在其它从库上建立相同的账号。如果建立了相同的账户，有可能造成复制链路的错误。

<pre>
# 在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
</pre>

==查看主库与从库的GTID是否开启==
<pre>
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)

</pre>
===简单说下几个常用参数的作用 ===

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==
<pre>mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 05a0b627-9b9d-11e8-9f72-5e295b78c544 |
+---------------+--------------------------------------+
</pre>
==查看主服务器状态==
<pre>
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 | | | |
+-----------+----------+--------------+------------------+-------------------+

</pre>

== 启动gtid复制：从库上操作 202 203==
<pre>
#从库连接至主库 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 |
+-----------+------+------+-----------+--------------------------------------+

</pre>

==测试GTID主从复制==
<pre>

在主库(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;

检查从服务器状态

</pre>

==trouble shooting==

===机器断电后 Slave_SQL_Running: No===
<pre>
问题:
机器断电后 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
</pre>

https://www.52os.net/articles/injecting-empty-transactions-repair-mysql-5-6-gtid-replication.html

[https://www.52os.net/articles/injecting-empty-transactions-repair-mysql-5-6-gtid-replication.html 插入空事务修复mysql 5.6 GTID复制错误]

===另一个slave 问题断电后全是NO ===
<pre>
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没注释掉
</pre>
[https://blog.csdn.net/weixin_37998647/article/details/79950133 slave时报错Slave failed to initialize relay log info structure from the repository]

===如何修复GTID复制错误===

https://www.hi-linux.com/posts/47176.html

[https://blog.csdn.net/wll_1017/article/details/70332107 mysql开启GTID跳过错误的方法]

[http://blog.sina.com.cn/s/blog_4da051a60102wx3b.html MySQL开启GTID同步跳过SLAVE错误]

[https://yq.aliyun.com/articles/155827 MySQL GTID 主从复制错误修复方法]

[http://blog.51cto.com/hnr520/1883282 解决mysql开启GTID主从同步出现1236错误问题]

[https://yq.aliyun.com/articles/52497 Slave_SQL_Running: No mysql同步故障解决方法]

[https://blog.csdn.net/wll_1017/article/details/70332107 mysql开启GTID跳过错误的方法]

[https://zhuanlan.zhihu.com/p/33033342 mysql 主从备库重起初始化relay log 失败的处理]

要根本 see also 再优化一下主从

=see also=
[[CentOS7上源码编译安装MySQL5.7]]

[https://www.hi-linux.com/posts/47176.html MySQL 5.7基于GTID的主从复制实践]

[https://my.oschina.net/xxj123go/blog/888000 MySQL5.7.18 基于GTID的复制搭建（on Cent6.5]

[https://yuerblog.cc/2017/09/15/mysql57-gtid-master-slave/ mysql5.7基于GTID搭建主从]

[http://keithlan.github.io/2016/06/23/gtid/ MySQL5.7杀手级新特性：GTID原理与实战]

[https://blog.csdn.net/No_red/article/details/67005797 维笔记36 mysql的一主多从模型（原始主从复制，基于GTID主从复制）]

[http://blog.51cto.com/yanghongwei/1890797 mysql5.7基于GTID实现主从复制]

[https://www.58jb.com/html/142.html Mysql基于gtid复制的半同步]

http://keithlan.github.io/2016/06/23/gtid/

[https://wenku.baidu.com/view/1280d2204b35eefdc8d33353.html?rec_flag=default mysql主从日常管理维护]

[[category:ops]] [[category:mysql]]

Mysql5.7 主从 Replication Using GTID - 版本历史

Evan：​/* 请移步 Docker搭建MySQL主从 */

Evan：/* 请移步 Docker搭建MySQL主从 */