mysql 数据备份与恢复

mysql数据库备份分为物理备份和逻辑备份两种，物理备份指将数据库data下的数据文件进行直接copy备份，逻辑备份指通过备份数据库的逻辑结构和数据。也就是数据库的对象对应的create语句和数据表的insert语句。这里主要说逻辑备份。

dump数据文件（mysqldump）

mysqldump [options] db_name [table_name ...] 
1

连接参数：

-h, --host=name :指定数据库主机

-P, --port=#：指定连接端口号

-u, --user=name：指定用户名

-p, --password[=name]：指定用户密码

连接参数是必须的，访问数据库数据需要用户认证。host和port如果不指定默认访问本机数据库服务

数据库实例参数

–all-databases：导出所有数据库

mysqldump -uroot -p --all-databases  > all.sql
1

导出所有数据库会包含mysql本身自带的数据库，像mysql、sys、information_schema、performance_schema库。

如果明确导出一个具体的数据库，可以在mysqldump命令行上直接写明数据库名进行导出

mysqldump -uroot -p db_test  > db_test.sql
1

上面的语句就是直接导出db_test数据库。

导出多个数据库

使用–databases指定数据库列表

mysqldump -uroot -proot --databases db_test nacos210  > db_multi.sql
1

导出db_test和nacos210两个数据库。

导出多个数据库会带有创建数据库的脚本，例如上面语句会有：

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db_test`;
USE `db_test`;
1
2

可以添加–add-drop-database参数会在create数据库之前添加drop数据库语句。生成的好像drop语句是被注释的。

表设置参数

指定要备份的表

mysqldump -uroot -p --databases db_test --tables account test  > db_tb1.sql
1

–tables后面跟要导出的表列表，这里导出了account和test两个表。当在明确要备份几个表名时使用。

跳过某些表

mysqldump -uroot -p --databases db_test --ignore-table=db_test.account  > db_tb1.sql
1

–ignore-table指定不进行dump的表信息。可以加数据库前缀。并且–ignore-table可以出现多次用来指定多个表。这个和上面的指定具体的表正好相反，这里指定不导出的表列表。

只导出表结构

mysqldump -uroot -proot --databases db_test --no-data  > db_tb1.sql
1

–no-data参数指定不到处表数据，只导出表结构。也可以直接使用-d来指定。

条件导出

mysqldump -uroot -proot --databases db_test  --tables test  -w'id>10'  > db_tb1.sql
1

-w,–where=name可以设置导出语句查询条件，这里tables理论上是可以支持多个，但是where条件对应的列要在所有表上都存在。

其它设置参数

-r, --result-file=name：指定导出文件

-i, --comments导出备注，默认是开启的，如果不带备注可以使用–skip-comments

-l, --lock-tables导出表数据时先锁表，默认开启。可以使用–skip-lock-tables关闭

–log-error=name：将错误或警告信息记录到具体的文件

–default-character-set：字符集，默认是utf8（utf8mb4）。

–single-transaction：这个会将数据库隔离基本调整为可重复读，并且开启一个新事物进行数据导出，不会阻塞其它连接操作。可以保证数据的一致性，只有在Innodb引擎有效。

–quick：导出大表时通常结合–single-transaction一块使用，一行行的将表数据导出，而不是等所有的数据先放到内存再一块导出。

导出时直接压缩

mysqldump -uroot -p --databases db_test  --tables test|gzip  > db_tb1.sql.gz
1

还原dump文件

#方法1
>mysql -uroot -p  db_test < dump.sql
#方法2
mysql > use db_test
mysql > source dump.sql
1
2
3
4
5

还原比较简单使用mysql命令连接到数据库，然后执行备份文件即可。这里要注意数据库库名环境一定要和mysqldump生成的备份文件的语句库名一致，，如果没有要先创建切换到对应的库。

数据文件导出（SELECT INTO OUTFILE）

有时候在多系统交互时，我们只需要表的数据而不需要表结构信息，这里的数据不是指insert类型语句，而是纯粹的行数据。比如在给其它系统或数仓进行供数的时候。这个就可以使用SELECT INTO OUTFILE来生成对应的数据文件。

SELECT column1, column2
INTO OUTFILE 数据文件名
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM your_table;
1
2
3
4
5
6

FIELDS TERMINATED BY ‘,’： 指定字段之间分隔符，在指定分割符的时候，可能有时候需要用一些特殊字符，肯能是不可见字符，这个时候可以使用16进制进行设置，如 x’20’表示空格。

ENCLOSED BY 指定每个字段的包裹内容，

LINES TERMINATED BY ‘\n’ ：指定行与行之间的分隔符，window下换行符是’\r\n’。

这是一个查询语句，要首先使用mysql命令登录mysql后执行，也可以mysql -e 之间指定导出语句来一条命令执行。

secure-file-priv

在导出时候可能会提示以下错误

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

这是因为开启了secure-file-priv配置，mysql只允许在特定的目录进行导入导出数据。

查看当前变量配置，终端mysql命令登录执行：SHOW VARIABLES LIKE ‘secure_file_priv’;

mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
1
2
3
4
5

这里看到只允许将数据导出到/var/lib/mysql-files/目录，同样的导入数据也只能从这个目录。

并且如果导出的文件已经存在，会执行失败，不会覆盖原文件。

ERROR 1086 (HY000): File ‘/var/lib/mysql-files/test.sql’ already exists

secure_file_priv的几个值：

1、null表示禁止导入导出

2、empty空串表示可以导入导出

3、具体的路径：只能在当前指定的路径执行导入导出

secure_file_priv是一个只读属性，必须通过配置文件方式进行修改其值，

在my.cnf文件

[mysqld]
secure_file_priv='具体值'
1
2

修改完后要重启mysql服务。

数据文件导入（LOAD DATA INFILE）

数据文件导出后可以使用 load data命令将数据文件进行导入

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;
1
2
3
4

大体和导出文件参数差不多，ignore指定跳过多少行，当然后面还可以指定对应的表列信息，具体看官方文档吧。LOAD DATA Statement
最后说依据文件导入导出还需要赋予用户对应的操作权限。