首页 > 数据库 >【MySQL数据库】MySQL主从复制

【MySQL数据库】MySQL主从复制

时间：2024-10-26 12:48:25浏览次数：7  
标签：主库 主从复制 数据库 复制 master 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
  1. 编辑 NTP 配置文件：
vim /etc/ntp.conf

在末尾添加：

server 127.127.80.0
fudge 127.127.80.0 stratum 8
  1. 启动 NTP 服务：
service ntpd start

从服务器设置

  1. 安装 NTP 和 ntpdate：
yum install ntp ntpdate -y
  1. 启动 NTP 服务：
service ntpd start
  1. 使用 ntpdate 进行时间同步：
/usr/sbin/ntpdate 192.168.80.10
  1. 设置定时任务每 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
  1. 重启 MySQL 服务：
systemctl restart mysqld
  1. 授权从服务器连接：
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.80.%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
  1. 查看主服务器状态：
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
  1. 配置从服务器同步：
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;                 -- 使用从主服务器获取到的位置
  1. 启动从服务器同步：
START SLAVE;
  1. 查看从服务器状态：
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语句执行
创建索引，避免全表扫描

标签：主库,主从复制,数据库,复制,master,MySQL,从库
From： https://blog.csdn.net/Karoku/article/details/143186113

相关文章

  • 【MySQL数据库】MySQL读写分离
    文章目录读写分离概念读写分离的动机读写分离的适用场景主从复制与读写分离MySQL读写分离原理MySQL读写分离的实现方式代表性程序MySQL读写分离实验搭建MySQL读写分离Amoeba服务器配置测试读写分离问答读写分离概念读写分离是为了优化数据库性能，通过将写......
  • 【MySQL基础】数据库与表的基本操作：从创建到管理
    文章目录写在前面：1、数据库的创建和管理1.创建数据库：CREATEDATABASE注意事项：2.查看已有数据库：SHOWDATABASES3.删除数据库：DROPDATABASE防止误删4.总结2、表的创建与管理1.创建数据表：CREATETABLE2.查看表结构：DESCRIBE表名3.删除数据表：DROPTABLE4.修改表结......
  • 织梦怎么进数据库,织梦网站源码在哪里看数据库
    当织梦CMS(DedeCMS)无法连接到数据库时，可能是由多种原因引起的。以下是一些常见的原因及解决方法：1. 数据库服务未启动原因：MySQL服务没有运行。解决方法：Linux：使用命令 sudosystemctlstartmysql 或 sudoservicemysqlstart 启动MySQL服务。Windows：打开“服务”管......
  • 解决Mysql:ERROR 1045 (28000):Access denied for user ‘root‘@‘localhost‘ (usin
    遇到 ERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:NO) 错误时，通常是因为尝试以root用户身份登录MySQL时没有提供密码或提供的密码不正确。以下是解决此问题的步骤：检查是否设置了密码：如果从未为root用户设置过密码，可以尝试在命......
  • sql数据库置疑
    sql数据库置疑以XXX数据库为例:1.停止SQLServer的服务,备份D:\XXX_data目录下的文件2.启动SQLServer服务,创建一个新的数据库，命名为原来数据库的名字若创建新数据库提示已存在，需要在查询分析器执行dropdatabaseXXX来删除数据库，然后重新建立新数据库。3.停止SQLServer4.......
  • mysql5.7主从搭建
    mysql下主从（主主）搭建首先要准备两台服务器，一台主服务器(Master)，另一台从服务器(Slave)，然后要保证Master与Slave的版本要相同且Master不能高于Slave的版本，一般稳健的做法都是使其版本相同，因为MySQL不同版本之间的binlog(二进制日志)格式可能会不一样，最后会导致同步出现异常。参考地......
  • Linux下搭建mysql5.7数据库
    Linux下搭建mysql数据库参考网址：https://www.cnblogs.com/dengshihuang/p/8029092.html系统约定系统版本：Centos7.3 Mysql版本：5.7安装文件下载目录：/data/softwareMysql安装目录：/opt/app/mysql数据库保存位置：/opt/app/mysql/data/mysql日志保存位置：/opt/app/mysql/data/mysql/log/my......
  • Mysql主主搭建
    Mysql主主搭建参考网址：https://www.jianshu.com/p/0fadd3c54875IP1：10.10.133.117（主）IP2：10.10.133.118（从主）环境：Centos7.3前提是两台机器都装好了mysql，要同步的数据库数据相同，mysql版本尽量相同Mysql复制原理master服务器将数据的改变都记录到二进制binlog日志中，只要master上......
  • redis数据库操作指令
    一、数据库操作指令2、redis中库说明对于一个redis服务而言，包含默认有16个数据库给我们使用，从0开始编号，共15号数据库，默认使用的是0号数据库切换库，select库号举例：使用1号库：select1库和库之间数据不共享库和库之间的键可以重名2、redis中清空库的指令清空当前库flush......
  • Mysql 安装(yum)Linux
    yum安装mysql清理环境yumerasemariadbmariadb-servermariadb-libsmariadb-devel-y#移除mariadb的相关组件userdel-rmysql#删除mysql用户rm-rf/etc/my*#删除etc下的配置文件rm-rf/var/lib/mysql#删除var下的配置文件 下载yum源的rpm包（mysql5.7.41......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99