【MySQL数据库】MySQL主从复制

MySQL主从复制

MySQL主从复制是一种数据库复制技术，用于将数据从一个MySQL数据库服务器（称为主服务器或主库）复制到一个或多个MySQL数据库服务器（称为从服务器或从库）。这种技术主要用于数据备份、读写分离、负载均衡等场景，以提高数据库系统的可用性和性能。
主从复制的应用场景
数据备份：通过主从复制，可以将主服务器的数据实时同步到从服务器，从而实现数据备份和灾难恢复。
读写分离：在主服务器上处理写操作（如INSERT、UPDATE等），在从服务器上处理读操作（如SELECT等），以减轻主服务器的负载并提高系统的吞吐量。
负载均衡：通过配置多个从服务器，可以将读操作分散到不同的从服务器上，从而实现负载均衡。

MySQL主从复制的分类

1. 基于SQL语句的复制（STATEMENT）：
   • 优点：执行效率高，因为只需要复制SQL语句；占用空间小，因为只存储了SQL语句而非实际数据；性能消耗低，因为复制过程相对简单。
   • 缺点：在高并发高负载情况下，由于SQL语句的执行顺序、执行时间等因素，可能导致主从复制精确度下降。例如，当主库和从库选择的索引不一致时，相同的SQL语句可能产生不同的结果集。
2. 基于行的复制（ROW）：
   • 优点：精确度高，因为复制的是具体的行数据变化，不受SQL语句执行顺序、执行时间等因素的影响。
   • 缺点：占用空间大，因为需要复制每一行数据的变化；性能消耗高，因为复制过程需要处理大量的行数据。
3. 混合类型的复制（MIXED）：
   • 原理：默认采用基于SQL语句的复制，当发现基于语句的复制无法保证精确复制时（如上述索引不一致的情况），会切换到基于行的复制。
   • 优点：结合了基于SQL语句复制和基于行复制的优点，既保证了复制效率，又提高了复制精确度。

MySQL主从复制原理

MySQL主从复制的原理主要涉及两个日志（bin log和relay log）和三个线程（IO线程、SQL线程、DUMP线程）：

1. 两个日志：
   • bin log（二进制日志）：主库上的数据更新操作（如INSERT、UPDATE、DELETE等）会被记录到二进制日志中。
   • relay log（中继日志）：从库上的IO线程会从主库上拉取二进制日志事件，并将其保存到中继日志中。随后，从库上的SQL线程会读取中继日志中的事件，并在本地进行重放（即将事件解析为SQL语句并逐一执行）。
2. 三个线程：
   • DUMP线程：主库上为每个从库的IO线程请求开启的线程，负责发送二进制日志事件给从库。
   • IO线程：从库上用于与主库通信的线程，负责从主库上拉取二进制日志事件并保存到中继日志中。
   • SQL线程：从库上用于读取中继日志中的事件并在本地进行重放的线程。

MySQL主从复制的配置步骤

MySQL主从复制的配置步骤通常包括以下几个步骤：

1. 主从服务器时间同步：确保主从服务器的时间一致，以避免因时间差异导致的数据不一致问题。
2. 修改配置文件：在主从数据库的配置文件中配置二进制日志、中继日志、server-id（每个节点都不同）、gtid等相关参数。
3. 创建复制用户：在主库上创建一个专门用于复制的用户，并授予主从复制权限。
4. 对接主库：在从库上使用CHANGE MASTER TO语句对接主库，并配置相关的复制参数（如主库地址、用户名、密码、二进制日志文件名和位置等）。
5. 启动同步：在从库上使用START SLAVE语句启动同步过程。
6. 检查状态：在从库上使用SHOW SLAVE STATUS语句查看IO线程和SQL线程的状态是否都是YES，以确保同步过程正常进行。

MySQL主从复制的同步模式

MySQL主从复制支持以下三种同步模式：

1. 异步复制：
   • 原理：主库在执行完客户端提交的事务后，会立即将结果返回给客户端，而不关心从库是否已经接收并处理这些事务。
   • 优点：效率高，因为主库不需要等待从库的响应。
   • 缺点：数据一致性差，如果主库在事务提交后崩溃，而这些事务还没有被复制到从库上，那么从库上的数据将不完整。
