签到成功

知道了

CNDBA社区CNDBA社区

TDSQL 集群 数据迁移工具 操作说明

2021-07-28 12:08 143 0 原创 TDSQL
作者: Dave

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 端口,否则会出现类似如下的错误:https://www.cndba.cn/dave/article/4624

[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。因为这里就有另外一个问题:

  1. 对于单表,使用db端口导没有区别,因为数据都在一个分区中;
  2. 对于分表,使用db端口导,每次只能导出对应分片中的数据,完整的数据就需要把所有结果合并起来。

2 mysqldump工具

2.1 工具说明

mysqldump 是 MySQL 自带的单线程逻辑备份工具,它的备份原理是通过协议连接到 MySQL 数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的insert 语句,当我们需要还原这些数据时,只要执行这些 insert 语句,即可将对应的数据还原。

该工具因为是mysql的命令,所以在mysql的目录下:

/data/home/tdsql/tdsqlinstall/percona-5.7.17/bin

该工具参数也比较多,可以通过如下命令查看:

https://www.cndba.cn/dave/article/4624

[dave@www.cndba.cn 2 bin]# ./mysqldump —help

这里看几个主要参数:https://www.cndba.cn/dave/article/4624

--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

导出某个库中的表https://www.cndba.cn/dave/article/4624

[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

导出所有库:https://www.cndba.cn/dave/article/4624

[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 文件,我们这里连系统表也删除了:https://www.cndba.cn/dave/article/4624

[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 导出/导入指定库

导出指定库:https://www.cndba.cn/dave/article/4624

[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' ',' '"'

参数说明:https://www.cndba.cn/dave/article/4624

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实例上准备库和表结构

https://www.cndba.cn/dave/article/4624

(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实例名。其他不用修改。https://www.cndba.cn/dave/article/4624

(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;

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1902
    原创
  • 2
    翻译
  • 456
    转载
  • 131
    评论
  • 访问:3641834次
  • 积分:3137
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群