MySQL日志简单分析

偏执的太偏执、 2022-04-15 04:42 179阅读 0赞

日志文件分类

错误日志文件：（默认开启）  
\--------------排错。

慢查询（系统默认不开启）  
\--------优化数据库  
mysql> SHOW GLOBAL VARIABLES LIKE "slow\_query\_%";  
\+---------------------+---------------------------------+  
| Variable\_name | Value |  
\+---------------------+---------------------------------+  
| slow\_query\_log | OFF |  
| slow\_query\_log\_file | /var/lib/mysql/server1-slow.log |  
\+---------------------+---------------------------------+  
mysql> SHOW GLOBAL VARIABLES LIKE "long%";"  
\+-----------------+-----------+  
| Variable\_name | Value |  
\+-----------------+-----------+  
| long\_query\_time | 10.000000 |  
\+-----------------+-----------+  
\#默认10s太长了。超过10s就会记录到日志文件中  
\#long\_query 开启慢查询日志  
long\_query\_time=2  
slow\_query\_log=1  
slow\_query\_log\_file=/var/lib/mysql/mysql-slow.log

二进制文件（默认不开启）  
\----------恢复备份数据，在安装mysql后第一时间进行启动。  
\#文件生成新的文件  
1.大于1G自动滚动。  
mysql> select @@max\_binlog\_size;  
\+-------------------+  
| @@max\_binlog\_size |  
\+-------------------+  
| 1073741824 |  
\+-------------------+  
1 row in set (0.00 sec)

2.MySQL服务重启

3.通过命令进行手动滚动  
mysql> FLUSH LOGS;

\#bin-log 开启二进制文件  
server\_id=94  
log\_bin=mysql-bin

查看二进制文件内容  
\[root@server1 mysql\]\# mysqlbinlog /var/lib/mysql/mysql-bin.000001

\--start-daretime 开始时间  
\--stop-datetime 停止时间  
\[root@server1 mysql\]\# mysqlbinlog --start-datetime=“2018-11-20 14:19:29” --stop-datetime="2018-11-20 14:23:53" /var/lib/mysql/mysql-bin.000001

\--start-position 开始位置  
\--stop-position 停止时间  
\[root@server1 mysql\]\# mysqlbinlog --start-position=3355 --stop-position=4140 /var/lib/mysql/mysql-bin.000001

\#查询服务器目录中所有的二进制文件  
mysql> SHOW BINARY LOGS;  
\+------------------+-----------+  
| Log\_name | File\_size |  
\+------------------+-----------+  
| mysql-bin.000001 | 4194 |  
| mysql-bin.000002 | 217 |  
| mysql-bin.000003 | 676 |  
| mysql-bin.000004 | 217 |  
| mysql-bin.000005 | 217 |  
| mysql-bin.000006 | 217 |  
| mysql-bin.000007 | 217 |  
| mysql-bin.000008 | 217 |  
| mysql-bin.000009 | 217 |  
| mysql-bin.000010 | 217 |  
| mysql-bin.000011 | 194 |  
\+------------------+-----------+  
11 rows in set (0.00 sec)

\#手动刷新一个新的二进制文件  
mysql> FLUSH LOGS;

\#查看当前正在使用的二进制文件  
mysql> SHOW MASTER STATUS;  
\+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+  
| File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
\+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+  
| mysql-bin.000011 | 194 | | | d56c691c-eba7-11e8-84e0-000c292db05e:1,  
e073964e-eba4-11e8-9eb4-000c2978fbe2:1-18 |  
\+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------+  
1 row in set (0.00 sec)