MHA+mysql实现高可用

待我称王封你为后i 2022-05-15 13:54 281阅读 0赞

## MHA工作原理： ##

1）一主三从架构，主库挂了，但主库能被从库ssh上去的情况下，MHA从三个从库中选择同步最接近的作为新主，然后新主和s2，s3都ssh到原主上通过binlog补上还没有同步的数据，io\_thread读取到binlog位置，传到save\_binary\_logs，然后回放，达到s1,s2,s3和原主一致。

（2）主库无法ssh上去的情况下，即主库的系统无法连接，假设有binlog server ，利用binlog补数据，和前面情况一样；

如果没有binlog server，假设s1靠前，s2和s3会通过relay-log和s1同步（所以从库的relay不能自动清除参数relay\_log\_purge = 0） ，提升s1为主，把s2和s3作为s1的从

## 实验环境： ##

rhel6.5 mysql5.7  
master server1  
slave server2. 3  
MHA server4

## 部署数据同步 ##

server1、serevr2、serevr3 做以下操作  
如果之前安装过mysql，卸掉

rpm -qa |grep mysql   查看系统中是否有mysql包
    rpm -e   --nodeps       删除

**安装mysql**

[root@server1 ~]# tar xf mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar
    [root@server1 ~]#  yum install -y mysql-community-client-5.7.20-1.el6.x86_64.rpm 
    mysql-community-libs-compat-5.7.20-1.el6.x86_64.rpm 
    mysql-community-common-5.7.20-1.el6.x86_64.rpm 
    mysql-community-libs-5.7.20-1.el6.x86_64.rpm 
    mysql-community-server-5.7.20-1.el6.x86_64.rpm

**编辑文件**  
server1、serevr2、serevr3只有serevr\_id不同，其他内容不变

[root@server1 ~]# vim /etc/my.cnf
    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

打开mysql
    [root@server1 ~]# /etc/init.d/mysqld start
    查看端口是否开启
    [root@server1 ~]# netstat -antlp |grep mysql
    tcp        0      0 :::3306                     :::*

安全初始化

查看mysql 初始密码
    [root@server1 ~]# grep password /var/log/mysqld.log
    2018-10-27T10:09:29.923519Z 1 [Note] A temporary password is generated for root@localhost: t1h5kLWl>Q9V
    [root@server1 ~]# mysql_secure_installation  修改root用户密码

到此server1、serevr2、serevr3共同的操作完成  
**在master 上给slave 授权**

[root@server1 ~]# mysql -pCUTE@123.com
    mysql> grant replication slave on *.* to salt@'172.25.77.%' identified by 'CUTE@123.com';

**在两个slave 上指定master**

[root@server2 ~]# mysql -pCUTE@123.com
    mysql> change master to master_host='172.25.77.1' , master_user='salt', master_password='CUTE@123.com', MASTER_AUTO_POSITION=1;
    mysql> start slave;
    mysql> show slave status\G;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70]  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
io线程和sql线程都开启则表示以上操作成功  
**测试是否同步**  
在master 端写入slave 端查看

**slave端与master端数据一致则表示数据同步成功**

## MHA切换 ##

serevr4安装MHA

**所需包**

Installed:
      mha4mysql-manager.noarch 0:0.56-0.el6             
      mha4mysql-node.noarch 0:0.56-0.el6                
      perl-Config-Tiny.noarch 0:2.12-7.1.el6            
      perl-Email-Date-Format.noarch 0:1.002-5.el6       
      perl-Log-Dispatch.noarch 0:2.27-1.el6             
      perl-MIME-Lite.noarch 0:3.027-2.el6               
      perl-MIME-Types.noarch 0:1.28-2.el6               
      perl-Mail-Sender.noarch 0:0.8.16-3.el6            
      perl-Mail-Sendmail.noarch 0:0.79-12.el6           
      perl-Parallel-ForkManager.noarch 0:0.7.9-1.el6

**生成密钥：**

