2023.6.18 11.数据库主从复制

11.数据库主从复制

1.MySQL数据库传统复制

2.MySQL数据库Gtid复制

3.MySQL数据库多源复制

4.MysQL数据库读写分离

5.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 线程读取中继⽇志中的事件，将其重放到备库数据库之上。

 

1.MySQL数据库传统主从复制

数据库传统主从复制如下, 如需实现多从, 将以下实验从案例在配置⼀次即可

 

/etc/hosts ⽂件解析

10.1.106.70 master1

10.1.106.66 slave1

Master 配置

//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;

+------+---------+

| 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/

备注说明

grant replication slave, replication client on . to 'rep'@'10.1.106.%' identified by 'WingRep@123';

授权⼀个名为 'rep' 的⽤户在 IP 地址为 10.1.106.% 的主机上进⾏主从复制操作。

该语句使⽤了 GRANT 命令，授权了 'rep' ⽤户在所有数据库（

.）上进⾏复制操作（replication slave）和连

接到服务器（replication client）,⽤户的密码为 'WingRep@123'。

注意 'rep' ⽤户需要在主库和从库上都存在，并且在从库上需要使⽤ CHANGE MASTER TO 命令将主库的信

息添加到从库中。此外，为了确保安全性，建议不要使⽤简单的密码，⽽是使⽤更加复杂的密码来保护数据

库。

 

Slave 配置

从库安装mysql

#2.基础环境准备

[root@slave ~]# systemctl stop firewalld

[root@slave ~]# systemctl disable firewalld

[root@slave ~]# setenforce 0

//建⽴⽤户与相应⽬录

[root@slave ~]# groupadd mysql

[root@slave ~]# useradd -r -g mysql -s /sbin/nologin mysql

[root@slave ~]# mkdir /soft/src -p && cd /soft/src

#3.下载`MySQL`并安装

