MySQL 备份恢复（二）

前面一篇已经介绍了MySQL 备份相关的原理与方法，要是还没有来得及看的可以戳此查看『MySQL 备份恢复（一）』，那么今天就接着上一篇的内容继续谈谈备份恢复相关内容。数据备份是 DBA 非常重要的工作之一，系统意外奔溃或者硬件损坏都可能导致数据库的数据丢失，因此 MySQL DBA 应该定期备份数据，使得意外发生时尽可能的减少损失。数据备份在工作中是重中之重，安全很重要。

前面说过逻辑备份中有mysqldump、select……into outfile、mydumper 等，下面一起看看 select……into outfile 备份方法。

select …… into outfile

SELECT INTO…OUTFILE 语句是一种逻辑备份方法，恢复速度非常快，比 inser的插入速度要快很多。将表数据导出到一个文本文件中，并用LOAD DATA …INFILE 语句恢复数据。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏或者表被 drop，则必须先恢复原来的表的结构。

常用的语法如下：

代码语言：javascript复制

select col1,col2……from table_name into outfile ‘/path/backup.sql’

例如：将库 testdb 下的数据全部导出命名为 testdb_t.sql 放到 /tmp 下

代码语言：javascript复制

use testdb;
select* from t;
select* from t into outfile ‘/tmp/test_t.sql’;

当备份时出现了如上 ERROR 1290 的错误，网上查阅资料时说是由于参数 --secure-file-priv 设置为空的问题，此问题在 MySQL5.6 中不会出现，5.7 中则会出现如上错误。

查看官方文档，secure_file_priv 参数用于限制 LOAD DATA, SELECT …OUTFILE, LOAD_FILE() 传到哪个指定目录。

secure_file_priv 为 NULL 时，表示限制 mysqld 不允许导入或导出。

secure_file_priv 为 /tmp 时，表示限制 mysqld 只能在 /tmp 目录中执行导入导出，其他目录不能执行。

secure_file_priv 没有值时，表示不限制 mysqld 在任意目录的导入导出。

查看 secure_file_priv 的值，默认为 NULL，表示限制不能导入导出。

但此参数是静态只读参数，故不能在线修改，需要修改配置文件/etc/my.cnf 重启 MySQL 服务方可生效。

代码语言：javascript复制

vi /etc/my.cnf
secure_file_priv=''

重启服务后查看参数值为空，则可以操作导出数据了。

代码语言：javascript复制

[root@JiekeXu tmp]# mysqladmin -uroot -proot shutdown 
[root@JiekeXu tmp]# 
[root@JiekeXu tmp]# ps -ef | grep mysql
root     24081 12728  0 15:34 pts/0    00:00:00 /usr/local/mysql/bin/mysql -uroot -p
root     24096  9826  0 15:40 pts/1    00:00:00 /usr/local/mysql/bin/mysql -uroot -p
root     24181 14148  0 16:13 pts/2    00:00:00 grep mysql
[root@JiekeXu tmp]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf & 
[1] 24193
[root@JiekeXu tmp]# 2019-03-05T08:17:26.966287Z mysqld_safe Logging to '/opt/mysql/error.log'.
2019-03-05T08:17:26.997172Z mysqld_safe Starting mysqld daemon with databases from /opt/mysql
[root@JiekeXu tmp]# 
[root@JiekeXu tmp]# ps -ef | grep mysql
root     24081 12728  0 15:34 pts/0    00:00:00 /usr/local/mysql/bin/mysql -uroot -p
root     24193 14148  0 16:17 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql    25488 24193  2 16:17 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/opt/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/opt/mysql/error.log --open-files-limit=65535 --pid-file=JiekeXu.pid --socket=/tmp/mysql.sock --port=3306
root     25528 14148  0 16:17 pts/2    00:00:00 grep mysql

代码语言：javascript复制

root@db 16:20:  [(none)] usetestdb;
Database changed
root@db 16:23:  [testdb] select *from t into outfile '/tmp/test_t.sql';
Query OK, 3 rows affected (0.01 sec)
 
root@db 16:24:  [testdb]

#查看导出数据
[root@JiekeXu tmp]# more test_t.sql
1       xxq     male
2       lqq     f
N      wbx     f

注意：这里导出的数据默认以空格分隔，若使用其他作为分隔符可在导出时添加参数fields terminated by ‘字段间分隔符’， 定义字段间的分隔符，还可添加 optionally enclosed by ‘字段包围符’定义包围字段的字符（数值型字段无效），以及行分隔符 lines terminated by ‘行间分隔符’， 定义每行的分隔符 ；完整的语法可如下所示：

代码语言：javascript复制

select * fromt into outfile '/tmp/t.csv' fields terminated by',' optionally enclosed by'"' lines terminated by'rn';

导出数据后，将原表数据删除再使用 load data 导入数据。

此方法对于单个表的备份非常有利，但不知大家发现没有，此备份都是将数据存在数据库服务器上，我们只能用类似 mysql -e "SELECT ..." > file_name的命令将文件输出到客户机上。使用本机去连虚拟机数据库可将其数据备份下来，不用登陆数据库服务器便可实现。

那么，今天就讲到这里了，还有很多场景也许没有涉及到，但限于篇幅等有机会在说吧，mydumper、XtraBackup 等备份工具等下次在介绍，保持关注就可以了！

参考资料：

https://blog.csdn.net/jesseyoung/article/details/41346861

张甦 著 《MySQL王者晋级之路》

---

云数据库SQLServer 数据备份 数据库 sql

0 人点赞