mysql日志与备份恢复

mysql 日志管理与备份恢复

目录

• 一 Mysql 日志管理
  • 1 ，修改配置文件开启mysql 日志
  • 2 在mysql 中查看与设置日志（重启mysqld服务失效）
    • 2.1 通用日志查看与设置
    • 2.2 慢日志查看与设置
    • 2.3 binlog日志查看是否开启与临时关闭bin-log写入
• 二 数据库备份的重要性与备份分类
  • 1 数据库备份的重要性
  • 2 数据库备份分类
    • 2.1 从物理和逻辑的角度分类
    • 2.2 从数据库的备份策略角度分类
  • 3 常见的备份方法
• 三： 数据库的完全备份
  • 1 物理冷备份与恢复
    • 1.1 物理冷备份
    • 1.2 删除数据库里的任意数据
    • 1.3 恢复数据
  • 2 mysqldump 备份与恢复
    • 2.1 mysqldump 备份
    • 2.2 mysql 完全恢复
• 四： mysql 增量备份
  • 1 增量备份的特点
  • 2 mysql 的增量被与二进制文件binlog
  • 3 mysql 增量备份
    • 3.1 开启二进制日志功能
    • 3.2 刷新binlog日志
    • 3.3 插入新数据，模拟数据的增加
    • 3.4 刷新binlog日志
  • 4 查看binlog日志内容
  • 5 增量恢复
    • 5.1 一般恢复
    • 5.2 基于位置恢复
    • 5.3 基于时间点恢复

一 Mysql 日志管理

1 ，修改配置文件开启mysql 日志

复制vim  /etc/my.cnf
....
#在配置文件的 mysqld设置中
[mysqld]
#配置错误日志，用来记录mysql启动，停止，或运行时发生的错误信息。默认已经开启
log-error=/usr/local/mysql/data/mysql_error.log

#配置通用查询日志，用来记录mysql的所有连接和语句。默认时关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

#配置二进制日志(binlog),用来记录所有更新了数据包或者已经潜在更新了数据的语句，记录了数据的更改，可以用于数据恢复，默认已经开启。
#也可以设置为log_bin=mysql-bin
log-bin=mysql-bin
#设置binlog日志的记录方式
binlog_format=mixed

#配置慢查询日志，用来记录所有执行时间超过log_query_time秒的语句，可以找到那些查询语句执行时间长，以便于优化。默认时关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5  #设置超过5秒执行的语句被记录，缺省时为10秒





#重启mysqld 服务
systemctl restart mysqld 

#查看mysql工作目录的data目录中生成的日志文件 
ls /usr/local/mysql/data

2 在mysql 中查看与设置日志（重启mysqld服务失效）

2.1 通用日志查看与设置

（1） 查看通用日志设置

复制#查看通用日志设置
show variables like 'general%';

（2） 配置通用日志设置

复制#开启通用日志
set global general_log=on;
#设置通用日志路径
set  global general_log_file='/usr/local/mysql/data/mysql_general.log';

2.2 慢日志查看与设置

（1） 查看你慢查询日志状态

复制#查看慢查询日志是否开启
show  variables like '%slow%';
#查看长查询时间设的值
 show  variables like 'long_query_time';
 show  global  variables like 'long_query_time';

(2 ) 开启慢查询日志

复制#开启慢查询日志 
set global slow_query_log=on;
#设置慢查询日志的路径
set global slow_query_log_file='/usr/local/mysql/data/mysql_slow_query.log';
#设置全局与当前的长查询时间
set global long_query_time=5;
set  long_query_time=5;

2.3 binlog日志查看是否开启与临时关闭bin-log写入

（1) 查看bin-log日志是否开启

复制 show variables like '%log_bin%';

(2) 临时关闭或开启bin-log日志写入。

使用命令 "show variables like '%log_bin%；" 查看到的变量 log_bin 是一个只读变量。但是，我们可以修改当前会话变量 sql_log_bin,这个变量没有全局global 变量。

我们可以修改 sql_log_bin的值，当为0或者为off时，则临时关闭命令写入bin-log日志的功能，如果值为1或者为on，则开启命令写入bin-log日志功能

复制#关闭数据库命令写入binlog日志功能
set sql_log_bin=1;
#开启数据库命令写入binlog日志功能
set sql_log_bin=1;

二 数据库备份的重要性与备份分类

1 数据库备份的重要性

• 备份的主要目的是灾难恢复
• 在生产环境中，数据的安全性至关重要
• 任何数据的丢失都可能产生严重的后果

2 数据库备份分类

2.1 从物理和逻辑的角度分类

• 物理备份： 对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
• 逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

物理备份方法：

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

2.2 从数据库的备份策略角度分类

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

3 常见的备份方法

• 物理冷备
  • 备份时，数据库处于关闭状态，直接打包数据库文件
  • 备份速度快，恢复也是最简单的