[root@slave src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-

linux-glibc2.12-x86_64.tar.gz

[root@slave src]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /soft

[root@slave src]# ln -s /soft/mysql-5.7.30-linux-glibc2.12-x86_64/ /soft/mysql

#4.进⾏`MySQL`初始化

//创建初始化⽬录以及数据库数据⽬录

[root@slave ~]# mkdir /soft/mysql/{mysql-file,data}

[root@sqslaveslavel ~]# chown mysql.mysql /soft/mysql/

//初始化数据库

[root@sslaveql ~]# /soft/mysql/bin/mysqld --initialize \

--user=mysql --basedir=/soft/mysql \

--datadir=/soft/mysql/data

-------

//初始化数据库会告诉默认登陆账户与密码

2023-05-28T01:27:22.174615Z 1 [Note] A temporary password is generated for

root@localhost: u*ksA8+bP+iH

//使⽤ssl连接, 初始化后重新授权⽬录权限[如不需要可忽略]

[root@slave ~]# /soft/mysql/bin/mysql_ssl_rsa_setup \

--datadir=/soft/mysql/data/

[root@sql ~]# chown -R mysql.mysql /soft/mysql/

#使⽤(systemV)⽅式管理, [强烈推荐]

[root@slave ~]# cp /soft/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@slave ~]# chkconfig --add mysqld

[root@slave ~]# chkconfig mysqld on

//修改安装⽬录与数据存放⽬录

[root@slave ~]# sed -i '/^basedir=/cbasedir=\/soft\/mysql' /etc/init.d/mysqld

[root@slave ~]# sed -i '/^datadir=/cdatadir=\/soft\/mysql\/data' /etc/init.d/mysqld

[root@slave ~]# mkdir -p /log/mysql/ && touch /log/mysql/err.log && mkdir -p

/var/run/mysqld/

[root@slave ~]# chown mysql.mysql -R /log/mysql/ && chown mysql.mysql -R

/var/run/mysqld/

//修改配置

cat > /etc/my.cnf <<EOF

[mysqld]

server-id = 2

 

 

//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"

//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;

//6.启动slave⻆⾊

mysql> start slave;

//7.查看⻆⾊是否同步

mysql> show slave status\G

Slave_IO_State: Waiting for master to send event

Master_Host: master1

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: sql_106_70.000024

Read_Master_Log_Pos: 639

Relay_Log_File: lb-node2-relay-bin.000002

Relay_Log_Pos: 806

Relay_Master_Log_File: sql_106_70.000024

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

2. MySQL数据库Gtid复制

GTID复制中的事务是指MySQL中的ACID（原⼦性、⼀致性、隔离性和持久性）事务。在MySQL中，事务是

由⼀系列的SQL语句组成的逻辑操作单元，可以保证这些操作的原⼦性、⼀致性、隔离性和持久性。GTID复

制使⽤事务ID来标识和跟踪事务，以确保数据的⼀致性和可靠性。

MySQL GTID（Global Transaction ID）复制是⼀种基于事务ID的复制⽅式，它可以解决主从复制中的⼀些问题，

例如主从复制延迟和数据丢失等。

在MySQL GTID复制中，每个事务都有⼀个全局唯⼀的ID，称为GTID。主库将GTID记录到⼆进制⽇志中，从库通

过读取主库的⼆进制⽇志，获取GTID并在⾃⼰的GTID集中记录。从库使⽤GTID集来跟踪其已经复制的事务，从⽽

保证不会重复复制或者漏掉任何⼀个事务。

当从库连接到主库时，主库会将⾃⼰的GTID集发送给从库，从⽽使从库能够确定⾃⼰需要复制的数据。如果从库与

主库之间的连接中断，从库可以使⽤⾃⼰的GTID集来确定从哪个点继续复制数据。

使⽤GTID复制可以避免主从复制中的⼀些问题，例如 主从复制延迟 和 数据丢失 等。同时，GTID复制还可以简化主

从复制的配置和管理，提⾼数据库的可靠性和可维护性。

数据库 Gtid 主从复制如下, 如需实现多从, 将以下实验从案例在配置⼀次即可

 

 

/etc/hosts ⽂件解析

10.1.106.70 master1

10.1.106.66 Slave1

注意:如果实验过传统主从复制, 请重置Slave数据库,⽂章后续会介绍

Master 配置

//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

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master1

Master_User: rep

Master_Port: 3306

Master_Log_File: sql_106_70.000003

Read_Master_Log_Pos: 194

Relay_Log_File: lb-node2-relay-bin.000002

Relay_Log_Pos: 369

Relay_Master_Log_File: sql_106_70.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 194

Relay_Log_Space: 579

Seconds_Behind_Master: 0

Master_Server_Id: 70

Master_UUID: 56490b35-ff03-11ed-94dc-00505694c38d

Master_Info_File: /soft/mysql/data/master.info

SQL_Delay: 0

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Executed_Gtid_Set: 56490b35-ff03-11ed-94dc-00505694c38d:1-6

Auto_Position: 1

1 row in set (0.00 sec)

 

以下是⼀些关键字段的解释：

Slave_IO_State：从库当前正在等待主库发送事件。

Master_Host：主库的主机名。

Master_User：⽤于从主库复制数据的⽤户。

Master_Port：主库使⽤的端⼝号。

Master_Log_File ：主库当前正在写⼊的⼆进制⽇志⽂件的名称。

Read_Master_Log_Pos ：从库当前已经读取的主库⼆进制⽇志的位置。

Slave_IO_Running ：从库的I/O线程正在运⾏。

Slave_SQL_Running ：从库的SQL线程正在运⾏。

Last_Errno：最近⼀次错误的错误代码。

Last_Error：最近⼀次错误的错误消息。

Exec_Master_Log_Pos ：从库当前正在执⾏的主库⼆进制⽇志的位置。

 

Relay_Log_Space : 579 从库当前使⽤的中继⽇志⽂件的⼤⼩，以字节为单位

SQL_Delay : 从库SQL线程当前的延迟时间

Seconds_Behind_Master ：从库当前滞后于主库的秒数。如果为0，表示从库已经与主库同步。

Slave_SQL_Running_State: 从库已经读取了所有的中继⽇志，正在等待更多的更新。

Master_Retry_Count: 如果主库⽆法连接，则从库会尝试重新连接主库的次数。这⾥的值为86400，表示从库

将尝试86400次重新连接主库。

Executed_Gtid_Set: 已经执⾏的GTID集合，这⾥的值为"56490b35-ff03-11ed-94dc-00505694c38d:1-6"，表

示从库已经执⾏了这个GTID集合中的1-6个GTID。

Auto_Position: 是否开启了⾃动位点模式，这⾥的值为1，表示已经开启了⾃动位点模式。开启⾃动位点模式

后，从库会⾃动记录已经执⾏的GTID，并在重新连接主库时，从最后⼀个已知的GTID继续进⾏复制。

Seconds_Behind_Master 是show slave status命令中⼀个⾮常重要的字段，它表示从库当前滞后于主库的

秒数。具体来说，它表示从库当前正在执⾏的主库⼆进制⽇志事件的时间戳与主库上相应事件的时间戳之间

的差异，也就是从库当前的时间与主库上相应事件发⽣的时间之间的差异。

如果Seconds_Behind_Master的值为0，则表示从库已经追上了主库，两者的数据完全⼀致。如果

Seconds_Behind_Master的值为NULL，则表示从库当前没有与主库进⾏同步。如果

Seconds_Behind_Master的值为⾮零，则表示从库当前滞后于主库，即从库上的数据还没有完全同步到主库

上。

通常情况下，我们希望从库与主库的数据同步尽可能地及时，因此，我们需要定期检查

Seconds_Behind_Master的值，以确保从库与主库之间的数据同步正常。

查看MySQL主从复制是否基于GTID，可以使⽤以下命令：

SHOW VARIABLES LIKE 'gtid_mode';

如果输出结果为 ON ，则表示MySQL主从复制是基于GTID的。如果输出结果为 OFF ，则表示MySQL主从复制不是

基于GTID的。

 

注意事项:

学习场景重置主从关系

#master

reset master;

#slave

stop slave;

reset slave;

 

2.1 Gtid M-M复制, 基于主从模式,演变为双主

 

 

 

MySQL数据库双主复制

注意: 数据库双主M-M架构, 需要基于gtid的主从架构演变

 

[root@sql ~]# cat /etc/hosts

#mysql

10.1.106.70 master1

10.1.106.66 master2 slave1

 

Master1

//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/

 

Master2

//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"

 

Master1 执⾏同步

master1和master2数据必须保持⼀致

在master1上执⾏ change to master1 --> mater2

 

change master to

master_host='master2',

master_user='rep',

master_password='WingRep@123',

master_auto_position=1;

start slave;

mysql> show slave status\G

 

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master2

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: sql_106_66.000001

Read_Master_Log_Pos: 154

Relay_Log_File: lb-node1-relay-bin.000002

Relay_Log_Pos: 369

Relay_Master_Log_File: sql_106_66.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

 

Master2 执⾏同步

在master2上执⾏ change to master2 -->master1

change master to

master_host='master1',

master_user='rep',

master_password='WingRep@123',

master_auto_position=1;

start slave;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master1

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: sql_106_70.000002

Read_Master_Log_Pos: 194

Relay_Log_File: lb-node2-relay-bin.000002

Relay_Log_Pos: 369

Relay_Master_Log_File: sql_106_70.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

 

验证

#master1

mysql> create database master1;

#master2

mysql> create database master2;

 

3.MySQL数据库多源复制

数据库M-M-S-S

 

[root@Master1 ~]# cat /etc/hosts

10.1.106.70 master1

10.1.106.66 master2

10.1.106.67 slave2

10.1.106.68 slave3

Master1

1.修改配置

log -bin

server_id=1

gtid_mode = ON

enforce_gtid_consistency=1

2.重启mysql服务器

systemctl restart mysqld

3.授权(建议⽹段)

mysql> grant replication slave, replication client on *.* to

'rep'@'10.1.106.%' identified by 'WingRep@123';

mysql> flush privileges;

4.导出对应的数据

mysqldump -uroot -p'Wing@123' -h 10.1.106.70 \

--all-databases \

--master-data=1 \

--single-transaction \

--flush-logs > /backup/$(date +%F)-mysql-all.sql

5.分发对应的数据

scp --> master2

scp --> slave1

scp --> slave2

Master2

1.修改配置

server_id=2

log_bin

gtid_mode = ON

enforce_gtid_consistency = 1

2.重启

3.导⼊数据

[root@Master2 ~]# mysql -uroot -pWing@123 -e "reset master;"

[root@Master2 ~]# mysql -uroot -pWing@123 < /root/2023-05-28-mysql-all.sql

4.验证远程账户是否可⽤

1.防⽕墙

2.账户密码

5.登陆数据库,清理从库的⼆进制⽇志

mysql> reset master;

6.指定master

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

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master1

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: Master1-bin.000003

Read_Master_Log_Pos: 154

Relay_Log_File: Master2-relay-bin.000002

Relay_Log_Pos: 371

Relay_Master_Log_File: Master1-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Master1

mysql> flush privileges;

mysql>change master to

master_host='master2',

master_user='rep',

master_password='WingRep@123',

master_auto_position=1;

2.启动slave⻆⾊

mysql > start slave; #

3.查看slave状态

mysql > show slave status\G

双主OK

Slave1和Slave2⼀致

1. 修改配置

server_id=3

gtid_mode = ON

enforce_gtid_consistency = 1

master-info-repository=TABLE

relay-log-info-repository=TABLE

2.重启数据库

[root@slave2 ~]# /etc/init.d/mysqld restart

3. 初始化数据库 导⼊数据

[root@slave2 ~]# mysql -uroot -p'Wing@123' -e "reset master;"

[root@slave2 ~]# mysql -uroot -p'Wing@123' < /backup/2023-05-28-mysql

all.sql

[root@slave2 ~]# mysql -uroot -p'Wing@123' -se 'show databases;'

4.清理⼆进制⽇志⽂件

reset master;

//指向Master1

mysql> change master to

master_host='master1',

master_user='rep',

master_password='WingRep@123',

master_log_file='sql_106_70.000001',

master_log_pos=154;

//指向Master2

mysql> change master to

master_host='master2',

master_user='rep',

master_log_file='sql_106_66.000001',

master_log_pos=154;

5.mysql > start slave;

6.mysql > show slave status\G

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master1

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: Master1-bin.000001

Read_Master_Log_Pos: 154

Relay_Log_File: slave2-relay-bin-master1@002dchannel.000002

Relay_Log_Pos: 371

Relay_Master_Log_File: Master1-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

mysql> show slave status\G

*************************** 2. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master1

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: Master1-bin.000001

Read_Master_Log_Pos: 154

Relay_Log_File: slave2-relay-bin-master1@002dchannel.000002

Relay_Log_Pos: 371

Relay_Master_Log_File: Master1-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4.MySQL数据库架构演变