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'); -- 对需要生成的值基于某一列做条件运算
