运维实战 Mysql高可用与健康检查

最新推荐文章于 2023-02-18 19:58:29 发布

洛冰音

最新推荐文章于 2023-02-18 19:58:29 发布

阅读量363

点赞数

分类专栏： LNMP架构运维实战企业架构

本文链接：https://blog.youkuaiyun.com/lunarlibrary/article/details/116403536

版权

运维实战同时被 3 个专栏收录

28 篇文章

订阅专栏

企业架构

11 篇文章

订阅专栏

LNMP架构

6 篇文章

订阅专栏

本文详细介绍了MySQL的主从复制、GTID复制、半同步复制、SQL线程优化、组复制、MySQL路由器和MHA高可用的配置与实践。通过这些方法，可以提高MySQL的高可用性和数据一致性，并实现数据库的读写分离，确保运维过程中的数据安全。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主从复制

主从复制自然是分为Maser端和Slave端的.

Master端设置操作

之前已经安装过Mysql了因此安装步骤在下文会被略过

##创建专门用于主从复制的用户
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'westos'; 
##为其设置密码并赋予权限
mysql>CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

查看主库目前的状态

mysql> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      437 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置Server ID

vim /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

server-id=1  
log-bin=mysql-bin

##设置完成后重启MySQL
/etc/init.d/mysqld restart

Slave端设置操作

之前已经安装过Mysql了因此安装步骤在下文会被略过

配置环境变量

[root@Server2 mysql]# vim ~/.bash_profile 

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

[root@Server2 mysql]# source ~/.bash_profile

新建数据库所需用户

[root@Server2 mysql]# groupadd -g 1001 mysql
[root@Server2 mysql]# useradd -u 1001 -g mysql -M -d /usr/local/mysql/ -s /sbin/nologin mysql

删除数据库内data数据,保证环境清洁

同步要保证数据完全一致,如果存在区别则会报错

[root@Server2 ~]# cd /usr/local/mysql/data/
[root@Server2 data]# ls
auto.cnf         ib_buffer_pool  mysql               Server1.err
ca-key.pem       ibdata1         mysql.sock.lock     Server1.pid
ca.pem           ib_logfile0     performance_schema  server-cert.pem
client-cert.pem  ib_logfile1     private_key.pem     server-key.pem
client-key.pem   ibtmp1          public_key.pem      sys
[root@Server2 data]# rm -rf *

配置Server ID

[root@Server2 mysql]# vim /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2

数据库安全初始化

[root@Server2 data]# mysqld --initialize --user=mysql
[root@Server2 data]# /etc/init.d/mysqld start
[root@Server2 data]# mysql_secure_installation

查看是否能够正确进入

[root@Server2 data]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.31 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

同步相关配置

##配置主机信息,注意信息与上文必须做到一字不差
##否则就会出现差异导致失败
mysql> CHANGE MASTER TO MASTER_HOST='172.25.5.1',MASTER_USER='repl',MASTER_PASSWORD='westos',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=437;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
##启动slave端
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
##查看slave状态
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.5.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 437
               Relay_Log_File: Server2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 437
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 7806f80a-9809-11eb-ac50-52540079f81b
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

注意事项

在查看Slave状态(SHOW SLAVE STATUS\G)时,以下两项必须均为YES

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

验证同步情况

在Master端进行数据库操作

mysql> CREATE DATABASE Test;
Query OK, 1 row affected (0.00 sec)

切换到Slave端进行查看,能够看到从机出现了刚刚创建的Test数据库

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Test               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

不难发现,这种方式费时费力,需要注意Master的状态,如果mysql-bin.000001变动或者重启, 都可能导致同步设置出错, 更不要提主从切换等操作了.

这大大增加了运维的难度, 因此也出现了新的方法

GTID复制

GTID工作原理

MASTER更新数据时, 会在事务前产生GTID, 一同记录到binlog日志中.
SLAVE端的IO线程将变更的binlog, 写入到本地的relay log中.
sql线程从relay log中获取GTID, 然后对比SLAVE端的binlog是否有记录.
如果有记录, 说明该GTID的事务已经执行, SLAVE会忽略.
如果没有记录, SLAVE就会从relay log中执行该GTID的事务, 并记录到binlog.
在解析过程中会判断是否有主键, 如果没有就用二级索引, 如果没有就用全部扫描.

GTID优势

一个事务对应一个唯一ID, 一个GTID在一个服务器上只会执行一次
GTID是用来代替传统复制的方法, GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置
减少手工干预和降低服务故障时间, 当主机挂了之后通过软件从众多的备机中提升一台备机为主机

默认情况下,整个过程是异步的. 而异步就可能会出现问题, 需要有确认机制.

