共计 6852 个字符,预计需要花费 18 分钟才能阅读完成。
前几天,有读者在后盾留言问我可有基于 Gtid 的 Mysql 主从同步的文章,我记得历史文章应该有提及过,也有可能是只是提及,可能没有具体的过程介绍,所以,明天,民工哥就给大家安顿一波。
什么是 GTID?
- 1、全局惟一,一个事务对应一个 GTID
- 2、代替传统的 binlog+pos 复制;应用 master_auto_position= 1 主动匹配 GTID 断点进行复制
- 3、MySQL5.6 开始反对
- 4、在传统的主从复制中,slave 端不必开启 binlog;然而在 GTID 主从复制中,必须开启 binlog
- 5、slave 端在承受 master 的 binlog 时,会校验 GTID 值
- 6、为了保障主从数据的一致性,多线程同时执行一个 GTID
组成
Master_UUID: 序列号举例:ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5ceb0ca3d-8366-11e8-ad2b-000c298b7c9a 其实就是 master 的 uuid 值;1- 5 是序列号,每次一个事务实现都会自增 1,也就是说下一次为 1 -6。
工作原理
- 1、master 更新数据时,会在事务前产生 GTID,一起记录到 binlog 日志中。
- 2、slave 端的 i /o 线程将变更的 binlog,写入到本地的 relay log 中。
- 3、sql 线程从 relay log 中获取 GTID,而后比照 slave 端的 binlog 是否有记录。
- 4、如果有记录,阐明该 GTID 的事务曾经执行,slave 会疏忽。
- 5、如果没有记录,slave 就会从 relay log 中执行该 GTID 的事务,并记录到 binlog。
- 6、在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全副扫描
GTID 主从配置
版本:MySQL5.7
配置 master
vim /etc/my.cnf | |
[client] | |
socket=/usr/local/mysql/mysql.sock | |
[mysqld] | |
basedir=/usr/local/mysql | |
datadir=/usr/local/mysql/data | |
user=mysql | |
pid-file=/usr/local/mysql/data/mysqld.pid | |
log-error=/usr/local/mysql/data/mysql.err | |
socket=/usr/local/mysql/mysql.sock | |
port=3306 | |
server-id=1 | |
gtid-mode=ON | |
enforce-gtid-consistency=ON | |
server-id=1 | |
binlog_format=row | |
log-bin=/usr/local/mysql/data/mysql-bin | |
systemctl restart mysqld | |
firewall-cmd --add-port=3306/tcp --permanent | |
firewall-cmd --reload |
配置 slave
vim /etc/my.cnf | |
[client] | |
socket=/usr/local/mysql/mysql.sock | |
[mysqld] | |
basedir=/usr/local/mysql | |
datadir=/usr/local/mysql/data | |
user=mysql | |
pid-file=/usr/local/mysql/data/mysqld.pid | |
log-error=/usr/local/mysql/data/mysql.err | |
socket=/usr/local/mysql/mysql.sock | |
port=3306 | |
server-id=2 | |
gtid-mode=ON | |
enforce-gtid-consistency=ON | |
server-id=2 | |
binlog_format=ROW | |
log-bin=/usr/local/mysql/data/mysql-bin | |
log_slave_updates=ON | |
skip-slave-start=1 | |
systemctl restart mysqld | |
firewall-cmd --add-port=3306/tcp --permanent | |
firewall-cmd --reload |
master 受权配置
mysql -uroot -p | |
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123'; | |
mysql> flush privileges; |
slave 配置同步
mysql -uroot -p | |
mysql> change master to master_host='10.0.0.132', master_user='rep',master_password='123',master_port=3306,master_auto_position=1; | |
mysql> start slave; |
查看 slave 的状态
mysql> show slave status\G; | |
*************************** 1. row *************************** | |
Slave_IO_State: Waiting for master to send event | |
Master_Host: 10.0.0.132 | |
Master_User: rep | |
Master_Port: 3306 | |
Connect_Retry: 60 | |
Master_Log_File: mysql-bin.000003 | |
Read_Master_Log_Pos: 635 | |
Relay_Log_File: slave-relay-bin.000005 | |
Relay_Log_Pos: 848 | |
Relay_Master_Log_File: mysql-bin.000003 | |
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: 635 | |
Relay_Log_Space: 1308 | |
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: 1 | |
Master_UUID: ceb0ca3d-8366-11e8-ad2b-000c298b7c9a | |
Master_Info_File: /usr/local/mysql/data/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: ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-4 | |
Executed_Gtid_Set: ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-4 | |
Auto_Position: 1 | |
Replicate_Rewrite_DB: | |
Channel_Name: | |
Master_TLS_Version: | |
1 row in set (0.00 sec) |
呈现这两个 yes 示意同步胜利
通过 slave 的状态信息,能够看到 GTID 的值、Matser\_UUID 等信息
查看 master 状态
mysql> show master status; | |
+------------------+----------+--------------+------------------+------------------------------------------+ | |
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | | |
+------------------+----------+--------------+------------------+------------------------------------------+ | |
| mysql-bin.000003 | 635 | | | ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-4 | | |
+------------------+----------+--------------+------------------+------------------------------------------+ | |
1 row in set (0.00 sec) |
留神比照 slave 端,Executed\_Gtid\_Set 的值应该是一样的。
验证主从
master 上
mysql> create database test01; | |
Query OK, 1 row affected (0.00 sec) | |
mysql> show master status; | |
+------------------+----------+--------------+------------------+------------------------------------------+ | |
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | | |
+------------------+----------+--------------+------------------+------------------------------------------+ | |
| mysql-bin.000003 | 800 | | | ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5 | | |
+------------------+----------+--------------+------------------+------------------------------------------+ | |
1 row in set (0.00 sec) |
slave 上
mysql> show databases; | |
+--------------------+ | |
| Database | | |
+--------------------+ | |
| information_schema | | |
| mysql | | |
| performance_schema | | |
| sys | | |
| test01 | | |
+--------------------+ | |
5 rows in set (0.07 sec) | |
mysql> show slave status\G; | |
*************************** 1. row *************************** | |
Slave_IO_State: Waiting for master to send event | |
Master_Host: 10.0.0.132 | |
Master_User: rep | |
Master_Port: 3306 | |
Connect_Retry: 60 | |
Master_Log_File: mysql-bin.000003 | |
Read_Master_Log_Pos: 800 | |
Relay_Log_File: slave-relay-bin.000005 | |
Relay_Log_Pos: 1013 | |
Relay_Master_Log_File: mysql-bin.000003 | |
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: 800 | |
Relay_Log_Space: 1473 | |
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: 1 | |
Master_UUID: ceb0ca3d-8366-11e8-ad2b-000c298b7c9a | |
Master_Info_File: /usr/local/mysql/data/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: ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5 | |
Executed_Gtid_Set: ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5 | |
Auto_Position: 1 | |
Replicate_Rewrite_DB: | |
Channel_Name: | |
Master_TLS_Version: | |
1 row in set (0.00 sec) |
须要留神的是,GTID 的值在实现一次事务后,变成了 ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5(自增 1)
排障
思路
- a、确保 master 凋谢 3306 端口
- b、最好敞开 selinux
- c、master 上受权同步,slave 上 change master 命令指定 master 的信息不要写错
- d、UUID 问题
如果你呈现了上图所示的问题,示意你的 master 和 slave 的 UUID 是一样的,个别这种状况多呈现于克隆虚拟机
解决办法:
找到 slave 上的 MySQL 数据目录下的 auto.cnf 文件(这个文件其实是主动生成的 mysql 服务器的 UUID 值),将它删除,而后重启 MySQL,而后 MySQL 会从新生成一个 UUID。而后停掉 slave,从新开启就能够了(我的 mysql 的数据目录是在 /usr/local/mysql/data 下,详情查看 my.cnf 配置文件)
cd /usr/local/mysql/data | |
rm -f auto.cnf | |
systemctl restart mysql | |
[root@slave data]# cat auto.cnf | |
[auto] | |
server-uuid=020c7f26-be57-11e8-8e2d-000c29b63bad |
通过 cat 命令查看该文件,发现 UUID 曾经扭转
mysql -uroot -p | |
mysql> stop slave; | |
mysql> start slave; |
总结
排障过程中,留神须要停掉 slave,做完批改之后在开启,否则你的批改可能是不会失效的。
起源:https://blog.51cto.com/u_1343…
正文完