mysql5.7的GTID主从复制
参考：https://www.cnblogs.com/luckcs/articles/6295992.html
参考：https://www.cnblogs.com/kevingrace/p/5569652.html（很详细）
一、GTID复制
GTID是MySQL 5.6的新特性，其全称是Global Transaction Identifier，可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识一个事务。当事务提交时，MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID，然后再写事务的Binlog。主从同步时GTID_Event和事务的Binlog都会传递到从库，从库在执行的时候也是用同样的GTID写binlog，这样主从同步以后，就可通过GTID确定从库同步到的位置了。也就是说，无论是级联情况，还是一主多从情况，都可以通过GTID自动找点儿，而无需像之前那样通过File_name和File_position找点儿了。
摘抄：https://www.cnblogs.com/zejin2008/p/7705473.html
传统的基于binlog position复制的方式有个严重的缺点：如果slave连接master时指定的binlog文件错误或者position错误，会造成遗漏或者重复，
很多时候前后数据是有依赖性的，这样就会出错而导致数据不一致。
从MYSQL5.6开始，mysql开始支持GTID复制。GTID的全称是global transaction id，表示的是全局事务ID。GTID的分配方式为uuid:trans_id，其中：uuid是每个mysql服务器都唯一的，记录在$datadir/auto.cnf中。如果复制结构中，任意两台服务器uuid重复的话(比如直接冷备份时，auto.conf中的内容是一致的)，在启动复制功能的时候会报错。这时可以删除auto.conf文件再重启mysqld。
基于GTID主从复制的优点大致有：
- 保证同一个事务在某slave上绝对只执行一次，没有执行过的gtid事务总是会被执行。
- 不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标。
- 故障转移到新的master的时候很方便，简化了很多任务。
- 很容易判断master和slave的数据是否一致。只要master上提交的事务在slave上也提交了，那么一定是一致的。
- 当然，MySQL提供了选项可以控制跳过某些gtid事务，防止slave第一次启动复制时执行master上的所有事务而导致耗时过久。
- 虽然对于row-based和statement-based的格式都能进行gtid复制，但建议采用row-based格式。
二、GTID服务器相关选项
gtid_mode gtid模式
enforce_gtid_consistency 保证GTID安全的参数
三、安装：
初始化机器
安装mysql
[root@localhost mysql-5.7-onekey-install]# cat mysql-install.sh
#!/bin/bash
CURRENTDIR=`pwd`
NAME="mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz"
FULL_NAME=${CURRENTDIR}/${NAME}
DATA_DIR="/data/mysql/"
StandardOutput(){
echo -e "\033[1;32m$1 ... \033[0m"
}
ErrorOutput(){
echo -e "\033[1;31m$1 ... \033[0m"
}
is_empty_dir(){
return `ls -A $1 2>/dev/null | wc -l`
}
judge_mysql(){
if [ -e /usr/local/mysql ];then
ErrorOutput "/usr/local/mysql目录存在...Mysql 似乎已经安装...终止安装..."
exit 10
fi
which mysqld &>/dev/null
if [ "$?" -eq 0 ];then
ErrorOutput "mysqld命令存在...Mysql 似乎已经安装...终止安装..."
exit 20
fi
if ss -ltn | grep 3306 -q;then
ErrorOutput "3306端口被占用...Mysql 似乎已经安装...终止安装..."
exit 30
fi
is_empty_dir $DATA_DIR
if [ ! "$?" -eq 0 ];then
ErrorOutput "$DATA_DIR 不为空...终止安装..."
exit 40
fi
}
download_mysql(){
if [ -f ${FULL_NAME} ];then
StandardOutput "Mysql安装文件存在，开始安装..."
else
StandardOutput "Mysql安装文件不存在...开始下载..."
curl -o mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz https://mirrors.huaweicloud.com/mysql/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
if [ ! "$?" -eq 0 ];then
ErrorOutput "下载失败...终止安装..." && exit 50
fi
fi
}
mysql_install(){
if id mysql &>/dev/null;then
StandardOutput "mysql 用户已经存在，跳过创建用户过程..."
else
useradd -r -s /sbin/nologin mysql
fi
tar xf ${FULL_NAME} -C /usr/local/
ln -sv /usr/local/mysql-5* /usr/local/mysql
chown -R mysql.mysql /usr/local/mysql/
if [ ! -d $DATA_DIR ];then
mkdir -pv $DATA_DIR && chown -R mysql.mysql $DATA_DIR -R
fi
/usr/local/mysql/bin/mysqld --initialize --datadir=$DATA_DIR --user=mysql &>mysql_install.log
cp /usr/local/mysql-5.*-x86_64/support-files/mysql.server /etc/init.d/mysqld
chmod a+x /etc/init.d/mysqld
cp ${CURRENTDIR}/my.cnf /etc/my.cnf
echo 'export PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
# /etc/init.d/mysqld start
mkdir /var/lib/mysql /var/run/mysqld
chown mysql.mysql /var/lib/mysql/ /var/run/mysqld
systemctl daemon-reload
}
mysql_check(){
systemctl start mysqld
if ss -ltn | grep 3306 -q ;then
StandardOutput "VERY GOOD..."
rm -f ${CURRENTDIR}/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
fi
echo -e "请手动执行：\033[1;32msource /etc/profile\033[0m"
echo -e "当前mysql数据库密码为：\033[1;32m$( grep "temporary password" ${CURRENTDIR}/mysql_install.log | awk '{print $NF}')\033[0m"
}
main(){
judge_mysql
download_mysql
mysql_install
mysql_check
}
main
[root@localhost mysql-5.7-onekey-install]# bash mysql-install.sh
四、配置
主服务器
**enforce-gtid-consistency：**MySQL官方解释说当启用enforce_gtid_consistency功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporary table语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行：
参考：https://blog.csdn.net/qq_27371099/article/details/85339536
[mysqld]
server-id=1 #数据库唯一ID，主和从不能一样
log-bin=mysql-bin #开启binlog，binlog的基名为mysql-bin
gtid_mode=on #开启gtid_mode
binlog_format=row #binlog复制模式（mysql5.7默认即是，可不写），行模式（数据最安全，最占内存那个）
enforce_gtid_consistency=on #
mysql> grant replication slave on *.* to 'repluser'@'192.168.38.%' identified by 'WWQD88OYrqSxLg';
mysql> flush privileges;
从服务器
[mysqld]
servir-id=2
gtid-mode=on
enforce_gtid_consistency=on
mysql> CHANGE MASTER TO MASTER_HOST='192.168.38.131',MASTER_USER='repluser',MASTER_PASSWORD='WWQD88OYrqSxLg',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
五、两个yes表示OK
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
六、测试
#这边创建个库
mysql> create database dba;
#另一边没事看看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dba |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
注意事项：
1、change master to那一行那么长不要记，用help查看
mysql> HELP CHANGE MASTER TO
2、如果master的信息填错了，不要慌
mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.38.131',MASTER_USER='repluser',MASTER_PASSWORD='WWQD88OYrqSxLg',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
3、判断slave是否连上
mysql> show slave status\G
显示两个yes表示OK
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4、如何跳过某些错误、
比如有实习员工误把从数据库当主数据库操作，创建数据库db1，然后主数据库又创建数据库db1
#从服务器创建db1
mysql> create database db1;
#主数据库创建db1
mysql> create database db1;
#从数据库查看status
mysql> show slave status\G
......
Slave_IO_Running: Yes
Slave_SQL_Running: No
......
Last_Error: Error 'Can't create database 'db1'; database exists' on query. Default database: 'db1'. Query: 'create database db1'
......
Retrieved_Gtid_Set: 9052644d-dc6d-11e9-9d0a-000c29011bc1:1-4
Executed_Gtid_Set: 9052644d-dc6d-11e9-9d0a-000c29011bc1:1-3
......
Retrieved_Gtid_Set是slave接收到的事务的信息，
Executed_Gtid_Set是slave已经执行的slave的信息
所以多个一个已经执行过的事务,跳过它即可，因为执行过了，不需要再次执行
#主节点看自己的binlog
[root@localhost ~]# mysqlbinlog -vvv /data/mysql/mysql-bin.000004 | grep '9052644d-dc6d-11e9-9d0a-000c29011bc1'
# 9052644d-dc6d-11e9-9d0a-000c29011bc1:1-3
SET @@SESSION.GTID_NEXT= '9052644d-dc6d-11e9-9d0a-000c29011bc1:4'/*!*/;
#从节点操作
mysql> SET @@SESSION.GTID_NEXT= '9052644d-dc6d-11e9-9d0a-000c29011bc1:4';
mysql> BEGIN; COMMIT; #设置空事务
mysql> SET SESSION GTID_NEXT = AUTOMATIC;
mysql> START SLAVE;
参考：https://blog.csdn.net/wll_1017/article/details/70332107
参考：https://www.cnblogs.com/luckcs/articles/6295992.html
5、如果错误太多，可以考虑重新部署slave节点，
可以在晚上访问量少的时候，将主节点数据mysqldump恢复到slave节点，再重新部署主从同步
6、主从复制的选项总结
server_id = 1 #mysql唯一标识符
gtid_mode = on #开启gtid模式
enforce_gtid_consistency = on #gtid的安全选项，官方说必须开，no transaction is allowed to violate GTID consistency
#binlog
log_bin = mysql-bin #开启binlog，且binlog文件的基名为mysql-bin
log-slave-updates = 1 #官方的解释是：从服务器收到主服务器的更新是否应记录到自己的二进制日志中，这样从服务器后面可以继续接一个从服务器
binlog_format = row #binlog复制模式，建议行模式，
sync-master-info = 1 #默认值为10000。为1表示每个EVENT执行后都进行同步到磁盘的操作，会增大磁盘IO
sync_binlog = 1 #控制MySQL服务器将二进制日志同步到磁盘的频率。默认为1，表示在提交事务之前启用二进制日志到磁盘的同步，增加磁盘IO，但最安全
#relay log
skip_slave_start = 1 # 服务器启动时要不要启动从线程；默认是OFF，开启后服务器重启mysql不再会自动开启从线程，而是需要手动执行start slave;官方文档如是说
7、相关命令
mysql> show master status; #可以查看master数据库当前使用的禁止日志和当前执行二进制日志位置，Binlog_Do_DB是数据库白名单，Binlog_Ignore_DB是数据库黑名单，Executed_Gtid_Set是已经执行的gtid事务
mysql> show master logs; #查看master数据库的二进制日志
mysql> show variables like '%uuid%'; #查看当前数据库的UUID
mysql> show slave status\G #在从服务上查看同步信息，可以看到master的IP，用户，端口，binlog文件，同步状态，slave接收到的事务信息，已经执行的事务信息
mysql> show variables like 'log_bin'; #查看binlog开启状态，只能通过配置文件开启
grant replication slave on *.* to 'repluser'@'192.168.38.%' identified by 'WWQD88OYrqSxLg'; #创建用于主从复制的用户，还要刷新权限
mysql> reset slave all; #清除所有slave的设置，可以用于重新change master ，也可以用于slave升master
mysql> show binary logs; #查看binlog文件名和大小
8、如何清除binlog日志
切记不要手动删，可以设置binlog的自动清理
默认应该是0，不自动清理
# vim /etc/my.cnf //修改expire_logs_days,x是自动删除的天数，一般将x设置为短点，如10
expire_logs_days = x //二进制日志自动删除的天数。默认值为0,表示“没有自动删除”
清除所有binlog，让binlog重新计数
mysql> reset master；
清除指定二进制
mysql 5.7的帮助中，写着支持删除指定某个binlog之前的日志 或者 删除某个时间点前的binlog
mysql> SHOW BINARY LOGS; #查看binlog文件名和大小
mysql> show master status; #查看当前使用的binlog文件名
mysql> PURGE MASTER LOGS BEFORE '2019-09-22 00:00:00'; #删除2019-09-22 00:00:00之前的binlog
mysql> PURGE BINARY LOGS TO 'mysql-bin.000008'; #删除mysql-bin.000008之前的binlog日志
9、相关概念
主从复制线程：
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系
10、从服务器一定不要修改数据库
这个很关键
11、主主复制不推荐使用
很容易导致数据不一致