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,则必须先恢复原来的表的结构。

常用的语法如下:

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

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

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 服务方可生效。

vi /etc/my.cnf
secure_file_priv=''

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

[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
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 ‘行间分隔符’, 定义每行的分隔符 ;完整的语法可如下所示:

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

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

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

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

参考资料:

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

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


本文分享自微信公众号 - JiekeXu之路(JiekuXu_IT)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-04-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

SAP R/3系统的R和3分别代表什么含义,负载均衡的实现原理

版权声明:本文为博主汪子熙原创文章,未经博主允许不得转载。 https://jerry.bl...

8530
来自专栏Jerry的SAP技术分享

SAP ABAP里数据库表的Storage Parameters从哪里来的

版权声明:本文为博主汪子熙原创文章,未经博主允许不得转载。 https://jerry.blog....

8320
来自专栏小网管运维之路

logstash sql 数据采集

22640
来自专栏Jerry的SAP技术分享

一个最简单的例子学会使用nodejs redis库进行数据库操作

要学会使用Redis数据库,总的先有个可用的数据库吧。这个只有大家自己想办法了,我用的是SAP云平台上的Redis实例,很多其他的云平台比如GCP,微软的Azu...

19180
来自专栏Jerry的SAP技术分享

Jerry的CRM Middleware(中间件)文章合集

1. 2013年下半年和2014年上半年曾经支持过中联重科和蒙牛的CRM项目相关的中间件问题;

12520
来自专栏好好学java的技术栈

你清楚HTTP缓存机制和原理吗?

Http 缓存机制作为 web 性能优化的重要手段,对于从事 Web 开发的同学们来说,应该是知识体系库中的一个基础环节,同时对于有志成为前端架构师的同学来说是...

12320
来自专栏迹_Jason的AppZone

MongoDB的Spring配置使用

Spring-data对MongoDB进行了很好的支持,接下来就讲解一下关于Spring对MongoDB的配置和一些正常的使用

21920
来自专栏Jerry的SAP技术分享

如何在SAP云平台上使用MongoDB服务

首先按照我这篇文章在SAP云平台上给您的账号分配MongboDB服务:如何在SAP云平台的Cloud Foundry环境下添加新的Service

11810
来自专栏Jerry的SAP技术分享

SAP Netweaver和Hybris的数据库层

在SAP基于Netweaver的ABAP应用里,应用开发人员用Open SQL访问数据库, 这些Open SQL会被Database interface(数据库...

10720
来自专栏悠扬前奏的博客

Groovy-16.数据库

UPDATE和DELETE操作都是写好SQL然后通过execute执行,但是需要commit()提交(在后文“提交”中介绍)。

12720

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励