MySQL高可用架构MHA
MHA：是一款开源的MySQL的高可用程序，它为MySQL主从复制提供了automating master failover 功能，MHA在监控到master节点故障时，会提升其中拥有最新数据的slave节点成为新的master节点，在此期间，MHA 会用过与其他节点获取额外信息来避免一致性方面的问题，MHA还提供了master节点的在线切换功能，即按需切换master/slave节点。
工作原理：
- 从宕机崩溃的master保存二进制日志事件（binlog events）
- 识别最新更新的slave
- 应用差异的中继日志（relay log）到其他slave
- 应用从master保存的二进制日志事件
- 提升一个slave为新master
- 使用其他的slave连接新的master进行复制
注意：MHA需要基于ssh-key验证登入方法
实验环境
10.0.2.7 MHA manager
10.0.2.8 master
10.0.2.18 slave1
10.0.2.28 slave2
所需安装包
MHA manager和node的rpm包网址：
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads（需要科学上网）
实验过程
先在master，slave1，slave2主机上配置主从复制
master
[root@master ~]#yum install mariadb-server -y
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin=/data/logbin/mysql-bin
skip-name-resolve
[root@master ~]#mkdir /data/logbin/
[root@master ~]#chown -R mysql.mysql /data/logbin/
[root@master ~]#systemctl resrart mariadb
[root@master ~]#mysql
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 28196 |
| mysql-bin.000002 | 540 |
+------------------+-----------+
2 rows in set (0.000 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.2.%' identified by 'xiurong';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> grant all on *.* to mha@'10.0.2.%' identified by 'xiurong';
#安装MHA node 的rpm包
[root@master ~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
slave1
[root@slave1 ~]#yum install mariadb-server -y
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only
log-bin=/data/logbin/mysql-bin
relay_log_purge=0
skip_name_resolve
[root@slave1 ~]#mkdir /data/logbin/
[root@slave1 ~]#chown -R mysql.mysql /data/logbin/
[root@slave1 ~]#systemctl restart mariadb
[root@slave1 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.0.2.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='xiurong',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=540;
Query OK, 0 rows affected (0.010 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.2.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 342
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 641
Relay_Master_Log_File: mysql-bin.000004
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: 342
Relay_Log_Space: 2234
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: 8
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
#安装MHA node 的rpm包
[root@slave1 ~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
slave2
[root@slave2 ~]#yum install mariadb-server -y
[root@slave2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=28
read-only
log-bin=/data/logbin/mysql-bin
relay_log_purge=0
skip_name_resolve
[root@slave2 ~]#mkdir /data/logbin/
[root@slave2 ~]#chown -R mysql.mysql /data/logbin/
[root@slave2 ~]#systemctl start mariadb
[root@slave2 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.0.2.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='xiurong',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=540;
Query OK, 0 rows affected (0.011 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.2.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 342
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 641
Relay_Master_Log_File: mysql-bin.000004
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: 342
Relay_Log_Space: 2234
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: 8
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
#安装MHA node 的rpm包
[root@slave2 ~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
MHA manager
1、准备ssh-key互通环境，任意主机之间可直接互连
[root@manager ~]#ssh-keygen -t rsa
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:
SHA256:wKTTzcXhYDHPFsMkPMrnClpeRdJZ4fPg/hcWp4Wf/UM root@manager
The key's randomart image is:
+---[RSA 2048]----+
| .o*BOo |
| =.+*Xoo |
| o.++o.O . |
| .o.oo + o o|
| +S . . *o|
| o . .. +Eo|
| + o . . ....|
| . . . . ...|
| .. .|
+----[SHA256]-----+
'
[root@manager ~]#ssh-copy-id -i .ssh/id_rsa.pub root@10.0.2.7
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: ".ssh/id_rsa.pub"
The authenticity of host '10.0.2.7 (10.0.2.7)' can't be established.
ECDSA key fingerprint is SHA256:65LdRdECf9xeZRidZUXy3MkeFOYxECOlJ9nVKwSlPcs.
ECDSA key fingerprint is MD5:44:1d:13:dd:cc:1e:45:c3:4e:99:49:dc:b7:c1:6a:df.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@10.0.2.7's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'root@10.0.2.7'"
and check to make sure that only the key(s) you wanted were added.
[root@manager ~]#ls .ssh/
authorized_keys id_rsa id_rsa.pub known_hosts
[root@manager ~]#scp -r .ssh 10.0.2.8:/root/
[root@manager ~]#scp -r .ssh 10.0.2.18:/root/
[root@manager ~]#scp -r .ssh 10.0.2.28:/root/
#最终实现ssh免密钥登录
[root@manager ~]#ssh 10.0.2.8
Last login: Tue Mar 3 19:38:15 2020 from 10.0.2.1
[root@master ~]#exit
logout
Connection to 10.0.2.8 closed.
[root@manager ~]#
[root@manager ~]#
[root@manager ~]#ssh 10.0.2.28
Last login: Tue Mar 3 20:31:49 2020 from 10.0.2.1
[root@slave2 ~]#
[root@manager ~]#ssh 10.0.2.18
Last login: Tue Mar 3 20:31:37 2020 from 10.0.2.1
[root@slave1 ~]#
2、安装
#安装manager和node的rpm包
[root@MHA ~]#yum install mha4mysql-manager-0.56-0.el5.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm -y
[root@MHA ~]#mkdir /etc/mastermha/
[root@MHA ~]#vim /etc/mastermha/app1.cnf
[root@MHA ~]#cat /etc/mastermha/app1.cnf
[server default]
user=mha
password=xiurong
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
master_binlog_dir=/data/logbin
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=xiurong
ping_interval=1
[server1]
hostname=10.0.2.8
candidate_master=1
[server2]
hostname=10.0.2.18
candidate_master=1 #指定18为master宕机后成为新的主
[server3]
hostname=10.0.2.28
3、验证和启动
#ssh-key的工作是否正常
[root@MHA ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
Wed Mar 4 22:24:08 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Mar 4 22:24:08 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Wed Mar 4 22:24:08 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Wed Mar 4 22:24:08 2020 - [info] Starting SSH connection tests..
Wed Mar 4 22:24:10 2020 - [debug]
Wed Mar 4 22:24:08 2020 - [debug] Connecting via SSH from root@10.0.2.8(10.0.2.8:22) to root@10.0.2.18(10.0.2.18:22)..
Wed Mar 4 22:24:09 2020 - [debug] ok.
Wed Mar 4 22:24:09 2020 - [debug] Connecting via SSH from root@10.0.2.8(10.0.2.8:22) to root@10.0.2.28(10.0.2.28:22)..
Wed Mar 4 22:24:09 2020 - [debug] ok.
Wed Mar 4 22:24:10 2020 - [debug]
Wed Mar 4 22:24:09 2020 - [debug] Connecting via SSH from root@10.0.2.18(10.0.2.18:22) to root@10.0.2.8(10.0.2.8:22)..
Wed Mar 4 22:24:09 2020 - [debug] ok.
Wed Mar 4 22:24:09 2020 - [debug] Connecting via SSH from root@10.0.2.18(10.0.2.18:22) to root@10.0.2.28(10.0.2.28:22)..
Wed Mar 4 22:24:09 2020 - [debug] ok.
Wed Mar 4 22:24:11 2020 - [debug]
Wed Mar 4 22:24:09 2020 - [debug] Connecting via SSH from root@10.0.2.28(10.0.2.28:22) to root@10.0.2.8(10.0.2.8:22)..
Wed Mar 4 22:24:10 2020 - [debug] ok.
Wed Mar 4 22:24:10 2020 - [debug] Connecting via SSH from root@10.0.2.28(10.0.2.28:22) to root@10.0.2.18(10.0.2.18:22)..
Wed Mar 4 22:24:10 2020 - [debug] ok.
Wed Mar 4 22:24:11 2020 - [info] All SSH connection tests passed successfully.
#检查主从复制是否出错
[root@MHA ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf
Thu Mar 5 01:17:30 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Mar 5 01:17:30 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Thu Mar 5 01:17:30 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Thu Mar 5 01:17:30 2020 - [info] MHA::MasterMonitor version 0.56.
Thu Mar 5 01:17:31 2020 - [info] GTID failover mode = 0
Thu Mar 5 01:17:31 2020 - [info] Dead Servers:
Thu Mar 5 01:17:31 2020 - [info] Alive Servers:
Thu Mar 5 01:17:31 2020 - [info] 10.0.2.8(10.0.2.8:3306)
Thu Mar 5 01:17:31 2020 - [info] 10.0.2.18(10.0.2.18:3306)
Thu Mar 5 01:17:31 2020 - [info] 10.0.2.28(10.0.2.28:3306)
Thu Mar 5 01:17:31 2020 - [info] Alive Slaves:
Thu Mar 5 01:17:31 2020 - [info] 10.0.2.18(10.0.2.18:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled
Thu Mar 5 01:17:31 2020 - [info] Replicating from 10.0.2.8(10.0.2.8:3306)
Thu Mar 5 01:17:31 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Mar 5 01:17:31 2020 - [info] 10.0.2.28(10.0.2.28:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled
Thu Mar 5 01:17:31 2020 - [info] Replicating from 10.0.2.8(10.0.2.8:3306)
Thu Mar 5 01:17:31 2020 - [info] Current Alive Master: 10.0.2.8(10.0.2.8:3306)
Thu Mar 5 01:17:31 2020 - [info] Checking slave configurations..
Thu Mar 5 01:17:31 2020 - [info] Checking replication filtering settings..
Thu Mar 5 01:17:31 2020 - [info] binlog_do_db= , binlog_ignore_db=
Thu Mar 5 01:17:31 2020 - [info] Replication filtering check ok.
Thu Mar 5 01:17:31 2020 - [info] GTID (with auto-pos) is not supported
Thu Mar 5 01:17:31 2020 - [info] Starting SSH connection tests..
Thu Mar 5 01:17:34 2020 - [info] All SSH connection tests passed successfully.
Thu Mar 5 01:17:34 2020 - [info] Checking MHA Node version..
Thu Mar 5 01:17:34 2020 - [info] Version check ok.
Thu Mar 5 01:17:34 2020 - [info] Checking SSH publickey authentication settings on the current master..
Thu Mar 5 01:17:35 2020 - [info] HealthCheck: SSH to 10.0.2.8 is reachable.
Thu Mar 5 01:17:35 2020 - [info] Master MHA Node version is 0.56.
Thu Mar 5 01:17:35 2020 - [info] Checking recovery script configurations on 10.0.2.8(10.0.2.8:3306)..
Thu Mar 5 01:17:35 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/logbin --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000002
Thu Mar 5 01:17:35 2020 - [info] Connecting to root@10.0.2.8(10.0.2.8:22)..
Creating /data/mastermha/app1 if not exists.. Creating directory /data/mastermha/app1.. done.
ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/logbin, up to mysql-bin.000002
Thu Mar 5 01:17:35 2020 - [info] Binlog setting check done.
Thu Mar 5 01:17:35 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Mar 5 01:17:35 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=10.0.2.18 --slave_ip=10.0.2.18 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.3.17-MariaDB-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Mar 5 01:17:35 2020 - [info] Connecting to root@10.0.2.18(10.0.2.18:22)..
Creating directory /data/mastermha/app1/.. done.
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000002
Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Mar 5 01:17:35 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=10.0.2.28 --slave_ip=10.0.2.28 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.3.17-MariaDB-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu Mar 5 01:17:35 2020 - [info] Connecting to root@10.0.2.28(10.0.2.28:22)..
Creating directory /data/mastermha/app1/.. done.
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000002
Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Mar 5 01:17:36 2020 - [info] Slaves settings check done.
Thu Mar 5 01:17:36 2020 - [info]
10.0.2.8(10.0.2.8:3306) (current master)
+--10.0.2.18(10.0.2.18:3306)
+--10.0.2.28(10.0.2.28:3306)
Thu Mar 5 01:17:36 2020 - [info] Checking replication health on 10.0.2.18..
Thu Mar 5 01:17:36 2020 - [info] ok.
Thu Mar 5 01:17:36 2020 - [info] Checking replication health on 10.0.2.28..
Thu Mar 5 01:17:36 2020 - [info] ok.
Thu Mar 5 01:17:36 2020 - [warning] master_ip_failover_script is not defined.
Thu Mar 5 01:17:36 2020 - [warning] shutdown_script is not defined.
Thu Mar 5 01:17:36 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
[root@MHA ~]#masterha_manager --conf=/etc/mastermha/app1.cnf
Wed Mar 4 22:24:34 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Mar 4 22:24:34 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Wed Mar 4 22:24:34 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
4、当master宕机，MHA manager自动启用一个从节点成为新的主。上面的MHA配置文件就定义了如果master宕机，提升哪个slave主机为新的master。
#manage日志
[root@centos7 ~]#tail -f /data/mastermha/app1/manager.log
注意！！！
- 新的master 配置文件里还是read-only，虽然在新的master查看变量时manager自动将read_only修改为了OFF,但是当主机重新启动，里边的变量还是会和配置文件一致！，所以在提升为新的master后，要将配置文件里的read-only注释掉。
- 那么当旧的master机器起来了以后，它会不会继续成为之前slave的主呢，答案自然是不会的~它还是主，但是只是自己的主，如果想要其恢复之前的所有从节点，就得把新的master降为slave，并且每个slave得重新指向旧的master。
2483
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
