第14章-MySQL数据库的复制

努力的老李

已于 2024-09-25 08:59:31 修改

阅读量1.1k

点赞数 25

分类专栏： MySQL：入门、精练与实战文章标签：数据库 mysql 教程

于 2024-09-25 08:56:04 首次发布

本文链接：https://blog.csdn.net/weixin_45086637/article/details/142513782

版权

MySQL：入门、精练与实战专栏收录该内容

22 篇文章 0 订阅

订阅专栏

学习指引

MySQL复制是MySQL中一个非常重要的功能，主要用于主服务器和从服务器之间的数据复制操作。数据库的复制技术是提高数据库系统并发性、安全性和容错性的重要技术，是构建大型、高性能应用程序的基础。通过复制可以将数据存储在一个分布式的网络环境中，由多个数据库系统来提供数据访问服务，可以提高数据库的响应速度和并发能力。

重点内容

了解MySQL复制的原理
熟悉MySQL复制的用途
掌握配置复制的主从机器的方法
了解MySQL复制的基本模式
掌握管理和维护复制的方法

14.1了解MySQL复制

本节学习数据的复制原理以及复制数据库的作用。

14.1.1复制的原理

MySQL从3.25.15版本开始提供数据库复制（replication）功能。MySQL复制是指从一个MySQL主服务器（Master）将数据复制到另一台或多台MySQL从服务器（Slaves）的过程，将主数据库的DDL和DML操作通过二进制日志传到复制服务器上，然后在从服务器上对这些日志重新执行，从而使主从服务器的数据保持同步。

在MySQL中复制操作是异步进行的，Slaves服务器不需要持续地保持连接接收Master服务器的数据。

MySQL支持一台主服务器同时向多台从服务器进行复制操作，从服务器同时可以作为其他从服务器的主服务器，如果MySQL主服务器的访问量比较大，可以复制数据，然后在从服务器上进行查询操作，从而降低主服务器的访问压力，同时从服务器作为主服务器的备份，可以避免主服务器因为故障丢失数据的问题。

MySQL数据库复制操作大致可以分为以下3个步骤：

（1）主服务器将数据的改变记录到二进制日志（binary log）中。

（2）从服务器将主服务器的binarylog events复制到它的中继日志（relay log）中。

（3）从服务器重做中继日志中的事件，将数据的改变与从服务器保持同步。

首先主服务器会记录二进制日志，在每个事务更新数据之前，主服务器将这些操作的信息记录在二进制日志里面，在事件写入二进制日志后，主服务器通知存储引擎提交事务。

Slave上面的I/O进程连接上Master，并发出日志请求，Master接收到来自Slave的I/O进程的请求后，通过负责复制的I/O进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave的I/O进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。Slave的I/O进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到Master端的bin-log的文件名和位置记录到master-info文件中。

Slave的SQL进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

MySQL复制环境90%以上都是一个Master带一个或者多个Slave的架构模式。如果Master和Slave的压力不是太大，异步复制的延时一般都很少，尤其是Slave端的复制方式改成两个进程处理之后更是减小了Slave端的延时。

14.1.2复制的用途

MySQL是目前世界上使用最广泛的免费数据库，相信很多从事相关工作的工程师都接触过。在实际的生产环境中，无论是安全性、高并发性以及高可用性等方面，由单台MySQL作为独立的数据库是完全不能满足实际需求的，因此一般需要通过主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力，或者是用来作为主备机的设计，保证当主机crash之后在很短的时间内就可以将应用切换到备机上继续运行。

通过复制可以带来以下几个方面的优势：

数据库集群系统具有多个数据库节点，在单个或多个节点出现故障的情况下，其他正常节点可以继续提供服务。
如果主服务器上出现了问题，可以切换到从服务器上。
通过复制可以在从服务器上执行查询操作，降低主服务器的访问压力，实现数据分布和负载平衡。
可以在从服务器上进行备份，以避免备份期间影响主服务器的服务。

14.2配置复制环境

本节通过案例来学习MySQL如何配置主从复制的功能。

14.2.1配置复制的主从机器

在Windows环境下，如果想实现主从复制功能，需要准备操作环境。选择配置的负责机器如下。

主服务器：Master，IP地址为192.168.1.100，操作系统为Windows 7，MySQL版本为mysql-installer-community-5.7.20.msi。
从服务器：Slave，IP地址为192.168.1.108，操作系统为Windows 7，MySQL版本为mysql-installer-community-5.7.20.msi。

14.2.2在Windows环境下实现主从复制

