MySQL的日志查询

查看日志路径

mysql> show variables like 'log_%';
+----------------------------------------+----------------------------------------+
| Variable_name                          | Value                                  |
+----------------------------------------+----------------------------------------+
| log_bin                                | ON                                     |
| log_bin_basename                       | /var/lib/mysql/binlog                  |
| log_bin_index                          | /var/lib/mysql/binlog.index            |
| log_bin_trust_function_creators        | OFF                                    |
| log_bin_use_v1_row_events              | OFF                                    |
| log_error                              | /var/log/mysql/mysqld.log              |
| log_error_services                     | log_filter_internal; log_sink_internal |
| log_error_suppression_list             |                                        |
| log_error_verbosity                    | 2                                      |
| log_output                             | FILE                                   |
| log_queries_not_using_indexes          | OFF                                    |
| log_slave_updates                      | ON                                     |
| log_slow_admin_statements              | OFF                                    |
| log_slow_extra                         | OFF                                    |
| log_slow_slave_statements              | OFF                                    |
| log_statements_unsafe_for_binlog       | ON                                     |
| log_throttle_queries_not_using_indexes | 0                                      |
| log_timestamps                         | UTC                                    |
+----------------------------------------+----------------------------------------+

查看所有日志文件

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000002 |    373719 | No        |
| binlog.000003 |      1520 | No        |
| binlog.000004 |   3372003 | No        |
| binlog.000005 |   1366184 | No        |
+---------------+-----------+-----------+
4 rows in set (0.00 sec)

查看最新日志文件

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 |  1366184 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysqlbinlog和grep妙用

mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000004 | grep -i -A 50 -B 50 xxx | grep -i -A 20 -B 20 DELETE | grep -i -A 10 -B 10 "=253"

mysqlbinlog

• --base64-output=DECODE-ROWS：对日志中sql进行解码；
• -v: 显示详情。

grep

• -i:忽略大小写；
• -A:匹配上的数据，后面多少行需要显示；
• -B:匹配上的数据，前面多少行需要显示；

参考

• 怎么查看mysql 的binlog日志存放的位置
• 通过 mysqlbinlog 和 grep 命令定位binlog文件中指定操作