Mysql备份与恢复

一.数据库备份类型

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

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

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

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

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

二.常见的备份方法

1.物理冷备份

物理冷备份时需要在数据库处于关闭状态下，能够较好的保证数据库的完整性。物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备分的特点就是速度快，恢复时也是最为简单的。通常通过直接打包数据文件夹（本章的数据文件夹位于/usr/local/mysql/data）来实现备份

2.专门的备份工具mysqldump或mysqlhotcopy

mysqldump程序和mysqlhotcopy都可以做备份。mysqldump是客户端常用的逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个mysql数据库，对其进行备份或传输到远程的mysql服务器。mysqldump更为通用，因为它可以备份各种表。mysqlhotcopy仅仅适用于某些存储引擎。

3.通过启用二进制日志进行增量备份

mysql支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份，需要刷新二进制日志。

三.数据库的完全备份操作

通过实验进行进一步理解

（1）物理冷备份与恢复

1.部署实验环境

mysql  -uroot  -ppwd123              //登录到mysql数据库中

create   database   auth；           //创建数据库

use  auth                                     //切换到auth下

create  table  users(user_name  char(20),user_passwd  char(50)）;          //创建数据表

insert  into  users values('zhangsan',password（'123456'）);         //添加数据

insert  into  users  values（‘lisi’，‘123456’））；

2.备份数据库

systemctl  stop  mysqld                //关闭mysqld服务

mkdir  /bak          //创建一个目录为备份数据库的存储路径

tar  zcvf  /bak/mysql_$(date +%F).tar.gz mysql/            //使用tar创建备份文件，用年月日来命名

3.首先模拟故障

systemctl  start  mysqld          //先开启mysqld服务

mysql  -uroot  -ppwd123  -e   “drop  database  auth”         //删除数据库

mysql  -uroot   -ppwd123  -e  “select * from  auth.users”        //查看数据是否还在

4.恢复数据库

system  stop  mysqld            //关闭mysqld服务

cd  /bak

tar  zxcf  mysql_2024.6.27.tar.gz       //解压tar包

cd  /usr/local

mv   mysql/     mysql.bak            //将mysql数据库移动走

cd   bak

mv  mysql   /usr/local                 //将数据库再移动到安装目录下

systemctl    start   mysqld             //开启msyqld服务

mysql   -uroot  -p   -e  ‘select   *   from  auth.users；’         //查看数据库中写入的表

（2）mysqldump备份与恢复

注：在服务启动状态下运行

1.导出数据

mysqldump  -uroot  -p  auth  users >/opt/auth-users.sql        //导出数据表

mysqldump  -uroot -p  auth >/opt/auth.sql                  //导出数据库

mysqldump  -urrot  -p  --opt  --all-databases>/opt/all-data.sql       //备份所有库

注：--opt的作用为优化执行速度

2.导入表

如果已经存在数据库，可以将表导入任何一个数据库

mysql  -uroot  -p  -e  'create  database  test;'         //新建一个数据库

mysql  -uroot  -p   test</opt/auth-users.sql             //导入数据库

mysql  -uroot  -p    auth</opt/auth-users.sql           //导入原始所在的库

mysql   -uroot  -p   -e  ‘show  tables  from  test；’     //查看数据库信息

3.导入库

如果备份了完整的数据库，可以在mysql中没有该库信息的情况下直接导入该库

mysql  -uroot  -p  -e  “drop  database auth；”          //将原有的数据库删除

mysql  -uroot  -p   -e  “show  tables   databases；”       //查看原有数据库是否存在

mysql  -urrot  -p  -e “create  database  auth；”            //创建一个新的数据库

mysql  -uroot  -p  auth</opt/auth.sql                          //将备份库导入到新库中

mysql  -uroot  -p  -e  ‘show  databases；’              //查看和原数据库信息

mysql  -uroot  -p  -e  “select  *  from  auth.users；”      //查看和原数据表的信息

4.全库还原

mysql  -uroot  -p </opt/all-data.sql             //还原数据库

四.增量与备份

增量备份（incremental backup）是备份的一个类型，是指在一次完全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。

（1）启用二进制日志功能

1. vim   /etc/my.cnf                            //修改主配置文件

在[mysqld]模块中添加

log-bin=mysql-bin

server-id=1

binlog_format = MIXED

systemctl     restart      mysqld                //重启mysqld服务

2.生成新的日志文件

cd  /usr/local/mysql/data             //到安装目录下

mysqladmin  -uroot  -p   flush-logs           //刷新mysql服务的日志文件

3.查看日志内容

musqlbinlog  --no-defaults  mysql-bin.000002     //读取和显示二进制日志文件的内容

注意：如果sql语句被加密了，可以使用以下指令

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002

（2）向数据表中添加数据（多添加一些）

mysql  -uroot  -ppwd123             //进入mysql服务

use  auth                                  //切换到auth数据库下

添加以下数据

insert into users values('aaa','123456');

insert into users values('bbb','123456');

insert into users values('ccc','123456');

insert into users values('ddd','123456');

insert into users values('eee','123456');

insert into users values('fff','123456');

exit                 //退出mysql

（3）模拟错误操作将auth.users表删掉

mysql  -uroot  -p  -e  ‘drop  table  auth.users；’             //将auth.users表删掉

mysql  -uroot  -p  -e   ‘select  *  from  auth.users；’       //查看表是否还存在

（4）常规恢复操作

1.先恢复完全备份

mysql -uroot  -p  auth</opt/auth.sql              //恢复原始的数据库

mysql  -uroot  -p  -e  “select  *  from  auth.users；”       //查看表，已经存在，但数据不全

2.再恢复增量备份

mysqldbinlog   --no--defaults  --base64-output=DECODE-ROWS  mysql-bin.00000.2

注：要先查看日志内容，确定是需要恢复的数据

--no-defaults：不读取任何选项文件，其后需要直接跟日志文件或开始停止位置

（5）基于位置的恢复

1.模拟误操作

mysql  -uroot  -p  -e “drop  table  auth.users;”                //将表数据删除

mysql  -uroot  -p  -e “select  *  from  auth.users；”         //查看数据是否被删除

2.恢复完全备份

mysql  -uroot  -p  auth</opt/autt.sql                          //恢复原始的完全数据

3.查看日志内容

mysqlbinlog   --no-defaults  mysql-bin.00000.2         //从日志中分析哪些数据还没有恢复，找出想要恢复到哪个数据的操作ID

mysqlbinlog  --no-defaults    --stop-position='521' mysql-bin.000002 | mysql -u root -p

4.恢复到指定的地方

[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p      ##恢复到521之前的一个数据，不包含521

[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'   

5.从指定的位置向后开始恢复

[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' mysql-bin.000002 | mysql -u root -p     ##从521之后开始恢复，包含521

6.恢复指定范围内的数据

[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' --stop-position='729' mysql-bin.000002  | mysql -u root -p

（6）基于时间点恢复

1.恢复到指定的时间

[root@localhost mysql]# mysqlbinlog --no-defaults --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p

2.从指定的时间开始恢复

[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' mysql-bin.000002  | mysql -u root -p

3.恢复指定时间范围内的数据

[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p