MySql 日志

最新推荐文章于 2021-02-06 20:46:31 发布

prcyang

最新推荐文章于 2021-02-06 20:46:31 发布

阅读量176

点赞数 1

分类专栏： MySql

本文链接：https://blog.csdn.net/prcyang/article/details/86649936

版权

MySql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1 通用查询日志 The General Query Log

2 慢查询日志 Slow Query Log

3 二进制日志 The Binary Log

3.2.4 手动启用新的日志文件,一般备份完数据库后执行

3.2.5 删除所有二进制日志，并从新开始记录

3.2.6 expire_logs_days 日志失效

1 通用查询日志 The General Query Log

参见《mysql 参考手册》 5.4.3 The General Query Log 和 5.1.7 Server System Variables

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

通用查询日志默认是不打开的，记录此日志对系统性能影响较大，所以正常情况下不打开此日志。需要时手动打开，用完关闭

general_log

Property	Value
Command-Line Format	`--general-log`
System Variable	`general_log`
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	`OFF`

Whether the general query log is enabled. The value can be 0 (or OFF) to disable the log or 1 (or ON) to enable the log. The default value depends on whether the --general_log option is given. The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled.

general_log_file

Property	Value
Command-Line Format	`--general-log-file=file_name`
System Variable	`general_log_file`
Scope	Global
Dynamic	Yes
Type	File name
Default Value	`host_name.log`

The name of the general query log file. The default value is host_name.log, but the initial value can be changed with the --general_log_file option.

2 慢查询日志 Slow Query Log

The slow query log consists of SQL statements that take more than long_query_time seconds
to execute and require at least min_examined_row_limit rows to be examined. The slow query
log can be used to find queries that take a long time to execute and are therefore candidates for
optimization. However, examining a long slow query log can be a time-consuming task. To make this
easier, you can use the mysqldumpslow command to process a slow query log file and summarize its
contents
Slow Query Log Parameters
The minimum and default values of long_query_time are 0 and 10, respectively. The value can be
specified to a resolution of microseconds.
By default, administrative statements are not logged, nor are queries that do not use indexes
for lookups. This behavior can be changed using log_slow_admin_statements and
log_queries_not_using_indexes, as described later.
By default, the slow query log is disabled. To specify the initial slow query log state explicitly, use
--slow_query_log[={0|1}]. With no argument or an argument of 1, --slow_query_log
enables the log. With an argument of 0, this option disables the log. To specify a log file name, use --
slow_query_log_file=file_name.

slow_query_log

Property Value
Command-Line Format --slow-query-log
System Variable slow_query_log
Scope Global
Dynamic Yes
Type Boolean
Default Value OFF
Whether the slow query log is enabled. The value can be 0 (or OFF) to disable the log or 1 (or ON) to enable the log. The default value depends on whether the --slow_query_log option is given. The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled.

“Slow” is determined by the value of the long_query_time variable. See Section 5.4.5, “The Slow Query Log”.
slow_query_log_file

Property Value
Command-Line Format --slow-query-log-file=file_name
System Variable slow_query_log_file
Scope Global
Dynamic Yes
Type File name
Default Value host_name-slow.log
The name of the slow query log file. The default value is host_name-slow.log, but the initial value can be changed with the --slow_query_log_file option.

Property	Value
Command-Line Format	`--slow-query-log`
System Variable	`slow_query_log`
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	`OFF`

Property	Value
Command-Line Format	`--slow-query-log-file=file_name`
System Variable	`slow_query_log_file`
Scope	Global
Dynamic	Yes
Type	File name
Default Value	`host_name-slow.log`

log_queries_not_using_indexes

Property Value
Command-Line Format --log-queries-not-using-indexes
System Variable log_queries_not_using_indexes
Scope Global
Dynamic Yes
Type Boolean
Default Value OFF
Whether queries that do not use indexes are logged to the slow query log. See Section 5.4.5, “The Slow Query Log”.

Property	Value
Command-Line Format	`--log-queries-not-using-indexes`
System Variable	`log_queries_not_using_indexes`
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	`OFF`

log_slow_admin_statements

Property	Value
Introduced	5.7.1
System Variable	`log_slow_admin_statements`
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	`OFF`

Include slow administrative statements in the statements written to the slow query log. Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, andREPAIR TABLE.

3 二进制日志 The Binary Log

详细参见《mysql 参考手册》 5.4.4 The Binary Log
二进制日志用来记录操作MySQL数据库中的写入性操作（增删改，但不包括查询）
二进制日志的作用？
　　1，用于复制，配置了主从复制的时候，主服务器会将其产生的二进制日志发送到slave端，slave端会利用这个二进制日志的信息在本地重做，实现主从同步
　　2，数据恢复，MySQL可以在全备和差异备份的基础上，利用二进制日志进行基于时间点或者事物Id的恢复操作。原理雷同于主从复制的日志重做。

启用二进制日志记录会稍微降低服务器性能（有的说大约1%）。不过，二进制日志的好处在于，它使您能够设置复制和恢复操作，其好处通常超过了这个小的性能下降

3.1 开启日志

3.1.1 通过修改 mysql 的配置文件 my.cnf

修改my.cnf

vi /etc/my.cnf

在 [mysqld] 中添加内容：

 server-id = 1                        # 确保在整个Mysql集群中唯一，5.7开始二进制日志必须设置此值
 log-bin = binlog                     # 日志文件名称 未指定目录，默认数据文件位置 
 log-bin-index = binlog.index #       # 记录二进制日志文件名 未指定目录，默认数据文件位置

log_bin是生成的bin-log的文件名，后缀则是6位数字的编码，从000001开始，按照上面的配置，生成的文件则为：

binlog.000001