准备好两台安装MySQL 5.7的计算机，之后即可实现两台MySQL服务器的主从复制备份操作。

在Windows操作系统下安装好两台主机的MySQL服务器，配置好两台主机的IP地址，实现两台计算机可以网络连通。

配置Master的相关配置信息，在Master主机上开启binlog日志，首先查看datadir的具体路径。

show variables like '%datadir%';

此时需要打开“C:\ProgramData\MySQL\MySQL Server 5.7\Data\MySQL\MySQLServer 5.7”下面的配置文件my.ini，添加如下代码，开启binlog功能。

[mysql]
log_bin="D:.MySQLlog/binlog"
expire_logs_days = 10
max_binlog_size = 100M

此时需要在D盘中创建MySQLlog文件夹，binlog日志记录在该文件夹里面，该配置中其他参数的含义如下。

（1）expire_logs_days：表示二进制日志文件删除的天数。

（2）max_binlog_size：表示二进制日志文件最大的大小。

在登录MySQL之后，可以执行showvariables like '%log_bin%'语句来测试log_bin是否成功开启，语句的执行如下：

show variables like '%log_bin%';

如果log_bin参数的值为ON，那么表示二进制日志文件已经成功开启；如果为OFF，那么表示二进制日志文件开启失败。

在Master上配置复制所需要的账户，这里创建一个名为repl的用户，%表示任何远程地址的repl用户都可以连接Master主机，语句的执行如下：

grant replication slave on *.* to repl@'%' identified by '123';

flush privileges;

在my.ini配置文件中配置Master主机的相关信息。

这些配置语句的含义如下。

（1）server-id：表示服务器标识id号，Master和Slave主机的server-id不能一样。

（2）binlog-do-db：表示需要复制的数据库，这里以test数据库为例。

（3）binlog-ignore-db：表示不需要复制的数据库。

重启Master主机的MySQL 5.7服务，然后输入show master status查询Master主机的信息。

show master status \G;

将Master主机的数据备份出来，然后导入到Slave主机中，具体执行语句如下：

C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump -u root -p -h localhost test > C:\a.text

配置Slave主机（192.168.1.108）在“C:\ProgramData\MySQL\MySQL Server 5.7\Data\MySQL\MySQLServer 5.7”下面的配置文件my.ini，具体配置信息如下：

[mysql]
dafault-character-set=utf8
log_bin="D:/MySQLlog/binlog"
expire_logs_days=10
max_binlog_size = 100M
[mysql]
server-id = 2

注意，在配置Slave主机中的my.ini文件的时候需要将server-id = 2写到[mysqld]后面，另外，如果配置文件中还有log_bin的配置，可以将它注释掉。

重启Slave主机（192.168.1.108），在Slave主机（192.168.1.108）的MySQL中执行如下命令，关闭Slave服务。

stop slave;

设置Slave从机实现复制的相关信息，命令的执行如下：

change master to
master_host='192.168.1.100',
master_user='repl',
master_password='123',
master_log_file='binglog.000003',
master_log_pos=120;

各个参数代表的具体含义如下。

（1）master_host：表示实现复制的主机的IP地址。

（2）master_user：表示实现复制的登录远程主机的用户。

（3）master_password：表示实现复制的登录远程主机的密码。

（4）master_log_file：表示实现复制的binlog日志文件。

（5）master_log_pos：表示实现复制的binlog日志文件的偏移量。

14.2.3MySQL复制的基本模式

在MySQL 5.1之后的版本中，在复制方面的改进就是引进了新的复制技术——基于行的复制。这种新技术就是关注表中发生变化的记录，而非以前的照抄binlog模式。从MySQL5.1.12开始，可以用以下3种模式来实现：

（1）基于SQL语句的复制（statement-based replication，SBR）；

（2）基于行的复制（row-basedreplication，RBR）；

（3）混合模式复制（mixed-basedreplication，MBR）。

相应地，binlog的格式也有3种，即STATEMENT、ROW、MIXED。在MBR模式中，SBR模式是默认的。在运行时可以动态地改变binlog的格式。设定主从复制模式的方法非常简单，只要在以前设定复制配置的基础上再加一个参数即可：

binlog_format="STATEMENT"
binlog_format="ROW"
binlog_format="MIXED"

当然，也可以在运行时动态修改binlog的格式，例如：

SET SESSION binlog_format = 'STATEMENT';
SET SESSION binlog_format = 'ROW';
SET SESSION binlog_format = 'MIXED';
SET GLOBAL binlog_format = 'STATEMENT';
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_format = 'MIXED';