2. 半同步复制：
   • 原理：主库在执行完客户端提交的事务后，会等待至少一个从库接收并写到relay log中才返回给客户端。
   • 优点：相对于异步复制，提高了数据的安全性。
   • 缺点：仍然存在一定的延迟（至少一个TCP/IP往返的时间），且需要在低延时的网络中使用。
3. 全同步复制：
   • 原理：主库在执行完客户端提交的事务后，会等待所有从库都执行了该事务才返回给客户端。
   • 优点：数据一致性好，因为所有从库都执行了相同的事务。
   • 缺点：性能差，因为主库需要等待所有从库的响应才能继续处理下一个事务。

MySQL主从复制实验

环境准备

• Master 服务器：IP 地址 192.168.80.10，运行 MySQL 5.7
• Slave1 服务器：IP 地址 192.168.80.11，运行 MySQL 5.7
• Slave2 服务器：IP 地址 192.168.80.12，运行 MySQL 5.7
• Amoeba 服务器：IP 地址 192.168.80.20，运行 JDK 1.6 和 Amoeba
• 客户端 服务器：IP 地址 192.168.80.30，运行 MySQL 客户端

关闭防火墙和 SELinux

在所有服务器上执行以下命令：

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

时间同步

主服务器设置

1. 安装 NTP：

yum install ntp -y

2. 编辑 NTP 配置文件：

vim /etc/ntp.conf

在末尾添加：

server 127.127.80.0
fudge 127.127.80.0 stratum 8

3. 启动 NTP 服务：

service ntpd start

从服务器设置

1. 安装 NTP 和 ntpdate：

yum install ntp ntpdate -y

2. 启动 NTP 服务：

service ntpd start

3. 使用 ntpdate 进行时间同步：

/usr/sbin/ntpdate 192.168.80.10

4. 设置定时任务每 30 分钟同步一次时间：

crontab -e

添加以下行：

*/30 * * * * /usr/sbin/ntpdate 192.168.80.10

MySQL 主从复制配置

主服务器配置

1. 编辑 MySQL 配置文件：

vim /etc/my.cnf

在 [mysqld] 部分添加或修改以下配置：

server-id=11
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days=7
max_binlog_size=500M
skip_slave_start=1# 数据安全性高的设置
innodb_flush_log_at_trx_commit=1
sync_binlog=1# 或者性能较快的设置（推荐在磁盘IO压力大时使用）
# innodb_flush_log_at_trx_commit=2
# sync_binlog=500

2. 重启 MySQL 服务：

systemctl restart mysqld

3. 授权从服务器连接：

GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.80.%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

4. 查看主服务器状态：

SHOW MASTER STATUS;
如显示以下：
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysql-bin.000002  |    339   |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

记录 File 和 Position 的值。
File 列显示日志名，Position 列显示偏移量

从服务器配置（以 Slave1 为例，Slave2 配置类似）

1. 编辑 MySQL 配置文件：

vim /etc/my.cnf

在 [mysqld] 部分添加或修改以下配置：

[mysqld]
# 设置服务器的唯一ID，用于复制环境中的主从识别
server-id=22# 启用中继日志，用于复制过程中的数据传输
relay-log=relay-bin# 设置InnoDB存储引擎的缓冲池大小，这里设置为2GB
# 缓冲池是用于缓存数据和索引的内存区域，较大的缓冲池可以提高性能
innodb_buffer_pool_size=2048M# 关闭二进制日志的同步写入到磁盘操作，可以提高性能但降低数据安全性
# 在某些情况下，你可能希望在确认事务提交后再写入磁盘，这时可以设置为1
sync_binlog=0# 设置InnoDB事务日志的刷新策略，这里设置为每秒刷新一次
# 这个值可以在性能和数据安全性之间做出权衡，0表示每次事务提交都刷新，1表示每次事务提交都写入并刷新，2表示每秒刷新一次
innodb_flush_log_at_trx_commit=2# 禁止从服务器更新二进制日志，适用于不需要将从服务器的更改记录到二进制日志的场景
log-slave-updates=0# 启用中继日志自动恢复功能，当从服务器重启时，可以自动应用未完成的中继日志事件
relay_log_recovery=1

