mysql的主从复制高可用性解决方案MHA

主服务器配置

配置服务器选项

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin
skip_name_resolve=1
#跳过名字解析


systemctl start mariadb

创建复制用的用户

grant replication slave on *.* to repluser@'192.168.160.%' identified by 'centos';

创建MHA用的账号，权限给的大些

grant all on *.* to mhauser@'192.168.160.%' identified by 'centos';

安装MHA的Node工具包

yum install mha*.rpm -y

从服务器一配置

配置服务器选项

[mysqld]
server-id=2
log-bin
read-only
relay_log_purge=0
#中继日志不清理
skip_name_resolve=1


systemctl start mariadb

配置slave

CHANGE MASTER TO
MASTER_HOST='192.168.160.129',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;


start slave;

安装MHA的Node工具包

yum install mha*.rpm -y

从服务器二配置

配置服务器选项

[mysqld]
server-id=3
log-bin
read-only
relay_log_purge=0
skip_name_resolve=1


systemctl start mariadb

配置slave

CHANGE MASTER TO
MASTER_HOST='192.168.160.129',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;


start slave;

安装MHA的Node工具包

yum install mha*.rpm -y

MHA服务器配置

安装MHA的Node工具包和Manager工具包

yum install mha* -y

让设备间基于KEY验证免密码登入

ssh-keygen
ssh-copy-id 192.168.160.136
scp -r .ssh 192.168.160.129:/root
scp -r .ssh 192.168.160.140:/root
scp -r .ssh 192.168.160.150:/root

创建存放配置信息的文件夹，并配置MHA配置信息

mkdir /etc/mha/
vim /etc/mha/app1.cnf
[server default]
user=mhauser
password=centos
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=centos
ping_interval=1
[server1]
hostname=192.168.160.129
candidate_master=1
[server2]
hostname=192.168.160.140
candidate_master=1
[server3]
hostname=192.168.160.150

检查ssh是否正常

 masterha_check_ssh --conf=/etc/mha/app1.cnf

检查主从复制是否正常

masterha_check_repl --conf=/etc/mha/app1.cnf

开启MHA服务

masterha_manager --conf=/etc/mha/app1.cnf