MySQL备份和恢复

一、MySQL的备份

1.1 备份相关概念

备份的目的：备灾，防止数据丢失

在生产环境中，数据的安全性是非常重要的。

数据丢失的可能原因：

• 程序问题，程序出错
• 人为的问题
• 磁盘故障

备份的策略

全量备份：每次对数据库进行完整的备份。

增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份。

备份的分类：

• 物理备份：对磁盘或者对文件直接进行备份。
  • 冷备份：脱机备份，先把指定的服务关闭/程序停止，然后进行备份
  • 热备份：联机备份，不用关闭服务/停止程序，就可以对数据进行备份
• 逻辑备份：根据数据库文件当中保存的SQL语句、表结构等等，以特定的格式和命令对文件的内容进行还原。
  • 逻辑备份是热备份的一种。
  • **逻辑备份只能对表进行恢复，库没了没有办法恢复。**所以一般结合全量备份使用。

1.2 全量备份

1.2.1 物理备份

物理备份一般是全量备份，把数据库的内容整个一次性的备份。全量备份常用冷备份。

yum -y install xz
##使用xz软件进项压缩，没有的先yum安装
tar -jcvf /opt/mysq_all_$(date +%F).tar.xz /usr/local/mysql/data
## 打包数据库文件存放目录/usr/local/mysql/data
## 压缩包命名按日期命名，便于管理

物理备份的恢复：

把打包的数据库文件解压到数据库工作目录即可

1.2.2 mysqldump备份

mysqldump ：mysql自带的备份命令，可以备份库，也可以备份库里的表

备份库：

#在命令行操作,备份指定的库
mysqldump -u root -p --datebases basename1 basename2 > /opt/filename.sql
#备份全部的库(所有)
mysqldump -u root -p --all-datebases > /opt/filename.sql

恢复库：

mysql -u root -p < /opt/filename.sql

即时生效，是热备份

备份表：要指定库名和表名

mysqldump -u root -p basename tablename1 tablename2 > /opt/filename.sql

恢复表：要指定库名

mysql -u root -p basename < /opt/filename.sql

1.3 增量备份

增量备份使用binlog日志间接实现

要先开启二进制日志功能：binlog日志记录

binlog ：逻辑备份，会生成一个文件，包含了SQL语句，要使用特定的方式和语句才能够恢复。

vim /etc/my.cnf
#添加代码行
log-bin = mysql-bin
#开启二进制日志功能
binlog_format = MIXED
#记录二进制日志的文件格式为MIXED

binlog的文件格式

• STATEMENT ：基于SQL语句：只是记录用户操作的SQL语句，效率较高。但是在高并发的情况下，记录操作的SQL语句的顺序可能会出错，导致恢复数据有误差或丢失数据。

• ROW ：基于行：记录每一行的数据，准确，高并发也不会出错，但是恢复的效率低。

• MIXED ：混合模式：正常情况下（非高并发情况），使用STATEMENT，高并发情况下使用ROW。服务会智能判断工作情况，自行切换格式。

开启二进制日志成功后，在工作目录(data目录)会看到mysql-bin.000001

cd /usr/local/mysql/data
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
#日志文件需要专门的解码方式读取

#使用binlog日志文件恢复数据
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -p

#为binlog日志文件创建断点
mysqladmin -u root -p flush-logs
#后续的SQL语句会保存在新的binlog文件中，这里示例中是mysql-bin.000002文件

从特定位置节点开始恢复

position（位置节点）：

在mysql-bin.000001 这些binlog日志文件中可以看到at 位置节点（数字），at后面的数字就是位置节点，每个SQL语句对应一个at。

mysqlbinlog --no-defaults --start-position='位置节点的名称' mysql-bin.000001 | mysql -u root -p

恢复到特定位置节点

mysqlbinlog --no-defaults --stop-position='位置节点的名称' mysql-bin.000001 | mysql -u root -p

从特定时间节点恢复

时间节点也是从binlog日志文件中获得，每个位置节点都有对应的时间节点

mysqlbinlog --no-defaults --start-datetime='开始时间' --stop-datetime='结束时间' mysql-bin.000001 | mysql -u root -p

注意事项：日志中的时间节点格式是 yymmdd hh:mm:ss，但是命令行中恢复数据时的开始时间和结束时间的格式要求是yyyy-mm-dd hh:mm:ss，要注意这个坑！

1.4 打开查询日志（select的记录）

vim /etc/my.cnf
general_log = ON
#开启通用查询日志,默认是开启的
general_log_file = /usr/local/mysql/data/mysql_general.log
#查询日志的保存位置
#报错日志默认是开启的，不需要ON
log-error = /usr/local/mysql/data/mysql_error.log
#报错日志的保存位置
slow_query_log = ON
#开启慢查询日志（执行记录超过一定时间的语句）
slow_query_log_file = /usr/local/mysql/data/mysql_slow_query.log
#慢查询日志的保存位置
long_query_time = 5
#默认的慢查询时间是10秒，这里设置为5秒，执行时间超过5秒的记录被保存