• 专用工具mysqldump 或者mysqlhotcopy
  • mysqldump 常用的逻辑备份工具
  • mysqlhotcopy 仅拥有备份 myisam 和archive 表

• 启用二进制日志binlog日志进行增量备份
  • 进行增量备份，需要刷新二进制日志

• 第三方工具备份
  • 免费的mysql 热备份软件Percona XtraBackup

三： 数据库的完全备份

1 物理冷备份与恢复

1. 关闭mysql 数据库
2. 使用tar 命令直接打包数据库文件夹(工作目录下的data目录)
3. 直接替换现有Mysql目录即可

1.1 物理冷备份

复制#停止mysqld服务
systemctl stop mysqld
#创建专门用来存放数据库备份文件的目录
mkdir /backu
#打包数据文件夹
tar Jcvf /backup/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
ls /backup

1.2 删除数据库里的任意数据

复制systemctl restart mysqld
mysql -uroot -pabc123 -e 'drop database bank;'
mysql -uroot -pabc123 -e 'show databases;'

1.3 恢复数据

复制#先停止mysql 服务
systemctl stop  mysqld
#解压备份的tar包
tar -Jxf mysql_all_2021-09-01.tar.xz
#将原来的mysql工作目录的data目录移走备份
mv /usr/local/mysql/data/ ./data.bak
#将tar包解压后目录usr里的data目录 移动到mysql的工作目录
mv usr/local/mysql/data/ /usr/local/mysql/
#重启mysqld服务
systemctl start mysqld.service
#查询数据库，发现bank库恢复了
mysql -uroot -pabc123 -e 'show databases;'

备份的tar包，如果打包时是在mysql工作目录，直接以相对路径打包的data目录，则解压后直接是data目录， 如果tar 打包时使用的是绝对路径，则解压后出现的会是usr目录。因为tar打包时将路径也一起打包进去了。需要进入解压后出现的当前目录下的usr/local/mysql里，把data目录移动到mysql工作目录下

2 mysqldump 备份与恢复

2.1 mysqldump 备份

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

格式

mysqldump -u root -p[密码] --databases 库名1 [库名2] .... > /备份路径/备份文件名.sql

复制#备份数据库 bank 库(备份单个数据库可以不用加 --databases)
mysqldump -uroot -pabc123 --databases bank > /backup/bank.sql
#备份数据库 test, test2 （备份多个库，要加--databases)
mysqldump -uroot -pabc123 --databases  test test2 > /backup/tests.sql
ls /backup

（2） 完全备份mysql 服务器中所有的库

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

复制#完全备份数据库中所有的库
mysqldump -uroot -pabc123 --all-databases > /backup/all_bak.sql

(3) 完全备份数据库中的部分表

mysqldump -uroot -p密码 [-d ] 库名 [表名1] 表名2 ..... > /备份路径/备份文件名.sql

-d 选项：表示使用-d 选项，表示只备份数据表的表结构。不使用-d 表示连同数据也一起备份

复制#备份数据表，连同数据也一起备份(备份数据表时，不可以加--databases 选项)
mysqldump  -uroot -pabc123  test location store_info > /backup/test_tables.sql
#备份数据表，只备份数据表的表结构
mysqldump  -uroot -pabc123   -d  test2 Total_Sales city > /backup/test2_tables.sql

（4） 查看备份文件

2.2 mysql 完全恢复

（1） 恢复数据库

mysql -uroot -p密码 < /备份文件路径/备份文件名.sql

复制#删除数据库bank
mysql -uroot -pabc123 -e 'drop database bank;'
#在线恢复数据库bank
mysql -uroot -pabc123 < /backup/bank.sql

（2）恢复数据表

mysql -uroot -p密码 库名 < /备份文件路径/备份文件名.sql

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时，必须指定库名，并且，目标库必须存在

复制#如果没有库，则先创建数据库
mysql -uroot -pabc123 -e 'create database bank;'
#向数据库中导入数据表
mysql -uroot -pabc123 bank  < /backup/bank_employee.sql

四： mysql 增量备份

1 增量备份的特点

• 增量备份是自上一次备份后增加/变化的文件或者内容
• 没有重复数据，备份量不大，时间短
• 恢复需要上次-完全备份及完全备份之后的所有增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

2 mysql 的增量被与二进制文件binlog

mysql没有提供直接的增量备份方法，需要通过mysql提供的二进制日志间接实现增量备份

mysql二进制日志对备份的意义

• 二进制日志保存了所有更新或者可能更新数据库的操作
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_ binlog_ size所设 置的大小或者接收到flush logs命令后重新创建新的日志文件
• 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了-一个时间段的增量备份

3 mysql 增量备份

3.1 开启二进制日志功能

二进制binlog有3种不同的记录格式：statement(基于sql语句)，row(基于行)，mixed（混合模式）,默认格式是statement。

