迁移mysql数据到oceanbase集群

服务器A，mysql数据库，创建新库用于测试：

create database yitianyu;

use yitianyu;

CREATE TABLE tt1 (

ID INT(6) primary key,

name varchar(20),

parent_id int(6));

INSERT INTO tt1 (id, name, parent_id) VALUES (110000, '北京市', null);

INSERT INTO tt1 (id, name, parent_id) VALUES (110100, '北京市', 110000);

INSERT INTO tt1 (id, name, parent_id) VALUES (110101, '东城区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110102, '西城区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110103, '朝阳区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110104, '丰台区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110105, '石景山区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110106, '海淀区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110107, '门头沟区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110108, '房山区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110109, '通州区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110110, '顺义区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110111, '昌平区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110112, '大兴区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110113, '怀柔区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110114, '平谷区', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110115, '密云县', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (110116, '延庆县', 110100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120000, '天津市', null);

INSERT INTO tt1 (id, name, parent_id) VALUES (120100, '天津市', 120000);

INSERT INTO tt1 (id, name, parent_id) VALUES (120101, '和平区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120102, '河东区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120103, '河西区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120104, '南开区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120105, '河北区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120106, '红桥区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120107, '滨海新区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120108, '东丽区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120109, '西青区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120110, '津南区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120111, '北辰区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120112, '武清区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120113, '宝坻区', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120114, '宁河县', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120115, '静海县', 120100);

INSERT INTO tt1 (id, name, parent_id) VALUES (120116, '蓟县', 120100);

CREATE TABLE tt2 (

ID int(4) primary key not null auto_increment,

TABLE_SCHEMA varchar(64) NOT NULL DEFAULT '',

TABLE_NAME varchar(64) NOT NULL DEFAULT '',

COLUMN_NAME varchar(64) NOT NULL DEFAULT '',

ORDINAL_POSITION bigint(21) unsigned NOT NULL DEFAULT '0',

IS_NULLABLE varchar(3) NOT NULL DEFAULT '',

DATA_TYPE varchar(64) NOT NULL DEFAULT '',

CHARACTER_MAXIMUM_LENGTH bigint(21) unsigned DEFAULT NULL,

CHARACTER_OCTET_LENGTH bigint(21) unsigned DEFAULT NULL,

NUMERIC_PRECISION bigint(21) unsigned DEFAULT NULL,

NUMERIC_SCALE bigint(21) unsigned DEFAULT NULL,

DATETIME_PRECISION bigint(21) unsigned DEFAULT NULL,

CHARACTER_SET_NAME varchar(32) DEFAULT NULL,

COLLATION_NAME varchar(32) DEFAULT NULL,

COLUMN_TYPE longtext NOT NULL,

COLUMN_KEY varchar(3) NOT NULL DEFAULT '',

EXTRA varchar(30) NOT NULL DEFAULT '',

PRIVILEGES varchar(80) NOT NULL DEFAULT ''

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tt2 select null,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_O21:18:44.955 CTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,EXTRA,PRIVILEGES from information_schema.columns;

root@127.0.0.1 [yitianyu]>show variables like '%per_table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

对应的数据文件如下所示：

tt1.frm tt1.ibd tt2.frm tt2.ibd

使用mysqldump导出整个库，表结构和数据分开导出：

mysqldump -S /app/mysql/mysql3306.sock -p123456 -d -n --databases yitianyu --single-transaction --set-gtid-purged=OFF > /app/biaojiegou.sql

mysqldump -S /app/mysql/mysql3306.sock -p123456 -t -n --databases yitianyu --single-transaction --set-gtid-purged=OFF > /app/biaoshuju.sql

检查biaojiegou.sql，看看有无ob不支持的语法，否则报错：

这些报错不影响表的创建，不用理会。

本案例需要导入到ob的test库，因此导出时加上了-n。

导入成功后看到了tt1和tt2：

MySQL [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| tb1 |

| tt1 |

| tt2 |

+----------------+

3 rows in set (0.00 sec)

继续导入表数据：

MySQL [test]> source biaoshuju.sql;