mysqldump备份恢复数据库

备份单库[单表]:

mysqldump -u username -p dbname [tbname ...] > filename.sql

备份单库多表:

mysqldump -u username -p dbname tbname1 tbname2... > filename.sql

备份多库:

mysqldump -u username -p --databases dbname1 dbname2 ... > filename.sql

备份所有库:

mysqldump -u username -p --all-databases > filename.sql

备份压缩:

mysqldump -u username -p dbname [tbname ...] | gzip > filename.sql

备份结构不备份数据：

mysqldump -u username -p --no-data dbname [tbname ...] > filename.sql mysqldump -u username -p -d dbname [tbname ...] > filename.sql

恢复：

mysql -u username -p [target_database_name] < filename.sql

如果使用--all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库

导出表数据：

导出表数据(非insert语句)到文件:

SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件'[OPTIONS]

[OPTIONS] 为可选参数选项，OPTIONS 部分的语法包括 FIELDS 和 LINES 子句，其常用的取值有：

• FIELDS TERMINATED BY '字符串'：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符‘\t’。
• FIELDS [OPTIONALLY] ENCLOSED BY '字符'：设置字符来括上 CHAR、VARCHAR 和 TEXT 等字符型字段。如果使用了 OPTIONALLY 则只能用来括上 CHAR 和 VARCHAR 等字符型字段。
• FIELDS ESCAPED BY '字符'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\’。
• LINES STARTING BY '字符串'：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
• LINES TERMINATED BY '字符串'：设置每行结尾的字符，可以为单个或多个字符，默认值为‘\n’ 。

注意：FIELDS 和 LINES 两个子句都是自选的，但是如果两个都被指定了，FIELDS 必须位于 LINES的前面。

该语句用 SELECT 来查询所需要的数据，用 INTO OUTFILE 来导出数据。其中，目标文件用来指定将查询的记录导出到哪个文件。这里需要注意的是，目标文件不能是一个已经存在的文件，文件只能导出到实例所在服务器上。

导出表数据(insert语句)到文件:

SELECT * FROM mysql.user INTO OUTFILE 'targetfile.sql' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY 'INSERT INTO tablename VALUE(' TERMINATED BY ')\;\r\n';

报错：

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。

原因：

这是因为MySQL 限制了数据的导出路径。MySQL 导入导出文件只能在 secure-file-priv 变量的指定路径下的文件才可以导入导出。

解决：

1.使用show variables like '%secure%';语句查看 secure-file-priv 变量配置，将文件导出到secure_file_priv变量值的目录下

2. 如果 secure_file_priv 值为 NULL，则为禁止导出，可以在 MySQL 安装路径下的 my.cnf文件中添加secure_file_priv=设置路径语句，然后重启服务即可。如secure_file_priv=‘\’