Mysql MHA的搭建
使用环境：实验环境两从一主
四台centos 6.7
mha manager 192.168.2.32
master 192.168.2.36
slave 192.168.2.38
slave 192.168.2.40
MAH用的用到的软件版本号是： （注意：装这两个包的时候会有很多依赖包的，需要提前把epel源装上，然后用yum 来安装）
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo阿里云的epel源
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
链接：https://pan.baidu.com/s/1nvta8Ut 密码：fv3b
MAH：
HA是一位日本MySQL大牛用Perl写的一套MySQL故障切换方案，来保证数据库系统的高可用.在宕机的时间内（通常10—30秒内），完成故障切换，部署MHA，可避免主从一致性问题，节约购买新服务器的费用，不影响服务器性能，易安装，不改变现有部署。
先在master 和slave 做好主从，最好在其中一台slave上做半同步！
在实验环境里面配置之前记得一定要把防火墙和selinux先关闭!
如果在真实环境中在iptables里把相对应的端口开启，否则因为这个导致你实验不成功！
master 配置
编辑/etc/my.cnf
server_id = 1
log_bin = master-bin
relay_log = realy-bin
配置好之后重启mysqld服务
# service mysqld restart
在master上安装半同步
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
开启半同步模式；
mysql> set global rpl_semi_sync_master_enabled=1;
查看是否开启：
mysql> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
查看状态
mysql> show global statuslike '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
创建主从复制用到的帐号，及MHA管理用到的帐号密码
mysql> grant replication slave, replication client on *.* to 'repluser'@'192.168.2.%'>
mysql> grant all on *.* to 'mhauser'@'192.168.2.%'> Slave 配置（192.168.2.38）
在/etc/my.cnf
server_id = 2
relay_log => read_only = 1
relay_log_purge = 0
log_bin = master-bin
重启mysqld服务
# service mysqld restart
Shutting down MySQL.
Starting MySQL..
配置半同步slave端：
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
开启半同步：
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
连接master端的配置
mysql> change master to master_host='192.168.2.36',master_user='repluser',master_password='123456',master_log_file='master-bin.000001',master_log_pos=107;
启动以及查看是否正常工作
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.36
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 499
Relay_Log_File:>
>
> 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: 499
> 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
1 row in set (0.00 sec)
在master上查看半同步是否已经接受到客户端了
mysql> show global statuslike '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
第二台slave配置主从复制；（192.168.2.40）
在/etc/my.cnf
server_id = 2
relay_log => read_only = 1
relay_log_purge = 0
log_bin = master-bin
连接master端
mysql> change master to master_host='192.168.2.36',master_user='repluser',master_password='123456',master_log_file='master- bin.000001',master_log_pos=107;
查看连接信息：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.36
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 499
Relay_Log_File:>
>
> Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
在MAH manager端启用ssh無密钥连接各台服务器；
# ssh-keygen -t rsa -P ''
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Your> Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
87:fb:6d:2e:f8:f7:72:5d:28:41:01:54:f4:48:7c:12 root@xiong.cn
The key's randomart image is:
+--[ RSA 2048]----+
| .o=Eo |
| .+o.|
| ..o.|
| .. |
| S .. . |
| o. . .|
| .. . ..|
| ....+ . .|
| .o=o+.|
+-----------------+
本机也要一份密钥
# cat .ssh/id_rsa.pub > .ssh/authorized_keys
修改.ssh/authorized_keys 为600权限
chmod go= .ssh/authorized_keys
把密钥复制给其它几个节点
# scp -p.ssh/id_rsa .ssh/authorized_keys root@192.168.2.36:/root/.ssh
# scp -p.ssh/id_rsa .ssh/authorized_keys root@192.168.2.38:/root/.ssh
# scp -p.ssh/id_rsa .ssh/authorized_keys root@192.168.2.40:/root/.ssh
然后验证每个节点是否能正常登录
# ssh root@192.168.2.36 'ifconfig eth0'
eth0 Link encap:EthernetHWaddr 00:0C:29:16:44:84
inet addr:192.168.2.39Bcast:192.168.2.255Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe16:4484/64 Scope:Link
UP BROADCAST RUNNING MULTICASTMTU:1500Metric:1
# ssh root@192.168.2.38 'ifconfig eth0'
eth0 Link encap:EthernetHWaddr 00:0C:29:16:44:84
inet addr:192.168.2.38Bcast:192.168.2.255Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe16:4484/64 Scope:Link
UP BROADCAST RUNNING MULTICASTMTU:1500Metric:1
# ssh root@192.168.2.40 'ifconfig eth0'
eth0 Link encap:EthernetHWaddr 00:0C:29:16:44:84
inet addr:192.168.2.40 Bcast:192.168.2.255Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe16:4484/64 Scope:Link
UP BROADCAST RUNNING MULTICASTMTU:1500Metric:1
192.168.2.32MAH manager ：
上传mha4mysql-manager和mha4mysql-node包之前说过 装这两个会有依赖包的而且都在epel源里面
]# yum -y install mha4mysql-*
已安装:
mha4mysql-manager.noarch 0:0.56-0.el6 mha4mysql-node.noarch 0:0.56-0.el6
作为依赖被安装:
perl-Config-Tiny.noarch 0:2.12-7.1.el6 perl-DBD-MySQL.x86_64 0:4.013-3.el6 perl-Email-Date-Format.noarch 0:1.002-5.el6 perl-Log-Dispatch.noarch 0:2.27-1.el6
perl-MIME-Lite.noarch 0:3.027-2.el6 perl-MIME-Types.noarch 0:1.28-2.el6 perl-Mail-Sender.noarch 0:0.8.16-3.el6 perl-Mail-Sendmail.noarch 0:0.79-12.el6
perl-MailTools.noarch 0:2.04-4.el6 perl-Parallel-ForkManager.noarch 0:0.7.9-1.el6 perl-Params-Validate.x86_64 0:0.92-3.el6 perl-TimeDate.noarch 1:1.16-13.el6
master 安装node 包 和 slave 安装node包 都是一样的这里就写一条就行了
]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
Installed:
mha4mysql-node.noarch 0:0.56-0.el6
Dependency Installed:
perl-DBD-MySQL.x86_64 0:4.013-3.el6
Complete!
在manager创建相关的目录以及配置文件
# mkdir /etc/masterha
# vim /etc/masterha/app1.cnf
user=mhauser
password=123456
manager_workdir=/data/masterha/app1
manager_log=/data/masterha/app1/manager.log
remote_workdir=/data/masterha/app1
ssh_user=root
repl_user=repluser
repl_password=123456
ping_interval=1
hostname=192.168.2.36
candidate_master=1
master_binlog_dir = "/mydata/data"
hostname=192.168.2.38
candidate_master=1
master_binlog_dir = "/mydata/data"
hostname=192.168.2.40
在manager上检查ssh连接是否正常
# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Tue Jun7 05:51:30 2016 - ok.
Tue Jun7 05:51:30 2016 - All SSH connection tests passed successfully.
检查脚本是否有误
# masterha_check_repl --conf=/etc/masterha/app1.cnf
Tue Jun7 05:53:23 2016 - Slaves settings check done.
Tue Jun7 05:53:23 2016 -
192.168.2.36(192.168.2.36:3306) (current master)
+--192.168.2.38(192.168.2.38:3306)
+--192.168.2.40(192.168.2.40:3306)
Tue Jun7 05:53:23 2016 - Checking replication health on 192.168.2.38..
Tue Jun7 05:53:23 2016 - ok.
Tue Jun7 05:53:23 2016 - Checking replication health on 192.168.2.40..
Tue Jun7 05:53:23 2016 - ok.
Tue Jun7 05:53:23 2016 - master_ip_failover_script is not defined.
Tue Jun7 05:53:23 2016 - shutdown_script is not defined.
Tue Jun7 05:53:23 2016 - Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
这里没有问题可以启动mha了,注意它是在前台显示的然后如果master挂了之后 它会自动切换到一台从服务器来充当master，然后它mha 也会停止 然后修好挂掉的服务器之后配置好 然后在启动mha 也可以写个脚本在后台自动检测。
# masterha_manager --conf=/etc/masterha/app1.cnf
Tue Jun7 05:56:42 2016 - Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Jun7 05:56:42 2016 - Reading application default configuration from /etc/masterha/app1.cnf..
Tue Jun7 05:56:42 2016 - Reading server configuration from /etc/masterha/app1.cnf..
然后测试的话 把master停止掉 就行 看mha自动切换从库为主！这里我就不测试了
页:
[1]