mysql分片迁移_MYCAT实战之分片迁移

最新推荐文章于 2025-06-19 14:06:07 发布

原创最新推荐文章于 2025-06-19 14:06:07 发布 · 293 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql分片迁移

本文介绍了使用Mycat进行MySQL分片迁移的详细步骤，包括在保持业务中断最短的情况下，将2个分片从一台MySQL迁移到另一台。主要涉及Mycat的配置修改、数据锁定、主从切换等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实践扩容

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

dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10" rule="sharding-by-month" />

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

5.2 配置 rule.xml

create_time

partbymonth

class="org.opencloudb.route.function.PartitionByMonth">

yyyy-MM-dd

2017-12-01

2018-12-01

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)

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> 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)

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> 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

dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10" rule="sharding-by-month" />

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

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)

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> 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上的库表

至此迁移完成