mysqldump 使用小结

语法：

备份某个数据库：

mysqldump -uroot -p*** [options] –-databases DB_name  > back_db_name.sql

--databases：保留创建数据库语句

备份数据库中的某张表：

mysqldump -uroot -p*** [options] DB_name Tb_name > back_tb_name.sql

恢复时需要指定库名

只备份表结构：

mysqldump -uroot -p*** --no-data DB_name Tb_name > back_tb_struct.sql

-d, --no-data No row information.

备份所有数据库：

mysqldump -uroot -p*** [options] --single-transaction –-all-databases > back_all_db.sql
#远程备份
mysqldump -uroot -p*** [options] --single-transaction –-all-databases | ssh -p 22 -o StrictHostKeyChecking=no  127.0.0.1 "cat -> /tmp/back_all_db.sql"

常用选项：

--single-transaction：该选项导出数据之前提交一个BEGIN SQL语句,不会阻塞任何应该程序而且能保证导出数据时的一致性状态。
--lock-all-tables：备份之前锁定所有库的所有表。
--flush-logs：开始导出之前刷新日志。请注意如果导出多个数据库(选项--databases或者--all-databases)，将会逐个数据库刷新日志，除使用--lock-all-tables 或者--master-data外，这种情况下日志将会被刷新一次，相应的所有表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables或者-master-data和--flush-logs
--routines|-R：导出存储过程以及自定义函数
--evnets|-E：导出事件
--no-data|-d：不到表数据
--master-data={0|1|2}：该选项将binlog的位置和文件名追加到输出文件中。如果是0，不记录日志的位置；如果为1，以CHANGE MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器；如果为2，CHANGE MASTER TO 的方式记录位置，默认被注释。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间）。该选项自动关闭--lock-tables选项。

数据导入导出

该功能在MySQL 5.7.6版本后必须要secure_file_priv指定一个已存在的目录，然后outfile所输出的文件名在这个目录下

secure_file_priv参数说明：

这个参数用来限制数据导入和导出操作的效果，例如执行LOAD DATA、SELECT ... INTO OUTFILE语句和LOAD_FILE()函数。这些操作需要用户具有FILE权限；
如果这个参数为空，这个变量没有效果，可以再任意位置执行导入导出；
如果这个参数设为一个目录名，MySQL服务只允许在这个目录中执行文件的导入和导出操作，这个目录必须存在，MySQL服务不会创建它；
如果这个参数为NULL，MySQL服务会禁止导入和导出操作。

导出SCV：

select [列名] into outfile '/dir/path/filename.csv' 
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
from [表名];

LOAD导入数据：

load data infile '/dir/path/filename.csv' 
replace into table [表名]  #文件的列出应与表相同
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'

先mysqldump导出表结构：

mysqldump -uroot -p*** --single-transaction --databases -d [dbname]> /dir/path/db_struc.sql

再mysqldump导出txt：

mysqldump -uroot -p*** --single-transaction --skip-tz-utc -T /dir/path/ [dbname]

先恢复表结构：

mysql -uroot -p*** [dbname] < /dir/path/db_struc.sql

再导入txt数据：

mysqlimport --local -uroot -p*** [dbname] /dir/path/tablename.txt

LOAD的其他用法:
场景 1. LOAD 文件中的字段比数据表中的字段多

load data infile '/dir/path/filename.txt'
replace into table [表名]
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6) -- 该部分对应txt文件中6列数据
-- 只对导出数据中指定的2个列与表中字段做匹配,mapping关系指定的顺序不影响导入结果
set field1=@C6,
    field2=@C1;

场景 2. LOAD 文件中的字段比数据表中的字段少

load data infile '/dir/path/filename.txt'
replace into table [表名]
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6) -- 该部分对应employees.txt文件中6列数据
-- 将文件中的字段与表中字段做mapping对应,表中多出的字段不做处理
set field1=@C1,
    field2=@C2,
    field3=@C3,
    field4=@C4,
    field5=@C5,
    field6=@C6;

场景 3. LOAD 生成自定义字段数据
LOAD 时通过 MySQL支持的函数或给定固定值自行定义数据，对于文件中存在的字段也可做函数处理，结合导入导出，实现简单的 ETL 功能。

load data infile '/dir/path/filename.txt'
replace into table [表名]
character set utf8mb4
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(@C1,@C2,@C3,@C4,@C5,@C6)              -- 该部分对应employees.txt文件中6列数据
-- 以下部分明确对表中字段与数据文件中的字段做Mapping关系,不存在的数据通过函数处理生成(也可设置为固定值)
set field1=@C1,
   field2=@C2,
   field3=upper(@C3),              -- 转为大写
   field4=lower(@C4),               -- 转为小写
   field5=concat(field3,' ',field4),    -- 拼接
   field6=@C5,
   field7=@C6 ,
   field8=now(),                 -- 生成当前时间数据
   field9=if(field8<'1988-01-01','Y','N'); -- 对需要生成的值基于某一列做条件运算

学习参考：https://mp.weixin.qq.com/s/WNXRshkvC3bFcc5NDaWlrw