Mysql--数据的导入导出以及备份

一、导入导出

1.1、into outfile（只导出数据）

注意：mysql 5.7+版本，secure_file_priv 的值默认为NULL，即不允许导入或导出，需在 /etc/my.cnf 添加  secure_file_priv = '' 或者  secure_file_priv = '指定目录'  ，重启mysql

1.1.1 导出

select * from table_name into outfile 'file_path';

#指定分隔符
select * from table_name into outfile 'file_path' fields terminated by ',';

注意事项：

• 导出的文件一定是不存在的
• mysql用户对导出目录要有读写权限
• 导出的文件默认以\t为字段分隔符 \n为行分隔符

1.1.2 导入

load data infile '文件路径' into table 表名;

#指定分隔符
oad data infile '文件路径' into table 表名 fields terminated by '分隔符';

注意事项：

• 文件不要放在mysql读不到的地方（注意目录权限）
• mysql用户对要导入的文件至少要有读的权限（文件权限）
• 文件名用双引或单引号引起来

二、备份

2.1、mysqldump

2.1.1 简介

mysqldump 是 MySQL 自带的逻辑备份工具。

备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

2.1.2 选项

2.1.3 导出

导出所有库，表（包含表结构与表数据）

#all.sql默认保存在bin下
库：
mysqldump -u -p --all-databases > all.sql

表：
mysqldump -u -p --databases db1 –-all-tablespaces > all.sql

导出多个库，表

库：
mysqldump -u -p --databases db1 db2 > all.sql

表:
mysqldump -u -p --databases db1 --tables t1 t2 > all.sql

导出单库，表

库：
mysqldump -u -p --databases db1 > all.sql

表：
mysqldump -u -p db1 t1 > all.sql

只导出表结构

mysqldump -u -p -d db1 t1 > all.sql

只导出表数据

mysqldump -u -p -t db1 t1 > all.sql

使用-w指定时间段导出

mysqldump -uroot -pyy123456 -t -w "notify_time > unix_timestamp('2022-04-20') and notify_time < unix_timestamp('2022-05-06')" sre alarm_lists > test2.sql

2.1.4 恢复

库：
mysql -u -p < all.sql

表：
mysql -u -p db1 < all.sql 

2.1.5 source(导入大的sql文件)

mysql -u -p -e 'use db1;source all.sql;'

2.1.6 LOCK TABLES

导表的时候可能会报错：mysqldump: Got error: 1044: "Access denied for user 'sre'@'10.84.xxx.226' to database 'db1'" when doing LOCK TABLES

这是因为没有lock table的权限，在命令行加上 --single-transaction 解决

mysqldump --single-transaction -u sre -p -P 33066 -h 10.79.5.xxx db1 alarm_lists > alarm_lists.sql