.MySQL数据库主从复制
数据库主从复制
Mysql的主从架构模式，是很多企业⼴泛使⽤，并且是⼴为熟知的⼀种架构模式，这是 DBA 所应该熟练掌握的技
能。
1.mysql主从复制主要⽤途
a.⽤于备份，避免影响业务
b.实时灾备，⽤于故障切换
c.读写分离，提供查询服务
2.mysql主从复制存在的问题
a.主库宕机后, 数据可能丢失
b.主库写压⼒⼤, 复制可能会延时
3.mysql主从复制解决⽅法
a. 半同步复制 、或者 全同步复制 .要求: Mysql5.7版本
b. 并⾏复制 , 解决从库复制延迟的问题. 建议: 5.7版
半同步复制是指主库在把数据写⼊⼆进制⽇志之前，等待⾄少⼀个从库确认收到数据。
全同步复制是指主库在把数据写⼊⼆进制⽇志之前，等待所有从库确认收到数据。这种⽅法需要MySQL
5.7版本以上⽀持。
4 .mysql主从复制原理
0.依赖 主库必须打开binlog
1.在主库上把将更改 DDL DML DCL 记录到⼆进制⽇志 Binary Log 中。
2.备库 I/O 线程将主库上的⼆进制⽇志复制到⾃⼰的中继⽇志 Relay Log 中。
3.备库 SQL 线程读取中继⽇志中的事件，将其重放到备库数据库之上。
.MySQL****数据库传统主从复制
192.168.107.133 master1
192.168.107.134 slave1
//1.配置⽂件/etc/my.cnf
log-bin
server-id = 1
//2.重启mysql服务⽣效
[root@Master ~]# /etc/init.d/mysqld restart
//3.模拟线上数据库
mysql> create database winglinuxdb;
mysql> use winglinuxdb;
mysql> create table t1(id int, name varchar(20));
mysql> insert into t1 values (1,'wing');
mysql> insert into t1 values (2,'wingsre');
mysql> select * from t1;
vx: WingspanGo
vx: WingspanGovx: WingspanGo
+------+---------+
| id | name |
+------+---------+
| 1 | wing |
| 2 | wingsre |
+------+---------+
//4.授权, 允许能够远程连接的主机(replicaiton)
mysql> grant replication slave, replication client on *.*
to 'rep'@'10.1.106.%' identified by 'WingRep@123';
mysql> flush privileges;
//5.导出当前数据
[root@Master ~]# mysqldump -uroot -p'Wing@123' \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/db-$(date +%F)-all.sql
//6.重置 binlog,如果是新数据库不需要重制binlog,此步骤在⽣产谨慎执⾏
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
//7.将备份⽂件传送⾄Slave
[root@Master ~]# scp /backup/db-$(date +%F)-all.sql root@10.1.106.66:/backup/
[root@slave ~]# mysql -hmaster1 -urep -pWingRep@123
//2.修改配置⽂件/etc/my.cnf, 从需开启binlog
server-id=2
[root@slave ~]#/etc/init.d/mysqld restart
//4.导⼊数据,追master的bin_log
[root@Slave ~]# mysql -uroot -p'Wing@123' -e "source /backup/db-$(date +%F)-all.sql"
//5.指向Master,⽆需指定binlogfile和pos
[root@Slave ~]# sed -n '22p' db-2023-05-31-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000024', MASTER_LOG_POS=154;
// 如果master是新的数据库执⾏,这⾥跟上⽂reset master做关联,模拟是⼀个新的数据库
mysql> change master to
master_host='master1',
master_user='rep',
master_password='WingRep@123';
//如果master已经运⾏很久了 需要查找binlog位置进⾏配置
mysql> change master to
master_host='master1',
master_user='rep',
master_password='WingRep@123',
master_log_file='sql_106_70.000005',
master_log_pos=154;
mysql> start slave;
mysql -uroot -p'zgw123' -e "source /backup/db-$(date +%F)-all.sql"
[root@zgw backup]# ll
总用量 840
-rw-r--r-- 1 root root 858208 8月 18 19:25 db-2024-08-18-all.sql
[root@zgw backup]# sed -n '22p' db-2024-08-18-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='sql_107_133.000004', MASTER_LOG_POS=154;
MySQL数据库Gtid****复制
MySQL GTID（Global Transaction ID）复制是⼀种基于事务ID的复制⽅式，它可以解决主从复制中的⼀些问题，
例如主从复制延迟和数据丢失等。
在MySQL GTID复制中，每个事务都有⼀个全局唯⼀的ID，称为GTID。主库将GTID记录到⼆进制⽇志中，从库通
过读取主库的⼆进制⽇志，获取GTID并在⾃⼰的GTID集中记录。从库使⽤GTID集来跟踪其已经复制的事务，从⽽
保证不会重复复制或者漏掉任何⼀个事务。
当从库连接到主库时，主库会将⾃⼰的GTID集发送给从库，从⽽使从库能够确定⾃⼰需要复制的数据。如果从库与
主库之间的连接中断，从库可以使⽤⾃⼰的GTID集来确定从哪个点继续复制数据。
使⽤GTID复制可以避免主从复制中的⼀些问题，例如 主从复制延迟 和 数据丢失 等。同时，GTID复制还可以简化主
从复制的配置和管理，提⾼数据库的可靠性和可维护性。
Master 配置
10.1.106.70 master1
10.1.106.66 Slave1
//1.配置⽂件/etc/my.cnf
log-bin
server-id=1
gtid_mode = ON
enforce_gtid_consistency=1
//2.重启mysql服务⽣效
[root@Master ~]# /etc/init.d/mysqld restart
//3.模拟线上数据库
mysql>SET sql_log_bin=0;
mysql> drop database winglinuxdb;
mysql>SET sql_log_bin=1;
mysql> create database winglinuxdb;
mysql> use winglinuxdb;
mysql> create table t1(id int, name varchar(20));
mysql> insert into t1 values (1,'wing');
mysql> insert into t1 values (2,'wing1');
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | wing |
| 2 | wing1 |
+------+-------+
//4.授权, 允许能够远程连接的主机(replicaiton)
mysql> grant replication slave, replication client on *.*
to 'rep'@'10.1.106.%' identified by 'WingRep@123';
mysql> flush privileges;
//5.导出当前数据
[root@Master ~]# mysqldump -uroot -p'Wing@123' \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/db-$(date +%F)-all.sql
//5.将备份⽂件传送⾄Slave
[root@Master ~]# scp /backup/db-$(date +%F)-all.sql root@slave1:/backup/
Slave 配置
//1.检查是否能使⽤远程账户登录
[root@slave ~]# mysql -hmaster1 -urep -p'WingRep@123'
//2.修改配置⽂件/etc/my.cnf
server-id=2
gtid_mode = ON
enforce_gtid_consistency=1
[可选,保存连接信息⾄表中]
\#log-info-repository=TABLE
\#relay-log-intp-repository=TABLE
//3.重启mysql数据库服务
[root@slave ~]# /etc/init.d/mysqld restart
//4.导⼊数据,追master的bin_log
[root@Slave ~]# mysql -uroot -p'Wing@123' -e "source /backup/db-$(date +%F)-all.sql"
//4.指向Master,gtid⾃动协商同步
mysql> change master to
master_host='master1',
master_user='rep',
master_password='WingRep@123',
master_auto_position=1;
//7.启动slave⻆⾊
mysql> start slave;
//8.查看⻆⾊是否同步
mysql> show slave status\G
Gtid M-M复制, 基于主从模式,演变为双主
[root@sql ~]# cat /etc/hosts
#mysql
10.1.106.70 master1
10.1.106.66 master2 slave1
//1.配置⽂件/etc/my.cnf
server-id = 1
log-bin = /log/mysql/bin/sql_106_70
expire_logs_days = 30
gtid_mode = ON
enforce_gtid_consistency=1
//2.重启mysql服务⽣效
[root@Master ~]# /etc/init.d/mysqld restart
//3.授权, 允许能够远程连接的主机(replicaiton)
mysql> grant replication slave, replication client on *.*
to 'rep'@'10.1.106.%' identified by 'WingRep@123';
mysql> flush privileges;
//4.导出当前数据
[root@Master ~]# mysqldump -uroot -p'Wing@123' \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /backup/db-$(date +%F)-all.sql
//5.将备份⽂件传送⾄Master2
[root@Master ~]# scp /backup/db-$(date +%F)-all.sql root@master2:/backup/
//1.检查是否能使⽤远程账户登录
[root@slave ~]# mysql -hmaster1 -urep -p'WingRep@123'
//2.修改配置⽂件/etc/my.cnf
server-id = 2
gtid_mode = ON
enforce_gtid_consistency=1
log-bin = /log/mysql/bin/sql_106_66
[可选,保存连接信息⾄表中]
#log-info-repository=TABLE
#relay-log-intp-repository=TABLE
//3.重启mysql数据库服务
[root@slave ~]# /etc/init.d/mysqld restart
//4.导⼊数据
[root@Slave ~]vim /backup/db-$(date +%F)-all.sql
#注释这⼀段内容
24 -- SET @@GLOBAL.GTID_PURGED='56490b35-ff03-11ed-94dc-00505694c38d:1-2';
30 -- CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000002', MASTER_LOG_POS=194;
[root@Slave ~]# mysql -uroot -p'Wing@123' -e "source /backup/db-$(date +%F)-all.sql"
浙公网安备 33010602011771号