mysql高可用方案

第一节：mysql 高可用方案

1.1 一主一从架构

　　mysql 配置为主从模式，从库是主库的 backup，同一时间设置其中一台为主服务器，提供读写，另一台服务器作为热备，不提供读写，通过复制与主服务器数据保持一致，二者均开启 binlog。

（1）主从复制实现

　　在主库把数据更改记录到 binlog 中；

　　备库将主库的日志复制到自己的 relaylog 中；

　　备库读取 relaylog 中的事件，将其重放到备库上。

（2）主从复制关键技术

　　　　异步复制、半同步复制

 1.2 双主（主从）架构

　　在一主一从架构的基础上配置双向复制和 Keepalived 自动切换功能，通过周期性调用监测脚本，监测进程，实现故障时 VIP 的无缝切换，当活跃点出现故障时，通过 VIP+Keepalived 脚本执行实现向另一台数据库的切换，以此实现 mysql 架构的高可用。

（1）Keepalived 切换实现

　　　　双向复制运行；

　　　　VIP 所在的库作为主库；

　　　　主库出现问题时，VIP 切换至另一个主库。

（2）Keepalived 切换关键技术

　　　　VRRP 原理、Keepalived 监测机制

1.3 MHA + 一主两从架构

　　MHA（Master High Availability）在 mysql 高可用方面是一个相对成熟的解决方案。在 mysql 故障切换的过程中，MHA 能做到在 0-30 秒之内自动完成数据库的故障切换工作，并且在进行故障切换的过程中，MHA 能在最大程度上保证数据的一致性，已达到真正意义上的高可用，但 GTID 出现后 MHA 功能弱化。

（1）MHA切换实现

　　　　主库；备主；备库；

　　　　MHA 配合 VIP 漂移；

　　　　补齐数据。

（2）MHA切换关键技术

　　　　MHA manager、GTID（全局事务标志）出现，MHA弱化

　　　　　　一个事务对应一个唯一ID，一个GTID在一个服务器上只会执行一次（一个事务在从库上只能出现一次）

1.4 mysql 集群架构

　　集群架构原理（PXC）：节点接收 sql 请求后，对于 ddl 操作，在 commit 之前，由 wsrep API 调用 galera 库进行集群内广播，所有其他节点验证成功后事务在集群所有节点进行提交，反之 roll back。PXC 保证整个集群所有数据的强一致性，满足：Consistency和Availability。

（1）mysql 集群实现

　　　　上层中间调度如 HAproxy；

　　　　调度到的 Node上进行读写；

　　　　同步至其他两个节点。

（2）mysql 集群关键技术

　　　　同步复制、wsrep 插件技术

2、mysql 主从复制

2.1 mysql 主从复制概念

　　mysql 主从复制是指数据可以从一个 mysql 数据库服务器主节点复制到一个或多个从节点。mysql 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

2.2 mysql 主从复制的主要用途

（1）读写分离

（2）数据实时备份，当系统某节点发生故障时，可以进行故障切换

（3）高可用性（HA）

　　随着系统中业务访问量的增大，如果是单机部署数据库，就会导致 I/O 访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘 I/O 访问的频率，提高单个机器的 I/O 性能。

2.3 mysql 主从复制原理

　　过程：主库开启 binlog 功能并授权从库连接主库，主库 IO 线程根据从库的请求，从 master.info 开始记录的位置点向下开始取信息，同时把取到的位置点和最新的位置与 binlog 信息一同发给从库 IO 线程，从库将相关的 sql 语句存放在 relay-log 里面，最终从库的 sql 线程将 relay-log 里的 sql 语句应用到从库上，至此整个同步过程完成，之后将是无限重复上述过程。

　　binlog 输出线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送 binlog 内容到从库。对于每一个即将发送给从库的 sql 事件，binlog 输出线程会将其锁住。一旦该事件被线程读取完之后，该锁会被释放，即使在该事件完全发送到从库的时候，该锁也会被释放。在从库里，当复制开始的时候，从库就会创建两个线程进行处理：

　　从库 I/O 线程：当 START SLAVE 语句在从库开始执行之后，从库创建一个 I/O 线程，该线程连接到主库并请求主库发送 binlog 里面的更新记录到从库上。从库 I/O 线程读取主库的 binlog 输出线程发送的更新并拷贝这些更新到本地文件，其中包括 relay log 文件。

