问题描述

在程序上线运行一段时间之后，随着用户量的逐渐增多，单台MySQL开始无法承受所有的压力，为了承载更大的数据库并发，避免单台MySQL宕机，即无法正常提供服务，出现整体应用程序崩溃的情况，此时需要使用MySQL集群，此阶段会出现的典型问题如下：

（1）在生产环境中，当MySQL处于运行状态时应如何备份当前数据？

（2）在某场景下，某个接口需要锁表以便修改数据，而其他读取的线程都处于阻塞等待状态，此时应如何对其进行优化？

（3）并发读取越来越多，单台无法满足业务需求，如何进行处理？

问题分析与解决方案

针对在问题描述中提出的问题，都可以使用MySQL主从复制解决。MySQL主从复制是最常见的解决单台MySQL性能瓶颈的方案之一。在业务复杂的系统中，架构的发展导致业务量越来越大、I/O访问次数越来越多，单台MySQL开始无法满足需求，此时就需要做多库的存储，以便降低磁盘I/O的访问次数，提高单台I/O的访问性能。

MySQL主从复制原理

MySQL主从复制指我们可以把数据从一个MySQL服务器（主服务器、主节点）复制到一个或多个从节点，即从节点可以复制主服务器中的所有数据库实例、特定数据库实例或特定表等。MySQL默认采用异步的复制方式，也就是说，从节点无须一直访问主服务器，而是可以在远程服务器上更新自己的数据。

主服务器也叫作master服务器。当主服务器上的数据发生改变时，主服务器会将数据的更改记录存储在二进制日志中。

从服务器也叫作slave服务器。从服务器会定期对主服务器上的二进制日志进行探测，观测其是否发生了改变。如果主服务器上的数据发生了改变，则从服务器会启动一个I/O线程，请求更新数据，具体过程如下所示：

• （1）客户端SQL更新命令。
• （2）主服务器执行SQL语句。
• （3）主服务器写二进制日志。
• （4）从服务器启动I/O线程。
• （5）从服务器从I/O线程写盘（relay-log）。
• （6）从服务器启动SQL线程读（relay-log）。
• （7）从服务器执行更新命令（relay-info）。

1. 部署过程中需要注意的事项

（1）主服务器和从服务器中的MySQL版本必须相同，否则可能出现未知的异常与错误

（2）主服务器和从服务器的时间必须同步，否则两个线程的时间节点可能对不上，导致同步数据失败。

（3）在MySQL中，一般最少包含两个从服务器。当主服务器与从服务器的数据不同时，可以与第三方进行参照。

2. MySQL主从复制的架构拓扑

（1）一主一从：一主一从指一台服务器作为主服务器（M），另一台服务器作为从服务器（S）。主服务器负责写入或读取数据，从服务器只负责读取数据，并且从服务器会从主服务器上下载数据。一主一从使用场景较为有限，更多的时候是使用一主多从的形式，即主从复制至少由三台服务器组成（一台主服务器和两台从服务器）。当一台服务器的数据出现异常时，可以参考其他服务器上的数据。

（2）主主复制：主主复制类似于常见的集群模式，指把两台服务器都设置为主服务器，即两台服务器既可以分别写入数据，也可以分别从对方那里下载数据。该架构还可以扩展成master+slave+master+slave的形式，即两台主服务器进行主主复制，每台主服务器下面各有一台个人服务器进行主从复制。此架构方案将压力平分给多台服务器，但不是按照写入或读取的方式分配的。

（3）一主多从：一主多从适合写入较少，但读取较多的场景。

（4）多主一从：多主一从适用于写入较多，但读取较少的场景，即由不同的主服务器进行写入，只由一台从服务器进行读取。

（5）联级复制：联级复制指master A→slave B→slave C的架构方式，slaveB和slaveC，会替换掉之前旧的masterA。同时，slaveB和slaveC是新的主从关系，因此，配置成联级复制来迁移数据，另外也方便切换。架构图如下所示。

深入理解MySQL中的二进制日志

MySQL中的二进制日志是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的SQL语句。二进制日志记录了对MySQL进行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其他额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志主要用于数据库恢复和主从复制，以及审计操作。在MySQL主从复制解决方案中，二进制日志是主从复制解决方案的基础。