[root@server4 ~]# ssh-keygen
    [root@server4 ~]# ssh-copy-id 172.25.77.1
    [root@server4 ~]# ssh-copy-id 172.25.77.2
    [root@server4 ~]# ssh-copy-id 172.25.77.3

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 2]  
**分发钥匙**

[root@server4 ~]# cd .ssh/
    [root@server4 .ssh]# ls
    id_rsa  id_rsa.pub  known_hosts
    [root@server4 .ssh]# scp id_rsa server1:.ssh/
    [root@server4 .ssh]# scp id_rsa server2:.ssh/
    [root@server4 .ssh]# scp id_rsa server3:.ssh/

**在其他各个节点测试是否互相可以免密连接（如server1的操作）**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 3]  
**在master 端创建监控用户同步到slave 端**

mysql> grant all on *.* to root@'172.25.77.%' identified by 'CUTE@123.com';
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> grant replication slave on *.* to salt@'172.25.77.%' identified by 'CUTE@123.com';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql>

**在MHA 端**

[root@server4 ~]# chmod +x /usr/local/bin/master_ip_failover  //给脚本加可执行权限
    [root@server4 ~]# chmod +x /usr/local/bin/master_ip_online_change
    [root@server4 ~]# mkdir /etc/mha
    [root@server4 ~]# vim /etc/mha/mha.conf
    [server default] 
    manager_workdir=/etc/mha //设置manager的工作目录 
    manager_log=/etc/mha/mha.log //设置manager的日志 
    master_binlog_dir=/var/lib/mysql //设置master 保存binlog的位置,以便MHA可以找到master的日志 
    #master_ip_failover_script=/usr/local/bin/master_ip_failover //设置自动failover时候的切换脚本 
    #master_ip_online_change_script=/usr/local/bin/master_ip_online_change //设置手动切换时候的切换脚本 
    user=root //设置监控用户root 
    password=CUTE@123.com //设置mysql中root用户的密码,这个密码是前文中创建监控用户的那个密码 
    ping_interval=1 //设置监控主库,发送ping包的时间间隔,默认是3秒,尝试三次没有回应的时候自动进行railover 
    remote_workdir=/tmp //设置远端mysql在发生切换时binlog的保存位置 
    repl_user=salt //设置复制环境中的复制用户名 
    repl_password=CUTE@123.com //设置复制用户的密码 
    #report_script=/usr/local/send_report //设置发生切换后发送的报警的脚本 
    ssh_user=root //设置ssh的登录用户名 
    
    [server1] 
    hostname=172.25.77.1 
    port=3306 
    
    [server2] 
    hostname=172.25.77.2 
    port=3306 
    #candidate_master=1 //设置为候选master 
    #check_repl_delay=0 //默认情况下如果一个slave落后master 100M的relay logs的话,MHA将不会选择该slave作为一个新的master 
    
    [server3] 
    hostname=172.25.77.3 
    port=3306

**检测ssh 与健康检查是否配置成功**

[root@server4 ~]# masterha_check_ssh --conf=/etc/mha/mha.conf
    Sat Oct 27 20:01:56 2018 - [info] All SSH connection tests passed successfully.
    
    [root@server4 ~]# masterha_check_repl --conf=/etc/mha/mha.conf
    MySQL Replication Health is OK.

**测试：**  
手动在线切换

*  在slave端查看现在的master是谁

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 4]  
**在MHA 上手动切换master**

masterha_master_switch --conf=/etc/mha/mha.conf --master_state=alive --new_master_host=172.25.77.2 --new_master_port=3306 --orig_master_is_new_slave

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 5]  
**在slave 端查看是否成功**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 6]  
server2为新的master 原来的master变为slave  
**测试切换后是否同步：**  
在新master上写入数据

mysql> use linux ;
    Database changed
    mysql> create table info(
        -> username varchar(10) not null,
        -> password varchar(10) not null);
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> insert into info values('user1','111');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from linux.info;
    +----------+----------+
    | username | password |
    +----------+----------+
    | user1    | 111      |
    +----------+----------+
    1 row in set (0.00 sec)

