一.Mysql架构的简介
MHA：
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。
MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。
MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。
MHA优点：
在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。
MHA处理流程：
从宕机崩溃的master保存二进制日志事件（binlog events）;
识别含有最新更新的slave；
应用差异的中继日志（relay log）到其他的slave；
应用从master保存的二进制日志事件（binlog events）；
提升一个slave为新的master；
使其他的slave连接新的master进行复制；
二.mysql的高可用集群架构的搭建
实验环境：
在server1、server2、server3配置基于gtid的主从复制
刷新数据库，server1、server2、server3的操作相同 编辑配置文件
server1：
server2:
server3:
重启数据库，获取初始密码
systemctl start mysqldcat /var/log/mysql.log | grep password
配置主从
server1（主）：
[root@server1 mysql]# mysql -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.24-logCopyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> alter user root@localhost identified by 'Szy+123en';Query OK, 0 rows affected (0.01 sec)mysql> grant replication slave on *.* to repl@'172.25.16.%' identified by 'Szy+123en';Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.04 sec)mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.02 sec)mysql> set global rpl_semi_sync_master_enabled=1;Query OK, 0 rows affected (0.00 sec)mysql> set global rpl_semi_sync_master_timeout=10000000000000;Query OK, 0 rows affected (0.00 sec)
server2（从）:
[root@server2 mysql]# mysql -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.24-logCopyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> alter user root@localhost identified by 'Szy+123en';Query OK, 0 rows affected (0.14 sec)mysql> change master to master_host='172.25.16.1', master_user='repl',master_password='Szy+123en',master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.17 sec)mysql> set global rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec)mysql> stop slave io_thread;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> start slave io_thread;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.16.1Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 691Relay_Log_File: server2-relay-bin.000002Relay_Log_Pos: 898Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 691Relay_Log_Space: 1107Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c1d68221-b782-11e9-9293-5254004772f0Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: c1d68221-b782-11e9-9293-5254004772f0:1-2Executed_Gtid_Set: c1d68221-b782-11e9-9293-5254004772f0:1-2,ef766a82-b783-11e9-a2e0-52540039676f:1-2Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
server3(从):
mysql> alter user root@localhost identified by 'Szy+123en';Query OK, 0 rows affected (0.08 sec)mysql> change master to master_host='172.25.16.1', master_user='repl',master_password='Szy+123en',master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.15 sec)mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.07 sec)mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';Query OK, 0 rows affected (0.02 sec)mysql> set global rpl_semi_sync_slave_enabled=1;Query OK, 0 rows affected (0.00 sec)mysql> stop slave io_thread;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> start slave io_thread;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.16.1Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 691Relay_Log_File: server3-relay-bin.000002Relay_Log_Pos: 898Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 691Relay_Log_Space: 1107Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: c1d68221-b782-11e9-9293-5254004772f0Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: c1d68221-b782-11e9-9293-5254004772f0:1-2Executed_Gtid_Set: 2988fbc1-b785-11e9-ab3c-52540046c65d:1,c1d68221-b782-11e9-9293-5254004772f0:1-2Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
测试：
在server1写入数据:
mysql> create database westos;Query OK, 1 row affected (0.01 sec)mysql> use westos;Database changedmysql> create table userlist(-> username varchar(10) not null,-> password varchar(15) not null);Query OK, 0 rows affected (0.05 sec)mysql> insert into userlist values ('user1','123')-> ;Query OK, 1 row affected (0.02 sec)mysql> select * from userlist;+----------+----------+| username | password |+----------+----------+| user1 | 123|+----------+----------+1 row in set (0.00 sec)
server2查看
server3查看
三.在server4上安装管理节点
[root@server4 MHA-7]# lsmha4mysql-manager-0.58-0.el7.centos.noarch.rpmmha4mysql-manager-0.58.tar.gzmha4mysql-node-0.58-0.el7.centos.noarch.rpmperl-Config-Tiny-2.14-7.el7.noarch.rpmperl-Email-Date-Format-1.002-15.el7.noarch.rpmperl-Log-Dispatch-2.41-1.el7.1.noarch.rpmperl-Mail-Sender-0.8.23-1.el7.noarch.rpmperl-Mail-Sendmail-0.79-21.el7.noarch.rpmperl-MIME-Lite-3.030-1.el7.noarch.rpmperl-MIME-Types-1.38-2.el7.noarch.rpmperl-Parallel-ForkManager-1.18-2.el7.noarch.rpm[root@server4 MHA-7]# yum install *
2.添加解析
vim /etc/hosts
3.生成server4的ssh密钥，并发送给server1、server2、server3
ssh-keygenssh-copy-id server1ssh-copy-id server2ssh-copy-id server3
4.server1、server2、server3安装节点
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:mha4mysql-node-0.58-0.el7.centos.noarch. 100% 35KB 9.1MB/s 00:00 [root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:mha4mysql-node-0.58-0.el7.centos.noarch. 100% 35KB 9.4MB/s 00:00 [root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:mha4mysql-node-0.58-0.el7.centos.noarch. 100% 35KB 10.6MB/s 00:00 [root@server1 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
5.编辑mha配置文件
[root@server4 ~]# mkdir /etc/masterha[root@server4 ~]# lsMHA-7[root@server4 ~]# cd /etc/masterha/[root@server4 masterha]# ls[root@server4 masterha]# [root@server4 masterha]# vim f[server default]manager_workdir=/etc/masterhamanager_log=/var/log/masterha.logmaster_binlog_dir=/etc/masterhapassword=Szy+123enuser=rootping_interval=1remote_workdir=/tmprepl_password=Szy+123enrepl_user=replssh_user=root[server1]hostname=172.25.16.1port=3306[server2]hostname=172.25.16.2port=3306candidate_master=1check_repl_delay=0candidate_master=1check_repl_delay=0[server3]hostname=172.25.16.3port=3306no_master=1
6.密钥互相传递
[root@server4 masterha]# scp -r ~/.ssh server1:id_rsa100% 16795.8KB/s 00:00 id_rsa.pub 100% 3946.6KB/s 00:00 known_hosts100% 543 384.3KB/s 00:00 [root@server4 masterha]# scp -r ~/.ssh server2:id_rsa100% 16791.3MB/s 00:00 id_rsa.pub 100% 394 381.2KB/s 00:00 known_hosts100% 543 58.7KB/s 00:00 [root@server4 masterha]# scp -r ~/.ssh server3:id_rsa100% 16791.1MB/s 00:00 id_rsa.pub 100% 394 349.5KB/s 00:00 known_hosts100% 543 95.2KB/s 00:00
7.检查ssh是否出错
[root@server4 masterha]# masterha_check_ssh --conf=/etc/masterha/f
server1：
mysql> grant all on *.* to root@'%' identified by 'Szy+123en';
server2：
mysql> set global read_only=1;
server3：
mysql> set global read_only=1;
8.查看mysql的复制情况
[root@server4 masterha]# masterha_check_repl --conf=/etc/masterha/f
四.MHA测试
(1). 手动测试：
1.关闭server1的mysql
[root@server1 ~]# systemctl stop mysqld
2.手动将master节点转换到server2上
[root@server4 masterha]# masterha_master_switch --master_state=dead --conf=/etc/masterha/f --dead_master_host=172.25.16.1 --dead_master_port=3306 --new_master_host=172.25.16.2 --new_master_port=3306 输入yes yes
3.server2查看slave状态为空
4.server3查看slave状态（master的ip转到server2）
5.打开server1的mysql将slave添加进群组
[root@server1 ~]# systemctl start mysqld[root@server1 ~]# mysql -pSzy+123enmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.24-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> change master to master_host='172.25.16.2', master_user='repl', master_password='Szy+123en', master_auto_position=1;mysql> start slave;Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.16.2Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 3026Relay_Log_File: server1-relay-bin.000002Relay_Log_Pos: 942Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 3026Relay_Log_Space: 1151Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2Master_UUID: ef766a82-b783-11e9-a2e0-52540039676fMaster_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: ef766a82-b783-11e9-a2e0-52540039676f:1-2Executed_Gtid_Set: c1d68221-b782-11e9-9293-5254004772f0:1-9,ef766a82-b783-11e9-a2e0-52540039676f:1-2Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
6.手动将matser转到server1上
[root@server4 masterha]# masterha_master_switch --master_state=alive --conf=/etc/masterha/f --new_master_host=172.25.16.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=1000
server1:
server2:
server3:
(2)自动转换
1.在server4下创建一个检测进程，来创建监控master的进程并查看进程，即执行自动转换命令
[root@server4 masterha]# nohup masterha_manager --conf=/etc/masterha/f &> /dev/null &[1] 1639[root@server4 masterha]# ps aPID TTYSTAT TIME COMMAND1108 tty1Ss+ 0:00 -bash1131 pts/0 Ss0:00 -bash1639 pts/0 S0:00 perl /usr/bin/masterha_manager --conf=/etc/mas1663 pts/0 R+0:00 ps a
2.关掉server1的mysql
[root@server1 ~]# systemctl stop mysqld
3.此时master服务自动调转到server2
3.server4中的脚本进程挂掉了
（3）通过脚本控制（通过vip的漂移查看）
1.官网下载mha高可用的manager的安装包并且解压
2.编辑master_ip_failover文件（脚本文件一）
my $vip = '172.25.16.100/24';my $key = '1';my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
3.编辑master_ip_online_change文件（脚本文件二）
my $vip = '172.25.16.100/24'; # Virtual IP my $key = "1";my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
4.将两个脚本文件拷贝到/usr/local/bin目录下，并添加权限
在这里插入代码片[root@server4 scripts]# cp master_ip_* /usr/local/bin/[root@server4 scripts]# chmod +x /usr/local/bin/master_ip_*[root@server4 scripts]# ll /usr/local/bin/total 16-rwxr-xr-x 1 root root 3802 Aug 5 23:58 master_ip_failover-rwxr-xr-x 1 root root 10041 Aug 5 23:58 master_ip_online_change
给server2添加vip172.25.16.100
手动转换master到server1上
[root@server4 bin]# masterha_master_switch --conf=/etc/masterha/f --master_state=alive --new_master_host=172.25.16.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
在server1中查看ip，会查看到vip漂移到了server1上
在server4下创建监控master的进程，当master节点宕机，server4会自动执行/usr/local/bin/的两个脚本，两个脚本会自动选择一个新的节点作为master
[root@server4 masterha]# nohup masterha_manager --conf=/etc/masterha/f &> /dev/null &[root@server4 masterha]# ps a
在server1中关闭mysqld
[root@server1 ~]# systemctl stop mysqld
在server3中查看master，发现此时master变成了server2,vip回到server2
