MySQL的日志管理与备份、恢复

目录

一、MySQL日志管理

Ⅰ、开启日志

Ⅱ、日志查看

二、MySQL的备份

 Ⅰ、备份的重要性

Ⅱ、备份类型

（1）物理层面的备份

（2）逻辑层面的备份

Ⅲ、备份测试

（1）物理冷备份

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

（3）定时备份

三、MySQL增量备份与恢复

Ⅰ、二进制日志进行增量备份

（1）恢复的方式

（2）二进制日志(binlog)的3种不同的记录格式

（3）查看二进制日志内容

(4)二进制日志中需要注意的重点部分

Ⅱ、进行增量备份

（1）使用mysqldump（完全备份+增量备份）

（2）增量恢复

（3）断点恢复d  

（4）时间点恢复

---

一、MySQL日志管理

Ⅰ、开启日志

通过修改配置文件来对日志文件管理是否开启

vim /etc/my.conf
 
[mysqld]
#省略部分内容
 
#错误日志
log-error=/usr/local/mysql/data/mysql_error.log	 
 
#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
 
#二进制日志
log-bin=mysql-bin	
 
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

重启mysql服务后，进入数据库查询日志是否开启

MySQL [(none)]>show variables like 'general%';       #查看通用查询日志是否开启
MySQL [(none)]>show variables like 'log_bin%';       #查看二进制日志是否开启
MySQL [(none)]>show variables like '%slow%';         #查看慢查询日功能是否开启
MySQL [(none)]>show variables like 'long_query_time';#查看慢查询时间设置
MySQL [(none)]>set global slow_query_log=ON;         #在数据库中设置开启慢查询的方法

查询结果

#查看通用查询日志是否开启
MySQL [(none)]> show variables like 'general%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | ON                            |
| general_log_file | /data/mysql/mysql_general.log |
+------------------+-------------------------------+
2 rows in set (0.01 sec)
 
#查看二进制日志是否开启
MySQL [(none)]> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /data/mysql/mysql-bin       |
| log_bin_index                   | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)
 
#查看慢查询日功能是否开启
MySQL [(none)]> show variables like '%slow%';
+---------------------------+----------------------------+
| Variable_name             | Value                      |
+---------------------------+----------------------------+
| log_slow_admin_statements | OFF                        |
| log_slow_slave_statements | OFF                        |
| slow_launch_time          | 2                          |
| slow_query_log            | ON                         |
| slow_query_log_file       | /data/mysql/mysql-slow.log |
+---------------------------+----------------------------+
5 rows in set (0.00 sec)
 
