【MySQL】4.MySQL日志管理与数据库的备份和恢复

备份的目的只要是为了灾难恢复，备份还可以测试应用，回滚数据，修改和查询历史数据，审计等

日志在备份、恢复中起着重要作用

一、数据库备份的重要性

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

造成数据丢失的原因有：
程序错误
人为操作错误
运算错误
磁盘错误
灾难（如火灾、地震）和盗窃

容灾

二、数据库备份的分类

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

1.物理备份：

对数据库操作系统的物理文件（如：数据文件、日志文件等）进行备份；适用于在出现问题的时候需要快速恢复的大型重要数据库

物理备份又分为：

冷备份（脱机备份）：需要关闭数据库才能进行tar打包备份

热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）

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

2.逻辑备份：

逻辑备份是对数据库逻辑组件的备份，表示为逻辑数据库结构；这种类型的备份适用于可以编辑数据值或表结构

从数据库的备份策略来看，逻辑备份又分为完全备份、差异备份和增量备份

①完全备份

每次都对整个数据库进行完整的备份（包括数据库结构和文件结构）；
缺点：有大量重复数据，占用磁盘空间最大；
优点：结构完整，恢复时速度快，直接把文件导入即可；

②差异备份

备份自从上次完全备份之后修改过的文件；后续的差异备份都会备份前面差异备份的内容，导致出现重复的数据，占用额外的磁盘空间；恢复时会先恢复完全备份，再导入最后一次差异备份的数据即可；

③增量备份

只有在上次完全备份或者增量备份后 被修改的数据(或文件)才会被备份；不会出现重复数据(导致额外占用磁盘空间)；恢复时要先恢复完全备份，再按次序恢复增量备份

备份方式的比较

备份方式          完全备份            差异备份      增量备份
完全备份时的状态  表1、表2             表1、表2      表1、表2
第1次添加内容     创建表3              创建表3       创建表3
备份内容          表1、表2、表3        表3           表3
第2次添加内容     创建表4              创建表4       创建表4
备份内容          表1、表2、表3、表4   表3、表4      表4

逻辑备份的策略

逻辑备份策略的选择（频率）

一周一次进行完全备份，全备的时间需要再不提供业务或访问量少的时间区间进行，例：PM 10:00 到AM 5:00（看你公司情况）
增量备份：3天/2天/1天 一次增量备份
差异备份：选择特定的场景进行备份（比较少用到）

通过配置NFS可以提供额外的空间给mysql服务器使用；NFS严格上说不算是共享存储，知识共享了文件；

三、常见的备份方法

1.物理冷备

备份时，数据库要处于关闭状态，直接打包数据库文件；备份速度快，恢复也简单

2.专用备份工具mysqldump和mysqlhotcopy

mysqldump常用的逻辑备份工具；mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

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

进行增量备份时，需要刷新二进制文件

4.第三方工具备份

Navicat、免费的MySQL热备份软件Percona XtraBackup

四、数据库完全备份分类

是对整个数据库、数据库结构和文件结构的备份；保存的是备份完成时刻的数据库；是差异备份和增量备份的基础
优点：备份和恢复操作简单方便
缺点：数据大量重复，占用额外的磁盘空间，备份与恢复时等待时间长

物理冷备与恢复

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

mysqldump备份与回复

MySQL自带的备份工具，可以方便实现对MySQL的备份
可以见指定的库、表导出为SQL脚本文件
恢复：使用命令导入备份的数据

实验

环境准备

建库、建表，插入数据；

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| test1          |
+----------------+
2 rows in set (0.00 sec)mysql> select * from test;
+------+----------+-------+--------------+
| id   | name     | score | address      |
+------+----------+-------+--------------+
| 0001 | zhangsan |   100 | 地址不详     |
| 0002 | lisi     |    85 | bijing       |
| 0003 | wangwu   |    91 | 地球村       |
| 0004 | laoliu   |    75 | nanjing      |
+------+----------+-------+--------------+
4 rows in set (0.00 sec)mysql> select * from test1;
+------+----------+-------+-------+
| id   | name     | score | phone |
+------+----------+-------+-------+
| 0001 | zhangsan | 82.50 |   111 |
| 0002 | lisi     | 85.00 |   222 |
| 0003 | wangwu   | 91.00 |   333 |
+------+----------+-------+-------+
3 rows in set (0.00 sec)mysql> 

