MySql--导入与导出

摘要

• MySql知识点介绍:导入与导出

• 本文基于mysql-8.0.30，https://dev.mysql.com/doc/refman/8.0/en/

数据库导出与导入

全量导出

# 包含建库、建表和数据语句
mysqldump -uroot --all-databases --triggers --routines --events -p > all_databases.sql

导出指定数据库

# 包含建表和数据语句
mysqldump -uroot -p DB1 > DB1.sql

# 包含建库、建表和数据语句[推荐]
mysqldump -uroot -p --databases DB1 DB2 > databases.sql

重要参数
-B, --databases Dump several databases. 备份多个数据库
-R, --routines Dump stored routines (functions and procedures). 备份函数和存储过程
-E, --events Dump events. 备份events
–triggers Dump triggers for each dumped table. 备份每个表的触发器

导出指定数据库的指定表

# 包含建表和数据语句
mysqldump -uroot -p DB1 --tables table1 table2 > tables.sql

导出指定数据库时排除某些表

# 包含建库、建表和数据语句
mysqldump -uroot -p --databases DB1 DB2 --ignore-table=DB1.table1 --ignore-table=DB2.table2 > tables.sql

导出结构不导出数据 -d

# 包含建表语句
mysqldump -uroot -p -d DB1 --tables table1 table2 > tables.sql

导出数据不导出结构 -t

# 包含数据语句
mysqldump -uroot -p -t DB1 --tables table1 table2 > tables.sql

小贴士
使用msyqldump时要注意，尽量使用与待导出的数据库版本一致的msyqldump版本，否则不能执行导出，比如使用8.x的版本去导出5.7的版本数据库时，是不能导出成功的。

导出sql查询结果

# 这个实际上是命令行执行sql语句的命令 -e 参数指定要执行的sql语句
mysql -uroot -p -DdbName -e "SELECT t.chapter_num as num, t.title as title FROM chapter_info t where t.book_id = 10  order by t.chapter_num asc" > result.txt

导出json格式的数据

# 这个实际上是命令行执行sql语句的命令 -e 参数指定要执行的sql语句，输出结果为每行一个json格式，而不是整体是json格式，要转成完整的json格式，需要每行后面加个逗号，去掉第一行标题行，以及开头和结尾加上 {[]}
mysql -uroot -p -DdbName -e "SELECT JSON_OBJECT('num', t.chapter_num, 'title', t.title, 'content',t.content) FROM chapter_info t where t.book_id = 10 order by t.chapter_num asc" > result.json
    每行结尾加逗号：sed -i 's/"}/"},/' result.json
    删除文件第一行：sed -i '1d' result.json
    第一行上面插入{[ ：sed -i '1i {[' result.json
    最后一行下面插入]}：sed -i '$a ]}' result.json

    一行命令搞定：sed -i -e 's/"}/"},/' -e '1d' -e '2i {[' -e '$a ]}'  result.json
    注意这里插入"{["时要在第二行上插入，因为是同时处理的，此时还没有完成删除第一行的操作

导入数据

mysql -uroot -p < all_databases.sql
mysql -uroot -p < databases.sql

# -D指定要导入的数据库，此时数据库必须先创建好
mysql -uroot -p -Dbdname < DB1.sql
mysql -uroot -p -Dbdname < tables.sql

导入导出CSV

导出CSV

• 需要先开通导出权限，默认关闭

  • secure_file_prive=null 限制mysql不允许导出，默认值
  • secure_file_priv=/path/ 限制mysql的导出只能发生在默认的/path/目录下
  • secure_file_priv="" 不对mysql的导出做限制，可以导出到任意目录

  mysql> show variables like 'secure_file_priv';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | secure_file_priv |  NULL |
  +------------------+-------+

  • 这是一个只读变量，需要修改mysql的配置文件，这里我们设置为secure_file_priv=""

• 命令行执行导出

mysqldump -uroot -p DBName tableName --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --tab=/mysqldump_dir

# 参数说明：
--fields-enclosed-by='"'：每个字段以空字符结尾。
--fields-terminated-by=','：字段间以逗号分隔。
--lines-terminated-by='\r\n'：以回车符为每行的结束。
--tab=/mysqldump_dir：生成文件的路径，确保有写权限
生成的文件包含两个，一个是数据文件：tableName.txt，一个是建表语句：tableName.sql

# 如果同时导出多张表，则也会分别生成相应的2个文件。
mysqldump -uroot -p DBName table1 table2 --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --tab=/mysqldump_dir

• sql执行导出

# 导出全部字段
mysql> select * from tableName into outfile '/mysqldump_dir/tableName.csv' fields terminated by ','  ENCLOSED BY '"' lines terminated by '\r\n';

# 导出部分字段
mysql> select 字段1,字段2 from tableName into outfile '/mysqldump_dir/tableName.csv' fields terminated by ','  ENCLOSED BY '"' lines terminated by '\r\n';

这种方式只会导出数据文件，同时可以指定要导出的字段

导入CSV

• 需要先开通导入权限，默认关闭

mysql> show variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
# 开通
mysql> set GLOBAL local_infile=1;

• mysql和mysqld都需要配置开通，不要配置到client下，因为只有mysql命令支持该参数

[mysql]
local_infile=ON

[mysqld]
local_infile=ON

• 命令行导入
  此方法要求文件名称"table1.csv"必须为表名称"table1"

mysqlimport --local -uroot -p DBName -i --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' ./table1.csv

# 参数说明
-L, --local : 读取的所有文件都是基于执行命令时所在的客户端。
-i, --ignore  :如果唯一索引重复则忽略后面的数据， 如果希望覆盖前面的数据，需要使用 -r 参数，两个参数不能同时出现。
--fields-enclosed-by='"'：每个字段以空字符结尾。
--fields-terminated-by=','：字段间以逗号分隔。
--lines-terminated-by='\r\n'：以回车符为每行的结束。

# 此时由于没有指定数据与字段对应方式，所以默认按表中字段顺序导入，如果要指定导入的字段，需要增加 --columns 参数，逗号分隔
mysqlimport --local -uroot -p DBName -i --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --columns=字段1,字段2,字段3 ./table1.csv

# 如果数据文件中的头部有title，可以指定忽略掉头部的几行，--ignore-lines=1：忽略掉第一行。
mysqlimport --local -uroot -p DBName -i --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --ignore-lines=1 ./table1.csv

• sql执行导入
  此方法不要求文件名称与表名称一致

警告⚠️
明明配置文件中已经配置了local_infile=ON，但是通过sql执行load data local infile时还是会报错呢？
这就说明客户端运行时没有找到默认的配置文件，所以还是建议把my.cnf放到/etc目录下吧
也可以在登录时加上--local-infile

mysql -uroot -p --local-infile

mysql> load data local infile '~/file.csv' ignore into table table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

# 跳过表头
mysql> load data local infile '~/file.csv' ignore into table table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ignore 1 lines;

# 指定字段
mysql> load data local infile '~/file.csv' ignore into table table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ignore 1 lines (字段1,字段2,字段3);

#参数说明：
ignore : 如果唯一索引重复则忽略后面的数据， 如果希望覆盖前面的数据，需要使用 replace 参数，两个参数不能同时出现。
ignore 1 lines : 忽略开头的行数
(字段1,字段2,字段3) : 关联的字段顺序