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>
欢迎转载,不必注明出处