mysql的MHA高可用集群+mycat的读写分离，实战不停机维护详细版

mysql的mha+mycat读写分离

配置各主机的主从同步

数据库角色 IP 系统与MySQL版本
主数据库 192.168.121.142 RHEL7 MySQL5.7
从数据库 192.168.121.143 RHEL7 MySQL5.7
从数据库 192.168.121.144 RHEL7 MySQL5.7

主库142的my.cnf配置

vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
autocommit=1
skip-name-resolve
log-bin=mysql-bin-master           #开启二进制日志
server-id=1                        #服务器ID，必须唯一
log-slave-updates=1                #从库binlog记录主库同步的操作日志
binlog-ignore-db=mysql             #不可被复制的库
lower_case_table_names=1

然后进入数据库添加复制用户，这里为了方便就开启所有权限了

mysql -uroot -pMySQL@666

grant all privileges on *.* to 'cat'@'192.168.121.%' identified by "MySQL@666";
flush privileges;

重启数据库

systemctl restart mysqld

从库143的my.cnf配置

vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
autocommit=1
lower_case_table_names = 1
log-bin=mysql-slave1
server-id=2
log-slave-updates=1
binlog-ignore-db=mysql

然后进入数据库添加复制用户，这里为了方便就开启所有权限了

mysql -uroot -pMySQL@666

grant all privileges on *.* to 'cat'@'192.168.121.%' identified by "MySQL@666";
flush privileges;

重启数据库

systemctl restart mysqld

进入数据库，连接同步主库

mysql -uroot -pMySQL@666

连接主库，开启主从配置

stop slave;
reset slave all;
change master to master_host='192.168.121.142',master_user='cat',master_password='MySQL@666';
start slave;
show slave status\G

看到有下面那个IO和SQL线程的两个yes就是成功了

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.121.142
                  Master_User: cat
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000002
          Read_Master_Log_Pos: 2060
               Relay_Log_File: ljc103-relay-bin.000003
                Relay_Log_Pos: 1166
        Relay_Master_Log_File: mysql-bin-master.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 

从库144的my.cnf配置

基本和143一样

vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
autocommit=1
lower_case_table_names = 1
log-bin=mysql-slave1
server-id=3
log-slave-updates=1
binlog-ignore-db=mysql

然后进入数据库添加复制用户，这里为了方便就开启所有权限了

mysql -uroot -pMySQL@666

grant all privileges on *.* to 'cat'@'192.168.121.%' identified by "MySQL@666";
flush privileges;

重启数据库

systemctl restart mysqld

进入数据库，连接同步主库

mysql -uroot -pMySQL@666

连接主库，开启主从配置

stop slave;
reset slave all;
change master to master_host='192.168.121.142',master_user='cat',master_password='MySQL@666';
start slave;
show slave status\G

看到有下面那个IO和SQL线程的两个yes就是成功了

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.121.142
                  Master_User: cat
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000002
          Read_Master_Log_Pos: 2060
               Relay_Log_File: ljc104-relay-bin.000002
                Relay_Log_Pos: 862
        Relay_Master_Log_File: mysql-bin-master.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

主从配置到这里就完成了，我就不测试了，我的其他文章有关于这些的介绍，可以看我的其他文章。

配置mha高可用

数据库角色 IP 系统与MySQL版本
主数据库 192.168.121.142 RHEL7 MySQL5.7
从数据库 192.168.121.143 RHEL7 MySQL5.7
从数据库 192.168.121.144 RHEL7 MySQL5.7
监控主机 192.168.121.161 RHEL7 MySQL5.7

监控主机可以选一台新的主机或者使用从库充当也行

下面开始操作步骤：

首先上传MHA相关包，在所有的节点安装mha-node。
然后在监控主机上安装MHA Manager
MHA Manager中主要包括了几个管理员的命令行工具，例如master_manger，master_master_switch等；MHA Manger也依赖于perl模块，具体如下：

（1）安装MHA Node软件包之前需要安装依赖，我这里使用yum完成，首先epel源要安装注意：刚才已经配置epel源

（2）安装MHA Manager 首先安装MHA Manger依赖的perl模块（我这里使用yum安装）：

使用yum安装全部依赖

yum install perl perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN -y

安装MHA Manager软件包完成后会在/usr/bin目录下面生成一些脚本文件，mha模块的文章已经说过这些，这里不再重复，不理解的可以再去翻一下 我的文章。

然后配置mha配置文件
创建相关目录，再写入配置信息

