JAVA高级面试进阶训练营视频教程

admin

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>
　　<!-- can have multi write hosts -->
　　<writeHost host="hostM1" url="localhost:3306" user="root" password="root123">
　　　　<!-- can have multi read hosts -->
　　　　<readHost host="hostS1" url="localhost:3307" user="root" password="root123" />
　　</writeHost>

　　<writeHost host="hostM2" url="localhost:3308" user="root" password="root123"></writeHost>

</dataHost>

 

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)

 

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 446 | | |
+------------------+----------+--------------+------------------+
1 row in set (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> 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 从机 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