statement 模式：每一条修改数据的sql都会记录在binlog种。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高了性能。但是mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(

row模式：不记录sql语句上下文相关信息，仅保存哪条记录被修改，清楚的记录下每一行数据修改的细节。但是会产生大量的日志内容，占用IO资源

mixed模式:基于statement 和 row 两种模式混合使用。一般语句修改使用statement格式保存，但类似与函数操作，statement无法完成主从赋值的操作时，则使用row格式保存

复制vim /etc/my.cnf
[mysqld]
.....
log-bin=mysql-bin  #开启binlog日志
binlog_format=mixed  #使用mixed混合模式
server-id=1        #节点id
.....



systemctl restart mysqld
#mbinlog日志设置保存在工作目录的data目录中
ls /usr/local/mysql/data

3.2 刷新binlog日志

开启binlog日志后，mysql数据库里执行的需改数据的命令都会记录在binlog日志中。当日志达到设定的大小，或者执行刷新日志的命令，就会生成新的binlog日志。刷新后，保存每天产生的binlog日志，即增量备份。可以每周三或周四进行一此完全备份，然后每天进行增量备份

mysqladmin -uroot -p密码 flush-log

或者

mysql数据库命令 ： flus logs

复制#mysqladmin 刷新binlog日志
mysqladmin  -uroot -pabc123 flush-log

#通过myql数据库命令flush logs刷新日志 
# linux 命令行中，-e 选项，可以在命令行非交互执行mysql数据库命令。数据库里可以也可也使用 \!linux命令，在 mysql数据库里执行linux命令如 \! ping 127.0.0.1 ）
mysql  -uroot -pabc123 -e 'flush logs'

3.3 插入新数据，模拟数据的增加

复制#进入mysql数据库

create database school;
create table school.class(id int primary key,name char(20));
insert into school.class values (1,'zhangsan');
insert into school.class values (2,'lisi');
insert into school.class values (3,'wangwu');
insert into school.class values (4,'zhaoliu');

3.4 刷新binlog日志

复制#刷新日志。
flush logs
#从数据库里执行linux命令，查看data目录下mysql-bin. 开头的文件
\! ls /usr/local/mysql/data/mysql-bin.*

4 查看binlog日志内容

mysqlbinlog --no-defaults --base64-output=decode-rows -v binlog日志路径 > 新的文件

--base64-output=decode-rows: 使用64位编码机制去解码并按行读取

-v:显示详细内容

复制#将二进制文件mysql-bin.000003转码后输入到新的文件/backup/mysql-bin.000003中
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003 > /backup/mysql-bin.000003 

vim /backup/mysql-bin.000003

5 增量恢复

mysql 数据库的增量恢复有三种

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

• 基于位置恢复
  • 数据库在某一时间点可能即有错误的操作也有正确的操作
  • 可以基于精准的位置跳过错误的操作

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

5.1 一般恢复

msyqlbinlog [--no-defaults] binlog日志文件 | mysql -uroot -p密码

复制#删除库school
mysql -uroot -pabc123 -e 'drop database school;'
#从binlog日志中恢复全部数据
mysqlbinlog  --no-defaults /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -pabc123

5.2 基于位置恢复

mysqlbinlog --no-defaults --start-position='开始位置1' --stop-position='结束位置2' binlog日志 | mysql -uroot -p密码

--start-positon=''位置1'：

• 从位置1开始恢复。此位置应该选择，想要开始恢复的sql语句前面的 ‘begin“字段的前一个at 位置开始、

  
  

• '如果后面不加--stop-position=''位置2'，则表示从位置1开始恢复后面所有binlog日志的内容.

--stop-position=‘位置2':

恢复到位置2。此位置应该选择想要结束的sql语句后面的“commit”字段后一个at位置开始

如果前面不加 --start-position=’位置1' ，则表示从目标binlog日志最开启位置恢复到位置2

复制#清空表记录
mysql -uroot -pabc123 -e 'truncate table school.class;'

#想要只恢复id为2和3的表记录，不恢复id为1和id为4的数据
#选择开启位置871，结束位置1524恢复期间的数据
mysqlbinlog --no-defaults --start-position='871'  --stop-position='1382'  \
/usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -pabc123

5.3 基于时间点恢复

mysqlbinlog --no-defaults --start-datetime='xxxx-xx-xx hh:mm:ss' --stop-datetime='xxxx-xx-xx hh:mm:ss' binlog日志 | mysql -uroot -p密码

基于时间点恢复与基于位置恢复差不多。时间格式有要求，类似于2021-09-03 12:12:12

--start-datetime='xxxx-xx-xx hh:mm:ss' :开始位置，应该以sql语句前begin字段前面的时间为开始

-stop-datetime='xxxx-xx-xx hh:mm:ss':结束位置，应该以sql 语句后 commit 字段的后面一个时间为结束

复制#清空数据表
mysql -uroot -pabc123 -e 'truncate table school.class;'

#以时间为节点，恢复id为2和id为3的两条表记录
mysqlbinlog --no-defaults --start-datetime='2021-09-03 01:05:47' \
--stop-datetime='2021-09-03 01:06:08' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -pabc123