mkdir /etc/mha
mkdir -p /var/log/mha/app1
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=MySQL@666
ping_interval=2
repl_password=MySQL@666
repl_user=cat
ssh_user=root
user=cat

[server1]
hostname=192.168.121.142
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.121.143
port=3306
[server3]
hostname=192.168.121.144
port=3306

然后主从通信是否正常

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

如果最后一行显示下面的结果则各主机之间通信没有问题

[info] All SSH connection tests passed successfully.

检查主从复制是否完好

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

如果最后一行显示下面的结果则各主机之间通信没有问题

MySQL Replication Health is OK.

如果有报错就得看问题解决了
没有问题以后启动MHA Manager

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运行状态

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

像我这样就是正常运行着

[root@server1 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:43749) is running(0:PING_OK), master:192.168.121.142
[root@server1 ~]# 

配置好读写分离以后在主库上创建一个数据库mycatdb，等会读写分离的时候用到

mysql -uroot -pMySQL@666

创库创表

create database mycatdb；
create table aa(id int,name char(15));

这里好了就可以去配置mycat读写分离了

配置mycat的读写分离

mycat的安装我这里就不再介绍了，想看的可以去我的其他文章翻一下
只要有mycat就可以了，之前用过也没事，跟着我下面修改配置就行
下面直接开始操作

mycat的schema.xml 配置
整个配置可以替换成下面的配置

vim /opt/module/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
        </schema>
        <dataNode name="dn1" dataHost="dthost" database="mycatdb"/>
        <dataHost name="dthost" maxCon="500" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="192.168.121.142" url="192.168.121.142:3306" user="cat" password="MySQL@666">
        <readHost host="192.168.121.143" url="192.168.121.143:3306" user="cat" password="MySQL@666" />
        <readHost host="192.168.121.144" url="192.168.121.144:3306" user="cat" password="MySQL@666" />
        </writeHost>
        <writeHost host="192.168.121.143" url="192.168.121.143:3306" user="cat" password="MySQL@666">
                        <!-- can have multi read hosts -->
         <readHost host="192.168.121.144" url="192.168.121.144:3306" user="cat" password="MySQL@666" />
                </writeHost>
        </dataHost>
</mycat:schema>

还有server.xml的配置
这里只是修改这部分的内容，上面一大串不用动，不要整个文件替换

 vim /opt/module/mycat/conf/server.xml 

        <user name="cat" defaultAccount="true">
                <property name="password">MySQL@666</property>
                <property name="schemas">mycatdb</property>
                <!--<property name="defaultSchema">TESTDB</property>-->
                <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->

                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>

        <user name="user">
                <property name="password">MySQL@666</property>
                <property name="schemas">mycatdb</property>
                <property name="readOnly">true</property>
                <!--<property name="defaultSchema">TESTDB</property>-->
        </user>

然后启动mycat

/opt/module/mycat/bin/mycat start
/opt/module/mycat/bin/mycat status

然后登录我们的mycat，就可以控制mysql集群了，这里的用户和密码是server.xml里设置的用户密码，不是MySQL的用户和密码

mysql -ucat -pMySQL@666 -P8066 -h 192.168.121.161

然后就可以看到我们配置里写的可以控制读写分离的数据库

mysql> show databases;
+----------+
| DATABASE |
+----------+
| mycatdb  |
+----------+
1 row in set (0.00 sec)

mysql> use mycatdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_mycatdb |
+-------------------+
| aa                |
+-------------------+
1 row in set (0.00 sec)

mysql> 

在9066端口可以查看我们mycat控制的mysql集群情况

mysql -ucat -pMySQL@666 -P8066 -h 192.168.121.161

mysql> show @@datasource;
+----------+-----------------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME            | TYPE  | HOST            | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+-----------------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | 192.168.121.142 | mysql | 192.168.121.142 | 3306 | W    |      0 |    1 |  500 |    1473 |         0 |          0 |
| dn1      | 192.168.121.143 | mysql | 192.168.121.143 | 3306 | W    |      0 |    8 |  500 |    1485 |         0 |          2 |
| dn1      | 192.168.121.143 | mysql | 192.168.121.143 | 3306 | R    |      0 |    8 |  500 |    1482 |         2 |          0 |
| dn1      | 192.168.121.144 | mysql | 192.168.121.144 | 3306 | R    |      0 |    8 |  500 |    1484 |         4 |          0 |
| dn1      | 192.168.121.144 | mysql | 192.168.121.144 | 3306 | R    |      0 |    8 |  500 |    1484 |         4 |          0 |
+----------+-----------------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
5 rows in set (0.00 sec)

