本文轉(zhuǎn)自互聯(lián)網(wǎng)
本系列文章將整理到我在GitHub上的《Java面試指南》倉庫,更多精彩內(nèi)容請到我的倉庫里查看
喜歡的話麻煩點(diǎn)下Star哈
文章首發(fā)于我的個(gè)人博客:
本文是微信公眾號【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ù)秋秤。