MHA+Mysql+Mycat 实现高可用读写分离架构:
1.1主2从 实现MHA 监控 192.168.110.100 db-2 为主库Master 192.168.110.200 db-1 ; 192.168.110.110 db-3 为从库slave
注意:MHA Manager 配置文件 指定备选主机
=====================================================
[root@db-3 ~]# cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
password=Uplooking_123
ping_interval=2
repl_password=Uplooking_123
repl_user=slave
ssh_user=root
user=mha
[server1]
#推介故障切换时 备选主机
candidate_master=1
check_repl_delay=0
hostname=192.168.110.200
port=3306
[server2]
hostname=192.168.110.100
port=3306
[server3]
hostname=192.168.110.110
port=3306
==========================================================
###启动MHA Manager
[root@db-3 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
查看MHA Manager运行状态
[root@db-3 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:2527) is running(0:PING_OK), master:192.168.110.200
===========================================================
### Mycat schema.xml 配置
[root@web-proxy conf]# cat 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="host1" database="cloud" />
<!--
balance='1':全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说:"当双主双从模式(M1->S1，
M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。"
switchType="1" switchType='1' 默认值，表示自动切换
writeType='0' 所有写操作发送到配置的第一个writeHost，如果第一个writeHost挂了，则切到还生存的第二个writeHost。
重新启动后已切换后的为准，切换记录在配置文件dnindex.properties中。
-->
<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
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.110.100:3306" user="cloud"
password="Uplooking_123">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.110.200:3306" user="cloud" password="Uplooking_123" />
<readHost host="hostS2" url="192.168.110.110:3306" user="cloud" password="Uplooking_123" />
</writeHost>
<writeHost host="hostM2" url="192.168.110.200:3306" user="cloud"
password="Uplooking_123">
<!-- can have multi read hosts -->
<readHost host="hostS3" url="192.168.110.110:3306" user="cloud" password="Uplooking_123" />
</writeHost>
</dataHost>
</mycat:schema>
==========================================================
###模拟故障 停止 192.168.110.100 主库
1.查看MHA 是否按照约定 切换主Matsert到 备选主机192.168.110.200
[root@db-3 ~]# cat /var/log/mha/app1/manager
[root@db-3<192.168.110.110> ~]# mysql -uroot -pUplooking_123 -e "show slave status\G" ---显示SLAVE状态数据主为192.168.110.200
[root@db-1<192.168.110.200> ~]# mysql -uroot -pUplooking_123 -e "show slave status" ---无SLAVE状态数据显示
通过Mycat登入 ，测试写数据 在192.168.110.200 主 192.168.110.110 从都有写入的数据则 正常
[root@db-3 test_mycat]# mysql -umycat -p123456 -h192.168.110.99
通过Mycat登入 测试读的正确性
mysql> show variables like 'host%'; ----无论多少次读 都只能看到db-3 192.168.110.110 的数据信息
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| host_cache_size | 279 |
| hostname | db-3 |
+-----------------+-------+
2 rows in set (0.00 sec)
======================================================================
故障恢复:
1.启动故障机器 192.168.110.100 的mysql
[root@db-2 ~]# systemctl start mysqld
备份新主的数据 同步库 <当前备份cloud 是因为 mycat 只能操作改变cloud库>
mysql> flush tables with read lock; ----在新的主库192.168.110.200 锁表
[root@db-2 ~]# mysqldump -umha -pUplooking_123 -h192.168.110.200 --databases cloud > /tmp/mysql_cloud_db.sql
恢复数据
[root@db-2 ~]# mysql -uroot -pUplooking_123 < /tmp/mysql_cloud_db.sql
[root@db-2 ~]# mysql -uroot -pUplooking_123
mysql> CHANGE MASTER TO MASTER_HOST='192.168.110.200', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=34392, MASTER_USER='slave', MASTER_PASSWORD='Uplooking_123';
mysql> start slave;
在新的主库192.168.110.200 解锁:
mysql> unlock tables;
2.MHA 配置文件 加入 故障host 信息
#vim /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
password=Uplooking_123
ping_interval=2
repl_password=Uplooking_123
repl_user=slave
ssh_user=root
user=mha
[server1]
candidate_master=1
check_repl_delay=0
hostname=192.168.110.200
port=3306
[server2]
hostname=192.168.110.100
port=3306
[server3]
hostname=192.168.110.110
port=3306
--------------------------------------------------------------------------------------
[root@db-3 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
[root@db-3 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
[root@db-3 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
[root@db-3 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:25446) is running(0:PING_OK), master:192.168.110.200
============================================================
进入mycat 测试： 发现 写 能正确写 到新master主机 192.168.110.200 但是 读 同时也会分担到 192.168.110.200
由于 schema.xml writeType=0 当原来第一个writeHost 故障之后切换到 第二个writeHost 且记录到配置文件dnindex.properties
所以当192.168.110.100 恢复故障故障后 ，实现心跳，但是写依然只会负载到 配置文件dnindex.properties中所记录的writeHost主机
恢复心跳的<writeHost host="hostM1" url="192.168.110.100:3306" user="cloud" password="Uplooking_123">相当于 standy by writeHost 只分担读压力
因此 此时 mycat 读写非完全分离: master 192.168.110.200 既分担 读 也分担 写
解决方案一:
[root@web-proxy conf]# cat 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="host1" database="cloud" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
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.110.100:3306" user="cloud"
password="Uplooking_123">
<!-- can have multi read hosts -->
<!-- 解决方案:移除该readHost -->
<readHost host="hostS1" url="192.168.110.200:3306" user="cloud" password="Uplooking_123" />
<readHost host="hostS2" url="192.168.110.110:3306" user="cloud" password="Uplooking_123" />
</writeHost>
<writeHost host="hostM2" url="192.168.110.200:3306" user="cloud"
password="Uplooking_123">
<!-- can have multi read hosts -->
<readHost host="hostS3" url="192.168.110.110:3306" user="cloud" password="Uplooking_123" />
</writeHost>
</dataHost>
</mycat:schema>
---------------------------------------------------------------------------------------------
[root@db-3 test_mycat]# mysql -umycat -p123456 -P9066 -h192.168.110.99
mysql> reload @@config_all; ---重新加载 /usr/local/mycat/conf 所有 配置文件
----------------------------------------------------------------------------------------------
解决方案二:
[root@web-proxy conf]# vim dnindex.properties
#update
#Fri Jul 10 11:36:34 CST 2020
host1=1 改成 host=0 意思:写负载在配置文件schema.xml中的第一个writeHost
[root@db-3 test_mycat]# mysql -umycat -p123456 -P9066 -h192.168.110.99
mysql> reload @@config_all; ---重新加载 schema.xml 配置文件
在MHA Manager 节点 手工在线切换 主为schema.xml 配置文件指定的第一个 writeHost 192.168.110.100
[root@db-3 ~]# masterha_stop --conf=/etc/mha/app1.cnf
[root@db-3 ~]# masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.110.100 --new_master_port=3306 --orig_master_is_new_slave