数据库的备份与恢复

一、数据备份：

1.重要性：

（1）备份的主要目的是灾难恢复
（2）在生产环境中，数据的安全性至关更要
（3）任何敬据的丢失都可能产生严重的后果
（4）造成教据丢失的原因：程序错误，人为操作错误，运算错误
磁盘故障，灾难(如火灾、地震)和盗窃。

2.数据库备份类型：

数据库备份可以分为物理备份和逻相备份。

（1）物理备份是对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。物理备份又可以成为冷备份 (脱机备份) 、热备份 (连接备份) 和温备份。
① 冷备份(脱机备份)：是在关闭数据库的时候进行的 (tar)

② 热备份(联机备份): 数据库处于运行状态，依赖于数据库的日志文件 (mysgl hotcopy mysqlbackup)

③ 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。

（2）逻辑备份是对数据库逻辑组件的备份.表示为逻辑数据库结构这种类型的备份适用于可以编辑数据值或表结构。从数据库的备份策珞角度来看，备份又可分为完全备份、差异备份和增量备份

① 完全备份每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长，每次都进行完全备份，会导致备份文件占用空间巨大，并且有大量的重复数据，恢复时，直接使用完全备份的文件即可。
② 差异备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份。每次差异备份，都会备份上一次完全备份之后的数据，可能会出现本复数据。恢复时，先恢复完全备份的数据，再恢复差异备份的教据。

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dIzFCScP-1689639313475)(C:\Users\yuyong\AppData\Roaming\Typora\typora-user-images\image-20230717094042973.png)]

3.常见的备份方法：

（1）物理冷备：

① 物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。

② 物理冷备份一般用于非核心业务，这类业务一般都允许中断。

③ 物理冷备份的特点就是速度快，恢复时也是最为简单的。

④ 通常通过直接打包数据库文件夹(/usr/local/mysql/data)来实现备份。

（2）专用备份工具mydump或者mysqlhotcopy：

① mysqldump程序和mysqlhotcopy都可以做备份。

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

③ mysqlhotcopy仅适用于某些存储引擎（MyISAM和ARCHIVE）。
（3）启用二进制日志进行增量备份：

① 支持增量备份，进行增量备份时必须启用二进制日志。

② 二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。

③ 如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改) ，需要刷新二进制日志。

（4）通过第三方工具备份：第三方工具Percona xtraBackup是一个免费的MysQL热备份软件，支持在线热备份Innodb和xtraDB，也可以支持MySQL表备份，不过MyISAM表的备份要在表锁的情况下进行。

4.MySQL完全备份：

（1）是对整个数据库、数据库结构和文件结构的备份

（2）保存的是备份完成时刻的数据库

（3）是差异备份与增量备份的基础

（4）优点：备份与恢复操作简单方便

（5）缺点：数据存在大量的重复、占用大量的备份空间、备份与恢复时间长

5.完全数据库分类：

（1）物理冷备份与恢复：

① 关闭MySQL数据库（如果在进行冷备份的时候，忘记关数据库会出现什么情况？？ 会导致在那个时间点去备份的时候，会遗漏一部分数据没有完全备份所有数据。）

② 使用tar命令直接打包数据库文件夹（其实也可以不打包，打包的目的就是为了加快速度，并且减少磁盘存储空间！）

③ 直接替换现有MySQL目录即可
（2）mysqldump备份与恢复：

① MySQL自带的备份工具，可方便实现对MySQL的备份

② 可以将指定的库、表导出为SQL 脚本

③ 使用命令mysq|导入备份的数据

二、实战案例：

• 环境准备：

create database exo;
use exo;
create table if not exists info1 (id int(4) not null auto_increment,name varchar(10) not null,age char(10) not null,hobby varchar(50),primary key (id));
insert into info1 values(1,'chanyeol',24,'running');
insert into info1 values(2,'baekhyun',25,'singing');

1.MySQL完全备份与恢复：

