基于mysql5.7主从同步实现不停机修复主从同步问题_pekeka的博客-优快云博客
GTID (Global Transaction ID)是全局事务ID,当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务，他采用了新的复制协议，旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。
GTID比传统复制的优势：
1.更简单的实现failover，不需要找log_file和log_Pos 2.更简单的搭建主从复制 3.比传统复制更加安全 4.GTID是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过
配置GTID主从
192.168.88.67 master mysql5.7
192.168.88.68 slave mysql5.7
配置master
[root@master ~]# mysql -uroot -p mysql> grant replication slave on *.* to 'pekeka'@'192.168.88.%' identified by 'GuangZhou_123'; #给用户pekeka授权 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit [root@master ~]# vim /etc/my.cnf ... log-bin=mysql-bin #开启二进制日志 server-id=1 #服务器ID，必须唯一 gtid-mode=on #开启gtid模式 enforce-gtid-consistency=on #强制gtid一致性，开启后对特定的create table不支持 binlog-format=row #默认为mixed混合模式，更改成row复制，为了数据一致性 log-slave-updates=1 #从库binlog记录主库同步的操作日志 skip-slave-start=1 #跳过slave复制线程 [root@master ~]# systemctl restart mysqld [root@master ~]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since 二 2022-11-22 18:34:08 CST; 4s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html [root@master ~]# mysql -uroot -p 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 | +----------------------------------+-----------+
配置slave
[root@slave ~]# vim /etc/my.cnf ... log-bin=mysql-bin server-id=2 gtid-mode=on enforce-gtid-consistency=on binlog-format=row log-slave-updates=1 skip-slave-start=1 [root@slave ~]# systemctl restart mysqld [root@slave ~]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since 二 2022-11-22 10:39:48 CST; 2s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 2415 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) [root@slave ~]# mysql -uroot -p 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 | +----------------------------------+-----------+
建立主从同步
[root@slave ~]# mysql -upekeka -pGuangZhou_123 -h 192.168.88.67 #测试是否可以远程连接主数据库 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 3 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 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>exit [root@slave ~]# mysql -uroot -p mysql> change master to master_host='192.168.88.67', master_user='pekeka', master_password='GuangZhou_123', master_auto_position=1; mysql> show slave status-> \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.88.67 Master_User: pekeka Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes
测试GTID主从同步
[root@master ~]# mysql -uroot -p
CREATE DATABASE BOOK;
USE BOOK;
mysql>CREATE TABLE book (name char(66),price int,pages int);
mysql>INSERT INTO book(name,price,pages) VALUES('Linux','30','666');
mysql>INSERT INTO book(name,price,pages) VALUES('Cloud Computing','60','666');
mysql>INSERT INTO book(name,price,pages) VALUES('Operation System','80','666');
mysql>INSERT INTO book(name,price,pages) VALUES('Artificial Intelligence','166','666');
mysql>select * from book;
+-------------------------+-------+-------+
| name | price | pages |
+-------------------------+-------+-------+
| Linux | 30 | 666 |
| Cloud Computing | 60 | 666 |
| Operation System | 80 | 666 |
| Artificial Intelligence | 166 | 666 |
+-------------------------+-------+-------+
[root@slave ~]# mysql -uroot -p
mysql> use BOOK
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from book;
+-------------------------+-------+-------+
| name | price | pages |
+-------------------------+-------+-------+
| Linux | 30 | 666 |
| Cloud Computing | 60 | 666 |
| Operation System | 80 | 666 |
| Artificial Intelligence | 166 | 666 |
+-------------------------+-------+-------+
4 rows in set (0.00 sec)
GTID主从同步实现！
手动关系主从关系
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.88.67
Master_User: pekeka
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1620
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 1833
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
#######在主库创建数据后从库开启主从同步查看是否同步
mysql> INSERT INTO book(name,price,pages) VALUES('xinlizui','600','888');
#######从库重新开启主从同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.88.67
Master_User: pekeka
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1892
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 726
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> use BOOK;
Database changed
mysql> select * from book；
+-------------------------+-------+-------+
| name | price | pages |
+-------------------------+-------+-------+
| Linux | 30 | 666 |
| Cloud Computing | 60 | 666 |
| Operation System | 80 | 666 |
| Artificial Intelligence | 166 | 666 |
| xinlizui | 600 | 888 |
+-------------------------+-------+-------+
主从同步完成恢复！！！
传统主从和GTID主从最大的不同就是当从数据库发生故障的时候，而主服务器还在运行，从服务器不需要重新加载master_log_file和master_log_pos的步骤。这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。
扫一扫
417
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
