在四台主机上下载epel源:
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum makecache
安装依赖:
yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-ParallelForkManager perl-Config-IniFiles ncftp perl-Params-Validate perl-CPAN perl-Test-Mock-LWP.noarch perl-LWP-Authen-Negotiate.noarch perl-devel perl-ExtUtils-CBuilder
perl-ExtUtils-MakeMaker
修改主机名
Vim /etc/hosts
第一个主机IP master01
第二个主机IP master02
第三个主机IP slave01
第四个主机IP manager
禁用selinux:
vim /etc/selinux/config
SELINUX=disabled
设置防火墙策略
firewall-cmd --add-port=3306/tcp –permanent
删除uuid
Rm -rf /usr/local/mysql/data/auto.conf
克隆三台
Manager不需要开启数据库
无密登录:
Ssh-keygen -t rsa
传到其他主机修改的主机名:
for i in {master02-20,slave01-30,manager-40};do scp /etc/hosts $i:/etc;done
ssh-copy-id manager-40
切换到master02
Ssh master02
Ssh-keygen -t rsa
for i in {master01-10,master02-20,slave01-30,manager-40};do ssh-copy-id $i;done
一直做到第四台
四台主机验证需要不需要密码
for i in {master01-10,master02-20,slave01-30,manager-40};do ssh $i hostname;done
配置mysql半同步复制
如果不清楚Plugin的目录,用如下查找:
show variables like '%plugin_dir%';
分别在主从节点上安装相关的插件(master, Candicate master,slave) 在MySQL上安装插件需要数据库支持动态载入。检查是否支持,用如下检测:
show variables like '%have_dynamic%';
安装半同步插件(semisync_master.so,semisync_slave.so) 除了manager不装
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
其他mysql主机采用同样的方法安装 检查Plugin是否已正确安装:
show plugins;
select * from information_schema.plugins;
查看半同步相关信息
show variables like '%rpl_semi_sync%';
修改主配文件
master mysql主机:
server-id = 1
log-bin=mysql-bin
binlog_format=mixed
log-bin-index=mysql-bin.index
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_slave_enabled=1
relay_log_purge=0
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
Candicate master主机:
server-id = 2
log-bin=mysql-bin
binlog_format=mixed
log-bin-index=mysql-bin.index
relay_log_purge=0
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000
rpl_semi_sync_slave_enabled=1
Slave主机:
server-id = 3
log-bin = mysql-bin
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
read_only = 1
rpl_semi_sync_slave_enabled=1
查看半同步状态:
show status like ‘%rpl_semi_sync%’;
状态参数值得关注的:
rpl_semi_sync_master_status :显示主服务是异步复制模式还是半同步复制模式
rpl_semi_sync_master_clients :显示有多少个从服务器配置为半同步复制模式
rpl_semi_sync_master_yes_tx :显示从服务器确认成功提交的数量
rpl_semi_sync_master_no_tx :显示从服务器确认不成功提交的数量
rpl_semi_sync_master_tx_avg_wait_time :事务因开启 semi_sync ,平均需要额外等待的时间
rpl_semi_sync_master_net_avg_wait_time :事务进入等待队列后,到网络平均等待时间
master主机:
grant replication slave on *.* to mharep@'192.168.157.%' identified by '123';
grant all privileges on *.* to manager@'192.168.157.%' identified by '123';
show master status;
Candicate master主机:
mysql> grant replication slave on *.* to mharep@'192.168.157.%' identified by '123';
mysql> grant all privileges on *.* to manager@'192.168.157.%' identified by '123';
change master to
master_host='192.168.157.10',master_port=3306,master_user='mharep',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=1336;
mysql> start slave;
Slave主机:
mysql> grant all privileges on *.* to manager@'192.168.157.%' identified by '123';
change master to
master_host='192.168.157.10',master_port=3306,master_user='rep',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=1336;
start slave;
查看master服务器的半同步状态:
show status like '%rpl_semi_sync%';
在所有数据库节点上安装mha4mysql-node-0.56.tar.gz
tar zxf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58
perl Makefile.PL
make && make install
在manager两个安装包都要装
tar zxf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58
perl Makefile.PL
make && make install
tar zxf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58/
perl Makefile.PL
make && make install
在manager创建目录:
mkdir /etc/masterha
mkdir -p /masterha/app1
mkdir /scripts
cp samples/conf/* /etc/masterha/
cp samples/scripts/* /scripts/
配置mha
vim /etc/masterha/app1.cnf
[server default]
manager_workdir=/masterha/app1
manager_log=/masterha/app1/manager.log
user=manager
password=123
ssh_user=root
repl_user=mharep
repl_password=123
ping_interval=1
[server1]
hostname=master01的IP
port=3306
master_binlog_dir=/usr/local/mysql/data
candidate_master=1
[server2]
hostname= master02的IP
port=3306
master_binlog_dir=/usr/local/mysql/data
candidate_master=1
[server3]
hostname= slave01 的IP
port=3306
master_binlog_dir=/usr/local/mysql/data
no_master=1
清空masterha_default.cnf
>/etc/masterha/masterha_default.cnf
SSH 有效性验证:
masterha_check_ssh --global_conf=/etc/masterha/masterha_default.cnf --conf=/etc/masterha/app1.cnf
集群复制的有效性验证:(在主从主机都做)
ln -s /usr/local/mysql/bin/* /usr/local/bin/
masterha_check_repl --global_conf=/etc/masterha/masterha_default.cnf --conf=/etc/masterha/app1.cnf
启动 manager:
nohup masterha_manager --conf=/etc/masterha/app1.cnf &>/tmp/mha_manager.log &
状态检查:
masterha_check_status --conf=/etc/masterha/app1.cnf
监控日志:
Tailf /masterha/app1/manager.log
将master01mysql停止就会转移到master02
排错:
将master01启动起来
过滤/masterha/app1/manager.log
Grep ‘GHANGE’ | /masterha/app1/manager.log
复制GHANGE 开头到master01下面将rep密码写上
删除app1.failover.complete
启动manager
nohup masterha_manager --conf=/etc/masterha/app1.cnf
&>/tmp/mha_manager.log &
如果现实有多个主从复制可以创建多个
/etc/masterha/app个数.cnf
nohup masterha_manager --conf=/etc/masterha/app个数.cnf &>/tmp/mha_manager.log &