本文轉(zhuǎn)自互聯(lián)網(wǎng)

本系列文章將整理到我在GitHub上的《Java面試指南》倉庫，更多精彩內(nèi)容請到我的倉庫里查看

https://github.com/h2pl/Java-Tutorial

喜歡的話麻煩點(diǎn)下Star哈

文章首發(fā)于我的個(gè)人博客：

www.how2playlife.com

本文是微信公眾號【Java技術(shù)江湖】的《重新學(xué)習(xí)MySQL數(shù)據(jù)庫》其中一篇逞姿，本文部分內(nèi)容來源于網(wǎng)絡(luò)，為了把本文主題講得清晰透徹，也整合了很多我認(rèn)為不錯(cuò)的技術(shù)博客內(nèi)容杠河，引用其中了一些比較好的博客文章茅茂，如有侵權(quán)或渤，請聯(lián)系作者旺罢。

該系列博文會告訴你如何從入門到進(jìn)階斯棒，從sql基本的使用方法，從MySQL執(zhí)行引擎再到索引主经、事務(wù)等知識，一步步地學(xué)習(xí)MySQL相關(guān)技術(shù)的實(shí)現(xiàn)原理庭惜，更好地了解如何基于這些知識來優(yōu)化sql罩驻，減少SQL執(zhí)行時(shí)間，通過執(zhí)行計(jì)劃對SQL性能進(jìn)行分析护赊，再到MySQL的主從復(fù)制惠遏、主備部署等內(nèi)容砾跃，以便讓你更完整地了解整個(gè)MySQL方面的技術(shù)體系，形成自己的知識框架节吮。

如果對本系列文章有什么建議抽高，或者是有什么疑問的話，也可以關(guān)注公眾號【Java技術(shù)江湖】聯(lián)系作者透绩，歡迎你參與本系列博文的創(chuàng)作和修訂翘骂。

重新學(xué)習(xí)MySQL數(shù)據(jù)庫10：MySQL里的那些日志們

同大多數(shù)關(guān)系型數(shù)據(jù)庫一樣，日志文件是MySQL數(shù)據(jù)庫的重要組成部分帚豪。MySQL有幾種不同的日志文件碳竟，通常包括錯(cuò)誤日志文件，二進(jìn)制日志狸臣，通用日志莹桅，慢查詢?nèi)罩荆鹊戎蛞唷＿@些日志可以幫助我們定位mysqld內(nèi)部發(fā)生的事件诈泼，數(shù)據(jù)庫性能故障，記錄數(shù)據(jù)的變更歷史煤禽，用戶恢復(fù)數(shù)據(jù)庫等等铐达。本文主要描述錯(cuò)誤日志文件。

1.MySQL日志文件系統(tǒng)的組成

a呜师、錯(cuò)誤日志：記錄啟動娶桦、運(yùn)行或停止mysqld時(shí)出現(xiàn)的問題。 b汁汗、通用日志：記錄建立的客戶端連接和執(zhí)行的語句衷畦。 c、更新日志：記錄更改數(shù)據(jù)的語句知牌。該日志在MySQL 5.1中已不再使用祈争。 d、二進(jìn)制日志：記錄所有更改數(shù)據(jù)的語句角寸。還用于復(fù)制菩混。 e、慢查詢?nèi)罩荆河涗浰袌?zhí)行時(shí)間超過long_query_time秒的所有查詢或不使用索引的查詢扁藕。 f沮峡、Innodb日志：innodb redo log 和undo log

缺省情況下，所有日志創(chuàng)建于mysqld數(shù)據(jù)目錄中亿柑。 可以通過刷新日志邢疙，來強(qiáng)制mysqld來關(guān)閉和重新打開日志文件（或者在某些情況下切換到一個(gè)新的日志）。 當(dāng)你執(zhí)行一個(gè)FLUSH LOGS語句或執(zhí)行mysqladmin flush-logs或mysqladmin refresh時(shí)，則日志被老化疟游。 對于存在MySQL復(fù)制的情形下呼畸，從復(fù)制服務(wù)器將維護(hù)更多日志文件，被稱為接替日志颁虐。

2.錯(cuò)誤日志