同时也存在着半同步状态, 默认情况下MASTER的半同步时间为10s, 超过10s就会切为异步, 出现主从不一致.

因此生产环境中这一事件应该尽可能调大.

配置流程

主机

##在数据库主配置文件中增加GTID支持
[root@Server1 ~]# vim /etc/my.cnf

gtid_mode=ON
enforce-gtid-consistency=ON

##重启服务
[root@Server1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL............ SUCCESS! 
Starting MySQL.. SUCCESS! 

##查看数据库状态
[root@Server1 ~]# mysql -p
mysql> show master status;

从机

##在数据库主配置文件中增加GTID支持
[root@Server2 data]# vim /etc/my.cnf\

gtid_mode=ON
enforce-gtid-consistency=ON

##重启服务
[root@Server2 data]# /etc/init.d/mysqld restart

##查看数据库状态
[root@Server2 data]# mysql -pwestos
mysql> show slave status\G;

##停止当前的slave,修改其配置后重启
mysql> stop slave;
mysql> change master to master_host='172.25.5.1', master_user='repl', master_password='westos', MASTER_AUTO_POSITION = 1;
mysql> start slave;

##假设Server2将Server1当作主机,Server3将Server2当作主机
##则Server3的设置应该如下所示
[root@Server3 ~]# vim /etc/my.cnf

gtid_mode=ON
enforce-gtid-consistency=ON

[root@Server3 ~]# /etc/init.d/mysqld restart

[root@Server3 ~]# mysql -pwestos
MySQL [(none)]> stop slave;
MySQL [(none)]> change master to master_host='192.168.0.12', master_user='repl', master_password='westos',
MASTER_AUTO_POSITION = 1;
MySQL [(none)]> start slave;

半同步复制

传统的复制模式属于异步复制, 主库只管发送二进制日志, 而不关心从库是否收到.

这种复制方式虽然复制速度很快, 但从实现上就无法保证主从数据的一致性.

在生产环境中数据量增大的情况下可能造成极大的危险性.

半同步复制的流程

主库发送二进制二进制日志到从库
从库接收到数据并存入relay-log
从库发送ack确认给主库
主库接收到确认, 整个复制流程结束

ACK确认提交后才作引擎提交, 做了引擎提交之后其他用户才能查看到数据.

不难看出, 这一操作保证了主从数据的一致性.

主机

##启动Mysql并登陆
/etc/init.d/mysqld start
mysql -pwestos

##启用半同步
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

##查看相关信息
SHOW variables like 'rpl%';

##启用半同步服务并设置超时时间
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;

##启用后也可以查看半同步服务的状态
SHOW status like 'rpl%';

从机

##启动Mysql并登陆
/etc/init.d/mysqld start
mysql -pwestos

##查看IO和SQL线程的状态是否为YES
SHOW SLAVE STATUS\G;

##启用半同步插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

##启用半同步服务
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

##查看启用情况
SHOW variables like 'rpl%';

##但是如果不重启IO线程,实际上服务并没有运行
mysql> SHOW status like 'rpl%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+

##重启IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

##观察到服务正确运行
mysql> SHOW status like 'rpl%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

此时半同步服务连接完成

此时在主机对数据库进行操作,如果半同步服务和从机状态无误,会通过半同步服务进行同步,如果半同步出现问题会等待半同步超时时间的时长.

如果同步成功则会在SHOW status like 'rpl%';中显示出来

举例

mysql> SHOW status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 565   |
| Rpl_semi_sync_master_tx_wait_time          | 1131  |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 2     |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)

##解释
Rpl_semi_sync_master_clients			半同步复制客户端的个数
Rpl_semi_sync_master_yes_tx				通过半同步方式同步的操作个数
Rpl_semi_sync_master_no_tx				当半同步出现问题时,通过异步方式同步的操作个数

mysql> SHOW variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
当存在多台从机时,如果其中一台坏掉并不会影响其他从机的半同步复制,无法正确进行半同步的那台机器无法正确进行同步.
而我们不一定需要这种设置,通过设置slave应答数量,可以从逻辑上设置多少台主机出现问题时启用异步.
当应答数量等于从机数量时,只要任意一台从机的情况出现问题就会切换到异步模式同步.

SET GLOBAL rpl_semi_sync_master_wait_for_slave_count=2;

SQL线程优化

写入数据库中的数据持久化到磁盘后由SQL线程复现主机进行过的操作.

随着数据量的增加,这一步的延迟会越来越大.这也是数据库在业务使用中的一个热点.

通过并行复制的操作可以解决这个问题.

并行复制

##在从机上操作

