2MUCH

Mysql导入导出

2022-11-27

Mysql导入导出

导出

  1. mysqldump

(1) 导出完整数据：

mysqldump -u用户名 -p 数据库名 > 数据库名.sql

例如：# /usr/local/mysql/bin/mysqldump -uroot -p abc > abc.sql

(2) 只导出表结构（使用 -d 选项）：

mysqldump -u用户名 -p -d 数据库名 > 数据库名.sql

  1. INTO OUTFILE

https://www.modb.pro/db/98539

SELECT * FROM data_tbl --查找数据源，如果是全表所有字段也可以用 TABLE <talbe>
INTO OUTFILE "/tmp/data.txt" --数据输出文件
FIELDS TERMINATED BY ','  --字段分隔符
OPTIONALLY ENCLOSED BY '"' --字段引用符
LINES TERMINATED BY '\n' --整条记录分割符
;

导入

  1. <.sql

mysql -u用户名 -p 数据库名 < 数据库名.sql

例如：

# mysql -uroot -p123456 < abc.sql

  1. source

(1) 连接数据库：mysql -u {用户名} -p 回车 再输入密码 mysql -u root -p 回车

(2) 指定目标数据库名：use {库名} use eva 回车

(3) 导入文件 source {路径} source C:\Users\chengh\Desktop\user.sql 需要注意的是 你得有这个文件，如果没有则会报异常

  1. load data

使用场景和语法参见：https://blog.csdn.net/u012815136/article/details/88953289

load data local infile 'D:/my_user_info.txt' into table user_info
CHARACTER SET utf8 -- 可选，指定导入文件的编码，避免中文乱码问题。假如这里文件 my_user_info.txt 的编码为 gbk，那么这里编码就应该设为 gbk 了
FIELDS TERMINATED BY '||' -- 字段分隔符，每个字段(列)以什么字符分隔，默认是 \t
	OPTIONALLY ENCLOSED BY '' -- 文本限定符，每个字段被什么字符包围，默认是空字符
	ESCAPED BY '\\' -- 转义符，默认是 \
LINES TERMINATED BY '\n' -- 记录分隔符，如字段本身也含\n，那么应先去除，否则load data 会误将其视作另一行记录进行导入
(id, name, age, address, create_date) -- 每一行文本按顺序对应的表字段，建议不要省略

注意事项：

LOAD DATA INFILE和 SELECT INTO OUTFILE是相互依赖的，即：如果你导出方式使用了 SELECT INTO OUTFILE，那么导入的时候就需要使用 LOAD DATA INFILE进行导入。需要格外注意，该方式会锁全表。

LOAD DATA格式

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

shell脚本实现导入导出

  1. select into outfile & load data infile

https://blog.csdn.net/u013946356/article/details/84960273

如何使用导入定长文件（fixed-width records ）

定长文件：不同于用分隔符（如逗号或空格）来分割每行记录的各字段，定长数据文件是根据约定好的各字段长度来组织数据文件的。如：

 slideNum, startTime, endTime
   1     75175.18     95128.46
   1    790890.89    795829.16
   1    875975.98    880914.25
   8   2137704.37   2162195.53
   8   2167267.27   2375275.28
  10   2375408.74   2763997.33
  14   2764264.26   2804437.77
  15   2804504.50   2881981.98
  16   2882048.72   2887921.25
  16   2993093.09   2998031.36
  19   3004104.10   3008041.37

LOAD DATA虽然很好地支持了分隔符格式的数据文件导入，但是这类型的数据文件，需要进行特殊的处理。

可参考：https://stackoverflow.com/questions/11461790/loading-fixed-width-space-delimited-txt-file-into-mysql

针对上面的例子，解决方案可以是（with user variables (@row)）：

LOAD DATA LOCAL INFILE 
'/some/Path/segmentation.txt' 
INTO TABLE clip
(@row)
SET slideNum = TRIM(SUBSTR(@row,1,4)),
    startTime = TRIM(SUBSTR(@row,5,13)),
    endTime = TRIM(SUBSTR(@row,18,13))
;

使用LOAD DATA来处理定长文件的注意事项：

此类场景的解决方案，参考链接：

https://stackoverflow.com/questions/11461790/loading-fixed-width-space-delimited-txt-file-into-mysql

https://docs.singlestore.com/managed-service/en/reference/sql-reference/data-manipulation-language-dml/load-data.html

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

导入实践

建表

create table t1 (
	id int,
	name VARCHAR(25)
)

准备infile文件数据

[root@iZbp15qc4wmx335c268l5mZ cindy]# cat data.txt
3 Alice  
4 bob

尝试导入

脚本内容：

