MySQL备份与恢复

一、数据库备份的分类

1.数据备份的重要性

• 在生产环境中，数据的安全性至关重要
• 任何数据的丢失都可能产生严重的后果
• 造成数据丢失的原因
  1.程序错误
  2.认为操作错误
  3.运算错误
  4.磁盘错误
  5.灾难（火灾、地震）和盗窃

2.数据备份的分类

从物理与逻辑的角度，备份可分为

• 1.物理备份
  对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
  物理备份的方法：
  • 冷备份（脱机备份）：是在关闭数据库的时候进行的
  • 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
  • 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
• 2.逻辑备份
  对数据库逻辑组件（如，表等数据库对象）的备份

从数据库的备份策略角度，备份可分为：

• 1.完全备份：每次对数据库进行完整的备份
• 2.差异备份：备份自从上次完全备份之后被修改过的文件
• 3.增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

三、常见的备份方法

• 1.物理冷备
  备份时数据库处于关闭状态，直接打包数据库文件
  备份速度快，恢复时也是最简单的
• 2.专用备份工具mydump或mysqlhotcopy
  mysqldump常用的逻辑备份工具
  mysqlhotcopy仅拥有备份MylSAM和ARCHIVE表
• 3.启用二进制日志进行增量备份
  进行增量备份，需要刷新二进制日志
• 4.第三方工具备份
  免费的MySQL热备份软件Percona XtraBackup

四、完全备份

• 是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基础

优点：

备份与恢复操作简单方便

缺点：

数据存在大量的重复
占用大量的备份空间
备份与恢复时间长

1.完全备份分类

• 物理冷备份与恢复
  关闭MySQL数据库
  使用tar命令直接打包数据库文件夹
  直接替换现有MySQL目录即可
• mysqldump备份与恢复
  MySQL自带的备份工具，可方便实现 对MySQL的备份
  可以将指定的库、表导出为SQL脚本
  使用命令mysql导入备份的数据

2.物理冷备份及恢复操作

备份

systemctl stop mysqld
mkdir /backup
tar -zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

恢复

mkdir /bak
mv /usr/local/mysql/data/ /bak/
tar -zxvf /backup/mysql_all-2020-12-01.tar.gz
mv /backup/usr/local/mysql/data /usr/local/mysql/
systemctl start mysqld

3.mysqldump备份与恢复

###备份

• 单库备份

mkdir /backup
mysqldump -u root -p mysql > /backup/mysql.sql
##-p后可带密码，但明文显示不安全，不带密码则会在之后交互输入，mysql为要备份的数据库，备份文件以.sql结尾

• 多库备份

mysqldump -u root -p --databases mysql aa bb > /backup/databases-mysql-aa-bb.sql
##同时备份mysql、aa、bb三个库

• 所有库备份

mysqldump -u root -p --all-databases > /backup/all-data.sql
##备份所有数据库

• 备份单张表

mysqldump -u root -p aa test > /backup/aa_test-$(date +%F).sql	#备份aa库下的test表

恢复

• source命令恢复

use mysql	#在需要恢复的数据库内
source /backup/mysql.sql

• mysql命令恢复

mysql -u root -p mysql < /backup/mysql.sql

• 恢复单张表

1.use aa
source /backup/aa_test-2020-12-01.sql
2.mysql -u root -p aa < /backup/aa_test-2020-12-01.sql

五、增量备份

• 使用mysqldump进行完全备份存在的问题
  备份数据中有重复数据
  备份时间与恢复时间张
• 增量备份
  是自上一次备份后增加/变化的文件或者内容
• 增量备份特点：
  没有重复数据，备份量不大，时间短
  恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复（对于同一个日志文件）
• MySQL没有提供直接的增量备份方法
  可通过MySQL提供的二进制日志间接实现增量备份
• MySQL二进制日志对备份的意义
  二进制日志保存了所有更新或者可能更新数据库的操作
  二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
  只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并机实把这些日志保存到安全的地方就完成了一个时间段的增量备份
• 增量恢复方式：

1.一般恢复
将所有备份的二进制日志内容全部恢复
2.基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
3.基于时间点恢复
跳过某个发生错误的时间点实现数据恢复

1.增量备份操作

• 完整备份

mysqldump -u root -p aaa > /backup/aaa-$(date +%F).sql

• 开启增量备份

vi /etc/my.cnf
[mysqld]
log_bin=/usr/local/mysql/data/mysql_bin
systemctl restart mysqld

• 操作增量备份

mysqladmin -u root -p flush-logs   ##将二进制日志更新，产生新的日志文件
cd /usr/local/mysql/data 
ls -lh  查询增量备份结果

• 查询该二进制日志内容是否正确

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003

• 进行写入操作，模拟加入错误操作

insert into test values('aa','90');	#正确操作
delete from test where name='bb';		#模拟误删除
insert into test values('cc','65');	#正确操作

• 再次操作增量备份

mysqladmin -u root -p flush-logs 	##将二进制日志更新，产生新的日志文件

• 查询产生新的日志

cd /usr/local/mysql/data 
ls -lh  #查询增量备份结果

• 查询该二进制日志内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000004

2.增量恢复操作

1.基于时间点恢复

删除原先坏掉的那张表

use aaa;
drop table test;

还原到完全备份时的数据库

mysql  -u root -p aaa < /opt/bak_sql/aaa-2020-12-01.sql

停止恢复的时间（错误操作开始时间）

mysqlbinlog --no-defaults --stop-datetime='2020-12-01 12:13:20' /usr/local/mysql/data/mysql_bin.000004 | mysql -u root -p

继续开始恢复的时间（错误操作后的第一个正确操作开始时间）

mysqlbinlog --no-defaults --start-datetime='2020-12-01 12:13:30 ' /usr/local/mysql/data/mysql_bin.000004 | mysql -u root -p

2.基于位置恢复

注：必须保证正确和错误的操作在一个日志文件中

删除原先坏掉的那张表

use aaa;
drop table test;

还原到完全备份时的数据库

mysql  -u root -p aaa < /opt/bak_sql/aaa-2020-12-01.sql

查询该二进制日志内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000004

上一次正确操作的位置点停止

mysqlbinlog --no-defaults --stop-position='559' /usr/local/mysql/data/mysql_bin.000004 | mysql -u root -p

下一次正确操作的位置点开始

mysqlbinlog --no-defaults --start-position='655' /usr/local/mysql/data/mysql_bin.000004 | mysql -u root -p