MySQL 数据库日志总结(二)

日志信息滚动切割

在应用binlog日志过程中，经常需要对日志文件进行日志切割（滚动更新），可以有效避免日志文件数据量过大问题；
在某些场景中，如果需要对binlog日志文件进行备份操作时，也可以对原有使用的binlog日志文件进行滚动更新；
常用的日志滚动更新方法：

1. # 方法一：
2. mysql> flush logs;
3. -- 滚动更新前的日志文件就会处于静止状态，不会在进行数据信息的更新
5. # 方式二：
6. [root@cheng ~ ]# mysql -uroot -p123456 flush-logs
8. # 方式三：
9. mysql> restart;  
10. -- mysql 8.0之后支持的数据库中重启服务；之前的版本只支持shutdown关闭数据库；
11. [root@cheng ~ ]# /etc/init.d/mysqld restart
13. # 方式四：
14. mysql> select @@max_binlog_size;
15. +--------------------------+
16. | @@max_binlog_size |
17. +--------------------------+
18. |                1073741824 |
19. +--------------------------+
20. -- 配置binlog日志最大数据存储量，默认大小为1G，到达最大日志存储量也会进行自动切割；

复制代码

日志信息清理方法

在系统中日志信息，随着时间的推移将会越来越多，将严重占用磁盘空间，因此需要对日志做相应清理工作；
对于日志信息常用的清理方式有两种：
方式一：进行日志信息自动清理

1. mysql> show variables like '%expire%';
2. +-------------------------------------+-----------+
3. | Variable_name                          | Value      |
4. +-------------------------------------+-----------+
5. | binlog_expire_logs_seconds   | 2592000 |
6. | expire_logs_days                      | 0             |
7. +-------------------------------------+-----------+
8. 3 rows in set (0.00 sec)
9. -- 在最新数据库8.0中，可以以秒为单位进行日志信息清理，默认是30天进行日志清理，或者也可以以天为单位进行清理；
10. -- 在最先数据库8.0前，主要是以天为单位进行清理，但默认清理功能并未激活；
11. -- 在企业实战环境中，建议过期时间最少保留一轮全备周期以上，有条件最好是保留两轮+1；

复制代码

方式二：进行日志信息手工清理

1. mysql> help purge binary logs;
2. -- 获取清理日志命令帮助信息
4. mysql> purge binary logs to 'mysql-bin.010'
5. -- 删除到指定日志文件前结束
6. mysql> PURGE BINARY LOGS BEFORE '2025-12-02 22:46:26';
7. -- 可以基于日志时间点信息进行日志清理

复制代码

说明：在对数据库服务日志信息进行清理时，最好使用数据库服务自带的清理工具进行清理，不建议使用rm做日志清理；

日志信息远程备份：

可以实现将数据库中（特别是主库）生成的binlog日志文件，及时备份保存到专门的日志备份服务器中，并且整个备份操作都是在线的；

1. [root@cheng-01 ~]# mkdir -p /binlog_backup
2. [root@cheng-01 ~]# cd /binlog_backup/
3. [root@cheng-01 binlog_backup]# mysqlbinlog -R --host=192.168.30.101 --user=root --password=123456 --raw --stop-never binlog.000008 &
4. -- 备份过程可以放后台一直运行，但是需要注意当连接的数据库服务器停止或重启了，也会导致备份中断；
6. # 数据库服务多实例情况binlog日志备份
7. mysqlbinlog -R --host=10.0.0.51 -P 3306 --user=root --password=123456 --raw --stop-never binlog.000002 &
8. mysqlbinlog -R --host=10.0.0.51 -P 3307 --user=root --password=123456 --raw --stop-never binlog.000002 &
9. -- 需要考虑备份后日志文件名称一样的覆盖问题

复制代码

远程备份命令参数说明：
参数信息官方说明解释说明-R
--read-from-remote-serverRead binary logs from a MySQL server.读取binlog日志文件从数据库服务端-h
--hostGet the binlog from server指定binlog日志文件存储服务器地址-u
--user=nameConnect to the remote server as username指定binlog日志服务器连接用户信息-p
--password[=name]Password to connect to remote server.指定binlog日志服务器连接密码信息--rawRequires -R. Output raw binlog data instead of SQL statements, output is to log files指定binlog日志信息记录二进制信息--stop-neverWait for more data from the server instead of stopping at the end of the last log指定binlog日志信息将会一直备份记录binlog.000008代表从哪个binlog日志开始进行备份分类日志信息配置：慢日志（slow_log）

