主要内容:
MySQL主从同步(概述、原理、构建主从同步)、主从同步结构类型(主多从、主从从、主主)、复制模式(异步、半同步)、启用半同步复制
一、MySQL主从同步
MySQL 主从同步(Master-Slave Replication)是一种常见的数据库复制技术,用于提高数据库的可用性、扩展性和数据安全性。通过主从同步,可以将一个 MySQL 数据库(主库)的数据复制到一个或多个 MySQL 数据库(从库),从而实现数据的冗余备份和读写分离。
1、主从同步概述
1)实现同步的服务结构
- ① 主服务器(主库):接收从服务器访问连接;
- ② 从服务器(从库):自动同步主服务器数据;
2)主从同步的工作原理
主库(Master):
- 主库是数据的源头,负责处理所有的写操作(INSERT、UPDATE、DELETE)。
- 主库将所有的写操作记录在二进制日志(Binary Log)中。
从库(Slave):
- 从库是主库的副本,负责处理读操作(SELECT)。
- 从库通过读取主库的二进制日志,将主库的写操作应用到自身,从而保持与主库的数据一致性。
二进制日志(Binary Log):
- 二进制日志记录了主库的所有写操作,包括事务的开始和结束。
- 从库通过连接到主库,读取并应用这些二进制日志,实现数据的同步。
3)主从同步的配置步骤
① 配置主库(Master)
- 启用二进制日志:在主库的配置文件(通常是 my.cnf 或 my.ini)中,添加以下配置:
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
server-id = 1
binlog_do_db = your_database_name
log_bin
:指定二进制日志文件的路径和名称。
server-id
:指定主库的唯一标识符。
binlog_do_db
:指定需要同步的数据库名称(可选)。
- 重启 MySQL 服务:重启 MySQL 服务以应用配置更改:
sudo systemctl restart mysql
- 创建用于复制的用户:在主库上创建一个用于复制的用户,并授予复制权限:
CREATE USER 'repl_user'@'slave_ip' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip';
FLUSH PRIVILEGES;
- 获取主库的二进制日志位置:在主库上执行以下命令,获取当前的二进制日志文件和位置:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
记录
File
和Position
的值,这些值将用于从库的配置。
② 配置从库(Slave)
- 配置从库:在从库的配置文件中,添加以下配置:
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = your_database_name
server-id
:指定从库的唯一标识符。
relay_log
:指定中继日志文件的路径和名称。
log_bin
:指定二进制日志文件的路径和名称(可选)。
binlog_do_db
:指定需要同步的数据库名称(可选)。
- 重启 MySQL 服务:重启 MySQL 服务以应用配置更改:
sudo systemctl restart mysql
- 配置从库连接主库:在从库上执行以下命令,配置从库连接到主库:
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345;
MASTER_HOST
:主库的 IP 地址或主机名。
MASTER_USER
:用于复制的用户名。
MASTER_PASSWORD
:用于复制的用户密码。
MASTER_LOG_FILE
:主库的二进制日志文件名。
MASTER_LOG_POS
:主库的二进制日志位置。
- 启动从库复制:在从库上执行以下命令,启动复制进程:
START SLAVE;
- 检查从库状态:在从库上执行以下命令,检查复制状态:
SHOW SLAVE STATUS\G
检查
Slave_IO_Running
和Slave_SQL_Running
是否为Yes
,表示复制正常运行。
2、主从同步的优点
提高可用性:通过数据冗余,提高数据库的可用性,减少单点故障的风险
读写分离:将读操作分配到从库,减轻主库的负载,提高系统性能
数据备份:从库可以作为主库的备份,提供数据的安全性和可靠性
扩展性:通过增加从库,可以扩展系统的读能力,支持更多的并发读操作
3、主从同步的缺点
延迟问题:从库的数据同步可能存在延迟,特别是在主库写操作频繁的情况下
单点写入:主库仍然是单点写入,如果主库故障,需要手动切换到从库
配置复杂:主从同步的配置和管理相对复杂,需要仔细规划和维护
构建主从同步示例:
实验拓扑结构:
- 服务器角色:node10为主(Master),node11为从(Slave);
- node10配置:ip地址为192.168.2.10,安装mysql,初始化密码为NSD2021@tedu.cn
- node11配置:ip地址为192.168.2.11,安装mysql,初始化密码为NSD2021@tedu.cn
提示:防火墙与SELinux提前关闭;
步骤1:准备初始化环境(node10、node11操作)
① 准备MySQL数据库,并初始化密码(以node10为例)
[root@node10 ~]# yum -y install mysql-community-* //检查Yum源,并安装MySQL
[root@node10 ~]# systemctl start mysqld //启动服务
[root@node10 ~]# systemctl enable mysqld //设置开机自启
[root@node10 ~]# grep -i password /var/log/mysqld.log //查看生成的随机密码
2021-06-10T13:44:26.945514Z 1 [Note] A temporary password is generated for root@localhost: O-,Ya=5qpkpW
[root@node10 ~]# mysqladmin -uroot -p' O-,Ya=5qpkpW ' password 'NSD2021@tedu.cn'
② 导入sql数据(仅node10操作)
参考:# git clone https://gitee.com/mrzhangzhg/tedu_nsd.git
[root@localhost ~]# scp -r /var/lib/mygit/tedu_nsd/dbs/mysql_scripts/ 192.168.2.10:/root
[root@node10 ~]# ls
anaconda-ks.cfg mysql_scripts
[root@node10 ~]# mysql -uroot -pNSD2021@tedu.cn < mysql_scripts/nsd2021_data.sql
[root@node10 ~]# mysql -uroot -pNSD2021@tedu.cn -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nsd2021 |
| performance_schema |
| sys |
+--------------------+
步骤2:配置主服务器(node10操作)
① 修改配置文件,并重启服务
[root@node10 ~]# vim /etc/my.cnf
[mysqld]
server_id = 10 //服务器身份标识
log_bin = master10 //启用binlog日志
...
[root@node10 ~]# systemctl restart mysqld //重启服务
② 查看生成的binlog日志文件
[root@node10 ~]# ls /var/lib/mysql/master10.*
/var/lib/mysql/master10.000001 /var/lib/mysql/master10.index
[root@node10 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master10.000001 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
③ 授权Slave从服务器,可通过repluser用户同步数据
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
mysql> show grants for repluser@'%';
+--------------------------------------------------+
| Grants for repluser@% |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+--------------------------------------------------+
1 row in set (0.00 sec)
步骤3:配置从服务器(node11操作)
① 修改配置文件,并重启服务
[root@node11 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11 //服务器身份标识
...
[root@node11 ~]# systemctl restart mysqld
② 通过mysqldump手动同步主服务数据库数据
注意:从服务器与主服务器进行自动同步前提,需保证之前已有的数据库数据一致;
# 主服务器备份数据,并拷贝备份文件到从服务器
[root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
[root@node10 ~]# scp /root/fullbackup.sql root@192.168.2.11:/root
# 从服务器恢复数据
[root@node11 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> create database nsd2021 default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
[root@node11 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql
【--master-data】作用:如果完全备份完成后,又有新的数据产生,它可记录备份时的数据状态信息。
步骤4:配置从服务器与主服务器同步
① 在同步的备份文件中,查看binlog日志名(master_log_file)和偏移量(master_log_pos)
注意:没有过滤出日志名和偏移量,需检查主服务器的日志状态
[root@node11 ~]# grep master10 /root/fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='master10.000001', MASTER_LOG_POS=441;
② 从服务器指定主服务器同步信息
[root@node11 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to-> master_host='192.168.2.10', //指定连接主库地址-> master_user='repluser', //指定连接已授权用户-> master_password='NSD2021@tedu.cn', //授权用户密码-> master_log_file='master10.000001', //binlog日志-> master_log_pos=441; //偏移量
Query OK, 0 rows affected, 2 warnings (0.00 sec)
③ 启动slave服务器进程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
④ 验证:查看slave状态信息
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.2.10Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master10.000001Read_Master_Log_Pos: 441Relay_Log_File: node11-relay-bin.000004Relay_Log_Pos: 319Relay_Master_Log_File: master10.000001Slave_IO_Running: YesSlave_SQL_Running: Yes
…
⑤ 验证:查看从服务器上的相关文件
[root@node11 ~]# ls /var/lib/mysql
文件名 | 说明 |
master.info | 主库信息(端口、IP、日志、用户账户密码、UUID等) |
relay-log.info | 中继日志信息(中继日志、端口、偏移量等) |
主机名-relay-bin.编号 | 中继日志(binlog日志记录的SQL命令) |
主机名-relay-bin.index | 索引日志(记录已有的中继日志) |
补充:将目录中的四类从库文件全部删除,再重启服务,可还原为独立的、非从服务器;
排错思路:
① 通过`show slave status`查看Error错误说明;
② 如果配置主库信息输入错误,需先关闭slave功能,配置完成后再开启slave功能
mysql> stop slave; //停止slave
mysql> start slave; //开启slave
mysql> show slave status\G
③ master.info文件和relay-log.info文件有配置记录,可用于排错;
常见报错:Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
- 原因分析:主从架构中使用了相同的UUID,原因是克隆了虚拟机,而只改server_id不行
- 解决办法:将从服务器上的/var/lib/mysql/auto.cnf重命名,重启Mysqld服务生成新的auto.cnf文件,即新的UUID;或者直接修改UUID,不重复即可;
[root@node11 mysql]# mv auto.cnf auto.cnf.bak
[root@node11 mysql]# systemctl restart mysqld
[root@node11 mysql]# vim auto.cnf
[auto]
server-uuid=fd780a70-ca14-11eb-9562-000c29da9126
步骤5:验证主从架构同步效果
① 在主服务器上的数据库中新增数据
[root@node10 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> insert into nsd2021.departments(dept_name) values('行政部');
Query OK, 1 row affected (0.01 sec)
mysql> select * from nsd2021.departments where dept_name='行政部';
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 9 | 行政部 |
+---------+-----------+
1 row in set (0.00 sec)
② 从服务器验证同步数据
[root@node11 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> select * from nsd2021.departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
| 9 | 行政部 |
+---------+-----------+
9 rows in set (0.00 sec)
二、主从同步架构
1、常见的架构类型
① 单主单从架构,一主一从
架构描述:一个主库和一个从库,主库负责所有的写操作,从库负责读操作
优点:- 简单易配置
- 适用于读多写少的场景
缺点:- 主库仍然是单点写入,存在单点故障风险
- 从库的数据同步可能存在延迟
适用场景:- 小型应用或初创企业
- 读操作远多于写操作的场景
② 单主多从架构,一主多从
架构描述:一个主库和多个从库。主库负责所有的写操作,多个从库负责读操作
优点:- 提高了读操作的扩展性
- 通过增加从库,可以分担主库的读负载
缺点:- 主库仍然是单点写入,存在单点故障风险
- 从库的数据同步可能存在延迟
适用场景:- 读操作远多于写操作的场景
- 需要扩展读能力的应用
③ 级联复制架构,主从从(主->从主->从)
架构描述:一个主库和多个从库,其中一个从库作为中间节点,再连接到其他从库;
主库负责所有的写操作,中间节点从库负责部分读操作,并作为其他从库的主库
优点:- 减轻主库的复制负载
- 提高了读操作的扩展性
缺点:- 增加了复制的复杂性
- 中间节点的故障会影响到下游从库
适用场景:- 主库的复制负载较高,需要减轻主库压力的场景
- 需要扩展读能力的应用
④ 环形复制架构,主主结构、双主结构(互为主从)
架构描述:多个主库之间形成一个环形复制链。每个主库既是主库也是从库,数据在环形链中循环复制
优点:- 提高了写操作的可用性和扩展性
- 避免了单点写入的故障风险
缺点:- 配置和管理非常复杂
- 存在数据一致性问题,需要解决冲突
适用场景:- 需要极高可用性和扩展性的写操作场景
- 对数据一致性要求不高的应用
构建一主多从同步示例:(基于主从架构,额外添加一个从服务器)
实验拓扑结构:
- 服务器角色:node10为主(Master),node11为从(Slave1) ,node12为从(Slave2);
- node10配置:ip地址为192.168.2.10,安装mysql,初始化密码为NSD2021@tedu.cn
- node11配置:ip地址为192.168.2.11,安装mysql,初始化密码为NSD2021@tedu.cn
- node12配置:ip地址为192.168.2.12,安装mysql,初始化密码为NSD2021@tedu.cn
提示:防火墙与SELinux提前关闭;
步骤1:准备初始化环境(node12操作)
① 准备MySQL数据库,并初始化密码
[root@node12 ~]# yum -y install mysql-community-* //检查Yum源,并安装MySQL
[root@node12 ~]# systemctl start mysqld //启动服务
[root@node12 ~]# systemctl enable mysqld //设置开机自启
[root@node12 ~]# grep -i password /var/log/mysqld.log //查看生成的随机密码
2021-06-10T13:44:26.945514Z 1 [Note] A temporary password is generated for root@localhost: O-,Ya=5qpkpW
[root@node12 ~]# mysqladmin -uroot -p' O-,Ya=5qpkpW ' password 'NSD2021@tedu.cn'
步骤2:主服务器数据备份(node10操作)
① 检查配置文件
[root@node10 ~]# cat /etc/my.cnf
[mysqld]
server_id = 10 //服务器身份标识
log_bin = master10 //启用binlog日志
...
② 检查授权用户
mysql> show grants for repluser@'%';
+--------------------------------------------------+
| Grants for repluser@% |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+--------------------------------------------------+
1 row in set (0.00 sec)
③ 主服务器完全备份数据,并拷贝到从服务器
[root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
[root@node10 ~]# scp /root/fullbackup.sql root@192.168.2.12:/root
步骤3:配置从服务器(node12操作)
① 修改配置文件,并重启服务
[root@node12 ~]# vim /etc/my.cnf
[mysqld]
server_id = 12 //服务器身份标识
...
[root@node12 ~]# systemctl restart mysqld
② 从服务器手动同步主服务数据库数据
[root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> create database nsd2021 default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
[root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql
步骤4:配置从服务器同步主服务器
① 获取日志文件名和偏移量
[root@node12 ~]# grep master10 /root/fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='master10.000001', MASTER_LOG_POS=717;
② 从服务器指定主服务器同步信息
[root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to-> master_host='192.168.2.10',-> master_user='repluser',-> master_password='NSD2021@tedu.cn',-> master_log_file='master10.000001',-> master_log_pos=717;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
③ 启动slave服务器进程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
④ 验证:查看slave状态信息
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State:Master_Host: 192.168.2.10Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master10.000001Read_Master_Log_Pos: 717Relay_Log_File: node12-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: master10.000001Slave_IO_Running: YesSlave_SQL_Running: Yes
…
步骤5:验证一主多从架构同步效果
① 在主服务器上的数据库中新增数据(node10操作)
mysql> insert into nsd2021.departments(dept_name) values('风控部');
② 从服务器验证同步数据(node11、node12操作)
mysql> select * from nsd2021.departments where dept_name='风控部';
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 10 | 风控部 |
+---------+-----------+
1 row in set (0.00 sec)
构建主从从同步示例:
实验拓扑结构:
- 服务器角色:node12为主(Master),node13为从/主(Slave1,Master2) ,node14为从(Slave2);
- node12配置:ip地址为192.168.2.12,将角色Slave还原并做成Master(nsd2021保留)
- node13配置:ip地址为192.168.2.13,安装mysql,初始化密码为NSD2021@tedu.cn
- node14配置:ip地址为192.168.2.14,安装mysql,初始化密码为NSD2021@tedu.cn
提示:防火墙与SELinux提前关闭;
步骤1:准备初始化环境(node13、node14操作)
① 准备MySQL数据库,并初始化密码(以node13为例)
[root@node13 ~]# yum -y install mysql-community-* //检查Yum源,并安装MySQL
[root@node13 ~]# systemctl start mysqld //启动服务
[root@node13 ~]# systemctl enable mysqld //设置开机自启
[root@node13 ~]# grep -i password /var/log/mysqld.log //查看生成的随机密码
2021-06-10T13:44:26.945514Z 1 [Note] A temporary password is generated for root@localhost: O-,Ya=5qpkpW
[root@node13 ~]# mysqladmin -uroot -p' O-,Ya=5qpkpW ' password 'NSD2021@tedu.cn'
② 将一主多从配置中的从服务器192.168.2.12改为主服务器(node12操作)
[root@node12 ~]# cd /var/lib/mysql
[root@node12 mysql]# rm -rf master.info relay-log.info node12-relay-bin.*
[root@node12 ~]# systemctl restart mysqld //重启服务
[root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show slave status; //检查slave状态(为空则恢复成功)
Empty set (0.00 sec)
补充:将从服务器/var/lib/mysql中的四类从库文件删除并重启服务,即可还原独立数据库;
步骤2:配置主服务器(node12操作)
① 修改配置文件,并重启服务
[root@node12 ~]# vim /etc/my.cnf
[mysqld]
server_id=12
log_bin=master12
...
[root@node12 ~]# systemctl restart mysqld
② 查看生成的binlog日志文件
[root@node12 ~]# ls /var/lib/mysql/master12.*
/var/lib/mysql/master12.000001 /var/lib/mysql/master12.index
[root@node12 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master12.000001 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
③ 授权Slave1从服务器,可通过repluser用户同步数据
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
mysql> show grants for repluser@'%';
+--------------------------------------------------+
| Grants for repluser@% |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+--------------------------------------------------+
1 row in set (0.00 sec)
步骤3:配置从-主服务器(node13操作)
提示:node13作为node12的Slave服务器,同时也作为node14的Master服务器
① 修改配置文件,并重启服务
[root@node13 ~]# vim /etc/my.cnf
[mysqld]
server_id=13
log_bin=master13 //开启binlog日志功能
log_slave_updates //让从库从主库复制数据时可以写入到binlog日志
…
[root@node13 ~]# systemctl restart mysqld
补充:从库开启log_bin参数,如果直接往从库写数据,是可以记录binlog日志的;但从库通过I0线程读取主库二进制日志文件,然后通过SQL线程写入的数据,是不会记录在binlog日志的;也就是说从库从主库上复制的数据,是不写入从库的binlog日志的。所以从库做为其他从库的主库时,需要在配置文件中添加 log-slave-updates 参数;
② 查看生成的binlog日志文件
[root@node13 ~]# ls /var/lib/mysql/master13.*
/var/lib/mysql/master13.000001 /var/lib/mysql/master13.index
[root@node13 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master13.000001 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
③ 授权Slave2服务器,可通过repluser用户同步数据
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
Query OK, 0 rows affected, 1 warning (0.00 sec):
④ 通过mysqldump手动同步主服务数据库数据
注意:自动同步前,需保证主从服务器的数据库数据一致;
# 主服务器备份数据,并拷贝备份文件到slave1从服务器
[root@node12 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
[root@node12 ~]# scp /root/fullbackup.sql root@192.168.2.13:/root
# 从服务器恢复数据
[root@node13 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> create database nsd2021 default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
[root@node13 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql
步骤4:配置Slave1从服务器同步主服务器
① 获取日志文件名和偏移量
[root@node13 ~]# grep master /root/fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='master12.000001', MASTER_LOG_POS=441;
② 从服务器指定主服务器同步信息
[root@node13 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to-> master_host='192.168.2.12',-> master_user='repluser',-> master_password='NSD2021@tedu.cn',-> master_log_file='master12.000001',-> master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
③ 启动slave服务器进程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
④ 验证:查看slave状态信息
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.2.12Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master12.000001Read_Master_Log_Pos: 441Relay_Log_File: node13-relay-bin.000002Relay_Log_Pos: 319Relay_Master_Log_File: master12.000001Slave_IO_Running: YesSlave_SQL_Running: Yes…
…
⑤ 验证:查看master状态信息
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master13.000001 | 173726 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
步骤5:配置从服务器(node14操作)
① 修改配置文件,并重启服务
[root@node14 ~]# vim /etc/my.cnf
[mysqld]
server_id = 14
...
[root@node14 ~]# systemctl restart mysqld
② 通过mysqldump手动同步Slave1从-主服务数据库数据
# 从-主服务器备份数据,并拷贝备份文件到slave2从服务器
[root@node13 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
[root@node13 ~]# scp /root/fullbackup.sql root@192.168.2.14:/root
# 从服务器恢复数据
[root@node14 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> create database nsd2021 default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
[root@node14 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql
步骤6:配置Slave2从服务器同步Slave1主服务器
① 获取日志文件名和偏移量
[root@node14 ~]# grep master /root/fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='master13.000001', MASTER_LOG_POS=173726;
② 从服务器指定主服务器同步信息
mysql> change master to-> master_host='192.168.2.13',-> master_user='repluser',-> master_password='NSD2021@tedu.cn',-> master_log_file='master13.000001',-> master_log_pos=173726;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
③ 启动slave服务器进程
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
④ 验证:查看slave状态信息
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.2.13Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master13.000001Read_Master_Log_Pos: 173726Relay_Log_File: node14-relay-bin.000002Relay_Log_Pos: 319Relay_Master_Log_File: master13.000001Slave_IO_Running: YesSlave_SQL_Running: Yes
…
步骤7:验证主从从架构同步效果
① 在主服务器上的数据库中新增数据(node12操作)
mysql> insert into nsd2021.departments(dept_name) values('企划部');
② 从服务器验证同步数据(node13、node14操作)
mysql> select * from nsd2021.departments where dept_name='企划部';
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 11 | 企划部 |
+---------+-----------+
1 row in set (0.00 sec)
③ 在Slave1从服务器上的数据库中新增数据(node13操作)
mysql> insert into nsd2021.departments(dept_name) values('创业部');
④ 从服务器验证同步数据(node14操作)
mysql> select * from nsd2021.departments where dept_name='创业部';
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 12 | 创业部 |
+---------+-----------+
1 row in set (0.00 sec)
构建主主(互为主从)同步示例:
实验拓扑结构:
- 服务器角色:node15为主-从(Master1/Slave2),node16为主-从(Master2/Slave1);
- node15配置:ip地址为192.168.2.15,安装mysql,初始化密码为NSD2021@tedu.cn
- node16配置:ip地址为192.168.2.16,安装mysql,初始化密码为NSD2021@tedu.cn
补充:node15和node16互为主库和从库,若有数据库数据更新,相互进行同步
步骤1:准备初始化环境(node15、node16操作)
① 准备MySQL数据库,并初始化密码(以node15为例)
[root@node15 ~]# yum -y install mysql-community-* //检查Yum源,并安装MySQL
[root@node15 ~]# systemctl start mysqld //启动服务
[root@node15 ~]# systemctl enable mysqld //设置开机自启
[root@node15 ~]# grep -i password /var/log/mysqld.log //查看生成的随机密码
2021-06-10T13:44:26.945514Z 1 [Note] A temporary password is generated for root@localhost: O-,Ya=5qpkpW
[root@node15 ~]# mysqladmin -uroot -p' O-,Ya=5qpkpW ' password 'NSD2021@tedu.cn'
步骤2:配置主服务器(node15操作)—> “将192.168.2.15作为主服务器”
① 修改配置文件,并重启服务
[root@node15 ~]# vim /etc/my.cnf
[mysqld]
server_id=15
log_bin=master15
...
[root@node15 ~]# systemctl restart mysqld
② 查看生成的binlog日志文件
[root@node15 ~]# ls /var/lib/mysql/master15.*
/var/lib/mysql/master15.000001 /var/lib/mysql/master15.index
③ 授权从服务器(node16),可通过repluser用户同步数据
[root@node15 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master status; //查看日志文件和偏移量
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master15.000001 | 441 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
步骤3:配置从服务器(node16操作)—> “将192.168.2.16作为从服务器”
① 修改配置文件
[root@node16 ~]# vim /etc/my.cnf
[mysqld]
server_id = 16
...
[root@node16 ~]# systemctl restart mysqld
② 从服务器指定主服务器同步信息(通过主服务器show master status获取信息)
[root@node16 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to-> master_host='192.168.2.15',-> master_user='repluser',-> master_password='NSD2021@tedu.cn',-> master_log_file='master15.000001',-> master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave; //启动slave程序
Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G //查看slave状态
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.2.15Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master15.000001Read_Master_Log_Pos: 441Relay_Log_File: node16-relay-bin.000002Relay_Log_Pos: 319Relay_Master_Log_File: master15.000001Slave_IO_Running: YesSlave_SQL_Running: Yes
…
步骤4:配置主服务器(node16操作)—> “将192.168.2.16作为主服务器”
① 修改配置文件,并重启服务
[root@node16 ~]# vim /etc/my.cnf
[mysqld]
server_id=16
log_bin=master16 //启用binlog功能(只需新增此项)
...
[root@node16 ~]# systemctl restart mysqld
② 查看生成的binlog日志文件
[root@node16 ~]# ls /var/lib/mysql/master16.*
/var/lib/mysql/master16.000001 /var/lib/mysql/master16.index
③ 授权从服务器(node15),可通过repluser用户同步数据
[root@node16 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master status; //查看日志文件和偏移量
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master16.000001 | 441 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
步骤5:配置从服务器(node15操作)—> “将192.168.2.15作为从服务器”
① 从服务器指定主服务器同步信息(通过主服务器show master status获取信息)
[root@node15 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to-> master_host='192.168.2.16',-> master_user='repluser',-> master_password='NSD2021@tedu.cn',-> master_log_file='master16.000001',-> master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> start slave; //启动slave程序
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G //查看slave状态
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.2.16Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: master16.000001Read_Master_Log_Pos: 441Relay_Log_File: node15-relay-bin.000002Relay_Log_Pos: 319Relay_Master_Log_File: master16.000001Slave_IO_Running: YesSlave_SQL_Running: Yes
…
步骤6:验证主主架构同步效果
① 以node15作为主服务器进行验证
# 主服务器上创建数据库和表(node15操作)
[root@node15 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> create database mydb;
mysql> use mydb;
mysql> create table students(id int primary key, name varchar(20));
# 从服务器上查看(node16操作)
[root@node16 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)mysql> use mydb;
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| students |
+----------------+
1 row in set (0.00 sec)
② 以node16作为主服务器进行验证
# 主服务器上添加用户(node16操作)
[root@node16 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> insert into mydb.students values(1,'tom');
# 从服务器上查看(node15操作)
mysql> select * from mydb.students;
+----+------+
| id | name |
+----+------+
| 1 | tom |
+----+------+
1 row in set (0.00 sec)
三、复制模式
MySQL 复制模式是指主库(Master)和从库(Slave)之间数据同步的方式。常见的复制模式包括异步复制、全同步复制和半同步复制。每种复制模式都有其特点和适用场景。
1、异步复制(Asynchronous replication)
工作原理:
- 主库将写操作记录到二进制日志(Binary Log)中,但不等待从库确认接收和应用这些日志
- 从库通过连接到主库,读取并应用这些二进制日志,实现数据的同步
优点:
- 性能高:主库不需要等待从库的确认,写操作的延迟较低。
- 配置简单:默认的复制模式,无需额外配置。
缺点:- 数据一致性较差:从库的数据同步可能存在延迟,主库故障时可能丢失部分数据。
- 单点故障风险:主库仍然是单点写入,存在单点故障风险。适用场景:
- 对性能要求较高,但对数据一致性要求不严格的场景。
- 读操作远多于写操作的场景。
2、全同步复制(Fully syncchronous replication)
工作原理:
- 主库在执行写操作后,必须等待所有从库确认接收并应用了二进制日志,才能返回成功。
- 所有从库的数据都是实时同步的,确保数据的一致性。
优点:
- 数据一致性高:所有从库的数据都是实时同步的,确保数据的一致性。
- 高可用性:从库可以作为主库的备份,提供数据的安全性和可靠性。
缺点:- 性能较低:主库需要等待所有从库的确认,写操作的延迟较高。
- 配置复杂:需要确保所有从库的网络连接稳定,配置和管理较为复杂。
适用场景:- 对数据一致性要求极高的场景。
- 金融、交易等对数据可靠性要求极高的应用。
3、半同步复制(Semi-synchronous replication)
工作原理:
- 主库在执行写操作后,必须等待至少一个从库确认接收了二进制日志,但不等待从库应用这些日志。
- 从库在接收到二进制日志后,异步地应用这些日志,实现数据的同步。
优点:
- 性能和一致性的平衡:主库不需要等待所有从库的确认,写操作的延迟较低,同时确保至少一个从库接收到数据。
- 配置相对简单:只需配置半同步插件,无需复杂的网络配置。
缺点:- 数据一致性介于异步复制和全同步复制之间:从库的数据同步可能存在延迟,但比异步复制更可靠。
- 单点故障风险:主库仍然是单点写入,存在单点故障风险。
适用场景:- 对性能和数据一致性都有一定要求的场景。
- 需要一定程度的数据冗余和高可用性的应用。
4、配置半同步复制
1. 安装半同步插件
在主库和从库上安装半同步插件:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2. 启用半同步复制
在主库上启用半同步复制:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
在从库上启用半同步复制:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
3. 重启从库的复制线程
在从库上重启复制线程以应用半同步复制配置:
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
4. 检查半同步复制状态
在主库上检查半同步复制状态:
SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
在从库上检查半同步复制状态:
SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
半同步复制模式配置示例:
实验基于主主同步结构,启用半同步复制模式,以便切换角色后能继续使用半同步复制
- 服务器角色:node15为主-从(Master1/Slave2),node16为主-从(Master2/Slave1);
补充:若是主从模式,则对应角色加载相应的模块和启用模式;
步骤1:查看MySQL是否允许动态加载模块(have_dynamic_loading)
mysql> show variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
1 row in set (0.00 sec)
步骤2:启用半同步复制模式
方法1:修改配置文件(永久配置)(node15、node16操作)
[root@node15 ~]# vim /etc/my.cnf
[mysqld]
server_id=15
log_bin=master15
plugin-load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" //加载半同步模块
rpl_semi_sync_master_enabled=1 //主库-启动半同步模式
rpl_semi_sync_slave_enabled=1 //从库-启动半同步模式
...
[root@node15 ~]# systemctl restart mysqld //重启服务[root@node16 ~]# vim /etc/my.cnf
[mysqld]
server_id=16
log_bin=master16
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
...
[root@node16 ~]# systemctl restart mysqld //重启服务
方法2:MySQL命令行下配置(重启MySQL服务失效)
① 下载对应的半同步模块
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> install plugin rpl_semi_sync_master soname 'semisync_slave.so';
② 启用半同步复制,默认是关闭的
mysql> set global rpl_semi_sync_master_enabled = 1;
mysql> set global rpl_semi_sync_slave_enabled = 1;
步骤3:验证,查看半同步模块是否安装,半同步模式是否启用
① 查看 information_schema.plugins 表,模块是否安装成功
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
② 查看 "rpl_semi_sync_%_enabled" 是否启用半同步模式
mysql> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_slave_enabled | ON |
+------------------------------+-------+
2 rows in set (0.00 sec)
总结:
MySQL 复制模式包括异步复制、全同步复制和半同步复制。每种复制模式都有其优缺点和适用场景。异步复制性能高但数据一致性较差,全同步复制数据一致性高但性能较低,半同步复制在性能和数据一致性之间取得平衡。选择合适的复制模式需要根据实际需求、系统规模和业务特点进行综合考虑。通过合理的配置和管理,可以充分发挥不同复制模式的优势,提高数据库的可用性、扩展性和数据安全性。
思维导图:
小结:
本篇章节为【第四阶段】RDBMS2-DAY1 的学习笔记,这篇笔记可以初步了解到 MySQL主从同步(概述、原理、构建主从同步)、主从同步结构类型(主多从、主从从、主主)、复制模式(异步、半同步)、启用半同步复制,除此之外推荐参考相关学习网址:
-
参考:mysql数据库主从同步复制模式_mysl的主从同步的方式-CSDN博客
Tip:毕竟两个人的智慧大于一个人的智慧,如果你不理解本章节的内容或需要相关笔记、视频,可私信小安,请不要害羞和回避,可以向他人请教,花点时间直到你真正的理解。