mysql备份与恢复

数据备份

数据备份得重要性

在生成环境中，数据安全性至关重要
任何数据得丢失都可能产生严重后果

造成数据丢失得原因

程序错误
人为操作事务
运算错误
磁盘故障
灾难（火灾、地震）和盗窃

备份的分类

从物理与逻辑角度

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

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

从数据库的备份策略角度

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

完全备份在每次备份时都会将所有数据备份一次，差异备份会备份完全备份之后的所有数据，二者都无法避免大量的数据重复，增量备份每次都是在上次备份的基础上对被修改的文件进行备份，节省磁盘空间

常见的备份方法

物理冷备：

备份时数据库处于关闭状态，直接打包数据库文件，备份速度块，恢复时也是最简单的

专用备份工具mydump或mysqlhotcopy

mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份myisam和archive表

启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志（因为每次启动都会产生新的二进制日志文件）
日志的生成条件：服务重启，刷新日志，超过储存容量

第三方工具备份

免费mysql热备份软件percona xtrabackup

mysql完全备份

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

优点：备份恢复操作简单

缺点：数据存在大量的数据重复，占用大量的备份空间，备份和恢复时间过长

分类

物理冷备份与恢复

关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可

systemctl stop mysqld #关闭mysql数据库 
mkdir /backup #创建一个备份目录
tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ #打包数据库所有数据，并标名日期

查看备份文件

登录数据库，删除表，模拟数据丢失

systemctl start mysqld  #开启数据库
mysql -uroot -p #进入数据库
drop database a; #删除数据库a
show databases; #查看数据库
exit; #退出mysql

恢复数据

systemctl stop mysqld #停止数据库
tar zxf /backup/mysql_all-$(date +%F).tar.gz #解压备份压缩包
mkdir mysql_old #创建出问题的数据库备份目录
mv /usr/local/mysql/data/ mysql_old #将当前的数据库数据移动到此处
mv usr/local/mysql/data/ /usr/local/mysql/ #将解压的备份数据，移到mysql目录下
systemctl start mysqld #启动mysql
mysql -uroot -p # 登录mysql
show databases; #查看数据库

mysqldump备份与恢复

mysqldump备份

MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库，表导出为SQL脚本（后缀为.sql）
使用命令mysql导入备份的数据
mysqldump命令进行完全备份
单库备份

mysqldump -u 用户名 -p [密码] [选项] [数据库名] > /备份路径/备份文件名
mysqldump -uroot -p123123 a > /backup/a-$(date +%F).sql

多库备份

mysqldump -u 用户名 -p [密码] [选项] --databases 库名1 [库名2]...
mysqldump -uroot -p123123 --databases a b > /backup/ab-$(date +%F).sql

所有库备份

mysqldump -u 用户名 -p [密码] [选项]  --all-databases > /备份路径/备份文件名
mysqldump -uroot -p123123 --all-databases > /backup/all-$(date +%F).sql

查看备份文件

恢复数据库使用

mysqldump导出
source命令
mysql命令

使用source恢复数据库
登录到mysql数据库
执行source备份sql脚本的路径

登录mysql，删除数据库，模拟数据库故障，然后恢复

恢复数据库

source []< /备份路径/备份文件名
source /backup/ab-2020-12-25.sql

mysql -u 用户名 -p 【密码】 < 库备份脚本路径
mysql -uroot -p123123 < /backup/all-2020-12-25.sql

表的恢复

恢复表时同样可以使用source或者mysql命令
source恢复表的操作与恢复库的操作相同
当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在

mysqldump -uroot -p123123 a b > /backup/a-b.sql #创建表的备份文件
mysql -uroot -p #登录数据库
drop table a.b; #删除数据库a的表b
source /backup/a-b.sql #恢复数据库a里的表b

增量备份与恢复

增量备份

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

是自上次备份后增加/变化的文件或者内容

特点
没有重复数据，备份量不大，时间短
恢复需要上次完全备份以及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复（对同一个日志文件）

mysql没有提供直接的增量备份方法

可通过mysql提供的二进制日志间接实现增量备份

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

恢复

一般恢复：将所有备份的二进制日志内容全部恢复
基于位置恢复：数据库在某一时间点可能既有错误操作也有正确操作可以基于精准的位置跳过错误的操作
基于时间点：恢复跳过某个发生错误的时间点实现数据恢复（停止在发生错误的时间点）
在错误的时间点开始，在下一个正确的时间点开始

基于时间点恢复

基本步骤

删除原先坏掉的那张表
还原完全备份的那个数据库
停止在错误的时间点
开始在正确的时间点

流程

开启二进制日志文件

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


systemctl restart mysqld #重启服务

查看二进制日志文件

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000001 
--no-defaults：解决utf-8报错
--base64-output=decode-rows：解决乱码问题
-v：输出文件指向无

无新增的信息

mysql -uroot -p123123 #进入mysql
create database a;
use a;
create table b(name varchar(128), score int(4)); #创建表b
insert into b values('p',80);
insert into b values('h',70);#插入数据
select * from b;  #查看表b数据

exit #退出mysql
mysqldump -uroot -p123123 --all-databases  > /backup/a.sql #建立完全备份
cd  /backup/
ls -lh #查看

mysqladmin -uroot -p flush-logs  #刷新日志 #生成02日志
mysql -uroot -p123123 #进入数据库
delete from b where name='p'; #进入mysql删除表b的name为h的数据，作为错误操作,此步骤保存在02日志中
insert into b values('ph',90); #进入数据库在表b增加数据，此步骤保存在02日志
mysqladmin -uroot -p flush-logs  #退出mysql，刷新日志 生成03日志
select * from b; #查看表b

查看二进制日志文件

cd /usr/local/mysql/data/
ls -lh

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

数据损坏

进入数据库后删除数据库a
drop database a; 
建立新的数据库a
create database a;
use a;
source /backup/a.sql #恢复完全备份的数据库
退出mysql后进行恢复
mysqlbinlog --no-defaults --stop-datetime='2020-12-27   23:54:03' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p #停止在错误的时间点
mysqlbinlog --no-defaults --start-datetime='2020-12-27   23:54:11 ' /usr/local/mysql/data/mysql_bin.000002 | mysql -uroot -p #恢复在正确的时间点

进入数据库后查看表b

基于位置点恢复

基本步骤

位置点恢复
删除原先坏掉的那张表
任意删除两个数据字段
利用日志，备份恢复
查询该二进制日志内容
上一次正确操作的位置点停止
下一次正确操作的位置点开始

流程

mysql -uroot -p123123use a;
delete from b where name='p';
delete from b where name='h';
select * from a.b;

退出mysql后恢复数据

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

mysqlbinlog --no-defaults --stop-position='1776' /usr/local/mysql/data/mysql_bin.000004 | mysql -uroot -p #停止于错误位置点
mysqlbinlog --no-defaults --start-position='2028' /usr/local/mysql/data/mysql_bin.000004 | mysql -uroot -p #开始于下次正确位置点

进入mysql查看表b