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 status\G
*************************** 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 status\G
*************************** 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 processlist\G
*************************** 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 processlist\G
*************************** 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 status\G
*************************** 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 status\G
*************************** 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 status\G
*************************** 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 status\G
*************************** 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生效!

 

posted @ 2020-05-04 19:30  我听过  阅读(11508)  评论(0编辑  收藏  举报