MYCAT实战之分片迁移
实践扩容
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
<mycat:schema xmlns:mycat="http://org.opencloudb/">
</mycat:schema>
5.2 配置 rule.xml
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
<mycat:schema xmlns:mycat="http://org.opencloudb/">
</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上的库表
至此迁移完成
浙公网安备 33010602011771号