mysql完全备份与恢复

InnoDB 存储引擎的数据库在磁盘上存储成三个文件:

db.opt(表属性文件)
表名.frm(表结构文件)
表名.ibd(表数据文件)

1.物理冷备与恢复

第一步关闭mysqld

tar zxvf 直接备份指定路径的文件，不包括路径tar jxvf
按路径备份，会将整个路劲备份下来，但不会备份路径中其他的数据

第一种 zxvf

冷备

#关闭mysqld
systemctl stop mysqld
#
[root@test2 mysql]# cd /usr/local/mysql/
[root@test2 mysql]# tar zcvf data.tar.gz data/ -C /opt/mysql.bak/

恢复

第二种 jxvf

关闭mysql，安装 xz 压缩

systemctl stop mysqld
yum -y install xz

压缩备份

tar jcvf /opt/mysql_data_$(date +%F).tar.xz /usr/local/mysql/data/#j    表示按路径备份(打包)数据

解压恢复

[root@test2 ~]# tar jxvf /opt/mysql_data_.tar.xz -C /usr/local/mysql/[root@test2 ~]# cd /usr/local/mysql/[root@test2 mysql]# mv usr/local/mysql/data /usr/local/mysql/

2.mysqldump备份与恢复

完全备份

建库、建表，插入数据

create database school;
use school;
create table class (id int(4) zerofill not null,name char(10),score decimal(5,2),address varchar(80));
insert into class values (1,'yiyi',78,'shanghai');
insert into class values (2,'lizi',92,'henan');
insert into class values (3,'monor',88,'nanjing');
select * from class;完成后，quit退出MySQL

完全备份一个或多个完整的库（包括其中所有的表）

备份多个表
mysqldump -u用户名 -p密码 --databases 库1 库2 > 备份路径/备份文件名.sql

例：

[root@test2 ~]# mysqldump -uroot -p123456 --databases school test > /opt/mysql.bak/school.test_$(date +%F).sql备份成功后可以通过 vim 来查看备份表的内容
[root@test2 ~]# vim /opt/mysql.bak/school.test_2024-03-25.sql

删除数据库

备份恢复

[root@test2 ~]# mysql -uroot -p123456 < /opt/mysql.bak/school.test_2024-03-25.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

查看数据库和表

使用 -e 可以在bash环境中使用sql语句；

ps：报错

[root@test2 data]# mysql -uroot -p123456 < /opt/mysql.bak/school.test_2024-03-25.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1030 (HY000) at line 66: Got error 168 from storage engine1.检查mysql用户是否有数据目录的读写权限
2.或导入的数据库引擎不匹配（把报错行下的引擎改为InnoDB或MyISAM试试）另一种情况
df -h看一下磁盘空间是否有余，
若是不足的话        show variables like '%log';    查看general_log状态
关闭日志记录模式    set global general_log = OFF
清理日志            echo "" > mysql.log
再开启日志          set global general_log = OFF

完全备份MySQL服务器中所有库

mysqldump -u用户名 -p密码 --all-databases > /备份路径/备份文件名.sql

例

mysqldump -uroot -p123456 --all-databases > /opt/mysql.bak/all.$(date +%F).sql

完全备份指定库中的部份表

mysqldump -u用户名 -p密码 库名 表名1 表名2 > 备份路径/备份文件名.sql

例：

mysqldump -uroot -p123456 test test class > /opt/mysql.bak/table.test.class.$(date +%F).sql

保存数据库的表结构

#使用"-d"或"-t"选项，表示只保存数据库的表结构
#不使用"-d"或"-t"选项，说明表数据也要进行备份
#可以用来当作一个表结构模板

查看备份文件

grep -v "^--" 备份文件名| grep -v "^/" | grep -v "^$"#-v "^$"    表示非空行

例：

grep -v "^--" /opt/mysql.bak/school.test_2024-03-25.sql| grep -v "^/" | grep -v "^$"

mysql完全恢复（有问题，搞不懂，重做）

恢复数据库

1.使用mysqldump导出的文件，可导入的方法有：

source命令
mysql命令

2.使用source 恢复数据库的步骤

登录mysql数据库

执行source备份sql脚本的路径

3.source 恢复的示例

MySQL [(none)]> source /backup/all-data.sql

使用 source 命令恢复数据