mysql> 

到这里配置基本就全完成了。

故障模拟

在142主机上停掉MySQL

systemctl stop mysqld.service 
systemctl status mysqld.service 

查看我们mha的日志，查看主机切换情况

Started automated(non-interactive) failover.
The latest slave 192.168.121.143(192.168.121.143:3306) has all relay logs for recovery.
Selected 192.168.121.143 as a new master.
192.168.121.143: OK: Applying all logs succeeded.
192.168.121.144: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.121.144: OK: Applying all logs succeeded. Slave started, replicating from 192.168.121.143.
192.168.121.143: Resetting slave info succeeded.
Master failover to 192.168.121.143(192.168.121.143:3306) completed successfully.
[1]+  完成                  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已经成功把主机换成143，说明主机的切换成功。
然后监控mycat的日志

2023-09-13 14:28:18.878  INFO [$_NIOConnector] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:520)) - close connection,reason:java.net.ConnectException: 拒绝连接 ,MySQLConnection@1224421014 [id=0, lastTime=1694586498875, user=cat, schema=mycatdb, old shema=mycatdb, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.121.142, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2023-09-13 14:28:18.878 DEBUG [$_NIOREACTOR-7-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:633)) - release channel MySQLConnection@528206028 [id=101, lastTime=1694586498875, user=cat, schema=mycatdb, old shema=mycatdb, borrowed=true, fromSlaveDB=true, threadId=347, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.121.144, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2023-09-13 14:28:18.878  INFO [$_NIOConnector] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql :select user()
2023-09-13 14:28:18.878  WARN [$_NIOConnector] (io.mycat.backend.heartbeat.MySQLDetector.onResult(MySQLDetector.java:208)) - heart beat error: 192.168.121.142/dthost retry=10 tmo=30000
2023-09-13 14:28:18.878 DEBUG [$_NIOConnector] (io.mycat.backend.heartbeat.MySQLHeartbeat.switchSourceIfNeed(MySQLHeartbeat.java:276)) - to  switchSourceIfNeed function 进行读节点转换 
2023-09-13 14:28:18.878  INFO [$_NIOConnector] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionError(PhysicalDatasource.java:508)) - connection connectionError 

可以看到142已经失去连接，但是进行数据的操作还是可以的，我们的mycat没有停掉，还是正常运行的。

在mycat的8066端口插入数据的时候，监控mycat的日志可以看到写请求已经被发送到了143主机

mysql> insert into aa value(6,'mycat');

