SQL优化系列 - （二）数据库的主从分离

前言

对于大型网站，为了解决大量的并发访问，会在服务端实现分布式负载均衡，但是，这还不够。如果后台仅使用一个数据库来处理数据，那么，数据库压力势必过大，响应慢，甚至崩溃。如果数据库崩溃、数据丢失，后果将会很严重。

针对于上面存在的隐患，我们可以 主从分离 来避免。

使用主从数据库实现主从分离，主数据库支持数据的写操作，从数据库支持数据的读操作，所以，我们需要确保主库与从库中的数据完全一致。

 

使用主从分离，有如下好处：

    一：提高读写数据库的效率。对于大部分的应用程序，大部分的操作都是查询（即读数据），小部分的操作是增删改（即写数据）。所以，数据库的大部分压力来自于读操作。当读的压力较大时，我们可以通过读写分离的方式，降低读的压力，提升读取时的效率。

    二：确保数据的安全性。如果不实现读写分离，所有的数据全部存储于同一个库中，这样，如果因网络问题而连接不到这个库，或者，某些原因导致这个数据库出现故障，那么，所有数据都将丢失。可能会出现单点故障的风险。
 

主从分离的实现原理

我们可以提供多个数据库，其中，一个库为主库，主要实现写功能（增、删、改），其他库（一个或多个）为从库，主要实现读功能（查询）。

主库与从库中的数据完全一致。所以，我们需要关注的是，如何确保主库与从库之间的数据是一致的。这里，主要是使用二进制日志文件来确保主从之间数据一致的。

这是从网络上搜来的一张图，及介绍： https://blog.csdn.net/qq_15092079/article/details/81672920

 

在上面的模型中，Mysql-A就是主库，即master，Mysql-B就是从库，即slave。

在 Mysql-A主库 的数据库事件（例如修改数据库的sql操作语句），都会存储到日志系统A中，在相应的端口（默认3306）通过网络发送给 Mysql-B从库 。Mysql-B从库 收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库 Mysql-B从库 中完成。

日志系统A，是MYSQL的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，即bin log，注意MYSQL会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全。

日志系统B，不是二进制日志，由于它是从 MYSQL-A主库 的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为中继日志，即relay log。

通过上面的机制，可以保证 Mysql-A主库 和 Mysql-B从库 的数据库数据一致，但是时间上肯定有延迟，即 Mysql-B从库 的数据是滞后的。因此，会出现这样的问题，Mysql-A主库 的数据库操作是可以并发的执行的，但是 Mysql-B从库 只能从relay log中一条一条的读取执行。若 Mysql-A主库 的写操作很频繁，Mysql-B从库 很可能就跟不上了。

 

主从同步复制有以下几种方式：

（1）同步复制，master的变化，必须等待slave-1,slave-2,...,slave-n完成后才能返回。

（2）异步复制，master只需要完成自己的数据库操作即可，至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。

（3）半同步复制，master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MYSQL引入的。

 

主从分离的实现步骤

本文中的主从分离环境是：vmware虚拟机中安装两个centos7操作系统，每个操作系统上，分别安装MySQL5.7数据库。以此环境实现主从分离。CentOS7中离线安装MySQL5.7

前提： 主库所在系统的ip：192.168.11.58 ，  从库所在系统的ip：192.168.11.62 

1、配置主库

1.1 修改/etc/my.cnf 配置文件

  添加如下信息：

必须配置的选项：

1）server-id   服务器唯一标识。

2）log-bin     启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。

3）log-bin-index   二进制日志文件的索引

如果仅配置上面选项，指的是主服务器上的所有库都会同步到从库中，比如，主库中创建一个新的库，那么，从库中也将创建一个新的库。

可选配置的选项：

1）binlog-do-db = test   指定 将主库中的 test数据库 同步到 从库 中。

2）binlog-ignore-db = test  指定 不将主库 中的 test数据库 同步到 从库 中。

     需要注意的是，binlog-do-db和binlog-ignore-db为互斥选项，一般只需要一个即可。

1.2 创建从库连接主库时的用户与分配权限

1.3 使用 exit 退出数据库登录，并重启数据库

1.4  登录到mysql库中，查看主库信息

2、配置从库

2.1 修改 /etc/my.cnf配置文件

  添加如下信息：

必须配置的选项：

1）server-id     从服务器唯一标识，如果有多个从服务器，那么，每个从服务器的server-id不可重复。

2）relay-bin     启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。

3）relay-bin-index   二进制日志文件的索引

可选配置的选项：

1）replicate-do-db  指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
2）replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
需要注意的是，replicate-do-db 和 replicate-ignore-db为互斥选项，一般只需要一个即可。

2.2 重启mysql服务

 

2.3 连接主库

选项说明：

1）master_host=主库所在的ip地址

2）master_port=主库的端口号

3）master_user=主库中为从库创建的连接用户名

4）master_password=连接用户名的密码

5）master_log_file=主库中二进制日志文件名，即主库中使用 show master status 命令显示的File列文件名，如：master-bin.000004。

6）master_log_pos=对应show master status显示的Position列的值，比如：1122。

2.4 启动slave，并查看信息

说明：必须确保红色框内的结果均为 Yes，才说明主从连接配置正确

3、测试

在主库中创建新的数据库：

在从库中查询：

 

 

至此，数据库中的 主从分离 已完成