慢日志主要是用于以文本形式记录数据库服务运行过程中，执行过程较慢的语句；
利用慢日志信息生成的信息，可以在日常巡检过程中，通过日志定位SQL语句性能问题；

• 1_1 日志信息基本配置
  

1. mysql> select @@slow_query_log;
2. +-------------------------+
3. | @@slow_query_log |
4. +-------------------------+
5. |                                 0 |
6. +-------------------------+
7. 1 row in set (0.00 sec)
8. -- 此参数配置信息，表示是否激活启动慢日志记录功能，默认处于关闭状态
10. mysql> select @@slow_query_log_file;
11. +--------------------------------------------+
12. | @@slow_query_log_file                    |
13. +--------------------------------------------+
14. | /data/3306/data/wenC-01-slow.log |
15. +--------------------------------------------+
16. 1 row in set (0.00 sec)
17. -- 此参数配置信息，表示慢日志文件保存的路径信息；建议日志文件路径与数据存放路径进行分离；
19. mysql> select @@long_query_time;
20. +---------------------------+
21. | @@long_query_time |
22. +---------------------------+
23. |                    10.000000 |
24. +---------------------------+
25. 1 row in set (0.00 sec)
26. -- 此参数信息配置，表示记录慢日志的条件，默认是大于10s执行的语句，就会记录为慢查询语句；(建议时间为0.01~0.1)     
28. mysql> select @@log_queries_not_using_indexes;
29. +---------------------------------------------+
30. | @@log_queries_not_using_indexes |
31. +---------------------------------------------+
32. |                                                            0 |
33. +---------------------------------------------+
34. 1 row in set (0.00 sec)
35. -- 此参数信息配置，表示慢日志中会记录没有使用索引的语句信息；
37. # 修改日志默认状态（激活日志）：
38. mysql> set global slow_query_log=1;
39. mysql> set global long_query_time=0.01;
40. mysql> set global log_queries_not_using_indexes=1;
41. -- 可以对以上参数信息进行在线调整，也可以将以上参数编写到数据库my.cnf配置文件中，作为永久配置；

复制代码

• 1_2 日志应用配置核实
  

1. mysql> use cheng;
2. mysql> show index from t100w;
3. mysql> alter table t100w drop index idx;
4. -- 删除数据表中索引信息
5. mysql> select * from t100w limit 100;
6. mysql> select * from t100w where id=10;
7. mysql> select * from t100w where id=20;
8. mysql> select count(*) from t100w group by num limit 10;
9. ...
10. -- 模拟执行慢查询的操作语句
12. # 查看核实慢日志文件是否生成
13. [root@cheng-01 ~]# ll /data/3306/data/wenC-01-slow.log
14. -rw-r----- 1 mysql mysql 6842 11月 22 23:54 /data/3306/data/wenC-01-slow.log
15. [root@cheng-01 ~]# cat /data/3306/data/wenC-01-slow.log
16. /usr/local/mysql/bin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
17. Tcp port: 3306  Unix socket: /tmp/mysql.sock
18. Time                 Id Command    Argument
19. # Time: 2022-11-22T15:41:03.849261Z
20. # User@Host: root[root] @ localhost []  Id:   490
21. # Query_time: 0.000446  Lock_time: 0.000143 Rows_sent: 100  Rows_examined: 100
22. use cheng;
23. SET timestamp=1669131663;
24. select * from t100w limit 100;
25. # Time: 2022-11-22T15:41:05.677310Z
26. # User@Host: root[root] @ localhost []  Id:   490
27. # Query_time: 0.000282  Lock_time: 0.000083 Rows_sent: 100  Rows_examined: 100
28. SET timestamp=1669131665;
29. select * from t100w limit 100;
30. # Time: 2022-11-22T15:41:06.630012Z
31. # User@Host: root[root] @ localhost []  Id:   490
32. # Query_time: 0.000242  Lock_time: 0.000075 Rows_sent: 100  Rows_examined: 100
33. SET timestamp=1669131666;
34. select * from t100w limit 100;
35. -- 会按照执行语句的操作时间顺序，进行慢查询日志信息的记录；

复制代码

• 1_3 日志信息分析方法
  

1. [root@cheng-01 data]# mysqldumpslow -s c -t 3 /data/3306/data/wenC-01-slow.log
2. -- 按照慢查询语句的重复执行次数(c)进行排序(-s)，取出其中靠前(t)的前三名慢查询语句
3. -- 还可以扩展使用pt-query-digest更好的分析慢查询日志，支持图形化展示
4. -- what to sort by (al, at, ar, c, l, r, t), 'at' is default
5.     al: average lock time
6.     ar: average rows sent
7.     at: average query time
8.     c: count
9.     l: lock time
10.     r: rows sent
11.     t: query time  

