前面我们聊到了mariadb的事务，以及事务隔离级别，回顾请参考https://www.cnblogs.com/qiuhom-1874/p/13198186.html；今天我们来聊一聊mariadb的日志相关话题；mariadb日志有6种，分别是查询日志（general_log）,慢查询日志（log_slow_queries）,错误日志（log_error,log_warnings）,二进制日志（binlog）,中继日志（relay_log）和事务日志（innodb_log）;
1、查询日志，主要记录查询语句，日志存储位置可放在表中，也可以放在文件中，这个要根据自己的配置，当然也可以同时放在表和文件中；一般情况服务器IO压力不大的情况下是可以开启查询日志的，如果服务器IO压力大，建议不要开启查询日志；具体配置方法如下
把查询日志放在mysql库的general_log 表中的配置方法：
在/etc/my.cnf.d/server.cnf中的server配置段下添加如下配置，并重启mariadb服务即可
提示：以上配置表示开启查询日志，日志输出到表；默认会把查询日志存放在mysql库中的general_log表中；
重启服务，然后查看general_log表是否有数据？
[root@lxc my.cnf.d]# systemctl restart mariadb [root@lxc my.cnf.d]# mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 3 Server version: 10.5.4-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> select * from mysql.general_log ; +----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------+ | 2020-06-28 09:14:33.402211 | [root] @ localhost [] | 3 | 3 | Connect | root@localhost on using Socket | | 2020-06-28 09:14:33.409731 | root[root] @ localhost [] | 3 | 3 | Query | select @@version_comment limit 1 | | 2020-06-28 09:14:38.087307 | root[root] @ localhost [] | 3 | 3 | Query | SELECT DATABASE() | | 2020-06-28 09:14:38.087952 | root[root] @ localhost [] | 3 | 3 | Init DB | mysql | | 2020-06-28 09:14:38.091356 | root[root] @ localhost [] | 3 | 3 | Query | show databases | | 2020-06-28 09:14:38.092713 | root[root] @ localhost [] | 3 | 3 | Query | show tables | | 2020-06-28 09:14:38.094222 | root[root] @ localhost [] | 3 | 3 | Field List | column_stats | | 2020-06-28 09:14:38.095628 | root[root] @ localhost [] | 3 | 3 | Field List | columns_priv | | 2020-06-28 09:14:38.096401 | root[root] @ localhost [] | 3 | 3 | Field List | db | | 2020-06-28 09:14:38.097869 | root[root] @ localhost [] | 3 | 3 | Field List | event | | 2020-06-28 09:14:38.099603 | root[root] @ localhost [] | 3 | 3 | Field List | func | | 2020-06-28 09:14:38.100382 | root[root] @ localhost [] | 3 | 3 | Field List | general_log | | 2020-06-28 09:14:38.101266 | root[root] @ localhost [] | 3 | 3 | Field List | global_priv | | 2020-06-28 09:14:38.101867 | root[root] @ localhost [] | 3 | 3 | Field List | gtid_slave_pos | | 2020-06-28 09:14:38.102563 | root[root] @ localhost [] | 3 | 3 | Field List | help_category | | 2020-06-28 09:14:38.103556 | root[root] @ localhost [] | 3 | 3 | Field List | help_keyword | | 2020-06-28 09:14:38.104430 | root[root] @ localhost [] | 3 | 3 | Field List | help_relation | | 2020-06-28 09:14:38.105328 | root[root] @ localhost [] | 3 | 3 | Field List | help_topic | | 2020-06-28 09:14:38.106362 | root[root] @ localhost [] | 3 | 3 | Field List | index_stats | | 2020-06-28 09:14:38.107459 | root[root] @ localhost [] | 3 | 3 | Field List | innodb_index_stats | | 2020-06-28 09:14:38.109085 | root[root] @ localhost [] | 3 | 3 | Field List | innodb_table_stats | | 2020-06-28 09:14:38.110367 | root[root] @ localhost [] | 3 | 3 | Field List | plugin | | 2020-06-28 09:14:38.111098 | root[root] @ localhost [] | 3 | 3 | Field List | proc | | 2020-06-28 09:14:38.112958 | root[root] @ localhost [] | 3 | 3 | Field List | procs_priv | | 2020-06-28 09:14:38.113798 | root[root] @ localhost [] | 3 | 3 | Field List | proxies_priv | | 2020-06-28 09:14:38.114734 | root[root] @ localhost [] | 3 | 3 | Field List | roles_mapping | | 2020-06-28 09:14:38.115476 | root[root] @ localhost [] | 3 | 3 | Field List | servers | | 2020-06-28 09:14:38.116419 | root[root] @ localhost [] | 3 | 3 | Field List | slow_log | | 2020-06-28 09:14:38.118138 | root[root] @ localhost [] | 3 | 3 | Field List | table_stats | | 2020-06-28 09:14:38.119065 | root[root] @ localhost [] | 3 | 3 | Field List | tables_priv | | 2020-06-28 09:14:38.120027 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone | | 2020-06-28 09:14:38.120907 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone_leap_second | | 2020-06-28 09:14:38.121914 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone_name | | 2020-06-28 09:14:38.122718 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone_transition | | 2020-06-28 09:14:38.123713 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone_transition_type | | 2020-06-28 09:14:38.124958 | root[root] @ localhost [] | 3 | 3 | Field List | transaction_registry | | 2020-06-28 09:14:38.126722 | root[root] @ localhost [] | 3 | 3 | Field List | user | | 2020-06-28 09:14:48.615477 | root[root] @ localhost [] | 3 | 3 | Query | select * from mysql.general_log | +----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------+ 38 rows in set (0.002 sec) MariaDB [mysql]>
提示：可以看到重启服务后，general_log表中就有数据了，此时查询日志记录到表中就配置好了；通常不建议开启查询日志，这个很消耗服务器性能；
配置查询日志记录到文件
提示：以上配置表示明确开启查询日志，并把日志记录到/var/lib/mysql/general_log中；
重启服务，看看对应目录下是否生成日志文件，连接到数据，执行查询操作，看看是否把日志记录到相应文件中哦？
[root@lxc my.cnf.d]# systemctl restart mariadb
[root@lxc my.cnf.d]# ll /var/lib/mysql/general_log
-rw-rw---- 1 mysql mysql 143 Jun 28 09:22 /var/lib/mysql/general_log
[root@lxc my.cnf.d]# cat /var/lib/mysql/general_log
/usr/sbin/mariadbd, Version: 10.5.4-MariaDB-log (MariaDB Server). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
[root@lxc my.cnf.d]# mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 3
Server version: 10.5.4-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| first_db |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.001 sec)
MariaDB [(none)]> q
Bye
[root@lxc my.cnf.d]# cat /var/lib/mysql/general_log
/usr/sbin/mariadbd, Version: 10.5.4-MariaDB-log (MariaDB Server). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
200628 9:22:32 3 Connect root@localhost on using Socket
3 Query select @@version_comment limit 1
200628 9:22:37 3 Query show databases
200628 9:22:38 3 Quit
[root@lxc my.cnf.d]#
提示：可以看到我们在数据库中执行了一个show databases; 在对应日志文件中是能够记录对应语句的；
配置查询日志记录同时记录到表和文件中
提示：以上配置表示开启查询日志功能，并把日志同时记录到表和文件中，文件路径为/var/lib/mysq/general_log；
重启mariadb，执行查询操作，看看对应表和文件中是否有记录？
[root@lxc my.cnf.d]# systemctl restart mariadb
[root@lxc my.cnf.d]# mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 3
Server version: 10.5.4-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> select * from mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
| 2020-06-28 09:14:33.402211 | [root] @ localhost [] | 3 | 3 | Connect | root@localhost on using Socket |
| 2020-06-28 09:14:33.409731 | root[root] @ localhost [] | 3 | 3 | Query | select @@version_comment limit 1 |
| 2020-06-28 09:14:38.087307 | root[root] @ localhost [] | 3 | 3 | Query | SELECT DATABASE() |
| 2020-06-28 09:14:38.087952 | root[root] @ localhost [] | 3 | 3 | Init DB | mysql |
| 2020-06-28 09:14:38.091356 | root[root] @ localhost [] | 3 | 3 | Query | show databases |
| 2020-06-28 09:14:38.092713 | root[root] @ localhost [] | 3 | 3 | Query | show tables |
| 2020-06-28 09:14:38.094222 | root[root] @ localhost [] | 3 | 3 | Field List | column_stats |
| 2020-06-28 09:14:38.095628 | root[root] @ localhost [] | 3 | 3 | Field List | columns_priv |
| 2020-06-28 09:14:38.096401 | root[root] @ localhost [] | 3 | 3 | Field List | db |
| 2020-06-28 09:14:38.097869 | root[root] @ localhost [] | 3 | 3 | Field List | event |
| 2020-06-28 09:14:38.099603 | root[root] @ localhost [] | 3 | 3 | Field List | func |
| 2020-06-28 09:14:38.100382 | root[root] @ localhost [] | 3 | 3 | Field List | general_log |
| 2020-06-28 09:14:38.101266 | root[root] @ localhost [] | 3 | 3 | Field List | global_priv |
| 2020-06-28 09:14:38.101867 | root[root] @ localhost [] | 3 | 3 | Field List | gtid_slave_pos |
| 2020-06-28 09:14:38.102563 | root[root] @ localhost [] | 3 | 3 | Field List | help_category |
| 2020-06-28 09:14:38.103556 | root[root] @ localhost [] | 3 | 3 | Field List | help_keyword |
| 2020-06-28 09:14:38.104430 | root[root] @ localhost [] | 3 | 3 | Field List | help_relation |
| 2020-06-28 09:14:38.105328 | root[root] @ localhost [] | 3 | 3 | Field List | help_topic |
| 2020-06-28 09:14:38.106362 | root[root] @ localhost [] | 3 | 3 | Field List | index_stats |
| 2020-06-28 09:14:38.107459 | root[root] @ localhost [] | 3 | 3 | Field List | innodb_index_stats |
| 2020-06-28 09:14:38.109085 | root[root] @ localhost [] | 3 | 3 | Field List | innodb_table_stats |
| 2020-06-28 09:14:38.110367 | root[root] @ localhost [] | 3 | 3 | Field List | plugin |
| 2020-06-28 09:14:38.111098 | root[root] @ localhost [] | 3 | 3 | Field List | proc |
| 2020-06-28 09:14:38.112958 | root[root] @ localhost [] | 3 | 3 | Field List | procs_priv |
| 2020-06-28 09:14:38.113798 | root[root] @ localhost [] | 3 | 3 | Field List | proxies_priv |
| 2020-06-28 09:14:38.114734 | root[root] @ localhost [] | 3 | 3 | Field List | roles_mapping |
| 2020-06-28 09:14:38.115476 | root[root] @ localhost [] | 3 | 3 | Field List | servers |
| 2020-06-28 09:14:38.116419 | root[root] @ localhost [] | 3 | 3 | Field List | slow_log |
| 2020-06-28 09:14:38.118138 | root[root] @ localhost [] | 3 | 3 | Field List | table_stats |
| 2020-06-28 09:14:38.119065 | root[root] @ localhost [] | 3 | 3 | Field List | tables_priv |
| 2020-06-28 09:14:38.120027 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone |
| 2020-06-28 09:14:38.120907 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone_leap_second |
| 2020-06-28 09:14:38.121914 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone_name |
| 2020-06-28 09:14:38.122718 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone_transition |
| 2020-06-28 09:14:38.123713 | root[root] @ localhost [] | 3 | 3 | Field List | time_zone_transition_type |
| 2020-06-28 09:14:38.124958 | root[root] @ localhost [] | 3 | 3 | Field List | transaction_registry |
| 2020-06-28 09:14:38.126722 | root[root] @ localhost [] | 3 | 3 | Field List | user |
| 2020-06-28 09:14:48.615477 | root[root] @ localhost [] | 3 | 3 | Query | select * from mysql.general_log |
| 2020-06-28 09:19:46.865108 | root[root] @ localhost [] | 3 | 3 | Quit | |
| 2020-06-28 09:28:29.542343 | [root] @ localhost [] | 3 | 3 | Connect | root@localhost on using Socket |
| 2020-06-28 09:28:29.549997 | root[root] @ localhost [] | 3 | 3 | Query | select @@version_comment limit 1 |
| 2020-06-28 09:28:44.924061 | root[root] @ localhost [] | 3 | 3 | Query | select * from mysql.general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------------+
42 rows in set (0.002 sec)
MariaDB [(none)]> q
Bye
[root@lxc my.cnf.d]# cat /var/lib/mysql/general_log
/usr/sbin/mariadbd, Version: 10.5.4-MariaDB-log (MariaDB Server). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
200628 9:22:32 3 Connect root@localhost on using Socket
3 Query select @@version_comment limit 1
200628 9:22:37 3 Query show databases
200628 9:22:38 3 Quit
/usr/sbin/mariadbd, Version: 10.5.4-MariaDB-log (MariaDB Server). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
200628 9:28:29 3 Connect root@localhost on using Socket
3 Query select @@version_comment limit 1
200628 9:28:44 3 Query select * from mysql.general_log
200628 9:28:47 3 Quit
[root@lxc my.cnf.d]#
提示：可以看到mysql.general_log表中和/var/lib/mysql/general_log文件中是可以记录我们执行的查询语句；
2、慢查询日志，这个日志对于运维来讲是比较重要的，通常我们可以利用慢查询日志来判断哪些语句执行时间超出指定时间；慢查询日志主要记录运行时间超出指定时长度查询语句；这个日志同查询日志类似，它也是可以存储在表和文件中的；具体配置方式如下
配置慢查询日志存放在表中
提示：以上配置表示开启慢查询日志，并把日志记录到表中，默认是mysql.slow_log表中；log_slow_filter用来定义过滤哪些语句不记录的；log_slow_rate_limit表示开启慢查询日志记录速率；log_slow_verbosity开启慢查询日志详细记录；long_query_time定义时长，超出我们指定的时长就会视为慢查询；配置好以上配置以后重启服务，我们就可以在mariadb中看到对应变量的值；
[root@lxc my.cnf.d]# systemctl restart mariadb; [root@lxc my.cnf.d]# mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 3 Server version: 10.5.4-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> show global variables like 'slow%'; +---------------------+--------------+ | Variable_name | Value | +---------------------+--------------+ | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | lxc-slow.log | +---------------------+--------------+ 3 rows in set (0.003 sec) MariaDB [(none)]> show global variables like 'log_slow%'; +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | log_slow_admin_statements | ON | | log_slow_disabled_statements | sp | | log_slow_filter | admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | | log_slow_rate_limit | 1 | | log_slow_slave_statements | ON | | log_slow_verbosity | innodb | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.002 sec) MariaDB [(none)]> show global variables like 'long%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 3.000000 | +-----------------+----------+ 1 row in set (0.003 sec) MariaDB [(none)]>
提示：从上面的信息可以看到我们配置的相关参数已经生效；
测试：执行select sleep(5);看看mysql.slow_log表中是否有记录？
MariaDB [(none)]> select sleep(5) ;
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.001 sec)
MariaDB [(none)]> select * from mysql.slow_logG
*************************** 1. row ***************************
start_time: 2020-06-28 10:32:19.643885
user_host: root[root] @ localhost []
query_time: 00:00:05.000700
lock_time: 00:00:00.000000
rows_sent: 1
rows_examined: 0
db:
last_insert_id: 0
insert_id: 0
server_id: 3
sql_text: select sleep(5)
thread_id: 3
rows_affected: 0
1 row in set (0.001 sec)
MariaDB [(none)]>
提示：可以看到slow_log表中已经记录了我们执行的select sleep(5)语句，执行时长为5.007秒；
配置慢查询日志记录到文件；
提示：以上配置表示把慢查询日志保存在/var/lib/mysql/slow_query_log文件中；
测试：重启mariadb，执行select sleep(5)语句，看看对应文件是否记录？
[root@lxc my.cnf.d]# systemctl restart mariadb [root@lxc my.cnf.d]# ll /var/lib/mysql/slow_query_log -rw-rw---- 1 mysql mysql 143 Jun 28 10:39 /var/lib/mysql/slow_query_log [root@lxc my.cnf.d]# mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 3 Server version: 10.5.4-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> show global variables like 'slow%'; +---------------------+-------------------------------+ | Variable_name | Value | +---------------------+-------------------------------+ | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/slow_query_log | +---------------------+-------------------------------+ 3 rows in set (0.003 sec) MariaDB [(none)]> show global variables like 'log_slow%'; +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ | log_slow_admin_statements | ON | | log_slow_disabled_statements | sp | | log_slow_filter | admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | | log_slow_rate_limit | 1 | | log_slow_slave_statements | ON | | log_slow_verbosity | innodb | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.003 sec) MariaDB [(none)]> show global variables like 'long%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 3.000000 | +-----------------+----------+ 1 row in set (0.002 sec) MariaDB [(none)]> select sleep(5); +----------+ | sleep(5) | +----------+ | 0 | +----------+ 1 row in set (5.001 sec) MariaDB [(none)]> q Bye [root@lxc my.cnf.d]# cat /var/lib/mysql/slow_query_log /usr/sbin/mariadbd, Version: 10.5.4-MariaDB-log (MariaDB Server). started with: Tcp port: 0 Unix socket: (null) Time Id Command Argument # Time: 200628 10:40:50 # User@Host: root[root] @ localhost [] # Thread_id: 3 Schema: QC_hit: No # Query_time: 5.000553 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 # Rows_affected: 0 Bytes_sent: 64 SET timestamp=1593355250; select sleep(5); [root@lxc my.cnf.d]#
提示：可以看到我们配置的参数在mariadb中已经可正常查询到，对应的文件中已经记录我们执行select sleep(5)这条语句执行了5.000553秒；
配置慢查询日志记录到表和文件中
提示：红框中的内容表示把慢查询日志同时记录到文件和表中；
测试：重启mariadb服务，执行select sleep（5）语句看看是否在表和文件中都记录了？
[root@lxc my.cnf.d]# systemctl restart mariadb
[root@lxc my.cnf.d]# mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 3
Server version: 10.5.4-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.002 sec)
MariaDB [(none)]> select * from mysql.slow_logG
*************************** 1. row ***************************
start_time: 2020-06-28 10:32:19.643885
user_host: root[root] @ localhost []
query_time: 00:00:05.000700
lock_time: 00:00:00.000000
rows_sent: 1
rows_examined: 0
db:
last_insert_id: 0
insert_id: 0
server_id: 3
sql_text: select sleep(5)
thread_id: 3
rows_affected: 0
*************************** 2. row ***************************
start_time: 2020-06-28 10:45:37.720365
user_host: root[root] @ localhost []
query_time: 00:00:05.000784
lock_time: 00:00:00.000000
rows_sent: 1
rows_examined: 0
db:
last_insert_id: 0
insert_id: 0
server_id: 3
sql_text: select sleep(5)
thread_id: 3
rows_affected: 0
2 rows in set (0.001 sec)
MariaDB [(none)]> q
Bye
[root@lxc my.cnf.d]# cat /var/lib/mysql/slow_query_log
/usr/sbin/mariadbd, Version: 10.5.4-MariaDB-log (MariaDB Server). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 200628 10:40:50
# User@Host: root[root] @ localhost []
# Thread_id: 3 Schema: QC_hit: No
# Query_time: 5.000553 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
# Rows_affected: 0 Bytes_sent: 64
SET timestamp=1593355250;
select sleep(5);
/usr/sbin/mariadbd, Version: 10.5.4-MariaDB-log (MariaDB Server). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 200628 10:45:37
# User@Host: root[root] @ localhost []
# Thread_id: 3 Schema: QC_hit: No
# Query_time: 5.000784 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
# Rows_affected: 0 Bytes_sent: 64
SET timestamp=1593355537;
select sleep(5);
[root@lxc my.cnf.d]#
提示：可以看到slow_log表和我们指定文件中都记录；
用mysqldumpslow来统计慢查询日志
[root@lxc my.cnf.d]# mysqldumpslow Can't determine datadir from 'my_print_defaults instances' output: --slow_query_log=on --log_output=file,table --slow_query_log_file=/var/lib/mysql/slow_query_log --log_slow_filter=admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk --log_slow_rate_limit=1 --log_slow_verbosity=1 --long_query_time=3 --server_id=3 --read_only --relay_log_purge=0 --skip_name_resolve=1 [root@lxc my.cnf.d]# mysqldumpslow /var/lib/mysql/slow_query_log Reading mysql slow query log from /var/lib/mysql/slow_query_log Count: 2 Time=5.00s (10s) Lock=0.00s (0s) Rows_sent=1.0 (2), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost select sleep(N) Count: 1 Time=4.02s (4s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=2.0 (2), Rows_affected=0.0 (0), root[root]@localhost select sleep(N),count(id) from first_db.test_tb Count: 3 Time=4.00s (12s) Lock=0.00s (0s) Rows_sent=1.0 (3), Rows_examined=5.0 (15), Rows_affected=0.0 (0), root[root]@localhost select sleep(N),count(start_time) from mysql.slow_log Count: 1 Time=4.00s (4s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost select sleep(N)as a, N as b [root@lxc my.cnf.d]#
提示：默认mysqldumpslow 不加任何选项和参数 它会打印配置文件内容，mysqldumpslow 后面给指定的slow日志 它会统计出那些命令执行了几次，总时长是多少等等；
使用日志分析工具mysqlsla工具分析慢查询日志
安装mysqlsla
[root@lxc my.cnf.d]# yum install perl-DBI perl-DBD-MySQL perl-devel -y
Loaded plugins: fastestmirror
base | 3.6 kB 00:00:00
docker-ce-stable | 3.5 kB 00:00:00
epel | 4.7 kB 00:00:00
extras | 2.9 kB 00:00:00
mariadb-main | 2.9 kB 00:00:00
mariadb-maxscale | 2.4 kB 00:00:00
mariadb-tools | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/3): updates/7/x86_64/primary_db | 2.9 MB 00:00:00
(2/3): epel/x86_64/updateinfo | 1.0 MB 00:00:00
(3/3): epel/x86_64/primary_db | 6.8 MB 00:00:01
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
Package perl-DBI-1.627-4.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be updated
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be an update
---> Package perl-devel.x86_64 4:5.16.3-295.el7 will be installed
……省略部分内容
Installed:
perl-devel.x86_64 4:5.16.3-295.el7
Dependency Installed:
gdbm-devel.x86_64 0:1.10-8.el7 glibc-devel.x86_64 0:2.17-307.el7.1 glibc-headers.x86_64 0:2.17-307.el7.1
kernel-headers.x86_64 0:3.10.0-1127.13.1.el7 libdb-devel.x86_64 0:5.3.21-25.el7 perl-ExtUtils-Install.noarch 0:1.58-295.el7
perl-ExtUtils-MakeMaker.noarch 0:6.68-3.el7 perl-ExtUtils-Manifest.noarch 0:1.61-244.el7 perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7
perl-Test-Harness.noarch 0:3.28-3.el7 pyparsing.noarch 0:1.5.6-9.el7 systemtap-sdt-devel.x86_64 0:4.0-11.el7
Updated:
perl-DBD-MySQL.x86_64 0:4.023-6.el7
Dependency Updated:
glibc.x86_64 0:2.17-307.el7.1 glibc-common.x86_64 0:2.17-307.el7.1 libdb.x86_64 0:5.3.21-25.el7 libdb-utils.x86_64 0:5.3.21-25.el7
Complete!
[root@lxc my.cnf.d]#cd
[root@lxc ~]#wget ftp://ftp.tw.freebsd.org/pub/distfiles/mysqlsla-2.03.tar.gz
--2020-06-28 11:07:02-- ftp://ftp.tw.freebsd.org/pub/distfiles/mysqlsla-2.03.tar.gz
=> ‘mysqlsla-2.03.tar.gz’
Resolving ftp.tw.freebsd.org (ftp.tw.freebsd.org)... 140.113.17.209
Connecting to ftp.tw.freebsd.org (ftp.tw.freebsd.org)|140.113.17.209|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done. ==> PWD ... done.
==> TYPE I ... done. ==> CWD (1) /pub/distfiles ... done.
==> SIZE mysqlsla-2.03.tar.gz ... 33674
==> PASV ... done. ==> RETR mysqlsla-2.03.tar.gz ... done.
Length: 33674 (33K) (unauthoritative)
100%[=================================================================================================================================>] 33,674 --.-K/s in 0s
2020-06-28 11:07:10 (195 MB/s) - ‘mysqlsla-2.03.tar.gz’ saved [33674]
[root@lxc ~]# ls
192.168.0.22 lxc_br_set.sh LXC-Web-Panel mysqlsla-2.03.tar.gz
[root@lxc ~]# tar xf mysqlsla-2.03.tar.gz
[root@lxc ~]# cd mysqlsla-2.03/
[root@lxc mysqlsla-2.03]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for mysqlsla
[root@lxc mysqlsla-2.03]# make
cp lib/mysqlsla.pm blib/lib/mysqlsla.pm
cp bin/mysqlsla blib/script/mysqlsla
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/mysqlsla
Manifying blib/man3/mysqlsla.3pm
[root@lxc mysqlsla-2.03]# make install
Installing /usr/local/share/perl5/mysqlsla.pm
Installing /usr/local/share/man/man3/mysqlsla.3pm
Installing /usr/local/bin/mysqlsla
Appending installation info to /usr/lib64/perl5/perllocal.pod
[root@lxc mysqlsla-2.03]#
使用mysqlsla分析慢查询日志/var/lib/mysql/slow_query_log
[root@lxc mysqlsla-2.03]# mysqlsla -lt slow /var/lib/mysql/slow_query_log
Report for msl logs: /var/lib/mysql/slow_query_log
7 queries total, 4 unique
Sorted by 't_sum'
Grand Totals: Time 30 s, Lock 0 s, Rows sent 7, Rows Examined 17
______________________________________________________________________ 001 ___
Count : 3 (42.86%)
Time : 12.003227 s total, 4.001076 s avg, 4.000803 s to 4.001615 s max (39.97%)
Lock Time (s) : 595 otal, 198 vg, 151 o 257 ax (26.81%)
Rows sent : 1 avg, 1 to 1 max (42.86%)
Rows examined : 5 avg, 4 to 6 max (88.24%)
Database : QC_hit: No
Users :
root@localhost : 100.00% (3) of query, 100.00% (7) of all users
Query abstract:
SELECT sleep(N),COUNT(start_time) FROM mysql.slow_log;
Query sample:
select sleep(4),count(start_time) from mysql.slow_log;
______________________________________________________________________ 002 ___
Count : 2 (28.57%)
Time : 10.001337 s total, 5.000668 s avg, 5.000553 s to 5.000784 s max (33.31%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
Rows sent : 1 avg, 1 to 1 max (28.57%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database : QC_hit: No
Users :
root@localhost : 100.00% (2) of query, 100.00% (7) of all users
Query abstract:
SELECT sleep(N);
Query sample:
select sleep(5);
______________________________________________________________________ 003 ___
Count : 1 (14.29%)
Time : 4.023146 s total, 4.023146 s avg, 4.023146 s to 4.023146 s max (13.40%)
Lock Time (s) : 1.624 ms total, 1.624 ms avg, 1.624 ms to 1.624 ms max (73.19%)
Rows sent : 1 avg, 1 to 1 max (14.29%)
Rows examined : 2 avg, 2 to 2 max (11.76%)
Database : QC_hit: No
Users :
root@localhost : 100.00% (1) of query, 100.00% (7) of all users
Query abstract:
SELECT sleep(N),COUNT(id) FROM first_db.test_tb;
Query sample:
select sleep(4),count(id) from first_db.test_tb;
______________________________________________________________________ 004 ___
Count : 1 (14.29%)
Time : 4.000851 s total, 4.000851 s avg, 4.000851 s to 4.000851 s max (13.32%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
Rows sent : 1 avg, 1 to 1 max (14.29%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database : QC_hit: No
Users :
root@localhost : 100.00% (1) of query, 100.00% (7) of all users
Query abstract:
SELECT sleep(N)AS a, N AS b;
Query sample:
select sleep(4)as a, 1 as b;
[root@lxc mysqlsla-2.03]#
提示：可以看到msyqlsla把慢查询日志更具体的分析了一次，每个语句执行了多少次，总时间，平均时间等等信息；
3、错误日志，该日志记录了mairadbd启动关闭过程中的输出信息，mariadbd运行中产生的错误信息，事件调度产生的信息，和主从复制架构中，从服务器复制线程启动时产生的信息；配置错误日志如下
提示：以上红框中的内容表示启动错误日志功能，并保持到/var/log/mariadb/mariadb_error.log;并开启记录警告信息到错误日志中；
重启服务看看对应文件中是否会记录mariadb启动信息？
[root@lxc my.cnf.d]# systemctl restart mariadb [root@lxc my.cnf.d]# ll /var/log/mariadb/mariadb_error.log -rw-rw---- 1 mysql mysql 2411 Jun 28 11:35 /var/log/mariadb/mariadb_error.log [root@lxc my.cnf.d]# cat /var/log/mariadb/mariadb_error.log 2020-06-28 11:35:44 0 [Note] /usr/sbin/mariadbd (initiated by: unknown): Normal shutdown 2020-06-28 11:35:44 0 [Note] Event Scheduler: Purging the queue. 0 events 2020-06-28 11:35:44 0 [Note] InnoDB: FTS optimize thread exiting. 2020-06-28 11:35:44 0 [Note] InnoDB: Starting shutdown... 2020-06-28 11:35:44 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool 2020-06-28 11:35:44 0 [Note] InnoDB: Buffer pool(s) dump completed at 200628 11:35:44 2020-06-28 11:35:45 0 [Note] InnoDB: Shutdown completed; log sequence number 91510; transaction id 181 2020-06-28 11:35:45 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2020-06-28 11:35:45 0 [Note] /usr/sbin/mariadbd: Shutdown complete 2020-06-28 11:35:45 0 [Note] InnoDB: Using Linux native AIO 2020-06-28 11:35:45 0 [Note] InnoDB: Uses event mutexes 2020-06-28 11:35:45 0 [Note] InnoDB: Compressed tables use zlib 1.2.7 2020-06-28 11:35:45 0 [Note] InnoDB: Number of pools: 1 2020-06-28 11:35:45 0 [Note] InnoDB: Using SSE4.2 crc32 instructions 2020-06-28 11:35:45 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 2020-06-28 11:35:45 0 [Note] InnoDB: Completed initialization of buffer pool 2020-06-28 11:35:45 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-06-28 11:35:45 0 [Note] InnoDB: 128 rollback segments are active. 2020-06-28 11:35:45 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-06-28 11:35:45 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-06-28 11:35:45 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-06-28 11:35:45 0 [Note] InnoDB: 10.5.4 started; log sequence number 91510; transaction id 180 2020-06-28 11:35:45 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-06-28 11:35:45 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-06-28 11:35:45 0 [Note] InnoDB: Buffer pool(s) load completed at 200628 11:35:45 2020-06-28 11:35:45 0 [Note] Server socket created on IP: '::'. 2020-06-28 11:35:45 0 [Warning] 'proxies_priv' entry '@% root@lxc' ignored in --skip-name-resolve mode. 2020-06-28 11:35:45 0 [Note] /usr/sbin/mariadbd: ready for connections. Version: '10.5.4-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server [root@lxc my.cnf.d]#
提示：可以看到我们手动指定的文件是可以正常记录mariadb启动过程中产生的日志信息和警告信息；
测试：故意把配置文件配置错误，重启服务，看看是否反映到错误日志中？
提示：红框中内容是我故意多写了一个i ，接下来我们重启服务，看看错误日中是否会反馈出来；
提示：可以看到在错误日志文件中，它告诉我们未知的变量；
4、二进制日志：用于记录引起数据改变或存在引起数据改变的潜在可能性的语句（STATEMENT）或改变后的结果（ROW），也可能是二者混合；这个日志在主从复制架构中非常重要，主要功能就是记录增删改语句，用于“重放”实现从节点和主节点数据相同的目的；配置如下
提示：以上红框中的配置表示开启二进制日志，并保持到/var/lib/mysql/下，以mysql-bin开头命名；二进制文件的最大容量是1G；sync_binlog=1表示只要有二进制文件产生就立刻同步到磁盘；
测试：重启服务，看看对应文件是否产生？
提示：可以看到/var/lib/mysql/目录下有一个mysql-bin.000001的文件产生了；
连接数据库，查看二进制文件列表
[root@lxc my.cnf.d]# mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 4 Server version: 10.5.4-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 513 | +------------------+-----------+ 1 row in set (0.001 sec) MariaDB [(none)]> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 513 | +------------------+-----------+ 1 row in set (0.000 sec) MariaDB [(none)]>
提示：以上语句都表示查看二进制日志文件列表；
查看当前正在使用的二进制日志文件
MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 513 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]>
提示：可以看到当前正在使用mysql-bin.000001这个文件，当前位置是328
查看二进制日志文件中的事件
MariaDB [first_db]> show binlog events; +------------------+-----+-------------------+-----------+-------------+-----------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------------+-----------+-------------+-----------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 3 | 256 | Server ver: 10.5.4-MariaDB-log, Binlog ver: 4 | | mysql-bin.000001 | 256 | Gtid_list | 3 | 285 | [] | | mysql-bin.000001 | 285 | Binlog_checkpoint | 3 | 328 | mysql-bin.000001 | | mysql-bin.000001 | 328 | Gtid | 3 | 370 | BEGIN GTID 0-3-1 | | mysql-bin.000001 | 370 | Query | 3 | 482 | use `first_db`; insert into test_tb values(3,"wangwu",22) | | mysql-bin.000001 | 482 | Xid | 3 | 513 | COMMIT /* xid=17 */ | +------------------+-----+-------------------+-----------+-------------+-----------------------------------------------------------+ 6 rows in set (0.001 sec) MariaDB [first_db]>
提示：以上是在数据库上用语句查看二进制日志事件；我们也可以在shell中使用mysqlbinlog命令来查看二进制文件内容；
使用msyqlbinlog命令查看二进制日志内容
[root@lxc ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200628 11:58:31 server id 3 end_log_pos 256 CRC32 0x9afc2aa7 Start: binlog v 4, server v 10.5.4-MariaDB-log created 200628 11:58:31 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' J774Xg8DAAAA/AAAAAABAAABAAQAMTAuNS40LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAnvvheEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAEEwQADQgICAoKCgGnKvya '/*!*/; # at 256 #200628 11:58:31 server id 3 end_log_pos 285 CRC32 0x516669db Gtid list [] # at 285 #200628 11:58:31 server id 3 end_log_pos 328 CRC32 0x8395a8cd Binlog checkpoint mysql-bin.000001 # at 328 #200628 12:13:13 server id 3 end_log_pos 370 CRC32 0xd9b2a8a4 GTID 0-3-1 trans /*!100101 SET @@session.skip_parallel_replication=0*//*!*/; /*!100001 SET @@session.gtid_domain_id=0*//*!*/; /*!100001 SET @@session.server_id=3*//*!*/; /*!100001 SET @@session.gtid_seq_no=1*//*!*/; BEGIN /*!*/; # at 370 #200628 12:13:13 server id 3 end_log_pos 482 CRC32 0x5737f424 Query thread_id=5 exec_time=0 error_code=0 use `first_db`/*!*/; SET TIMESTAMP=1593360793/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/; SET @@session.sql_mode=1411383296/*!*/; 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/*!*/; insert into test_tb values(3,"wangwu",22) /*!*/; # at 482 #200628 12:13:13 server id 3 end_log_pos 513 CRC32 0x43126028 Xid = 17 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@lxc ~]#
提示：可以看到我们往test_tb表中插入的数据，在二进制文件中有记录，但是没有查询语句；二进制日志文件是不会记录查询语句，它只会记录对数据有变动的语句；
用mysqlbinlog工具查看指定位置后端日志内容
[root@lxc ~]# mysqlbinlog -j 370 /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200628 11:58:31 server id 3 end_log_pos 256 CRC32 0x9afc2aa7 Start: binlog v 4, server v 10.5.4-MariaDB-log created 200628 11:58:31 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' J774Xg8DAAAA/AAAAAABAAABAAQAMTAuNS40LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAnvvheEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAEEwQADQgICAoKCgGnKvya '/*!*/; # at 370 #200628 12:13:13 server id 3 end_log_pos 482 CRC32 0x5737f424 Query thread_id=5 exec_time=0 error_code=0 use `first_db`/*!*/; SET TIMESTAMP=1593360793/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/; SET @@session.sql_mode=1411383296/*!*/; 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/*!*/; insert into test_tb values(3,"wangwu",22) /*!*/; # at 482 #200628 12:13:13 server id 3 end_log_pos 513 CRC32 0x43126028 Xid = 17 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@lxc ~]#
用mysqlbinlog查看指定起始位置的日志信息
[root@lxc ~]# mysqlbinlog --start-position=370 --stop-position=482 /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200628 11:58:31 server id 3 end_log_pos 256 CRC32 0x9afc2aa7 Start: binlog v 4, server v 10.5.4-MariaDB-log created 200628 11:58:31 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' J774Xg8DAAAA/AAAAAABAAABAAQAMTAuNS40LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAnvvheEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAEEwQADQgICAoKCgGnKvya '/*!*/; # at 370 #200628 12:13:13 server id 3 end_log_pos 482 CRC32 0x5737f424 Query thread_id=5 exec_time=0 error_code=0 use `first_db`/*!*/; SET TIMESTAMP=1593360793/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/; SET @@session.sql_mode=1411383296/*!*/; 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/*!*/; insert into test_tb values(3,"wangwu",22) /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@lxc ~]#
用mysqlbinlog查看指定开始时间以后的日志
[root@lxc ~]# mysqlbinlog --start-datetime="2020-06-28 12:39:05" /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200628 11:58:31 server id 3 end_log_pos 256 CRC32 0x9afc2aa7 Start: binlog v 4, server v 10.5.4-MariaDB-log created 200628 11:58:31 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' J774Xg8DAAAA/AAAAAABAAABAAQAMTAuNS40LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAnvvheEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAEEwQADQgICAoKCgGnKvya '/*!*/; # at 513 #200628 12:39:05 server id 3 end_log_pos 555 CRC32 0xf924553d GTID 0-3-2 trans /*!100101 SET @@session.skip_parallel_replication=0*//*!*/; /*!100001 SET @@session.gtid_domain_id=0*//*!*/; /*!100001 SET @@session.server_id=3*//*!*/; /*!100001 SET @@session.gtid_seq_no=2*//*!*/; BEGIN /*!*/; # at 555 #200628 12:39:05 server id 3 end_log_pos 668 CRC32 0x496c0f4f Query thread_id=6 exec_time=0 error_code=0 use `first_db`/*!*/; SET TIMESTAMP=1593362345/*!*/; SET @@session.pseudo_thread_id=6/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/; SET @@session.sql_mode=1411383296/*!*/; 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/*!*/; insert into test_tb values (4,"wukong",99) /*!*/; # at 668 #200628 12:39:05 server id 3 end_log_pos 699 CRC32 0xf5032d63 Xid = 27 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@lxc ~]#
用mysqlbinlog查看指定时间段的日志信息
[root@lxc ~]# mysqlbinlog --start-datetime="2020-06-28 12:13:13" --stop-datetime="2020-06-28 12:43:42" /var/lib/mysql/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200628 11:58:31 server id 3 end_log_pos 256 CRC32 0x9afc2aa7 Start: binlog v 4, server v 10.5.4-MariaDB-log created 200628 11:58:31 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' J774Xg8DAAAA/AAAAAABAAABAAQAMTAuNS40LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAnvvheEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAEEwQADQgICAoKCgGnKvya '/*!*/; # at 328 #200628 12:13:13 server id 3 end_log_pos 370 CRC32 0xd9b2a8a4 GTID 0-3-1 trans /*!100101 SET @@session.skip_parallel_replication=0*//*!*/; /*!100001 SET @@session.gtid_domain_id=0*//*!*/; /*!100001 SET @@session.server_id=3*//*!*/; /*!100001 SET @@session.gtid_seq_no=1*//*!*/; BEGIN /*!*/; # at 370 #200628 12:13:13 server id 3 end_log_pos 482 CRC32 0x5737f424 Query thread_id=5 exec_time=0 error_code=0 use `first_db`/*!*/; SET TIMESTAMP=1593360793/*!*/; SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/; SET @@session.sql_mode=1411383296/*!*/; 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/*!*/; insert into test_tb values(3,"wangwu",22) /*!*/; # at 482 #200628 12:13:13 server id 3 end_log_pos 513 CRC32 0x43126028 Xid = 17 COMMIT/*!*/; # at 513 #200628 12:39:05 server id 3 end_log_pos 555 CRC32 0xf924553d GTID 0-3-2 trans /*!100001 SET @@session.gtid_seq_no=2*//*!*/; BEGIN /*!*/; # at 555 #200628 12:39:05 server id 3 end_log_pos 668 CRC32 0x496c0f4f Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1593362345/*!*/; insert into test_tb values (4,"wukong",99) /*!*/; # at 668 #200628 12:39:05 server id 3 end_log_pos 699 CRC32 0xf5032d63 Xid = 27 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@lxc ~]#
提示：根据上面时间或者位置指定范围后，我们就可以过滤我们需要的信息来做处理；如下，过滤insert语句
[root@lxc ~]# mysqlbinlog --start-datetime="2020-06-28 12:13:13" --stop-datetime="2020-06-28 12:43:42" /var/lib/mysql/mysql-bin.000001|grep insert /*!40019 SET @@session.max_insert_delayed_threads=0*/; insert into test_tb values(3,"wangwu",22) insert into test_tb values (4,"wukong",99) [root@lxc ~]#
提示：可以看到通过过滤关键字就可以很快定位到我们日志中记录了那些语句，一眼就能清楚知道之前执行过什么语句；
5、中继日志，该日志主要是在主从复制架构中记录从主服务器的二进制日志文件同步过来的事件信息；开启中继日志配置如下
提示：以上配置表示开启中继日志并保持到/var/lib/mysql/relay_log中；
确定配置中继日志是否开启成功，方法一，搭建主从复制，开启主从复制线程，在对应目录看是否有对应文件生成，方法二，直接在数据库里查看reay_log变量的值，如果是我们配置的路基，表示开启成功，否则失败
提示：从上面的截图可以看到关于中继日志参数的配置有以上几种，max_relay_log_size表示中继日志的最大容量；relay_log表示中继日志存放路径和中继日志以那个名称开头，这个和二进制日志的配置逻辑差不多；relay_log_basename表示已那个名字作为中继日志的基名；relay_log_index表示relay_log.index文件存放地；relay_log_info_file表示relay_log.info 文件名；relay_log_purge表示是否开启修剪中继日志；relay_log_recovery表示是否开启中继日志恢复功能（是否随mariadb服务启动而创建一个新的relay_log,将sql线程的位置初始化到新的relay log，并将i/o线程初始化到sql线程位置。）relay_log_space_limit表示是否开启中继日志空间限制；sync_relay_log表示多少次事务同步一次中继日志到磁盘；sync_relay_log_info表示多少次事务同步一次relay-log.info；