　　从库的 SQL 线程：从库创建一个 SQL 线程，这个线程读取从库 I/O 线程写到 relay log 的更新事件并执行。

　　可以知道，对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个 binlog 输出线程，每一个从库都有它自己的 I/O 线程和 SQL 线程。

　　从库通过创建两个独立的线程，使得在进行复制时，从库的读和写进行了分离。因此，即使负责执行的线程运行较慢，负责读取更新语句的线程并不会因此变得缓慢。比如说，如果从库有一段时间没运行了，当它在此启动的时候，尽管它的 SQL 线程执行比较慢，它的 I/O 线程可以快速地从主库里读取所有的 binlog 内容。这样一来，即使从库在 SQL 线程执行完所有读取到的语句前停止运行了，I/O 线程也至少完全读取了所有的内容，并将其安全地备份在从库本地的 relay log，随时准备在从库下一次启动的时候执行语句。

2.4 主从复制的实现

（1）环境构建：基于 centos7 操作系统，mariadb 数据库（同 mysql 数据库）

master节点：192.168.129.128
slave1节点：192.168.129.129

（2）分别在 master 节点和 slave1 节点上关闭防火墙，关闭 selinux

#master节点：
[root@Master ~]#systemctl stop firewalld
[root@Master ~]#systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@Master ~]# sed -ri '/^SELINUX=/s/(SELINUX=).*/1disabled/' /etc/selinux/config#slave1节点：
[root@Slave1 ~]# systemctl stop firewalld
[root@Slave1 ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@Slave1 ~]# sed -ri '/^SELINUX=/s/(SELINUX=).*/1disabled/' /etc/selinux/config

（3）master 节点和 slave1 节点上分别安装 mariadb：

#两个节点分别执行：
yum install mariadb-server -y

（4）在主服务器（master）上：

　　启用二进制日志

　　选择一个唯一的server-id

　　创建具有复制权限的用户

[root@Master ~]#vim /etc/my.cnf    
[mysqld]
log-bin=master-bin
# 行模式
binlog_format = row
# 刷新binlog到磁盘
sync_binlog = 1
# 禁止域名解析
skip_name_resolv = 1
# 同步设置的重要参数
log_slave_updates = 1
# 设置唯一id
server_id = 128
datadir=/var/lib/mysql
[root@Master ~]#systemctl restart mariadb
[root@Master ~]#mysql
MariaDB [(none)]> reset master;    #删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作；

Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> grant replication slave,replication client on *.*
    -> to rep@'192.168.129.%' identified by 'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

（5）在从服务器（slave1）上

　　启用中继日志（二进制日志可开启，也可不开启）

　　选择一个唯一的 server-id

　　连接至主服务器，并开始复制

　　首先测试是否能远程登录 master 节点上的数据库：

[root@Slave1 ~]# mysql -h 192.168.129.128 -urep -plocalhost
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 3
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MariaDB [(none)]> quit    #测试成功，退出

　　配置从服务器：

[root@Slave1 ~]# vim /etc/my.cnf
[mysqld]
log-bin=slave1-bin
binlog_format = row
sync_binlog = 1
skip_name_resolv = 1
log_slave_updates = 1
server_id = 129
datadir=/var/lib/mysql
[root@Slave1 ~]# systemctl start mariadb
[root@Slave1 ~]# mysql
MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> change master to
    -> master_host='192.168.129.128',
    -> master_user='rep',
    -> master_password='localhost',
    -> master_log_file='master-bin.000001',
    -> master_log_pos=0;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.129.128
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.129.128
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 493
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 778
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

（6）测试：master 创建数据库数据，slave 节点查看是同步了 master 节点的数据库信息

　　master节点：

MariaDB [(none)]> show processlistG
*************************** 2. row ***************************
      Id: 5
    User: rep
    Host: 192.168.129.129:47964
      db: NULL
 Command: Binlog Dump
    Time: 40
   State: Master has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
Progress: 0.000
2 rows in set (0.00 sec)

MariaDB [(none)]> create database bbs;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create table bbs.t1(id int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> insert into bbs.t1 values(1);
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> select * from bbs.t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

　　slave1节点：

MariaDB [(none)]> show processlistG
*************************** 1. row ***************************
      Id: 2
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: NULL
    Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
      Id: 3
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 203
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 4
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 42
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)

