MySQL 8如何通过gtid来配置主从同步数据库
Contents
一背景说明
在前面，我们通过手工的方式，一步一步配置了MySQL 8.0的master slave主从同步数据库。在配置过程中，我们需要先在主库上执行show master status\G获取主库上的binlog的信息(当前写的几号binlog文件，以及该binlog的position位置信息)，然后再在备库上执行change master to xxx时，也需要显示指定master_log_file和master_log_pos的参数。配置起来，稍显麻烦。那么，有没有更简便的其它方法呢？有，就是通过gtid来实现。
本文档记录在CentOS 7上通过gtid的方式来配置MySQL 8.0.31一主一从数据库同步复制环境，其中主库上已有业务数据库且有业务数据，且备库环境是全新的场景下，执行的配置过程。
机器信息：
| IP | 系统版本 | 内核版本 | 角色 | MySQL版本 |
|---|---|---|---|---|
| 172.16.17.56 | CentOS Linux release 7.5.1804 (Core) | 3.10.0-862.el7.x86_64 x86_64 | master | 8.0.31 |
| 172.16.17.57 | CentOS Linux release 7.5.1804 (Core) | 3.10.0-862.el7.x86_64 x86_64 | slave | 8.0.31 |
二配置步骤
0 思路和流程
主库开启gtid：配置文件/etc/my.cnf中指定gtid_mode=on和enforce_gtid_consistency=on参数，并重启主库；
主库创建同步用户并授权：
主库备份数据库：mysqldump –all-databases -uroot -p’password’ –flush-logs –source-data=1 >all.dmp
传递备份文件到备库服务器：scp all.dmp
备库同样开启gtid:配置文件/etc/my.cnf中指定gtid_mode=on和enforce_gtid_consistency=on参数，并重启备库；
备库恢复主库备份的数据：
备库停止slave进程；
备库开启同步：CHANGE MASTER TO MASTER_HOST=’172.16.17.56′ ,MASTER_USER=’replica’ ,MASTER_PASSWORD=’Onlyou_168′,GET_MASTER_PUBLIC_KEY=1 ,master_auto_position=1;
备库开启slave同步进程：
备库查看slave状态：
主、备库校验数据同步：
1 主库开启gtid，并重启主库
[root@host56 ~]# vi /etc/my.cnf
...
# master server-id
server-id=56
#主库添加gtid
gtid_mode=on
enforce_gtid_consistency=on
...
[root@host56 ~]# systemctl restart mysqld
[root@host56 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 日 2022-10-30 11:58:03 CST; 4s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 24810 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 24834 (mysqld)
Status: "Server is operational"
Tasks: 39
Memory: 357.3M
CGroup: /system.slice/mysqld.service
└─24834 /usr/sbin/mysqld
10月 30 11:58:01 host56 systemd[1]: Starting MySQL Server...
10月 30 11:58:03 host56 systemd[1]: Started MySQL Server.
[root@host56 ~]# mysql -uroot -p'Onlyou_168';
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | d3c90e61-5806-11ed-b96a-005056a59231:1-3 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------+
8 rows in set (0.01 sec)
mysql> 2 主库创建同步用户并授权
mysql> CREATE USER 'replica'@'172.16.17.57' identified WITH caching_sha2_password by 'Onlyou_168'; Query OK, 0 rows affected (0.09 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.17.57'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
3 主库备份数据库
master创建测试数据库和数据：
mysql> create database testdb; Query OK, 1 row affected (0.01 sec) mysql> create table testdb.t1(id int); Query OK, 0 rows affected (0.18 sec) mysql>
master备份数据库：
[root@host56 ~]# mysqldump --all-databases -uroot -p'Onlyou_168' --flush-logs --source-data=1 >all.dmp mysqldump: [Warning] Using a password on the command line interface can be insecure. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [root@host56 ~]# ll all.dmp -rw-r--r--. 1 root root 1279715 10月 30 18:24 all.dmp [root@host56 ~]#
4 传递备份文件到备库服务器
[root@host56 ~]# scp all.dmp root@172.16.17.57:/root/mysql8.0/ all.dmp 100% 1250KB 92.7MB/s 00:00 [root@host56 ~]#
5 备库同样开启gtid
:配置文件/etc/my.cnf中指定gtid_mode=on和enforce_gtid_consistency=on参数，并重启备库；
[root@localhost mysql8.0]# vi /etc/my.cnf ... server-id=57 gtid_mode = on enforce_gtid_consistency = on ... ~ "/etc/my.cnf" 36L, 1304C written [root@localhost mysql8.0]#
6 备库恢复主库备份的数据：
[root@localhost mysql8.0]# mysql -uroot -p'Onlyou_168'; mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.31 MySQL Community Server - GPL Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> source all.dmp ... Query OK, 0 rows affected (0.00 sec) mysql>
7 备库停止slave进程；
备库开启同步：CHANGE MASTER TO MASTER_HOST=’172.16.17.56′ ,MASTER_USER=’replica’ ,MASTER_PASSWORD=’Onlyou_168′,GET_MASTER_PUBLIC_KEY=1 ,master_auto_position=1;
mysql> stop slave; mysql> CHANGE MASTER TO MASTER_HOST='172.16.17.56' ,MASTER_USER='replica' ,MASTER_PASSWORD='Onlyou_168',GET_MASTER_PUBLIC_KEY=1 ,master_auto_position=1; Query OK, 0 rows affected, 8 warnings (0.40 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.06 sec) mysql>
8 备库开启slave同步进程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql>
9 备库查看slave状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.16.17.56
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000005
Read_Master_Log_Pos: 197
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: binlog.000005
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: 197
Relay_Log_Space: 581
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: 56
Master_UUID: d3c90e61-5806-11ed-b96a-005056a59231
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Executed_Gtid_Set: 427d9a27-583e-11ed-9ace-005056a5a6d3:1-3,
d3c90e61-5806-11ed-b96a-005056a59231:1-8
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> 10 主、备库校验数据同步：
主库插入测试数据：
mysql> insert into testdb.t1 values(101); Query OK, 1 row affected (0.06 sec) mysql> select * from testdb.t1; +------+ | id | +------+ | 101 | +------+ 1 row in set (0.00 sec) mysql>
备库查看数据同步情况：
mysql> select * from testdb.t1; +------+ | id | +------+ | 101 | +------+ 1 row in set (0.00 sec) mysql>
三参考链接
参考链接：
MySQL官方文档：https://dev.mysql.com/doc/mysql-replication-excerpt/5.6/en/replication-gtids-concepts.html
GTIDs are always preserved between source and replica. This means that you can always determine the source for any transaction applied on any replica by examining its binary log. In addition, once a transaction with a given GTID is committed on a given server, any subsequent transaction having the same GTID is ignored by that server. Thus, a transaction committed on the source can be applied no more than once on the replica, which helps to guarantee consistency.
When GTIDs are in use, the replica has no need for any nonlocal data, such as the name of a file on the source and a position within that file. All necessary information for synchronizing with the source is obtained directly from the replication data stream. From the perspective of the database administrator or developer, GTIDs entirely take the place of the file-offset pairs previously required to determine points for starting, stopping, or resuming the flow of data between source and replica. This means that, when you are using GTIDs for replication, you do not need (or want) to include
MASTER_LOG_FILEorMASTER_LOG_POSoptions in theCHANGE MASTER TOstatement used to direct a replica to replicate from a given source; in place of these options, it is necessary only to enable theMASTER_AUTO_POSITIONoption introduced in MySQL 5.6.5. For the exact steps needed to configure and start sources and replicas using GTID-based replication, see Section 2.3.2, “Setting Up Replication Using GTIDs”.
GTID是global transaction identifier,主库上产生的每一个事务，都和一个全局唯一的gtid相对应。这样，备库将来就可以依据这个gtid事务号来在本地通过应用事务来实现和主库同步。并且相同的gtid在备库上是不被允许的，也就是说，拥有相同gtid的2个事务，在备库上只有第1个被执行，其余的将被忽略。
另外，通过gtid来配置master-slave了架构的额数据库时，在备库端，不再需要向以前那样指定master的binlog信息：比如，备库使用change master to来配置和主库同步的命令中，不再需要使用master_log_file和master_log_pos，转而只需要指定1个参数master_auto_position=1就可以了。
补充：gtid也有其自身局限性，比如在8.0.21版本之前，使用gtid-based replication，主库上执行的CTAS，create table as select不被允许。因为这条语句可能会产生不同的gtid,select从句产生1个，create table 产生1个。将来无法放到备库上执行。
关于gtid更多的限制信息，可以查看官方文档：https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-gtids-restrictions.html
相关阅读：
一条评论
Pingback: