记录一次向TiDB数据库导入数据的例子

时间:2020-12-24 21:43:04

导出数据

今天从Mysql的某个库中导出一个表大概有20分钟吧,等了一会终于导出成功了。查看一下文件的大小:

[tidb@:vg_adn_CkhsTest ~]$du -h ./creative_output.sql 
41G    ./creative_output.sql

这个文件是相当大的,有41G左右。

导出的命令很简单:mysqldump -hhost  -uname -ppwd   表名 >   xxx.sql

就这样就导出来的,我们只需要等结果就可以。

如果我们想导出一个数据库,那就要使用:mysqldump -hhost  -uname -ppwd   --database  库名  >   xxx.sql。也就是说多加了一个参数。

 

导入数据

使用mysqldump导出的数据,我们打开查看一下会发现是含有表结构的,并不是说像Csv那样的纯文本数据,因此使用source命令到导入数据即可。

由于本人搞混了几个备份和恢复数据的命令,导致本来就很快搞定的事情,花了几个小时。虽然踩了不少坑,但还是有收获的。

注意:mysqldump导出的数据是含有表结构的数据,除此之外还含有mysql的一些状态参数等。并不是纯文本数据。所以这里我一开始使用了load  data命令来导入数据,发现总是出错的。且先不说load  data命令的参数挺多的。

使用load  data参数导入数据的时候,要确认一下导入的文件是不是纯文本文件,load  data只适合导入一下类似与csv类型的文本。这种类型的文本只有数据,所以你要先创建好数据库后还要创建好表的结构,我这次就是又去尝试创建表结构后去导入数据,在这个过程中其中我心里面就觉得怪怪的,因为我之前也做过对表的备份,那个时候我并没有建表,而是直接将表导入数据库就可以了,但是怎么导入的我就是想不起来。后来去网上查找一下发现有个load  data命令可以导入,所以我就一直在踩坑。一直在load  data命令里面错误循环。

所以在这里纠正一下,mysqldump导出的数据使用mysql命令(客户端未登录数据库)或者source命令(客户端链接mysql后)来导入数据。

1、首先出现错误:

# mysqlimport -uabc -p'123' -h db1 -P 3306 DB a.txt
mysqlimport: Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table:

是因为mysql限制了导入数据的目录,修改一下就可以了。

我们来查看--secure-file-priv参数

> show global variables like 'secure_file_priv';
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| secure_file_priv | /dev/null |
+------------------+-----------| 

 

    此参数不是动态参数。需要添加配置文件,重启服务才可以生效。

    1)不限制导入导出

# cat /etc/my.cnf
    [mysqld]
    secure_file_priv = ""

    2)限制在特定目录下

# cat /etc/my.cnf
    [mysqld]
    secure_file_priv    = /tmp

        --只允许/tmp目录下的数据文件可以导入,其他目录下的文件没有权限导入。

2、在load data的时候,也出现了一些错误:比如:

>load  data .........................
error(2013):Lost connection to MySQL server during query

像这种错误就是客户端连接mysql数据库延时的错误。再深入一点就是说sql语句操作的时间过长,查询或导入的语句过大导致的。

我们可以这样子做:

先查看max_allowed_packet的值是多少:

show VARIABLES like '%max_allowed_packet%'; 

如果你觉得比较小,那么在mysql客户端中对max_allowed_packet等几项设置较大的值。以KB为单位

比如:

set global max_allowed_packet = 50000000

set global wait_timeout=2880000
set global interactive_timeout = 2880000

这三个中我们要先设置第一个,先把第一个设置的值大一点,上面的这个例子是我已经设置过了,你的查出来可能不会有这么大,因为我的文件就是40多个G ,因此我设置成了50G的大小,一般根据文件大小来设置。

如果第一个设置了之后还是出现同样的问题,那就把剩下的两个值也设置的大一点。注意:我上面是自己已经设置的比较大。

最后,你设置完之后要退出客户端重新连接一下才会生效(并不是重启mysql数据库),再次连接之后你会发现这个值就变成了你修改的那个值。不过重启过mysql之后这个值就会重新还原,因此如果你想要永久生效,最好在配置文件中添加这三个参数。

 

对我来说,本来导入的数据就不应该使用load  data命令来导入,出现了上面的情况,因此修改了参数还是没有解决,而且,我的系统报错非常快,就是当你执行load  data命令后,马上返回错误,这一点大家要注意,根本就没有超过延迟时间可就报错了,这本身就很可疑。

本次实验额参考了两位博主:

https://blog.csdn.net/lovemysea/article/details/79121154

https://www.aliyun.com/jiaocheng/1125866.html

参考连接:http://blog.51cto.com/11580138/1931925