mycat 分片数据平滑迁移实施过程
来源：互联网 发布：淘宝txt 编辑：程序博客网 时间：2024/06/10 23:08
1，实践扩容
travelrecord表定义为10个分片，尝试将10个分片中的2个分片转移到第二台MySQL上，并完成记录
要求，最快的数据迁移做法，中断业务时间最短
2，思路说明
2.1，10个分片的环境准备
我的理解里面，分片就代表了集群，一个分片就是一个实例，迁移2个分片，就是迁移2个实例到新的环境上面，但是由于测试没法准备那么多10个实例，所以用一个database来代表一个实例一个分片，这样测试起来方便简单快捷得多；
2.2，数据迁移最快、时间最短
要中断业务时间最短，那么只有第二个实例上也有在实际生产环境中，也就是说，基本在数据迁移这中间时间最短，这种目前可以用mysql主从的机制来实现，因为主从同步延迟基本上是1秒以下，基本能满足需求。
1). 在迁移前，修改mycat配置。
2). 将第二台MySQL实例作为第一个Master的slave，注意，只同步db5、db6这两个分片。
3). 等同步完成后在业务量较小的时候，重新加载mycat
4). 登录第一个Master节点，手动删除两个分片的数据。
2.3，服务器准备：
名称
Ip地址
端口
Mycat
192.168.121.52
8066
Mysql 主
192.168.121.61
3306
Mysql从
192.168.121.12
3307
3，mysql5.6.12主从搭建
Mysql5.6.12安装：http://blog.csdn.net/mchdba/article/details/35994251
主从搭建：
A）在主库（121.61）上创建复制用户：GRANT REPLICATION SLAVE ON *.* TO 'repl_mycat'@'192.168.%' IDENTIFIED BY 'replmycattest'; grant all on *.* to root@'192.168.%' identified by '';
B）在主库上（121.61）获取复制position：show master status;
C）在从上，修改my.cnf配置文件，只同步2个分片的数据，
replicate-do-db=db5
replicate-do-db=db6
D）在从库上（121.12）开始搭建复制：
stop slave;
reset slave;
change master to master_user='repl_mycat', master_password='replmycattest', master_host='192.168.121.61',master_port=3306, master_log_file='mysql-bin.025055',master_log_pos=120;
start slave;
show slave status\G
原blog地址：http://blog.csdn.net/mchdba/article/details/50991649，未经过原作者mchdba（黄杉）同意，谢绝转载。
4，准备在主库上建好10个分片
这里如果按照实际是需要10个mysql实例的，因为一个实例一个分片，我这里仅仅是功能测试，所以一时没有那么多实例，就用了实例上的多个db库来代替，达到类似多个分片的效果，可以理解一个库一个分片，db1就是第一个分片，db2就是第二个分片，db3就是第三个分片……db10就是第10个分片，在主库上192.168.121.61执行，会自动同步到从库192.168.121.12上面去。
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)
mysql> create database db4;
Query OK, 1 row affected (0.00 sec)
mysql> create database db5;
Query OK, 1 row affected (0.01 sec)
mysql> create database db6;
Query OK, 1 row affected (0.00 sec)
mysql> create database db7;
Query OK, 1 row affected (0.00 sec)
mysql> create database db8;
Query OK, 1 row affected (0.00 sec)
mysql> create database db9;
Query OK, 1 row affected (0.00 sec)
mysql> create database db10;
Query OK, 1 row affected (0.00 sec)
mysql>
5，准备mycat配置文件
Schema.xml迁移前的配置（迁移前是如下配置，迁移后会有变化） ：
<!— 配置表 -->
<table name="travelrecord" dataNode="dn$1-10" rule="auto-sharding-long" />
<!— 配置分片 -->
<dataNode name="dn1" dataHost="dh1" database="db1" />
<dataNode name="dn2" dataHost="dh1" database="db2" />
<dataNode name="dn3" dataHost="dh1" database="db3" />
<dataNode name="dn4" dataHost="dh1" database="db4" />
<dataNode name="dn5" dataHost="dh1" database="db5" />
<dataNode name="dn6" dataHost="dh1" database="db6" />
<dataNode name="dn7" dataHost="dh1" database="db7" />
<dataNode name="dn8" dataHost="dh1" database="db8" />
<dataNode name="dn9" dataHost="dh1" database="db9" />
<dataNode name="dn10" dataHost="dh1" database="db10" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.121.61:3306" user="root" password=""/>
</dataHost>
分片规则文件：
[root@oracle_standby conf]# more autopartition-long.txt
0-10=0
10-20=1
20-30=2
30-40=3
40-50=4
50-60=5
60-70=6
70-80=7
80-90=8
90-100=9
[root@oracle_standby conf]#
之后启动mycat。
6，往10个分片里面录入测试数据
准备工作：
建表sql：
CREATE TABLE `travelrecord` (
`id` bigint(20) NOT NULL,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
录入测试数据：
insert into travelrecord(id,user_id,traveldate,fee,days) values(1,'000001',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(3,'000001',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(11,'110002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(13,'130002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(21,'210003',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(23,'230003',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(31,'310002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(33,'330002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(41,'410002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(43,'430002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(51,'510002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(53,'530002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(61,'610002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(63,'630002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(71,'710002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(73,'730002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(81,'810002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(83,'830002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(91,'910002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(93,'930002',now(),1235,6);
执行过程：
执行过程如下：
mysql> CREATE TABLE `travelrecord` (
-> `id` bigint(20) NOT NULL,
-> `user_id` varchar(100) DEFAULT NULL,
-> `traveldate` date DEFAULT NULL,
-> `fee` decimal(10,0) DEFAULT NULL,
-> `days` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(1,'000001',now(),1235,6);
now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(13,'130002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(21,'210003',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(23,'230003',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(31,'310002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(33,'330002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(41,'410002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(43,'430002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(51,'510002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(53,'530002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(61,'610002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(63,'630002',now(),1235,6);
insert iQuery OK, 1 row affected, 1 warning (0.02 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(3,'000001',now(),1235,6);
nto travelrecord(id,user_id,traveldate,fee,days) values(71,'710002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(73,'730002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(81,'810002',now(),Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(11,'110002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(13,'130002',now(),1235,6);
1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(83,'830002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(91,'910002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(9Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(21,'210003',now(),1235,6);
3,'930002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(101,'1010002',now(),1235,6);
insert into travelrecord(id,user_id,traveldate,fee,days) values(103,'1030002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(23,'230003',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(31,'310002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(33,'330002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(41,'410002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(43,'430002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(51,'510002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(53,'530002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(61,'610002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(63,'630002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(71,'710002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(73,'730002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(81,'810002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(83,'830002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(91,'910002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(93,'930002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql>
查看执行路由导向：
mysql> explain select * from travelrecord; +-----------+--------------------------------------+| DATA_NODE | SQL |+-----------+--------------------------------------+| dn1 | SELECT * FROM travelrecord LIMIT 100 || dn10 | SELECT * FROM travelrecord LIMIT 100 || dn2 | SELECT * FROM travelrecord LIMIT 100 || dn3 | SELECT * FROM travelrecord LIMIT 100 || dn4 | SELECT * FROM travelrecord LIMIT 100 || dn5 | SELECT * FROM travelrecord LIMIT 100 || dn6 | SELECT * FROM travelrecord LIMIT 100 || dn7 | SELECT * FROM travelrecord LIMIT 100 || dn8 | SELECT * FROM travelrecord LIMIT 100 || dn9 | SELECT * FROM travelrecord LIMIT 100 |+-----------+--------------------------------------+10 rows in set (0.00 sec) mysql>
去mysql实例上验证数据已经分到10个分片上了：
mysql> select * from db1.travelrecord;+----+---------+------------+------+------+| id | user_id | traveldate | fee | days |+----+---------+------------+------+------+| 1 | 000001 | 2016-03-27 | 1235 | 6 || 3 | 000001 | 2016-03-27 | 1235 | 6 |+----+---------+------------+------+------+2 rows in set (0.00 sec) mysql> select * from db2.travelrecord;+----+---------+------------+------+------+| id | user_id | traveldate | fee | days |+----+---------+------------+------+------+| 11 | 110002 | 2016-03-27 | 1235 | 6 || 13 | 130002 | 2016-03-27 | 1235 | 6 |+----+---------+------------+------+------+2 rows in set (0.00 sec)mysql> select * from db5.travelrecord;+----+---------+------------+------+------+| id | user_id | traveldate | fee | days |+----+---------+------------+------+------+| 41 | 410002 | 2016-03-27 | 1235 | 6 || 43 | 430002 | 2016-03-27 | 1235 | 6 |+----+---------+------------+------+------+2 rows in set (0.00 sec) mysql>mysql> select * from db9.travelrecord;+----+---------+------------+------+------+| id | user_id | traveldate | fee | days |+----+---------+------------+------+------+| 81 | 810002 | 2016-03-27 | 1235 | 6 || 83 | 830002 | 2016-03-27 | 1235 | 6 |+----+---------+------------+------+------+2 rows in set (0.00 sec) mysql> select * from db10.travelrecord;+----+---------+------------+------+------+| id | user_id | traveldate | fee | days |+----+---------+------------+------+------+| 91 | 910002 | 2016-03-27 | 1235 | 6 || 93 | 930002 | 2016-03-27 | 1235 | 6 |+----+---------+------------+------+------+2 rows in set (0.00 sec) mysql>
7，mycat分片迁移前配置文件修改
要将db5、db6这2个分片切到第二个实例，也就是从库192.168.121.12上面去，都在schema.xml里面修改：
新添加从库的dataHost配置：
<dataHost name="dh2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="192.168.121.12:3307" user="root" password=""/> </dataHost>
修改db5、db6分片的位置：
<dataNode name="dn5" dataHost="dh2" database="db5" /> <dataNode name="dn6" dataHost="dh2" database="db6" />
8，在业务量小的时候，比如凌晨，进行切换
先在db5、db6之上做flush table命令将db5.travelrecord、db6.travelrecord内存中数据刷到磁盘中，并重新加载mycat：
A）先在dh1上192.168.121.61刷磁盘
mysql> flush table db2.travelrecord,db3.travelrecord with read lock; Query OK, 0 rows affected (0.00 sec) mysql>
B）然后重新加载mycat，使改动的新配置的分片到新实例上生效
mysql> reload @@config;ERROR 1064 (HY000): com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'reload @@config',expect IDENTIFIER, actual IDENTIFIER reloadmysql>
WHY？我的reload失败了？，看来只有restart了
[root@oracle_standby logs]# ../bin/mycat restartStopping Mycat-server...Stopped Mycat-server.Starting Mycat-server...[root@oracle_standby logs]#
C）之后去dh1上解锁表
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql>
D）然后验证mycat上刀片完整性，如下是完整的
mysql> explain select * from travelrecord;
+-----------+--------------------------------------+| DATA_NODE | SQL |+-----------+--------------------------------------+| dn1 | SELECT * FROM travelrecord LIMIT 100 || dn10 | SELECT * FROM travelrecord LIMIT 100 || dn2 | SELECT * FROM travelrecord LIMIT 100 || dn3 | SELECT * FROM travelrecord LIMIT 100 || dn4 | SELECT * FROM travelrecord LIMIT 100 || dn5 | SELECT * FROM travelrecord LIMIT 100 || dn6 | SELECT * FROM travelrecord LIMIT 100 || dn7 | SELECT * FROM travelrecord LIMIT 100 || dn8 | SELECT * FROM travelrecord LIMIT 100 || dn9 | SELECT * FROM travelrecord LIMIT 100 |+-----------+--------------------------------------+10 rows in set (0.20 sec) mysql>
E）去看mycat.log后台日志，发现db5、db6已经连接到新的实例192.168.121.12:3307上面了
03/27 14:10:58.433 INFO [$_NIOREACTOR-1-RW] (GetConnectionHandler.java:66) -connected successfuly MySQLConnection [id=11, lastTime=1459059058433, user=root, schema=db6, old shema=db6, borrowed=true, fromSlaveDB=false, threadId=216, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.121.12, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/27 14:10:58.434 INFO [$_NIOREACTOR-1-RW] (GetConnectionHandler.java:66) -connected successfuly MySQLConnection [id=13, lastTime=1459059058433, user=root, schema=db5, old shema=db5, borrowed=true, fromSlaveDB=false, threadId=218, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.121.12, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
9，录入新的数据，验证下
在mycat窗口执行录入：
mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(45,'450002',now(),1235,6);
Query OK, 1 row affected, 1 warning (0.03 sec) mysql> insert into travelrecord(id,user_id,traveldate,fee,days) values(55,'550002',now(),1235,6);Query OK, 1 row affected, 1 warning (0.01 sec) mysql>
去第一个旧实例上192.168.121.61，查询不到新记录：
[root@oracle_standby logs]# mysql -uroot -p -h192.168.121.61 -P3306 -e "select * from db5.travelrecord where id=45; select * from db6.travelrecord where id=55; "
Enter password:[root@oracle_standby logs]#
去第二个新实例192.168.121.12上，可以查到新记录：
[root@oracle_standby logs]# mysql -uroot -p -h192.168.121.12 -P3307 -e "select * from db5.travelrecord where id=45; select * from db6.travelrecord where id=55; "
Enter password:+----+---------+------------+------+------+| id | user_id | traveldate | fee | days |+----+---------+------------+------+------+| 45 | 450002 | 2016-03-27 | 1235 | 6 |+----+---------+------------+------+------++----+---------+------------+------+------+| id | user_id | traveldate | fee | days |+----+---------+------------+------+------+| 55 | 550002 | 2016-03-27 | 1235 | 6 |+----+---------+------------+------+------+[root@oracle_standby logs]#
10，去原来的实例上删除分片db5、db6
[root@oracle_standby logs]# mysql -uroot -p -h192.168.121.61 -P3306 -e "drop database db5; drop database db6;"
Enter password:[root@oracle_standby logs]#
至此，mycat分片迁移测试完毕。
这种操作的缺陷是，with read lock;的时候会锁表，短暂影响业务的正常使用。
解决思路是：如果想不锁表不影响业务的话，就不做with read lock但是切换后手工分析旧实例中的binlog，去找出db5分片db6分片的dml、ddl操作，然后重新在新的实例中去手工恢复下。
- mycat 分片数据平滑迁移实施过程
- mycat 分片中快速数据迁移思考
- 利用MyCAT实现数据分片
- mycat数据分片 之 通过HINT（注解）执行存储过程
- Mycat系列之一：数据分片入门实战
- mycat分片
- Mycat学习实战-Mycat分片
- Mycat 数据分片--取模函数源码阅读
- Mycat之——数据分片入门实战
- mongodb 分片群集配置 及数据迁移
- mycat 离散分片 -> 枚举分片
- mycat 离散分片(枚举分片)
- mycat分片JOIN，分片规则
- 笔记：数据平滑扩容和迁移
- 互联网“平滑数据迁移”架构技术实践
- mycat+mysql分片
- mycat分片规则
- Mycat 月分片方法
- Linux内核协议栈（附2）内核如何通过file对象找到对应的socket对象？
- Thinking in Java读书笔记
- HDU 4323 Magic Number 编辑距离
- JAVA学习初步 级数求和
- 获得Java类的方法类型签名
- mycat 分片数据平滑迁移实施过程
- socket编程入门
- 加载json数据创建控制器
- 动态排序和动态查询条件
- Light OJ 1047 Neighbor House
- JavaScript学习笔记一
- According to TLD or attribute directive in tag file, attribute value does not accept any expressions
- Java equals方法
- Nginx学习笔记——配置简单代理服务器
