MySQL备份与恢复

数据备份的重要性

在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难（如火灾，地震）和盗窃
数据库备份的分类

数据库备份的分类

从物理与逻辑的角度

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

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

从数据库的备份策略角度

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

完全备份与恢复

常见的备份方法

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

简介

是对整个数据库、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础
每次对数据进行完整的备份，完全备份是增量备份的基础，完全备份保存的是备份完成时刻的数据库
优点
备份与恢复操作简单方便
缺点
数据存在大量的重复
占用大量的备份空间
备份与恢复时间长

分类

物理冷备份与恢复
关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可

mysqldump备份与恢复
MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL脚本（.sql结尾）
使用命令MySQL导入备份的数据

物理冷备份与恢复步骤

创建数据库数据

create database sy;
use sy; 
create table a(id int(10)); 
desc a; 
exit 

数据备份打包，数据丢失，损坏

systemctl stop mysqld 
tar zcvf /opt/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/  #源文件来源路径,备份打包所有数据库，当天日期
systemctl start mysqld
mysql -uroot -123456
drop database sy;
show databases;
exit

恢复数据

systemctl stop mysqld 
cd /opt/ 
tar zxvf mysql_all-2020-12-26.tar.gz
mkdir mysql_old 
mv /usr/local/mysql/data/ mysql_old/    #剪切在备份完成之前产生的数据
mv /usr/local/mysql/data/ /usr/local/mysql/
cd /usr/local/mysql/
systemctl start mysqld 
mysql -uroot -p123456
show databases;     #查看数据库信息 ，数据恢复
use sy; 
show tables; 
desc a; 
exit 

mysqldump（热备份）备份数据库

备份格式

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql  #备份所有数据库
mysqldump -u root -p -databases auth mysql > auth-mysql.sql     #备份auth和mysql库
mysqldump -u root -p auth > auth-$(data +%F).sql                #备份auth数据库
mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql    #备份mysql的user表
mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql   #备份mysql库user表的结构

恢复：
基于mysql命令恢复

mysqldump -u root -p sy > test-$(date +%F).sql #使用mysqldump工具备份sy库
Enter password:  #输入密码，不显示

mysql -uroot -p123456
drop database sy; #删除sy库模拟数据库故障
create database b; #建立空库
exit

mysql -u root -p b < test-2020-12-27.sql #恢复至b库
Enter password:  

mysql -uroot -p123456
show databases;
use b;
show tables;

source基于脚本恢复

drop database b;
create database aa;
use aa;
mysql> source /root/test-2020-12-27.sql;   #通过.sql格式的备份文件恢复至当前库中
show tables;
select * from a;

增量备份与恢复

MySQL的日志文件记录了详细的操作过程，千万不能随便删！！！

简介

使用mysqldump进行完全备份存在的问题：
备份数据中有重复数据
备份时间与恢复时间过长

MySQL增量备份是自上一次备份后增加/变化的文件或者内容
特点
没有重复数据，备份量不大，时间短
恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

MySQL没有提供直接的增量备份方法
可通过MySQL提供的二进制日志间接实现增量备份

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

分类

一般恢复
将所有备份的二进制日志内容全部恢复

断点恢复
基于时间点恢复
跳过某个发生错误的时间点实现数据恢复

基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作

一般恢复

mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p

基于时间点恢复

跳过某个发生错误的时间点实现数据恢复
恢复数据到指定时间（停止错误操作的时间）

mysqlbinlog --stop-datetime='错误时间' 二进制日志 | mysql -u 用户名 -p密码        #恢复数据至指定的位置（误操作开始时间）
mysqlbinlog --start-datetime='正确操作时间' 二进制日志 | mysql -u 用户名 -p密码   #从指定的位置开始恢复数据（跳过误操作后指定正确操作的时间）

开启二进制日志文件

vi /etc/my.cnf    #添加
log_bin=/usr/local/mysql/data/mysql_bin
选项                          日志名称

systemctl restart mysqld  #服务重启，日志生成
cd /usr/local/mysql/data/  
ll
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000001    #查看二进制日志内容

创建库

create table word(name varchar(128), score int(10)); 
insert into word values('zhu',88); 
insert into word values('tao',87);
select * from word; 

备份

mysqldump -uroot -p123456 aa  > /opt/aa-word.sql
cd /opt/ 查看备份
ll
mysqladmin -uroot -p flush-logs  #刷新日志
cd /usr/local/mysql/data/
ll

再次增量备份

mysql -uroot -p123456 
use aa; 
insert into word values('meng',75);   #插入数据记录 正确操作
delete from word where name='zhu';    #删除数据记录，误操作
insert into word values('bu',85);       #插入数据记录 正确操作
select * from word;  #查询所有字段
exit

mysqladmin -uroot -p flush-logs
cd /usr/local/mysql/data/
ll
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002

数据损坏

mysql -uroot -p123456 
show databases; 
drop database aa;  
show databases;  
create database aa; #创建同名为空的数据库
show databases; 
use aa; 
source /opt/aa-word.sql  #还原完全备份的那个数据库
select * from word; 
exit 

恢复数据

mysqlbinlog --no-defaults --stop-datetime='2020-12-27 21:30:40' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p
#停止在错误的时间点
mysqlbinlog --no-defaults --start-datetime='2020-12-27 21:31:16' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p
#开始在正确的时间点

mysql -uroot -p123456 
use aa;
select * from word; 

基于位置的恢复

恢复数据到指定位置（误操作前的位置）

mysqlbinlog --stop-position='操作id' 二进制日志 | mysql -u 用户名 -p密码

从指定的位置开始恢复数据（误操作后正确操作的位置，即误操作后的第一个位置节点）

mysqlbinlog --start-position='操作id' 二进制日志 | mysql -u 用户名 -p密码

设置数据库

delete from word where name='meng'; 
delete from word where name='tao';
select * from word; 

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003  #查看日志

mysqlbinlog --no-defaults --stop-position='3184' /usr/local/mysql/data/mysql_bin.000003 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position='3537' /usr/local/mysql/data/mysql_bin.000003 | mysql -uroot -p

mysql -uroot -p123456
use aa;
select * from word;