##修改主配置文件并重启服务
[root@Server2 data]# vim /etc/my.cnf 

[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2
log-slave-updates
log-bin=mysql-bin
validate_password=OFF
gtid_mode=ON
enforce-gtid-consistency=ON

rpl_semi_sync_master_enabled=1 				##优化IO线程
rpl_semi_sync_slave_enabled=1

slave-parallel-type=LOGICAL_CLOCK			##优化SQL线程 
slave-parallel-workers=16					##设置并行复制

master_info_repository=TABLE      			##采用数据表的方式存储记录,提高效率
relay_log_info_repository=TABLE
relay_log_recovery=ON

[root@Server2 data]# /etc/init.d/mysqld restart
[root@Server2 data]# mysql -pwestos
mysql> show slave status\G ;
mysql> show variables like 'slave%';
mysql> show variables like '%info%';
+--------------------------------+----------------+
| Variable_name                  | Value          |
+--------------------------------+----------------+
| master_info_repository         | TABLE          |
| relay_log_info_file            | relay-log.info |
| relay_log_info_repository      | TABLE          |
| session_track_transaction_info | OFF            |
| sync_master_info               | 10000          |
| sync_relay_log_info            | 10000          |
+--------------------------------+----------------+
6 rows in set (0.00 sec)
mysql> use mysql;
mysql> select * from slave_master_info;

内容讲解

slave-parallel-workers=16开启了16个工作线程,原本的SQL线程则退化成一个协调线程.

因此这里并不能设置为1, 否则工作性能还不如原本的设置方式

开启并行复制之后relay-log.info文件的刷写会快得多,如果继续采用文件读写的方式会影响性能.

master_info_repository=TABLE, 采用数据库表的方式来存放,更有利于性能发挥.

并行为数据库为基础,一个数据库一个线程

slave-parallel-type可以有两个值: DATABASE 默认值(基于库的并行复制方式); LOGICAL_CLOCK(基于组提交的并行复制方式)

延迟复制

主从复制延迟是必然存在的, 即使进行优化

延迟复制可以作为业务上的保险, 也可以作为性能优化使用.

通过设置SLAVE落后MASTER的时间来控制主从复制的时间间隔.

##从机上操作
mysql> STOP SLAVE SQL_THREAD;
##设置延迟为30s
mysql> CHANGE MASTER TO MASTER_DELAY = 30;
mysql> START SLAVE SQL_THREAD;
mysql> show slave status\G ;

##在主机上插入数据
mysql> insert into user_tb values ('user1','11');

##查看从机上的slave状态
mysql> show slave status\G ;

可以看到其中有着延迟计时器

慢查询

不规范的数据库操作语句或者设计失误会导致在数据量庞大时每一条命令耗时倍增.

耗时较长的指令被我们称为慢查询.

如果出现慢查询的次数过多就可能拖累数据库的性能甚至导致业务出现问题.

记录慢查询并告诉开发人员使其进行修改就是我们需要做的事了.

[root@Server1 ~]# /etc/init.d/mysqld start
[root@Server2 ~]# /etc/init.d/mysqld start
[root@Server3 ~]# /etc/init.d/mysqld start


[root@Server1 ~]# mysql -pwestos

##查询慢查询相关参数
mysql> show variables like 'slow%';

##开启慢查询日志
mysql> set global slow_query_log=ON;

##查看慢查询的时间,默认情况下为10s
mysql> show variables like "long%";
mysql> select sleep(10);

##慢查询的日志文件,有记录的日志信息
##通过查询相关日志并告知开发人员进行修改
[root@Server1 data]# cat /usr/local/mysql/data/server11-slow.log

组复制

多主模式,增加多个写入节点,同时提供若错机制.

前期操作

停掉之前的所有数据库并删除原始数据
不进行安全初始化, 因为安全初始化会写入其他信息
临时禁用二进制日志

注意事项

Server1作为初始化节点需要做的事

设置时需要临时禁用二进制日志
必须采用INNODB引擎
需要关闭单主模式
增加172.25.5.0网段到白名单
刷新用户授权表使设置生效
以上操作只有初始化节点(这里为Server1)才需要做

Server1上的操作

##清空数据目录保证各节点数据统一
cd /usr/local/mysql/data
rm -rf *

##进行初始化并启动MqSQL
mysqld --initialize --user=mysql
/etc/init.d/mysqld start

##使用初始化时的密码登陆MySQL
mysql -p

##进入设置操作
##修改用户密码
alter user root@localhost identified by 'westos';
##临时关闭二进制日志
SET SQL_LOG_BIN=0;
##创建用于组复制的用户并设置密码
CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
##赋予其复制权限使其用于组复制
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
##开启临时关闭的二进制日志
SET SQL_LOG_BIN=1;
##设置同步时所用的组,用户和密码
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
##使用该节点来引导集群(只在第一个节点使用)
SET GLOBAL group_replication_bootstrap_group=ON;
##启用组复制
START GROUP_REPLICATION;
##组复制已经启用,关闭引导
SET GLOBAL group_replication_bootstrap_group=OFF;

##查看组中的节点及其情况
SELECT * FROM performance_schema.replication_group_members;

Server2/3上的操作

##清空数据目录保证各节点数据统一
cd /usr/local/mysql/data
rm -rf *

##进行初始化并启动MqSQL
mysqld --initialize --user=mysql
/etc/init.d/mysqld start

##使用初始化时的密码登陆MySQL
mysql -p

##进入设置操作
##修改用户密码
alter user root@localhost identified by 'westos';
##临时关闭二进制日志
SET SQL_LOG_BIN=0;
##创建用于组复制的用户并设置密码
CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
##赋予其复制权限使其用于组复制
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
##开启临时关闭的二进制日志
SET SQL_LOG_BIN=1;
##设置同步时所用的组,用户和密码
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
##启用组复制
START GROUP_REPLICATION;

##查看组中的节点及其情况
SELECT * FROM performance_schema.replication_group_members;

如果配置成功,在三台机器上都应该能看到如下结果.

只有当状态均为ONLINE时才为设置正确, 如果状态为RECOVERING则仍存在设置问题.

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 951d5d58-9c1c-11eb-93fb-5254005515b8 | Server2     |        3306 | ONLINE       |
| group_replication_applier | c30f736a-9c1d-11eb-b192-52540029b40d | Server3     |        3306 | ONLINE       |
| group_replication_applier | e6c9eca6-9c1c-11eb-9e4d-52540079f81b | Server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

My.cnf的配置示例

[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "172.25.5.1:33061"
group_replication_group_seeds= "172.25.5.1:33061,172.25.5.2:33061,172.25.5.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.5.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON

MySQL路由器

上文中做了多主模式的高可用
因此在业务上应用层可以通过路由方式访问中间件, 进而访问路由器
当然, 如果并没有做多主模式自然也就用不上这个过程了

数据库的读写分离

下载中间件mysql-router-community
修改主配置文件实现读写分离和负载均衡
启动中间件,使用宿主机链接中间件所在主机验证设置情况

##安装中间件mysql-router-community
rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm

##编辑配置文件,在末尾添加如下内容
vim /etc/mysqlrouter/mysqlrouter.conf

[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.5.1:3306,172.25.5.2:3306,172.25.5.3:3306
routing_strategy = round-robin

[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.5.1:3306,172.25.5.2:3306,172.25.5.3:3306
routing_strategy = first-available

添加的配置实现了以下功能.

读写分离
只读操作使用7001端口,集群包含三台主机,使用round-robin算法.
读写操作使用7002端口,集群包含三台主机,使用first-available算法
这里都使用三台主机是因为我只有三台主机,实际上按照业务情况写和读的机器并不应该完全相同
由于上文做了组复制的设置,集群内节点是多主模式;若是主从模式则读写配置的节点只能包括主机而不能包括从机

对Server1作预处理

##创建用于读操作的user1并赋予其权限,设置密码
GRANT SELECT ON *.* to user1@'%' identified by 'westos';
##创建Test数据库并创建t1表,在表中插入内容
CREATE DATABASE Test;
USE Test;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
INSERT INTO t1 VALUES (1, 'Luis');

##查看插入情况
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)

##创建用于写操作的user2并赋予其读写权限
GRANT INSERT,UPDATE ON Test.* to user2@'%' identified by 'westos';
##重新加载权限表
flush privileges;

在对Server1作完预处理后返回宿主机进行测试

##连接中间件所在IP的7001端口,使用user1
mysql -h 172.25.5.4 -P 7001 -u user1 -pwestos
##可以看到其能够查看
MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Test               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.001 sec)
##但是无法进行写操作
MySQL [(none)]> INSERT INTO Test.t1 VALUES (2, 'NeuWings');
ERROR 1142 (42000): INSERT command denied to user 'user1'@'Server4' for table 't1'

##连接中间件所在IP的7002端口,使用user2
mysql -h 172.25.5.4 -P 7001 -u user2 -pwestos
##对Test.t1写入数据成功
MySQL [(none)]> INSERT INTO Test.t1 VALUES (2, 'NeuWings');
Query OK, 1 row affected (0.007 sec)

在Server1/2/3上做lsof查询,查看连接情况

由于读使用的是论调算法,写使用的是first-available算法,此时应该连接的是Server1

[root@Server1 data]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4019 mysql   22u  IPv6  27137      0t0  TCP *:mysql (LISTEN)
mysqld  4019 mysql   82u  IPv6  31830      0t0  TCP Server1:mysql->Server4:43098 (ESTABLISHED)

MHA高可用

MHA集群要求每个组必须具有1主2从, 如果不满足此条件则整个系统无法启动.

配置一主两从

##首先停止服务, 删除数据, 恢复初始状态
[root@Server1 ~]# /etc/init.d/mysqld stop
[root@Server2 ~]# /etc/init.d/mysqld stop
[root@Server3 ~]# /etc/init.d/mysqld stop 

##对所有机器做相同操作
[root@Server1 data]# cd /usr/local/mysql/data
[root@Server1 data]# rm -fr
[root@Server1 data]# vim /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

server_id=1								##机器不同号码不同
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

[root@Server1 data]# mysqld --initialize --user=mysql
[root@Server1 data]# /etc/init.d/mysqld start
[root@Server1 data]# mysql_secure_installation 

##对MASTER端
[root@Server1 data]# mysql -p ##登陆数据库
mysql> ALTER USER root@localhost identified by 'westos';
mysql> show master status;
mysql> grant replication slave on *.* to repl@'%' identified by 'westos';

##对SLAVE端
[root@Server2 data]# mysql -p
mysql> ALTER USER root@localhost identified by 'westos';
mysql> change master to master_host='172.25.5.1', master_user='repl', master_password='westos',MASTER_AUTO_POSITION = 1;
mysql> start slave;
mysql> show slave status\G;

部署MHA

Manager工具包主要包括以下工具

masterha_check_ssh			检查MHA的SSH配置状况
masterha_check_repl			检查MySQL复制状况	
masterha_manger				启动MHA
masterha_check_status		检测当前MHA运行状态
masterha_master_monitor		检测master是否宕机
masterha_master_switch		控制故障转移(自动或者手动)
masterha_conf_host			添加或删除配置的server信息

Node工具包由MHA Manager的脚本触发, 无需人为操作

其中主要包括以下工具

save_binary_logs			保存和复制master的二进制日志
apply_diff_relay_logs		识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog 			去除不必要的ROLLBACK事件(MHA已不再使用这个工具)
purge_relay_logs			清除中继日志(不会阻塞SQL线程)

新开一台机器作为管理端

##安装所需软件包
[root@Server4 ~]# tar zxf mha.tgz
[root@Server4 MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz                   perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm     perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm          perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm  perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm       perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@Server4 MHA-7]# yum isntall -y *.rpm

[root@Server4 mnt]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm 
warning: mysql-router-community-8.0.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-router-community-8.0.21-1.e################################# [100%]
[root@Server4 mnt]# vim /etc/mysqlrouter/mysqlrouter.conf 
[root@Server4 mnt]# systemctl start mysqlrouter.service 
[root@Server4 mnt]# netstat -antlp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      3161/sshd           
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      3941/mysqlrouter    
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      3319/master         
tcp        0      0 0.0.0.0:7002            0.0.0.0:*               LISTEN      3941/mysqlrouter    
tcp        0      0 172.25.5.4:22           172.25.5.250:44818      ESTABLISHED 3811/sshd: root@pts 
tcp6       0      0 :::22                   :::*                    LISTEN      3161/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      3319/master         
[root@Server4 mnt]# systemctl stop mysqlrouter.service

##获取配置模板并进行配置
[root@Server4 MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz 
[root@Server4 MHA-7]# cd mha4mysql-manager-0.58/
[root@Server4 mha4mysql-manager-0.58]# ls
AUTHORS  COPYING  lib          MANIFEST       README  samples  tests
bin      debian   Makefile.PL  MANIFEST.SKIP  rpm     t
[root@Server4 mha4mysql-manager-0.58]# cd samples/conf/
[root@Server4 conf]# ls
app1.cnf  masterha_default.cnf
[root@Server4 conf]# mkdir /etc/masterha
[root@Server4 conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app.cnf
[root@Server4 conf]# vim /etc/masterha/app.cnf

##检查SSH连通性,由于没有做免密一定会出现报错
[root@Server4 masterha]# masterha_check_ssh --conf=/etc/masterha/app.cnf
Tue Apr 13 16:56:59 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 13 16:56:59 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Tue Apr 13 16:56:59 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Tue Apr 13 16:56:59 2021 - [info] Starting SSH connection tests..
Tue Apr 13 16:57:00 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63] 
Tue Apr 13 16:56:59 2021 - [debug]  Connecting via SSH from root@172.25.5.1(172.25.5.1:22) to root@172.25.5.2(172.25.5.2:22)..
Warning: Permanently added '172.25.5.1' (ECDSA) to the list of known hosts.
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Tue Apr 13 16:56:59 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.25.5.1(172.25.5.1:22) to root@172.25.5.2(172.25.5.2:22) failed!
Tue Apr 13 16:57:00 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63] 
Tue Apr 13 16:57:00 2021 - [debug]  Connecting via SSH from root@172.25.5.2(172.25.5.2:22) to root@172.25.5.1(172.25.5.1:22)..
Warning: Permanently added '172.25.5.2' (ECDSA) to the list of known hosts.
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Tue Apr 13 16:57:00 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.25.5.2(172.25.5.2:22) to root@172.25.5.1(172.25.5.1:22) failed!
Tue Apr 13 16:57:01 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63] 
Tue Apr 13 16:57:00 2021 - [debug]  Connecting via SSH from root@172.25.5.3(172.25.5.3:22) to root@172.25.5.1(172.25.5.1:22)..
Warning: Permanently added '172.25.5.3' (ECDSA) to the list of known hosts.
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Tue Apr 13 16:57:00 2021 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@172.25.5.3(172.25.5.3:22) to root@172.25.5.1(172.25.5.1:22) failed!
SSH Configuration Check Failed!
 at /usr/bin/masterha_check_ssh line 44.
 
 ##作免密登录
[root@Server4 masterha]# ssh-keygen
[root@Server4 masterha]# ssh-copy-id Server1
[root@Server4 masterha]# ssh-copy-id Server2
[root@Server4 masterha]# ssh-copy-id Server3
[root@Server4 masterha]# scp -r ~/.ssh/ Server1:
[root@Server4 masterha]# scp -r ~/.ssh/ Server2:
[root@Server4 masterha]# scp -r ~/.ssh/ Server3:

##再次检查通过
[root@Server4 masterha]# masterha_check_ssh --conf=/etc/masterha/app.cnf
Tue Apr 13 17:03:31 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 13 17:03:31 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Tue Apr 13 17:03:31 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Tue Apr 13 17:03:31 2021 - [info] Starting SSH connection tests..
Tue Apr 13 17:03:32 2021 - [debug] 
Tue Apr 13 17:03:31 2021 - [debug]  Connecting via SSH from root@172.25.5.1(172.25.5.1:22) to root@172.25.5.2(172.25.5.2:22)..
Tue Apr 13 17:03:32 2021 - [debug]   ok.
Tue Apr 13 17:03:32 2021 - [debug]  Connecting via SSH from root@172.25.5.1(172.25.5.1:22) to root@172.25.5.3(172.25.5.3:22)..
Tue Apr 13 17:03:32 2021 - [debug]   ok.
Tue Apr 13 17:03:33 2021 - [debug] 
Tue Apr 13 17:03:32 2021 - [debug]  Connecting via SSH from root@172.25.5.2(172.25.5.2:22) to root@172.25.5.1(172.25.5.1:22)..
Tue Apr 13 17:03:32 2021 - [debug]   ok.
Tue Apr 13 17:03:32 2021 - [debug]  Connecting via SSH from root@172.25.5.2(172.25.5.2:22) to root@172.25.5.3(172.25.5.3:22)..
Tue Apr 13 17:03:32 2021 - [debug]   ok.
Tue Apr 13 17:03:34 2021 - [debug] 
Tue Apr 13 17:03:32 2021 - [debug]  Connecting via SSH from root@172.25.5.3(172.25.5.3:22) to root@172.25.5.1(172.25.5.1:22)..
Tue Apr 13 17:03:33 2021 - [debug]   ok.
Tue Apr 13 17:03:33 2021 - [debug]  Connecting via SSH from root@172.25.5.3(172.25.5.3:22) to root@172.25.5.2(172.25.5.2:22)..
Tue Apr 13 17:03:33 2021 - [debug]   ok.
Tue Apr 13 17:03:34 2021 - [info] All SSH connection tests passed successfully.

##做MHA切换等功能的检查,因为其他三台主机并没有安装配套的包因此也会报错
[root@Server4 masterha]# masterha_check_repl --conf=/etc/masterha/app.cnf
Tue Apr 13 17:14:48 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 13 17:14:48 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Tue Apr 13 17:14:48 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Tue Apr 13 17:14:48 2021 - [info] MHA::MasterMonitor version 0.58.
Creating directory /etc/masterha/app1.. done.
Tue Apr 13 17:14:48 2021 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.5.2(172.25.5.2:3306) :1045:Access denied for user 'root'@'Server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Tue Apr 13 17:14:48 2021 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.5.1(172.25.5.1:3306) :1045:Access denied for user 'root'@'Server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Tue Apr 13 17:14:48 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301]  at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Tue Apr 13 17:14:48 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301]  at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Tue Apr 13 17:14:48 2021 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 172.25.5.3(172.25.5.3:3306) :1045:Access denied for user 'root'@'Server4' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Tue Apr 13 17:14:48 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301]  at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Tue Apr 13 17:14:49 2021 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Tue Apr 13 17:14:49 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.
Tue Apr 13 17:14:49 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Tue Apr 13 17:14:49 2021 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!

