mysql下的MHA的高可用

最新推荐文章于 2024-03-19 10:14:54 发布

zhou562334410

最新推荐文章于 2024-03-19 10:14:54 发布

阅读量202

点赞数

本文链接：https://blog.csdn.net/zhou562334410/article/details/88139198

版权

MHA简介

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于 Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在 0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

实验环境

redhat6.5
iptables和selinux关闭
master:server2 (172.25.35.52)
slave:server3、4 (172.25.35.53、172.25.35.54)
mha:server5 (172.25.35.55)

1、数据同步

Server2、3、4：

安装mysql，安全初始化
[root@server2 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@server2 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y
[root@server2 ~]# vim /etc/my.cnf  //server3、server4的server_id不同其他相同
server_id=2
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
[root@server2 ~]# /etc/init.d/mysqld start
[root@server2 ~]# grep password /var/log/mysqld.log
2018-08-10T17:37:45.733990Z 1 [Note] A temporary password is generated for root@localhost: Fb7d*tg4wR8q   //初始密码
[root@server2 ~]# mysql_secure_installation  //修改root密码

server2：

[root@server2 ~]# mysql -pZhanG@2424
mysql> grant replication slave on *.* to student@'172.25.35.%' identified by 'ZhanG@2424';  //用户授权

server3、server4：

mysql> change master to master_host='172.25.35.52' , master_user='student' , master_password='ZhanG@2424' , MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.18 sec)

mysql> start slave;
Query OK, 0 rows affected (0.07 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.35.52
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 150
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 349
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试：

Server2：

mysql> create database haha;
Query OK, 1 row affected (0.35 sec)
mysql> show databases;

这里写图片描述

server3、server4：查看可以同步到新建的数据库haha

mysql> show databases;

这里写图片描述

2、MHA切换

server5:

[root@server5 ~]# ls
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
[root@server5 ~]# yum install * -y
[root@server5 ~]# ssh-keygen  //生成key
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
30:44:87:c9:d4:3d:c3:49:35:4b:f2:45:2b:09:a2:ac root@server5
The key's randomart image is:
+--[ RSA 2048]----+
|     +++o++o+.o  |
|     o+o..*= = . |
|      =    o= .  |
|     . o     .   |
|    E   S        |
|                 |
|                 |
|                 |
|                 |
+-----------------+
[root@server5 ~]# ssh-copy-id 172.25.35.52
[root@server5 ~]# ssh-copy-id 172.25.35.53
[root@server5 ~]# ssh-copy-id 172.25.35.54
[root@server5 ~]# scp ~/.ssh/id_rsa -p root@172.25.35.52:~/.ssh/
[root@server5 ~]# scp ~/.ssh/id_rsa -p root@172.25.35.53:~/.ssh/
[root@server5 ~]# scp ~/.ssh/id_rsa -p root@172.25.35.54:~/.ssh/

测试各结点之间是否免密连接server3、4同server2

[root@server2 ~]# ssh server3
Last login: Sat Aug 11 02:46:15 2018 from server2
[root@server3 ~]# exit
logout
Connection to server3 closed.
[root@server2 ~]# ssh server4
Last login: Sat Aug 11 01:02:45 2018 from 172.25.35.250
[root@server4 ~]# exit
logout
Connection to server4 closed.

Server2:创建监控用户同步到server3、server4

server5:

[root@server5 ~]# chmod +x /usr/local/bin/master_ip_failover  //给脚本加可执行权限
[root@server5 ~]# chmod +x /usr/local/bin/master_ip_online_change 
[root@server5 ~]# mkdir /etc/mha
[root@server5 ~]# 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=ZhanG@2424    //设置mysql中root用户的密码,这个密码是前文中创建监控用户的那个密码
ping_interval=1     //设置监控主库,发送ping包的时间间隔,默认是3秒,尝试三次没有回应的时候自动进行railover
remote_workdir=/tmp    //设置远端mysql在发生切换时binlog的保存位置
repl_user=student     //设置复制环境中的复制用户名
repl_password=ZhanG@2424    //设置复制用户的密码
#report_script=/usr/local/send_report    //设置发生切换后发送的报警的脚本
ssh_user=root    //设置ssh的登录用户名

[server2]
hostname=172.25.35.52
port=3306

[server4]
hostname=172.25.35.54
port=3306
#candidate_master=1   //设置为候选master
#check_repl_delay=0    //默认情况下如果一个slave落后master 100M的relay logs的话,MHA将不会选择该slave作为一个新的master

[server3]
hostname=172.25.35.53
port=3306
[root@server5 ~]# masterha_check_ssh --conf=/etc/mha/mha.conf
Sat Aug 11 02:47:41 2018 - [info] All SSH connection tests passed successfully.
//最后一行显示此行表示各结点ssh是ok

[root@server5 ~]# masterha_check_repl --conf=/etc/mha/mha.conf
MySQL Replication Health is OK.  //最后一行显示此行表示成功

mysql> grant all on *.* to root@'172.25.35.%' identified by 'ZhanG@2424';
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> grant replication slave on *.* to student@'172.25.35.%' identified by 'ZhanG@2424';
Query OK, 0 rows affected, 1 warning (0.34 sec)

1、手动在线切换

Server3、Server4：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.35.52
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 886
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 389
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

server5：

[root@server5 ~]# masterha_master_switch --conf=/etc/mha/mha.conf --master_state=alive --new_master_host=172.25.35.54 --new_master_port=3306 --orig_master_is_new_slave
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.25.35.52(172.25.35.52:3306)? (YES/no): YES
Starting master switch from 172.25.35.52(172.25.35.52:3306) to 172.25.35.54(172.25.35.54:3306)? (yes/NO): yes
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes

查看切换：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.35.54
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 649
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 389
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

server4：

mysql> show slave status\G;  //因为master变成server4，所以查看不到slave的状态
Empty set (0.00 sec)

ERROR: 
No query specified
mysql> show master status;

这里写图片描述

测试切换后是否同步：

server4：给数据库插入内容

mysql> use haha;
Database changed
mysql> create table info(
    -> username varchar(10) not null,
    -> password varchar(10) not null);
Query OK, 0 rows affected (0.28 sec)

mysql> insert into info values('user1','111');
Query OK, 1 row affected (0.45 sec)

mysql> select * from haha.info;

这里写图片描述

server2、server3：可以看到插入的内容

mysql> select * from haha.info;

这里写图片描述

2、手动故障切换

server4：

[root@server4 ~]# ps ax
 1642 pts/1    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/my
 1947 pts/1    Sl     0:07 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib
[root@server4 ~]# kill -9 1642
[root@server4 ~]# kill -9 1947

server5：

[root@server5 ~]# masterha_master_switch --conf=/etc/mha/mha.conf --master_state=dead --dead_master_host=172.25.35.54 --dead_master_post=3306 --new_master_host=172.25.35.52 --new_master_port=3306
Master 172.25.35.54(172.25.35.54:3306) is dead. Proceed? (yes/NO): yes
Starting master switch from 172.25.35.54(172.25.35.54:3306) to 172.25.35.52(172.25.35.52:3306)? (yes/NO): yes

查看切换：

Server3：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.35.52
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 886
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 389
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

server4：

[root@server4 ~]# /etc/init.d/mysqld start
[root@server4 ~]# mysql -pZhanG@2424
mysql> change master to master_host='172.25.35.52' , master_user='student' , master_password='ZhanG@2424' , MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.81 sec)

