這篇文章主要介紹“后端開發(fā)必備的MySQL日志文件知識(shí)點(diǎn)有哪些”，在日常操作中，相信很多人在后端開發(fā)必備的MySQL日志文件知識(shí)點(diǎn)有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”后端開發(fā)必備的MySQL日志文件知識(shí)點(diǎn)有哪些”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

創(chuàng)新互聯(lián)建站專業(yè)為企業(yè)提供平利網(wǎng)站建設(shè)、平利做網(wǎng)站、平利網(wǎng)站設(shè)計(jì)、平利網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、平利企業(yè)網(wǎng)站模板建站服務(wù)，10多年平利做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

前言

日志文件記錄了影響MySQL數(shù)據(jù)庫(kù)的各種類型活動(dòng)，MySQL數(shù)據(jù)庫(kù)中常見的日志文件有錯(cuò)誤日志，二進(jìn)制日志，慢查詢?nèi)罩竞筒樵內(nèi)罩?。下面分別對(duì)他們進(jìn)行介紹。

錯(cuò)誤日志

錯(cuò)誤日志文件對(duì)MySQL的啟動(dòng)，運(yùn)行，關(guān)閉過程進(jìn)行了記錄。

mysql> show variables like 'log_error';  +---------------+---------------------+  | Variable_name | Value               |  +---------------+---------------------+  | log_error     | /var/log/mysqld.log |  +---------------+---------------------+  1 row in set (0.03 sec)

可以看到錯(cuò)誤日志的路徑和文件名，默認(rèn)情況下錯(cuò)誤文件的文件名為服務(wù)器的主機(jī)名，即：hostname.err。只不過我這里設(shè)置的是/var/log/mysqld.log,修改錯(cuò)誤日志地址可以在/etc/my.cnf中添加

# Recommended in standard MySQL setup  sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  [mysqld_safe]  log-error=/var/log/mysqld.log  pid-file=/var/run/mysqld/mysqld.pid

當(dāng)出現(xiàn)MySQL數(shù)據(jù)庫(kù)不能正常啟動(dòng)時(shí)，第一個(gè)必須查找的文件就是錯(cuò)誤日志文件，該文件記錄了出錯(cuò)信息，能夠幫助我們找到問題。

慢查詢?nèi)罩?/strong>

慢查詢?nèi)罩居脕?lái)記錄響應(yīng)時(shí)間超過閾值的SQL語(yǔ)句，所以我們可以設(shè)置一個(gè)閾值，將運(yùn)行時(shí)間超過該值的所有SQL語(yǔ)句都記錄到慢查詢?nèi)罩疚募?。該閾值可以通過參數(shù) long_query_time 來(lái)設(shè)置，默認(rèn)為10秒。

啟動(dòng)慢查詢?nèi)罩?/strong>

默認(rèn)情況下，MySQL數(shù)據(jù)庫(kù)并不啟動(dòng)慢查詢?nèi)罩荆枰謩?dòng)將這個(gè)參數(shù)設(shè)為ON，然后啟動(dòng)

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       | /var/lib/mysql/iz2zeaf3cg1099kiidi06mz-slow.log |  +---------------------------+-------------------------------------------------+  5 rows in set (0.00 sec)  mysql> set global slow_query_log='ON';  Query OK, 0 rows affected (0.00 sec)  mysql> show variables like "slow_query_log";  +---------------------------+-------------------------------------------------+  | Variable_name             | Value                                           |  +---------------------------+-------------------------------------------------+                                 | slow_query_log            | ON                                              |  | slow_query_log_file       | /var/lib/mysql/iz2zeaf3cg1099kiidi06mz-slow.log |  +---------------------------+-------------------------------------------------+  2   rows in set (0.00 sec)

但是使用 set global slow_query_log='ON' 開啟慢查詢?nèi)罩?，只是?duì)當(dāng)前數(shù)據(jù)庫(kù)有效，如果MySQL數(shù)據(jù)庫(kù)重啟后就會(huì)失效。所以如果要永久生效，就要修改配置文件 my.cnf (其他系統(tǒng)變量也是如此)，如下：

[mysqld]  slow_query_log=1

然后重啟MySQL就可以讓慢查詢?nèi)罩居涗涢_啟了,至于日志文件的路徑就是上面slow_query_log_file對(duì)應(yīng)的路徑。

設(shè)置閾值

mysql> show variables like 'long_query_time';  +-----------------+-----------+  | Variable_name   | Value     |  +-----------------+-----------+  | long_query_time | 10.000000 |  +-----------------+-----------+  1 row in set (0.00 sec)