錯(cuò)誤日志是一個(gè)文本文件蛮原。 錯(cuò)誤日志記錄了MySQL Server每次啟動和關(guān)閉的詳細(xì)信息以及運(yùn)行過程中所有較為嚴(yán)重的警告和錯(cuò)誤信息。 可以用--log-error[=file_name]選項(xiàng)來開啟mysql錯(cuò)誤日志另绩，該選項(xiàng)指定mysqld保存錯(cuò)誤日志文件的位置儒陨。 對于指定--log-error[=file_name]選項(xiàng)而未給定file_name值，mysqld使用錯(cuò)誤日志名host_name.err 并在數(shù)據(jù)目錄中寫入日志文件板熊。 在mysqld正在寫入錯(cuò)誤日志到文件時(shí)框全，執(zhí)行FLUSH LOGS 或者mysqladmin flush-logs時(shí)，服務(wù)器將關(guān)閉并重新打開日志文件干签。 建議在flush之前手動重命名錯(cuò)誤日志文件津辩，之后mysql服務(wù)將使用原始文件名打開一個(gè)新文件。 以下為錯(cuò)誤日志備份方法： shell> mv host_name.err host_name.err-old shell> mysqladmin flush-logs shell> mv host_name.err-old backup-directory

3.InnoDB中的日志

MySQL數(shù)據(jù)庫InnoDB存儲引擎Log漫游

1 – Undo Log

Undo Log 是為了實(shí)現(xiàn)事務(wù)的原子性容劳，在MySQL數(shù)據(jù)庫InnoDB存儲引擎中喘沿，還用Undo Log來實(shí)現(xiàn)多版本并發(fā)控制(簡稱：MVCC)。

• 事務(wù)的原子性(Atomicity) 事務(wù)中的所有操作竭贩，要么全部完成蚜印，要么不做任何操作，不能只做部分操作留量。如果在執(zhí)行的過程中發(fā)生 了錯(cuò)誤窄赋，要回滾(Rollback)到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過楼熄。

• 原理 Undo Log的原理很簡單忆绰，為了滿足事務(wù)的原子性，在操作任何數(shù)據(jù)之前可岂，首先將數(shù)據(jù)備份到一個(gè)地方 （這個(gè)存儲數(shù)據(jù)備份的地方稱為Undo Log）错敢。然后進(jìn)行數(shù)據(jù)的修改。如果出現(xiàn)了錯(cuò)誤或者用戶執(zhí)行了 ROLLBACK語句缕粹，系統(tǒng)可以利用Undo Log中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開始之前的狀態(tài)稚茅。

除了可以保證事務(wù)的原子性，Undo Log也可以用來輔助完成事務(wù)的持久化平斩。

• 事務(wù)的持久性(Durability) 事務(wù)一旦完成亚享，該事務(wù)對數(shù)據(jù)庫所做的所有修改都會持久的保存到數(shù)據(jù)庫中。為了保證持久性绘面，數(shù)據(jù)庫 系統(tǒng)會將修改后的數(shù)據(jù)完全的記錄到持久的存儲上虹蒋。

• 用Undo Log實(shí)現(xiàn)原子性和持久化的事務(wù)的簡化過程 假設(shè)有A糜芳、B兩個(gè)數(shù)據(jù)，值分別為1,2魄衅。 A.事務(wù)開始. B.記錄A=1到undo log. C.修改A=3. D.記錄B=2到undo log. E.修改B=4. F.將undo log寫到磁盤。 G.將數(shù)據(jù)寫到磁盤塘辅。 H.事務(wù)提交 這里有一個(gè)隱含的前提條件：‘?dāng)?shù)據(jù)都是先讀到內(nèi)存中晃虫，然后修改內(nèi)存中的數(shù)據(jù)，最后將數(shù)據(jù)寫回磁盤’扣墩。

  之所以能同時(shí)保證原子性和持久化哲银，是因?yàn)橐韵绿攸c(diǎn)： A. 更新數(shù)據(jù)前記錄Undo log。 B. 為了保證持久性呻惕，必須將數(shù)據(jù)在事務(wù)提交前寫到磁盤荆责。只要事務(wù)成功提交，數(shù)據(jù)必然已經(jīng)持久化亚脆。 C. Undo log必須先于數(shù)據(jù)持久化到磁盤做院。如果在G,H之間系統(tǒng)崩潰，undo log是完整的濒持， 可以用來回滾事務(wù)键耕。 D. 如果在A-F之間系統(tǒng)崩潰,因?yàn)閿?shù)據(jù)沒有持久化到磁盤。所以磁盤上的數(shù)據(jù)還是保持在事務(wù)開始前的狀態(tài)柑营。

缺陷：每個(gè)事務(wù)提交前將數(shù)據(jù)和Undo Log寫入磁盤屈雄，這樣會導(dǎo)致大量的磁盤IO，因此性能很低官套。

如果能夠?qū)?shù)據(jù)緩存一段時(shí)間酒奶，就能減少IO提高性能。但是這樣就會喪失事務(wù)的持久性奶赔。因此引入了另外一 種機(jī)制來實(shí)現(xiàn)持久化惋嚎，即Redo Log.

