mysql导数据到tidb

mysql导数据到tidb，tidb有专门的工具tidb-enterprise-tools。

1，下载tidb-enterprise-tools

# su - tidb //tidb的安装和启动用户

$ cd  /home/tidb/
$ wget http://download.pingcap.org/tidb-enterprise-tools-latest-linux-amd64.tar.gz
$ wget http://download.pingcap.org/tidb-enterprise-tools-latest-linux-amd64.sha256
$ sha256sum -c tidb-enterprise-tools-latest-linux-amd64.sha256
$ tar -zxvf tidb-enterprise-tools-latest-linux-amd64.tar.gz

2，配置tidb的sql_mode

如果tidb的sql_mode和mysql的一样，这一步就可以省略。tidb不支持配置文件修改sql_mode。

mysql> set global sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

tidb的sql_mode修改后，是需要重新集群才能生效。

$ tiup cluster stop tidb_ziwei && tiup cluster start tidb_ziwei

3，mysql导出数据

[tidb@tidb1 tidb-enterprise-tools-latest-linux-amd64]$ ./bin/mydumper -h 10.0.20.57 -u async_data -p 密码 -t 10 -F 128 -B account_center --skip-tz-utc -o /home/tidb/57bak/account_center

[tidb@tidb1 tidb-enterprise-tools-latest-linux-amd64]$ ls /home/tidb/57bak/account_center
account_center.account-schema.sql account_center.auth_company.sql account_center.login_log-schema.sql account_center.migrations.sql account_center.token.sql metadata
account_center.account.sql account_center.auth_personal-schema.sql account_center.login_log.sql account_center-schema-create.sql account_center.user-schema.sql
account_center.auth_company-schema.sql account_center.auth_personal.sql account_center.migrations-schema.sql account_center.token-schema.sql account_center.user.sql

参数说明：
-h 源数据库IP
-P 源数据库端口
-u 用户名 -p 密码
-B 源数据库名
-t 导出线程数
-F 分割文件大小，单位M（推荐64）
--skip-tz-utc 不修改时间
-o 导出到文件夹名，支持绝对路径

4，数据导入到tidb

[tidb@tidb1 tidb-enterprise-tools-latest-linux-amd64]$ ./bin/loader -h 127.0.0.1 -P 4000 -u root -p 密码 -t 16 -d /home/tidb/57bak/account_center
[2020/12/24 13:57:53.816 +08:00] [WARN] [disk.go:52] ["Remove temporary file error"] [tmpDir=/tmp/tidb-server-loader] [error="unlinkat /tmp/tidb-server-loader: operation not permitted"]
[2020/12/24 13:57:53.816 +08:00] [WARN] [disk.go:56] ["Mkdir temporary file error"] [tmpDir=/tmp/tidb-server-loader] [error="mkdir /tmp/tidb-server-loader: file exists"]
2020/12/24 13:57:53 printer.go:52: [info] Welcome to loader
2020/12/24 13:57:53 printer.go:53: [info] Release Version: v1.0.0-78-g6aea485
2020/12/24 13:57:53 printer.go:54: [info] Git Commit Hash: 6aea4851bb0c6e599c64b5c952ce257863c21586
2020/12/24 13:57:53 printer.go:55: [info] Git Branch: master
2020/12/24 13:57:53 printer.go:56: [info] UTC Build Time: 2019-12-18 04:25:49
2020/12/24 13:57:53 printer.go:57: [info] Go Version: go version go1.13 linux/amd64
。。。。。。。。。。。。。。。。。。。。。。。省略。。。。。。。。。。。。。。。。。。。。。。。。。。。
2020/12/24 13:57:55 loader.go:791: [info] [loader] all data files has been finished, takes 1.269558 seconds
2020/12/24 13:57:55 status.go:32: [info] [loader] finished_bytes = 2194465, total_bytes = GetAllRestoringFiles2194465, progress = 100.00 %
2020/12/24 13:57:55 main.go:88: [info] loader stopped and exits

参数说明：
-h 目标数据库IP
-P 目标数据库端口
-u 用户名
-p 密码（密码为空时，去掉该参数）
-t 导入线程数
-d 导入的文件夹，支持绝对路径

5，检查数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA     |
| PERFORMANCE_SCHEMA |
| account_center     |
| mysql              |
| tank_test          |
| test               |
| tidb_loader        |
+--------------------+
8 rows in set (0.00 sec)

mysql> use tidb_loader;
Database changed

mysql> show tables;
+-----------------------+
| Tables_in_tidb_loader |
+-----------------------+
| checkpoint            |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from checkpoint;
+--------+----------------------------------+----------------+---------------+---------+---------+---------------------+---------------------+
| id     | filename                         | cp_schema      | cp_table      | offset  | end_pos | create_time         | update_time         |
+--------+----------------------------------+----------------+---------------+---------+---------+---------------------+---------------------+
| ab866d | account_center.user.sql          | account_center | user          |  453559 |  453559 | 2020-12-24 13:57:54 | 2020-12-24 13:57:55 |
| ab866d | account_center.migrations.sql    | account_center | migrations    |     394 |     394 | 2020-12-24 13:57:54 | 2020-12-24 13:57:54 |
| ab866d | account_center.auth_company.sql  | account_center | auth_company  |   49911 |   49911 | 2020-12-24 13:57:54 | 2020-12-24 13:57:54 |
| ab866d | account_center.auth_personal.sql | account_center | auth_personal |  161994 |  161994 | 2020-12-24 13:57:54 | 2020-12-24 13:57:54 |
| ab866d | account_center.login_log.sql     | account_center | login_log     |   18198 |   18198 | 2020-12-24 13:57:54 | 2020-12-24 13:57:54 |
| ab866d | account_center.account.sql       | account_center | account       |  367258 |  367258 | 2020-12-24 13:57:54 | 2020-12-24 13:57:55 |
| ab866d | account_center.token.sql         | account_center | token         | 1143151 | 1143151 | 2020-12-24 13:57:54 | 2020-12-24 13:57:55 |
+--------+----------------------------------+----------------+---------------+---------+---------+---------------------+---------------------+
7 rows in set (0.01 sec)