Mycat学习笔记第三篇. MySql 主从同步异常后，主从切换

最新推荐文章于 2022-07-11 18:59:04 发布

HKaye

最新推荐文章于 2022-07-11 18:59:04 发布

阅读量386

点赞数

分类专栏： MySql MyCat Linux

本文链接：https://blog.csdn.net/kaye0110/article/details/50722590

版权

MySql 同时被 3 个专栏收录

44 篇文章 0 订阅

订阅专栏

MyCat

27 篇文章 1 订阅

订阅专栏

Linux

27 篇文章 0 订阅

订阅专栏

1）系统环境说明

MySql 5.5 主从节点

127.0.0.1：3306 主结点，为验证主从切换效果，手动停止服务

127.0.0.1: 3307 从结点 1

127.0.0.1：338 从结点 2 ，为验证主从切换效果，在主结点停止后，新增两个记录。

MyCat 1.5 schema.xml 配置

具体配置说明，参考上篇：《Mycat学习笔记》第二篇. MySql 读写分离与日志分析——主从多结点

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
　　writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
　　<heartbeat>show slave status</heartbeat>
　　
　　<writeHost host="hostM1" url="localhost:3306" user="root" password="root123">
　　　　
　　　　<readHost host="hostS1" url="localhost:3307" user="root" password="root123" />
　　</writeHost>

2）整体切换流程

原主机（3306）停止服务
MyCat 自动检测进行主从切换（hostM1转为hostM2）
通过mycat insert data，数据只在 3308 的数据库中体现
启动原主机mysql 3306 服务
手动同步数据 mysql workbench （暂时不知道别的方法，）
调整mysql master 和 slave的关系，将3308设为主机，3306设为从机，3307配置不动
通过mycat 验证新的主从配置
mycat 不用重启，服务会自动切换

3）具体操作步骤

3.1）停止3308 环境 slave模式并开启 master

停止slave

mysql> stop slave;

配置3308为主机模式

mysql> GRANT REPLICATION SLAVE ON *.* to 'mycat_sync_3308'@'%' identified by 'mycat_sync_3308';
Query OK, 0 rows affected (0.00 sec)

3.2）3306 环境开启slave ，数据库其他备份方式参考第4点

从3308 服务中导出新增的记录

mysql> select * from aaa where id > 7 into outfile 'D:/bin/mysql/MySQL_3308/dump/aaa_20160118.data'

3306 服务中导入数据

mysql> load data infile 'D:/bin/mysql/MySQL_3308/dump/aaa_20160118.data' into t
able aaa;
Query OK, 2 rows affected (0.04 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

设置3306 为 3308 的从机

mysql> change master to master_host='192.168.1.247'
-> ,master_port=3308
-> ,master_user='mycat_sync_3308'
-> ,master_password='mycat_sync_3308'
-> ,master_log_file='mysql-bin.000005'
-> ,master_log_pos=446;
Query OK, 0 rows affected (0.03 sec)

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

3.3）mycat 验证

登陆mycat 8066口，新增数据

mysql> use TESTDB
Database changed
mysql> insert into aaa (id,context) values (10,'master3308 slave3306 slave3307')
;
Query OK, 1 row affected (0.02 sec)

通过mysql 主机（3308）确认

mysql> select * from aaa;
+----+--------------------------------+
| id | context |
+----+--------------------------------+
| 1 | hello 1 |
| 2 | hello 2 |
| 3 | hello3 |
| 4 | hello4 |
| 5 | hell world5 |
| 6 | new mysql 3308 |
| 7 | insert by M1 |
| 8 | insert by M2 |
| 9 | need to sync |
| 10 | master3308 slave3306 slave3307 |
+----+--------------------------------+
10 rows in set (0.00 sec)

通过mysql 从机 (3306) 确认

通过 mysql 从机 3307 确认（原3306的从机）。发现数据没有同步更新，那就还需要再把3307的数据同步服务指向3308，具体操作如上。

mysql> select * from aaa;
+----+----------------+
| id | context |
+----+----------------+
| 1 | hello 1 |
| 2 | hello 2 |
| 3 | hello3 |
| 4 | hello4 |
| 5 | hell world5 |
| 6 | new mysql 3308 |
| 7 | insert by M1 |
| 8 | insert by M2 |
| 9 | need to sync |
+----+----------------+
9 rows in set (0.00 sec)

3.4）再来看下mycat 日志文件 logs/mycat.log

insert 操作使用 3308 服务进行处理

01/18 14:37:16.417 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=4, schema=TESTDB, host=0:0:0:0:0:0:0:1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into aaa (id,context) values (10,'master3308 slave3306 slave3307')

01/18 14:37:16.429 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=4, schema=TESTDB, host=0:0:0:0:0:0:0:1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into aaa (id,context) values (10,'master3308 slave3306 slave3307'), route={
1 -> dn1{insert into aaa (id,context) values (10,'master3308 slave3306 slave3307')}
} rrs
01/18 14:37:16.432 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=2, lastTime=1453099036418, user=root, schema=mycat_sync_test, old shema=mycat_sync_test, borrowed=true, fromSlaveDB=false, threadId=8, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into aaa (id,context) values (10,'master3308 slave3306 slave3307')}, respHandler=SingleNodeHandler [node=dn1{insert into aaa (id,context) values (10,'master3308 slave3306 slave3307')}, packetId=0], host=localhost, port=3308, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

数据库查询操作已切换至3306服务中，确认mycat 已自动检测到3306恢复正常

01/18 14:44:52.547 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect * from aaa
01/18 14:44:52.564 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect * from aaa value:select * from aaa, route={
1 -> dn1{SELECT *
FROM aaa
LIMIT 100}
}
01/18 14:44:52.564 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=4, schema=TESTDB, host=0:0:0:0:0:0:0:1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from aaa, route={
1 -> dn1{SELECT *
FROM aaa
LIMIT 100}
} rrs
01/18 14:44:52.564 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source hostM1 for dataHost:localhost1
01/18 14:44:52.564 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=15, lastTime=1453099492564, user=root, schema=mycat_sync_test, old shema=mycat_sync_test, borrowed=true, fromSlaveDB=false, threadId=1, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{SELECT *
FROM aaa
LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *
FROM aaa
LIMIT 100}, packetId=0], host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/18 14:44:52.565 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=15, lastTime=1453099492560, user=root, schema=mycat_sync_test, old shema=mycat_sync_test, borrowed=true, fromSlaveDB=false, threadId=1, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM aaa
LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *
FROM aaa
LIMIT 100}, packetId=14], host=localhost, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@1d516b8, writeQueue=0, modifiedSQLExecuted=false]

4）数据库备份策略

mysql备份的三种方式详解

http://www.jb51.net/article/41570.htm

HKaye

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mycat学习笔记第三篇. MySql 主从同步异常后，主从切换

1）系统环境说明MySql 5.5 主从节点127.0.0.1：3306 主结点，为验证主从切换效果，手动停止服务127.0.0.1: 3307 从结点 1 127.0.0.1：338 从结点 2 ，为验证主从切换效果，在主结点停止后，新增两个记录。 MyCat 1.5 schema.xml 配置具体配置说明，参考上篇：《Mycat学习笔记》第二篇
复制链接

扫一扫