2023.6.16 09.数据库⽇志管理

09.数据库⽇志管理

1.错误⽇志

2.查询⽇志

3.慢查询⽇志

4.⼆进制⽇志

0. ⽇志作⽤

 

1. 排查故障

2. 性能优化

3. 安全审计

4. 统计分析

5. 数据备份与恢复

 

1. mysql⽇志管理

 

 

2.错误⽇志

MySQL的错误⽇志 error log 记录 mysqld 服务进程启动/关闭或运⾏过遇到的错误⽇志。
1.在配置⽂件增加如下⾏

 

2.创建对应⽬录与⽇志⽂件, 并授权

[root@sql ~]# mkdir -p /log/mysql/

[root@sql ~]# touch /log/mysql/err.log

[root@sql ~]# chown -R mysql.mysql /log/mysql/

//重启数据库⽣效

[root@sql ~]# systemctl restart mysqld

 

3.查看错误⽇志

[root@sql ~]# egrep -i "error" /log/mysql/err.log

2023-05-26T02:02:32.463347Z 0 [ERROR] unknown option '--dasdasdsadas'

2023-05-26T02:02:32.463362Z 0 [ERROR] Aborting

 

4.通过终端查看⽇志

mysql> show variables like "log_error%";

+---------------------+--------------------+

| Variable_name | Value |

+---------------------+--------------------+

| log_error | /log/mysql/err.log |

| log_error_verbosity | 3 |

+---------------------+--------------------+

2 rows in set (0.01 sec)

 

3.查询⽇志

普通查询⽇志 general query log 记录客户端连接信息和执⾏的所有 SQL 语句信息, 默认关闭

1.开启 general ⽇志

[root@sql ~]# cat /etc/my.cnf

[mysqld]

general_log=ON

general_log_file=/log/mysql/select.log

2.创建对应⽬录与⽇志⽂件, 并授权

[root@sql ~]# mkdir -p /log/mysql

[root@sql ~]# touch /log/mysql/select.log

[root@sql ~]# chown mysql.mysql /log/mysql/select.log

//重启数据库⽣效

[root@sql ~]# systemctl restart mysqld

3.查看⽇志

[root@sql mysql]# cat select.log

/soft/mysql/bin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started

with:

Tcp port: 0 Unix socket: /tmp/mysql.sock

Time Id Command Argument

2023-05-26T02:04:05.071650Z 2 Connect root@mysql.server on using SSL/TLS

2023-05-26T02:04:05.072034Z 2 Query select @@version_comment limit 1

2023-05-26T02:04:13.981535Z 2 Query show databases

2023-05-26T02:04:32.152472Z 2 Query drop database test

2023-05-26T02:04:32.154171Z 2 Query SELECT DATABASE()

 

4.终端查询⽇志情况

mysql> show variables like 'general_log%';

+------------------+-----------------------+

| Variable_name | Value |

+------------------+-----------------------+

| general_log | ON |

| general_log_file | /log/mysql/select.log |

+------------------+-----------------------+

2 rows in set (0.01 sec)

//临时开启查询⽇志

mysql> set global general_log = On;

//临时关闭查询⽇志

mysql> set global general_log = Off;

 

4.慢查询⽇志

慢查询⽇志 slow query log 记录执⾏过⻓的 SQL 语句, 可利⽤慢查询优化进⾏调整优化

1.开启慢查询⽇志

[root@sql ~]# cat /etc/my.cnf

[mysqld]

slow_query_log = on

slow_query_log_file=/log/mysql/slow.log

long_query_time=2

log_queries_not_using_indexes #没有启⽤索引的查询语句

 

开启了慢查询⽇志

⽇志⽂件存储在`/log/mysql/slow.log``

`long_query_time 参数设置为2秒,如果⼀个查询的执⾏时间超过2秒，它将被记录在慢查询⽇志中。

对于优化数据库性能⾮常有⽤，它可以帮助找出哪些查询需要优化。

 

注意 : 开启慢查询⽇志会对MySQL服务器的性能产⽣⼀定的影响，因此在⽣产环境中，应该谨慎地使⽤它。

2.创建对应⽬录与⽇志⽂件, 并授权

[root@sql ~]# mkdir -p /log/mysql

[root@sql ~]# touch /log/mysql/slow.log

[root@sql ~]# chown mysql.mysql /log/mysql/slow.log

//重启数据库⽣效

[root@sql ~]# systemctl restart mysqld

 

3.执⾏过慢语句进⾏测试

mysql> SELECT BENCHMARK(150000000,2*3);

MySQL中的BENCHMARK函数⽤于多次执⾏语句以测量系统性能。在这种情况下，被执⾏的语句是⼀个简单的算

术运算2*3。BENCHMARK的第⼀个参数指定了语句应该执⾏的次数。

因此，查询"SELECT BENCHMARK(150000000,2*3);"将执⾏语句"2*3" 1.5亿次，并返回重复执⾏此语句所需的总

时间。

这个查询的结果将是⼀个包含单个列的单⾏，该列包含重复执⾏语句所需的总时间。确切的结果将取决于执⾏查询

的系统的性能。

 

4.检查慢⽇志是否记录

[root@sql ~]# cat/log/mysql/slow.log

# Time: 2023-05-27T02:27:02.843913Z

# User@Host: root[root] @ mysql.server [10.1.106.70] Id: 3

# Query_time: 2.061564 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1685154422;

SELECT BENCHMARK(150000000,2*3);

 

5.⼆进制⽇志