2 – Redo Log

• 原理 和Undo Log相反，Redo Log記錄的是新數(shù)據(jù)的備份纺阔。在事務(wù)提交前瘸彤，只要將Redo Log持久化即可， 不需要將數(shù)據(jù)持久化笛钝。當(dāng)系統(tǒng)崩潰時(shí)质况，雖然數(shù)據(jù)沒有持久化，但是Redo Log已經(jīng)持久化玻靡。系統(tǒng)可以根據(jù) Redo Log的內(nèi)容结榄，將所有數(shù)據(jù)恢復(fù)到最新的狀態(tài)。

• Undo + Redo事務(wù)的簡化過程 假設(shè)有A囤捻、B兩個(gè)數(shù)據(jù)臼朗，值分別為1,2. A.事務(wù)開始. B.記錄A=1到undo log. C.修改A=3. D.記錄A=3到redo log. E.記錄B=2到undo log. F.修改B=4. G.記錄B=4到redo log. H.將redo log寫入磁盤。 I.事務(wù)提交

  undo log 保存的是修改前的數(shù)據(jù),并且保存到內(nèi)存中,回滾的時(shí)候在讀取里面的內(nèi)容(從而實(shí)現(xiàn)了原子性),redolog保存的是修改后的數(shù)據(jù)(對新數(shù)據(jù)的備份,同時(shí)也會將redo log備份),在事務(wù)提交寫入到磁盤,從而保證了持久性

4- 慢查詢?nèi)罩?/h3>

概述 　　數(shù)據(jù)庫查詢快慢是影響項(xiàng)目性能的一大因素，對于數(shù)據(jù)庫视哑，我們除了要優(yōu)化 SQL绣否，更重要的是得先找到需要優(yōu)化的 SQL。如何找到低效的 SQL 是寫這篇文章的主要目的挡毅。

MySQL 數(shù)據(jù)庫有一個(gè)“慢查詢?nèi)罩尽惫δ芩獯椋脕碛涗洸樵儠r(shí)間超過某個(gè)設(shè)定值的SQL，這將極大程度幫助我們快速定位到問題所在跪呈，以便對癥下藥段磨。至于查詢時(shí)間的多少才算慢，每個(gè)項(xiàng)目耗绿、業(yè)務(wù)都有不同的要求苹支，傳統(tǒng)企業(yè)的軟件允許查詢時(shí)間高于某個(gè)值，但是把這個(gè)標(biāo)準(zhǔn)放在互聯(lián)網(wǎng)項(xiàng)目或者訪問量大的網(wǎng)站上误阻，估計(jì)就是一個(gè)bug债蜜，甚至可能升級為一個(gè)功能性缺陷。

為避免誤導(dǎo)讀者堕绩，特申明本文的討論限制在 Win 64位 + MySQL 5.6 范圍內(nèi)策幼。其他平臺或數(shù)據(jù)庫種類及版本，我沒有嘗試過奴紧，不做贅述特姐。

設(shè)置日志功能 關(guān)于慢查詢?nèi)罩荆饕婕暗较旅鎺讉€(gè)參數(shù)：

slow_query_log ：是否開啟慢查詢?nèi)罩竟δ埽ū靥睿?long_query_time ：超過設(shè)定值黍氮，將被視作慢查詢唐含，并記錄至慢查詢?nèi)罩疚募校ū靥睿?log-slow-queries ：慢查詢?nèi)罩疚募ú豢商睿詣釉?\data\ 創(chuàng)建一個(gè) [hostname]-slow.log 文件 　　也就是說沫浆，只有滿足以上三個(gè)條件捷枯，“慢查詢功能”才可能正確開啟或關(guān)閉。

5.二進(jìn)制日志

• 主從復(fù)制的基礎(chǔ)：binlog日志和relaylog日志

什么是MySQL主從復(fù)制 簡單來說就是保證主SQL（Master）和從SQL（Slave）的數(shù)據(jù)是一致性的专执，向Master插入數(shù)據(jù)后淮捆，Slave會自動從Master把修改的數(shù)據(jù)同步過來（有一定的延遲），通過這種方式來保證數(shù)據(jù)的一致性本股，就是主從復(fù)制

復(fù)制方式 MySQL5.6開始主從復(fù)制有兩種方式：基于日志（binlog）攀痊、基于GTID（全局事務(wù)標(biāo)示符）。 本文只涉及基于日志binlog的主從配置

