实践扩容
1.要求:
travelrecord 表定义为10个分片,尝试将10个分片中的 2 个分片转移到第二台MySQL上,
并完成记录要求,最快的数据迁移做法,中断业务时间最短
2.针对分片以及迁移方式
mycat中分片可以理解为dbn
而dbn可以是单独datahost中的某个database,
也可以是一个datahost上的mysql实例中多个database.
迁移方式:
1、如果dbn是对应mysql实例中唯一database,迁移可以采用 mha+vip 方式快速迁移，
这种方式很快，线上实际生产环境几秒可以实现切换
2、如果dbn是对应mysql中多个 database 中一个，这个时候采用 mha+vip 方式不太合适,
这个时候可以采用手动启动 slave(保证数据一致性的话，需要手动对 master加上 read lock(5.6 使用 lock),
5.7 可以设置 super_read_only and read_ony=on，这个会影响所有表)
由于测试环境问题，采用第二种方式,第一种方式相对简单.
3. 环境说明
192.168.2.136 mycat1
192.168.2.134 mydb1
192.168.2.135 mydb2
4. mysql主从搭建（略）
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.134
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000010
Read_Master_Log_Pos: 120
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 280
Relay_Master_Log_File: binlog.000010
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:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 446
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 101
Master_UUID: 11764feb-b3df-11e7-ad1a-000c29962dd5
Master_Info_File: /MySQL/my3306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
5.配置mycat
5.1 配置 schema.xml--10 个分片
[root@mycat conf]# vi schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="mycatbymonth" primaryKey="id"
dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10" rule="sharding-by-month" />
</schema>
<dataNode name="dn1" dataHost="mysqlserver" database="db1" />
<dataNode name="dn2" dataHost="mysqlserver" database="db2" />
<dataNode name="dn3" dataHost="mysqlserver" database="db3" />
<dataNode name="dn4" dataHost="mysqlserver" database="db4" />
<dataNode name="dn5" dataHost="mysqlserver" database="db5" />
<dataNode name="dn6" dataHost="mysqlserver" database="db6" />
<dataNode name="dn7" dataHost="mysqlserver" database="db7" />
<dataNode name="dn8" dataHost="mysqlserver" database="db8" />
<dataNode name="dn9" dataHost="mysqlserver" database="db9" />
<dataNode name="dn10" dataHost="mysqlserver" database="db10" />
<!--######### TESTDB ########-->
<dataHost name="mysqlserver" 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.2.134:3306" user="root" password="root123">
</writeHost>
</dataHost>
</mycat:schema>
5.2 配置 rule.xml
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<function name="partbymonth"
class="org.opencloudb.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2017-12-01</property>
<property name="sEndDate">2018-12-01</property>
</function>
5.3 重新加载配置文件
[mysql@localhost ~]$ mysql -utest -ptest -h192.168.2.136 -P9066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (monitor)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
mysql>
mysql>
mysql> reload @@config;
Reload config success
5.4 验证一下
[mysql@localhost ~]$ mysql -utest -ptest -h192.168.2.136 -P8066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
mysql>
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB;
Database changed
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| mycatbymonth |
+------------------+
6 在逻辑库创建表插入数据
CREATE TABLE `mycatbymonth` (
`id` int(11) NOT NULL ,
`create_time` datetime DEFAULT NULL,
`datanode` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> explain select * from mycatbymonth;
+-----------+--------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------+
| dn1 | SELECT * FROM mycatbymonth LIMIT 100 |
| dn10 | SELECT * FROM mycatbymonth LIMIT 100 |
| dn2 | SELECT * FROM mycatbymonth LIMIT 100 |
| dn3 | SELECT * FROM mycatbymonth LIMIT 100 |
| dn4 | SELECT * FROM mycatbymonth LIMIT 100 |
| dn5 | SELECT * FROM mycatbymonth LIMIT 100 |
| dn6 | SELECT * FROM mycatbymonth LIMIT 100 |
| dn7 | SELECT * FROM mycatbymonth LIMIT 100 |
| dn8 | SELECT * FROM mycatbymonth LIMIT 100 |
| dn9 | SELECT * FROM mycatbymonth LIMIT 100 |
+-----------+--------------------------------------+
10 rows in set (0.23 sec)
#插入数据
insert into mycatbymonth (id,create_time,datanode) values(1,'2017-12-12',@database);
insert into mycatbymonth (id,create_time,datanode) values(2,'2018-01-12',@database);
insert into mycatbymonth (id,create_time,datanode) values(3,'2018-02-12',@database);
insert into mycatbymonth (id,create_time,datanode) values(4,'2018-03-12',@database);
insert into mycatbymonth (id,create_time,datanode) values(5,'2018-04-12',@database);
insert into mycatbymonth (id,create_time,datanode) values(6,'2018-05-12',@database);
insert into mycatbymonth (id,create_time,datanode) values(7,'2018-06-12',@database);
insert into mycatbymonth (id,create_time,datanode) values(8,'2018-07-12',@database);
insert into mycatbymonth (id,create_time,datanode) values(9,'2017-08-12',@database);
insert into mycatbymonth (id,create_time,datanode) values(10,'2018-09-12',@database);
7 修改 9，10 分片到 mydb2 修改 schema.xml 配置
[root@mycat conf]# vi schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="mycatbymonth" primaryKey="id"
dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10" rule="sharding-by-month" />
</schema>
<dataNode name="dn1" dataHost="mysqlserver" database="db1" />
<dataNode name="dn2" dataHost="mysqlserver" database="db2" />
<dataNode name="dn3" dataHost="mysqlserver" database="db3" />
<dataNode name="dn4" dataHost="mysqlserver" database="db4" />
<dataNode name="dn5" dataHost="mysqlserver" database="db5" />
<dataNode name="dn6" dataHost="mysqlserver" database="db6" />
<dataNode name="dn7" dataHost="mysqlserver" database="db7" />
<dataNode name="dn8" dataHost="mysqlserver" database="db8" />
<dataNode name="dn9" dataHost="mysqlserver2" database="db9" />
<dataNode name="dn10" dataHost="mysqlserver2" database="db10" />
<!--######### TESTDB ########-->
<dataHost name="mysqlserver" 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.2.134:3306" user="root" password="root123">
</writeHost>
</dataHost>
<dataHost name="mysqlserver2" 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.2.135:3306" user="root" password="root123">
</writeHost>
</dataHost>
</mycat:schema>
8 业务切换
8.1 对 db9 和 db10 上表进行加锁
mysql> select * from db9.mycatbymonth;
+----+---------------------+----------+
| id | create_time | datanode |
+----+---------------------+----------+
| 9 | 2018-08-12 00:00:00 | NULL |
+----+---------------------+----------+
1 row in set (0.00 sec)
mysql> select * from db10.mycatbymonth;
+----+---------------------+----------+
| id | create_time | datanode |
+----+---------------------+----------+
| 10 | 2018-09-12 00:00:00 | NULL |
+----+---------------------+----------+
1 row in set (0.00 sec)
mysql> flush table db9.mycatbymonth,db10.mycatbymonth with read lock
8.2 重新加载 mycat
** 注释涉及 dbn 修改需要重新加载所有**
[mysql@localhost ~]$ mysql -utest -ptest -h192.168.2.136 -P9066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 6
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (monitor)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
mysql>
mysql> reload @@config_all;
Query OK, 1 row affected (0.37 sec)
Reload config success
8.3 mydb2 reset slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)
8.4 通过日志验证新路由是否生效
8.5 插入新数据是否在新库mydb2上
mysql> select * from db10.mycatbymonth;
+----+---------------------+----------+
| id | create_time | datanode |
+----+---------------------+----------+
| 10 | 2018-09-12 00:00:00 | NULL |
+----+---------------------+----------+
1 row in set (0.01 sec)
mysql> select * from db9.mycatbymonth;
+----+---------------------+----------+
| id | create_time | datanode |
+----+---------------------+----------+
| 9 | 2018-08-12 00:00:00 | NULL |
+----+---------------------+----------+
1 row in set (0.00 sec)
插入数据后验证：
#mycat上执行插入
mysql> insert into mycatbymonth (id,create_time,datanode) values(11,'2018-09-14',@database);
Query OK, 1 row affected (0.02 sec)
mydb2上查询对应分片
mysql> select * from db10.mycatbymonth;
+----+---------------------+----------+
| id | create_time | datanode |
+----+---------------------+----------+
| 10 | 2018-09-12 00:00:00 | NULL |
| 11 | 2018-09-14 00:00:00 | NULL |
+----+---------------------+----------+
2 rows in set (0.00 sec)
可以看到数据已经插入到mydb2上了
9、删除mydb1上的库表
至此迁移完成