MySQL日志管理

来源：互联网发布：mac忘记密码怎么关机编辑：程序博客网时间：2024/05/17 01:41

最近在配置主从服务器，对接触的一些日志文件，进行一下总结。

是否开启

首先就是确定是否启用了二进制日志

mysql> show variables like '%log_bin%';+---------------------------------+--------------------------------------------------------+| Variable_name                   | Value                                                  |+---------------------------------+--------------------------------------------------------+| log_bin                         | ON                                                     || log_bin_basename                | /usr/local/var/mysql/dingjiacaideMacBook-Pro-bin       || log_bin_index                   | /usr/local/var/mysql/dingjiacaideMacBook-Pro-bin.index || log_bin_trust_function_creators | OFF                                                    || log_bin_use_v1_row_events       | OFF                                                    || sql_log_bin                     | ON                                                     |+---------------------------------+--------------------------------------------------------+6 rows in set (0.00 sec)mysql>

上面的变量log_bin 值为on说明已经启用了，如果没有启用的话，就在my.cnf文件加入下面一行

[mysqld]log_bin

查看二进制内容

方法一：

mysql> show binlog events;//当前在用的日志+------------------------------------+-----+----------------+-----------+-------------+-------------------------------------------+| Log_name                           | Pos | Event_type     | Server_id | End_log_pos | Info                                      |+------------------------------------+-----+----------------+-----------+-------------+-------------------------------------------+| dingjiacaideMacBook-Pro-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.12-log, Binlog ver: 4     || dingjiacaideMacBook-Pro-bin.000001 | 123 | Previous_gtids |         1 |         150 |                                           || dingjiacaideMacBook-Pro-bin.000001 | 150 | Anonymous_Gtid |         1 |         211 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'      || dingjiacaideMacBook-Pro-bin.000001 | 211 | Query          |         1 |         281 | BEGIN                                     || dingjiacaideMacBook-Pro-bin.000001 | 281 | Table_map      |         1 |         323 | table_id: 108 (mysync.t)                  || dingjiacaideMacBook-Pro-bin.000001 | 323 | Write_rows     |         1 |         359 | table_id: 108 flags: STMT_END_F           || dingjiacaideMacBook-Pro-bin.000001 | 359 | Xid            |         1 |         386 | COMMIT /* xid=51 */                       |+------------------------------------+-----+----------------+-----------+-------------+-------------------------------------------+14 rows in set (0.00 sec)

方法二：

mysql $ sudo mysqlbinlog --start-position=150 /usr/local/var/mysql/dingjiacaideMacBook-Pro-bin.000001 // 从150开始查看二进制文件内容

mysqlbinlog具体有提供哪些参数，这里就不详细说明了，通过–help可以查看帮助，这个在主从服务器同步是出现错误的时候，用来查找问题很管用

显示二进制数目

mysql> show master logs;+------------------------------------+-----------+| Log_name                           | File_size |+------------------------------------+-----------+| dingjiacaideMacBook-Pro-bin.000001 |       784 |+------------------------------------+-----------+1 row in set (0.00 sec)mysql>

查看当前状态

mysql> show master status\G // 查看从服务器的状态把master改为slave即可*************************** 1. row ***************************             File: dingjiacaideMacBook-Pro-bin.000001         Position: 784     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)mysql>

删除二进制日志

mysql> reset master;Query OK, 0 rows affected (0.08 sec)mysql>

刷新日志

mysql> flush logs;//刷新日志Query OK, 0 rows affected (0.07 sec)mysql> show master logs;//查看日志，从结果可知多了000002这个日志+------------------------------------+-----------+| Log_name                           | File_size |+------------------------------------+-----------+| dingjiacaideMacBook-Pro-bin.000001 |       211 || dingjiacaideMacBook-Pro-bin.000002 |       150 |+------------------------------------+-----------+2 rows in set (0.00 sec)mysql>

上面提到的是在配主从服务器时，用到的二进制日志文件

除了上面提到日志文件之外，在平时用的比较多的就是错误日志了，如果在对MySQL不是很熟悉的情况下，怎样快速得知日志的路径，只要想下面进行操作：

方法一：

mysql $ ps -ef| grep mysql//查看进程    0 70529     1   0  1:30下午 ??         0:00.03 /bin/sh /usr/local/Cellar/mysql/5.7.12//bin/mysqld_safe --datadir=/usr/local/var/mysql --pid-file=/usr/local/var/mysql/dingjiacaideMacBook-Pro.local.pid   74 70735 70529   0  1:30下午 ??         0:03.38 /usr/local/Cellar/mysql/5.7.12/bin/mysqld --basedir=/usr/local/Cellar/mysql/5.7.12/ --datadir=/usr/local/var/mysql --plugin-dir=/usr/local/Cellar/mysql/5.7.12//lib/plugin --user=_mysql --log-error=/usr/local/var/mysql/dingjiacaideMacBook-Pro.local.err --pid-file=/usr/local/var/mysql/dingjiacaideMacBook-Pro.local.pid --socket=/tmp/mysql.sock --port=3306  501 79053 61539   0  5:06下午 ttys003    0:00.00 grep mysqlmysql $

方法二：

mysql> show variables like '%log_error%';//全局变量查看+---------------------+--------------------------------------------------------+| Variable_name       | Value                                                  |+---------------------+--------------------------------------------------------+| binlog_error_action | ABORT_SERVER                                           || log_error           | /usr/local/var/mysql/dingjiacaideMacBook-Pro.local.err || log_error_verbosity | 3                                                      |+---------------------+--------------------------------------------------------+3 rows in set (0.00 sec)mysql>

当然了，还有其他的日志，这里就不一一说明了，想知道他们的所在位置就通过show variables like ‘%log_%’;这个命令查看

0 0