关于MySQL稳定性提高方案：主从复制、读写分离、集群李青玄博客

在大流量，高并发等场景下，数据库往往会成为服务端的性能瓶颈，那么有什么办法，可以提高数据库在此场景下的的稳定性，以及如何缓解数据库的压力呢？

针对于MySQL服务器来说，我们今天讲一下这三个基础点：

1、主从复制

2、读写分离

3、集群

MySQL主从复制

主从复制功能通过主服务器和从服务器之间切分处理客户的查询压力。

数据变更时操作主服务器,主服务器数据变更后，立即传输给从服务器,使得从服务器进行更新数据，程序读取时，读取从服务器，同时由于读数据库完全不涉及数据变更,可以开启多台读数据库,进行分散数据库压力。

主从复制的优点如下：

增加健壮性，主服务器出现宕机等问题时，可以切换到从服务器作为备份。
优化响应时间，分散数据库读写压力

主从复制工作原理：

主从复制通过三个过程实现，其中一个发生在主服务器上，另外两个过程发生在从服务器上。

主服务器将用户对数据库更新的操作以二进制格式保存到 Binary log 日志文件中，然后由 Binlog Dump 线程将 Binary Log 日志文件传输给从服务器。
从服务器通过一个I/O线程将主服务的 Binary Log 日志文件中的更新操作复制到一个叫 Relay Log 的中继日志文件中。
从服务器通过另一个SQL线程将Relay log 中继日志文件中的操作依次在本地执行，从而实现主从之间的数据同步。

MySQL读写分离

简单来讲，就是把对数据库的读/写操作分开为多台服务器负责,实现读写分离。

为什么要分离读和写呢？写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，从另一个方面有效的提高了读的效率，保证了系统的高可用性。

MySQL集群

mysql集群除了为了解决数据库压力分散问题,同时为了实现数据库的高可用性,在一台数据库宕机的情况下,尽可能的降低业务的影响.

mysql集群有着以下几种方式:

1:mysql一主一从,mysql读写分离,使数据库压力分散,提高服务器性能

2:mysql一主多从,当主服务器出问题后,可以选择一台从服务器变更为主服务器,继续提供服务

3:mysql多主多从,一台主服务器出问题了,可立即切换另一台主服务器提供服务.

同时,mysql集群将带来相关的一些问题,例如:

1、主从同步数据延迟问题

首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高。

次要原因：读写binlog带来的性能影响，网络传输延迟，带宽太低。

从库同步延迟情况出现的判断

首先在服务器上执行show slave satus;可以看到很多同步的参数：

Master_Log_File：                      SLAVE中的I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos：        在当前的主服务器二进制日志中，SLAVE中的I/O线程已经读取的位置
Relay_Log_File：                        SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos：                        在当前的中继日志中，SQL线程已读取和执行的位置
Relay_Master_Log_File：      由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
Slave_IO_Running：                 I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running：              SQL线程是否被启动
Seconds_Behind_Master：     从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。

● show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大
● show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大
● mysql的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害。

2、一主多从虽然可以提高可用性,但在主服务器宕机的时候,可能会出现一些数据同步未完成,数据丢失的问题,需要在主服务器恢复后增量恢复。

3、多主多从需要考虑主服务器都在使用时,id自增,主键冲突的问题,以及其中一台主服务器宕机时间至恢复时间内的数据丢失,增量同步的问题。

搭建一个集群环境

了解了以上的基本实现原理，我们现在就可以动手搭建一个一主一从的集群环境了。

首先准备两台虚拟机,分配了固定IP,安装了MySql5.7

Master服务器：192.168.65.100

Slave服务器:192.168.65.101

1、修改配置项

打开二进制日志，指定唯一server-id。

在my.cnf中添加：

[mysqld] #主服务器配置项 log-bin = mysql-bin server-id = 1 #从服务器配置项 log-bin = mysql-bin server-id = 2 #配置完成后重启mysql服务器

2、在主服务上创建一个用户，授予REPLICATION SLAVE权限，用于从服务器连接。

mysql > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%.%' IDENTIFIED BY 'password';
#只允许通过192.168.*.* ip连接 ## 刷新系统用户权限
mysql>  flush privileges; ## 查询当前数据库账号
mysql> select user,host from mysql.user;
+------+-----------------------+
| user | host                  |
+------+-----------------------+
| root | 127.0.0.1             |
| rep  | 192.168.%.%           |
| root | ::1                   |
|      | localhost             |
| root | localhost             |
|      | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
7 rows in set (0.00 sec)
## 记录当前binlog位置,
 
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      90 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# File 表示主服务器正在使用的binlog文件；Position的值与binlog文件的大小相同，表示下一个被记录事件的位置；
# Binlog_Do和Binlog_ignore参数是主服务器控制写入binlog文件内容的过滤选项，默认为空；

3、在从服务器中，指定主服务信息

mysql> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.65.100',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#此处指定MASER_LOG_POS=0，因为要从日志的开始位置开始读。
#查看设置是否正确
mysql>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.65.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No #表示还没开始运行
            Slave_SQL_Running: No #同上
                   ......
        Seconds_Behind_Master: NULL

4、启动从服务器主从复制

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
#再查看运行状态，注意,只有当 slave_io_running和slave_sql_running 都为yes时,才算是启动成功.
mysql>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.65.100
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 164
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 164
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes #运行成功
            Slave_SQL_Running: Yes #运行成功
                   ......
        Seconds_Behind_Master: 0

测试

主服务器当前数据库:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

从服务器当前数据库:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
 
主服务器新增数据库:

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
 
从服务器查看:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

可以看到主从已经成功同步，搭建成功！！！