mysql日志

mysql常见以下四种日志：

错误日志，二进制日志，查询日志，慢查询日志

1，错误日志：

记录mysql启动与停止时，以及服务器在运行过程中发生过任何严重错误时信息，当数据库出现故障导致无法启动可以首先查看此日志。

指定格式：[mysqld] 中 --log-error=file_name.err 默认产生文件在datadir（数据目录）中。

下面是错误日志的一段信息：

180707 11:18:46  InnoDB: Starting shutdown...
180707 11:18:47  InnoDB: Shutdown completed; log sequence number 1614934
180707 11:18:47 [Note] /application/mysql-5.5.32/bin/mysqld: Shutdown complete

180707 11:18:47 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended
180707 11:18:56 mysqld_safe Starting mysqld daemon with databases from /data/3306/data
180707 11:18:56 [Note] Plugin 'FEDERATED' is disabled.
180707 11:18:56 InnoDB: The InnoDB memory heap is disabled
180707 11:18:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180707 11:18:56 InnoDB: Compressed tables use zlib 1.2.3
180707 11:18:56 InnoDB: Using Linux native AIO
180707 11:18:56 InnoDB: Initializing buffer pool, size = 32.0M
180707 11:18:56 InnoDB: Completed initialization of buffer pool
180707 11:18:56 InnoDB: highest supported file format is Barracuda.
180707 11:18:57  InnoDB: Waiting for the background threads to start
180707 11:18:58 InnoDB: 5.5.32 started; log sequence number 1614934
180707 11:18:58 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
180707 11:18:58 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
180707 11:18:58 [Note] Server socket created on IP: '0.0.0.0'.
180707 11:18:58 [Warning] 'proxies_priv' entry '@ root@web03' ignored in --skip-name-resolve mode.
180707 11:18:58 [Note] Event Scheduler: Loaded 0 events
180707 11:18:58 [Note] /application/mysql-5.5.32/bin/mysqld: ready for connections.
Version: '5.5.32-log'  socket: '/data/3306/mysql.sock'  port: 3306  Source distribution

2，二进制日志：

1）二进制日志（binlog）记录着所有的DDL语句和DNL语句，但不包含查询语句。语句以"事件"的形式保存，描述了数据的更改过程，通常此日志文件的内容对数据的恢复及其重要。

2）日志格式： --log-bin=file_name 例如：log-bin = /data/3306/mysql-bin 默认产生文件在datadir（数据目录）中。

3）日志的读取：此日志是二进制存储形式，读取需使用mysql自带命令mysqlbinlog工具来查看：

[root@web03 3306]# mysqlbinlog mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180801 17:58:50 server id 1  end_log_pos 107     Start: binlog v 4, server v 5.5.32-log created 180801 17:58:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
WoRhWw8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABahGFbEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#180801 18:26:59 server id 1  end_log_pos 177     Query    thread_id=15    exec_time=0    error_code=0
SET TIMESTAMP=1533119219/*!*/;
SET @@session.pseudo_thread_id=15/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 177
#180801 18:26:59 server id 1  end_log_pos 261     Query    thread_id=15    exec_time=0    error_code=0
use `oldboy`/*!*/;
SET TIMESTAMP=1533119219/*!*/;
delete from student
/*!*/;
# at 261
#180801 18:26:59 server id 1  end_log_pos 288     Xid = 125
COMMIT/*!*/;
# at 288
#180801 18:27:03 server id 1  end_log_pos 358     Query    thread_id=15    exec_time=0    error_code=0
SET TIMESTAMP=1533119223/*!*/;
BEGIN
/*!*/;
# at 358
#180801 18:27:03 server id 1  end_log_pos 515     Query    thread_id=15    exec_time=0    error_code=0
SET TIMESTAMP=1533119223/*!*/;
insert into student values(3,'韩海','23','网络工程师'),(4,'刘珊','26','Java开发')
/*!*/;
# at 515
#180801 18:27:03 server id 1  end_log_pos 542     Xid = 126
COMMIT/*!*/;
# at 542
#180801 18:27:16 server id 1  end_log_pos 623     Query    thread_id=15    exec_time=0    error_code=0
SET TIMESTAMP=1533119236/*!*/;
flush privileges
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

4)日志删除

方法一：RESET MASTER：清空所有binlog日志

查看当前binlog日志：

[root@web03 3306]# ls -lr mysql-bin*
-rw-rw---- 1 mysql mysql  84 8月   4 12:07 mysql-bin.index
-rw-rw---- 1 mysql mysql 107 8月   4 12:07 mysql-bin.000003
-rw-rw---- 1 mysql mysql 126 8月   3 22:43 mysql-bin.000002
-rw-rw---- 1 mysql mysql 642 8月   1 18:29 mysql-bin.000001

使reset master进行日志删除

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> system ls -ltr mysql-bin*
-rw-rw---- 1 mysql mysql 107 8月   4 12:13 mysql-bin.000001
-rw-rw---- 1 mysql mysql  28 8月   4 12:13 mysql-bin.index

更多binlog日志删除参照：https://blog.csdn.net/yunuobing/article/details/74779995

日志管理：https://www.cnblogs.com/yinzhengjie/p/7898275.html