数据库的备份与恢复

数据库备份的分类

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

物理备份：

对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。
物理备份的方法：

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

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

• 完全备份：每次对数据库进行完整的备份
  完全备份是对整个数据库的备份、数据库结构和文件结构的备份
  完全备份保存的是备份完成时刻的数据库
  完全备份是增量备份的基础
• 差异备份：备份自从上次完全备份之后被修改过的文件
• 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

常见的备份方法

物理备份

备份时数据库处于关闭状态，直接打包数据库文件。备份速度快，恢复时也是最简单的，常用tar命令、cp命令。

专用备份工具mydump

mysqldump是常用的逻辑备份工具（mysqldump备份需要和mysql进行数据交互，如果关闭mysql 则无法备份和恢复）

命令格式：

mysqldump -u 用户名 -p [密码] [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名
mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名

MySQL数据库完全备份操作

1.物理冷备份与恢复（tar命令直接打包数据库文件夹）

备份数据库：

[root@localhost ~]# systemctl stop mysqld （关闭数据库）
[root@localhost ~]# mkdir /backup （在根下新建个backup目录）
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ （/usr/local/mysql/data备份压缩到/backup命名加时间戳）
[root@localhost ~]# ll /backup/ （查看备份）
[root@localhost ~]# rm -rf /usr/local/mysql/data/ （模拟故障，实际情况慎重操作）

数据库恢复：

[root@localhost ~]# tar xzvf /backup/mysql_all-2020-09-22.tar.gz -C /root/ （将备份数据库解压到 /root目录下）
[root@localhost ~]# mv /root/usr/local/mysql/data/ /usr/local/mysql/ （将备份数据移动到到/usr/local/mysql里）
[root@localhost ~]# systemctl start mysqld （启动mysql数据库）
[root@localhost ~]# systemctl status mysqld （查看mysql数据库启动状态 ，日志文件显示正常）

2、mysqldump备份与恢复（将指定的库、表、或全部的库导出为SQL脚本）

常用格式： $(date +%T) 时间戳

mysqldump -u 用户名 -p [密码] [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名
单个库或单个多个表备份，用这种方式来备份库的话恢复时需要先创建这个库：
mysqldump -u root –p mysql [user] > /bakcup/mysql-$(date +%T).sql
mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名
多个库备份要加–databases单个也可以加加了后就不需要先创建库了可以直接恢复
mysqldump -u root –p --databases auth mysql > /backup/databases-auth-mysql.sql
mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
所有库备份加–all-databases
mysqldump -u root -p --opt --all-databases > /backup/all-data.sql

创建环境：

进入MySQL数据库
mysql> create databases bai; （创建名为bai数据库）
mysql> create table bai.users (id int(10), name CHAR(48)); （创建名为users表）
mysql> insert into bai.users values(1,‘zhangsan’); （往表里写点数据）
mysql> insert into bai.users values(1,‘zhangsan’); （往表里写点数据）

备份：

[root@localhost ~]# mysqldump -u root -p bai user > /backup/my_user.sql （备份数据库bai中的user表，文件保存在/backup目录下）
[root@localhost ~]# mysqldump -u root -p --databases bai > /backup/db.sql （备份bai数据库，文件保存在/backup目录下）

恢复操作格式：

mysql [选项] [库名] [表名] < /备份路径/备份文件名

恢复表：

[root@localhost /]# mysql -u root -p -e “create database test” （新建数据库test ,等下导入表用）
[root@localhost /]# mysql -u root -p text < mysql-user.sql （根据上述的恢复操作，将user表导入数据库test）
[root@localhost /]# mysql -u root -p -e “select * from test” （验证输出结果，可以看到user表已经导入数据库test）

恢复库：

[root@localhost /]# mysql -u root -p -e ‘drop database bai’ （删除bai库，模拟故障）
[root@localhost /]# mysql -u root -p < /backup/db.sql （导入数据库bai）
[root@localhost /]# mysql -u root -p -e ‘show databases’ （查看所有数据库有没有bai）

使用mysqldump命令导出的SQL备份脚本，在进行数据恢复时可使用以下方法导入

mysq|命令 Linux模式中运行
使用source恢复数据库的步骤
登录到MySQL数据库
执行source备份sq|脚本的路径（绝对路径）

source 恢复例子

MySQL [(none)] > source /backup/all-data.sql

使用mysq|命令恢复数据

mysql -u用户名-p [密码] <库备份脚本的路径

恢复表的操作

恢复表时同样可以使用source或者mysql命令进行
source恢复表的操作与恢复库的操作相同
当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在
mysql -u用户名-p [密码] < 表备份脚本的路径
mysql -u root -p mysql < /backup/mysql-user.sql

MySQL增量备份

使用mysqldump进行完全备份的存在的问题

备份数据中有重复数据
备份时间与恢复时间长
增量备份就是备份自上一次备份之后增加或变化的文件或者内容

增量备份的特点

没有重复数据，备份量不大，时间短
恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
MySQL没有提供直接的增量备份方法可以通过MySQL提供的二进制日志(binary logs)间接实现增量备份

MySQL二进制日志对备份的意义

二进制日志保存了所有更新或者可能更新数据库的操作
二进制日志在启动MySQL服务器后开始记录，并在文件达到max_ binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份

MySQL数据库增量恢复

一般恢复： 添加数据 > 进行完全备份 > 录入新的数据 > 进行增量备份 > 模拟故障 > 恢复操作

基于位置恢复

就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复

基于时间点恢复

使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式
mysqladmin -uroot -p flush-logs 日志分隔命令
mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 翻译查看指定的日志文件

增量恢复的方法

一般恢复

mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u用户名 -p
mysqlbinlog [–no-defaults] /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p

基于位置的恢复

1、恢复数据到指定位置
mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
mysqlbinlog --stop-position=‘332’ mysql-bin.000005 | mysql -uroot -p #332日志文件每一段操作at后
2、从指定的位置开始恢复数据
mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
mysqlbinlog --start-position=‘211’ mysql-bin.00005 | mysql -uroot -p #211日志文件每一段操作at后
3、从指定的位置开始结束恢复数据
mysqlbinlog --start-position=’操作 id’ --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码
mysqlbinlog --start-position=‘211’ --stop-position=‘332’ mysql-bin.00005 | mysql -uroot -p

基于时间点的恢复

1、从日志开头截止到某个时间点的恢复
mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
mysqlbinlog --stop-datetime=‘2020-09-20 12:10:10’ mysql-bin.000005 | mysql -uroot -p
2、从某个时间点到日志结尾的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
mysqlbinlog --start-datetime=‘2020-09-20 11:10:10’ mysql-bin.000005 | mysql -uroot -p
3、从某个时间点到某个时间点的恢复
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
mysqlbinlog --start-datetime=‘2020-09-20 11:10:10’ --stop-datetime=‘2020-09-20 12:10:10’ mysql-bin.000005 | mysql -uroot -p

shell脚本自动备份

#!/bin/bash
user=root
passwd=root!
backup_dir=/backup/
name_dir=$(date +%F"-%H")
single_database=test
binlog_dir=/usr/local/mysql/binlog
if [ ! -d $backup_dir ];  then
	mkdir -p $backup_dir
fi
if [ ! -d  $backup_dir$name_dir ];  then
	mkdir -p $backup_dir$name_dir
fi
#全量备份
mysql_all(){
	cd $backup_dir$name_dir
	mysqldump -u$user -p$passwd  --master-data --flush-logs --all-databases > $backup_dir$name_dir/$name_dir.sql
}
#单个数据库备份
mysql_single(){
	cd $backup_dir$name_dir
	mysqldump -u$user -p$passwd --master-data --flush-logs --databases $single_database > $backup_dir$name_dir/$name_dir-$single_database.sql
}
#增量备份
mysql_increment(){
    cd $binlog_dir
	mysqladmin -u$user -p$passwd flush-logs
	cp $(ls mysql-bin.[0-9]* | head -n -1 | sort -rg | head -n 1) $backup_dir$name_dir/
}
$1

设置计划任务：

在周五实行全备。
在周一到周六实行增量备份。
因为过期日志在七天会自动清除。

crontab -e
0 1 * * 0 /bin/bash /root/mysqldump.sh mysql_all >/dev/operate_`date +"\%Y\%m\%d"`.log 2>&1
0 1 * * 1-6 /bin/bash /root/mysqldump.sh mysql_increment >/dev/operate_`date +"\%Y\%m\%d"`.log 2>&1