在slave端查看是否同步  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 7]

## 手动故障切换 ##

**kill掉master 端的mysql进程**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 8]  
**在MHA端手动切换坏掉的master**

[root@server4 ~]# masterha_master_switch --conf=/etc/mha/mha.conf --master_state=dead --dead_master_host=172.25.77.2 --dead_master_port=3306 --new_master_host=172.25.77.1 --new_master_port=3306

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 9]  
**在slave 端查看**  
成功替换掉坏的master  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 10]  
**重新打开server2**

Starting mysqld:                                           [  OK  ]
    [root@server2 ~]# mysql -pCUTE@123.com
    mysql> change master to master_host='172.25.77.1' , master_user='salt' , master_password='CUTE@123.com' , MASTER_AUTO_POSITION=1;
    Query OK, 0 rows affected, 2 warnings (0.05 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.02 sec)
    mysql> show slave status\G;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 11]  
**测试切换后是否同步**  
在master端写入数据

mysql> use linux;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> insert into info values('use2','123');
    Query OK, 1 row affected (0.02 sec)

**slave 端查看**

mysql> select * from linux.info
        -> ;
    +----------+----------+
    | username | password |
    +----------+----------+
    | user1    | 111      |
    | use2     | 123      |
    +----------+----------+
    2 rows in set (0.00 sec)
    
    mysql>

## 自动切换 ##

**删除MHA端的切换脚本，将服务打入后台**

[root@server4 ~]# cd /etc/mha/
    [root@server4 mha]# ls
    mha.conf  mha.failover.complete
    [root@server4 mha]# rm -fr mha.failover.complete 
    [root@server4 mha]# nohup masterha_manager --conf=/etc/mha/mha.conf &
    [1] 1698

**关闭master端的mysql 进程**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 12]  
在其他主机上查看新master为谁  
server3  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 13]  
显示serevr2现在为master  
手动打开server1

[root@server1 ~]# /etc/init.d/mysqld start
    Starting mysqld:                                           [  OK  ]
    [root@server1 ~]# mysql -pCUTE@123.com
    mysql> change master to master_host='172.25.77.2', master_user='salt', master_password='CUTE@123.com', MASTER_AUTO_POSITION=1;
    Query OK, 0 rows affected, 2 warnings (0.07 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show slave status\G;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 14]  
**检测切换后是否同步**  
在master端写入数据

mysql> use linux;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    mysql> insert into info values('user3','234');
    Query OK, 1 row affected (0.01 sec)

在slave端是否同步  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 15]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70]: /images/20220503/e9fe45a0bc0644da99bd69842a2a1eb0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 1]: /images/20220503/750a0f5a442f4079a3090d7c51d38d42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 2]: /images/20220503/88629fcac8c5473dba07fb8ab0dff262.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 3]: /images/20220503/2403721746a241bfaced553e610520da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 4]: /images/20220503/92b4487815294275932111ec35e5e416.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 5]: /images/20220503/7078c3caaa41432482e4ad2169c33958.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 6]: /images/20220503/da82dad82cf849b9b815f350576de5a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 7]: /images/20220503/2c15d28a43d240c3a345a29593d53895.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 8]: /images/20220503/c412d9e2b925403fb72c5a5c227d0ab0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 9]: /images/20220503/dadaa46e47fc4d3ca54c79980d2a3c7f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 10]: /images/20220503/40dbd8a3bf1842328ed199886d8cea87.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 11]: /images/20220503/a614e6ab112e4db6acfd104d378d2e45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 12]: /images/20220503/ac6067dc64f3409c831a7347b9fd310b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 13]: /images/20220503/21ebb69b52864eb491dbfac3f6657e74.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 14]: /images/20220503/2e3d85b7f8e54c66ad693953065c45ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjcxMTU0OQ_size_27_color_FFFFFF_t_70 15]: /images/20220503/53945b3abc7e42db9040d6cc7ec4504d.png