mysql日志与备份恢复
mysql 日志管理与备份恢复
一 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 物理冷备份与恢复
- 关闭mysql 数据库
- 使用tar 命令直接打包数据库文件夹(工作目录下的data目录)
- 直接替换现有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;'
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