systemctl stop mysqld
yum -y install xz
#压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
systemctl start mysqld
tar Jxvf /opt/mysql_all_2023-07-17.tar.xz -C /usr/local/mysql/data
cd /usr/local/mysql/data
cp /usr/local/mysql/data/* /opt

2.mysqldump 备份与恢复（温备份）:

create table info2 (id int,name char(10),age int,sex char(4));
insert into info2 values(1,'leo',18,'boy');
insert into info2 values(2,'riky',16,'girl');
quit
(1)完全备份一个或多个完整的库 (包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql    #导出的就是数据库脚本文件
mysqldump -u root -pabc123 --databases exo > /opt/exo.sql       #备份一个exo库
mysqldump -u root -pabc123 --databases mysql exo > /opt/mysql-exo.sql    #备份mysql与 exo两个库 
(2)、完全备份 MySQL 服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
例:
mysqldump -u root -pabc123 --all-databases > /opt/all.sql

(3)、完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
例:
mysqldump -u root -pabc123 -d exo info1 info2 > /opt/exo_info1.sql
#使用“-d”选项，说明只保存数据库的表结构 
#不使用“-d"选项，说明表数据也进行备份
#做为一个表结构模板

(4)查看备份文件
grep -v "^--" /opt/exo_info1.sql | grep -v "^/" | grep -v "^$"

3.Mysql 完全恢复：

（1）恢复数据库：

mysql -uroot -pabc123 -e 'drop database exo;' #删除数据库
mysql -uroot -pabc123 -e 'show databases;'  #展示

mysql -uroot -pabc123 < /opt/exo.sql  #从备份文件中恢复
mysql -uroot -pabc123 -e 'show databases;'

（2）恢复数据表：

mysql -uroot -pabc123 -e 'drop table exo.info1;'  #删除表info1
mysql -uroot -pabc123 -e 'show tables from exo;'  #查看为空
mysql -uroot -pabc123 exo < /opt/exo_info1.sql    #恢复表
mysql -uroot -pabc123 -e 'show tables from exo;'  #已恢复

4.MySQL增量备份与恢复：

（1）MySQL数据库增量恢复

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

② 基于位置恢复：数据库在某一时间点可能既有错误的操作也有正确的操作，可以基于精准的位置跳过错误的操作，发生错误节点之前的一个节点，上一次正确操作的位置点停止

③ 基于时间点恢复：跳过某个发生错误的时间点实现数据恢复，在错误时间点停止，在下一个正确时间点开始

（2）MySQL增量备份：

① 开启二进制日志：

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED      #可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）
server-id = 1              #可加可不加该命令

#二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

① STATEMENT（基于SQL语句）:
每一条涉及到被修改的sql 都会记录在binlog中
缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题

总结：增删改查通过sql语句来实现记录，如果用高并发可能会出错，可能时间差异或者延迟，可能不是我们想想的恢复可能你先删除或者在修改，可能会倒过来。准确率底

② ROW(基于行)
只记录变动的记录，不记录sql的上下文环境
缺点:如果遇到update…set…where true 那么binlog的数据量会越来越大

总结：update、delete以多行数据起作用，来用行记录下来，
只记录变动的记录，不记录sql的上下文环境，
比如sql语句记录一行，但是ROW就可能记录10行，但是准确性高，高并发的时候由于操作量，性能变低 比较大所以记录都记下来，

③ MIXED 推荐使用
一般的语句使用statement，函数使用ROW方式存储。

systemctl restart mysqld.service
ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r-----. 1 mysql mysql 154 7月  17 18:09 /usr/local/mysql/data/mysql-bin.000001 #第一次增量备份
-rw-r-----. 1 mysql mysql  19 7月  17 18:09 /usr/local/mysql/data/mysql-bin.index
mysql -uroot -pabc123  #登陆数据库
use exo; #选择数据库
insert into info1 values(3,'billkin',23,'music'); #在表中插入新数据
insert into info1 values(4,'pp',22,'act');
systemctl restart mysqld.service  #重启服务
ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r-----. 1 mysql mysql 1348 7月  17 18:17 /usr/local/mysql/data/mysql-bin.000001
-rw-r-----. 1 mysql mysql  154 7月  17 18:17 /usr/local/mysql/data/mysql-bin.000002  #第二次增量备份
-rw-r-----. 1 mysql mysql   38 7月  17 18:17 /usr/local/mysql/data/mysql-bin.index
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults  /opt/mysql-bin.000002  #查看二进制日志文件的内容
#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）

（3）MySQL增量备份恢复：

① 一般恢复：

• 模拟丢失更改的数据的恢复步骤：

mysql -uroot -pabc123  #登陆数据库
use exo； #选择数据库
select * from info1;
delete from info1 where id=4;  #删除第四行数据
select * from info1;

② 模拟丢失所有数据的恢复步骤

mysql -uroot -pabc123 -e 'drop database exo;'  #模拟删除数据库
mysql -uroot -pabc123 -e 'show databases;' 
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@wang3 opt]# mysql -uroot -pabc123 < /opt/exo.sql #恢复数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wang3 opt]# mysql -uroot -pabc123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| exo                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

（4） 断点恢复：

 mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002

查看：

/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
at 4
#230718 15:08:57 server id 1 end_log_pos 154 CRC32 0xb865453f Previous-GTIDs

[empty]

230718 15:08:57 server id 1 end_log_pos 123 CRC32 0xfe732cd5 Start: binlog v 4, server v 5.7.20-log created 230718 15:08:57 at startup
ROLLBACK/!/;
at 123

#230718 15:08:57 server id 1 end_log_pos 154 CRC32 0xb865453f Previous-GTIDs

[empty]

at 154

#230718 15:13:18 server id 1 end_log_pos 219 CRC32 0xd6c3096e Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET
@@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 219

230718 15:13:18 server id 1 end_log_pos 296 CRC32 0xdfe4d98b Query thread_id=3 exec_time=0 error_code=0 SET
TIMESTAMP=1689664398/!/; SET @@session.pseudo_thread_id=3/!/; SET
@@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/!/; SET
@@session.sql_mode=1437073414/!/; SET
@@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/!/; /!\C utf8 //!/; SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/!/;
SET @@session.lc_time_names=0/!/; SET
@@session.collation_database=DEFAULT/!/; BEGIN /!/;

at 296

#230718 15:13:18 server id 1 end_log_pos 411 CRC32 0x2ffc7c4f Query thread_id=3 exec_time=0 error_code=0 use exo/!/; SET
TIMESTAMP=1689664398/!/; insert into info1 values(5,‘riky’,21,‘cat’)
/!/;

at 411

#230718 15:13:18 server id 1 end_log_pos 442 CRC32 0x9fe8a253 Xid = 10 COMMIT/!/;

at 442

#230718 15:14:02 server id 1 end_log_pos 465 CRC32 0xe2c031d0 Stop SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!*/;
DELIMITER ;

