MySQL稳定性提高方案：主从复制

青旅半醒 2022-07-16 02:10 181阅读 0赞

主从复制技术通过在主服务器（master）和从服务器（slave）之间切分处理客户端查询的负荷，可以得到更快的数据读取速度。在从服务器执行SELECT查询（读操作），以降低主服务器的查询处理负荷；在主服务器执行增、删、改（写操作），并保持主、从服务器的数据同步。

MySQL数据库的主从复制有以下优点：

*  增加健壮性。主服务器出现问题时，切换到从服务器作为备份。
 *  优化响应时间。不要同时在主从服务器上进行写操作，这样可能引起冲突。
 *  在从服务器同步数据（备份）过程中，主服务器继续处理客户端的写操作。

**1. 主从复制的工作原理**

主从复制通过三个过程实现，其中一个过程发生在主服务器上，另外两个过程发生在从服务器上。具体情况如下：

主服务器将用户对数据库的写操作以二进制格式保存到 Binary Log（二进制日志）文件中， 然后由 Binlog Dump 线程将二进制日志文件传输给从服务器。

从服务器通过一个 I/O 线程将主服务器的二进制日志文件中的写操作复制到一个叫 Relay Log 的中继日志文件中。

从服务器通过另一个 SQL 线程将 Relay Log 中继日志文件中的写操作依次在本地执行，从而实现主从服务器之间的数据的同步。

（1）BinLog Dump线程

该线程运行在主服务器上，主要工作是把 Binary Log 二进制日志文件的数据发送给从服务器。

使用 SHOW PROCESSLIST 语句，可查看该线程是否正在运行。

（2）I/O线程

从服务器执行 START SLAVE 语句后，会创建一个 I/O 线程。此线程运行在从服务器上，与主服务器建立连接，然后向主服务器发出同步请求。之后，I/O 线程将主服务器发送的写操作复制到本地 Relay Log 日志文件中。

使用 SHOW SLAVE STATUS 语句，可查看 I/O 线程状态。

（3）SQL线程

该线程运行在从服务器上，主要工作是读取 Relay Log 日志文件中的更新操作，并将这些操作依次执行，从而使主从服务器的数据保持同步。

**2. 主从复制配置**

需要在主、从服务器分别进行相关配置，方法如下：

（1）确定主从服务器的MySQL版本。

MySQL不同版本的 BinLog 格式可能不一样，最好采用相同的版本。如果达不到要求，必须保证主服务器版本不高于从服务器的版本。

关于查看MySQL的版本，有很多种方法，可参考： [http://blog.csdn.net/lamp\_yang\_3533/article/details/52266320][http_blog.csdn.net_lamp_yang_3533_article_details_52266320]

（2）在主服务器上为从服务器设置一个连接账户，并授予 REPLICATION SLAVE 权限。

每个从服务器使用标准MySQL用户名和密码连接主服务器。假设主服务器IP地址为 192.168.1.101，从服务器的IP地址为 192.168.1.102。

在主服务器中创建连接账户，用户名为 user01 ，密码为  123456，如下：

create user 'user01'@'192.168.1.102' identified by '123456';

在主服务器中，为该账户授予 REPLICATION SLAVE 权限，如下：

grant replication slave on \*.\* to 'user01'@'192.168.1.102' identified by '123456';

（3）配置主服务器

修改主服务器的MySQL配置文件 my.ini，启动二进制日志，指定唯一的 server-id。代码如下：

\[mysqld\]

\#开启二进制日志

log-bin=mysql-bin

\#服务器id

server-id=1

（4）重启主服务器

运行 show master status 语句，查看主服务器的 master 状态，如下：

mysql> show master status;

\+------------------+----------+--------------+------------------+-------------------+

\+------------------+----------+--------------+------------------+-------------------+

\+------------------+----------+--------------+------------------+-------------------+

File表示主服务器正在使用的 binlog 文件；Postion的值与binlog文件的大小相同，表示下一个被记录事件的位置； Binlog\_Do\_DB 和 Binlog\_Ignore\_DB 是主服务器控制写入 binlog 日志文件内容的过滤选项。默认为空，即不做任何过滤。

File和Position两个选项指明了从服务器将从哪个二进制文件的哪个位置开始复制。

（5）配置从服务器

从服务器的配置与主服务器类似，必须提供一个唯一的 server-id（不能跟主服务器的相同），配置如下：

\[mysqld\]

\#开启二进制日志（从服务器不是必须要开启二进制日志）

log-bin=mysql-bin

\#服务器id

server-id=2

（6）重启从服务器

（7）在从服务器中指定主服务器的信息

在命令行窗口连接从服务器后，使用 CHANGE MASTER TO 语句，指定主服务器的信息，不要在配置文件中设置。该语句可以替代在配置文件中指定主服务器的信息，另外，不需要停止从服务器，便可以为从服务器指定不同的主服务器，语句如下：

change master to master\_host='192.168.1.101',master\_user='user01',master\_password='123456',master\_port=3306,master\_log\_file='mysql-bin.000008',master\_log\_pos=498;

参数详解：

master\_host： 主服务器的IP  
master\_user： 主服务器上新创建的用户名  
master\_password： 用户的密码  
master\_port： 主服务器的端口，如果未曾修改，默认即可。

master\_log\_file： 主服务器二进制日志文件的名称，填写查看主服务器的master状态时显示的File的值

master\_log\_pos： 日志的位置，填写查看主服务器的master状态时显示的Position的值

（8） 启动从服务器的slave复制功能

在从服务器中，执行 START SLAVE 语句，开始复制，如下：

start slave;

（9）查看从服务器的slave状态

mysql> show slave status \\G

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

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 192.168.1.101

Master\_User: user01

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: mysql-bin.000009

Read\_Master\_Log\_Pos: 120

Relay\_Log\_File: hp-PC-relay-bin.000004

Relay\_Log\_Pos: 283

Relay\_Master\_Log\_File: mysql-bin.000009

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

如果  Slave\_IO\_Running 和 Slave\_SQL\_Running 的值都为 Yes，则说明主从复制的所有配置已成功，即从服务器已经可以自动与主服务器的数据库数据实现同步了。可在主服务器和从服务器上执行 SHOW PROCESSLIST \\G 语句，查看各个线程的运行情况。

此后，只要主服务器的数据有更新（比如：在 test数据库中新建了一张表或者表中的数据发生了变化），从服务器都会自动与主服务器保持一致。但如果有人刻意改变了从服务器的数据，主服务器中的数据并不会同步更新，除非我们把这两个MySQL服务器设置为互为主从。

注意：从服务器通过读主服务器的二进制日志实现自我更新，对数据库的写操作都要放在主服务器上执行，而从服务器只用来进行读操作。

如果只是为了测试主从复制，也可在同一台主机上安装两个MySQL服务，具体可参考： [http://blog.csdn.net/lamp\_yang\_3533/article/details/52264784][http_blog.csdn.net_lamp_yang_3533_article_details_52264784]

[http_blog.csdn.net_lamp_yang_3533_article_details_52266320]: http://blog.csdn.net/lamp_yang_3533/article/details/52266320
[http_blog.csdn.net_lamp_yang_3533_article_details_52264784]: http://blog.csdn.net/lamp_yang_3533/article/details/52264784