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 [email protected] 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','[email protected]'); mysql> select * from users; +----+------+-------------+ | id | name | email | +----+------+-------------+ | 1 | wing | [email protected] | +----+------+-------------+ 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 标签：log,16,mysql,09,查询,session,2023.6,sql,root From： https://www.cnblogs.com/BXXY5961/p/17486652.html