mysql> start slave;
Query OK, 0 rows affected (0.39 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.35.52
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 886
               Relay_Log_File: server4-relay-bin.000002
                Relay_Log_Pos: 389
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试切换后同步：

Server2:

mysql> use haha;
Database changed
mysql> insert into info values('user2','222');
Query OK, 1 row affected (0.37 sec)

Server3、Server4：

mysql> select * from haha.info;

这里写图片描述

3、自动切换测试

server5：

[root@server5 mha]# rm -f mha.failover.complete 
[root@server5 mha]# nohup masterha_manager --conf=/etc/mha/mha.conf &

Server2:

[root@server2 ~]# ps ax
 4513 pts/1    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql
 4822 pts/1    Sl     0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/my
[root@server2 ~]# kill -9 4513
[root@server2 ~]# kill -9 4822

server3：

mysql> show slave status\G;  //表示server3已经变成master
Empty set (0.24 sec)

ERROR: 
No query specified

Server4:

mysql> show slave status\G;  //证实上面的状态是正确的，master是server3
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.35.53
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 507
               Relay_Log_File: server4-relay-bin.000002
                Relay_Log_Pos: 389
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

server2：

[root@server2 ~]# /etc/init.d/mysqld start
[root@server2 ~]# mysql -pZhanG@2424
mysql> change master to master_host='172.25.35.53' , master_user='student' , master_password='ZhanG@2424' , MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.56 sec)

mysql> start slave;
Query OK, 0 rows affected (0.06 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.35.53
                  Master_User: student
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 507
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 389
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

切换后检测同步：

Server3:

mysql> use haha;
mysql> insert into info values('user3','333');
Query OK, 1 row affected (0.24 sec)

server2、server3：

mysql> select * from info;

这里写图片描述

zhou562334410

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