End of log file

/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/; /!50530 SET
@@SESSION.PSEUDO_SLAVE_MODE=0/;

① 基于位置恢复：

mysql -uroot -pabc123 -e "select * from exo.info1;"
mysql -uroot -pabc123 -e "truncate table exo.info1;"
mysql -uroot -pabc123 -e "select * from exo.info1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqlbinlog --no-defaults --stop-position='442' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -pabc123
mysql -uroot -pabc123 -e "select * from exo.info1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----+-------+
| id | name | age | hobby |
+----+------+-----+-------+
|  5 | riky | 21  | cat   |
+----+------+-----+-------+

② 基于时间点恢复：

mysql -uroot -pabc123
use exo;
insert into info1 values(7,'chen',21,'smile'); #在表中插入新数据
mysqladmin -u root -p flush-logs  #刷新生成新的日志
Enter password: 
mysql -uroot -pabc123 -e "truncate table exo.info1;"  #删除表内数据
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000005 #查看恢复日志

/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/; /!50003 SET
@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/; DELIMITER
/!/;

at 4

#230718 15:50:16 server id 1 end_log_pos 123 CRC32 0xb789c89d Start: binlog v 4, server v 5.7.20-log created 230718 15:50:16

at 123

#230718 15:50:16 server id 1 end_log_pos 154 CRC32 0x21e88f0d Previous-GTIDs

[empty]

at 154

230718 15:51:30 server id 1 end_log_pos 219 CRC32 0x1a64a65b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET
@@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 219

#230718 15:51:30 server id 1 end_log_pos 296 CRC32 0x3abef087 Query thread_id=20 exec_time=0 error_code=0 SET
TIMESTAMP=1689666690/!/; SET @@session.pseudo_thread_id=20/!/; SET
@@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/!/; SET
@@session.sql_mode=1437073414/!/; SET
@@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/!/; /!\C utf8 //!/; SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/!/;
SET @@session.lc_time_names=0/!/; SET
@@session.collation_database=DEFAULT/!/; BEGIN /!/;

at 296

#230718 15:51:30 server id 1 end_log_pos 413 CRC32 0xa0926370 Query thread_id=20 exec_time=0 error_code=0 use exo/!/; SET
TIMESTAMP=1689666690/!/; insert into info1
values(7,‘chen’,21,‘smile’) /!/;

at 413

#230718 15:51:30 server id 1 end_log_pos 444 CRC32 0xe5ad4d2c Xid = 183 COMMIT/!/;

at 444

#230718 15:55:24 server id 1 end_log_pos 509 CRC32 0xd87366eb Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no SET
@@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 509

#230718 15:55:24 server id 1 end_log_pos 602 CRC32 0xb3421a49 Query thread_id=22 exec_time=0 error_code=0 SET
TIMESTAMP=1689666924/!/; truncate table exo.info1 /!/;

at 602

#230718 15:51:30 server id 1 end_log_pos 667 CRC32 0xafa0e99b Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no SET
@@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 667

#230718 15:51:30 server id 1 end_log_pos 744 CRC32 0x79ded635 Query thread_id=20 exec_time=463 error_code=0 SET
TIMESTAMP=1689666690/!/; BEGIN /!/;

at 744

#230718 15:51:30 server id 1 end_log_pos 861 CRC32 0xc52c4968 Query thread_id=20 exec_time=463 error_code=0 use exo/!/; SET
TIMESTAMP=1689666690/!/; insert into info1
values(7,‘chen’,21,‘smile’) /!/;

