mysql高可用
server1：master
server2：备用master
server3:slave
server4：MHA manager
先配置server mysql主从复制
一、数据库配置
1、server1：master
[[email protected] mysql]# mysql -pwestos
mysql> grant replication slave on *.* to [email protected]'172.25.77%' identified by 'westos';
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 | 1006 | | | 2a0a9397-83ea-11e8-b141-52540091ccfd:1-4 |
+---------------+----------+--------------+------------------+------------------------------------------+
2、server2、3：slave
[[email protected] ~]# mysql -pwestos
mysql> change master to master_host='172.25.77.1',master_user='natasha',master_password='westos',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G'
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.77.1
Master_User: natasha
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1006
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 1213
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
##server4同server3即可，需测试主从复制ok
二、软件安装
1、3 个节点上安装
mha4mysql-node-0.56-0.el6.noarch.rpm
perl-DBD-MySQL.x86_64
2、管理节点上安装
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
三、配置 4 台主机 ssh 免密链接
##注意:安装 openssh-clients-5.3p1-94.el6.x86_64
1、server1、2、3节点操作相似,以 server2 主机为例
[[email protected] ~]# ssh-****** -t rsa ##Enter 即可,选择默认方式
[[email protected] ~]# ssh-copy-id -i .ssh/id_rsa.pub [email protected]
[[email protected]~]# ssh-copy-id -i .ssh/id_rsa.pub [email protected]
##server2 主机 copy **至 server1、3 主机;server3 主机 copy **
至 server1、2 主机
2、server4 主机:管理节点
[[email protected] ~]# ssh-****** -t rsa
[[email protected]~]# ssh-copy-id -i .ssh/id_rsa.pub [email protected]
[[email protected] ~]# ssh-copy-id -i .ssh/id_rsa.pub [email protected]
[[email protected] ~]# ssh-copy-id -i .ssh/id_rsa.pub [email protected]
3、此时测试免密链接
##server1、1、3 主机可互相免密登陆,server4 主机免密登陆其他主机
四、MHA配置
1、创建 MHA 的工作目录,并做相应配置
[[email protected] masterha]# pwd
/etc/masterha
[[email protected] masterha]# vim app.cnf
[server default]
manager_log=/etc/masterha/mha.log
manager_workdir=/etc/masterha/
master_binlog_dir=/var/lib/mysql
#master_ip_online_change_script=/etc/masterha/master_ip_online_change
password=westos
ping_interval=1
remote_workdir=/tmp
repl_password=westos
repl_user=repl
ssh_user=root
user=root
[server1]
hostname=172.25.77.1
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=172.25.77.2
port=3306
[server3]
hostname=172.25.77.3
port=3306
2、每个 slave 节点设置 relay log 的清除方式
mysql> set global relay_log_purge=0;
Query OK, 0 rows affected (0.00 sec)
3、检测ssh配置
[[email protected] ~]# masterha_check_ssh --conf=/etc/masterha/app.cnf
Tue Jul 10 10:54:59 2018 - [info] All SSH connection tests passed successfully.
4、检测repl环境
[[email protected]]# masterha_check_repl --conf=/etc/masterha/app.cnf
MySQL Replication Health is OK.
五、测试
1、开启MHA后台监控
[[email protected]ver4 masterha]# nohup masterha_manager --conf=/etc/masterha/app.cnf &
[1] 1112
[[email protected] masterha]# nohup: ignoring input and appending output to `nohup.out'
[[email protected] masterha]#
2、server1：master down
[[email protected] mysql]# kill -9 2199
[[email protected] mysql]# kill -9 2470
##此时 MHA manager 自动生成 app.failover.complete 文件
[[email protected] masterha]# ls
app.cnf master_ip_failover mha.log
app.failover.complete master_ip_online_change nohup.out
3、master down后server2自动接管
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| binlog.000002 | 2017 | | | 2a0a9397-83ea-11e8-b141-52540091ccfd:1-6,
a5ddab64-83ea-11e8-9661-5254008d996b:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show slave status\G;
Empty set (0.00 sec)
ERROR:
No query specified
4、server4：master指向server2
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.77.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 2017
Relay_Log_File: server3-relay-bin.000002
Relay_Log_Pos: 961
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
六、MHA手动切换
1、server1：mysql 开启（此时3为master，状态alive）
[[email protected] ~]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
mysql> change master to master_host='172.25.77.2',master_user='natasha',master_password='westos',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.95 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.77.2