MySQL之日志管理（一）

xinxuaw231

　　MySQL的日志有以下六种：

• 　　错误日志：服务器启动、关闭、运行中产生的错误信息、及event（事件调度）运行一个事件时产生的信息、及从服务器上启动和关闭从服务器进程时产生的信息。
  
• 　　一般查询日志：general_log、general_log_file.
  
• 　　慢查询日志：查询时间超过指定的查询时间的记录
  
• 　　二进制日志：任何引起或者可能引起数据变化的操作都要记录进二进制日志（DDL、DML、DCL语句）；二进制日志又叫做几十点还原，可在server崩溃后将数据还原到崩溃的那一刻。
  
• 　　中继日志：中继日志就是从服务器复制的主服务器上的二进制日志，其格式一样，在从服务器上会根据中级日志的记录重新执行一遍，进行数据备份
  
• 　　事务日志：只有支持事务的存储引擎（如innodb）才有事务日志，事务日志保证了数据的ACID，并将随机I/O转化为顺序I/O。
  

　　mysql> show global variables like '%log%'; 查询与日志有关的变量
　　错误日志文件参数：
　　log_error: /mydata/data/localhost.localdomain.err（数据目录下当前主机名+.err）
　　log_warnings
　　一般查询日志参数：
　　general_log
　　general_log_file: /mydata/data/localhost.log
　　log_output={table|file|none} table则日志全记录入表，file则日志全记录进文件，none不记录
　　man查询日志参数：
　　long_query_time=# 超过long_query_time的查询都要记录，默认为10s
　　slow_query_log={on|off} 启用或关闭记录慢查询日志，启用不代表记录
　　slow_query_log_file=/path 慢查询日志保存目录
　　log_slow_queries={yes|no} 慢查询日志是否启用，启用不代表记录
　　>set global slow_query_log=1;启用慢查询日志记录
　　二进制日志的格式：
　　1.基于语句: statement
　　2.基于行: row
　　3.混合方式: mixed
　　二进制日志事件一般要记录两个内容：
　　1.记录这个事件产生的时间（starttime）
　　2.这个事件在整个事件中的相对位置
　　让二进制日志文件不要和数据存放在同一个磁盘上，当数据丢失或者崩溃时，可以用备份 + 二进制日志的方式将数据还原到数据崩溃的那一刻
　　[root@node1 ~]# cd /mydata/data/
　　[root@node1 data]# ls
　　master-bin.000002  master-bin.000006  master-bin.000010
　　master-bin.000003  master-bin.000007  master-bin.000011
　　master-bin.000004  master-bin.000008  master-bin.000012
　　master-bin.000005  master-bin.000009  master-bin.000013
　　mysql> show master status; 查看当前使用的日志文件时哪个
　　+-------------------+----------+--------------+------------------+-------------------+
　　| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　+-------------------+----------+--------------+------------------+-------------------+
　　| master-bin.000021 |    673 |         |
　　+-------------------+----------+--------------+------------------+-------------------+
　　1 row in set (0.14 sec)
　　当前使用的是master-bin.00002
　　事件相对于整个事件的位置为673
　　mysql> show binlog events in 'master-bin.000021'; 查看二进制日志文件
　　+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
　　| Log_name          | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                            |
　　+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
　　| master-bin.000021 |   4 | Format_desc |         1 |          120 | Server ver: 5.6.34-log, Binlog ver: 4
　　| master-bin.000021 | 120 | Query       |         1 |         207 | BEGIN                                                                                                           |
　　| master-bin.000021 | 207 | Query       |         1 |         344 | use `students`; insert into test1 (cid,name,sid) values (4,'faded','D')
　　| master-bin.000021 | 344 | Xid         |         1 |        375 | COMMIT /* xid=40 */                                                                                             |

