数据库服务日志管理
数据库服务中有什么日志？（4种）
通用日志（了解）
记录登录数据库的用户信息，登录用户操作行为会被记录
错误日志（关注）
记录数据库启动运行信息/数据库运行过程中错误情况 ，便于排查数据库问题
二进制日志（重点）
（1）二进制日志可以记录用户事务操作信息（DDL DML DCL--commit DQL不会记录-select show），可以用于修复数据
（2）利用二进制日志文件，可以实现主从同步数据
慢查询日志
记录在操作数据库过程中的慢查询语句
（1）给DBA管理员，提供进行数据配置和架构优化的建议;
（2）对于运维人员关注慢查询日志，主要确认慢查询日志的数据量，如果相同慢查询语句出现次数过多，可以告知DBA做优化
数据库日志信息管理
通用日志（默认不存在）
general_log=ON
general_log_file=/data/3306/log/general.log
错误日志（默认存在）
log_error=0/off --禁用错误日志
log_error=./db01.err --指定生成默认日志保存在哪里
#数据库数据目录下
[root@db01 ~]# ll /data/3306/data/db01.err
-rw-r----- 1 mysql mysql 26646 8月 21 08:43 /data/3306/data/db01.err
select @@log_error;
二进制日志（默认存在）
- log_bin -- 可以配置二进制日志开启或关闭功能/指定二进制日志存储路径
select @@log_bin;
- sql_log_bin=1 -- 是否激活事务记录功能 1表示事务信息会记录到二进制日志 0表示事务信息不会记录到二进制日志
- 做binlog日志进行数据修复时，可以将记录binlog日志功能临时关闭 sql_log_bin=0/off
select @@sql_log_bin;
（1）如何查看二进制日志（在数据库中查看）
- 查看二进制日志
show binary logs; --- 显示当前数据库实例中，所有生成binlog日志信息，并且是当前存在的；
- 查看当前正在使用的binlog日志
show master status; --- 查看主库binlog日志信息记录情况
File： 表示当前数据库实例正在使用binlog日志；
Position：事务完成之后的位置点用于可以截取日志信息，完成特定数据的修复/还可以便于进行主从同步
Binlog_Do_DB： 表示指定某个或某几个数据库的事务操作，会记录到binlog日志中；(二进制日志记录信息白名单)
Binlog_Ignore_DB：表示指定某个或某几个数据库的事务操作，不会记录到binlog日志中； (二进制日志记录信息黑名单)
- 直接查看binlog日志内容
show binlog events in '日志文件名称'; --- 在数据库中查看binlog日志内容
- 知识点补充：如歌实现事务自动提交
- 利用autocommit配置项，可以实现事务信息自动提交
- 当完成事务过程，出现DDL或DCL语句，也会自动触发提交功能
（2）在命令行中查看二进制文件
cd /data/3306/data/
mysqlbinlog binlog.000036;
- 数据恢复
--start-position=# -- 从binlog日志中，根据位置点过滤指定事务信息（从哪过滤）
--stop-position=# -- 从binlog日志中，根据位置点过滤指定事务信息（到哪结束）
[root@db01 /data/3306/data]# mysqlbinlog --help | grep start
[root@db01 /data/3306/data]# mysqlbinlog --help | grep stop
#过滤起始点到结束点导入到备份文件
mysqlbinlog --start-position=234 --stop-position=345 binlog.000036 >/backup/xiaoA.sql
ll /backup/xiaoA.sql
#加载恢复文件
[root@db01 /data/3306/data]# mysql -uroot -p1 </backup/xiaoA.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
- 将DML编码语句进行解码，以明文方式显示DML语句
mysql> insert into student values (1,'xiaoA');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+-------+
| id | name |
+------+-------+
| 1 | xiaoA |
+------+-------+
1 row in set (0.00 sec)
#从binlog日志中，将DML编码语句进行解码，以明文方式显示DML语句
mysqlbinlog --base64-output=decode-rows -vvv '日志文件名称';
[root@db01 /data/3306/data]# mysqlbinlog --base64-output=decode-rows -vvv 'binlog.000038';
--decode-rows 解码
- 从binlog日志中，将DML编码语句进行解码，以明文方式显示DML语句
INSERT INTO `xiaoA`.`student` SET @1=1 @2='xiaoA';
INSERT INTO `xiaoA`.`student` value (1,'xiaoA');
二进制日志记录格式（面试题）
（1）二进制日志记录信息有三种格式：
SBR（语句格式记录）：会将DML操作事务信息，以语句明文方式记录
PBR（行格式记录）：会将DML操作事务信息，以具体数据行的信息记录
MBR（混合方式记录）：会将DML操作事务信息，进行自动识别，根据识别后内容自动选择记录方式
binlog_format=statement 语句格式记录
应用场景：对表操作过程中，没有函数信息应用，并且需要更直观查看DML事务信息
binlog_format=row 行格式记录
应用场景：对表操作过程中，需要函数信息应用，并且有主从同步架构，为了保证数据一致性
- 修改配置文件添加语句格式
- 语句格式记录
- 行格式记录
#注释掉语句格式记录
- 查看当前记录格式
- 删除表中数据，会以行格式记录
- 再执行相同的插入语句
删除带有函数信息的数据，导致操作前后的数据不一致
二进制日志切割
（1）手动执行命令切割
#数据库中执行
mysql> flush logs;
#命令行
mysqladmin -uroot -p1 flush-logs
（2）根据文件大小进行切割
max_binlog_size 字节量切割（默认1G）
#查看数据库默认切割数据量
select @@max_binlog_size;
#调节数据库默认切割数据量
select 100*1024*1024;
二进制日志清理
（1）进行日志信息手动清理
#修剪
#删除到指定文件前的binlog日志
mysql>purge binary logs to 'binlog.000047'
#可以基于日志时间点信息进行日志清理，超过某个时间的就清理
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
（2）进行日志信息自动清理（根据时间）
方法一
binlog_expire_logs_seconds 3600
--根据二进制日志存在了多少秒，就会进行删除清理
- 编写数据库配置文件，写入binlog_expire_logs_seconds=3600
- 清理超过一个小时的日志文件
- 重启数据库查看结果
/etc/init.d/mysql restart
方法二
expire_logs_days
--根据二进制日志存在了多少天进行清理
二进制日志远程备份（实现日志信息统一管理）
- 实现高可用架构会应用binlog日志远程备份功能
- 环境准备--两台数据库服务器db01与db03
mkdir -p /binblog_backup/10.0.0.{51..55}
cd /binblog_backup/10.0.0.51
#拉取相应主机日志信息
mysqlbinlog -R --host=10.0.0.51 --user=root --password=1 --raw --stop-never binlog.00008 &
-R --远程拉取目标主机日志信息
--host --指定拉取日志目标主机IP地址
--uesr --指定登录数据库用户名
--password --指定登录数据库用户登录密码
--raw --实时拉取日志信息（指定binlog日志信息记录二进制信息）
--stop-never --以守护进程方式一直拉取日志信息（指定binlog日志信息将会一直备份记录）
binlog.000008 --代表从哪个binlog日志开始进行备份
- 远程备份实践过程，db03执行远程备份命令
知识扩展--利用binlog日志实现误操作数据的闪回功能
- 数据信息物理损坏：硬件出现异常 数据库服务异常 （主从架构 高可用架构）
- 数据信息逻辑损坏：出现数据信息误操作（DML） （数据闪回功能）
- binlog2sql介绍
从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等
数据快速回滚(闪回)
主从切换后新master丢数据的修复
从binlog生成标准SQL，带来的衍生功能
步骤一：下载数据闪回工具
www.github.com -- 搜索 binlog2sql -- mysql数据实现数据闪回
步骤二：安装软件程序包
ll /opt/binlog2sql-master
yum install -y python3 （可选）
pip3 install -r requirements.txt
步骤三：测试应用数据闪回工具
use xiaoD;
create table t1 (id int,name char(10),age int);
insert into t1 values (1,'xiaoA',18),(2,'xiaoB',19),(3,'xiaoC',20);
模拟误操作：
- 误插入数据
insert into t1 values (4,'xiaoD',18);
- 误修改数据
update t1 set name='oldboy' where id=2;
- 误删除数据
delete from t1 where id=3;
#python工具闪回 -B展示逆向操作，不写显示误操作信息
python3 binlog2sql.py -h10.0.0.51 -P3306 -uroot -p1 -d xiaoD -t t1 --sql-type=insert --start-file='binlog.000003' -B
#显示操作信息
--sql-type=insert
insert into t1 values (4,'xiaoD',18); -逆向操作信息-> delete from t1 where id=4 and name=xiaoD and age=18;
#python工具闪回
python3 binlog2sql.py -h10.0.0.51 -P3306 -uroot -p1 -d xiaoD -t t1 --sql-type=update --start-file='binlog.000003' -B
#显示操作信息
--sql-type=update
update t1 set name='oldboy' where id=2; --> update t1 set name='xiaoB' where id=2;
修改为 xiaoB-oldboy id=2
#python工具闪回
python3 binlog2sql.py -h10.0.0.51 -P3306 -uroot -p1 -d xiaoD -t t1 --sql-type=delete --start-file='binlog.000003' -B
#显示操作信息
--sql-type=delete
delete from t1 where id=3; --> insert into t1 values (3,'xiaoC',20);
- 返回误插入数据
- 返回误修改数据
- 返回误删除数据
#复制到数据库执行
DELETE FROM `xiaoA`.`t1` WHERE `id`=4 AND `name`='xiaoD' AND `age`=18;
UPDATE `xiaoA`.`t1` SET `id`=2, `name`='xiaoB', `age`=19 WHERE `id`=2 AND `name`='oldboy' AND `age`=19;
INSERT INTO `xiaoA`.`t1`(`id`, `name`, `age`) VALUES (3, 'xiaoC', 20);
数据库服务备份恢复
逻辑备份与恢复
备份和恢复原理
（1）备份原理：读取数据库中数据对象以及表中数据信息，将数据对象创建过程和表中数据插入过程SQL语句导出
show databases -- show create database 库名 --创库语句
use 库名 --show tables --show create table 表名 --创表语句
识别表中数据信息 --insert XXX
- 备份实践过程
mysqldump
-A 全库备份
-B 分库备份
- 备份方式一：实现数据库信息全备备份
本地备份 socket_file数据库配置文件没有需要加上
mysqldump -u用户 -p密码 -S socket_file -A > /backup/all.sql
远程备份（异地备份）
mysqldump -u用户 -p密码 -h数据库服务器IP地址 -P数据库服务端口 > -A /backup/all.sql
- 备份方式二：实现数据库分库备份（分库备份脚本）
- https://blog.csdn.net/weixin_64334766/article/details/145671441
- 建议一个一个备份数据库，避免业务断联......
mysqldump -uroot -pXXXX -B 库名,库名，.... > /backup/world.sql
- 备份方式三：实现数据库分表备份
mysqldump -uroot -pXXX 库名 表名 > /backup/xiaoD_t1.sql
xiaoD t1
（2）恢复原理：重新加载备份文件中SQL语句
应用场景：只适合于少量数据进行备份与恢复（50G）
可以将备份后的数据进行灵活恢复
- 恢复数据过程：
- 恢复方式一：实现全备恢复数据
#导入数据到数据库
mysql -uroot -pxxx < /backup/all.sql
#实时查看恢复过程
mysql>source /backup/all.sql;
- 恢复方式二：实现恢复分库备份数据
mysql -uroot -pxxx < /world/world.sql
- 恢复方式三：实现恢复分表备份数据
mysql -uroot -pxxx 库名 </backup/xiaoD_t1.sql
#切换到对应的数据库
mysql>use xiaoD
mysql>source /backup/xiaoD_t1.sql
物理备份与恢复
备份与恢复原理
备份原理：将数据库数据目录中的所有信息做备份
恢复原理：将备份后的数据目录重新迁移到原有数据库数据目录中，重新启动数据库服务加载
（1）实现物理方式冷备份
备份实践过程:
/etc/init.d/mysql stop
cp -a /data/3306/data/ /bakup/data_2025_08_21
恢复实践过程
/etc/init.d/mysql stop
rm -rf /data/3306/data/*
cp -a /bakup/data_2025_08_21/* /data/3306/data/
（2）实现物理方式热备份（xbk）
-
步骤一：下载xbk工具
[https://www.percona.com/downloads/](https://www.percona.com/downloads/) -
麒麟版本选择
- Centos7.9版本选择
mv percona-xtrabackup-80-8.0.32-26.1.el7.x86_64.rpm/usr/local/
cd /usr/local
yum localinstall -y percona-xtrabackup-80-8.0.32-26.1.el7.x86_64.rpm
-
步骤二：实现物理备份与恢复
方式一：物理全备与恢复
物理备份
mkdir -p /data/backup/full -p --创建保存物理数据的空目录
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.51 --user=root --password=1 --port=3306 --backup --target-dir=/data/backup/full
全备恢复
/etc/init.d/mysqld stop
rm -rf /data/3306/data/*
xtrabackup --prepare --target-dir=/data/backup/full -- 恢复备份过程中的内存数据
prepare -- 表示先将内存备份数据，恢复回内存中
xtrabackup --copy-back --target-dir=/data/backup/full -- 恢复备份过程前的磁盘数据
恢复目录磁盘数据
chown -R mysql.mysql /data/3306/data
/etc/init.d/mysqld start
-- 恢复数据库服务运行
- 方式二：物理增量备份与恢复
#增量备份过程
mkdir /data/backup/week01 -p -- 作为第一周全备数据存储目录
mkdir /data/backup/week02 -p -- 作为第二周增量数据存储目录
mkdir /data/backup/week03 -p -- 作为第三周增量数据存储目录
第一周全备
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.53 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/week01
第二周增量备份：
create database week02;
use week02
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.53 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/week02 --incremental-basedir=/data/backup/week01
第三周增量备份：
create database week03;
use week03
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
xtrabackup --defaults-file=/etc/my.cnf --host=10.0.0.53 --user=root --password=123456 --port=3306 --backup --parallel=4 --target-dir=/data/backup/week03 --incremental-basedir=/data/backup/week02
#增量恢复数据
模拟数据库数据损坏
/etc/init.d/mysqld stop
rm -rf /data/3306/data/*
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/week01
-- 全量数据目录中数据信息恢复到内存中的（不做恢复）
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/week01 --incremental-dir=/data/backup/week02
-- 第一次增量备份过程中需要恢复到内存中的数据也做加载
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/week01 --incremental-dir=/data/backup/week03
-- 第二次增量备份过程中需要恢复到内存中的数据也做加载
xtrabackup --prepare --target-dir=/data/backup/week01
-- 将全量和增量备份过程中，需要恢复到内存的数据统一恢复到内存中
--apply-log-only -- 取消undo日志文件加载过程
以上信息表示将内存信息做整合恢复
xtrabackup --datadir=/data/3306/data --copy-back --target-dir=/data/backup/week01
以上信息表示将磁盘信息做整合恢复
chown -R mysql.mysql /data/3306/data
/etc/init.d/mysqld start