MariaDB [(none)]> select * from bbs.t1;    #查看数据主从配置成功
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

2.5 mysql 主从状况监测主要参数

　　Slave_IO_Running：线程是否打开 YES/No/NULL

　　Slave_SQL_Running：线程是否打开 YES/No/NULL

　　Seconds_Behind_Master：和主库比同步的延迟的秒数

2.6 可能导致主从延时的因素

　　主从时钟是否一致

　　网络通信是否存在延迟

　　是否和日志类型，数据过大有关

　　从库性能，有没开启 binlog

　　从库查询是否优化

3、mysql 双主架构

3.1 实现

（1）两台 mysql 都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用；

（2）masterA 是 masterB 的主库，masterB 又是 masterA 的主库，它们互为主从；

（3）两台主库之间做高可用，可以采用 keepalived 等方案（使用 VIP 对外提供服务）；

（4）所有提供服务的从服务器与 masterB 进行主从同步（双主多从）;

（5）建议采用高可用策略的时候，masterA 或 masterB 均不因宕机恢复后而抢占 VIP（非抢占模式）；

　　这样做可以在一定程度上保证主库的高可用，在一台主库 down 掉之后，可以在极短的时间内切换到另一台主库上（尽可能减少主库宕机对业务造成的影响），减少了主从同步给线上主库带来的压力；

　　masterB 可能会一直处于空闲状态（可以用它当从库，负责部分查询）；

　　主库后面提供服务的从库要等 masterB 先同步完了数据后才能去 masterB 上去同步数据，这样可能会造成一定程度的同步延时；

 3.2 mysql 主主同步配置

 　　主主环境构建：同样基于centos7-mariadb

　　　　mysql1：192.168.129.128

　　　　mysql2：192.168.129.129

（1）关闭防火墙、selinux（同上：主从）

（2）mysql1 节点：

[root@mysql1 ~]#vim /etc/my.cnf
log-bin=mysql-bin
server-id = 4
[root@mysql1 ~]#systemctl start mariadb
[root@mysql1 ~]#mysql
MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant replication slave,replication client on *.*
    -> to rep@'192.168.129.%' identified by 'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> change master to
    -> master_host='192.168.129.129',
    -> master_user='rep',
    -> master_password='localhost',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=0;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.129.129
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
  
MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.129.129
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes

（3）mysql2 节点：

[root@mysql2 ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id = 251
[root@mysql2 ~]# systemctl start mariadb
[root@mysql2 ~]# mysql

MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant replication slave,replication client on *.*
    -> to rep@'192.168.129.%' identified by 'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> change master to
    -> master_host='192.168.129.128',
    -> master_user='rep',
    -> master_password='localhost',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=0;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first    #此处报错需要停止SLAVE
MariaDB [(none)]> stop slave    #停止SLAVE
    -> ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> change master to master_host='192.168.129.128', master_user='rep', master_password='localhost', master_log_file='mysql-bin.000001', master_log_pos=0;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.129.128
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.129.128
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 493
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 777
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

（4）测试：

　　在 mysql1 节点创建数据库，在 mysql2 节点查看：

　　mysql1 节点：

MariaDB [(none)]> create database data1;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> create table data1.t1;
ERROR 1113 (42000): A table must have at least 1 column
MariaDB [(none)]> create table data1.t1(name int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> insert into data1.t1 values(1);
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> select * from data1.t1;
+------+
| name |
+------+
|    1 |
+------+

　　mysql2 节点查看：

MariaDB [(none)]> select * from data1.t1;
+------+
| name |
+------+
|    1 |
+------+

　　同理 mysql2 节点创建数据库，mysql1 节点查看；至此实现 mysql 主主复制。

4、生产环境其他常用设置

4.1 配置忽略权限库同步参数

binlog-ignore-db='information_schema mysql test'

4.2 从库备份开启 binlog

log-slave-updates
log_bin = mysql-bin
expire_logs_days = 7

应用场景：级联复制或从库做数据备份。

4.3 从库只读 read-only 来实现

innodb_read_only = ON

结论：当用户权限中没有SUPER权限(ALL权限是包括SUPER的)时，从库的read-only生效！