CNDBA社区
TDSQL 数据迁移工具主要有如下三种方法：mydumper / myloader、mysqldump 、load_data。 下面分别介绍他们的用法。
1 备份恢复说明
1.1 连接端口问题
使用备份工具来备份TDSQL 的数据库时，不能使用proxy的端口。因为proxy 不支持flush tables with read lock;的语法，同时还有一些其他的不兼容，导致备份出来的数据，无法进行恢复。
所以在备份时，需要直接连接单节点来跑脚本。 对于我们的测试环境，其实proxy 和db 的IP是一样的，那么区别就是端口号。 这个可以在赤兔平台上直接查看，proxy 端口都是15002 这种，而DB 的端口则是4002 这种。
我们在备份时需要使用DB 端口，不能使用proxy 端口，否则会出现类似如下的错误：
[dave@www.cndba.cn 2 bin]# mysql -h10.206.0.3 -udave -pdave -P15002 cndba < /data/dump/cndba.sql
ERROR 1064 (HY000) at line 75: Proxy ERROR: You have an error in your SQL syntax; check the manual that corresponds to your Mysql server version for the right syntax to use near 'if(@is_rocksdb_supported,'SET SESSION rocksdb_bulk_load = @old_rocksdb_bulk_load','SET @dummy_rocksdb_bulk_load = 0')' at line 1
[dave@www.cndba.cn 2 bin]# ./mydumper --host=10.206.0.3 --port=15002 --user=dave --password=dave --events --routines --triggers --less-locking --ignore-sysdb=1 --chunk-filesize=1024 --complete-insert --outputdir=/data/dump/alldb2.sql
** (mydumper:41438): CRITICAL **: 18:50:41.099: Couldn't acquire LOCK TABLES FOR BACKUP, snapshots will not be consistent: Proxy ERROR: You have an error in your SQL syntax; check the manual that corresponds to your Mysql server version for the right syntax to use near 'FOR BACKUP' at line 1
** (mydumper:41438): CRITICAL **: 18:50:41.100: Couldn't acquire LOCK BINLOG FOR BACKUP, snapshots will not be consistent: Proxy ERROR: You have an error in your SQL syntax; check the manual that corresponds to your Mysql server version for the right syntax to use near 'BINLOG FOR BACKUP' at line 1
** (mydumper:41438): WARNING **: 18:50:41.144: Broken table detected, please review: sys.io_global_by_file_by_bytes
** (mydumper:41438): WARNING **: 18:50:41.144: Broken table detected, please review: sys.io_global_by_file_by_latency
1.2 操作节点问题
对备份一般情况需要单节点来跑脚本，因为proxy 不支持flush tables with read lock。因为这里就有另外一个问题：
- 对于单表，使用db端口导没有区别，因为数据都在一个分区中；
- 对于分表，使用db端口导，每次只能导出对应分片中的数据，完整的数据就需要把所有结果合并起来。
2 mysqldump工具
2.1 工具说明
mysqldump 是 MySQL 自带的单线程逻辑备份工具，它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。
该工具因为是mysql的命令，所以在mysql的目录下:
/data/home/tdsql/tdsqlinstall/percona-5.7.17/bin
该工具参数也比较多，可以通过如下命令查看：
[dave@www.cndba.cn 2 bin]# ./mysqldump —help
这里看几个主要参数：
--complete-insert, -c
使用完整的insert语句(包含列名称)。
--single-transaction
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
--add-drop-database
每个数据库创建之前添加drop数据库语句。
--add-drop-table
每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
--add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
--set-gtid-purged=OFF
这个选项可以取消GTID信息写入dump文件。
注意：
如果导出的数据要导入到其他的 TDSQL 环境，必须加上-c 选项。
2.2 使用示例
基于前面的理论说明，我们这里看TDSQL 中使用单节点操作的使用。
2.2.1 导出
导出多个库
[dave@www.cndba.cn 1 log]# mysqldump -h192.168.31.61 -udave -pdave -P4002 --databases cndba dave --complete-insert --single-transaction --add-drop-database --add-drop-table --skip-add-locks --set-gtid-purged=OFF >/data/dump/dave.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[dave@www.cndba.cn 1 log]# ll /data/dump/dave.sql
-rw-r--r--. 1 root root 3918 Jul 28 10:08 /data/dump/dave.sql
导出某个库中的表
[dave@www.cndba.cn 1 log]# mysqldump -h192.168.31.61 -udave -pdave -P4002 dave dave --complete-insert --single-transaction --add-drop-database --add-drop-table --skip-add-locks --set-gtid-purged=OFF> /data/dump/table.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[dave@www.cndba.cn 1 log]# ll /data/dump/table.sql
-rw-r--r--. 1 root root 2931 Jul 28 10:10 /data/dump/table.sql
导出所有库：
[dave@www.cndba.cn 1 log]# mysqldump -h192.168.31.61 -udave -pdave -P4002 --all-databases --complete-insert --single-transaction --add-drop-database --add-drop-table --skip-add-locks --set-gtid-purged=OFF> /data/dump/alldb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[dave@www.cndba.cn 1 log]#
2.2.2 导入
因为mysqldump 导出的就是sql 语句，所以直接在mysql命令中执行sql 导入即可。
导入语法：
mysql -h -u -p -P database_name < /root/data.sql
我们这里把上面导出到表在导入。
[dave@www.cndba.cn 1 log]# mysql -htdsql1 -udave -pdave -P4002
Welcome to the MariaDB monitor. Commands end with ; or /g.
Your MySQL connection id is 35176
Server version: 5.7.17-11-V2.0R540D002-20191226-1152-log Source distribution
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 [(none)]> drop database cndba;
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> create database cndba;
Query OK, 1 row affected (0.00 sec)
导入数据并验证：
MySQL [(none)]> use cndba;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [cndba]> show tables;
+-----------------+
| Tables_in_cndba |
+-----------------+
| cndba |
+-----------------+
1 row in set (0.00 sec)
MySQL [cndba]>
3 mydumper和myloader工具
3.1 工具说明
mydumper和myloader是一组多线程工具，mydumper负责导出，myloader负责导入。 可以在DB 节点上直接调用，在/data/home/tdsql/tdsqlinstall/mysqlagent/bin 目录下。
3.1.1 mydumper 命令帮助
[dave@www.cndba.cn 2 bin]# pwd
/data/home/tdsql/tdsqlinstall/mysqlagent/bin
[dave@www.cndba.cn 2 bin]# ./mydumper --help
Usage:
mydumper [OPTION?] multi-threaded MySQL dumping
Help Options:
-?, --help Show help options
Application Options:
-A, --db-list tdsql modify for support multi-database
--hdfs-path dump to hdfs file system
--ignore-sysdb ignore sysdb and mysql
--cos-bucket Bucket name in tencent cos storage service
--cos-secretid Secret key in tencent cos storage service
--cos-secretkey Secret ID in tencent cos storage service
--cos-region Region name in tencent cos storage service
--ignore-emptytb ignore empty tables
-B, --database Database to dump
-T, --tables-list Comma delimited table list to dump (does not exclude regex option)
-o, --outputdir Directory to output files to
-s, --statement-size Attempted size of INSERT statement in bytes, default 1000000
-r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
-F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB
-c, --compress Compress output files
-e, --build-empty-files Build dump files even if no data available from table
-x, --regex Regular expression for 'db.table' matching
-i, --ignore-engines Comma delimited list of storage engines to ignore
-N, --insert-ignore Dump rows with INSERT IGNORE
-m, --no-schemas Do not dump table schemas with the data
-d, --no-data Do not dump table data
-G, --triggers Dump triggers
-E, --events Dump events
-R, --routines Dump stored procedures and functions
-W, --no-views Do not dump VIEWs
-k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups
--no-backup-locks Do not use Percona backup locks
--less-locking Minimize locking time on InnoDB tables.
-l, --long-query-guard Set long query timer in seconds, default 60
-K, --kill-long-queries Kill long running queries (instead of aborting)
-D, --daemon Enable daemon mode
-I, --snapshot-interval Interval between each dump snapshot (in minutes), requires --daemon, default 60
-L, --logfile Log file name to use, by default stdout is used
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
--skip-tz-utc
--use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege
--success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist
--lock-all-tables Use LOCK TABLE for all, instead of FTWRL
-U, --updated-since Use Update_time to dump only tables updated in the last U days
--trx-consistency-only Transactional consistency only
--complete-insert Use complete INSERT statements that include column names
-h, --host The host to connect to
-u, --user Username with the necessary privileges
-p, --password User password
-a, --ask-password Prompt For User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads Number of threads to use, default 4
-C, --compress-protocol Use compression on the MySQL connection
-V, --version Show the program version and exit
-v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
--defaults-file Use a specific defaults file
[dave@www.cndba.cn 2 bin]#
主要几个参数如下：
--events 备份事件
--routines 备份存储过程和函数
--triggers 备份触发器
--less-locking 减少对InnoDB表的锁施加时间
--ignore-sysdb 忽略sysdb库和mysql库
--chunk-filesize 将表按大小分块时，指定的块大小，单位是 MB
--complete-insert 使用完整的insert语句(包含列名称)。
--outputdir 导出的目录
3.1.2 myloder 命令帮助
[dave@www.cndba.cn 2 bin]# ./myloader -?
Usage:
myloader [OPTION?] multi-threaded MySQL loader
Help Options:
-?, --help Show help options
Application Options:
-d, --directory Directory of the dump to import
-q, --queries-per-transaction Number of queries per transaction, default 1000
-o, --overwrite-tables Drop tables if they already exist
-B, --database An alternative database to restore into
-s, --source-db Database to restore
-e, --enable-binlog Enable binary logging of the restore data
-h, --host The host to connect to
-u, --user Username with the necessary privileges
-p, --password User password
-a, --ask-password Prompt For User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads Number of threads to use, default 4
-C, --compress-protocol Use compression on the MySQL connection
-V, --version Show the program version and exit
-v, --verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
--defaults-file Use a specific defaults file
[dave@www.cndba.cn 2 bin]#
myloader主要2个参数：
--enable-binlog 启用还原数据的二进制日志
--overwrite-tables 如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构
3.2 操作示例
3.2.1 导出全库
[dave@www.cndba.cn 1 bin]# ./mydumper --host=192.168.31.61 --port=4002 --user=dave --password=dave --events --routines --triggers --less-locking --ignore-sysdb=1 --chunk-filesize=1024 --complete-insert --outputdir=/data/mydumper
** (mydumper:64511): WARNING **: 11:16:14.328: Broken table detected, please review: sys.io_global_by_file_by_bytes
** (mydumper:64511): WARNING **: 11:16:14.328: Broken table detected, please review: sys.io_global_by_file_by_latency
** (mydumper:64511): WARNING **: 11:16:14.328: Broken table detected, please review: sys.latest_file_io
** (mydumper:64511): WARNING **: 11:16:14.328: Broken table detected, please review: sys.tdsql
[2021-07-29 11:16:14] progress:0%
[2021-07-29 11:16:14] progress:1%
[2021-07-29 11:16:14] progress:2%
……
[2021-07-29 11:16:14] progress:96%
[2021-07-29 11:16:14] progress:97%
[2021-07-29 11:16:14] progress:98%
[2021-07-29 11:16:14] progress:99%
[dave@www.cndba.cn 1 bin]#
[dave@www.cndba.cn 1 dump]# cd /data/mydumper/
[dave@www.cndba.cn 1 mydumper]# ll
total 836
-rw-r--r--. 1 root root 259 Jul 28 11:26 cndba.cndba-schema.sql
-rw-r--r--. 1 root root 146 Jul 28 11:26 cndba.cndba.sql
-rw-r--r--. 1 root root 112 Jul 28 11:26 cndba-schema-create.sql
-rw-r--r--. 1 root root 258 Jul 28 11:26 dave.dave-schema.sql
-rw-r--r--. 1 root root 194 Jul 28 11:26 dave.dave.sql
-rw-r--r--. 1 root root 111 Jul 28 11:26 dave-schema-create.sql
-rw-r--r--. 1 root root 3770 Jul 28 11:26 dump_progress.info
-rw-r--r--. 1 root root 143 Jul 28 11:26 metadata.partial
-rw-r--r--. 1 root root 29 Jul 28 11:26 sys.host_summary_by_file_io-schema.sql
-rw-r--r--. 1 root root 1504 Jul 28 11:26 sys.host_summary_by_file_io-schema-view.sql
-rw-r--r--. 1 root root 29 Jul 28 11:26 sys.host_summary_by_file_io_type-schema.sql
-rw-r--r--. 1 root root 1797 Jul 28 11:26 sys.host_summary_by_file_io_type-schema-view.sql
-rw-r--r--. 1 root root 29 Jul 28 11:26 sys.host_summary_by_stages-schema.sql
-rw-r--r--. 1 root root 1693 Jul 28 11:26 sys.host_summary_by_stages-schema-view.sql
……
这里会生产很多的dump 文件。
3.2.2 导入全库
直接导会报如下错误：
[dave@www.cndba.cn 1 bin]# ./myloader --host=192.168.31.61 --port=4002 --user=dave --password=dave --directory=/data/mydumper/ --enable-binlog
** (myloader:95916): CRITICAL **: 11:28:30.428: the specified directory is not a mydumper backup
需要先重命名metadata.partial 文件，我们这里连系统表也删除了：
[dave@www.cndba.cn 1 mydumper]# mv metadata.partial metadata
[dave@www.cndba.cn 1 mydumper]# rm -rf sys.*
[dave@www.cndba.cn 1 bin]# ./myloader --host=192.168.31.61 --port=4002 --user=dave --password=dave --directory=/data/mydumper/ --enable-binlog --overwrite-tables
[2021-07-29 11:35:22] progress:16%
[2021-07-29 11:35:22] progress:33%
[2021-07-29 11:35:22] progress:50%
[2021-07-29 11:35:22] progress:66%
[2021-07-29 11:35:22] progress:83%
[2021-07-29 11:35:22] progress:100%
** (myloader:69278): CRITICAL **: 11:35:22.585: Error restoring sys.(null) from file sys-schema-post.sql: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
[dave@www.cndba.cn 1 bin]#
3.2.3 导出/导入指定库
导出指定库：
[dave@www.cndba.cn 1 bin]# ./mydumper --host=192.168.31.61 --port=4002 --user=dave --password=dave --database=cndba --complete-insert --chunk-filesize=1024 --outputdir=/data/cndba
[2021-07-29 11:39:07] progress:50%
[2021-07-29 11:39:07] progress:100%
[2021-07-29 11:39:07] progress:100%
[2021-07-29 11:39:07] progress:100%
[2021-07-29 11:39:07] progress:100%
[2021-07-29 11:39:07] progress:100%
You have new mail in /var/spool/mail/root
[dave@www.cndba.cn 1 bin]#
[dave@www.cndba.cn 1 cndba]# pwd
/data/cndba
[dave@www.cndba.cn 1 cndba]# ll
total 20
-rw-r--r--. 1 root root 259 Jul 28 11:39 cndba.cndba-schema.sql
-rw-r--r--. 1 root root 146 Jul 28 11:39 cndba.cndba.sql
-rw-r--r--. 1 root root 112 Jul 28 11:39 cndba-schema-create.sql
-rw-r--r--. 1 root root 215 Jul 28 11:39 dump_progress.info
-rw-r--r--. 1 root root 181 Jul 28 11:39 metadata
[dave@www.cndba.cn 1 cndba]#
导入数据到指定的库,必须加 —enable-binlog:
[dave@www.cndba.cn 1 bin]# ./myloader --host=192.168.31.61 --port=4002 --user=dave --password=dave --database=cndba --directory=/data/cndba --enable-binlog --overwrite-tables
[2021-07-29 11:40:34] progress:20%
[2021-07-29 11:40:34] progress:60%
[2021-07-29 11:40:34] progress:80%
[2021-07-29 11:40:34] progress:80%
[2021-07-29 11:40:34] progress:100%
[dave@www.cndba.cn 1 bin]#
4 load data工具
4.1 工具说明
load_data是专门的文本格式的数据导入工具，类似oracle 里的sqlloader。 该工具的原理是将源文件按照 shardkey 的路由规则，切分成多个文件，然后将每个文件单独透传到对应的后端数据库中。
[dave@www.cndba.cn 1 bin]# find /data -name load_data
/data/home/tdsql/tdsqlinstall/gateway/bin/load_data
/data/tdsql_run/15001/gateway/bin/load_data
/data/tdsql_run/15002/gateway/bin/load_data
/data/tdsql_run/15003/gateway/bin/load_data
/data/tdsql_run/15004/gateway/bin/load_data
/data/tdsql_run/15005/gateway/bin/load_data
/data/tdsql_run/15006/gateway/bin/load_data
[dave@www.cndba.cn 1 bin]# /data/tdsql_run/15001/gateway/bin/load_data --help
format:./load_data mode0/mode1 proxy_host proxy_port user password db_table shardkey_index file field_terminate filed_enclosed
example:./load_data mode1 10.231.136.34 3336 test test123 shard.table 1 '/tmp/datafile' ' ' ''
format:./load_data mode2 routers_file shardkey_index file field_terminate filed_enclosed
example:./load_data mode2 '/data/3336.xml' 1 '/tmp/datafile' ' ' ''
note:lines should terminated by /n
note:field_terminate may have more than one char,filed_enclosed must have only one char,all can not have ',do not support escape char
You have new mail in /var/spool/mail/root
[dave@www.cndba.cn 1 bin]#
命令的参数说明
导入命令：
./load_data mode1 $proxy_host $proxy_port $proxy_username $proxy_passwd $proxy_dbname.$proxy_tablename auto '$file_name' ',' '"'
参数说明：
mode0：load_data的原理是分割数据，然后导入数据，mode0是先分割数据不进行导入。
mode0：只分割数据，不进行导入，一般用于调试。
mode1：分割数据，然后对应load data语句中IGNORE模式导入数据，正式导入数据使用 mode1 指令。
mode2：在mode1基础上，忽略导入行错误，继续导入。
mode3：分割数据，然后对应load data语句中REPLACE模式导入数据。
mode4：在mode2基础上，忽略导入行错误，继续导入。
proxy_host：网关的host
proxy_port：网关的端口
user：用户名
password：密码
db_talbe：库表，格式为db.table
shardkey_index：shardkey字段在导入文件的索引（位置），从 0 开始，如果 shardkey 在第 2 个字段，则 shardkey_index 为 1。
file：绝对路径的文件所在位置
field_terminated：与导出时使用的field terminated 一致，行的分割
field_enclosed：与导出时使用的field enclosed一致，设置字段包围字符
注意:
1、源文件必须以’/n’作为换行符。
4.2 使用示例
4.2.1 导入到noshard：
（1）在noshard实例上准备库和表结构
（2）导入数据到noshard
./load_data mode1 10.120.109.204 15002 hanlon hanlon hanlon.test auto '/data1/4013bak/hanlon_test.csv' ',' '"'
4.2.2 导入数据到groupshrad
（1）在groupshard上创建好对应的库和表。
（2）到groupshard子set1的主DB机器上，如下目录（这里以4003端口为例，具体以实际的为准）：
# cd /data/tdsql_run/4003/mysqlagent/bin
将local_infile参数设置为ON
# ./mysql_param_modify --agent-conf="../conf/mysqlagent_4003.xml" --mode="modify" --param="param=local_infile&conf=local_infile&value=ON" --name="group_1606285105_5" --gropshard=1
注意：这里需要修改—agent-conf对应的端口号，—name对应的groupshard实例名。其他不用修改。
（3）将纯文本文件利用load_data工具导入到groupshard中。
# ./load_data mode1 172.16.16.26 15003 abc abc scott.t2 auto '/data/dumpdata1/scott.t1.sql' ',' '"'
如上语句只需要修改 ip、端口号、用户名、密码、库.表、纯数据文本文件这6项即可，其他不用修改。
（4）最后登陆groupshard中验证
mysql -uabc -pabc -h172.16.16.26 -P15003
MySQL [scott]> select count(*) from t1;
版权声明：本文为博主原创文章，未经博主允许不得转载。
