詳細介紹Mysql中的4種日誌
- 零下一度原創
- 2017-05-03 16:24:212233瀏覽
Mysql 有4種類型的日誌：Error Log、Genaral Query Log、 Binary Log 和Slow Query Log**
一、Error Log
#記錄Mysql運行過程中的Error、 Warning、Note等訊息，系統出錯或某筆記錄出問題可以查看Error日誌。
-
Mysql的錯誤日誌預設以hostname.err存放在Mysql的日誌目錄，可以透過以下語句查看：
mysql> show variables like "log_error"; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | log_error | /tmp/mysql.log | +---------------+---------------
-
修改錯誤日誌的位址可以在/etc/my.cnf中加入--log-error = [filename]來開啟mysql錯誤日誌。我的是：
log_error = /tmp/mysql.log
-
先來看看：tail -f /tmp/mysql.log
bash-3.2# tail -f /tmp/mysql.log 2015-12-23T02:22:41.467311Z 0 [Note] IPv6 is available. 2015-12-23T02:22:41.467324Z 0 [Note] - '::' resolves to '::'; 2015-12-23T02:22:41.467350Z 0 [Note] Server socket created on IP: '::'. 2015-12-23T02:22:41.584287Z 0 [Note] Event Scheduler: Loaded 0 events 2015-12-23T02:22:41.584390Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld: ready for connections. Version: '5.7.9' socket: '/tmp/mysql.sock' port: 3306 Homebrew 2015-12-23T02:22:42.540786Z 0 [Note] InnoDB: Buffer pool(s) load completed at 151223 10:22:42 151223 10:22:51 mysqld_safe A mysqld process already exists 2015-12-23T02:25:30.984395Z 2 [ERROR] Could not use /tmp/mysql_query.log for logging (error 13 - Permission denied). Turning logging off for the server process. To turn it on again: fix the cause, then either restart the query logging by using "SET GLOBAL GENERAL_LOG=ON" or restart the MySQL server. 2015-12-23T07:28:03.923562Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 61473ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
資訊量比較大，暫不分析了。 。 。 。當然如果mysql配置或連接出錯時， 仍然可以透過tail -f 來追蹤日誌的
二、General Query Log
記錄mysql的日常日誌，包括查詢、修改、更新等的每條sql。
-
先查看mysql是否啟用了查詢日誌: show global variables like "%genera%"
mysql> show global variables like "%genera%"; +----------------------------------------+----------------------+ | Variable_name | Value | +----------------------------------------+----------------------+ | auto_generate_certs | ON | | general_log | OFF | | general_log_file | /tmp/mysql_query.log | | sha256_password_auto_generate_rsa_keys | ON | +----------------------------------------+----------------------+ 4 rows in set (0.00 sec)
我這裡是設定了日誌輸出檔案：/tmp/mysql_query.log，且日誌功能關閉
查詢日誌的輸出檔案可以在/etc/my.cnf 中新增general -log-file = [filename]
-
Mysql打開general log日誌後，所有的查詢語句都可以在general log檔中輸出，如果打開，檔案會非常大，建議調試的時候打開，平時關閉
mysql> set global general_log = on; Query OK, 0 rows affected (0.01 sec) mysql> set global general_log = off; Query OK, 0 rows affected (0.01 sec)
-
注意：
如果打開了日誌功能，但是沒有寫入日誌，那就有可能是mysql對日誌檔案的權限不夠，所以需要指定權限，我的日誌檔案是/tmp/mysql_query.log , 則：
chown mysql:mysql /tmp/mysql_query.log
三、Binary Log
二進位日誌，包含一些事件，這些事件描述了資料庫的改動，如建表、資料變更等，主要用於備份復原、回溯操作等
1. 作用：
包含了所有更新了資料或是已經潛在更新了資料(例如沒有符合任何行的一個DELETE)
包含關於每個更新資料庫(DML )的語句的執行時間資訊
不包含沒有修改任何資料的語句，如果需要啟用該選項，則需要開啟通用日誌功能
主要目的是盡可能的將資料庫還原到資料庫故障點，因為二進位日誌包含備份後進行的所有更新
用於在主複製伺服器上記錄所有將發送給從伺服器的語句
啟用此選項資料庫效能降低1%，但保障資料庫完整性，對於重要資料庫值得以效能換完整
2. 格式
Binlog有3種格式
STATMENT：每個會修改資料的sql都會記錄到master的binlog中，slave在複製的時候sql進程會解析成和原來master端執行多相同的sql再執行。
有點：在statement模式下首先就是解決了row模式的缺點，不需要記錄每一行資料的變化減少了binlog日誌量，節省了I/O以及儲存資源，提高效能。因為他只需要激勵在master上所執行的語句的細節一屆執行語句時候的上下的信息。
缺點：在statement模式下，由於他是記錄的執行語句，所以，為了讓這些語句在slave端也能正確執行，那麼他還必須記錄每個語句在執行的時候的一些相關信息，也就是上下文訊息，以確保所有語句在slave端被執行的時候能夠得到和在master端執行時候相同的結果。另外就是，由於mysql現在發展比較快，很多的新功能不斷的加入，使mysql的複製遇到了不小的挑戰，自然複製的時候牽涉到越複雜的內容，bug也越容易出現。在statement中，目前已經發現不少情況會造成Mysql的複製出現問題，主要是修改資料的時候使用了某些特定的函數或功能的時候會出現，例如：sleep()函數在有些版本中就不能被正確複製，在預存程序中使用了last_insert_id()函數，可能會使slave和master上得到不一致的id等等。ROW：日誌中會記錄成每一行資料被修改的形式，然後在slave端再對相同的資料進行修改，只記錄要修改的數據，只有value，不會有sql多表關聯的情況。
優點：在row模式下，bin-log中可以不記錄執行的sql語句的上下文相關的信息，僅僅只需要記錄那一條記錄被修改了，修改成什麼樣了，所以row的日誌內容會非常清楚的記錄下每一行資料修改的細節，非常容易理解。而且不會出現某些特定情況下的預存程序和function，以及trigger的呼叫和出發無法被正確複製問題。
缺點：在row模式下，所有的執行的語句當記錄到日誌中的時候，都會以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容。MIXED：MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种
3. 配置
-
查看mysql中二进制文件的配置情况：show variables like "%log_bin%";
mysql> show variables like "%log_bin%"; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-------+
log_bin : 用于设定是否启用二进制日志, 由此看是未开启
-
配置文件仍然是在 /etc/my.cnf 中， 修改/etc/my.cnf， 增加日志文件目录：
log_bin = /tmp/mysql-bin.log
-
重启mysql ：
bash-3.2# mysql.server start; Starting MySQL . ERROR! The server quit without updating PID file (/usr/local/Cellar/mysql/5.7.9/data/mysql.pid).
-
又报错，查看错误日志，我的配置在/tmp/mysql.log
151224 00:37:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql 2015-12-23T16:37:34.643998Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2015-12-23T16:37:34.644124Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_pISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. 2015-12-23T16:37:34.644129Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set. 2015-12-23T16:37:34.644189Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2015-12-23T16:37:34.644226Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld (mysqld 5.7.9-log) starting as process 24268 ... 2015-12-23T16:37:34.646468Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive 2015-12-23T16:37:34.646945Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation 2015-12-23T16:37:34.646978Z 0 [ERROR] Aborting 2015-12-23T16:37:34.646991Z 0 [Note] Binlog end 2015-12-23T16:37:34.647068Z 0 [Note] /usr/local/Cellar/mysql/5.7.9/bin/mysqld: Shutdown complete 151224 00:37:34 mysqld_safe mysqld from pid file /usr/local/Cellar/mysql/5.7.9/data/mysql.pid ended
重点：
You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
说明需要配置一个server-id， 再拿这句话百度，果然是这样。所以在 配置文件/etc/my.cn中添加 server-id = 1，再重启mysql，解决问题。而且在配置的bin-log同级目录增加了mysql-bin.000001 mysql-bin.index mysql-bin.log 三个文件，前两个是自动生成。
参数：
log_bin：设置此参数表示启用binlog功能，并指定路径名称
log_bin_index：设置此参数是指定二进制索引文件的路径与名称
binlog_do_db：此参数表示只记录指定数据库的二进制日志
binlog_ignore_db：此参数表示不记录指定的数据库的二进制日志
max_binlog_cache_size：此参数表示binlog使用的内存最大的尺寸
binlog_cache_size：此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。binlog_cache_use：使用二进制日志缓存的事务数量
binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
max_binlog_size：Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束
sync_binlog：这个参数直接影响mysql的性能和完整性
sync_binlog=0：
当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失
-
登录mysql，再次查看bin-log的状态，属于启用状态
mysql> show variables like "%log_bin%"; +---------------------------------+----------------------+ | Variable_name | Value | +---------------------------------+----------------------+ | log_bin | ON | | log_bin_basename | /tmp/mysql-bin | | log_bin_index | /tmp/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+----------------------+
binlog的删除
binlog的删除可以手工删除或自动删除
自动删除binlog
通过binlog参数（expire_logs_days ）来实现mysql自动删除binlog
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 869 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'expire_logs_days' ;
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> ;set global expire_logs_days=3;
ERROR:
No query specified
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'expire_logs_days' ;
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 3 |
+------------------+-------+
1 row in set (0.00 sec)手工删除binlog
mysql> reset master; //删除master的binlog
mysql> reset slave; //删除slave的中继日志
mysql> purge master logs before '2012-03-30 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'mysql-bin.000001'; //删除指定日志文件的日志索引中binlog日志文件或者直接用操作系统命令直接删除
mysql> set sql_log_bin=1/0; //如果用户有super权限，可以启用或禁用当前会话的binlog记录
mysql> show master logs; //查看master的binlog日志
mysql> show binary logs; //查看master的binlog日志
mysql> show master status; //用于提供master二进制日志文件的状态信息
mysql> show slave hosts; //显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中-
blog查看：通过mysqlbinlog 查看日志文件
bash-3.2# mysqlbinlog /tmp/mysql-bin.log
四、Slow Query Log
记录Mysql 慢查询的日志
修改配置文件 /etc/my.cnf
1. Mysql 慢查询配置相关命令：
-
查看日志功能是否开启：show variables like "%slow%";
mysql> show variables like "%slow%"; +---------------------------+----------------------------------------------------+ | Variable_name | Value | +---------------------------+----------------------------------------------------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /usr/local/var/mysql/tongkundeMacBook-Pro-slow.log | +---------------------------+----------------------------------------------------+
slow_query_log 配置为OFF ， 说明未开启慢日志
-
打开慢日志功能：set global slow_query_log = on;
mysql> set global slow_query_log = on; Query OK, 0 rows affected (0.06 sec)
-
查看下默认设置的慢查询的时间：show variables like "%long_query%";
mysql> show variables like "%long_query%"; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+
可以看出，默认是10秒，按照这个配置，数据得上n亿才能达到，为了测试我们修改一下
2. 修改Mysql配置文件的方式
-
打开/etc/my.cnf , 加入慢查询配置文件
slow-query-log = 1 slow-query-log-file = /tmp/mysql-slow.log long_query_time = 1 #设置满请求时间, 设置查多少秒的查询算是慢查询
保存退出后要重启mysql
mysql.server restart;
-
通过mysql命令查看配置：
mysql> show variables like "%slow%"; +---------------------------+---------------------+ | Variable_name | Value | +---------------------------+---------------------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /tmp/mysql-slow.log | +---------------------------+---------------------+
这里显示慢日志功能还未开启
-
打开慢日志功能
mysql> set global slow_query_log = on; ERROR 29 (HY000): File '/tmp/mysql-slow.log' not found (Errcode: 13 - Permission denied)
恩，报错了，说明什么呢，权限不够，那就给权限，退出mysql 执行：
chown mysql:mysql /tmp/mysql-slow.log
回到mysql，再次打开慢日志：
mysql> set global slow_query_log = on; Query OK, 0 rows affected (0.00 sec)
ok, 解决。
3. 测试一下
先监控下日志： tail -f /tmp/mysql-slow.log
-
在mysql中分别执行两句查询：
mysql> SELECT 2; +---+ | 2 | +---+ | 2 | +---+ 1 row in set (0.00 sec) mysql> SELECT sleep(3); +----------+ | sleep(3) | +----------+ | 0 | +----------+ 1 row in set (3.01 sec)
-
查看一下日志文件的输出：
# Time: 2015-12-23T15:50:44.140140Z # User@Host: root[root] @ localhost [] Id: 2 # Query_time: 3.003542 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1450885844; SELECT sleep(3);
基本上查询的所有信息都有显示，就不多白花了。
以上是詳細介紹Mysql中的4種日誌的詳細內容。更多資訊請關注PHP中文網其他相關文章！