1.模拟数据库出现问题

[root@server1 backup]# mysql -uroot -pabc123 登录数据库
mysql> show databases;  查看数据库信息
mysql> drop database school;  删除数据库school
mysql> show databases; 

2.应用实例

创建备份（对表进行备份）

备份表
mysqldump -uroot -p12356 school class > /opt/mysql.bak/class.sql删除表（模拟故障）
mysql -uroot -p123456恢复表
mysq 
mysql> select * from info;  查询所有字段
mysql> show tables; 查看表信息
或免交互l> source /opt/info.sql  
mysql -uroot -p123123 -e 'show tables from school;'

3.方式二：

#恢复class表
mysql -uroot -p123456 school < /opt/mysql.bak/school.class.sql
#查看class表
mysql -uroot -p123456 school -e 'show tables from school;'

PS:mysqldump 严格来说属于温备份，会需要对表进行写入的锁定
在全量备份与恢复实验中，假设现有school库，school库中有一个class表，需要注意的一点为：
① 当备份时加 --databases ,表示针对于school库
#备份命令
mysqldump -uroot -p123456 --databases school > /opt/mysql.bak/school.01.sql 备份库后
#恢复命令过程为：
mysql -uroot -p123456
drop database school;
exit
mysql -uroot -p12356 < /opt/mysql.bak/school.01.sql
② 当备份时不加 --databases，表示针对test库下的所有表
#备份命令
mysqldump -uroot -p123456 test > /opt/mysql.bak/test.02.sql
#恢复过程：
mysql -uroot -p123456
drop database test;
create database test;
exit
mysql -uroot -p123456 test < /opt/mysql.bak/test.02.sql 

#查看school.01.sql 和test.02.sql 
主要原因在于两种方式的备份（前者会从"create databases"开始,而后者则全是针对表格进行操作） 

4.自动化备份

0 1 * * 6 /usr/local/mysql/data/ mysqldump -uroot -p123456 库1 库2 > ./备份命$(date +%Y%m%d).sql ;/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

MySQL 增量备份与恢复

一、增备实验

1.开启二进制日志功能

二进制日志(binlog)有3种不同的记录格式:

STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

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

①statement（基于sql语句）：

每一条涉及到被修改的sql都会记录在binlog中；
缺点：日志量大；如：sleep()函数，主从复制等架构记录日志时会出现问题
总结：增删改查通过sql语句来实现记录，高并发时会出错(有时间差异和延迟)，记录的数据不一定会按顺序记录；准确率低

②row（基于行）

只记录变动的记录，不记录sql的上下文环境
缺点：如遇到update、set、wehere true那么binlog的数据量会越来越大
总结：update、delete等以多行数据起作用，被记录下来时；只会记录变动的记录，不记录sql的上下文环境；比如sql语句记录一行，但是row就很可能记录10行，准确性高；但高并发时由于操作量大，所有记录都被记录下来，性能会变低

③mixde 混合；推荐使用

一般的语句使用statement ，函数使用row方式存储

systemctl restart mysqld

2.查看二进制文件

cp /usr/local/mysql/data/mysql-bin.000002 /opt/

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）
PS: 可以将解码后的文件导出为txt格式，方便查阅
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002

二进制日志中需要关注的部分

开始的位置点    at
结束的位置点    end_log_pos
时间戳          210712 11:50:30
SQL语句

2.进行完全备份

增量备份是基于完全备份的，所以需要先完全备份一次数据库

mysqldump -uroot -p school class > /opt/school.class.$(date +%F).sql
mysqldump -uroot -p123456 school > /opt/school.all.$(date +%F).sql

3.可每天进行增量备份操作，生成新的二进制日志文件002

mysqladmin -uroot -p123456 flush-logs

4.插入新数据，模拟数据的增加或变更

ps：在第一次完全备份之后刷新二进制文件，在第二个二进制文件中记载着“增量备份的数据”

建库、建表、添加数据

5.再次生成新的二进制日志文件003

mysqladmin -uroot -p123456 flush-logs

#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后我们测试删除ky13库的操作会保存在mysql-bin.000003文件中 （以免当我们基于mysql-bin.000002日志进行恢复时，依然会删除库）

MySQL增量恢复

一、一般恢复

1、模拟丢失更改的数据库的就恢复步骤（直接使用恢复即可）