mysql 开启日志服务

mysql 版本：mysql-5.7

1.在/etc/my.cnf 中添加如下内容：

#错误日志： -log-err
log-error=/usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/log/error.log

#查询日志： -log
general-log=ON
general-log-file=/usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/log/general.log

#慢查询日志: -log-slow-queries
# 执行超过1秒的sql会被log下来
long_query_time=1
# 开启慢查询
slow_query_log=on
# 将查询执行时间较慢的语句进行记录
#log-slow-queries=/usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/log/slow.log
slow-query-log-file=/usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/log/slow.log

#二进制日志： -log-bin
server_id=100
log-bin=/usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/log/bin.log

2.重启mysql 

/etc/init.d/mysqld restart

3.查看日志目录

重启后并对mysql进行了简单的查询操作，error.log 保存了标准错误输出，general.log 保存了查询记录

[mysql@ns1 log]$ ll 
总用量 28
-rw-r----- 1 mysql mysql  154 9月  17 12:46 bin.000001
-rw-r----- 1 mysql mysql   62 9月  17 12:46 bin.index
-rw-r----- 1 mysql mysql 4569 9月  17 12:46 error.log
-rw-r----- 1 mysql mysql 4853 9月  17 12:49 general.log
-rw-r----- 1 mysql mysql  410 9月  17 12:46 slow.log

附：

什么是二进制日志？
 用来记录操作MySQL数据库中的写入性操作（增删改，但不包括查询），相当于sqlserver中的完整恢复模式下的事务日志文件。
二进制日志的作用？
 1，用于复制，配置了主从复制的时候，主服务器会将其产生的二进制日志发送到slave端，slave端会利用这个二进制日志的信息在本地重做，实现主从同步
 2，用户恢复，MySQL可以在全备和差异备份的基础上，利用二进制日志进行基于时间点或者事物Id的恢复操作。原理雷同于主从复制的日志重做。

 

1）日志状态查询

mysql> show variables like'log_%';
+----------------------------------------+--------------------------------------------------------------+
| Variable_name                          | Value                                                        |
+----------------------------------------+--------------------------------------------------------------+
| log_bin                                | ON                                                           |
| log_bin_basename                       | /usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/log/bin       |
| log_bin_index                          | /usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/log/bin.index |
| log_bin_trust_function_creators        | OFF                                                          |
| log_bin_use_v1_row_events              | OFF                                                          |
| log_builtin_as_identified_by_password  | OFF                                                          |
| log_error                              | /usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/log/error.log |
| log_error_verbosity                    | 3                                                            |
| log_output                             | FILE                                                         |
| log_queries_not_using_indexes          | OFF                                                          |
| log_slave_updates                      | OFF                                                          |
| log_slow_admin_statements              | OFF                                                          |
| log_slow_slave_statements              | OFF                                                          |
| log_statements_unsafe_for_binlog       | ON                                                           |
| log_syslog                             | OFF                                                          |
| log_syslog_facility                    | daemon                                                       |
| log_syslog_include_pid                 | ON                                                           |
| log_syslog_tag                         |                                                              |
| log_throttle_queries_not_using_indexes | 0                                                            |
| log_timestamps                         | UTC                                                          |
| log_warnings                           | 2                                                            |
+----------------------------------------+--------------------------------------------------------------+
21 rows in set (0.00 sec)

2）当前二进制状态

mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000001 |      154 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

 

3）二进制日志查询

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

mysql> 

 

转载于:https://www.cnblogs.com/jason-dong/p/9661538.html