如果指定目录

 log-bin = /var/log/mysql/binlog                    
 log-bin-index = /var/log/mysql/binlog.index

如果全过程使用的是Mysql用户，应该可以正常启动。
如果用的ROOT用户，可能不能正常启动，原因是新建的目录权限不对。
使用下面命令修改就可以了：
# chown mysql:mysql /var/log/mysql -R

重启 mysql：

systemctl restart mysqld

show variables like 'log_bin';

3.2 基本操作

3.2.1 查看所有日志文件

mysql> show binary logs; 或show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |       521 |
+---------------+-----------+
2 rows in set (0.00 sec)

3.2.2 查看正在写入的日志文件：

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

3.2.3 查看binlog文件内容

查看当前binlog文件内容

mysql> show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| binlog.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.25-log, Binlog ver: 4 |
| binlog.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| binlog.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| binlog.000002 | 219 | Query          |         1 |         290 | BEGIN                                 |
| binlog.000002 | 290 | Table_map      |         1 |         362 | table_id: 110 (via.fr_camera_copy)    |
| binlog.000002 | 362 | Delete_rows    |         1 |         490 | table_id: 110 flags: STMT_END_F       |
| binlog.000002 | 490 | Xid            |         1 |         521 | COMMIT /* xid=155515 */               |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set (0.00 sec)

注：

Log_name:此条log存在哪个文件中

Pos:log在bin-log中的开始位置

Event_type:log的类型信息

Server_id:可以查看配置中的server_id,表示log是哪个服务器产生

End_log_pos：log在bin-log中的结束位置

Info:log的一些备注信息，可以直观的看出进行了什么操作

查看指定binlog 文件内容

[root@l4 mysql]# mysqlbinlog ./binlog.000002 # 也可以使用linux 系统数据内容 如添加： | cat -n
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
........

binlog_format

对于STATEMENT 格式的，查看内容可以看到ddl 语句，但对于ROW格式的则不可以。

3.2.4 手动启用新的日志文件,一般备份完数据库后执行

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| binlog.000001 | 120 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> flush logs; #结束正在写入日志文件,开启新的日志文件

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| binlog.000002 | 120 | | | |

+------------------+----------+--------------+------------------+-------------------+

3.2.5 删除二进制日志，并从新开始记录

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| binlog.000002 | 120 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> reset master; #删除所有日志重新初始化

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

|binlog.000001 | 120 | | | |

+------------------+----------+--------------+------------------+-------------------+

另外：

1 2	`mysql> purge master logs` `to` `'binlog.000002'; #是将'binlog.000002'编号之前的所有日志进行删除` `mysql> purge master logs before` `'yyyy-mm-dd hh:mm:ss'` `#是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除` 或 purge binary logs 。。。。。

3.2.6 设置日志自动删除expire_logs_days

Property	Value
Command-Line Format	`--expire-logs-days=#`
System Variable	`expire_logs_days`
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	`0`
Minimum Value	`0`
Maximum Value	`99`

The number of days for automatic binary log file removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in Section 5.4, “MySQL Server Logs”.

3.3 二进制日志文件导出

1 2	`# mysqlbinlog --start-datetime="2015-07-02 11:25:56" --stop-datetime="2015-07-02 14:20:10" mysql-bin.000001 > /data/test01.log #按时间点导出` `# mysqlbinlog --start-position=203 --stop-position=203 mysql-bin.000001 > /data/test02.log #按事件位置导出`

3.4 恢复数据

强烈建议：做任何恢复之前都给数据库做一个完整备份，新建库进行恢复。

bin-log是记录着mysql所有事件的操作，可以通过bin-log做完整恢复，基于时间点的恢复，和基于位置的恢复

3.4.1完整恢复

先执行上次完整备份恢复，再执行自上次备份后产生的二进制日志文件恢复

1	`# mysql localhost mysql-bin.000001 \| mysql -uroot -p`

这样数据库就可以完全的恢复到崩溃前的完全状态

3.4.2基于时间点的恢复

如果确认误操作时间点为2015-06-04 10:00:00执行如下

1	`# mysqlbinlog --stop-date='2015-06-04 9:59:59' mysql-bin.000001 \| mysql -uroot -p`

然后跳过误操作的时间点，继续执行后面的binlog

1	`# mysqlbinlog --start-date='2015-06-04 10:01:00' mysql-bin.000001 \| mysql -uroot -p`

其中--stop-date='2015-06-04 9:59:59' 和 --start-date='2015-06-04 10:01:00'

取两时间点

1	`# mysqlbinlog --start-datetime="2015-07-02 11:25:56" --stop-datetime="2015-07-02 14:20:10" mysql-bin.000001 \| mysql -u root -p`

#注：其中的时间是你误操作的时间，而且这个时间点还可能涉及到的不只是误操作，也有可能有正确的操作也被跳过去了。那么执行位置恢复

3.4.3 基于位置恢复

通过查看日志文件信息，确认6259-6362为误操作点

# mysqlbinlog --stop-position=6259 mysql-bin.000001 | mysql -uroot -p #从1开始至6259的事件读，不包括6259事件

# mysqlbinlog --start-position=6363 mysql-bin.000001 | mysql -uroot -p #从6259的事件开始读

# 取两事件点

mysqlbinlog --start-position=5786 --stop-position=6254 mysql-bin.000001 | mysql -uroot -p

prcyang

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql 日志

目录1 通用查询日志The General Query Log2 慢查询日志 Slow Query Log3 二进制日志 The Binary Log3.1 开启日志3.2 基本操作3.2.1 查看所有日志文件3.2.2 查看正在写入的日志文件：3.2.3 查看binlog文件内容3.2.4 手动启用新的日志文件,一般备份完数据库后执行3.2....
复制链接

扫一扫

专栏目录