目录
1 通用查询日志 The General Query Log
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.
通用查询日志默认是不打开的,记录此日志对系统性能影响较大,所以正常情况下不打开此日志。需要时手动打开,用完关闭
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.
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
, but the initial value can be changed with the host_name
.log--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.
-
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 (orON
) 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 thelog_output
system variable; if that value isNONE
, 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”. -
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
, but the initial value can be changed with thehost_name
-slow.log--slow_query_log_file
option.
-
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 |
---|---|
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
- 登录 mysql 客户端,查看 log_bin 变量:
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 手动启用新的日志文件,一般备份完数据库后执行
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
3.2.5 删除二进制日志,并从新开始记录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
另外:
1 2 |
或 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 |
|
3.4 恢复数据
强烈建议:做任何恢复之前都给数据库做一个完整备份,新建库进行恢复。
bin-log是记录着mysql所有事件的操作,可以通过bin-log做完整恢复,基于时间点的恢复,和基于位置的恢复
3.4.1完整恢复
先执行上次完整备份恢复,再执行自上次备份后产生的二进制日志文件恢复
1 |
|
这样数据库就可以完全的恢复到崩溃前的完全状态
3.4.2基于时间点的恢复
如果确认误操作时间点为2015-06-04 10:00:00执行如下
1 |
|
然后跳过误操作的时间点,继续执行后面的binlog
1 |
|
其中--stop-date='2015-06-04 9:59:59' 和 --start-date='2015-06-04 10:01:00'
取两时间点
1 |
|
#注:其中的时间是你误操作的时间,而且这个时间点还可能涉及到的不只是误操作,也有可能有正确的操作也被跳过去了。那么执行位置恢复
3.4.3 基于位置恢复
通过查看日志文件信息,确认6259-6362为误操作点
1 2 3 4 5 6 |
|