mysql:备份与恢复

为什么要备份

在生产环境中，由于人为操作失误造成某些数据被误操作、软件 BUG 造成部分数据或全部数据丢失、硬件故障造成数据库部分数据或全部数据丢失、安全漏洞被入侵数据恶意破坏，或是特殊应用场景下基于时间点的数据恢复、开发测试环境数据库搭建、相同数据库的新环境搭建、数据库或者数据迁移，需要为数据进行备份。

三种备份方式

在物理层面上，备份分为：
冷备份：关闭数据库时进行的
热备份：通过数据库的日志文件进行备份
温备份：备份时，数据库锁定表，可读不可写的状态下进行备份

在逻辑层面上分为：
完全备份：每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长。

增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以上次完整备份或上次增量备份的时间为时间点，仅备份者之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份。

日志

日志用于记录数据库的操作，也能够通过日志文件恢复数据库中的数据。

七种日志类型：
redo重做日志:达到事务一致性（每次重启会重做)
作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redolog执行重做，达到事务一致性。

undo回滚日志
作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交隔离级别就是通过mvcc+undo实现。

errorlog错误日志
作用：Mysql本身启动，停止，运行期间发生的错误信息。

slow query log 慢查询日志
作用:记录执行时间过长的sql，时间阈值（10s）可以配置，只记录执行成功。

bin log二进制日志
作用:用于主从复制，实现主从同步，数据恢复也需要靠该日志进行恢复。

relay log中继日志
作用:用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放。

general log普通日志
作用:记录数据库的操作明细，默认关闭，开启后会降低数据库性能。

备份与恢复实验

物理冷备份

#首先关闭数据库,这里不关闭，看一下效果
#将data目录下的文件进行备份
tar jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/ 

#删除data目录
rm -rf /usr/local/mysql/data

#删除data目录后查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> 

#将归档的文件解压到data目录
mkdir /usr/local/mysql/data
tar jxvf /opt/mysql_all_2021-07-14.tar.xz -C /usr/local/mysql/data/
mv /usr/local/mysql/data/usr/local/mysql/data/ * /usr/local/mysql/data

#可以发现数据库回来了
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| data               |
| mysql              |
| performance_schema |
| sys                |
| usr                |
+--------------------+
7 rows in set (0.00 sec)

使用mysqldump进行完全备份

#备份单个库
mysqldump -uroot -pabc123 --databases test > /opt/bak.sql
[root@client opt]# ls /opt
bak.sql      

#备份多个库
mysqldump -uroot -pabc123 --databases test test01 > /opt/bak01.sql
[root@client opt]# ls /opt
bak01.sql                mysql-2021-07-14.tar.xz    
bak.sql                  mysql-5.7.20               

#备份全部库
mysqldump -uroot -pabc123 --all-databases > /opt/bakall.sql
ls /opt
bak01.sql     bakall.sql       mysqlbak.sql    bak.sql    

#备份单个表
mysqldump -uroot -pabc123 test aaa > /opt/aaa.sql

#备份表结构
mysqldump -uroot -pabc123 -d test aaa > /opt/aaa-d.sql

基于二进制文件进行的增量备份与恢复

首先开启日志文件

#进入/etc/my.cnf文件，在[mysqld]下面添加

[mysqld]
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
log-bin=mysql-bin
binlog_format = MIXED
log-error=/usr/local/mysql/data/mysql_error.log
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

#保存并重启服务
systemctl restart mysqld
#每次重启服务或刷新日志文件都会生成新的二进制日志文件
[root@www data]# mysqladmin -u root -p flush-logs
Enter password: 
[root@www data]# ls
auto.cnf        ibtmp1            mysql_error.log
bbs             mysql             mysql_general.log
ib_buffer_pool  mysql-bin.000001  mysql_slow_query.log
ibdata1         mysql-bin.000002  names
ib_logfile0     mysql-bin.000003  performance_schema
ib_logfile1     mysql-bin.index   sys

可以在data目录下看见创建的日志文件

#基于日志恢复
#首先将二进制日志文件转码并输出到其他文件中
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001 > /opt/mysql-bin.000001

其中at表示事务的起始位置，end_log_pos表示结束
/*!*/上面的是执行的操作
at下面的是执行的时间

#数据恢复的命令
#从日志文件开头恢复到at812为止
mysqlbinlog --no-defaults --stop-position='812' /opt/mysql-bin.000001 |mysql -uroot -p123

#从at512开始恢复
mysqlbinlog --no-defaults --start-position='512' /opt/mysql-bin.000001 |mysql -uroot -p123

#从512恢复到812
mysqlbinlog --no-defaults --start-position='512' --stop-position='812' /opt/mysql-bin.000001 |mysql -uroot -p123

#对整个日志文件进行恢复
mysqlbinlog --no-defaults /opt/mysql-bin.000001 |mysql -uroot -p123