LOAD DATA INFILE  '/home/cindy/data.txt' 
INTO TABLE t1
(@row)
SET id = TRIM(SUBSTR(@row,1,2)),
name = TRIM(SUBSTR(@row,3,7))
;

mysql> LOAD DATA INFILE  '/home/cindy/data.txt' 
    -> INTO TABLE t1
    -> (@row)
    -> SET id = TRIM(SUBSTR(@row,1,2)),
    ->     name = TRIM(SUBSTR(@row,3,7))
    -> ;
ERROR 1045 (28000): Access denied for user 'cindy'@'%' (using password: YES)

授予FILE权限

搜索了下，使用 load data语句导入数据需要有处理文件的权限。

参考：

https://blog.csdn.net/qq_37189082/article/details/121249966

https://www.cnblogs.com/darange/p/10508714.html

mysql> grant file on *.* to 'cindy'@'%';
ERROR 1045 (28000): Access denied for user 'cindy'@'%' (using password: YES)
-- 用cindy用户执行报错，原来需要用root用户授权
mysql> grant file on *.* to 'cindy'@'%';
Query OK, 0 rows affected (0.01 sec)

再次尝试导入

mysql> LOAD DATA INFILE  '/home/cindy/data.txt' 
    -> INTO TABLE t1
    -> (@row)
    -> SET id = TRIM(SUBSTR(@row,1,2)),
    -> name = TRIM(SUBSTR(@row,3,7))
    -> ;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

secure_file_priv参数修改

上述报错的解决方案参考：https://blog.csdn.net/lz6363/article/details/115805495

mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.12 sec)
-- 查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。
-- secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。
-- secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。
-- secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。
-- 解决方案：1、将允许的文档路径secure-file-priv改为所需的路径；2、干脆设置secure_file_priv=''，表示任意目录。我采取后者.

vim /etc/my.cnf
# 加上以下这行：
secure_file_priv='' # 允许导入导出任何路径的文件
# 重启数据库
/etc/init.d/mysqld restart

-- 再次查询，修改成功
mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

再次尝试导入

mysql> LOAD DATA INFILE  '/home/cindy/data.txt' 
    -> INTO TABLE t1
    -> (@row)
    -> SET id = TRIM(SUBSTR(@row,1,2)),
    -> name = TRIM(SUBSTR(@row,3,7))
    -> ;
ERROR 13 (HY000): Can't get stat of '/home/cindy/data.txt' (Errcode: 13 - Permission denied)

解决方法参考： https://blog.csdn.net/weixin_39722921/article/details/113156666

将LOAD DATA INFILE

换成

LOAD DATA LOCAL INFILE

原因分析：

源自Mysql中文手册：

出于安全原因，当读取位于服务器中的文本文件时， 文件必须位于数据库目录中，或者是全体可读的。另外，要对服务器文件使用LOAD DATA INFILE，您必须拥有FILE权限。

如果指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。

因此把脚本内容改为：(即，加上LOCAL)

LOAD DATA LOCAL INFILE  '/home/cindy/data.txt' 
INTO TABLE t1
(@row)
SET id = TRIM(SUBSTR(@row,1,2)),
name = TRIM(SUBSTR(@row,3,7))
;

再次尝试导入

mysql> LOAD DATA LOCAL INFILE  '/home/cindy/data.txt' 
    -> INTO TABLE t1
    -> (@row)
    -> SET id = TRIM(SUBSTR(@row,1,2)),
    -> name = TRIM(SUBSTR(@row,3,7))
    -> ;
Query OK, 3 rows affected, 1 warning (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 1

看起来有戏，看看现在的表结果：

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | cindy |
|    2 | kexin |
|    3 | Alice |
|    4 | bob   |
|    0 |       |
+------+-------+
5 rows in set (0.00 sec)

成功了！！

id为3,4,0的记录是刚insert的，其中0的那一条应该是从空白行得来的。。（这样不合适的数据，当然也是有waring的⬇️）

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'id' at row 3 |
+---------+------+------------------------------------------------------+
1 row in set (0.07 sec)

将insert脚本作为sql脚本执行

[root@iZbp15qc4wmx335c268l5mZ cindy]# cat insert.sql
use test_db;
LOAD DATA LOCAL INFILE  '/home/cindy/data.txt' 
INTO TABLE t1
(@row)
SET id = TRIM(SUBSTR(@row,1,2)),
name = TRIM(SUBSTR(@row,3,7))
;

mysql> source /home/cindy/insert.sql;
Database changed
Query OK, 3 rows affected, 1 warning (0.09 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 1

参考链接

https://www.runoob.com/w3cnote/linux-mysql-import-export-data.html

https://juejin.cn/post/6992489420188286983

https://juejin.cn/post/7039542872051302436

https://www.modb.pro/db/98539