在添加或修改这些配置项后，你需要重启MySQL服务以使更改生效。这些配置项可以根据你的具体需求和服务器的性能进行调整。例如，如果你对数据安全性有更高的要求，可能会将sync_binlog设置为1，并且可能需要调整innodb_flush_log_at_trx_commit的值来平衡性能和安全性。
2. 重启 MySQL 服务：

systemctl restart mysqld

3. 配置从服务器同步：

CHANGE MASTER TO
master_host='192.168.80.10',
master_port=3306,
master_user='myslave',
master_password='123456',
master_log_file='mysql-bin.000002',  -- 使用从主服务器获取到的文件名
master_log_pos=339;                 -- 使用从主服务器获取到的位置

4. 启动从服务器同步：

START SLAVE;

5. 查看从服务器状态：

SHOW SLAVE STATUS\G;

确保 Slave_IO_Running 和 Slave_SQL_Running 的值都是 Yes。

验证

验证主从复制效果：
在主服务器上进入执行

create database db_test;

在从服务器中查看

show databases;

常见问题

• 如果 Slave_IO_Running 是 No，可能的原因包括：
  • 网络不通
  • MySQL 配置文件错误
  • 密码、日志文件名、位置偏移量不正确
  • 防火墙未关闭
• 如果遇到错误，可以尝试执行 RESET SLAVE; 来重置从服务器配置，然后重新配置。

MySQL半同步复制

MySQL主从复制的几个同步模式

1. 异步复制（Asynchronous replication）
   • 描述：主库执行完事务后，立即返回结果给客户端，不关心从库是否已经接收并处理。
   • 问题：如果主库崩溃，已经提交但尚未传到从库的事务会丢失，可能导致数据不一致。
2. 全同步复制（Fully synchronous replication）
   • 描述：主库等待所有从库都执行完事务后才返回结果给客户端。
   • 性能影响：由于需要等待所有从库，性能会受到严重影响。
3. 半同步复制（Semisynchronous replication）
   • 描述：主库等待至少一个从库接收到并写到relay log中后才返回结果给客户端。
   • 优点：提高了数据安全性，但会有一定延迟（至少一个TCP/IP往返时间）。
   • 适用场景：适合低延时网络环境。

半同步复制配置

主数据库配置

vim /etc/my.cnf

在 [mysqld] 区域添加以下内容：

# 加载mysql半同步复制的插件
plugin-load=rpl_semi_sync_master=semisync_master.so
# 开启半同步复制功能，设置为"ON"或"1"
rpl_semi_sync_master_enabled=ON
# 超时时间，单位为毫秒，这里设置为1000ms，即1秒
rpl-semi-sync-master-timeout=1000

保存并重启MySQL服务：

systemctl restart mysqld

从数据库配置

vim /etc/my.cnf

在 [mysqld] 区域添加以下内容：

# 加载mysql半同步复制的插件
plugin-load=rpl_semi_sync_slave=semisync_slave.so
# 开启从库的半同步复制功能
rpl_semi_sync_slave_enabled=ON

保存并重启MySQL服务：

systemctl restart mysqld

查看半同步是否在运行

在主数据库执行：

-- 查看主库半同步状态
SHOW STATUS LIKE 'Rpl_semi_sync_master_status';
-- 查看半同步复制超时时间
SHOW VARIABLES LIKE 'rpl_semi_sync_master_timeout';

在从数据库执行（可能需要重启IO线程后状态才会更新为ON）：

-- 查看从库半同步状态
SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';

重启从数据库上的IO线程：

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

再次在主库查询半同步状态：

SHOW STATUS LIKE '%Rpl_semi%';

参数说明