##传输并安装
[root@Server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm Server1:/mnt
mha4mysql-node-0.58-0.el7.centos.noarch.rpm   100%   35KB  19.4MB/s   00:00    
[root@Server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm Server2:/mnt
mha4mysql-node-0.58-0.el7.centos.noarch.rpm   100%   35KB  20.6MB/s   00:00    
[root@Server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm Server3:/mnt
mha4mysql-node-0.58-0.el7.centos.noarch.rpm   100%   35KB  21.3MB/s   00:00

##检查成功通过
[root@Server4 masterha]# masterha_check_repl --conf=/etc/masterha/app.cnf
Tue Apr 13 17:16:26 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 13 17:16:26 2021 - [info] Reading application default configuration from /etc/masterha/app.cnf..
Tue Apr 13 17:16:26 2021 - [info] Reading server configuration from /etc/masterha/app.cnf..
Tue Apr 13 17:16:26 2021 - [info] MHA::MasterMonitor version 0.58.
Tue Apr 13 17:16:27 2021 - [info] GTID failover mode = 1
Tue Apr 13 17:16:27 2021 - [info] Dead Servers:
Tue Apr 13 17:16:27 2021 - [info] Alive Servers:
Tue Apr 13 17:16:27 2021 - [info]   172.25.5.1(172.25.5.1:3306)
Tue Apr 13 17:16:27 2021 - [info]   172.25.5.2(172.25.5.2:3306)
Tue Apr 13 17:16:27 2021 - [info]   172.25.5.3(172.25.5.3:3306)
Tue Apr 13 17:16:27 2021 - [info] Alive Slaves:
Tue Apr 13 17:16:27 2021 - [info]   172.25.5.2(172.25.5.2:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Tue Apr 13 17:16:27 2021 - [info]     GTID ON
Tue Apr 13 17:16:27 2021 - [info]     Replicating from 172.25.5.1(172.25.5.1:3306)
Tue Apr 13 17:16:27 2021 - [info]     Primary candidate for the new Master (candidate_master is set)
Tue Apr 13 17:16:27 2021 - [info]   172.25.5.3(172.25.5.3:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabled
Tue Apr 13 17:16:27 2021 - [info]     GTID ON
Tue Apr 13 17:16:27 2021 - [info]     Replicating from 172.25.5.1(172.25.5.1:3306)
Tue Apr 13 17:16:27 2021 - [info]     Not candidate for the new Master (no_master is set)
Tue Apr 13 17:16:27 2021 - [info] Current Alive Master: 172.25.5.1(172.25.5.1:3306)
Tue Apr 13 17:16:27 2021 - [info] Checking slave configurations..
Tue Apr 13 17:16:27 2021 - [info]  read_only=1 is not set on slave 172.25.5.2(172.25.5.2:3306).
Tue Apr 13 17:16:27 2021 - [info]  read_only=1 is not set on slave 172.25.5.3(172.25.5.3:3306).
Tue Apr 13 17:16:27 2021 - [info] Checking replication filtering settings..
Tue Apr 13 17:16:27 2021 - [info]  binlog_do_db= , binlog_ignore_db= 
Tue Apr 13 17:16:27 2021 - [info]  Replication filtering check ok.
Tue Apr 13 17:16:27 2021 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Tue Apr 13 17:16:27 2021 - [info] Checking SSH publickey authentication settings on the current master..
Tue Apr 13 17:16:27 2021 - [info] HealthCheck: SSH to 172.25.5.1 is reachable.
Tue Apr 13 17:16:27 2021 - [info] 
172.25.5.1(172.25.5.1:3306) (current master)
 +--172.25.5.2(172.25.5.2:3306)
 +--172.25.5.3(172.25.5.3:3306)

Tue Apr 13 17:16:27 2021 - [info] Checking replication health on 172.25.5.2..
Tue Apr 13 17:16:27 2021 - [info]  ok.
Tue Apr 13 17:16:27 2021 - [info] Checking replication health on 172.25.5.3..
Tue Apr 13 17:16:27 2021 - [info]  ok.
Tue Apr 13 17:16:27 2021 - [warning] master_ip_failover_script is not defined.
Tue Apr 13 17:16:27 2021 - [warning] shutdown_script is not defined.
Tue Apr 13 17:16:27 2021 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

三台主机上的操作

##从机安装客户端即可
[root@Server3 data]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

##主机安装客户端后需要在MySQL中对远程操作用户做授权
[root@Server1 .ssh]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@Server1 .ssh]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.31-log Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> GRANT ALL ON *.* TO root@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置文件示例

[server default]
user=root
password=westos
ssh_user=root
repl_user=root
repl_password=westos
master_binlog_dir= /usr/local/mysql/data
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.5.3
ping_interval=3
# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log

[server1]
hostname=172.25.5.1
candidate_master=1

[server2]
hostname=172.25.5.2
candidate_master=1

[server3]
hostname=172.25.5.3
no_master=1

[server default]
manager_workdir					manager工作目录
manager_log						manager日志文件
master_binlog_dir				mysql主服务器的binlog目录
master_ip_failover_script		failover自动切换脚本
master_ip_online_change_script	手动切换脚本
user							
password						mysql主从节点的管理员用户密码
ping_interval=3					发送ping包的时间间隔, 默认是3秒, 尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp				远端mysql在发生切换时binlog的保存位置
repl_user=repl					主从复制用户密码
repl_password=westos	

report_script=/usr/local/send_report		发生切换后发送报警的脚本
secondary_check_script=/usr/bin/masterha_secondary_check -s 172.25.5.4 -s 172.25.5.3
shutdown_script=""							故障发生后关闭故障主机脚本, 防止脑裂
ssh_user=root								ssh用户名

MHA高可用的切换

MHA高可用分为自动切换和手动切换

手动切换也分为两种:维护状态和故障处理

MHA的故障切换过程, 共包括以下的步骤:

配置文件检查阶段, 这个阶段会检查整个集群配置文件配置
宕机的master处理, 这个阶段包括虚拟IP摘除操作, 主机关机操作
复制dead master和最新slave相差的relay log, 并保存到MHA Manger具体的目录下
识别含有最新更新的slave
应用从master保存的二进制日志事件(binlog events)
提升一个slave为新的master进行复制
使其他的slave连接新的master进行复制

切换过程中会锁表,停止二进制日志,完成后解锁

故障切换时会生成文件app.failover.complete

自动切换会保存日志manager.log

手动切换

MASTER为1且存活, 将其切换到2

masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.5.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

MASTER为1且临时关闭模拟出现问题, 将其切换到2

masterha_master_switch --master_state=dead --conf=/etc/masterha/app.cnf --dead_master_host=172.25.5.2 --dead_master_port=3306 --new_master_host=172.25.5.1 --new_master_port=3306 --ignore_last_failover

此时集群中只有2台主机, Server2主Server3从, Server1死

masterha_check_repl --conf=/etc/masterha/app.cnf

会提示错误, 因为不满足一主两从配置

自动模式

下载两个脚本

master_ip_failover  master_ip_online_change

赋予运行权限

chmod +x master_ip_failover  master_ip_online_change

修改其中的VIP为自己的172.25.5.100
将一开始注释的配置文件中的脚本选项打开

操作记录

Mysql数据库读写分离

宿主机的操作

[root@foundation5 images]# mysql -h 172.25.5.4 -P 7001 -u user1 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.31-log Source distribution

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

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

MySQL [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Test               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.001 sec)

MySQL [(none)]> INSERT INTO Test.t1 VALUES (2, 'NeuWings');
ERROR 1142 (42000): INSERT command denied to user 'user1'@'Server4' for table 't1'
MySQL [(none)]> exit
Bye
[root@foundation5 images]# mysql -h 172.25.5.4 -P 7001 -u user2 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.31-log Source distribution

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

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

MySQL [(none)]> INSERT INTO Test.t1 VALUES (2, 'NeuWings');
Query OK, 1 row affected (0.007 sec)

Server1的操作

[root@Server1 data]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.31-log Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> GRANT SELECT ON *.* to user1@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE Test;
Query OK, 1 row affected (0.00 sec)

mysql> USE Test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)

mysql> GRANT INSERT,UPDATE ON Test.* to user2@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

##安装lsof用于查看端口情况
[root@Server1 data]# yum install lsof -y
[root@Server1 data]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  4019 mysql   22u  IPv6  27137      0t0  TCP *:mysql (LISTEN)
mysqld  4019 mysql   82u  IPv6  31830      0t0  TCP Server1:mysql->Server4:43098 (ESTABLISHED)