2023-09-13 14:34:43.696 DEBUG [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:503)) - con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection@313098112 [id=79, lastTime=1694586883696, user=cat, schema=mycatdb, old shema=mycatdb, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into aa value(6,'mycat')}, respHandler=SingleNodeHandler [node=dn1{insert into aa value(6,'mycat')}, packetId=0], host=192.168.121.143, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@79d62400, writeQueue=0, modifiedSQLExecuted=true]
2023-09-13 14:34:43.698 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:386)) - release connection MySQLConnection@313098112 [id=79, lastTime=1694586883695, user=cat, schema=mycatdb, old shema=mycatdb, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into aa value(6,'mycat')}, respHandler=SingleNodeHandler [node=dn1{insert into aa value(6,'mycat')}, packetId=1], host=192.168.121.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
2023-09-13 14:34:43.698 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:633)) - release channel MySQLConnection@313098112 [id=79, lastTime=1694586883695, user=cat, schema=mycatdb, old shema=mycatdb, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.121.143, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

登录144主机的mysql查看主从情况，可以看到主机已经切换到143主机了

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.121.143
                  Master_User: cat
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-slave1.000003
          Read_Master_Log_Pos: 1451
               Relay_Log_File: ljc104-relay-bin.000002
                Relay_Log_Pos: 859
        Relay_Master_Log_File: mysql-slave1.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

而143的mysql数据库的slave信息已经为空了

mysql> show slave status;
Empty set (0.00 sec)

然后重启动我们的142主机的mysql

systemctl start mysqld.service 
systemctl status mysqld.service 

进入mysql数据库，将142设置为143的从库

mysql -uroot -pMySQL@666

stop slave;
change master to master_host='192.168.121.143',master_user='cat',master_password='MySQL@666';
start slave;
show slave status;

这时候有一条报错信息 ，说我们mycatdb数据库已经存在

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.121.143
                  Master_User: cat
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-slave1.000003
          Read_Master_Log_Pos: 1451
               Relay_Log_File: ljc102-relay-bin.000003
                Relay_Log_Pos: 623
        Relay_Master_Log_File: mysql-slave1.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1007
                   Last_Error: Error 'Can't create database 'mycatdb'; database exists' on query. Default database: 'mycatdb'. Query: 'create database mycatdb'

然后我们执行下面这条指令跳过这条信息

stop slave;
set global sql_slave_skip_counter=1;
start slave;
show slave status;

然后还有一条报错信息说我们的表已经存在

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.121.143
                  Master_User: cat
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-slave1.000003
          Read_Master_Log_Pos: 1451
               Relay_Log_File: ljc102-relay-bin.000003
                Relay_Log_Pos: 791
        Relay_Master_Log_File: mysql-slave1.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1050
                   Last_Error: Error 'Table 'aa' already exists' on query. Default database: 'mycatdb'. Query: 'create table aa(id int,name char(15))'

那我们就再跳过一次

stop slave;
set global sql_slave_skip_counter=1;
start slave;
show slave status;

然后报错信息就没有了

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.121.143
                  Master_User: cat
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-slave1.000003
          Read_Master_Log_Pos: 1451
               Relay_Log_File: ljc102-relay-bin.000006
                Relay_Log_Pos: 323
        Relay_Master_Log_File: mysql-slave1.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 

这个是因为主从复制默认帮我们创建库和表，但是我们是用刚才停掉的库来加进去的，所以库和表都存在了，当然就会报错，所有在实际生产环境中，因为故障停掉的库最好就不用了，里面残留着数据，但是主库又在实时更新，容易导致数据不一致而产生问题。

这时候查看这个库的信息，就会发现表里插入了主库现在的信息，但是我们刚才是在第五条数据的时候停掉的，所以刚才残留的数据还在，就产生数据不一致的情况。这时候这个库根本没法用，所以我们得把库删了再重来。

mysql> select * from mycatdb.aa;
+------+------------+
| id   | name       |
+------+------------+
|    1 | webberking |
|    2 | king       |
|    3 | jack       |
|    4 | roules     |
|    5 | tom        |
|    1 | webberking |
|    2 | king       |
|    3 | jack       |
|    5 | dubug      |
|    6 | mycat      |
+------+------------+
10 rows in set (0.00 sec)

mysql> 

我们删掉库重新执行一遍发现还是不行，因为主库切换过，日志信息并不完整，还是没完全同步过来

mysql> drop database mycatdb;
Query OK, 1 row affected (0.01 sec)

mysql> stop slave;\
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='192.168.121.143',master_user='cat',master_password='MySQL@666';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mycatdb.aa;
+------+------------+
| id   | name       |
+------+------------+
|    1 | webberking |
|    2 | king       |
|    3 | jack       |
|    5 | dubug      |
|    6 | mycat      |
+------+------------+
5 rows in set (0.00 sec)

mysql> 

这时候最好的办法就是把主库复制过来再进行主从同步。
先把142mysql上的mycatdb数据库删除

mysql> drop database mycatdb;

然后在新的主库143上把数据库备份过来，严谨一点的话还要锁表，但是我这里只是测试，就不锁表了

mysqldump -ucat -pMySQL@666 -B mycatdb > /opt/mycatdb.sql
scp /opt/mycatdb.sql 192.168.121.142:/root

然后回到142将数据库导入

[root@ljc102 ~]# mysql -uroot -pMySQL@666 < mycatdb.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ljc102 ~]# mysql -uroot -pMySQL@666

这时候再查看数据就和主库一样了

mysql> select * from mycatdb.aa;
+------+------------+
| id   | name       |
+------+------------+
|    1 | webberking |
|    2 | king       |
|    3 | jack       |
|    4 | roules     |
|    5 | tom        |
|    5 | dubug      |
|    6 | mycat      |
+------+------------+
7 rows in set (0.00 sec)

然后继续我们的同步之路
这里要特别注意！！！
这里要特别注意！！！
这里要特别注意！！！

先去143主库的mysql里查询到日志文件和偏移量

mysql> show master status;
+---------------------+----------+--------------+------------------+------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------------+----------+--------------+------------------+------------------------------------------+
| mysql-slave1.000003 |     1451 |              | mysql            | d9e93691-d5ed-11ed-9d02-000c29da9022:1-4 |
+---------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

然后在同步的时候加上两条参数，指定文件和偏移量，防止出现数据不一致的情况

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.121.143',master_user='cat',master_password='MySQL@666',master_log_file='mysql-slave1.000003',master_log_pos=1451;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

然后没有报错就可以了

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.121.143
                  Master_User: cat
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-slave1.000003
          Read_Master_Log_Pos: 1451
               Relay_Log_File: ljc102-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: mysql-slave1.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 

确认一遍数据，没有问题

mysql> select * from mycatdb.aa;
+------+------------+
| id   | name       |
+------+------------+
|    1 | webberking |
|    2 | king       |
|    3 | jack       |
|    4 | roules     |
|    5 | tom        |
|    5 | dubug      |
|    6 | mycat      |
+------+------------+
7 rows in set (0.00 sec)

接下来到mha了 我们的mha和mycat都是在161主机上的
mha的情况是，在故障发生后切换主库后就会自动停掉，然后删除故障数据库的信息

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=MySQL@666
ping_interval=2
repl_password=MySQL@666
repl_user=cat
ssh_user=root
user=cat

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.121.143
port=3306

[server3]
hostname=192.168.121.144
port=3306

所以我们要加上新加的数据，为了防止意外发生，最好将备用节点换到别的主句，发生过故障的主机就不要设为备用节点了

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=MySQL@666
ping_interval=2
repl_password=MySQL@666
repl_user=cat
ssh_user=root
user=cat

[server2]
hostname=192.168.121.143
port=3306

[server3]
candidate_master=1
check_repl_delay=0
hostname=192.168.121.144
port=3306

[server4]
hostname=192.168.121.142
port=3306

检查连接

[root@server1 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
[info] All SSH connection tests passed successfully.
[root@server1 ~]# masterha_check_repl  --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.

没有问题，那就运行mha

[root@server1 ~]# 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 &
[1] 72602
[root@server1 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:72602) is running(0:PING_OK), master:192.168.121.143
[root@server1 ~]# 

可以看到主机也切换成143了

然后到mycat了

这里要修改一下配置信息，不然mycat读写分离就无法完全实现，因为原来读的请求是会落到143上面的，但是现在143成为主了，读请求是不应该会落到143上的，mycat不会默认切换，我们只能手动修改配置

修改schema.xml配置把主机名换一下，142换成143，备用节点143换成144

vim /opt/module/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
        </schema>
        <dataNode name="dn1" dataHost="dthost" database="mycatdb"/>
        <dataHost name="dthost" maxCon="500" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="192.168.121.143" url="192.168.121.142:3306" user="cat" password="MySQL@666">
        <readHost host="192.168.121.144" url="192.168.121.143:3306" user="cat" password="MySQL@666" />
        <readHost host="192.168.121.142" url="192.168.121.144:3306" user="cat" password="MySQL@666" />
        </writeHost>
        <writeHost host="192.168.121.144" url="192.168.121.143:3306" user="cat" password="MySQL@666">
                        <!-- can have multi read hosts -->
         <readHost host="192.168.121.142" url="192.168.121.144:3306" user="cat" password="MySQL@666" />
                </writeHost>
        </dataHost>
</mycat:schema>

然后进入mycat的9066端口刷新一下配置文件信息

mysql -ucat -pMySQL@666 -P9066 -h192.168.121.161

mysql> reload @@config_all;
Query OK, 1 row affected (0.13 sec)
Reload config success

然后查看数据信息

mysql> show @@datasource;
+----------+-----------------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME            | TYPE  | HOST            | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+-----------------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | 192.168.121.143 | mysql | 192.168.121.142 | 3306 | W    |      0 |    0 |  500 |       0 |         0 |          0 |
| dn1      | 192.168.121.144 | mysql | 192.168.121.143 | 3306 | W    |      0 |   10 |  500 |      10 |         0 |          0 |
| dn1      | 192.168.121.144 | mysql | 192.168.121.143 | 3306 | R    |      0 |    0 |  500 |       0 |         0 |          0 |
| dn1      | 192.168.121.142 | mysql | 192.168.121.144 | 3306 | R    |      0 |    0 |  500 |       0 |         0 |          0 |
| dn1      | 192.168.121.142 | mysql | 192.168.121.144 | 3306 | R    |      0 |    0 |  500 |       0 |         0 |          0 |
+----------+-----------------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
5 rows in set (0.00 sec)

可以看到我们集群的情况已经完成， 和上面没发生故障的时候对比，142只能接受读请求了，主机的请求信息已经发生了改变，到这里所有配置就完成了，实现了不停机维护mysql的mha+myat读写分离。

写这篇文章的时候也有参考一些其它文章，如有侵权请联系告知删除。