　　| master-bin.000021 | 375 | Query       |         1 |         549 | use `mysql`; CREATE USER 'faker'@'127.0.0.1'>　　| master-bin.000021 | 549 | Query       |         1 |         673 | use `mysql`; rename user faker@'127.0.0.1' to sky@'127.0.0.1'
　　+-------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
　　6 rows in set (0.02 sec)
　　Pos      事件的起始位置
　　Event_type  事件类型
　　Server_id  服务器id号
　　End_log_pos 事件结束位置
　　Info      文件头及进行的操作
　　mysql> show binlog events in 'master-bin.000021' from 344; 从304起始位置查看
　　+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
　　| Log_name          | Pos | Event_type | Server_id | End_log_pos | Info                                                                                                            |
　　+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
　　| master-bin.000021 | 344 | Xid        |         1 |         375 | COMMIT /* xid=40 */                                                                                             |

　　| master-bin.000021 | 375 | Query      |         1 |         549 | use `mysql`; CREATE USER 'faker'@'127.0.0.1'>　　| master-bin.000021 | 549 | Query      |         1 |         673 | use `mysql`; rename user faker@'127.0.0.1' to sky@'127.0.0.1'                                                   |
　　+-------------------+-----+------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
　　3 rows in set (0.00 sec)
　　除此之外还可以用mysqlbinlog命令查看日志文件（二进制格式的二进制文件不能用cat查看）
　　mysqlbinlog
　　--start-datetime
　　--stop-datetime
　　--start-position
　　--stop-position
　　[root@node1 data]# mysqlbinlog master-bin.000021
　　[root@node1 data]# mysqlbinlog --start-position=207 --stop-position=375 master-bin.000021
　　[root@node1 data]# mysqlbinlog --start-datetime='17-02-13  9:06:21' master-bin.000021
　　还可以将其导入一个文件中，然后再将该文件导入另外一个数据库中，就可以按照二进制日志重新执行一遍
　　[root@node1 data]# mysqlbinlog --start-datetime='17-02-13  9:06:21' master-bin.000021 >/root/my.sql
　　mysql> flush logs; 手动进行日志滚动
　　mysql> show master status;
　　+-------------------+----------+--------------+------------------+-------------------+
　　| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　+-------------------+----------+--------------+------------------+-------------------+
　　| master-bin.000022 |      120 |              |                  |                   |
　　+-------------------+----------+--------------+------------------+-------------------+
　　1 row in set (0.00 sec)
　　如果我已经将某时刻之前的数据进行了备份，应该删除该时刻之前的二进制日志，从而节省空间
　　mysql> purge binary logs to 'master-bin.0000010'; 将'master-bin.0000010'之前的日志全删除
　　mysql> show master status;
　　+-------------------+----------+--------------+------------------+-------------------+
　　| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
　　+-------------------+----------+--------------+------------------+-------------------+
　　| master-bin.000022 |      120 |              |                  |                   |
　　+-------------------+----------+--------------+------------------+-------------------+
　　1 row in set (0.00 sec)
　　mysql> show binary logs; 查看当前拥有的二进制日志
　　+-------------------+-----------+
　　| Log_name          | File_size |
　　+-------------------+-----------+
　　| master-bin.000001 |     69435 |
　　| master-bin.000002 |   1371047 |
　　| master-bin.000003 |       143 |
　　| master-bin.000004 |       143 |
　　| master-bin.000005 |       120 |
　　| master-bin.000006 |       143 |
　　| master-bin.000007 |   4836454 |
　　| master-bin.000008 |     21561 |
　　| master-bin.000009 |       143 |
　　| master-bin.000010 |       143 |
　　| master-bin.000011 |       120 |
　　| master-bin.000012 |       120 |
　　| master-bin.000013 |       120 |
　　| master-bin.000014 |       143 |
　　| master-bin.000015 |       120 |
　　| master-bin.000016 |       143 |
　　| master-bin.000017 |       120 |
　　| master-bin.000018 |       818 |
　　| master-bin.000019 |      2220 |
　　| master-bin.000020 |      4692 |
　　| master-bin.000021 |       721 |
　　| master-bin.000022 |       120 |
　　+-------------------+-----------+