at 861

#230718 15:51:30 server id 1 end_log_pos 892 CRC32 0x503814cf Xid = 229 COMMIT/!/;

at 892

#230718 15:55:24 server id 1 end_log_pos 957 CRC32 0xe9fd27d1 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no SET
@@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 957

#230718 15:55:24 server id 1 end_log_pos 1053 CRC32 0xcf47e101 Query thread_id=20 exec_time=229 error_code=0 SET
TIMESTAMP=1689666924/!/; truncate table exo.info1 /!/;

at 1053

#230718 16:00:06 server id 1 end_log_pos 1118 CRC32 0x446abf2f Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=no SET
@@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 1118

#230718 16:00:06 server id 1 end_log_pos 1195 CRC32 0x896bfbf8 Query thread_id=12 exec_time=0 error_code=0 SET
TIMESTAMP=1689667206/!/; BEGIN /!/;

at 1195

#230718 16:00:06 server id 1 end_log_pos 1312 CRC32 0x328ded9b Query thread_id=12 exec_time=0 error_code=0 SET
TIMESTAMP=1689667206/!/; insert into info1
values(7,‘chen’,21,‘smile’) /!/;

at 1312

#230718 16:00:06 server id 1 end_log_pos 1343 CRC32 0x091f08a7 Xid = 244 COMMIT/!/;

at 1343

#230718 16:00:38 server id 1 end_log_pos 1408 CRC32 0xd7847f61 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no SET
@@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 1408

#230718 16:00:38 server id 1 end_log_pos 1485 CRC32 0x91ecda20 Query thread_id=12 exec_time=0 error_code=0 SET
TIMESTAMP=1689667238/!/; BEGIN /!/;

at 1485

#230718 16:00:38 server id 1 end_log_pos 1606 CRC32 0x9324d1be Query thread_id=12 exec_time=0 error_code=0 SET
TIMESTAMP=1689667238/!/; insert into info1
values(8,‘xiumin’,27,‘dancing’) /!/;

at 1606

#230718 16:00:38 server id 1 end_log_pos 1637 CRC32 0xbf2524b8 Xid = 246 COMMIT/!/;

at 1637

#230718 16:04:50 server id 1 end_log_pos 1684 CRC32 0x5b54e70e Rotate to mysql-bin.000006 pos: 4 SET @@SESSION.GTID_NEXT=
‘AUTOMATIC’ /* added by mysqlbinlog / /!*/; DELIMITER ;

End of log file

/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/; /!50530 SET
@@SESSION.PSEUDO_SLAVE_MODE=0/;

mysqlbinlog --no-defaults --stop-datetime='2023-07-18 16:00:38' /usr/local/mysql/data/mysql-bin.000005 | mysql -uroot -pabc123   #以时间节点恢复
mysql -uroot -pabc123 -e "select * from exo.info1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----+-------+
| id | name | age | hobby |
+----+------+-----+-------+
|  7 | chen | 21  | smile |
+----+------+-----+-------+

三、总结：

1.MySQL 备份、日志、恢复是 MySQL 数据库管理的重要内容之一，主要用于保障数据的可靠性和完整性，并为业务应用提供恢复和故障转移的保障。

2.MySQL 备份通常包括物理备份和逻辑备份两种方式。物理备份是将 MySQL 中的数据源文件直接拷贝至备份目录中，常用工具有 mysqldump、mysqlhotcopy 等。逻辑备份是根据 SQL 语句进行备份，可以使用 mysqldump、SELECT INTO OUTFILE 等命令实现。MySQL 备份需要考虑备份策略、备份周期、备份恢复等方面的问题，同时结合数据量、数据敏感度、可用存储设备等综合问题做出合适的决策。

3.MySQL 日志按照作用可分为错误日志、查询日志、二进制日志、慢查询日志等。错误日志用于记录 MySQL 异常错误信息，查询日志用于记录 MySQL 的每个查询及其执行情况，二进制日志用于记录数据库的更新操作或数据变更，慢查询日志则用于记录执行时间超过指定时间阈值的 SQL 语句。适当设置和使用 MySQL 日志可以帮助管理员实时监控 MySQL 运行状态，准确找出问题并进行及时处理。

4.MySQL 恢复可按照备份类型和场景进行恢复。在备份类型方面，既可以使用物理备份还原数据库文件，也可以使用逻辑备份恢复 SQL 命令或语句等。在场景方面，MySQL 恢复可分为完整恢复和部分恢复，根据具体的故障和应用情况选择不同的恢复方案。

5.综上，MySQL 备份、日志和恢复是 MySQL 数据库管理的重要组成部分，需要综合考虑备份、日志、恢复策略和工具以及运行环境、应用场景等多方面因素，达到保障数据库稳健运行和及时恢复的目的。