复制代码

数据库服务备份恢复

数据库服务备份恢复目的

在企业环境中，无论是安全人员、运维人员、开发人员、数据库管理人员等所有技术人员都有一个共同的职责：
保障数据安全，防止数据库损坏

• 数据库物理损坏：磁盘、文件系统、数据文件（可以利用主从、高可用、备份+日志恢复数据）
  
• 数据库逻辑损坏：drop、truncate、delete、update（可以利用备份+日志、延时从库）
  

其中对于数据库服务来说，保障数据库服务的数据安全需要考量两个重要的指标：

• 一定要保障数据不能丢失和泄露；
  
• 一定要保障数据存储服务的稳定；（业务7*24）
  

说明：为了保障数据信息不丢失，最好的处理方案就是做备份，甚至是做多副本备份，多区域备份；就算丢失损坏也能快速复原。

数据库服务备份恢复方式

1 数据库服务备份数据方式：
在企业中实现数据库服务数据备份的方式主要有两种方式：
① 物理方式
采用拷贝物理文件数据进行备份的方式，数据库服务物理数据文件存放路径是：/var/lib/mysql
实现方式：

• 可以在某个特定时间点停机或停止业务访问，然后利用cp和tar命令将物理数据文件备份或打包；
  
• 可以在任意时间节点在不停机不停止业务时，然后利用专业的xtrabackup(Percona Xtrabackup)热备工具进行数据库数据备份；
  

应用场景：
当企业数据库服务产生的需要备份的数据量在50G以上，可以选择物理备份（xtrabackup）；
② 逻辑方式
可以采用以SQL语句形式把数据库的数据导出保存备份为数据库文件（xxx.sql），文件中会含有大量SQL语句信息；
实现方式：

• 可以在任意时间节点在不停机不停止业务时，然后利用专业的mysqldump(MDP)逻辑备份工具进行数据备份；
  
• 可以在任意时间节点在不停机不停止业务时，然后利用二进制日志binlog文件实现逻辑备份数据操作；
  
• 可以在任意时间节点在不停机不停止业务时，然后利用主从数据库架构实现备份数据信息；
  

应用场景：
当企业数据库服务产生的需要备份的数据量在50G以内，可以选择逻辑备份（mysqldump）；
2 数据库服务备份恢复职责：

• 设计数据库备份策略：备份数据周期、选择的备份工具、应用的备份方式（全备 增量..）；
  
• 定期数据库备份检查：核实是否存在、确认备份文件大小；
  
• 安排数据库恢复演练：真实确认备份的数据，是否能够准确的做数据恢复；
  
• 真实数据库恢复能力：在数据库服务出现异常情况时，可以将数据库服务修复，并恢复丢失的数据信息；
  
• 关于数据库迁移升级：可以采用Mergeing方式（主从架构）、可以单独备份数据信息到新的数据库节点做恢复（逻辑导出）；
  

数据库服务逻辑备份实践

在进行数据库数据逻辑备份操作过程中，主要会运用mysqldump逻辑备份工具，可以实现本地或远程的数据备份；
利用mysqldump进行逻辑备份数据时，主要的备份逻辑是将建库、建表、数据插入语句信息导出，实现数据的备份操作；
基于mysqldump备份数据的逻辑原理，对于数据量比较小的场景（单表数据行百万以内），mysqldump备份工具做备份会更适合些；
在跨平台或跨版本进行数据库数据信息迁移时，mysqldump备份工具做备份也会比较适合，可以避免物理备份的兼容性问题；

说明：在一般情况下，对数据库进行数据恢复的时间耗费，大约是数据库进行数据备份的时间耗费的3~5倍。

工具命令使用语法：

1. [root@cheng ~]# mysqldump -u数据库用户 -p数据库密码 [备份参数] > /路径信息/数据库备份文件.sql
2. -- 在执行mysqldump命令时，也会用到数据库连接登录的基础参数：-u -p -S -h -P

复制代码

工具命令常用参数：
序号参数信息官方说明解释说明01-ADump all the databases表示备份所有库中数据信息02-BDump several databases.表示备份指定库中数据信息03-FFlush logs file in server before starting dump表示在备份启动前自动刷新日志文件工具命令实践操作：
数据库备份恢复练习环境准备：