14.3管理与维护复制

在数据复制环境配置完成后，数据库管理员需要进行日常的监控和管理维护工作，以便能够及时发现问题和解决问题，以此来保证主从数据库能够正常工作。

14.3.1了解服务器的状态

一般使用show slave status命令来检查从服务器，例如：

show slave status\G;

在查看从服务器信息时，首先要查看Slave_IO_Running和Slave_SQL_Running这两个进程状态是否为“Yes”，Slave_IO_Running表明，此进程是否能够由从服务器到主服务器正确地读取binlog日志，并写到从服务器的中继日志中；Slave_SQL_Running则表明能否读取并执行中继日志中的bingog信息。

14.3.2服务器复制出错的原因

在某些情况下会出现从服务器更新失败，此时首先需要确定是否因为主从服务器的表不同造成的。如果是表结构不同导致的，则修改从服务器上的表与主服务器上的表一致，然后重新执行start slave命令。服务器复制出错的常见问题如下。

问题一：出现“log event entryexceeded max_allowed_pack”错误。

如果在应用中出现使用大的blog列或者长字符串，那么在从服务器上回复时可能会出现“log event entryexceeded max_allowed_pack”的错误，这是因为含有大文本的记录无法通过网络进行传输而导致错误，解决方法是在主从服务器上添加max_allowed_packet参数，该参数默认设置为1MB，例如：

SHOW VARIABLES LIKE 'MAX_ALLOWED_PACKET';

同时在my.cnf中设置max_allowed_packet=16MB，数据库重新启动之后该参数将有效。

问题二：多主复制时的自增长变量冲突问题。

大多数情况下使用一台主服务器对一台或者多台从服务器，但是在某些情况下可能会存在多个服务器配置为复制主服务器，在使用auto_increment时应采取特殊步骤以防止键值冲突，否则插入行时多个主服务器会试图使用相同的auto_increment值。

服务器变量auto_increment_increment和auto_increment_offset可以协调多主服务器复制和auto_increment列。

在多主服务器复制到从服务器的过程中迟早会发生主键冲突，为了解决这种情况，可以将不同的主服务器的这两个参数重新设置，可以在A数据库服务器上设置auto_increment_increment=1 、auto_increment_offset=1，此时B数据库服务器上设置auto_increment_increment=1、auto_increment_offset=0。

下面的例子演示修改这两个参数后的效果，具体操作步骤如下。

创建auto_t表，系统默认的auto_increment_increment和auto_increment_offset参数都是1，增加数据默认的也是以增加幅度为1进行增加，命令的执行如下：

CREATE TABLE auto_t(
	DATA INT PRIMARY KEY AUTO_INCREMENT
)ENGINE=MYISAM DEFAULT CHARSET=gbk;

SHOW VARIABLES LIKE 'auto_inc%';

INSERT INTO auto_t VALUES (NULL),(NULL),(NULL);

SELECT * FROM auto_t;

重新设置auto_increment_increment参数的值为10，然后插入数据，命令的执行如下：

SET @@auto_increment_increment=10;

SHOW VARIABLES LIKE 'auto_inc%';

INSERT INTO auto_t VALUES (NULL),(NULL),(NULL);

SELECT * FROM auto_t;

从测试效果来看，每次递增值是10，下面看auto_increment_offset参数的用法。

重新设置auto_increment_offset参数的值为5，再插入数据，命令的执行如下：

SET @@auto_increment_offset=5;

INSERT INTO auto_t VALUES (NULL),(NULL),(NULL);

SELECT * FROM auto_t;

从插入的记录可以看出，auto_increment_increment参数是每次增加的量，而auto_increment_offset参数设置的是每次增加后的偏移量，也就是每次按照10累加后还需要增加5个偏移量。

14.4就业面试技巧与解析

14.4.1面试技巧与解析（一）

面试官：在Windows环境下无法开启binlog怎么办？

应聘者：有些读者反映MySQL 5.7数据库执行“show variables like’log_bin’;”语句后不管怎么设置查询的值一直是OFF状态。假设用户安装好后是在“D:/ProgramFiles/MySQL/MySQL Server 5.7”下面，在将my-default.ini文件改成my.ini文件后，不管怎么改，环境变量都不能生效，此时读者可以执行“show variables like ‘datadir’;”。安装好之后真正生效的my.ini文件不在“D:/ProgramFiles/MySQL/MySQL Server 5.7”下面，用“show variables like’datadir’;”展现的才是真正的配置文件的路径。