MHA(Master High Availability)是一套相对成熟的MySQL高可用方案,能做到在0~30s内自动完成数据库的故障切换操作,在master服务器不宕机的情况下,基本能保证数据的一致性。
它由两部分组成:MHA Manager(管理节点)和MHA Node(数据节点)。其中,MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群,也可以部署在一台slave上。MHA Node则运行在每个mysql节点上,MHA Manager会定时探测集群中的master节点,当master出现故障时,它自动将最新数据的slave提升为master,然后将其它所有的slave指向新的master。
在MHA自动故障切换过程中,MHA试图保存master的二进制日志,从而最大程度地保证数据不丢失,当这并不总是可行的,譬如,主服务器硬件故障或无法通过ssh访问,MHA就没法保存二进制日志,这样就只进行了故障转移但丢失了最新数据。可结合MySQL 5.5中推出的半同步复制来降低数据丢失的风险。
MHA软件由两部分组成:Manager工具包和Node工具包,具体说明如下:
MHA Manager:
-
masterha_check_ssh:检查MHA的SSH配置状况
-
masterha_check_repl:检查MySQL的复制状况
-
masterha_manager:启动MHA
-
masterha_check_status:检测当前MHA运行状态
-
masterha_master_monitor:检测master是否宕机
-
masterha_master_switch:控制故障转移(自动或手动)
-
masterha_conf_host:添加或删除配置的server信息
-
masterha_stop:关闭MHA
MHA Node:
save_binary_logs:保存或复制master的二进制日志
apply_diff_relay_logs:识别差异的relay log并将差异的event应用到其它slave中
filter_mysqlbinlog:去除不必要的ROLLBACK事件(MHA已不再使用这个工具)
purge_relay_logs:消除中继日志(不会堵塞SQL线程)
另有如下几个脚本需自定义:
-
master_ip_failover:管理VIP
-
master_ip_online_change:
-
masterha_secondary_check:当MHA manager检测到master不可用时,通过masterha_secondary_check脚本来进一步确认,减低误切的风险。
-
send_report:当发生故障切换时,可通过send_report脚本发送告警信息。
集群信息
角色 IP地址 ServerID 类型
Master 192.168.244.10 1 写入
Candicate master 192.168.244.20 2 读
Slave 192.168.244.30 3 读
Monitor host 192.168.244.40 监控集群组
注:操作系统均为RHEL 6.7
其中,master对外提供写服务,备选master提供读服务,slave也提供相关的读服务,一旦master宕机,将会把备选master提升为新的master,slave指向新的master
一、在所有节点上安装MHA node
1. 在MySQL服务器上安装MHA node所需的perl模块(DBD:mysql)
# yum install perl-DBD-MySQL -y
2. 在所有的节点上安装mha node
下载地址为:https://code.google.com/p/mysql-master-ha/wiki/Downloads?tm=2
由于该网址在国内被墙,相关文件下载后,放到了个人网盘中,http://pan.baidu.com/s/1boS31vT,有需要的童鞋可自行下载。
# tar xvf mha4mysql-node-0.56.tar.gz
# cd mha4mysql-node-0.56
# perl Makefile.PL
View Code
通过报错可以看出,是相关依赖包没有安装。
# yum install perl-ExtUtils-MakeMaker -y
# perl Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies…
Can’t locate CPAN.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/AutoInstall.pm line 277.
# yum install perl-CPAN -y
# perl Makefile.PL
View Code
# make
# make install
至此,MHA node节点安装完毕,会在/usr/local/bin下生成以下脚本文件
复制代码
ll /usr/local/bin/
total 44
-r-xr-xr-x 1 root root 16367 Jul 20 07:00 apply_diff_relay_logs
-r-xr-xr-x 1 root root 4807 Jul 20 07:00 filter_mysqlbinlog
-r-xr-xr-x 1 root root 8261 Jul 20 07:00 purge_relay_logs
-r-xr-xr-x 1 root root 7525 Jul 20 07:00 save_binary_logs
复制代码
二、在Monitor host节点上部署MHA Manager
# tar xvf mha4mysql-manager-0.56.tar.gz
# cd mha4mysql-manager-0.56
# perl Makefile.PL
View Code
# make
# make install
执行完毕后,会在/usr/local/bin下新增以下几个文件
复制代码
复制代码
ll /usr/local/bin/
total 40
-r-xr-xr-x 1 root root 1991 Jul 20 00:50 masterha_check_repl
-r-xr-xr-x 1 root root 1775 Jul 20 00:50 masterha_check_ssh
-r-xr-xr-x 1 root root 1861 Jul 20 00:50 masterha_check_status
-r-xr-xr-x 1 root root 3197 Jul 20 00:50 masterha_conf_host
-r-xr-xr-x 1 root root 2513 Jul 20 00:50 masterha_manager
-r-xr-xr-x 1 root root 2161 Jul 20 00:50 masterha_master_monitor
-r-xr-xr-x 1 root root 2369 Jul 20 00:50 masterha_master_switch
-r-xr-xr-x 1 root root 5167 Jul 20 00:50 masterha_secondary_check
-r-xr-xr-x 1 root root 1735 Jul 20 00:50 masterha_stop
复制代码
复制代码
三、配置SSH登录无密码验证
1. 在manager上配置到所有Node节点的无密码验证
# ssh-keygen
一路按“Enter”
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.10
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.20
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.30
2. 在Master(192.168.244.10)上配置
# ssh-keygen
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.20
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.30
3. 在Candicate master(192.168.244.20)上配置
# ssh-keygen
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.10
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.30
4. 在Slave(192.168.244.30)上配置
# ssh-keygen
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.10
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.20
四、搭建主从复制环境
复制代码
安装mysql
yum -y install mysql-community-libs-5.7.19-1.el7.x86_64.rpm mysql-community-common-5.7.19-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.19-1.el7.x86_64.rpm mysql-community-client-5.7.19-1.el7.x86_64.rpm mysql-community-server-5.7.19-1.el7.x86_64.rpm mysql-community-devel-5.7.19-1.el7.x86_64.rpm
移动 datadir 或删除了data目录,重新初始化
mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --datadir=/data/mysql
初始密码
grep ‘temporary password’ /var/log/mysqld.log
修改bug
mysql_upgrade
mysql> alter user root@localhost identified by 'xx';
ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 42. Created with MySQL 50556, now running 50719. Please use mysql_upgrade to fix this error.
问题:ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
解决:SET GLOBAL validate_password_policy='LOW';
修改root密码
flush privileges;
alter user root@localhost identified by ‘user_pass’; 或 update mysql.user set authentication_string=password(‘user_pass’) where user=‘root’;
flush privileges;
建root@127.0.0.127 , 用修改密码后的root登录
grant all on . to root@127.0.0.1 identified by ‘user_pass’ with grant option;
建同步用户
grant REPLICATION SLAVE, REPLICATION CLIENT on . to ‘tongbushou’@‘172.16.0.%’ identified by ‘sync_pass’;
flush privileges;
建HAM管理用户
grant all on . to ‘mymha’@‘172.16.0.%’ identified by ‘JXqqSV63y9Ls8Nq’;
flush privileges;
创建监控用户
grant select,process,super on . to ‘lepus_monitor’@‘ip’ identified by ‘password’;
配置
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.