1. [root@cheng ~]# mkdir -p /database_backup

复制代码

1 数据库全库备份操作练习实践命令：

• 将数据库中所有数据库全部备份（-A）
  

1. [root@cheng ~]# mysqldump -uroot -p123456 -A >/database_backup/all_database.sql
2. [root@cheng ~]# ll -h /database_backup/all_database.sql
3. -rw-r--r--. 1 root root 744K 6月  23 23:13 /database_backup/all_database.sql
4. -- 利用mysqldump命令备份的数据文件是纯文本文件，是可以进行查看或过滤的；***

复制代码

说明：利用-A创建数据库备份数据时，在备份数据中会含有 create建库语句和use切换库语句，可以直接进行恢复操作即可；

2 数据库部分备份操作练习实践命令：

• 将数据库中单个数据库进行备份（-B）
  

1. # 进行数据库单库备份操作
2. [root@cheng ~]# mysqldump -uroot -p123456 -B cheng >/database_backup/cheng.sql
3. [root@cheng ~]# ll -h /databases_backup/cheng.sql
4. -rw-r--r--. 1 root root 2.0K 6月  23 23:21 /database_backup/cheng.sql
6. # 过滤部分内容后查看备份数据库文件信息：
7. [root@cheng ~]# egrep -vi '^-|^/\*|^$|lock' /databases_backup/cheng.sql
8. CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cheng` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
9. USE `cheng`;
10. DROP TABLE IF EXISTS `stu2`;
11. CREATE TABLE `stu2` (
12.   `id` int(10) NOT NULL,
13.   `name` varchar(20) NOT NULL,
14.   `age` tinyint(2) NOT NULL DEFAULT '0',
15.   `dept` varchar(16) DEFAULT NULL
16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
17. INSERT INTO `stu2` VALUES (1,'cheng',35,'net sec'),(2,'oldgirl',25,'linux');

复制代码

以上指定数据库备份完毕后，可以模拟删除相应数据，利用备份的数据库文件进行数据库恢复操作：

1. mysql> use cheng
2. mysql> show tables;
3. +------------------------+
4. | Tables_in_cheng   |
5. +------------------------+
6. | stu2                          |
7. +------------------------+
8. 1 row in set (0.00 sec)
10. mysql> drop table stu2;
11. mysql> show tables;
12. Empty set (0.00 sec)
13. -- 模拟删除数据库中数据表信息，造成数据库中数据损坏
15. # 进行数据库数据复原恢复操作：
16. # 方式一：在数据库系统中加载数据库备份文件
17. mysql> source /database_backup/cheng.sql;
19. # 方式二：在操作系统命令行执行数据恢复命令
20. [root@cheng ~]# mysql -uroot -p123456 cheng </database_backup/cheng.sql
22. # 数据信息恢复完毕后检查数据库情况
23. mysql> show tables;
24. +------------------------+
25. | Tables_in_cheng   |
26. +------------------------+
27. | stu2                          |
28. +------------------------+
29. 1 row in set (0.00 sec)
31. mysql> select * from stu2;
32. +----+---------+-----+-----------+
33. | id | name    | age  | dept      |
34. +----+---------+-----+-----------+
35. |  1 | cheng  |  35   | net sec |
36. |  2 | oldgirl  |  25   | linux     |
37. +----+---------+-----+-----------+
38. 2 rows in set (0.00 sec)

复制代码

• 将数据库中多个数据库进行备份（-B）
  

1. [root@cheng ~]# mysqldump -uroot -p123456 -B cheng world >/database_backup/cheng_world.sql
3. # 过滤部分内容后查看备份数据库文件信息：
4. [root@cheng ~]# egrep -vi '^-|^/\*|^$|lock' /database_backup/cheng_world.sql

复制代码

说明：利用-B创建数据库备份数据时，在备份数据中会含有 create建库语句和use切换库语句，可以直接进行恢复操作即可；

3 数据表部分备份操作练习实践命令：

• 将数据库中单个数据表进行备份
  

[code]# 备份指定数据库中的单个数据表：[root@cheng ~]# mysqldump -uroot -pcheng123 cheng stu1  >/databases_backup/cheng_tables_stu1.sql# 恢复指定数据库中的单个数据表：[root@cheng ~]# mysql -uroot -pcheng123  cheng /database_backup/world_tables_city_country.sql# 恢复指定数据库中的多个数据表：[root@cheng ~]# mysql -uroot -pcheng123  world