GTID主从复制
GTID是对传统基于binary log的复制进行了增强，在binary log复制方式中，我们必须手动跟踪主服务器的日志名称和位置；但在GTID工作方式下面，我们无需跟踪这两个值，取而代之的是由MySQL自动跟踪它们，并使用GTID来标记哪些事务已经被处理，哪些还没有被处理。GTID的一个优点是：相同GTID的事务不会被重复处理，好处是可以最大限度地确保数据的一致性。
想了解更多gtid和binlog：https://www.jianshu.com/p/63efedc95822
根据GTID搭建MySQL主从
比较喜欢docker，所以这次环境搭建也是用Docker部署
1、使用docker-compose部署两个mysql
version: '3.6'
services:
master:
image: mysql:8.0.25
restart: always
container_name: mysql-master
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: root
command:
--default-authentication-plugin=mysql_native_password
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
--max_allowed_packet=128M
--sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
ports:
- 3306:3306
slaver:
image: mysql:8.0.25
restart: always
container_name: mysql-slaver
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: root
command:
--default-authentication-plugin=mysql_native_password
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
--max_allowed_packet=128M
--sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
ports:
- 3307:3306
#Are you trying to mount a directory onto a file or vice-versa
#docker 挂载只能挂载目录，不能挂载文件，如果挂载的是文件，则docker会把他当成一个目录，对于挂载的文件需要先创建
#mysql8默认加密方式cahing_sha2_password，旧版navicate不支持，可以使用mysql5.7的加密方式mysql_native_password
#也可以在mysql中执行
#ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; #修改加密规则
#ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #更新一下用户的密码
#FLUSH PRIVILEGES; #刷新权限 。%表示远程连接
2、修改配置文件
master数据库
# 进入MySQL容器
~ docker exec -it mysql-master bash
# 编辑mysql配置文件
cat >> /etc/mysql/my.cnf << EOF
#GTID:
server_id=1 #服务器id
gtid_mode=on #开启gtid模式
enforce_gtid_consistency=on #强制gtid一致性，开启后对于特定create table不被支持
#binlog
log_bin=master-binlog
log-slave-updates=1
binlog_format=row #强烈建议，其他格式可能造成数据不一致
#relay log
skip_slave_start=1
max_connect_errors=1000
#default_authentication_plugin = 'mysql_native_password'
# 结束
EOF
slave数据库
# 进入MySQL容器
~ docker exec -it mysql-slaver bash
# 编辑mysql配置文件
cat >> /etc/mysql/my.cnf << EOF
#GTID:
gtid_mode=on
enforce_gtid_consistency=on
server_id=2
#binlog
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row #强烈建议，其他格式可能造成数据不一致
*#relay log*
skip_slave_start=1
EOF
3、搭建主从复制
主库执行
# 进入数据库
mysql -uroot -proot;
# 创建有复制权限的账号
create user 'repl'@'%' identified by 'repl';
grant replication slave,replication client on *.* to 'repl'@'%';
# 刷新权限
flush privileges;
从库执行
# 使用前面创建的账号连接主数据库测试下
# docker中同意网络中可以只用容器名代替ip，docker会使用自己的dns解析
mysql -urepl -prepl -hmysql-slave -P 3306
# 进入数据库
mysql -uroot -proot;
# 从库连接主数据库
CHANGE MASTER TO
MASTER_HOST='mysql-master',
master_user='repl',
master_password='repl',
master_port=3306,
MASTER_AUTO_POSITION = 1;
# 启动从库进程
start slave;
查看从库启动状态，从数据库执行 show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000002
Read_Master_Log_Pos: 1135
Relay_Log_File: mysql-103-relay-bin.000002
Relay_Log_Pos: 1357
Relay_Master_Log_File: master-binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1135
Relay_Log_Space: 1569
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 135
Master_UUID: 1c8f7f48-4a7c-11ea-ab18-0800278ffd3d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1c8f7f48-4a7c-11ea-ab18-0800278ffd3d:1-4
Executed_Gtid_Set: 1c8f7f48-4a7c-11ea-ab18-0800278ffd3d:1-4,
846de782-4a7c-11ea-95ab-080027e75c4d:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
Slave_IO_Running是Yes状态，Slave_SQL_Running是Yes状态则表示成功
查看主从数据库binlog日志复制情况 show master status\G；
*************************** 1. row ***************************
File: master-binlog.000001
Position: 475696
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: f0c7abf2-f548-11eb-a45d-0242ac190002:1-1128
1 row in set (0.00 sec)
*************************** 1. row ***************************
File: slave-binlog.000001
Position: 475696
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: f0c7abf2-f548-11eb-a45d-0242ac190002:1-1128
1 row in set (0.00 sec)
正常情况下两边binlog日志pos是相同的
4、主从不一致解决
方法一：忽略错误
# 从数据库关闭复制
stop slave;
# 表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
# 查看数据库情况
show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
则正常
方法二：重新做主从，完全同步
1.先进入主库，进行锁表，防止数据写入
使用命令：
mysql> flush tables with read lock;
注意：该处是锁定为只读状态，语句不区分大小写
2.进行数据备份
#把数据备份到mysql.bak.sql文件
[root@server01 mysql]#mysqldump -uroot -proot -hmysql-master > mysql.bak.sql
这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失
3.查看master 状态
mysql> show master status;
4.把mysql备份文件传到从库机器，进行数据恢复
5.停止从库的状态
mysql> stop slave;
6.然后到从库执行mysql命令，导入数据备份
mysql> source /mysql.bak.sql
7.设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项
#关闭自动pos
mysql> change master to master_auto_position=0;
# 同步主从数据库pos
mysql> change master to master_host = 'mysql-master', master_user = 'repl', master_port=3306, master_password='repl', master_log_file = 'mysql-master.000001', master_log_pos=411148;
# 开启自动pos
mysql> change master to master_auto_position=1;
8.重新开启从同步
mysql> start slave;
9.查看同步状态
mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
好了，同步完成啦。
10.同步完成
unlock tables;
