MySQL数据库备份与恢复

数据库备份的重要性

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

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘故障
• 灾难(如火灾、地震)和资窃

数据库备份分类

从物理与逻辑的角度，备份可分为
●物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
◆物理备份方法
1.冷备份(脱机备份) :是在关闭数据库的时候进行的
2.热备份(联机备份) :数据库处于运行状态，依赖于数据库的日志文件
3.温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
●逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
从数据库的备份策略角度，备份可分为

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

常见的备份方法

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

物理冷备份及恢复方法

1.先关闭数据库，之后打包备份

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data

2.误删除操作

mysql> delete from ky06 where xingming='yangzihan';

3.恢复数据库

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /bak
[root@localhost ~]# mv /usr/local/mysql/data /bak
[root@localhost ~]# mkdir /restore
[root@localhost ~]# tar zxf /backup/mysql_all-$(date +%F).tar.gz -C /restore
[root@localhost ~]# mv /restore/usr/local/mysql/data /usr/local/mysql/
[root@localhost ~]# systemctl start mysqld

4.查看数据库

mysqldump备份和恢复（无需关闭数据库）

1.备份数据库

mysqldump -u root -p --all-databases > all-data-$(date +%F).sql /备份所有数据库
mysqldump -u root -p --databases auth mysql > auth-mysql.sql //备份auth和mysql库mysqldump -u root -p auth > auth-$(date +%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表的结构。

2.还原数据库

进入数据库use数据库
1.create database你删除的数据库名后再建同名新库
2.source /tmp/auth.sql //要先进入某个具体的数据库再进行恢复，除非是恢复所有数据库。数据库外面
mysql -u root -p < all-data-$(date+mysql-uroot-pmysql〈mysql-user-$(date +%F). sql //恢复mysql的user表，需要指定库名，该库是否存在是关键。
mysql -uroot -p auth </tmp/desc-mysql-user. sql//将数据结构恢复到auth库中，单库备份的时候要指定还原到哪个数据库中，需要建立空库，库名可以任意

方法一恢复:
drop database client;
show databases;
create database client; //建立空库
mysql -u root -p client< client_user_info-$ (date +%F).sql
方法二恢复:
drop database client;
create databese client;
use client;
mysql>source /root/client_user_info-$ (date +%F).sql

MySQL增量备份与恢复

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

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

[root@localhost ~]# vi /etc/my.cnf
log-bin=mysql-bin

2.查看二进制文件 mysql-bin.000001

3.对数据库进行操作

mysql> insert into ky06(xingming,chengji) values('xiaopihai',85);
mysql> delete from ky06 where xingming='yangzihan';
mysql> insert into ky06(xingming,chengji) values('xiaohua',80);

4.刷新二进制文件

mysqladmin -uroot -p flush-logs
ll /usr/local/mysql/data/

5.查看二进制文件

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001

根据位置点恢复

[root@localhost data]# mysqlbinlog --no-defaults --stop-position='472' mysql-bin.000001 | mysql -uroot
[root@localhost data]# mysqlbinlog --no-defaults --start-position='589' mysql-bin.000001 | mysql -uroot 

跟据时间点恢复

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-11-03 10:07:26' mysql-bin.000003 | mysql -uroot -p
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-11-03 10:26:05' mysql-bin.000003 | mysql -uroot -p