查看MySQL二进制日志状态

当系统变量log_bin的值为OFF时，表示没有开启二进制日志；当系统变量log_bin的值为ON时，表示开启了二进制日志。在MySQL控制台输入如下命令即可查看二进制日志是否开启：

结果如图

模糊查询命令如下：

结果如图

log_bin和sql_log_bin的区别

log_bin主要用于数据恢复，以及在主从服务器之间同步数据。当MySQL启动时，可以通过配置文件开启二进制日志，而log_bin这个变量仅仅是报告当前二进制日志的状态（是否开启）。如果想要更改二进制日志的开启状态，则需要在更改配置文件后重新启动MySQL。

sql_log_bin是一个动态变量，该变量既可以是局部变量，即只对当前会话生效（Session），也可以是全局变量（Global）。当sql_log_bin为全局变量时，如果修改这个变量，则sql_log_bin只会对新的会话生效，这意味着sql_log_bin对当前会话不再生效。因此一般在全局修改sql_log_bin之后，都要把原来的所有连接关闭（kill）。如果在一连接中将该值设置为OFF，则该连接上的客户端的所有更新操作在MySQL的二进制日志中不会记录日志。因此，当通过log_bin还原数据库时，为了防止将还原的UPDATE命令写入二进制日志中，出现循环复制的现象，可以选择关闭sql_log_bin变量。

开启二进制日志

查看MySQL的配置文件/etc/my.cnf，看看是否有与二进制日志有关的配置：

如果没有，则在/etc/my.cnf的[mysqld]选项中追加以下内容：

如果上述内容不是在[mysqld]选项中新增的，而是在其他选项中新增的，那么即使更改了配置文件/etc/my.cnf，二进制日志也无法启动。下面解释一下代码中各项的含义。

server-id：MySQL的ID属性是唯一值，作用如下。

（1）MySQL的同步数据中是包含server-id的，用于标识该语句最初是从哪个server写入的，所以server-id一定要有。

（2）每一个同步的slave在master上都有对应的一个master线程，该线程就是通过slave的server-id来标识的。

? 每个slave在master上最多有一个master线程，如果两个slave的server-id相同，则后一个连接成功时，前一个会被“踢”掉。

? 在slave主动连接master之后，如果在slave上执行了slave stop，则连接断开，但是master上对应的线程并没有退出。

? 在slave运行之后，master不能再创建一个线程而保留原来的线程，否则在数据同步时可能出现问题。

（3）在MySQL中做主主同步时，多个主需要构成一个环状，但在同步时又要保证一条数据不会陷入死循环，这就是靠server-id来实现的。

log-bin：打开二进制日志功能。在复制（replication）配置中，master必须打开此项。

binlog-format：二进制日志的模式与配置。在MySQL中，复制二进制日志的方式主要有三种：

• 基于SQL语句的复制（Statement-Based Replication，SBR）。
• 基于行的复制（Row-Based Replication，RBR）。
• 混合模式复制（Mixed-Based Replication，MBR）。

对应的二进制日志模式有三种：Statement Level模式、Row Level模式和Mixed模式，其优点和缺点如表所示。

MySQL默认使用Statement Level模式，推荐使用Mixed模式。对于一些特殊使用，可以考虑使用Row Level模式。例如，通过二进制日志同步数据的修改，会节省很多相关操作，所以对于二进制日志数据处理会变得非常轻松。如 果 采 用 INSERT 、 UPDATE 、 DELETE 等 直 接 操 作 表 ， 则 日 志 格 式 根 据binlog_format的设定而记录。如果采用GRANT、REVOKE、SET PASSWORD等管理语句来操作表，那么一定要采用Statement Level模式记录。

除此之外，还可以对二进制日志进行以下配置：

binlog_cache_size：在一个事务中，二进制日志记录了SQL状态所持有的缓存大小。如果经常使用大的、多声明的事务，则可以把此值设置得大一些，以获取更好的性能。所有从事务来的状态都先被缓存在二进制日志中，在提交后再一次性写入二进制日志中。如果事务比此值大，则使用磁盘上的临时文件来替代。此缓存是在每个连接的事务第一次更新状态时被创建的，属于session级别，通常采用默认值即可，编写方式如下所示：

