一、高可用架构图
MHA简介:
MHA(master high availability)是成熟的MySQL高可用解决方案。MHA可以在30秒内实现故障切换,尽可能保证数据的一致性。目前淘宝也在开发类似的产品TMHA,支持一主一从/一主多从。
MHA结构:
该软件由两部分组成:MHA Manager(管理节点)和MHA node(数据节点).
MHA工作原理:
(1) 获取从宕机崩溃的 master 保存二进制日志事件(binlog events);
(2) 识别含有最新更新的 slave ;
(3) 将差异的中继日志(relay log)应用到其他 slave ;
(4) 将 master 保存的二进制日志事件(binlog events)应用到要提升为master节点的slave;
(5) 将这 slave 只读模式解除并提升为新 master ,重新部署主从关系;
MHA优势:
- 快速故障转移
- Master故障不会导致数据不一致
- 无需修改当前mysql设置
- 无需添加大量的服务器
- 无性能量下降
- 对于任何存储引擎
二、网络规划
本次采用9台机器
Ip地址规划:
Haproxy1:192.168.254.220
Haproxy2:192.168.254.221
Mycat1:192.168.254.222
Mycat2:192.168.254.223
Msqyl-manager:192.168.254.224
Mysql-master1:192.168.254.225
Mysql-master2:192.168.254.226
Mysql-slave1:192.168.254.227
Mysql-slave2:192.168.254.228
VIP:192.168.254.229
Ansible hosts规划
[qky]
192.168.254.220
192.168.254.221
192.168.254.222
192.168.254.223
192.168.254.224
192.168.254.225
192.168.254.226
192.168.254.227
192.168.254.228
[haproxy]
192.168.254.220
192.168.254.221
[mycat]
192.168.254.222
192.168.254.223
[mysqlall]
192.168.254.225
192.168.254.226
192.168.254.227
192.168.254.228
[mysql-manager]
192.168.254.224
[mysql-master]
192.168.254.225
192.168.254.226
[mysql-slave]
192.168.254.227
192.168.254.228
三、免密互信
批量免密脚本:
[root@haproxy1 ~]# vim key.sh
#!/bin/bash
if [ ! -f ~/.ssh/id_rsa ];then
ssh-keygen -t rsa
else
echo "id_rsa has created ..."
fi
while read line
do
user="root"
ip=`echo $line | cut -d " " -f 1`
passwd="000000"
expect <<EOF
set timeout 10
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub $user@$ip
expect {
"yes/no" { send "yes\n";exp_continue }
"password" { send "$passwd\n" }
}
expect "password" { send "$passwd\n" }
EOF
done < hostlist.txt
[root@haproxy1 ~]# chmod o+x key.sh
[root@haproxy1 ~]# vim hostlist.txt
192.168.254.220
192.168.254.221
192.168.254.222
192.168.254.223
192.168.254.224
192.168.254.225
192.168.254.226
192.168.254.227
192.168.254.228
[root@haproxy1 ~]# yum install expect -y
[root@haproxy1 ~]#sh key.sh
四、安装ansible
[root@haproxy1~]#wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
[root@haproxy1~]#rpm -ivh epel-release-6-8.noarch.rpm
[root@haproxy1~]#yum install ansible -y
使用ansible测试一下
[root@haproxy1 ~]# ansible qky -m shell -a "ping www.baidu.com -c 1"
五、关闭selinux、防火墙并reboot
[root@haproxy1 ~]# ansible qky -m shell -a "sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config"
[root@haproxy1 ~]# ansible qky -m shell -a "systemctl stop firewalld"
[root@haproxy1 ~]# ansible qky -m shell -a "systemctl disable firewalld"
六、安装mysql
开始配置mysql(mysql-master1,mysql-master2,mysql-slave1,mysql-slave2)
下载wget下载安装包
[root@haproxy1 ~]# ansible mysqlall -m shell -a "wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.15-1.el7.x86_64.rpm-bundle.tar"
创建目录/opt/mysql,并copy到目录并解压
[root@haproxy1 ~]# ansible mysqlall -m file -a 'path=/opt/mysql state=directory'
[root@haproxy1~]# ansible mysqlall -m copy -a "src=/root/mysql-5.7.15-1.el7.x86_64.rpm-bundle.tar dest=/opt/mysql/ mode=755 owner=root"
[root@haproxy1 ~]# ansible mysqlall -m shell -a "cd /opt/mysql && tar -xvf mysql-5.7.15-1.el7.x86_64.rpm-bundle.tar"
[root@haproxy1 ~]# ansible mysqlall -m yum -a "name=createrepo state=installed"
这里只需要6个包,其他全部删除
[root@haproxy1 ~]# ansible mysqlall -m shell -a " rm -rf mysql-community-embedded-* mysql-community-minimal-debuginfo-5.7.15-1.el7.x86_64.rpm mysql-community-server-minimal-5.7.15-1.el7.x86_64.rpm mysql-community-test-5.7.15-1.el7.x86_64.rpm repodata"
将mysql-community-*做成yum源
[root@haproxy1 ~]# ansible mysqlall -m shell -a "cd /opt/mysql/ && createrepo ."
编写本地源文件:
[root@haproxy1 ~]# cat mysql.repo
[mysql]
name=mysql
baseurl=file:///opt/mysql
enabled=1
gpgcheck=0
分发给mysql主机
[root@haproxy1 ~]# ansible mysqlall -m copy -a "src=/root/mysql.repo dest=/etc/yum.repos.d mode=755 owner=root"
安装mysql
[root@haproxy1 ~]# ansible mysqlall -m yum -a "name=mysql-community-* state=installed"
启动服务并设置开机自启:
[root@haproxy1 ~]# ansible mysqlall -m service -a "name=mysqld state=started"
[root@haproxy1 ~]# ansible mysqlall -m service -a “name=mysqld enabled=yes”
安装一个net-tools模块:
[root@haproxy1 ~]# ansible mysqlall -m service -a "name=mysqld enabled=yes"
检查服务是否启动:
[root@haproxy1 ~]# ansible mysqlall -m shell -a "netstat -lntp | grep 3306"
查看mysql密码
在mysql-master1上修改密码,同步到其他主机。
[root@mysql-master1 mysql]# mysql -uroot -pvuC-8YBqn#A?
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@localhost identified by "000000";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
同步的时候看一下其他主机下的/var/lib/mysql/auto.cnf 这个是放UUID的地方,一样需要改动或者删除
先对每一台auto.cnf备份
scp -r /var/lib/mysql/auto.cnf /opt/
在scp到其他主机上
scp -r /var/lib/mysql mysql-master2:var/lib
scp -r /var/lib/mysql mysql-slave1:var/lib
scp -r /var/lib/mysql mysql-slave2:var/lib
在把auto.cnf拷贝到原位置
scp -r /opt/auto.cnf /var/lib/mysql/
对所有mysql机器重启动
[root@haproxy1 yum.repos.d]# ansible mysqlall -m shell -a "systemctl restart mysqld"
七、配置mysql-manager
配置MHA集群(mysql-manager节点)
安装集群依赖包
这次安装是使用的rpm格式,在manager和node的所有节点均需安装MHA Node。
管理端:
perl环境
[root@mysql-manager~]#rpm -Uvh http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
[root@mysql-manager ~]# yum -y install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-devel perl-Module-Install.noarch
[root@mysql-manager ~]# yum install perl-DBD-MySQL -y
这里使用本地mha4mysql-node-0.56-0.el6.noarch.rpm包
[root@mysql-manager ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mysql-manager ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
解压mha4mysql-manager-0.56.tar.gz包并安装
[root@mysql-manager ~]# tar -zxvf mha4mysql-manager-0.56.tar.gz
[root@mysql-manager ~]# cd mha4mysql-manager-0.56
[root@mysql-manager mha4mysql-manager-0.56]# perl Makefile.PL
[root@mysql-manager mha4mysql-manager-0.56]# make && make install
Manager Tookit的主要工具
masterha_ check_ SSH 检查MHA的SSH配置
masterha_ check_ Repl 检查MySQL的复制状态
masterha_ Manger starts 启动MHA
masterha_ check_ Status 检测当前MHA运行状态
masterha_ master_ Monitor 检测主机是否已关闭
masterha_ master_ Switch 故障切换控制(自动或手动)
masterha_ conf_ Host 添加或删除已配置的服务器信息
[root@mysql-manager ~]# scp -r mha4mysql-manager-0.56/bin/* /usr/local/bin/
创建工作目录:
[root@mysql-manager ~]# mkdir /etc/mha_manager
[root@mysql-manager ~]# scp -r mha4mysql-manager-0.56/samples/conf/app1.cnf /etc/mha_manager/
[root@mysql-manager ~]# vim /etc/mha_manager/app1.cnf
#创建故障转移脚本
[root@mysql-manager ~]# cp mha4mysql-manager-0.56/samples/scripts/master_ip_failover /usr/local/bin/
node节点
Perl环境
[root@haproxy1 ~]# ansible mysqlall -m shell -a "rpm -Uvh http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm"
[root@haproxy1 ~]# ansible mysqlall -m shell -a "yum -y install perl-DBD-MySQL"
[root@haproxy1 ~]# ansible mysqlall -m shell -a "rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm"
配置主节点225:
[root@mysql-master1 ~]# vim /etc/my.cnf
###添加如下内容
validate_password_policy=0
validate_password_length=6
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
server_id=225
log_bin=master225
binlog_format="mixed"
relay_log_purge=off
重启mysql服务
[root@mysql-master1 ~]# systemctl restart mysqld
在225的主机上做root的授权,其他的会同步(如果不做,在验证数据节点的主从同步配置时会出错)
mysql> grant all on *.* to root@"%" identified by "000000";
Query OK, 0 rows affected, 1 warning (10.02 sec)
mysql> grant replication slave,replication client on *.* to repluser@"%" identified by "000000";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置备用节点226(操作基本一致,修改id即可)
重启mysql服务
[root@mysql-master2 ~]# systemctl restart mysqld
[root@mysql-master2 ~]# mysql -uroot -p000000
mysql> change master to
-> master_host="192.168.254.225",
-> master_user="repluser",
-> master_password="000000",
-> master_log_file="master225.000001",
-> master_log_pos=154;
mysql> flush privileges;
Query OK, 0 rows affected (10.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
mysql> select user from mysql.user;
配置从节点(227):
[root@mysql-slave1 ~]# vim /etc/my.cnf
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password_policy=0
validate_password_length=6
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
server_id=227
binlog_format="mixed"
relay_log_purge=off
[root@mysql-slave1 ~]# systemctl restart mysqld
mysql> change master to
-> master_host="192.168.254.225",
-> master_user="repluser",
-> master_password="000000",
-> master_log_file="master225.000001",
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
配置从节点228(修改id)
[root@mysql-slave2 ~]# vim /etc/my.cnf
validate_password_policy=0
validate_password_length=6
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
server_id=228
binlog_format="mixed"
relay_log_purge=off
mysql> change master to
-> master_host="192.168.254.225",
-> master_user="repluser",
-> master_password="000000",
-> master_log_file="master225.000001",
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
测试:
做免密登陆用之前的脚本
验证ssh免密登陆数据节点主机
[root@mysql-manager bin]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf
验证数据节点主从同步配置
添加注释测试
[root@mysql-manager bin]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf
启动mha集群
[root@mysql-manager bin]# masterha_manager --conf=/etc/mha_manager/app1.cnf --remove_dead_master_conf --ignore_last_failover
查看状态:
vip配置可以采用两种方式,一种通过keepalived的方式管理虚拟ip的浮动;另外一种通过脚本方式启动虚拟ip的方式 (即不需要keepalived或者heartbeat类似的软件).
本次采用:脚本方式启动虚拟ip
添加VIP在mhamanager上
[root@mysql-manager bin]# vim /usr/local/bin/master_ip_failover
添加35行:
my $vip = '192.168.254.190/24'; # Virtual IP
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; # Network name
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
........
##65行
&stop_vip();
.......
###删除96行,100行
FIXME_xxx_create_user( $new_master_handler->{dbh} );
FIXME_xxx;
.......
##101行
&start_vip();
###121行添加:
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
去掉master_ip_failover注释
[root@mysql-manager bin]# vim /etc/mha_manager/app1.cnf
临时设置VIP在225上
###要永久添加:ip addr add 192.168.254.190/24 dev ens33:1
[root@mysql-master1 ~]# ifconfig ens33:1 192.168.254.190/24
检查复制环境:
[root@mysql-manager bin]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf
在一次启动mha集群
[root@mysql-manager bin]# masterha_manager --conf=/etc/mha_manager/app1.cnf --remove_dead_master_conf --ignore_last_failover
八、安装mycat
配置mycat集群(mycat1,mycat2)
首先在225主库上创建一个用于查询的用户
[root@mysql-master1 ~]# mysql -uroot -p000000
mysql> grant select,insert on *.* to admin@"%" identified by "000000";
mysql> select user from mysql.user;
两个mycat安装java环境
这里直接用ansible
[root@haproxy1 ~]# ansible mycat -m shell -a "yum -y install java-1.8.0-openjdk-devel"
安装mycat包,这里用本地包
[root@haproxy1 ~]# ansible mycat -m copy -a "src=/root/Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz dest=/root mode=644 owner=root"
解压:
[root@haproxy1 ~]# ansible mycat -m shell -a "tar -xvf /root/Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz"
移动到/usr/local下
[root@haproxy1 ~]# ansible mycat -m shell -a "mv /root/mycat /usr/local/"
检查:
[root@haproxy1 ~]# ansible mycat -m shell -a "ls -al /usr/local/ | grep mycat"
在222上面修改配置文件(server.xml,schema.xml):
[root@mycat1 ~]# vim /usr/local/mycat/conf/server.xml
[root@mycat1 ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" >
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.254.190:3306" user="root" password="000000">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.254.227:3306" user="root" password="000000" />
<readHost host="hostS2" url="192.168.254.228:3306" user="root" password="000000" />
</writeHost>
</dataHost>
</mycat:schema>
Schema name属性:描述一个逻辑数据库名称,即通过这个逻辑数据库找到真实数据库
dataHost标签属性释义:
Balance:负载类型
0:不开启读写分离机制,所有读操作都发送到当前可用的writeHost上
1:全部的readHost与stand by writeHost参与select语句的负载均衡,
2:所有读操作都随机在writeHost、readHost上分发
3:所有读请求随机分发到writeHost对应的readHost执行,writeHost不负担读压力
writeType:负载均衡类型
0:所有写操作发送到配置的第一个writeHost,当第一个writeHost宕机时,切换到第二个writeHost,重新启动后以切换后的为准,切换记录在配置文件:dnindex.properties中
1:所有写操作都随发送到配置的writeHost
2:没实现
switchType:切换方式
-1:不自动切换
1:自动切换(默认)
2:基于MySql主从同步的状态来决定是否切换
启动mycat
设置成为开机自启动
[root@mycat1 conf]# ln -s /usr/local/mycat/bin/mycat /etc/init.d/mycat
[root@mycat1 conf]# chkconfig --add mycat
[root@mycat1 conf]# service mycat status
检查端口是否开放:
[root@mycat1 conf]# netstat -lntp | grep 066
配置文件中有一个坑: database=db1,这个地方要自己去创建,不然心跳检查会一直失败
直接在登陆192.168.254.190:3306上创建就行了.。
在装有mysql的数据库服务器上测试,这里用其中的一台数据库服务器测试
[root@mysql-master2 ~]# mysql -uroot -p000000 -h192.168.254.222 -P 8066 -e 'select @@hostname'
会发现读写分离了,随机从从节点读取。
拷贝文件server.xml和schema.xml文件到另外一台mycat2上面
[root@mycat1 conf]# scp -r server.xml schema.xml mycat2:/usr/local/mycat/conf/
[root@mycat2 conf]# /usr/local/mycat/bin/mycat start
设置成为开机自启动
[root@mycat1 conf]# ln -s /usr/local/mycat/bin/mycat /etc/init.d/mycat
[root@mycat1 conf]# chkconfig --add mycat
[root@mycat1 conf]# service mycat status
[root@mycat2 conf]# netstat -lntp |grep 066
九、haproxy+keepalived
部署haproxy1和haproxy2负载均衡+keepalived高可用
还是用ansible部署
[root@haproxy1 ~]# ansible haproxy -m shell -a "yum -y install haproxy keepalived"
修改haproxy配置文件
[root@haproxy1 ~]# vim /etc/haproxy/haproxy.cfg
listen mycat_3306 *:3306
mode tcp # mysql 得使用 tcp 协议
option tcpka # 使用长连接
balance leastconn # 最小连接调度算法
server mycat1 192.168.254.222:8066 check inter 3000 rise 1 maxconn 1000 fall 3
server mycat2 192.168.254.223:8066 check inter 3000 rise 1 maxconn 1000 fall 3
[root@haproxy2 ~]# vim /etc/haproxy/haproxy.cfg
listen mycat_3306 *:3306
mode tcp # mysql 得使用 tcp 协议
option tcpka # 使用长连接
balance leastconn # 最小连接调度算法
server mycat1 192.168.254.222:8066 check inter 3000 rise 1 maxconn 1000 fall 3
server mycat2 192.168.254.223:8066 check inter 3000 rise 1 maxconn 1000 fall 3
启动haproxy
[root@haproxy1 ~]# ansible haproxy -m shell -a "systemctl start haproxy"
开机自启动
随便找一台mysql宿主机分别测试:
[root@mysql-slave2 ~]# mysql -uroot -p000000 -h192.168.254.220 -e "select @@hostname"
[root@mysql-slave2 ~]# mysql -uroot -p000000 -h192.168.254.221 -e "select @@hostname"
修改keepalived配置文件
[root@haproxy1 ~]# vim /etc/keepalived/keepalived.conf
global_defs {
router_id haproxy1
}
vrrp_strict chk_haproxy {
script "/etc/keepalived/check_haproxy.sh"
interval 2
Weight 3
}
vrrp_instance mycat1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 200
nopreempt
advert_int 2
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.254.229/24 brd 192.168.254.255 dev ens33 label ens33:1
}
track_script {
chk_haproxy
}
}
[root@haproxy1 keepalived]# vim check_haproxy.sh
#!/bin/bash
if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then
systemctl restart haproxy
sleep 3
if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then
systemctl stop keepalived
fi
fi
[root@haproxy1 keepalived]# chmod 777 check_haproxy.sh
[root@haproxy2 ~]# vim /etc/keepalived/keepalived.conf
global_defs {
router_id haproxy1
}
vrrp_strict chk_haproxy {
script "/etc/keepalived/check_haproxy.sh"
interval 2
Weight 3
}
vrrp_instance mycat1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 200
nopreempt
advert_int 2
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.254.229/24 brd 192.168.254.255 dev ens33 label ens33:1
}
track_script {
chk_haproxy
}
}
[root@haproxy2 keepalived]# vim check_haproxy.sh
#!/bin/bash
if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then
systemctl restart haproxy
sleep 3
if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then
systemctl stop keepalived
fi
fi
[root@haproxy2 keepalived]# chmod 777 check_haproxy.sh
[root@haproxy1 ~]# ansible haproxy -m shell -a "systemctl start keepalived"
[root@haproxy1 ~]# ansible haproxy -m shell -a "systemctl status keepalived"
[root@haproxy1 ~]# ansible haproxy -m shell -a "systemctl enable keepalived"
测试:
[root@mysql-slave1 ~]# mysql -uroot -p000000 -h192.168.254.229 -e "select @@hostname"
[root@haproxy1 ~]# ip addr list ens33