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
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)
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. //最后一行显示此行表示成功
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;
扫一扫
1447
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
