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试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。
目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，因为至少需要三台服务器，出于机器成本的考虑，淘宝也在该基础上进行了改造，目前淘宝TMHA已经支持一主一从。
我们自己使用其实也可以使用1主1从，但是master主机宕机后无法切换，以及无法补全binlog。master的mysqld进程crash后，还是可以切换成功，以及补全binlog的。
官方介绍：https://code.google.com/p/mysql-master-ha/
展示了如何通过MHA Manager管理多组主从复制。
可以将MHA工作原理总结
（1）从宕机崩溃的master保存二进制日志事件（binlog events）;
（2）识别含有最新更新的slave；
（3）应用差异的中继日志（relay log）到其他的slave；
（4）应用从master保存的二进制日志事件（binlog events）；
（5）提升一个slave为新的master；
（6）使其他的slave连接新的master进行复制；
MHA软件由两部分组成，Manager工具包和Node工具包，具体的说明如下。
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线程）
注意：
为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL 5.5的半同步复制。关于半同步复制原理各位自己进行查阅。（不是必须）
以上修改的一些文件，把注释和空格都要去掉
部署MHA
搭建主从复制（一主两从）
设备：
master：10.0.0.41
slave1：10.0.0.42
slave2：10.0.0.43
slave3：10.0.0.44
Master主操作：
关闭防火墙，seliunx（四台）
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i '/^SELINUX/s#enforcing#disabled#g' /etc/selinux/config
设置同步（四台）
echo "*/5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com >/dev/null 2>&1">>/var/spool/cron/root
修改主机名
[root@ c7m01 ~]# hostname c7m01
[root@ c702 ~]# hostname c702
[root@ c703 ~]# hostname c703
[root@ c704 ~]# hostname c704
hosts解析（四台）
[root@ c7m01 ~]# vim /etc/hosts
10.0.0.41 c7m01
10.0.0.42 c702
10.0.0.43 c703
10.0.0.44 c704
配置免密登录（四台执行同样的操作）
[root@ c7m01 ~]# cat ssh.sh
#!/bin/bash
yum -y install sshpass &> /dev/null
read -p "请输入服务器密码：" passwd
UserName=root
IP="10.0.0."
#创建密钥
ssh-keygen -t dsa -f ~/.ssh/id_dsa -P "" &>/dev/null
#分发公钥
for i in 41 42 43 44
do
sshpass -p "$passwd" ssh-copy-id -i ~/.ssh/id_dsa.pub -p 22 -o StrictHostKeyChecking=no $UserName@$IP$i &>/dev/null
done
执行（四台）
[root@ c7m01 ~]# chmod +x ssh.sh
[root@ c7m01 ~]# sh ssh.sh
请输入服务器密码：123456
测试
[root@ c7m01 ~]# ssh root@10.0.0.43
Last login: Tue May 19 15:09:48 2020 from 10.0.0.1
[root@ c703 ~]# exit
logout
Connection to 10.0.0.43 closed.
[root@ c702 ~]# ssh root@10.0.0.41
Last login: Tue May 19 15:09:44 2020 from 10.0.0.1
[root@ master ~]# exit
logout
Connection to 10.0.0.41 closed.
mysql安装yum repo（三台41,42,43）
[root@ c7m01 ~]# wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
[root@ c7m01 ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
[root@ c7m01 ~]# yum -y install mysql-server
启动mariadb（三台41,42,43）
[root@ c7m01 ~]# systemctl start mysql
[root@ c7m01 ~]# netstat -anpt | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3131/mysqld
修改mysql密码（三台41,42,43）
[root@ c7m01 ~]# mysql
mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
主服务上操作
在master服务器上更改mysql配置文件
[root@ c7m01 ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
#禁止mysql自动删除relaylog工能
relay_log_purge = 0
#mysql5.6已上的特性，开启gtid，必须主从全开
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
[root@ c7m01 ~]# systemctl restart mysql
创建同步用户
[root@ c7m01 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看c7m01主库的master状态
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 530
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: c5173e3b-9860-11ea-89d2-000c29de703c:1-2
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 530 | | | c5173e3b-9860-11ea-89d2-000c29de703c:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#查看GTID状态
mysql> show global variables like '%gtid%';
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | c5173e3b-9860-11ea-89d2-000c29de703c:1-2 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+------------------------------------------+
7 rows in set (0.00 sec)
slave1从服务器上操作
在c702服务器上更改mysql配置文件
[root@ c702 ~]# vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
#禁止mysql自动删除relaylog工能
relay_log_purge = 0
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
skip-name-resolve
[root@ c702 ~]# systemctl restart mysql
######创建同步的用户
[root@ c702 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.12 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> change master to master_host='10.0.0.41',master_user='rep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=530;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 530
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 314
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: 530
Relay_Log_Space: 519
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: 3cd11b99-9a33-11ea-95b4-000c29b6ec09
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O threadto update it
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: 82b534da-9a33-11ea-95b6-000c29a7553d:1-2
Auto_Position: 0
1 row in set (0.09 sec)
ERROR:
No query specified
slave2从服务器上操作
在c703服务器上更改mysql配置文件
[root@ c703 ~]# vim /etc/my.cnf
[mysqld]
server-id=3
log-bin=mysql-bin
#禁止mysql自动删除relaylog工能
relay_log_purge = 0
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
skip-name-resolve
[root@ c703 ~]# systemctl restart mysql
创建同步的用户
[root@ c703 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.12 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> change master to master_host='10.0.0.41',master_user='rep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=530;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 530
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 314
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: 530
Relay_Log_Space: 519
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: 3cd11b99-9a33-11ea-95b4-000c29b6ec09
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O threadto update it
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: 82b534da-9a33-11ea-95b6-000c29a7553d:1-2
Auto_Position: 0
1 row in set (0.09 sec)
ERROR:
No query specified
在服务器上安装MHA的依赖（四台）
[root@ c7m01 ~]# yum -y install perl-DBD-mysql
[root@ c7m01 ~]# yum -y install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
授权MHA管理用户（三台41,42,43）
[root@ c7m01 ~]# mysql -uroot -p123456
mysql> grant all privileges on *.* to 'mha'@'10.0.0.%' identified by 'mha';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
安装MHA node节点（四台）
#上传mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@ c7m01x ~]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
在slave3安装（10.0.0.44）
[root@ c704 ~]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mha4mysql-manager-0.58-0.el7.cent################################# [100%]
配置MHA（10.0.0.44）
[root@ c704 ~]# mkdir -p /etc/mha
[root@ c704 ~]# mkdir -p /var/log/mha/app1
[root@ c704 ~]# vim /etc/mha/app1.cnf
[root@ c704 ~]# cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
user=mha
password=mha
ping_interval=2
repl_password=123456
repl_user=rep
ssh_user=root
[server1]
hostname=10.0.0.41
port=3306
[server2]
hostname=10.0.0.42
port=3306
[server3]
hostname=10.0.0.43
port=3306
ignore_fail=1
no_master=1
注意以上不要有注释和空格
测试（10.0.0.44）
[root@ c704 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Wed May 20 15:35:55 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 20 15:35:55 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed May 20 15:35:55 2020 - [info] Reading server configuration from /etc/mha/app1.cnf..
Wed May 20 15:35:55 2020 - [info] Starting SSH connection tests..
Wed May 20 15:35:56 2020 - [debug]
Wed May 20 15:35:55 2020 - [debug] Connecting via SSH from root@10.0.0.41(10.0.0.41:22) to root@10.0.0.42(10.0.0.42:22)..
Wed May 20 15:35:56 2020 - [debug] ok.
Wed May 20 15:35:56 2020 - [debug] Connecting via SSH from root@10.0.0.41(10.0.0.41:22) to root@10.0.0.43(10.0.0.43:22)..
Wed May 20 15:35:56 2020 - [debug] ok.
Wed May 20 15:35:56 2020 - [debug]
Wed May 20 15:35:55 2020 - [debug] Connecting via SSH from root@10.0.0.42(10.0.0.42:22) to root@10.0.0.41(10.0.0.41:22)..
Wed May 20 15:35:56 2020 - [debug] ok.
Wed May 20 15:35:56 2020 - [debug] Connecting via SSH from root@10.0.0.42(10.0.0.42:22) to root@10.0.0.43(10.0.0.43:22)..
Wed May 20 15:35:56 2020 - [debug] ok.
Wed May 20 15:35:57 2020 - [debug]
Wed May 20 15:35:56 2020 - [debug] Connecting via SSH from root@10.0.0.43(10.0.0.43:22) to root@10.0.0.41(10.0.0.41:22)..
Wed May 20 15:35:56 2020 - [debug] ok.
Wed May 20 15:35:56 2020 - [debug] Connecting via SSH from root@10.0.0.43(10.0.0.43:22) to root@10.0.0.42(10.0.0.42:22)..
Wed May 20 15:35:57 2020 - [debug] ok.
Wed May 20 15:35:57 2020 - [info] All SSH connection tests passed successfully.
[root@ c704 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
Wed May 20 15:36:15 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 20 15:36:15 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed May 20 15:36:15 2020 - [info] Reading server configuration from /etc/mha/app1.cnf..
Wed May 20 15:36:15 2020 - [info] MHA::MasterMonitor version 0.58.
Wed May 20 15:36:16 2020 - [info] GTID failover mode = 1
Wed May 20 15:36:16 2020 - [info] Dead Servers:
Wed May 20 15:36:16 2020 - [info] Alive Servers:
Wed May 20 15:36:16 2020 - [info] 10.0.0.41(10.0.0.41:3306)
Wed May 20 15:36:16 2020 - [info] 10.0.0.42(10.0.0.42:3306)
Wed May 20 15:36:16 2020 - [info] 10.0.0.43(10.0.0.43:3306)
Wed May 20 15:36:16 2020 - [info] Alive Slaves:
Wed May 20 15:36:16 2020 - [info] 10.0.0.42(10.0.0.42:3306) Version=5.6.48-log (oldest major version between slaves) log-bin:enabled
Wed May 20 15:36:16 2020 - [info] GTID ON
Wed May 20 15:36:16 2020 - [info] Replicating from 10.0.0.41(10.0.0.41:3306)
Wed May 20 15:36:16 2020 - [info] 10.0.0.43(10.0.0.43:3306) Version=5.6.48-log (oldest major version between slaves) log-bin:enabled
Wed May 20 15:36:16 2020 - [info] GTID ON
Wed May 20 15:36:16 2020 - [info] Replicating from 10.0.0.41(10.0.0.41:3306)
Wed May 20 15:36:16 2020 - [info] Not candidate for the new Master (no_master is set)
Wed May 20 15:36:16 2020 - [info] Current Alive Master: 10.0.0.41(10.0.0.41:3306)
Wed May 20 15:36:16 2020 - [info] Checking slave configurations..
Wed May 20 15:36:16 2020 - [info] read_only=1 is not set on slave 10.0.0.42(10.0.0.42:3306).
Wed May 20 15:36:16 2020 - [info] read_only=1 is not set on slave 10.0.0.43(10.0.0.43:3306).
Wed May 20 15:36:16 2020 - [info] Checking replication filtering settings..
Wed May 20 15:36:16 2020 - [info] binlog_do_db= , binlog_ignore_db=
Wed May 20 15:36:16 2020 - [info] Replication filtering check ok.
Wed May 20 15:36:16 2020 - [info] GTID (with auto-pos) is supported. Skipping all SSHand Node package checking.
Wed May 20 15:36:16 2020 - [info] Checking SSH publickey authentication settings on the current master..
Wed May 20 15:36:16 2020 - [info] HealthCheck: SSH to 10.0.0.41 is reachable.
Wed May 20 15:36:16 2020 - [info]
10.0.0.41(10.0.0.41:3306) (current master)
+--10.0.0.42(10.0.0.42:3306)
+--10.0.0.43(10.0.0.43:3306)
Wed May 20 15:36:16 2020 - [info] Checking replication health on 10.0.0.42..
Wed May 20 15:36:16 2020 - [info] ok.
Wed May 20 15:36:16 2020 - [info] Checking replication health on 10.0.0.43..
Wed May 20 15:36:16 2020 - [info] ok.
Wed May 20 15:36:16 2020 - [warning] master_ip_failover_script is not defined.
Wed May 20 15:36:16 2020 - [warning] shutdown_script is not defined.
Wed May 20 15:36:16 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
如果报错（10.0.0.43）
[root@ c704 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
Wed May 20 15:27:26 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed May 20 15:27:26 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed May 20 15:27:26 2020 - [info] Reading server configuration from /etc/mha/app1.cnf..
Wed May 20 15:27:26 2020 - [info] MHA::MasterMonitor version 0.58.
Wed May 20 15:27:26 2020 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180]Got MySQL error when connecting 10.0.0.42(10.0.0.42:3306) :1045:Access denied for user 'mha'@'slave1' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
Wed May 20 15:27:26 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm,ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Wed May 20 15:27:27 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm,ln309] Got fatal error, stopping operations
Wed May 20 15:27:27 2020 - [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.
Wed May 20 15:27:27 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm,ln525] Error happened on monitoring servers.
Wed May 20 15:27:27 2020 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
这个报错
在所有的机器上添加
vim /etc/my.cnf
skip-name-resolve
systemctl restart mysql
mysql -uroot -p123456
show slave status \G;
看一下是否有双yes
启动MHA（10.0.0.44）
[root@ c704 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[1] 8069
[root@ c704 ~]# ps -ef | grep mha
root 8069 7320 2 15:52 pts/0 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover
root 8080 7320 20 15:53 pts/0 00:00:00 grep --color=auto mha
查MHA状态（10.0.0.44）
[root@ c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:8069) is running(0:PING_OK), master:10.0.0.41
关闭MHA（10.0.0.44）
[root@ c704 ~]# masterha_stop --conf=/etc/mha/app1.cnf
从库重新加入新主（10.0.0.44）
[root@ c704 ~]# grep -i "CHANGE MASTER TO MASTER" /var/log/mha/app1/manager.log | tail -1
测试MHA故障转移（在master10.0.0.41）
停掉master主库10.0.0.41
[root@ c7m01 ~]# systemctl stop mysql
查看c703的状态，发现master_host变成了10.0.0.42
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.42
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1797
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1589
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
查看slave1的状态，变成了master（slave2 10.0.0.0.42）
[root@c702 ~]# mysql -uroot -p123456 -e "show master status;"
Warning: Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| mysql-bin.000001 | 190 |
查看c704的配置文件
发现这里少了server1
[root@c704 ~]# cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
password=mha
ping_interval=2
repl_password=123456
repl_user=rep
ssh_user=root
user=mha
#少了server1
[server2]
hostname=10.0.0.42
port=3306
[server3]
hostname=10.0.0.43
ignore_fail=1
no_master=1
port=3306
MHA故障还原
首先将宕掉mysql修复，然后加入到mysql一主两从集群
[root@c704 ~]# grep "CHANGE MASTER TO MASTER" /var/log/mha/app1/manager.log | tail -1
Thu May 21 19:05:05 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.42', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
[root@c7m01 ~]# systemctl restart mysql
[root@c7m01 ~]# mysql -uroot -p123456 -e "CHANGE MASTER TO MASTER_HOST='10.0.0.42', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123456';"
[root@c7m01 ~]# mysql -uroot -p123456 -e "start slave;"
[root@c7m01 ~]# mysql -uroot -p123456 -e "show slave status \G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.42
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1797
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 448
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
重新在将[server1]标签添加到MHA配置文件，并且启动MHA
[root@c704 ~]# vim /etc/mha/app1.cnf
[server1]
hostname=10.0.0.41
port=3306
[root@c704 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[1] 7092
#查看状态
[root@c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:7092) is running(0:PING_OK), master:10.0.0.42
配置vip漂移
漂移的两种方式
MHA脚本方式
修改MHA配置文件
[root@ c704 ~]# vim /etc/mha/app1.cnf
[server default]
master_ip_failover_script=/usr/bin/master_ip_failover
##添加到第二行就行了
编写漂移脚本
[root@ c7-44 ~]# vim /usr/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '10.0.0.49/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
[root@ c704 ~]# chmod +x /usr/bin/master_ip_failover
[root@ c704 ~]# ll /usr/bin/master_ip_failover
-rwxr-xr-x 1 root root 2170 May 22 15:50 /usr/bin/master_ip_failover
手动绑定vip
[root@ c704 ~]# ifconfig ens33:1 10.0.0.49/24
[root@ c704 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:5c:02:6a brd ff:ff:ff:ff:ff:ff
inet 10.0.0.44/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet 10.0.0.49/24 brd 10.0.0.255 scope global secondary ens33:1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe5c:26a/64 scope link
valid_lft forever preferred_lft forever
重启mha
[root@ c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:3139) is running(0:PING_OK), master:10.0.0.41
[root@ c704 ~]# masterha_stop --conf=/etc/mha/app1.cnf
Stopped app1 successfully.
[1]+ Exit 1 nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1
[root@ c704 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[1] 4163
[root@ c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:4163) is running(0:PING_OK), master:10.0.0.41
模拟主库宕机vip漂移
关闭c702的主库mysql
[root@c702 ~]# systemctl stop mysql
[root@c702 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:cd:14:44 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.42/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fecd:1444/64 scope link
valid_lft forever preferred_lft forever
查看c7m01上master状态和vip
[root@c7m01 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:de:70:3c brd ff:ff:ff:ff:ff:ff
inet 10.0.0.41/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet 10.0.0.49/24 brd 10.0.0.255 scope global secondary ens33:1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fede:703c/64 scope link
valid_lft forever preferred_lft forever
[root@c7m01 ~]# mysql -uroot -p123456 -e 'show master status\G'
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1325
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 5ab78bda-9b49-11ea-9cc9-000c29de703c:1-7,
75b3a4ef-9b49-11ea-9cca-000c29cd1444:1-6
#故障还原
Mysql之Atlas(读写分离)
数据库中间件Atlas与Mycat比较分库分表压测报告https://blog.csdn.net/izhitao/article/details/71680714
Atlas是由Qihoo 360公司Web平台部基础架构团队开发维护的一-个基于MySQL协议的数据中间层项目。它在MySQL言
方推出的MySQL-Proxy 0.8.2版本的基础上,修改了大量bug ,添加了很多功能特性。目前该项目在360公司内部得到了广
泛应用,很多MySQL业务已经接入了Atlas平台,每天承载的读写请求数达几十亿条。
源码Github : https://github.com/Qihoo360/Atlas
什么是读写分离
读写分离,基本的原理是让主数据库处理事务性增、删、改操作( INSERT. DELETE、 UPDATE ) , 而从数据库处理
SELECT查询操作。数据库复制把主数据库操作的变更同步到集群中的从数据库。
为什么读写分离
因为数据库的“写" (写10000条数据到oracle可能要3分钟)操作是比较耗时的。但是数据库的“读" (从oracle读10000
条数据可能只要5秒钟)。所以读写分离,解决的是，数据库的写入，影响了查询的效率。
什么时候要读写分离
数据库不一定要读写分离,如果程序使用数据库较多时,而更新少,查询多的情况下会考虑使用,利用数据库主从同步
可以减少数据库压力,提高性能。当然,数据库也有其它优化方案。memcache 或是表折分,或是搜索引学。都是解
决方法。
Atlas的功用与应用场景
Atlas的功能有:
读写分离、从库负载均衡、自动分表、IP过滤、 SQL语句黑白名单、DBA可平滑上下线DB、自动摘除宕机的DB。
Atlas的使用场景:
Atlas是一个位于前端 应用与后端MySQL数据库之间的中间件,它使得应用程序员无需再关心读写分离、分表等与
MySQL相关的细节,可以专注于编写业务逻辑,同时使得DBA的运维工作对前端应用透明，上下线DB前端应用无感知。
Atlas的安装过程
注意:
1.Atlas只能安装运行在64位的系统上
2.Centos 5.X安装Atlas-XX.el5.x86_ _64.rpm , Centos 6.X安装Atlas-XX.el6.x86 _64.rpm(经过测试centos7也可以使用6
的版本)
3、后端mysq|版本应大于5.1 ,建议使用Mysql 5.6以上=
###安装altas
[root@c704 ~]# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
[root@c704 ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
###修改配置文件
[root@c704 ~]# cp /usr/local/mysql-proxy/conf/test.cnf{,.bak}
[root@c704 ~]# egrep -v '^#|^$' /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.49:3306
proxy-read-only-backend-addresses = 10.0.0.42:3306,10.0.0.43:3306
pwds = rep:/iZxz+0GRoA=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log =ON
proxy-address = 0.0.0.0:1234
admin-address = 0.0.0.0:2345
charset = utf8
###加密方式 /usr/local/mysql-proxy/bin/encrypt 密码
[root@c704 ~]# /usr/local/mysql-proxy/bin/encrypt 123456
/iZxz+0GRoA=
[root@c704 ~]# /usr/local/mysql-proxy/bin/encrypt mha
O2jBXONX098=
###启动atlas
[root@c704 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
###查看是否启动atlas成功
[root@c704 ~]# ps -ef | grep mysql-proxy
root 9097 1 0 20:20 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 9098 9097 0 20:20 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 9364 8350 0 20:28 pts/0 00:00:00 grep --color=auto mysql-proxy
Atlas读写分离测试
读测试
[root@c704 ~]# mysql -umha -pmha -P1234 -h10.0.0.44
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
解释说明:发现上面’server. id每次的结果都不-样，分别是2台从库的server. id ,并且每执行- -次命令 , server. id就会变换一
次，这是因为默认读操作的权重都是1，两台从DB默认就是负载均衡。
写测试
[root@c704 ~]# mysql -umha -pmha -P1234 -h10.0.0.44
mysql> begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> create database www;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wg |
| www |
+--------------------+
5 rows in set (0.00 sec)
Altas管理操作
登录管理接口
[root@c704 ~]# mysql -uuser -ppwd -P2345 -h 10.0.0.44
查看帮助信息
mysql> select * from help;
+----------------------------+---------------------------------------------------------+
| command | description |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
| SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id | online backend server, ... |
| ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... |
| ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |
| SELECT * FROM clients | lists the clients |
| ADD CLIENT $client | example: "add client 192.168.1.2", ... |
| REMOVE CLIENT $client | example: "remove client 192.168.1.2", ... |
| SELECT * FROM pwds | lists the pwds |
| ADD PWD $pwd | example: "add pwd user:raw_password", ... |
| ADD ENPWD $pwd | example: "add enpwd user:encrypted_password", ... |
| REMOVE PWD $pwd | example: "remove pwd user", ... |
| SAVE CONFIG | save the backends to config file |
| SELECT VERSION | display the version of Atlas |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)
补充说明（自己的理解）：
第一条：查看帮助信息
第二条：查看后端信息
第三条：设置脱机$后端id
第四条：设置联机$后端id
第五条：添加MASTER$后端
第六条：添加从$backend
第七条：删除后端 id
第八条：从客户端选择
第九条：添加客户端
第十条：删除客户端
第十一条：从pwds中选择
第十二条：加上PWD
第十三条：删除PWD
第十四条：保存配置
第十五条：选择版本
查看后端的代理库
mysql> select * from backends;
+-------------+----------------+-------+------+
| backend_ndx | address | state | type |
+-------------+----------------+-------+------+
| 1 | 10.0.0.49:3306 | up | rw |
| 2 | 10.0.0.42:3306 | up | ro |
| 3 | 10.0.0.43:3306 | up | ro |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)
下线后端节点
mysql> set offline 3;
+-------------+----------------+---------+------+
| backend_ndx | address | state | type |
+-------------+----------------+---------+------+
| 3 | 10.0.0.43:3306 | offline | ro |
+-------------+----------------+---------+------+
1 row in set (0.00 sec)
mysql> select * from backends;
+-------------+----------------+---------+------+
| backend_ndx | address | state | type |
+-------------+----------------+---------+------+
| 1 | 10.0.0.49:3306 | up | rw |
| 2 | 10.0.0.42:3306 | up | ro |
| 3 | 10.0.0.43:3306 | offline | ro |
+-------------+----------------+---------+------+
上线后端节点
mysql> set off online 3;
ERROR 1105 (07000): use 'SELECT * FROM help' to see the supported commands
mysql> set online 3;
+-------------+----------------+---------+------+
| backend_ndx | address | state | type |
+-------------+----------------+---------+------+
| 3 | 10.0.0.43:3306 | unknown | ro |
+-------------+----------------+---------+------+
1 row in set (0.00 sec)
mysql> select * from backends;
+-------------+----------------+---------+------+
| backend_ndx | address | state | type |
+-------------+----------------+---------+------+
| 1 | 10.0.0.49:3306 | up | rw |
| 2 | 10.0.0.42:3306 | up | ro |
| 3 | 10.0.0.43:3306 | unknown | ro |
+-------------+----------------+---------+------+
3 rows in set (0.00 sec)