如何从MySQL数据库迁移数据到OceanBase
今天我们来看一下如何处理MySQL数据库迁移,因为OceanBase基本支持MySQL的语法,所以迁移过程会比较顺滑。1.在OceanBase中创建一个新租户首先我们依照OB的逻辑,创建一个新租户,包含如下步骤:创建一个资源单元 unit1创建一个资源池 pool1创建一个租户 test_tenant使用上面创建的资源池和资源单元MySQL [oceanbase]> CREATE RESOU
今天我们来看一下如何处理MySQL数据库迁移,因为OceanBase基本支持MySQL的语法,所以迁移过程会比较顺滑。
1.在OceanBase中创建一个新租户
首先我们依照OB的逻辑,创建一个新租户,包含如下步骤:
- 创建一个资源单元 unit1
- 创建一个资源池 pool1
- 创建一个租户 test_tenant使用上面创建的资源池和资源单元
MySQL [oceanbase]> CREATE RESOURCE UNIT unit1 MAX_CPU 1, MAX_MEMORY '1G', MAX_IOPS 128,MAX_DISK_SIZE '10G', MAX_SESSION_NUM 64, MIN_CPU=1, MIN_MEMORY='1G', MIN_IOPS=128;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> CREATE RESOURCE POOL pool1 UNIT='unit1',UNIT_NUM=1,ZONE_LIST=('zone1','zone2','zone3');
Query OK, 0 rows affected (0.03 sec)
MySQL [oceanbase]> CREATE TENANT IF NOT EXISTS test_tenant CHARSET='utf8mb4',ZONE_LIST=('zone1','zone2','zone3'), PRIMARY_ZONE='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1') SET ob_tcp_invited_nodes='%' ;
Query OK, 0 rows affected (1.12 sec)
创建完租户后,在该租户下创建一个数据库 test2 :
# 注意下面的命令连接到了test_tenant租户
[admin@obproxy ~]$ mysql -h 10.211.55.54 -uroot@test_tenant -P2883 -c -A oceanbase
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [oceanbase]> CREATE DATABASE test2 DEFAULT CHARACTER SET UTF8;
Query OK, 1 row affected (0.02 sec)
MySQL [oceanbase]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
| test2 |
+--------------------+
5 rows in set (0.01 sec)
2.采用DBeaver连接上数据库看看
连接过程比较简单,主要还是要理解用户、租户和数据库的关系才行,下面直接放一张图:
3.导出MySQL表结构
我这里在mariadb里面已经有一个提前创建好的数据库:gfva,给大家看一下里面的表:
接下来,使用mysqldump命令导出数据,这个操作我是在obproxy那台主机上做的,因为之前装了mysql的命令,所以比较方便:
[admin@obproxy ~]$ mysqldump -h 192.168.0.200 -uroot -P3306 -p -d gfva --compact > gfva_ddl.sql
Enter password:
# 文件内容节选:
[admin@obproxy ~]$ head gfva_ddl.sql
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `casbin_rule` (
`p_type` varchar(100) DEFAULT NULL,
`v0` varchar(100) DEFAULT NULL,
`v1` varchar(100) DEFAULT NULL,
`v2` varchar(100) DEFAULT NULL,
`v3` varchar(100) DEFAULT NULL,
`v4` varchar(100) DEFAULT NULL,
`v5` varchar(100) DEFAULT NULL
4.表结构恢复到OceanBase
OceanBase有一些语法不兼容MySQL,脚本需要略作调整,请根据链接中的介绍进行修改即可:https://open.oceanbase.com/docs/tutorials/quickstart/V1.0.0/4-2
采用obclient登录数据库,用 source
命令手动调用脚本:
[admin@obproxy ~]$ obclient -h 10.211.55.54 -uroot@test_tenant -P2883 -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.6.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [oceanbase]> use test2;
Database changed
MySQL [test2]> source /home/admin/gfva_ddl.sql
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected, 1 warning (0.083 sec)
Query OK, 0 rows affected (0.101 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected, 1 warning (0.063 sec)
Query OK, 0 rows affected (0.101 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.000 sec)
从下图可以看到,表结构都已经恢复到test_tenant租户的test2数据库中了:
5.配置数据同步
下面我们以表 casbin_rule
为例进行数据同步配置,我这里已经提前准备好了datax-web的环境。
首先如下配置,分别把mysql源头和ob目标链接配置好:
然后我们依据以下json创建并启动同步任务:
{
"job": {
"setting": {
"speed": {
"channel": 3,
"byte": 1048576
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "yRjwDFuoPKlqya9h9H2Amg==",
"password": "yrxjf9cYw0YSmB+OdrNrkA==",
"column": [
"`p_type`",
"`v0`",
"`v1`",
"`v2`",
"`v3`",
"`v4`",
"`v5`"
],
"splitPk": "",
"connection": [
{
"table": [
"casbin_rule"
],
"jdbcUrl": [
"jdbc:mysql://192.168.0.200:3306/gfva"
]
}
]
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"username": "ZavoX0wUIT+NXZLci1dvPX8sxuRddOv83hdqDOVR+i0=",
"column": [
"`p_type`",
"`v0`",
"`v1`",
"`v2`",
"`v3`",
"`v4`",
"`v5`"
],
"connection": [
{
"table": [
"casbin_rule"
],
"jdbcUrl": "jdbc:mysql://10.211.55.54:2883/test2"
}
]
}
}
}
]
}
}
在网页上我们点击执行一次:
上图显示执行成功,让我们来看一看数据库中的表数据有没有过来。
[admin@obproxy ~]$ obclient -h 10.211.55.54 -uroot@test_tenant -P2883 -proot -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.6.25 OceanBase 3.1.3 (r10000292022032916-3d79cacb37012cf61b7cb8faf00d9a6bb152bcd1) (Built Mar 29 2022 08:20:39)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [oceanbase]> use test2;
Database changed
MySQL [test2]> select count(*) from casbin_rule;
+----------+
| count(*) |
+----------+
| 155 |
+----------+
1 row in set (0.018 sec)
MySQL [test2]>
6.写在最后
至此为止,将数据从MySQL数据库迁移到OceanBase的操作就完成了,大家是不是觉得还蛮简单的,欢迎大家添加博主交流。
最后,如果觉得文章对您有帮助,请给博主点赞、收藏、关注,博主会不断推出更多优质的文章。
更多推荐
所有评论(0)