⼆进制⽇志 binary log 记录数据被修改的相关信息

1.开启 binlog ⼆进制⽇志

[root@sql ~]# cat /etc/my.cnf

[mysqld]

server-id = 70

log-bin = /log/mysql/bin/sql_106_70

expire_logs_days = 30 #多少天天数后的⽇志将会被⾃动删除

 

server-id : MySQL服务器的唯⼀标识符，它⽤于在主从复制中识别服务器。每个服务器必须拥有⼀个唯⼀

的 server-id 值。在这个例⼦中， server-id 被设置为70(根据服务器ip做唯⼀标识)

 

log-bin : 启⽤⼆进制⽇志记录（binary logging），记录了所有对MySQL数据库的修改操作，包括插⼊、

更新和删除等操作。这些⽇志可以⽤于恢复数据、备份数据、以及实现主从复制等功

能。 /log/mysql/bin/sql_106_70 指定了⼆进制⽇志⽂件的存储位置和⽂件名前缀，MySQL会⾃动在⽂件

名后⾯添加⼀些编号和扩展名。

expire_logs_days : 这个参数指定了⼆进制⽇志⽂件的过期时间，即MySQL会⾃动删除多久之前的⽇志⽂

件。在这个例⼦中，⽇志⽂件会在30天后被⾃动删除。

 

2.创建对应⽬录与⽇志⽂件, 并授权

[root@vm-70-161 ~]# mkdir /log/mysql/bin

[root@vm-70-161 ~]# chown -R mysql.mysql /log/mysql/

[root@vm-70-161 ~]# systemctl restart mysqld

//重启数据库⽣效

[root@sql ~]# systemctl restart mysqld

 

3.查看 binlog ⽇志信息

[root@sql ~]# mysqlbinlog -v /log/mysql/bin/sql_106_70.000001

# at 219

#230527 12:09:02 server id 70 end_log_pos 306 CRC32 0xc85c6c41 Query

thread_id=2 exec_time=0 error_code=0

use `wing`/*!*/;

SET TIMESTAMP=1685160542/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,

@@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET

@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation

_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

TRUNCATE TABLE users

/*!*/;s

 

# at 219 ：表示该查询在⼆进制⽇志⽂件中的位置

#230527 12:09:02 ：表示该查询执⾏的时间，⽇期格式为YYMMDD HH:MM:SS

server id 70 ：表示MySQL服务器的ID

end_log_pos 306 ：表示该查询在⼆进制⽇志⽂件中的结束位置

CRC32 0xc85c6c41 ：表示该查询的CRC32校验和

Query ：表示该⽇志记录是⼀个查询

 

thread_id=2 ：表示执⾏该查询的线程ID

exec_time=0 ：表示该查询的执⾏时间为0毫秒

error_code=0 ：表示该查询没有出错

use wing /!/; ：表示该查询使⽤了 wing`数据库。

SET TIMESTAMP=1685160542/*!*/; ：设置查询的时间戳。

SET @@session.pseudo_thread_id=2/*!*/; ：设置当前会话的虚拟线程ID。

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,

@@session.unique_checks=1, @@session.autocommit=1/*!*/; ：设置当前会话的⼀些参数，如外键检

查、⾃动提交等。

SET @@session.sql_mode=1436549152/*!*/; ：设置当前会话的SQL模式。

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; ：设

置当前会话的⾃增计数器起始值和步⻓。

/*!\C utf8 *//*!*/; ：设置当前会话的字符集为UTF-8。

SET

@@session.character_set_client=33,@@session.collation_connection=33,@@session.collatio

n_server=8/*!*/; ：设置当前会话的字符集和排序规则。

SET @@session.lc_time_names=0/*!*/; ：设置当前会话的时间格式。

SET @@session.collation_database=DEFAULT/*!*/; ：设置当前会话的数据库排序规则。

TRUNCATE TABLE users/*!*/; ：表示执⾏了⼀个 TRUNCATE TABLE 查询，将 users 表清空。

 

4.⽇志截断

1.切割binlog⽇志

重启 mysqld 服务, 在 mysql 控制台执⾏ mysql> flush logs

在 mysql 控制台执⾏ reset master 会清空所有 binlog (危险)

2.删除部分binlog⽇志

mysql> purge master logs to 'sql_106_70.000001'; #删除指定⽇志以前的⽇志⽂件

mysql> purge binary logs before '2023-05-27 12:44:00'; #删除指定时间以前的⽇志⽂件

5.恢复⽇志

use wing;

CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,

`email` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 ;

TRUNCATE TABLE users;

insert into users(name,email) values('wing','wing@qq.com');

mysql> select * from users;

+----+------+-------------+

| id | name | email |

+----+------+-------------+

| 1 | wing | wing@qq.com |

+----+------+-------------+

1 row in set (0.01 sec)

 

使⽤时间节点进⾏恢复

# mysqlbinlog sql_106_70.000006 --start-datetime='2023-05-28 15:41:00'

# mysqlbinlog sql_106_70.000005 --stop-datetime='2023-05-28 15:40:00'

# mysqlbinlog sql_106_70.000005 --start-datetime='2023-05-28 12:00:00' --stop

datetime='2023-05-28 15:40:00'

 

使⽤位置点进⾏恢复

# mysqlbinlog sql_106_70.000006 --start-position=219

# mysqlbinlog sql_106_70.000006 --stop-position=503

# mysqlbinlog sql_106_70.000006 --start-position=219 --stop-position=503