Day08-数据库服务日志管理
- 1、数据库事务工作流程
- 2、数据库服务日志概述
- 4、数据库服务日志分类
- 6、数据库备份恢复方式
- 7、数据库逻辑备份实践（mysqldump）
1、数据库事务工作流程 (A-原子性 c-一致性 I-隔离性 D-持久性) 详述
2、数据库存储行锁模式 (表锁)
3、数据库服务日志概述
4、数据库服务日志分类
5、数据库服务日志配置
6、数据库备份恢复方式 （物理备份/逻辑备份）
7、数据库逻辑备份实践 mysqldump
8、数据库逻辑备份参数 (进阶参数)
9、数据库逻辑备份案例
10、数据库逻辑备份痛点
学生问题:
1、如何知道多个undo日志文件，正在使用哪一个?
多个undo日志文件，是平均存储数据信息的
2、dblwr DWR双写文件的大小如何查看调整?
固定大小设置 总的dwr buffer缓冲区大小为2M，分别以1M大小为单位进行存储
1、数据库事务工作流程
事务处理I特性如何保证：隔离性 – 隔离级别+MVCC+锁（控制并发处理）
数据读隔离性：隔离级别+mvcc（锁机制-乐观锁/悲观锁）
RU：可以读未提交的信息 可以直接读取其他事务的内存中数据信息
RC：可以读提交的信息 可以直接读取其他事务提交的数据信息
RR：只能读取自己事务中的信息（写）
SR：事务对行操作时（读或者写）都会产生行锁，影响其他事务的操作；
mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
+--------------------------+------------+
5 rows in set (0.01 sec)mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)mysql> set global transaction_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)
-- 退出从新进
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE |
+-------------------------+
1 row in set (0.00 sec)
MVCC机制（类似git-多个分支），实质是应用快照技术实现读数据信息的隔离：
RC：应用MVCC的快照读机制，是基于语句级别的；（不可重复读）
在事务期间，执行每个查询语句的时候，都会检查MVCC版本（快照列表），获取最新的已提交事务的快照；
RR：应用MVCC的快照读机制，是基于事务级别的；（可重复读）
在事务期间，执行首条查询语句的时候，就会生成MVCC版本（相应快照），将会一直读取此快照数据信息，直到事务生命周期结束；
以上的RR隔离级别利用MVCC的快照读机制，又称为一致性快照读；
数据写隔离性：隔离级别+锁
补充：数据库中锁的作用
1）避免事务并发冲突问题；
2）避免资源信息抢占释放；
-
数据库服务资源分类-锁：内存 CPU IO
参考教案资料 -
数据库服务应用分类-锁：
S共享锁：对同一数据信息操作时，可以有多个锁申请
X排他锁：对同一数据信息操作时，只有一个锁可以申请
早期应用在表上：
A-读取数据-S锁 B-读取数据-S锁
A-写入数据-X锁 B-写入数据-？？（无法申请）
目前应用在行上：
A-读取数据-S锁 B-读取数据-S锁
A-写入数据-X锁 B-写入数据-？？（无法申请）
目前应用在表上：
IS-意向读锁 对A数据表中的某行进行读取时，必须先取得IS意向锁 A表-IS A表数据行-S
IX-意向排他锁 对A数据表中的某行进行修改时，必须先取得IX意向锁 A表-IX A表数据行-X
2、数据库服务日志概述
数据库中会有多个不同的日志信息，分别保存到不同的文件中：
服务层：多种不同日志，主要会记录服务运行情况 数据信息存储情况（用于数据恢复/用于数据同步）
引擎层：多种不同的日志redo日志、undo日志、dwr日志
4、数据库服务日志分类
服务层有4个重点关注的日志：
mysql> show variables like '%log%';
general_log：数据库运行过程中，所有操作数据库的记录信息
作用说明：可以用于测试和审计
配置应用：
mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /data/3306/data/db01.log |
+------------------+--------------------------+
2 rows in set (0.01 sec)general_log=OFF
-- 默认日志功能处于关闭，建议在需要做调试工作时（功能测试、语句审计）可以打开；
general_log_file=/data/3306/logs/general_log/db01.log
-- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；# 修改日志默认状态（激活日志）：
mysql > set global general_log=1;
说明：企业真实环境，由于日志记录量比较大，所以不建议打开此日志记录功能，可以在有需要时打开，支持在线配置调整；
# 调整时间
mysql> show variables like '%log_time%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| log_timestamps | UTC |
+----------------+-------+
1 row in set (0.01 sec)# 编辑配置文件
[root@db01 ~]# vim /etc/my80.cnf
......
[mysqld]
log_timestamps=SYSTEM
....
error_log：数据库服务运行的状态日志，会记录数据库服务影响情况 note error**** warning
作用说明：当数据库服务无法启动时，需要关注错误日志
PS：当数据库无法启动时，没有看到错误日志信息？
99.9% – 数据库服务配置文件写错了/数据库服务没有正常初始化
配置应用：
log_error=./xiaoQ-01.edu.err
-- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；# 修改日志存储路径（永久配置）：
[root@xiaoq ~]# vim /etc/my.cnf
log_error=/tmp/mysql3306.err
-- 配置文件编写完毕后，需要重启数据库服务生效mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /data/3306/logs/error_log/db01.err |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+----------------------------------------+
5 rows in set (0.00 sec)[root@db01 ~]# tail -f /data/3306/logs/error_log/db01.err
2024-08-23T05:36:54.130146Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2024-08-23T05:36:54.130239Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.26) starting as process 1826
2024-08-23T05:36:54.137771Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-08-23T05:36:54.336939Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-08-23T05:36:54.578464Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2024-08-23T05:36:54.578554Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2024-08-23T05:36:54.579665Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-08-23T05:36:54.579840Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-08-23T05:36:54.612458Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.26' socket: '/tmp/mysql3306.sock' port: 3306 MySQL Community Server - GPL.
2024-08-23T05:36:54.612910Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-08-23T05:45:49.841681Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.26).
2024-08-23T05:45:51.338683Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.26) MySQL Community Server - GPL.
-- 根据错误日志的错误提示信息，进行错误信息进行分析，从而排查故障可能出现的原因；
bin_log：存储数据库操作命令语句信息（只有DDL DML-insert update delete）
作用说明：可以实现数据恢复（增量数据恢复） 可以实现数据同步
配置应用：
1）激活日志功能
log_bin=ON
log_bin=/data/3306/logs/bin_log/db01-binlog
-- 表示是否开启binlog日志功能，8.0.26默认开启状态
2）查看日志状态信息
查看方式一：确认数据库binlog日志数量
mysql> show binary logs;
+------------------+-------------+--------------+
| Log_name | File_size | Encrypted |
+------------------+-------------+--------------+
| binlog.000001 | 156 | No |
+------------------+-------------+--------------+
-- 获取数据库服务运行过程中，使用的binlog日志的情况
-- 查看有多少个binlog日志文件可以识别，以及日志信息内容的变化量（查询操作不会记录到日志中）mysql> flush logs;
Query OK, 0 rows affected (0.12 sec)
-- 可以执行flush刷新命令，从而生成新的binlog日志文件，类似于实现了日志切割功能；mysql> show binary logs;
+------------------+-------------+--------------+
| Log_name | File_size | Encrypted |
+------------------+-------------+--------------+
| binlog.000001 | 200 | No |
| binlog.000002 | 156 | No |
+------------------+-------------+--------------+
2 rows in set (0.00 sec)
查看方式二：确认数据库binlog日志状态
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| db01-binlog.000002 | 655 | | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 查看日志的状态信息，以及产生的日志量字节大小；
Position： 记录日志事件变化的位置点
Binlog_Do_DB： 白名单设置信息（主从）
Binlog_Ignore_DB： 黑名单设置信息（主从）
Executed_Gtid_Set： 设置全局事务编号功能（5.7/8.0）
3）查看数据库binlog日志信息
方式一：数据库中查看binlog数据信息：
mysql> show binlog events in 'binlog.000002';
-- 在数据库中查看binlog日志信息内容
-- binlog日志信息是以事件方式进行记录的，所以日志查看过程是查看事件信息
-- 一般binlog日志的前两行，表示日志格式头信息（日志简单的描述信息）
-- 一般binlog日志中的query信息，就是对数据库的操作语句，其中包含了创建数据库的语句；方式二：系统命令查看binlog数据信息4）筛选数据库binlog日志事件
# 筛选数据库日志方式一：利用grep
[root@db01 ~]# mysql -S /tmp/mysql3306.sock -e "show binlog events in 'db01-binlog.000004'"|grep "drop database"
db01-binlog.000004 735434 Query 3306 735541 drop database world /* xid=5463 */
-- 获取指定事件信息产生的起点位置和终点位置信息；# 筛选数据库日志方式二：
mysql> pager less
-- 在数据库中定义pager功能，数据库连接会话退出即失效；
mysql> show binlog events in 'binlog.000002';
-- 开启pager less，再进行binlog日志信息查看时，就有了筛选过滤能力
-- 此时查看日志事件信息具有了翻页功能
/drop database
| binlog.000002 | 722789 | Query | 1 | 722896 | drop database world /* xid=5363 */
mysql> pager grep "drop database"
PAGER set to 'grep "drop database"'
-- 表示开启数据库pager的过滤功能
mysql> show binlog events in 'binlog.000002';
| binlog.000002 | 722789 | Query | 1 | 722896 | drop database world /* xid=5363 */
-- 再次查看binlog事件信息时，只过滤显示删除数据库的操作事件日志
binlog日志有关知识补充：
# 参数一：sync_binlog 表示刷新日志到磁盘策略 （数据库双一配置参数中的第二个）
mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
-- 在进行主从同步过程的双一标准的其中一个1的信息配置，主要是控制缓冲区里的binlog日志信息如何刷写到磁盘中；
-- 此参数信息是有三种方式进行配置的：
-- 参数信息配置0：表示由操作系统缓存自己决定，什么时候刷新日志到磁盘中；
-- 参数信息配置1：表示每次事务提交，立即刷新日志到磁盘中；(此方式配置更安全)
-- 参数信息配置N：表示每组事务提交，按照组的事务次数定义，确定刷新日志到磁盘中的频次；（可以有效减少IO性能损耗）
-- 参数官方资料链接：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html# 参数二：binlog_format 定义binlog日志的格式信息
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
-- 在进行主从同步数据恢复时，此参数配置可能会影响数据恢复的一致性问题；
-- 此参数信息是有三种方式进行配置的，确定了主从复制的级别，只针对DML语句的日志才有效；
-- 参数信息配置 statement（SBR）：语句格式记录binlog；
- ROW：RBR（Row-Based Replication）
-- 会记录行的变化信息，属于底层的记录信息，可能会有多个变化日志信息记录
- statement：（SBR）
-- DDL DCL语句只能使用statement 表示的就是原原本本的语句信息，即做什么就记录什么；
- mixed（MBR）：混合格式记录binlog
-- 由数据库服务自行决定，是记录语句信息，还是记录行的变化信息；
5）日志信息滚动切割：
方式一：数据库中操作
> flush logs;
方式二：命令行操作
[root@db01 bin_log]# mysqladmin -S /tmp/mysql3306.sock flush-logs
方式三：重启数据库服务
[root@db01 bin_log]# systemctl restart mysqld3307
方式四：实现自动切割
> select @@max_binlog_size;
-- 设置binlog日志切割时的存储容量上限
6）日志信息清理方法：
方式一：
binlog_expire_logs_seconds=2592000
-- 按照秒进行binlog日志清理
expire_logs_days=0
-- 按照天进行binlog日志清理
方式二：
purge binary logs to 'db01-binlog.000006';
-- 将binlog清理到哪个文件
PURGE BINARY LOGS BEFORE '2019-04-04 22:46:26';
-- 根据文件时间进行清理
企业清理binlog日志建议：binlog日志保留只要两个全备周期内日志量
| 周日（0点） | 周一（0） | 周二（0） | … | 周六（0） | 周日（0） | 周一（） |
|---|---|---|---|---|---|---|
| 整个数据库全备 | binlog-周一 | binlog-周二 | … | binlog-周六 | 整个数据库全备 | binlog-周一 |
| binlog-周日 |
7）日志信息远程备份：
利用专门的存储服务器，将数据库服务中binlog日志进行同步备份
[root@db02 ~]# mkdir -p /binlog_buckup
[root@db02 ~]# cd /binlog_buckup/
[root@db02 binlog_buckup]# mysqlbinlog -R --host=10.0.0.51 --user=root --password=123456 --port=3306 --raw --stop-never db01-binlog.000001 &
[root@db02 binlog_buckup]# ll
总用量 744
-rw-r----- 1 root root 205 2024-08-27 15:25 db01-binlog.000001
-rw-r----- 1 root root 704 2024-08-27 15:25 db01-binlog.000002
-rw-r----- 1 root root 1410 2024-08-27 15:25 db01-binlog.000003
-rw-r----- 1 root root 735541 2024-08-27 15:25 db01-binlog.000004
-rw-r----- 1 root root 156 2024-08-27 15:25 db01-binlog.000005
-rw-r----- 1 root root 205 2024-08-27 15:25 db01-binlog.000006
-rw-r----- 1 root root 156 2024-08-27 15:25 db01-binlog.000007screen # 实现后台永久运行
nohup # 实现后台永久运行
binlog日志应用实践：
# 步骤一：模拟创建库 创建表 插入数据
mysql> create database bindb;
Query OK, 1 row affected (0.01 sec)mysql> use bindb;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.04 sec)mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 在没有进行事务提交前，操作的事务事件信息，是不会出现在binlog事件日志中的
mysql> commit;
-- 对于数据库的binlog日志，只会记录事务已经提交的DML语句信息，没有提交的DML语句是不会进行记录的；
-- 在日志中变化的DML语句信息是无法识别的，因为记录DML操作的语句默认是以ROW模式记录的；# 步骤二：模拟删除数据库
mysql> drop database bindb;# 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
mysql> show binlog events in 'binlog.000002';
# 或者
[root@db01 bin_log]# mysqlbinlog --base64-output=decode-rows -vvv db01-binlog.000001
-- 查看截取日志信息事件区域范围# 步骤三：利用binlog恢复数据信息
mysqlbinlog --start-position=156 --stop-position=897 /data/3306/logs/bin_log/db01-binlog.000001 >/tmp/bin.sql
-- 依据binlog日志的position号码，即可获取到想要恢复数据信息；# 根据截取的日志信息，进行数据库服务数据恢复
mysql> set sql_log_bin=0;
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；# 步骤四：导入
mysql> source /tmp/bin.sql# 查看确认数据信息是否恢复
mysql> use bindb;
mysql> show tables;
mysql> select * from t1;- 如何解密DML加密语句信息
# mysqlbinlog --base64-output=decode-rows -vvv /data/3306/data/binlog.000003
解密binlog日志中的DML语句，显示内容为：
### INSERT INTO `oldboy`.`test02`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='xiaoA' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### @3=18 /* INT meta=0 nullable=1 is_null=0 */
等价于
insert into test02 set id=1,name='xiaoA',age=18;
等价于
insert into test02 values(2,'xiaoB',19);
slow_log：数据库服务慢查询日志，会记录查询操作比较慢的语句 select
作用说明：关注慢查询语句，可以减少对磁盘IO的消耗
配置参数：
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
-- 是否激活慢查询日志功能
-- 此参数配置信息，表示是否激活启动慢日志记录功能，默认处于关闭状态mysql> select @@slow_query_log_file;
+-------------------------------+
| @@slow_query_log_file |
+-------------------------------+
| /data/3306/data/db01-slow.log |
+-------------------------------+
1 row in set (0.00 sec)
-- 用于设置日志保存的路径信息
-- 此参数配置信息，表示慢日志文件保存的路径信息；建议日志文件路径与数据存放路径进行分离；mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)
-- 用于定义超过多长时间的，就是慢查询信息（建议时间为0.01~0.1）
-- 此参数信息配置，表示记录慢日志的条件，默认是大于10s执行的语句，就会记录为慢查询语句；(建议时间为0.01~0.1)mysql> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)
-- 识别是否由于没有走索引，而出现慢查询信息
-- 此参数信息配置，表示慢日志中会记录没有使用索引的语句信息；# 修改日志默认状态（激活日志）：
mysql> set global slow_query_log=1;
mysql> set global long_query_time=0.01;
mysql> set global log_queries_not_using_indexes=1;
-- 可以对以上参数信息进行在线调整，也可以将以上参数编写到数据库my.cnf配置文件中，作为永久配置；# 日志信息分析方法
mysqldumpslow -s c -t 3 /data/3306/data/db01-01-slow.log
-- 按照慢查询语句的重复执行次数(c)进行排序(-s)，取出其中靠前(t)的前三名慢查询语句
-- 还可以扩展使用pt-query-digest更好的分析慢查询日志，支持图形化展示
-- what to sort by (al, at, ar, c, l, r, t), 'at' is defaultal: average lock timear: average rows sentat: average query timec: countl: lock timer: rows sentt: query time
6、数据库备份恢复方式
为什么需要进行备份恢复？
防止数据的逻辑损坏：误删除 误修改 （可以利用备份+日志、延时从库）
防止数据的物理损坏：磁盘、文件系统、数据文件（可以利用主从、高可用、备份+日志恢复数据）
备份数据的方式：
物理备份方式：冷备/热备 从底层数据页或者文件信息进行备份
当企业数据库服务产生的需要备份的数据量在50G以上，可以选择物理备份（xtrabackup）；
逻辑备份方式：热备 将数据库中生成数据的语句进行备份 mysqldump(MDP)
当企业数据库服务产生的需要备份的数据量在50G以内，可以选择逻辑备份（mysqldump）；
7、数据库逻辑备份实践（mysqldump）
语法结构：
[root@xiaoQ ~]# mysqldump -u数据库用户 -p数据库密码 [备份参数] > /路径信息/数据库备份文件.sql
-- 在执行mysqldump命令时，也会用到数据库连接登录的基础参数：-u -p -S -h -P
常用参数：
-A：实现数据库全备
-B：指定要备份的数据库
-F：备份完毕后切换binlog日志信息
数据库全备：
[root@db01 ~]# mysqldump -S /tmp/mysql3306.sock -A >/database_backup/all_database.sql
-- 本地备份
[root@db01 ~]# ll /database_backup/all_database.sql
-rw-r--r-- 1 root root 1254014 2024-08-27 19:14 /database_backup/all_database.sql
-- 利用mysqldump命令备份的数据文件是纯文本文件，是可以进行查看或过滤的；***
说明：利用-A创建数据库备份数据时，在备份数据中会含有 create建库语句和use切换库语句，可以直接进行恢复操作即可；
数据库单库或多库备份：
单库备份：
[root@db01 ~]# mysqldump -S /tmp/mysql3306.sock -B oldboy >/database_backup/oldboy.sql
[root@db01 ~]# ll /database_backup/oldboy.sql
-rw-r--r-- 1 root root 6479 2024-08-27 19:23 /database_backup/oldboy.sql多库备份：
[root@db01 ~]# mysqldump -S /tmp/mysql3306.sock -B oldboy bindb >/database_backup/oldboy_bindb.sql# 过滤部分内容后查看备份数据库文件信息：
[root@db01 ~]# egrep -vi '^-|^/\*|^$|lock' /database_backup/oldboy_bindb.sql
说明：利用-B创建数据库备份数据时，在备份数据中会含有 create建库语句和use切换库语句，可以直接进行恢复操作即可；
数据库单表或多表备份：
数据库服务备份恢复职责：
- 设计数据库备份策略：备份数据周期、选择的备份工具、应用的备份方式（全备 增量…）；
- 定期数据库备份检查：核实是否存在、确认备份文件大小；
- 安排数据库恢复演练：真实确认备份的数据，是否能够准确的做数据恢复；
- 真实数据库恢复能力：在数据库服务出现异常情况时，可以将数据库服务修复，并恢复丢失的数据信息；
- 关于数据库迁移升级：可以采用Mergeing方式（主从架构）、可以单独备份数据信息到新的数据库节点做恢复（逻辑导出）；
课程作业:
情况一：日志文件被清理过，可能建库语句所在日志已经丢失；**(在后面课程章节处理)
情况二：所需日志跨越多个文件，如何进行日志信息的截取；
情况三：如何从日志文件中恢复单库、单表、或者部分行数据信息；