• Rpl_semi_sync_master_clients：半同步复制客户端的个数。
• Rpl_semi_sync_master_net_avg_wait_time：平均等待时间（单位毫秒）。
• Rpl_semi_sync_master_net_wait_time：总共等待时间。
• Rpl_semi_sync_master_net_waits：等待次数。
• Rpl_semi_sync_master_no_times：关闭半同步复制的次数。
• Rpl_semi_sync_master_no_tx：表示没有成功接收从库提交的次数。
• Rpl_semi_sync_master_status：表示当前是异步模式还是半同步模式，ON为半同步。
• Rpl_semi_sync_master_timefunc_failures：调用时间函数失败的次数。
• Rpl_semi_sync_master_tx_avg_wait_time：事务的平均传输时间。
• Rpl_semi_sync_master_tx_wait_time：事务的总共传输时间。
• Rpl_semi_sync_master_tx_waits：事务等待次数。
• Rpl_semi_sync_master_wait_pos_backtraverse：可以理解为"后来的先到了，而先来的还没有到的次数"。
• Rpl_semi_sync_master_wait_sessions：当前有多少个session因为从库的回复而造成等待。
• Rpl_semi_sync_master_yes_tx：成功接受到从库事务回复的次数。

注意：

• 如果半同步复制发生超时（由rpl_semi_sync_master_timeout参数控制），会暂时关闭半同步复制，转而使用异步复制。
• 在一主多从的架构中，开启半同步复制不要求所有从库都是半同步复制。
• MySQL 5.7 版本极大地提升了半同步复制的性能，通过独立出一个ack collector thread专门用于接收从库的反馈信息，解决了5.6版本中dump thread成为性能瓶颈的问题。

问答

在什么情况下半同步复制会将为异步复制？
当主库在半同步复制超时时间内没有收到从库的响应，就会降为异步复制，半同步复制超时时间参数为rpl_semi_sync_master_timeout（默认值为10s）
当主库发送完一个事务事件后，主库在半同步复制超时时间内收到了从库的响应，就会恢复为半同步复制

mysql主从复制不一致问题如何解决？
1.在主库使用mysqldump对数据不一致的库或表进行完全备份，并show master status检查当前的二进制日志和偏移量

mysqldump -uroot -p密码 库名 表名 > mysql_bak.sql

show master status;

2.使用scp把备份文件远程复制到从库，在从库关闭同步，导入数据备份

scp  mysql_bak.sql  从库IP:目录/

stop slave;

mysql -uroot -p密码 库名 <  mysql_bak.sql

3.在从库使用change master to 重新进行主从复制对接，再开启同步

change master to master_host='主库IP',master_port=3306,master_user='用户',master_password='密码',master_log_file='binlog文件名',master_log_pos=偏移量;

change master to master_host='主库IP',master_port=3306,master_user='用户',master_password='密码',master_auto_position=1;       #使用GTID主从复制模式

start slave;

主要步骤： 导出主库数据，恢复到从库，重新开启主从同步。

关于mysql主从复制延迟问题
原因：主库可以并发多线程执行写入操作，而从库的SQL线程默认是单线程串行化复制，从库的复制效率可能会跟不上主库的写入速度

如何判断发生了主从复制延迟？
可通过在从库执行show slave status查看输出的Seconds_Behind_Master参数的值来判断是否发生了主从复制延迟。如果值为正值则表示已经出现主从复制延迟，数值越大表示从库落后主库越多

导致主从复制延迟有哪些因素？

• 主库写入操作并发量太大，事务数太多
• 网络延迟
• 从库硬件比主库差太多
• 使用了全同步复制
• 事务太大，慢SQL语句太多

延迟问题的解决：
网络优化：
将从库分布在宇主库相同的局域网或网络延迟较小的环境中
硬件优化：
从库配置更好的硬件，使用更高规格的CPU和内存，使用固态硬盘和RAID提升磁盘的读写性能
配置优化：
innodb_buffer_pool_size #从库加大Innodb引擎缓存池的大小，让更多的数据读写在内存中完成，减少磁盘IO压力
slave_parallel_workers #从库使用多线程并行复制
log-slave-updates=0 #从库从主库复制的数据时不写入到二进制日志，减少磁盘IO压力
innodb_flush_log_at_trx_commit=2 #从库不使用双1设置，减少磁盘IO压力
sync_binlog=0
架构优化：
主从复制的同步模式采用 半同步复制或异步复制，采用读写分离，避免读写操作相互阻塞
操作优化：
将大型事务拆分成多个较小的事务
优化一些SQL语句操作，比如可将多个操作合并到一个SQL语句执行
创建索引，避免全表扫描