#查看慢查询时间设置
MySQL [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

Ⅱ、日志查看

开启日志后，日志就会生成在配置文件中设置的所在位置，我这里将所有的日志文件放在了/data/mysql/下

 查看general log

[root@test111 mysql]# cat mysql_general.log
/usr/local/mysql/bin/mysqld, Version: 5.7.38-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2022-09-17T03:12:07.032682Z         2 Connect   root@localhost on  using Socket
2022-09-17T03:12:07.032930Z         2 Query     select @@version_comment limit 1
2022-09-17T03:12:35.653154Z         2 Query     show variables like 'general%'
2022-09-17T03:12:52.686342Z         2 Query     show variables like 'log_bin%'
2022-09-17T03:13:06.838356Z         2 Query     show variables like '%slow%'
2022-09-17T03:13:22.371256Z         2 Query     show variables like 'long_query_time'
2022-09-17T03:13:45.796457Z         2 Query     set global slow_query_log=on
2022-09-17T03:13:48.240343Z         2 Query     show variables like '%slow%'
2022-09-17T03:18:01.875989Z         2 Quit

查看二进制日志使用mysqlbinlog。

mysql-bin.index：二进制日志文件的索引

索引文件：记录更新语句
索引文件刷新方式：
1、重启mysql的时候会更新索引文件，用于记录新的更新语句
2、刷新二进制日志

[root@test111 mysql]# mysqlbinlog mysql-bin.000020
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220913  8:13:35 server id 1  end_log_pos 123 CRC32 0x95dd91b2  Start: binlog v 4, server v 5.7.38-log created 220913  8:13:35 at startup
ROLLBACK/*!*/;
BINLOG '
L8sfYw8BAAAAdwAAAHsAAAAAAAQANS43LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAvyx9jEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AbKR3ZU=
'/*!*/;
# at 123
#220913  8:13:35 server id 1  end_log_pos 154 CRC32 0xd7fab7e2  Previous-GTIDs
# [empty]
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的备份

 Ⅰ、备份的重要性

在企业中，数据的价值至关重要，数据保障了企业业务的正常运行。因此，数据的安全性及数据的可靠性是运维的重中之重，任何数据的吊事都可能对企业产生严重的后果。

通常情况下，造成数据丢失的原因有一下几种：

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘故障
• 灾难（火灾、地震、盗窃等）

Ⅱ、备份类型

（1）物理层面的备份

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

冷备份 (脱机备份) ：是在关闭数据库的时候进行的（tar）
热备份 (联机备份) ：数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）
温备份 ：数据库锁定表格(不可写入但可读)的状态下进行备份操作（mysqldump）

（2）逻辑层面的备份

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

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

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

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

Ⅲ、备份测试

准备一个数据库

ceate database test;
create table if not exists class (
id int(4) not null auto_increment,
name varchar(10) not null,
age char(10) not null,
address varchar(50),
primary key (id));
insert into class values(1,'user1',19,'road1');
insert into class values(2,'user2',22,'road2');
MySQL [test]> select * from class;
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  1 | user1 | 19  | road1   |
|  2 | user2 | 22  | road2   |
+----+-------+-----+---------+
2 rows in set (0.00 sec)

（1）物理冷备份

systemctl stop mysqld
yum -y install xz
 
#压缩备份
cd /data/mysql/
tar jcvf mysql_all_$(date +%F).tar.xz /data/mysql/
systemctl start mysqld
 
#模拟故障，删除数据库
drop database test;
 
#解压恢复
tar jxf mysql-all-2022-06-22.tar.xz -C /data/mysql/
cd /data/mysql/

#备份完成
[root@test111 mysql]# ls
auto.cnf         ib_buffer_pool  mysql                        mysql-bin.000020  mysql-bin.000025  mysql-bin.000030   mysql-slow.log      server-key.pem
ca-key.pem       ibdata1         mysql_all_2022-09-17.tar.xz  mysql-bin.000021  mysql-bin.000026  mysql-bin.000031   performance_schema  sys
ca.pem           ib_logfile0     mysql-bin.000017             mysql-bin.000022  mysql-bin.000027  mysql-bin.index    private_key.pem     test
client-cert.pem  ib_logfile1     mysql-bin.000018             mysql-bin.000023  mysql-bin.000028  mysql-error.log    public_key.pem
client-key.pem   ib_logfile2     mysql-bin.000019             mysql-bin.000024  mysql-bin.000029  mysql_general.log  server-cert.pem
 
#恢复数据到/opt/
[root@test111 mysql]# tar jxf mysql_all_2022-09-17.tar.xz -C /opt/
[root@test111 mysql]# cd /opt
[root@test111 opt]# ls
bak_sql  data  rh  tar
[root@test111 opt]# cd data/
[root@test111 data]# ls
mysql
[root@test111 data]# cd mysql/
[root@test111 mysql]# ls
auto.cnf         ib_buffer_pool  mysql                        mysql-bin.000020  mysql-bin.000025  mysql-bin.000030   mysql-slow.log      server-key.pem
ca-key.pem       ibdata1         mysql_all_2022-09-17.tar.xz  mysql-bin.000021  mysql-bin.000026  mysql-bin.000031   performance_schema  sys
ca.pem           ib_logfile0     mysql-bin.000017             mysql-bin.000022  mysql-bin.000027  mysql-bin.index    private_key.pem     test
client-cert.pem  ib_logfile1     mysql-bin.000018             mysql-bin.000023  mysql-bin.000028  mysql-error.log    public_key.pem
client-key.pem   ib_logfile2     mysql-bin.000019             mysql-bin.000024  mysql-bin.000029  mysql_general.log  server-cert.pem

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

使用工具mysqldump

#完全备份一个/多个数据库
mysqldump -u root -p'密码' --databases 库名1 库名2 ...>/备份路径/备份文件名.sql
 
#完全备份 MySQL 服务器中所有的库
mysqldump -u root -p'密码' --all-databases >/备份路径/备份文件名.sql
 
#完全备份指定库中的部分表（-d表示只备份表结构）
mysqldump -u root -p'密码' -d 库名 表名1 表名2 ...>/备份路径/备份文件名.sql
 
#查看备份文件
grep -v "^--" /备份路径/备份文件名.sql | grep -v "^/" | grep -v "^$"

备份数据库test、查看备份文件

mysqldump -uroot -p'root' --databases test>/opt/bak_sql/test.sql
grep -v "^--" /opt/bak_sql/test.sql | grep -v "^/" | grep -v "^%"
 
 
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
 
USE `test`;
 
 
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` char(10) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
 
 
LOCK TABLES `class` WRITE;
INSERT INTO `class` VALUES (1,'user1','19','road1'),(2,'user2','22','road2');
UNLOCK TABLES;
 
 

模拟数据库数据丢失，进行数据的恢复

mysql> drop database test；
mysql> show database；
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
方式1：登录mysql执行
mysql> source  /opt/bak_sql/test.sql
 
方式2：不登录mysql执行
mysql -uroot -p'root' -e 'source /opt/bak_sql/test.sql;'
或者
mysql -uroot -p'root' < /opt/bak_sql/test.sql
 
登录mysql查看是否恢复，查看test库中的class表
MySQL [test]> select * from class;
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  1 | user1 | 19  | road1   |
|  2 | user2 | 22  | road2   |
+----+-------+-----+---------+
2 rows in set (0.00 sec)
数据恢复成功！！！

（3）定时备份

在生产环境中，可以使用定期任务来周期性的备份数据来达到数据库容灾能力

#每周日凌晨1点执行对某库的某表的备份操作保存到
0 1 * * 7 /usr/local/mysql/bin/mysqldump -uroot -p'password' 
库名 表名 > /opt/bak_sql/库名_表名_$(date +%Y%m%d).sql;
 
#生成新的日志
/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

三、MySQL增量备份与恢复

Ⅰ、二进制日志进行增量备份

（1）恢复的方式

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

基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

发生错误节点之前的一个节点，上一次正确操作的位置点停止

基于时间点恢复
跳过某个发生错误的时间点实现数据恢复

在错误时间点停止，在下一个正确时间点开始

（2）二进制日志(binlog)的3种不同的记录格式

STATEMENT（基于SQL语句）
每一条涉及到被修改的sql 都会记录在binlog中

缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题

ROW(基于行)
只记录变动的记录，不记录sql的上下文环境

缺点:如果遇到update…set…where true 那么binlog的数据量会越来越大

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

（3）查看二进制日志内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v 
/opt/bak_sql/mysql-bin.000020
#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）

将解码后的文件导出为txt格式

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/bak_sql/mysql-bin.000020>
/opt/sql_txt/mysql-bin.000020
 
[root@test111 /]# cd /opt && ls 
bak_sql  mysql-bin.000020  rh  tar

(4)二进制日志中需要注意的重点部分

1、at ：开始的位置点 
2、end_log_pos：结束的位置 position （位置点）
3、时间戳： 210712 11:50:30 
4、SQL语句

Ⅱ、进行增量备份

（1）使用mysqldump（完全备份+增量备份）

之前完全备份了一次test库，我们在class表中做一些修改，例如插入新数据，以模拟数据的增加或变更，然后进行二进制日志文件的刷新

mysqladmin -uroot -p'root' flush-logs

修改的操作会保存在刷新后出现的二进制日志中

例

先删掉所有的二进制文件（为方便实验，非必要）

#test库class表
MySQL [test]> select * from class;
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  1 | user1 | 19  | road1   |
|  2 | user2 | 22  | road2   |
+----+-------+-----+---------+
2 rows in set (0.00 sec)

进行插入数据操作并刷新日志文件

MySQL [test]> insert into class values(3,'user3',28,'road3')，(4,'user4',44,''road4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
MySQL [test]> select * from class;
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  1 | user1 | 19  | road1   |
|  2 | user2 | 22  | road2   |
|  3 | user3 | 23  | road3   |
|  4 | user4 | 44  | road4   |
+----+-------+-----+---------+
4 rows in set (0.00 sec)
 
 
#退出mysql
mysqladmin -uroot -p'root' flush-logs

 查看日志文件

ls /data/mysql/

此时我们上面的数据修改操作就保存在了mysql-bin.000001中 

（2）增量恢复

模拟数据丢失，删除test库（已备份）

mysql -uroot -p'root' -e'drop database test;'
mysql: [Warning] Using a password on the command line interface can be insecure.
 
mysql -uroot -p'root' -e'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

先进行完全备份的恢复，再进行增量备份的恢复,可以发现，虽然test库和库里的class表恢复成功了，但增量备份前所作的数据修改“user3和user4”的记录没有恢复

mysql -uroot -p'root' 
 
mysql -uroot -p'root' -e'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
 
mysql -uroot -p'root' -e'select * from test.class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  1 | user1 | 19  | road1   |
|  2 | user2 | 22  | road2   |
+----+-------+-----+---------+

这时需要基于二进制日志文件mysql-bin.000001来恢复增量

mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 | mysql -uroot -proot
 
 
mysql -uroot -proot -e'select * from test.class;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  1 | user1 | 19  | road1   |
|  2 | user2 | 22  | road2   |
|  3 | user3 | 23  | road3   |
|  4 | user4 | 44  | road4   |
+----+-------+-----+---------+

（3）断点恢复d  

SQL语句

#恢复一个位置点之前的数据
mysqlbinlog --no-defaults --stop-position='位置点' 
/opt/mysql-bin.000002 | mysql -uroot -p'密码'
 
#恢复一个位置点之后的数据
mysqlbinlog --no-defaults --start-position='位置点' 
/opt/mysql-bin.000002 | mysql -uroot -p'密码'
 
#恢复从一个位置点到另一位置点间的数据
mysqlbinlog --no-defaults --start-position='位置点'--stop-position='位置点' 
/opt/mysql-bin.000002 | mysql -uroot -p'密码'

（4）时间点恢复

SQL语句

#恢复一定时间前的数据
mysqlbinlog --no-defaults --stop-datetime='2022-9-17 16:41:24' 
/opt/bak_sql/mysql-bin.000002 | mysql -uroot -p'密码'
 
#恢复一定时间之后的数据
mysqlbinlog --no-defaults --start-datetime='2022-9-17 16:41:24' 
/opt/bak_sql/mysql-bin.000002 | mysql -uroot -p'密码'
 
#恢复一定时间段内的数据
mysqlbinlog --no-defaults --start-datetime='2022-9-17 16:41:24' 
--stop-datetime='2022-9-17 16:50:22' /opt/bak_sql/mysql-bin.000002 | 
mysql -uroot -p'密码'