閾值默認(rèn)為10秒，我們可以修改閾值大小，比如(當(dāng)然這還是對(duì)當(dāng)前數(shù)據(jù)庫(kù)有效)：

mysql> set global long_query_time=0.05;  Query OK, 0 rows affected (0.00 sec)

設(shè)置long_query_time這個(gè)閾值之后，MySQL數(shù)據(jù)庫(kù)會(huì)記錄運(yùn)行時(shí)間超過該值的所有SQL語(yǔ)句，但對(duì)于運(yùn)行時(shí)間正好等于 long_query_time 的情況，并不會(huì)被記錄下。而設(shè)置 long_query_time為0來(lái)捕獲所有的查詢

參數(shù)log_queries_not_using_indexes

另一個(gè)和慢查詢?nèi)罩居嘘P(guān)的參數(shù)是 log_queries_not_using_indexes,

如果運(yùn)行的SQL語(yǔ)句沒有使用索引，則MySQL數(shù)據(jù)庫(kù)同樣會(huì)將這條SQL語(yǔ)句記錄到慢查詢?nèi)罩疚募?。首先確認(rèn)打開了log_queries_not_using_indexes;

mysql> show variables like 'log_queries_not_using_indexes';  +-------------------------------+-------+  | Variable_name                 | Value |  +-------------------------------+-------+  | log_queries_not_using_indexes | ON    |  +-------------------------------+-------+  1 row in set (0.12 sec)

例子，沒有用到索引進(jìn)行查詢：

mysql> explain select * from vote_record_memory where vote_id = 323;  +----+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+  | id | select_type | table              | type | possible_keys | key  | key_len | ref  | rows   | Extra       |  +----+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+  |  1 | SIMPLE      | vote_record_memory | ALL  | NULL          | NULL | NULL    | NULL | 149272 | Using where |  +----+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+  1 row in set (1.56 sec)

可以看到是進(jìn)行了全表掃描；然后去log日志文件中查看這條SQL已經(jīng)被標(biāo)記為慢SQL，因?yàn)樗鼪]有使用索引。

# Time: 180817 11:42:59 # User@Host: root[root] @  [117.136.86.151]  Id:  2625  # Query_time: 0.016542  Lock_time: 0.000112 Rows_sent: 142  Rows_examined: 149272  SET timestamp=1534477379;  select * from vote_record_memory where vote_id = 323;

將日志記錄放入表中

MySQL5.1開始可以將慢查詢的日志記錄放入一張表中，在mysql數(shù)據(jù)庫(kù)下，名為slow_log