復(fù)制原理 1拄显、Master將數(shù)據(jù)改變記錄到二進(jìn)制日志(binary log)中苟径，也就是配置文件log-bin指定的文件，這些記錄叫做二進(jìn)制日志事件(binary log events) 2躬审、Slave通過I/O線程讀取Master中的binary log events并寫入到它的中繼日志(relay log) 3棘街、Slave重做中繼日志中的事件蟆盐，把中繼日志中的事件信息一條一條的在本地執(zhí)行一次，完成數(shù)據(jù)在本地的存儲遭殉，從而實(shí)現(xiàn)將改變反映到它自己的數(shù)據(jù)(數(shù)據(jù)重放)

1石挂、什么是binlog binlog是一個(gè)二進(jìn)制格式的文件，用于記錄用戶對數(shù)據(jù)庫更新的SQL語句信息险污，例如更改數(shù)據(jù)庫表和更改內(nèi)容的SQL語句都會記錄到binlog里誊稚，但是對庫表等內(nèi)容的查詢不會記錄。

默認(rèn)情況下罗心，binlog日志是二進(jìn)制格式的，不能使用查看文本工具的命令（比如城瞎，cat渤闷，vi等）查看，而使用mysqlbinlog解析查看脖镀。

2.binlog的作用 當(dāng)有數(shù)據(jù)寫入到數(shù)據(jù)庫時(shí)飒箭，還會同時(shí)把更新的SQL語句寫入到對應(yīng)的binlog文件里，這個(gè)文件就是上文說的binlog文件蜒灰。使用mysqldump備份時(shí)弦蹂，只是對一段時(shí)間的數(shù)據(jù)進(jìn)行全備，但是如果備份后突然發(fā)現(xiàn)數(shù)據(jù)庫服務(wù)器故障强窖，這個(gè)時(shí)候就要用到binlog的日志了凸椿。

主要作用是用于數(shù)據(jù)庫的主從復(fù)制及數(shù)據(jù)的增量恢復(fù)。

1.啥是binlog? 記錄數(shù)據(jù)庫增刪改,不記錄查詢的二進(jìn)制日志. 2.作用:用于數(shù)據(jù)同步. 3翅溺、如何開啟binlog日志功能 在mysql的配置文件my.cnf中脑漫，增加log_bin參數(shù)即可開啟binlog日志，也可以通過賦值來指定binlog日志的文件名咙崎，實(shí)例如下：

[root@DB02 ~]# grep log_bin /etc/my.cnf log_bin = /application/mysql/logs/dadong-bin

log_bin

[root@DB02 ~]# 提示：也可以按“l(fā)og_bin = /application/mysql/logs/dadong-bin”命名优幸，目錄要存在 為什么要刷新binlog?找到全備數(shù)據(jù)和binlog文件的恢復(fù)臨界點(diǎn).

總結(jié)

mysql數(shù)據(jù)庫的binlog和relay log日志有著舉足輕重的作用，并且relay log僅僅存在于mysql 的slave庫褪猛，它的作用就是記錄slave庫中的io進(jìn)程接收的從主庫傳過來的binlog,然后等待slave庫的sql進(jìn)程去讀取和應(yīng)用网杆，保證主從同步，但是binlog主庫和從庫（slave）都可以存在伊滋，記錄對數(shù)據(jù)發(fā)生或潛在發(fā)生更改的SQL語句碳却，并以二進(jìn)制的形式保存在磁盤，所以可以通過binlog來實(shí)時(shí)備份和恢復(fù)數(shù)據(jù)庫新啼。

1追城、什么是binlog binlog是一個(gè)二進(jìn)制格式的文件，用于記錄用戶對數(shù)據(jù)庫更新的SQL語句信息燥撞，例如更改數(shù)據(jù)庫表和更改內(nèi)容的SQL語句都會記錄到binlog里座柱，但是對庫表等內(nèi)容的查詢不會記錄迷帜。

默認(rèn)情況下，binlog日志是二進(jìn)制格式的色洞，不能使用查看文本工具的命令（比如戏锹，cat，vi等）查看火诸，而使用mysqlbinlog解析查看锦针。

2.binlog的作用 當(dāng)有數(shù)據(jù)寫入到數(shù)據(jù)庫時(shí)，還會同時(shí)把更新的SQL語句寫入到對應(yīng)的binlog文件里置蜀，這個(gè)文件就是上文說的binlog文件奈搜。使用mysqldump備份時(shí)，只是對一段時(shí)間的數(shù)據(jù)進(jìn)行全備盯荤，但是如果備份后突然發(fā)現(xiàn)數(shù)據(jù)庫服務(wù)器故障馋吗，這個(gè)時(shí)候就要用到binlog的日志了。

主要作用是用于數(shù)據(jù)庫的主從復(fù)制及數(shù)據(jù)的增量恢復(fù)秋秤。