max_binlog_cache_size：最大二进制日志缓存大小，通常采用默认值即可，编写方式如下所示：

max_binlog_size：如果二进制日志写入的内容超出给定值，则日志就会发生滚动。注意不能把该变量设置为大于1GB或小于4096字节，默认值是1GB。如果正在提交比较大的事务，则二进制日志的大小有可能会超过max_binlog_size值，从而引发报错，通常采用默认值即可，编写方式如下所示：

expire_logs_days：删除超过N天的二进制日志，通常采用默认值即可，编写方式如下所示：

在更改配置文件/etc/my.cnf之后，通过如下命令可重启MySQL服务器，检查是否开启了MySQL的二进制日志文件：

结果如图所示。

重新查看log_bin启动结果，如图所示。

如果在重启MySQL之后，无法正常启动MySQL，或者log-bin没有正常开启，则可以查看Linux系统下的两个日志文件是否有错误：

一 般 来 说 ， 在 输 入 相 对 路 径 时 ， 二 进 制 日 志 的 存 放 地 址为/var/lib/mysql，如图所示。

每次重启MySQL服务器都会生成一个新的二进制日志文件，相当于对二进制日志进行了切换。在切换二进制日志时，会看到mysql-bin文件的number在不断递增。

除二进制日志文件外，还生成了一个.index文件。这个文件中存储了所有二进制日志文件的清单，又称为二进制文件的索引。

查看二进制日志文件的名称、大小和状态

查看二进制日志文件的名称和大小的命令如下所示：

结果如图所示。

也可以输入如下命令进行查看：

该命令等价于show binary logs;命令，结果如图所示

查看当前二进制文件状态的命令如下所示，结果如图所示。

删除某个日志之前的所有二进制日志文件

在前面介绍过，可以通过expire_logs_days参数设定根据时间自动删除二进制日志。下面介绍如何通过purge命令手动删除某日志之前的所有二进制日志文件。首先，查看当前MySQL中的二进制日志文件，命令如下所示：

结果如图所示。

当通过purge命令删除mysql-bin.000002之前的所有二进制日志文件时，该删除操作会影响二进制日志文件的索引部分的内容，命令如下所示：

结果如图所示。

在执行purge命令之后，再次查看MySQL中的二进制日志文件可以发现，名为mysql-bin.000002的二进制日志文件已经被删除了，命令如下所示：

结果如图所示。

删除某个时间点以前的二进制日志文件

删除某个时间点以前的二进制日志文件的命令如下所示：

删除7天前的二进制日志文件的命令如下所示：

删除所有的二进制日志文件

在执行删除所有的二进制日志文件的命令后，所有的二进制日志文件都会被删除，并重新生成新的mysql-bin.000001文件，命令如下所示：

结果如图所示。

查看二进制日志文件内容

在查看二进制日志文件内容之前，首先创建一张表，以便让二进制日志文件中包含一些可以阅读的参数，创建表的命令如下所示：

在MySQL的命令行中读取相关的二进制日志文件，命令如下所示，结果如图所示。

从图中可以看到，创建表的命令也在其中，同时包含各种配置信息。执行INSERT语句：

再次查看二进制日志，命令如下所示：

结果如图所示。

查看二进制日志文件的部分输出，命令如下所示：

结果如图所示。

当发现上述输出内容不是十分容易观察之后，也可以使用如下命令继续观察二进制日志：

结果如图所示。

在生产环境下，通常会对MySQL进行很多增删改等操作，此时可以通过Pos参数，指定查询某个时间点之后的数据，命令如下所示：

结果如图所示

如果该数据仍然十分庞大，则可以使用limit分页参数，命令如下所示：

结果如图所示。

在limit分页参数中包含隐藏参数，即如果输入为limit 1,2，则会让该查询语句先跳过一行，再输出两行结果，命令如下所示：

结果如图所示。

复制二进制日志，并将它转换成文本文件（.txt），命令如下所示：

通过cat /log.txt|grep ''drop ''命令可以正常查询log.test中的二进制日志内容，结果如图所示。