| slow_log | CREATE TABLE `slow_log` (    `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    `user_host` mediumtext NOT NULL,    `query_time` time NOT NULL,    `lock_time` time NOT NULL,    `rows_sent` int(11) NOT NULL,    `rows_examined` int(11) NOT NULL,    `db` varchar(512) NOT NULL,    `last_insert_id` int(11) NOT NULL,    `insert_id` int(11) NOT NULL,    `server_id` int(10) unsigned NOT NULL,    `sql_text` mediumtext NOT NULL,    `thread_id` bigint(21) unsigned NOT NULL  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |

參數(shù)log_output指定了慢查詢輸出的格式，默認(rèn)為file,可以將它設(shè)置成table，將變成了上面的slow_log中

mysql> show variables like "log_output";  +---------------+-------+  | Variable_name | Value |  +---------------+-------+  | log_output    | FILE  |  +---------------+-------+  1 row in set (0.19 sec)

但是多數(shù)情況下這樣做沒什么必要，這不但對(duì)性能有較大影響，而且 MySQL 5.1 在將慢查詢記錄到文件中時(shí)已經(jīng)支持微秒級(jí)別的信息，然而將慢查詢記錄到表中會(huì)導(dǎo)致時(shí)間粒度退化為只能到秒級(jí)，而秒級(jí)別的慢查詢?nèi)罩緵]有太大的意義

慢查詢?nèi)罩痉治龉ぞ?/strong>

mysqldumpslow命令

當(dāng)越來(lái)越多的SQL查詢被記錄到慢查詢?nèi)罩疚募?，這時(shí)候直接看日志文件就不容易了，MySQL提供了mysqldumpslow 命令解決:

[root@iz2zeaf3cg1099kiidi06mz mysql]# mysqldumpslow iz2zeaf3cg1099kiidi06mz-slow.log  Reading mysql slow query log from iz2zeaf3cg1099kiidi06mz-slow.log  Count: 1  Time=60.02s (60s)  Lock=0.00s (0s)  Rows=149272.0 (149272), root[root]@[117.136.86.151]    select * from vote_record_memory  Count: 1  Time=14.85s (14s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[117.136.86.151]    CALL add_vote_memory(N)  Count: 1  Time=1.72s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[117.136.86.151]    INSERT into vote_record SELECT * from  vote_record_memory  Count: 1  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=142.0 (142), root[root]@[117.136.86.151]    select * from vote_record_memory where vote_id = N

更多關(guān)于 mysqldumpslow 命令的介紹，請(qǐng)參閱 ：

https://github.com/luisedware/Archives/issues/7

pt-query-digest 工具

pt-query-digest 是分析MySQL查詢?nèi)罩咀钣辛Φ墓ぞ?，該工具功能?qiáng)大，它可以分析binlog，Generallog，slowlog，也可以通過show processlist或者通過 tcpdump 抓取的MySQL協(xié)議數(shù)據(jù)來(lái)進(jìn)行分析，比 mysqldumpslow 更具體，更完善。以下是使用pt-query-digest的示例:

//直接分析慢查詢文件  pt-query-digest  slow.log > slow_report.log

該工具可以將查詢的剖析報(bào)告打印出來(lái)，可以分析結(jié)果輸出到文件中，分析過程是先對(duì)查詢語(yǔ)句的條件進(jìn)行參數(shù)化，然后對(duì)參數(shù)化以后的查詢進(jìn)行分組統(tǒng)計(jì)，統(tǒng)計(jì)出各查詢的執(zhí)行時(shí)間，次數(shù)，占比等，可以借助分析結(jié)果找出問題進(jìn)行優(yōu)化。

更多關(guān)于pt-query-digest的安裝與使用，請(qǐng)參閱 :

www.ywnds.com/?p=8179

查詢?nèi)罩?/strong>

查看日志記錄了所有對(duì) MySQL 數(shù)據(jù)庫(kù)請(qǐng)求的信息，不論這些請(qǐng)求是否得到了正確的執(zhí)行。默認(rèn)為 主機(jī)名.log

mysql> show variables like "general_log%";  +------------------+--------------------------------------------+  | Variable_name    | Value                                      |  +------------------+--------------------------------------------+  | general_log      | OFF                                        |  | general_log_file | /var/lib/mysql/iz2zeaf3cg1099kiidi06mz.log |  +------------------+--------------------------------------------+  2 rows in set (0.24 sec)

默認(rèn)情況下不啟動(dòng)查詢?nèi)罩荆仨氁乳_啟。

mysql> set global general_log='ON';  Query OK, 0 rows affected (0.05 sec)  mysql> show variables like "general_log%";  +------------------+--------------------------------------------+  | Variable_name    | Value                                      |  +------------------+--------------------------------------------+  | general_log      | ON                                         |  | general_log_file | /var/lib/mysql/iz2zeaf3cg1099kiidi06mz.log |  +------------------+--------------------------------------------+  2 rows in set (0.11 sec)

二進(jìn)制日志

二進(jìn)制日志記錄了對(duì)數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作，但是不包括select和show這類操作，因?yàn)檫@類操作對(duì)數(shù)據(jù)本身并沒有修改，如果你還想記錄select和show操作，那只能使用查詢?nèi)罩玖?，而不是二進(jìn)制日志。

此外，二進(jìn)制還包括了執(zhí)行數(shù)據(jù)庫(kù)更改操作的時(shí)間和執(zhí)行時(shí)間等信息。二進(jìn)制日志主要有以下幾種作用:

•  恢復(fù)(recovery)：某些數(shù)據(jù)的恢復(fù)需要二進(jìn)制日志，如當(dāng)一個(gè)數(shù)據(jù)庫(kù)全備文件恢復(fù)后，我們可以通過二進(jìn)制的日志進(jìn)行 point-in-time的恢復(fù)

•  復(fù)制(replication) : 通過復(fù)制和執(zhí)行二進(jìn)制日志使得一臺(tái)遠(yuǎn)程的 MySQL 數(shù)據(jù)庫(kù)(一般是slave 或者 standby) 與一臺(tái)MySQL數(shù)據(jù)庫(kù)(一般為master或者primary) 進(jìn)行實(shí)時(shí)同步

•  審計(jì)(audit)：用戶可以通過二進(jìn)制日志中的信息來(lái)進(jìn)行審計(jì)，判斷是否有對(duì)數(shù)據(jù)庫(kù)進(jìn)行注入攻擊

開啟二進(jìn)制日志

通過配置參數(shù) log-bin[=name] 可以啟動(dòng)二進(jìn)制日志。如果不指定name,則默認(rèn)二進(jìn)制日志文件名為主機(jī)名，后綴名為二進(jìn)制日志的序列號(hào)

[mysqld]  log-bin

mysql> show variables like 'datadir';  +---------------+-----------------+  | Variable_name | Value           |  +---------------+-----------------+  | datadir       | /var/lib/mysql/ |  +---------------+-----------------+  1 row in set (0.00 sec)

mysqld-bin.000001即為二進(jìn)制日志文件，而mysqld-bin.index為二進(jìn)制的索引文件，為了管理所有的binlog文件，MySQL額外創(chuàng)建了一個(gè)index文件，它按順序記錄了MySQL使用的所有binlog文件。如果你想自定義index文件的名稱，可以設(shè)置 log_bin_index=file參數(shù)。

-rw-rw---- 1 mysql mysql      120 Aug 21 16:42 mysqld-bin.000001  -rw-rw---- 1 mysql mysql       20 Aug 21 16:42 mysqld-bin.index

查看二進(jìn)制日志文件

對(duì)于二進(jìn)制日志文件來(lái)說(shuō)，不像錯(cuò)誤日志文件，慢查詢?nèi)罩疚募菢佑胏at，head, tail等命令可以查看，它需要通過 MySQL 提供的工具 mysqlbinlog。如:

[root@iz2zeaf3cg1099kiidi06mz mysql]# mysqlbinlog mysqld-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  #180821 16:42:53 server id 1  end_log_pos 120 CRC32 0x3e55be40     Start: binlog v 4, server v 5.6.39-log created 180821 16:42:53 at startup  # Warning: this binlog is either in use or was not closed properly.  ROLLBACK/*!*/;  BINLOG '  jdB7Ww8BAAAAdAAAAHgAAAABAAQANS42LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  AAAAAAAAAAAAAAAAAACN0HtbEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUC+  VT4=  '/*!*/;  DELIMITER ;  # End of log file  ROLLBACK /* added by mysqlbinlog */;  /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

二進(jìn)制日志文件配置參數(shù)

下面比較簡(jiǎn)要介紹下二進(jìn)制日志文件幾個(gè)重要的配置參數(shù)

max_binlog_size

可以通過max_binlog_size參數(shù)來(lái)限定單個(gè)binlog文件的大小(默認(rèn)1G)

binlog_cache_size

當(dāng)使用事務(wù)的表存儲(chǔ)引擎(如InnoDB存儲(chǔ)引擎)時(shí)，所有未提交(uncommitted)的二進(jìn)制日志會(huì)被記錄到一個(gè)緩沖中去，等該事務(wù)提交(committed)時(shí)，直接將緩存中的二進(jìn)制日志寫入二進(jìn)制日志文件中，而該緩沖的大小由binlog_cache_size決定，默認(rèn)大小為32K。

此外，binlog_cache_size 是基于會(huì)話(session)的，當(dāng)每一個(gè)線程開啟一個(gè)事務(wù)時(shí)，MySQL會(huì)自動(dòng)分配一個(gè)大小為 binlog_cache_size 的緩存

mysql> show variables like 'binlog_cache_size';  +-------------------+-------+  | Variable_name     | Value |  +-------------------+-------+  | binlog_cache_size | 32768 |  +-------------------+-------+  1 row in set (0.00 sec)

sync_binlog

在默認(rèn)情況下，二進(jìn)制日志并不是在每次寫的時(shí)候同步到磁盤。參數(shù) sync_binlog = [N] 表示每寫緩沖多少次就同步到磁盤。如果將N設(shè)置為1，即 sync_binlog = 1表示采用同步寫磁盤的方式來(lái)寫二進(jìn)制日志，這時(shí)寫操作就不用向上面所說(shuō)的使用操作系統(tǒng)的緩沖來(lái)寫二進(jìn)制日志

binlog_format

binlog_format 參數(shù)十分重要，它影響了記錄二進(jìn)制日志的格式,分為三種格式:

1、statement : 記錄的是日志的邏輯SQL語(yǔ)句

2、row: 記錄表的行更改情況

3、mixed: 在此格式下，mysql默認(rèn)采用statement格式進(jìn)行二進(jìn)制日志文件的記錄，但是有些情況下使用ROW格式，有以下幾種情況:

•  表的存儲(chǔ)引擎為NDB，這時(shí)對(duì)表的DML操作都會(huì)以ROW格式記錄。

•  使用了UUID()、USER()、CURRENT_USER()、FOUND_ROW()、ROW_COUNT()等不確定函數(shù)。

•  使用了INSERT DELAY語(yǔ)句。

•  使用了用戶定義函數(shù)（UDF）。

•  使用了臨時(shí)表（temporary table）。 

到此，關(guān)于“后端開發(fā)必備的MySQL日志文件知識(shí)點(diǎn)有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！