使用 mysqldump 迁移

更新时间：2023-07-18 17:25:58

mysqldump 是 MySQL 提供的用于导出 MySQL 数据库对象和数据的工具，非常方便。OceanBase 数据库兼容 MySQL 协议，您可以使用 mysqldump 对 OceanBase 数据库中的数据进行备份。mysqldump 是 MySQL 自带的逻辑备份工具。它的备份原理是通过协议连接到数据库后，将需要备份的数据查询出来，并将查询出的数据转换成对应的 INSERT 语句。当您需要还原这些数据时，只要执行这些 INSERT 语句，即可将对应的数据还原。

建议使用版本

系统	版本
Linux	mysqldump Ver 10.13 Distrib 5.6.37, for Linux (x86_64)
MacOS	mysqldump Ver 10.13 Distrib 5.7.21, for macos10.13 (x86_64)

注意事项

使用 mysqldump 仅支持导出 OceanBase 数据库 MySQL 模式实例中的数据。
不支持锁库和表 Dump。
当您需要导出的数据量较多时，可能会报 TIMEOUT 4012 错误。为避免这个错误您需要使用租户管理员账户登录数据库运行下述语句调整系统参数，导出完成后您可以修改回原值：

obclient> SET GLOBAL ob_trx_timeout=1000000000,GLOBAL ob_query_timeout=1000000000;

备份操作

下述示例语句展示了如何在 mysqldump 中备份 OceanBase 数据库中的数据，运行该语句后会生成一个 SQL 格式的文件：

mysqldump -h xx.xx.xx.xx -P2883 -u 'user@tenantname#clustenamer' -p****** --skip-triggers  --databases db1  db2  db3  --skip-extended-insert  > /tmp/data.sql

下述表格为上述备份语句的参数说明：

参数	说明
--host（-h）	服务器 IP 地址。
--port（-P）	服务器端口号。
--user（-u）	MySQL 模式租户的用户名。
--password（-p）	MySQL 密码。
-d	仅导出表结构不导出数据。
--databases	指定要备份的数据库。
--all-databases	备份所有数据库，不建议使用，建议单独指定。
--compact	压缩模式，产生更少的输出。
--comments	添加注释信息。
--complete-insert	输出完成的插入语句。
--force	忽略错误。
--skip-triggers	OceanBase 数据库目前不支持 trigger 语法，所以如果不指定 `--force` 参数时必须要加上该参数，否则无法导出。
--skip-extended-insert	导出语句为多条 `INSERT` 格式，否则为 `INSERT INTO table VALUES(...),(...),` 格式。

导出数据后，您可通过下述语句导入之前导出的数据：

source  /tmp/data.sql

使用 mysqldump 迁移 MySQL 表到 OceanBase

导出指定数据库的表结构（不包括数据）

mysqldump -h 127.1 -u**** -P3306 -p******  -d TPCH --compact > tpch_ddl.sql

在导出的脚本中：

视图的定义也会在里面，但是会以注释（/ ! /）形式被标注。您无须关注视图部分，可将该部分内容删除。

有一些语法 OceanBase MySQL 不支持，但不影响，替换掉其中部分即可。

示例如下：

/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `NATION` (
  `N_NATIONKEY` int(11) NOT NULL,
  `N_NAME` char(25) COLLATE utf8_unicode_ci NOT NULL,
  `N_REGIONKEY` int(11) NOT NULL,
  `N_COMMENT` varchar(152) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`N_NATIONKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=4294967295;

在示例中，导出的脚本里有一个 MAX_ROWS= 的设置，这个是 MySQL 特有的，OceanBase MySQL 没有这个问题，也不需要这个设置，且不支持这个语法。您需要把所有 MAX_ROWS= 以及后面部分注释掉。注释方式可以使用批量替换技术，例如，在 Vim 中使用 :%s/MAX_ROWS=/; -- MAX_ROWS=/g。

注意

上面导出的 SQL 中表名是大写，说明源端 MySQL 设置表名默认很可能是大小写敏感，因此目标 OceanBase MySQL 租户也要进行同样的设置。

在导出的表结构语句里，可能包含外键。在导入 OceanBase MySQL 里时，如果外键依赖的表没有创建，导入脚本会报错，因此在导入之前需要禁用外键检查约束。

MySQL [oceanbase]> SET GLOBAL foreign_key_checks=off;
Query OK, 0 rows affected

MySQL [oceanbase]> SHOW GLOBAL variables like '%foreign%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set

在 obclient 里通过 source 命令可以执行外部 SQL 脚本文件。

导出指定数据库的表数据（不包括结构）

mysqldump -h 127.1 -u**** -P3306 -p****** -t TPCH  > tpch_data.sql

mysqldump 导出的数据初始化 SQL 里会首先将表锁住，禁止其他会话写。然后使用 insert 写入数据。每个 insert 后面的 value 里会有很多值。这是批量 insert。

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES ('a'),('中');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;