涂抹MySQL——跟著三思一步一步學(xué)MySQL

●以前MySQL方面接觸的少，怎么安裝配置才合理？

●用戶和權(quán)限設(shè)計(jì)的這么靈活，怎么玩才安全？

●數(shù)據(jù)需要頻繁導(dǎo)入導(dǎo)出，怎么快速搞定？

●數(shù)據(jù)寫入時(shí)明明都是正常的，讀取出來顯示卻亂碼，到底怎么回事兒？

●MySQL自帶了那么多命令行工具，都能干點(diǎn)什么活？

●保存的數(shù)據(jù)越來越多，怎么保障它們的安全？

●數(shù)據(jù)庫運(yùn)行的越來越慢，想讓它快一點(diǎn)點(diǎn)，應(yīng)該怎么辦？

●單個(gè)實(shí)例承載的壓力太大，怎么將其擴(kuò)展？

●管理的數(shù)據(jù)庫服務(wù)越來越多，怎樣輕松有效地監(jiān)控?cái)?shù)據(jù)庫狀態(tài)？

……

不煩不燥，不困不乏，不徐不疾，MySQL精髓，得之于手。

出任CTO，迎娶白富美，分分鐘的搞定！

這些年一直堅(jiān)持在博客上發(fā)表系列文章，也出版過技術(shù)方面的書，在業(yè)內(nèi)積累了那么一點(diǎn)點(diǎn)知名度，

就有很多朋友慕名而來跟我交流，要向我請(qǐng)教技術(shù)上的問題，咨詢學(xué)習(xí)的技巧，讓我推薦閱讀的圖書等。

朋友們的熱情讓我感到很是忐忑，我雖然寫過一些文章，但都是些通俗的東西，其實(shí)沒什么學(xué)問，而且老

實(shí)講，有些朋友提出的問題真是不容易回答，比如有朋友上來就問我怎么優(yōu)化數(shù)據(jù)庫（這類問題恰恰是最

多的），也有人問我看什么書能達(dá)到我現(xiàn)在的水平等等。

我知道這其中有些人確實(shí)是喜歡并且希望從事DBA 這個(gè)職業(yè)的，他們當(dāng)前只是沒有經(jīng)驗(yàn)才顯得迷茫，

其實(shí)我也是從這個(gè)階段走過來的。多年以前我曾寫過一篇文章《我想對(duì)初學(xué)Oracle 的朋友說》

（http://www.5ienet.com/note/html/stdstep/how-to-learn-in-oracle.shtml），其中的內(nèi)容也是我有感而發(fā)。因?yàn)?/p>

了解DBA 成長道路上的艱辛，我不愿意隨口敷衍他們幾句，使他們走了彎路。可是因?yàn)樽约旱乃�平有限�?/p>

我又難以簡(jiǎn)單幾句話就說明白，有時(shí)候拉拉扯扯說了一堆，反倒是提問者自己不耐煩，甚至還有朋友直接

了當(dāng)?shù)刂肛?zé)我不愿意分享（這個(gè)殺傷力太大，我的文章都白寫了嗎），整的我的心里也很受傷，對(duì)于這樣

的人，我當(dāng)機(jī)立斷大喝一聲：“你是猴子搬來的救兵嗎？”。不過我知道，多數(shù)人還是希望通過不斷學(xué)習(xí)獲

得提高，看到他們就像看到曾經(jīng)的我，我真心想幫助這些朋友。

說起讀書和學(xué)習(xí)，國內(nèi)外不少前輩先賢都有論述的文章，先我們耳熟能詳?shù)模骸皩W(xué)而不思則罔，思而

不學(xué)則殆；知之為知之，不知為不知……”等出自《論語》的至理名言就已流傳千古，我一個(gè)剛及而立的

后學(xué)晚輩，竟然也敢以《論》起談，豈不是在自暴已丑，是要叫人笑掉大牙的嘛。但是我想，圣賢們的文

章高瞻遠(yuǎn)矚，高屋建瓴，高處不勝寒哪，以至于應(yīng)者寥寥，我基于自身實(shí)力水平，也想借這個(gè)場(chǎng)合，站在

稍低一層的層次，談一談這些年我學(xué)習(xí)過程中的一些感受。

先從一個(gè)典故開始吧。

春秋五霸之一的齊桓公，一次在堂上讀書，堂下一名叫輪扁的技工師傅看到了，就放下手上的工作，

走到堂上問桓公：“請(qǐng)問桓公在看什么書？”

齊桓公回答他說：“這是圣人的書。”

輪扁接著問：“圣人在哪呢？”

齊桓公回答：“圣人已經(jīng)死了！”

輪扁說道：“那桓公所讀的，不過是古人留下來的糟粕罷了。”

輪扁斫輪（自序）

齊桓公聞之怒道：“寡人讀書，豈是你這個(gè)做車輪的工匠可以議論的嗎，今天你要是能說出道理還則

罷了，要是說不出來，明年的今天就是你的忌日（好吧，我承認(rèn)武俠片看多了，這臺(tái)詞管不住自己都往外

蹦）。”

輪扁于是說道：“我是通過我平常工作觀察到的情況來理解的，給車做轱轆雖然不是高級(jí)崗位，但也

是個(gè)技術(shù)工種。在做車輪的時(shí)候，如果輪孔弄的太寬，那么雖然車輪能做的很光滑但用起來并不牢固，如

果輪孔弄的過緊，車輪又會(huì)很粗糙難以裝配；因此只有得心應(yīng)手，不緊不松才能做出高端大氣上檔次的車

輪。可是要如何做到得心應(yīng)手呢，我做輪子的時(shí)候知道存在這樣一種境界，但用嘴又說不清楚，我甚至都

沒辦法將這點(diǎn)技巧傳授給我的兒子，我的兒子也沒辦法從我這兒學(xué)到這一點(diǎn)，所以雖然我都七十多了還得

在這兒做輪子。因此我想說的是，古人和他們那些不能言傳的東西想必也早一起都死去了，所以桓公所讀

的，不過是古人留下來的糟粕而已！”

文中沒有寫明輪扁老師傅結(jié)局如何，但是想來老人家還是有極大的幾率繼續(xù)給桓公做車輪子的，輪扁

（當(dāng)然其實(shí)是莊子老人家借輪扁的口）講的很有道理，找個(gè)熟練工不容易啊。

讀到這里，我估摸著有些朋友已經(jīng)在暗自嘀咕：看我這意思，似乎是在宣揚(yáng)讀書無用論了喲，出版社

負(fù)責(zé)審校的同學(xué)，恐怕也已準(zhǔn)備著把我這段序文刪掉。都別著急，毛主席一直教導(dǎo)我們，要用辯證的眼光

看待問題。古人說的不一定都對(duì)，我覺著問題的關(guān)鍵不在于看還是不看書，關(guān)鍵點(diǎn)首先是所閱讀的圖書質(zhì)

量，作者有沒有把要表達(dá)的意思闡述清楚，其次是讀者們有沒有認(rèn)真閱讀，獨(dú)立思考，真正領(lǐng)會(huì)作者想要

表達(dá)的思想。

盡管時(shí)下寫文字有種種限制，但是得益于近些年出版行業(yè)發(fā)達(dá)，現(xiàn)如今世面上講經(jīng)驗(yàn)、談技巧、擺案

例類的圖書紛雜涌現(xiàn)，對(duì)于IT 技術(shù)領(lǐng)域這類圖書就更多了（因?yàn)镮T 行業(yè)本就是門實(shí)踐性很強(qiáng)的技術(shù)）。

在書中應(yīng)對(duì)案例中出現(xiàn)的故障，作者們自己往往駕輕就熟，揮灑之間數(shù)千字，似乎也講的透徹，但是初學(xué)

者朋友可能看的云里霧里似懂非懂而不自知。仿佛學(xué)到了什么，但真正應(yīng)對(duì)故障時(shí)卻手忙腳亂，場(chǎng)景稍有

變化甚至都不知道從何處著手處理。這種情況若對(duì)應(yīng)到《輪扁斫輪》這則典故，說明操作者還沒有達(dá)到“不

徐不疾，得之于手而應(yīng)于心”的境界。

讀者朋友們認(rèn)真看過書中的內(nèi)容，可是實(shí)際工作中卻不能很好的應(yīng)用，這究竟是什么緣故呢，我想大

概就是前面所說的兩點(diǎn)關(guān)鍵因素，詳細(xì)說來如下：

 其一：可能作者沒有（想或不想）把真正的精髓寫出來。目前比較暢銷的IT 圖書，其作者大都

這則典故出自《莊子外篇·天道十三》，叫做《輪扁斫輪》，原文如下：“桓公讀書于堂上，輪扁

斫輪于堂下，釋椎鑿而上，問桓公曰：“敢問：公之所讀者，何言邪？”公曰：“圣人之言也。”曰：

“圣人在乎？”公曰：“已死矣。”曰：“然則君之所讀者，古人之糟粕已夫！”桓公曰：“寡人讀書，

輪人安得議乎！有說則可，無說則死！”輪扁曰：“臣也以臣之事觀之。斫輪，徐則甘而不固，疾則

苦而不入，不徐不疾，得之于手而應(yīng)于心，口不能言，有數(shù)存焉于其間。臣不能以喻臣之子，臣之

子亦不能受之于臣，是以行年七十而老斫輪。古之人與其不可傳也死矣，然則君之所讀者，古人之

糟粕已夫！”

提 示

是來自一線的工程師，隨著這些年圖書出版門檻的降低，很多人有機(jī)會(huì)能將自己工作學(xué)習(xí)過程中

積累的經(jīng)驗(yàn)寫出來（我認(rèn)為這也是國內(nèi)IT 圖書中少見思想類圖書的原因）。優(yōu)秀的IT 工程師都

是出色的實(shí)踐者，他們技術(shù)掌握的比較扎實(shí)，接觸面廣，經(jīng)驗(yàn)豐富，當(dāng)遇到問題時(shí)，處理的方法

往往都是下意識(shí)的選擇，沒有為什么，就是要這么做。在寫作的時(shí)候也是下意識(shí)就將過程寫了出

來，而沒能把思路闡釋清楚。

 其二：并非書寫的不好，可能由于讀者自身層次的原因，沒能正確理解作者表達(dá)的精髓。對(duì)于讀

者來說，找到一本好書難，讀透一本好書更難。過去有一種說法叫做：“書讀百遍，其意自現(xiàn)”，

我覺著這點(diǎn)在IT 技術(shù)領(lǐng)域的局限是很大的，雖然任誰也不能否認(rèn)閱讀的作用是巨大的，可是，

正如我前面談到的，IT 行業(yè)是一門實(shí)踐性非常強(qiáng)的技術(shù)，按照過去的老話講，IT 工程師也是個(gè)

手藝人。因?yàn)樾袠I(yè)的特點(diǎn)，看的懂和做的到是兩碼事，盡管每讀一遍都會(huì)有新的理解，但是這種

理解必須要與實(shí)踐相結(jié)合才能發(fā)揮最大的威力。

對(duì)此，莊子老人家其實(shí)也早已高度抽象地概括為：視而可見者，形與色也；聽而可聞?wù)撸�名與聲也。

世人以形色名聲為足以得彼之情。夫形色名聲，果不足以得彼之情，則知者不言，言者不知，而世豈識(shí)

之哉！

要想學(xué)的好，首先所讀的書必須擁有比較高的質(zhì)量，其次學(xué)習(xí)也必須能學(xué)到書中的精華，所以您瞧，

有多種因素可能會(huì)給學(xué)習(xí)的質(zhì)量造成干擾。再舉這樣一個(gè)案例，DBA 管理的系統(tǒng)出現(xiàn)響應(yīng)慢的情況，通

過分析發(fā)現(xiàn)是由于之前執(zhí)行的某項(xiàng)操作，正是該操作占用了過多的資源才導(dǎo)致系統(tǒng)響應(yīng)變慢，針對(duì)這種情

況怎么處理呢。一定有些資料中提到，要?dú)⒌粽加眠^多資源的進(jìn)程，以釋放資源，提高系統(tǒng)的響應(yīng)效率，

并且有實(shí)際的案例佐證此方案的有效。于是在這個(gè)場(chǎng)景中，DBA 為了緩解系統(tǒng)負(fù)載壓力，利用之前看過

的材料中提到的方法，手動(dòng)殺掉了持有該操作的進(jìn)程。

若僅把所執(zhí)行的操作為獨(dú)立個(gè)體來看，這當(dāng)然是個(gè)很好的案例，有可能系統(tǒng)負(fù)載立刻就得到了明顯下

降，但問題有沒有得到真正解決呢？深層次的根源究竟又是什么呢？如果沒有弄清楚這些情況，那么所做

的操作有可能不起效果（這就算好消息了），甚至有可能充滿了風(fēng)險(xiǎn)。因?yàn)椴皇撬�有占用較多資源的進(jìn)程

都是不正常的，也不是所有進(jìn)程都能隨便中止，不管遇到的是什么問題，能找出造成問題的關(guān)鍵所在最重

要。元芳，你怎么看！

不管要學(xué)習(xí)哪方面的知識(shí)，在學(xué)習(xí)過程中可參考的資料會(huì)有很多，在互聯(lián)網(wǎng)時(shí)代更是可以用浩瀚來形

容，這種現(xiàn)狀換個(gè)角度看反倒更令讀者們無所適從，不知道該選擇看哪些資料好。若讓我來選擇，首要推

薦的仍然是官方提供的技術(shù)文檔，對(duì)于Oralce 數(shù)據(jù)庫可以到tahiti.oracle.com 瀏覽，對(duì)于MySQL 數(shù)據(jù)庫

可以到dev.mysql.com/doc 瀏覽，官方文檔始終都是內(nèi)容最權(quán)威、最全面的學(xué)習(xí)資料，恒久遠(yuǎn)永流傳。對(duì)

于有一定經(jīng)驗(yàn)的朋友，可能會(huì)認(rèn)為官方文檔的深度不夠，案例也少，對(duì)于這部分朋友，可以去看一些專門

的文章和圖書，在選擇圖書時(shí)只有一個(gè)準(zhǔn)則，就是要讀有口碑的書。目前各大網(wǎng)絡(luò)商城都有評(píng)分和評(píng)論系

統(tǒng)，購買前先看一下其他用戶的意見作為參考會(huì)很有幫助。

我讀過的很多圖書，文章開篇總要吹些牛皮，吸引讀者的眼球，把讀者的胃口調(diào)起來，希望大家能有

興趣接著往后看（歡迎對(duì)號(hào)入座）。像三思這種開篇不僅自我貶低，且一槍打擊一大片的，怕著實(shí)不多見，

我想這跟我的性格有關(guān)—耿直（好吧！我承認(rèn)其實(shí)是情商低），也跟我所從事的職業(yè)有關(guān)—技術(shù)，來

不得半點(diǎn)兒虛假。

好了，講到這里，是時(shí)候跟大家介紹下本書的內(nèi)容了（分明是要開始王婆賣瓜自賣自夸），這并不是

一本逐個(gè)介紹MySQL 命令的書，不是一本用戶幫助手冊(cè)，不是這個(gè)功能講完講那個(gè)功能的書。在寫作之

初我就考慮要設(shè)定一條主線，不是依次講特性，而是依據(jù)用戶接觸和學(xué)習(xí)MySQL 的脈絡(luò)去把握，介紹

MySQL 數(shù)據(jù)庫從無到有，其安裝、配置、管理、優(yōu)化的過程，在這個(gè)過程中再穿插數(shù)據(jù)導(dǎo)入導(dǎo)出、性能

/狀態(tài)監(jiān)控、備份恢復(fù)和優(yōu)化方面的內(nèi)容，最后再談一談MySQL 數(shù)據(jù)庫服務(wù)從單臺(tái)到多臺(tái)，從單實(shí)例到

多實(shí)例集群的部署等稍顯高階的應(yīng)用方案。

說起來，這其實(shí)是一本站在初學(xué)者的視角，描述他不斷學(xué)習(xí)和提高的路徑的圖書，在這個(gè)過程中，我

當(dāng)然不可能面面俱到地講到所有的技術(shù)特性，不過在介紹某些知識(shí)點(diǎn)時(shí)，會(huì)有意地忽略一些細(xì)節(jié)，是希望

能讓讀者有思考的空間，既能看到優(yōu)勢(shì)同時(shí)也學(xué)會(huì)看到不足，找出更適合自己的解決方案，逐漸形成自己

的操作思路，窺見“不徐不疾，得之于手而應(yīng)于心”的境界。

最后，我想說的是，官方文檔也好，技術(shù)圖書也好，這些都是外在因素，最重要的因素仍然是自己，

是否真正喜歡所要學(xué)習(xí)的技術(shù)，是否確實(shí)愿意花費(fèi)時(shí)間和精力去深入研究，是否能夠承受枯燥的應(yīng)用和測(cè)

試。只要打好了基礎(chǔ)，看多了案例，精通了技能，學(xué)好了本領(lǐng)，明了方方面面前因后果，用不了多久，就

可以成為大拿，升職加薪，當(dāng)上技術(shù)總監(jiān)，出任CTO，迎娶白富美，登上人生頂峰！是不是想想都激動(dòng)

啊！小伙伴兒們，那就從現(xiàn)在開始吧，翻開第一頁，MySQL 在向你招手。

第1 章 開源運(yùn)動(dòng)與開源軟件MySQL ··········· 1
1.1 開源軟件的故事 ·········································· 1
1.1.1 GNU 說，我代表著一個(gè)夢(mèng)想 ··········· 3
1.1.2 FSF 說，兄弟我頂你 ·························· 4
1.1.3 兄弟，你是“自由軟件”嗎 ············· 4
1.1.4 GPL 說，持證上崗光榮 ····················· 5
1.1.5 開源軟件說，隊(duì)長別開槍，
咱們是一伙的 ······································ 6
1.2 MySQL 的悄然而至 ···································· 7
1.2.1 起源 ······················································· 7
1.2.2 根據(jù)地成立 ·········································· 9
1.2.3 快速發(fā)展，大踏步向前······················ 9
1.2.4 世事難料，不經(jīng)歷風(fēng)雨怎能
見彩虹 ················································· 12
1.2.5 向前向前向前 ···································· 14
1.2.6 以開源的心態(tài)學(xué)開源 ························ 15
第2 章 安裝MySQL 數(shù)據(jù)庫軟件 ··············· 17
2.1 Windows 平臺(tái)安裝 ···································· 18
2.1.1 安裝包方式安裝 ································ 19
2.1.2 壓縮包方式安裝 ································ 28
2.1.3 Windows 平臺(tái)的一些限制 ··············· 29
2.2 Linux 平臺(tái)安裝 ·········································· 30
2.2.1 RPM 包方式安裝 ······························ 31
2.2.2 源碼編譯方式安裝 ···························· 37
2.2.3 二進(jìn)制包方式安裝 ···························· 40
第3 章 管理MySQL 數(shù)據(jù)庫服務(wù) ·············· 45
3.1 Windows 平臺(tái)下的MySQL 服務(wù) ············ 46
3.2 Linux 平臺(tái)下的MySQL 服務(wù) ·················· 47
3.2.1 創(chuàng)建數(shù)據(jù)庫服務(wù) ································ 47
3.2.2 啟動(dòng)數(shù)據(jù)庫服務(wù) ································ 51
3.2.3 配置MySQL 數(shù)據(jù)庫 ························· 51
3.3 MySQL 服務(wù)管理配置 ······························ 54
3.3.1 創(chuàng)建管理腳本····································· 55
3.3.2 開機(jī)自動(dòng)啟動(dòng)····································· 56
第4 章 管理MySQL 庫與表 ····················· 58
4.1 上帝說，要有庫 ········································· 58
4.1.1 說刪咱就刪 ········································· 60
4.1.2 說建咱就建 ········································· 61
4.2 上帝說，要有表 ········································· 64
4.2.1 想建咱就建 ········································· 66
4.2.2 想看咱就看 ········································· 71
4.2.3 想改咱就改 ········································· 74
4.2.4 想刪咱就刪 ········································· 78
第5 章 MySQL 數(shù)據(jù)庫中的權(quán)限體系 ········ 81
5.1 談?wù)剻?quán)限處理邏輯 ····································· 81
5.1.1 能不能連接 ········································· 81
5.1.2 能不能執(zhí)行操作 ································ 82
目 錄
5.1.3 權(quán)限變更何時(shí)生效 ···························· 82
5.2 權(quán)限授予與回收 ········································ 83
5.2.1 創(chuàng)建用戶············································· 84
5.2.2 授予權(quán)限············································· 91
5.2.3 查看和收回用戶權(quán)限 ························ 95
5.2.4 刪除用戶············································· 98
5.3 權(quán)限級(jí)別 ····················································· 99
5.3.1 全局 ····················································· 99
5.3.2 數(shù)據(jù)庫 ··············································· 103
5.3.3 表 ······················································· 108
5.3.4 列 ······················································· 110
5.3.5 程序 ··················································· 112
5.4 賬戶安全管理 ··········································· 113
5.4.1 用戶與權(quán)限設(shè)定原則 ······················ 113
5.4.2 小心歷史文件泄密 ·························· 114
5.4.3 管理員口令丟失怎么辦·················· 115
第6 章 字符，還有個(gè)集 ·························· 118
6.1 基礎(chǔ)掃盲 ··················································· 118
6.1.1 關(guān)于字符集 ······································ 119
6.1.2 關(guān)于校對(duì)規(guī)則 ·································· 120
6.2 支持的字符集和校對(duì)規(guī)則 ······················ 120
6.3 指定字符集和校對(duì)規(guī)則 ·························· 123
6.3.1 服務(wù)端設(shè)置默認(rèn)字符集·················· 124
6.3.2 連接時(shí)指定 ······································ 126
6.3.3 保存時(shí)指定 ······································ 132
6.4 字符集操作示例 ······································ 136
6.5 角落里的字符集設(shè)置 ······························ 139
6.5.1 字符串的字符集 ······························ 139
6.5.2 錯(cuò)誤提示的字符集 ·························· 140
6.5.3 國家字符集 ······································ 142
第7 章 選擇對(duì)象的存儲(chǔ)引擎 ··················· 144
7.1 存儲(chǔ)引擎體系結(jié)構(gòu) ·································· 145
7.2 常見存儲(chǔ)引擎 ··········································· 148
7.2.1 MEMORY 存儲(chǔ)引擎 ·······················149
7.2.2 CSV 存儲(chǔ)引擎 ··································152
7.2.3 ARCHIVE 存儲(chǔ)引擎 ·······················153
7.2.4 BLACKHOLE 存儲(chǔ)引擎·················154
7.2.5 MERGE 存儲(chǔ)引擎 ···························157
7.2.6 FEDERATED 存儲(chǔ)引擎 ·················159
7.3 MyISAM 存儲(chǔ)引擎 ··································165
7.3.1 MyISAM 引擎特性··························167
7.3.2 MyISAM 引擎存儲(chǔ)格式 ·················169
7.4 InnoDB 存儲(chǔ)引擎 ·····································171
7.4.1 默認(rèn)的存儲(chǔ)引擎 ······························173
7.4.2 InnoDB 引擎配置 ····························175
7.4.3 創(chuàng)建和使用InnoDB 表對(duì)象 ···········182
7.4.4 邏輯存儲(chǔ)結(jié)構(gòu)···································185
7.4.5 多版本機(jī)制 ·······································188
7.4.6 聯(lián)機(jī)修改表對(duì)象結(jié)構(gòu) ······················189
7.4.7 InnoDB 表對(duì)象的限制條件 ············201
第8 章 MySQL 數(shù)據(jù)庫文件結(jié)構(gòu) ············· 204
8.1 初始化選項(xiàng)文件 ·······································204
8.2 錯(cuò)誤日志文件 ···········································208
8.3 查詢?nèi)罩疚募?···········································209
8.3.1 慢查詢?nèi)罩?·······································209
8.3.2 普通查詢?nèi)罩尽ぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁ?11
8.3.3 配置查詢?nèi)罩尽ぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁぁ?12
8.4 二進(jìn)制日志文件 ·······································215
8.4.1 這個(gè)必須有 ·······································215
8.4.2 它不是隨便的人 ······························216
8.4.3 想說懂你不容易 ······························217
8.5 中繼日志及復(fù)制狀態(tài)文件 ······················219
8.6 表對(duì)象數(shù)據(jù)文件 ·······································221
8.7 其他文件 ···················································221
8.7.1 進(jìn)程id 文件······································221
8.7.2 套接字文件 ·······································222
8.7.3 自動(dòng)配置文件···································222
第9 章 數(shù)據(jù)導(dǎo)出與導(dǎo)入 ·························· 223
9.1 利用CSV 存儲(chǔ)引擎加載數(shù)據(jù) ················ 223
9.2 mysqlimport 命令行工具導(dǎo)入數(shù)據(jù) ········ 224
9.2.1 導(dǎo)入超簡(jiǎn)單 ······································ 225
9.2.2 分列超輕松 ······································ 226
9.2.3 換行很容易 ······································ 228
9.3 SQL 語句導(dǎo)入數(shù)據(jù) ·································· 229
9.3.1 快來認(rèn)識(shí)下LOAD DATA INFILE · 230
9.3.2 字符集咋處理的吶 ·························· 232
9.3.3 要導(dǎo)入的數(shù)據(jù)文件放哪兒 ············· 234
9.3.4 數(shù)據(jù)文件的前N 行記錄不想導(dǎo)
咋辦 ··················································· 236
9.3.5 列和行的精確處理 ·························· 236
9.3.6 對(duì)象結(jié)構(gòu)與數(shù)據(jù)文件不符咋整 ····· 246
9.4 SQL 語句導(dǎo)出數(shù)據(jù) ·································· 249
9.4.1 這些知識(shí)，不學(xué)都會(huì) ······················ 250
9.4.2 這些知識(shí)，一學(xué)就會(huì) ······················ 251
第10 章 MySQL 數(shù)據(jù)備份和數(shù)據(jù)恢復(fù) ····· 254
10.1 備份與恢復(fù)名詞解釋 ···························· 254
10.1.1 物理備份VS 邏輯備份 ················ 255
10.1.2 聯(lián)機(jī)備份VS 脫機(jī)備份 ················ 256
10.1.3 本地備份VS 遠(yuǎn)程備份 ················ 257
10.1.4 完整備份VS 增量備份 ················ 257
10.1.5 完整恢復(fù)VS 增量恢復(fù) ················ 258
10.2 備份工具知多少 ···································· 258
10.2.1 復(fù)制表對(duì)象相關(guān)文件的方式
創(chuàng)建備份集 ···································· 258
10.2.2 使用mysqlhotcopy 命令行工具
創(chuàng)建備份········································· 259
10.2.3 使用mysqldump 命令行工具
創(chuàng)建邏輯備份 ································ 259
10.2.4 使用SQL 語句創(chuàng)建備份 ·············· 260
10.2.5 冷復(fù)制方式創(chuàng)建物理備份 ··········· 260
10.2.6 二進(jìn)制日志創(chuàng)建增量備份············260
10.2.7 第三方工具創(chuàng)建聯(lián)機(jī)備份············260
10.3 Hey Jude, Don't be afraid, 備份咱有
mysqldump ··············································261
10.3.1 單個(gè)數(shù)據(jù)庫的備份任務(wù) ················261
10.3.2 備份多個(gè)數(shù)據(jù)庫 ····························263
10.3.3 輸出定界格式文件 ························263
10.3.4 恢復(fù)mysqldump 創(chuàng)建的備份集 ··265
10.3.5 多學(xué)些mysqldump 命令行參數(shù) ··266
10.3.6 自動(dòng)化備份策略 ····························272
10.4 冷備、增量備和備份恢復(fù)策略 ············275
10.4.1 創(chuàng)建冷備份·····································275
10.4.2 創(chuàng)建增量備份 ································276
10.4.3 備份和恢復(fù)策略 ····························278
10.5 XtraBackup 聯(lián)機(jī)備份 ····························280
10.5.1 關(guān)于XtraBackup ····························280
10.5.2 先試試xtrabackup 命令 ················282
10.5.3 再用用innobackupex 命令 ···········284
10.5.4 創(chuàng)建增量備份 ································287
10.5.5 執(zhí)行恢復(fù) ·········································290
10.5.6 打包和壓縮備份集 ························295
10.5.7 自動(dòng)化備份腳本 ····························296
第11 章 MySQL 復(fù)制特性 ····················· 298
11.1 創(chuàng)建復(fù)制環(huán)境 ·········································300
11.1.1 最簡(jiǎn)單的復(fù)制環(huán)境部署方法 ·······300
11.1.2 復(fù)制環(huán)境配置寶典 ························307
11.1.3 常用的復(fù)制環(huán)境管理命令············310
11.2 復(fù)制特性的實(shí)施原理和關(guān)鍵因素········315
11.2.1 復(fù)制格式 ·········································315
11.2.2 中繼日志文件和狀態(tài)文件············319
11.2.3 復(fù)制過濾規(guī)則 ································322
11.3 高級(jí)應(yīng)用技巧 ·········································332
11.3.1 通過XtraBackup 創(chuàng)建Slave
節(jié)點(diǎn) ·················································333
11.3.2 利用Slave 節(jié)點(diǎn)創(chuàng)建備份 ············ 336
11.3.3 部署級(jí)聯(lián)Slave 增強(qiáng)復(fù)制性能 ···· 340
11.3.4 半同步機(jī)制 ···································· 343
11.3.5 復(fù)制環(huán)境中的故障切換 ··············· 348
11.3.6 延遲復(fù)制 ········································ 352
第12 章 五花八門的MySQL 管理工具 ···· 354
12.1 這些年MySQL 提供的命令行工具 ···· 354
12.1.1 mysql_install_db—MySQL
建庫工具········································· 355
12.1.2 mysqld_safe—MySQL 啟動(dòng)
工具 ················································· 356
12.1.3 mysqld—MySQL 主進(jìn)程 ········· 357
12.1.4 mysqld_multi—MySQL 多實(shí)例
管理工具········································· 360
12.1.5 mysql—專業(yè)命令行工具 ········· 362
12.1.6 mysqladmin—管理工具 ··········· 368
12.1.7 其他常用命令 ································ 371
12.2 phpMyAdmin ·········································· 372
12.2.1 安裝phpMyAdmin ························ 372
12.2.2 配置phpMyAdmin ························ 376
12.2.3 試用phpMyAdmin ························ 379
12.3 MySQL Workbench ······························· 383
12.3.1 執(zhí)行SQL 查詢 ······························ 384
12.3.2 數(shù)據(jù)建模 ········································ 386
12.3.3 服務(wù)管理 ········································ 391
12.4 其他第三方圖形管理工具 ···················· 394
第13 章 性能調(diào)優(yōu)與診斷 ························ 396
13.1 測(cè)試方法 ················································· 397
13.1.1 關(guān)鍵性指標(biāo) ···································· 398
13.1.2 獲取關(guān)鍵性指標(biāo) ···························· 402
13.1.3 TPCC 測(cè)試 ····································· 411
13.2 數(shù)據(jù)庫參數(shù)配置優(yōu)化 ···························· 416
13.2.1 連接相關(guān)參數(shù) ································ 417
13.2.2 文件相關(guān)參數(shù) ································418
13.2.3 緩存控制參數(shù) ································420
13.2.4 MyISAM 專用參數(shù)························423
13.2.5 InnoDB 專用參數(shù) ··························425
13.2.6 參數(shù)優(yōu)化案例 ································428
13.3 分析慢查詢?nèi)罩?·····································432
13.3.1 mysqldumpslow 命令·····················433
13.3.2 mysqlsla 命令 ·································434
13.4 關(guān)注系統(tǒng)狀態(tài) ·········································438
13.4.1 MySQL 服務(wù)在做什么 ··················438
13.4.2 MySQL 語句在做什么 ··················440
13.4.3 實(shí)戰(zhàn)優(yōu)化案例 ································448
第14 章 部署MySQL 服務(wù)監(jiān)控平臺(tái) ······· 451
14.1 監(jiān)控狀態(tài)，我用Nagios ························452
14.1.1 初始化環(huán)境·····································452
14.1.2 初識(shí)監(jiān)控項(xiàng)·····································454
14.1.3 配置監(jiān)控項(xiàng)·····································460
14.1.4 監(jiān)控服務(wù)列表 ································462
14.2 監(jiān)控性能，我有Cacti ···························464
14.2.1 初始化環(huán)境與安裝Cacti ··············466
14.2.2 配置MySQL 監(jiān)控模板 ·················474
14.2.3 監(jiān)控MySQL 實(shí)例 ·························478
第15 章 搭建MySQL 高可用體系 ·········· 483
15.1 追求更高穩(wěn)定性的服務(wù)體系 ················483
15.1.1 可擴(kuò)展性 ·········································484
15.1.2 高可用性 ·········································485
15.2 Slave+LVS+Keepalived 實(shí)現(xiàn)高可用 ···488
15.2.1 配置LVS ········································489
15.2.2 配置RealServer······························491
15.2.3 增加高可用能力 ····························494
15.3 Dual-Master 高可用環(huán)境 ·······················497
15.3.1 故障隨便切換 ································498
15.3.2 IP 自動(dòng)飄移 ····································504
15.3.3 架構(gòu)設(shè)計(jì)有講究 ···························· 510
15.4 DRBD，為Master 節(jié)點(diǎn)數(shù)據(jù)提供
更高保障 ················································· 512
15.4.1 基礎(chǔ)知識(shí)掃掃盲 ···························· 512
15.4.2 一個(gè)好漢多個(gè)幫 ···························· 515
15.5 官方集群正統(tǒng)MySQL Cluster ············· 518
15.5.1 Cluster 體系結(jié)構(gòu)概述 ···················518
15.5.2 Cluster 安裝與配置························520
15.5.3 Cluster 應(yīng)用初體驗(yàn)························524
15.6 繼續(xù)擴(kuò)展數(shù)據(jù)庫服務(wù) ····························527
15.6.1 該拆分時(shí)要拆分 ····························528
15.6.2 處理策略得想清 ····························532

1. 數(shù)據(jù)庫技術(shù)與應(yīng)用實(shí)踐教程（SQL Server 2019） [主 編 嚴(yán)暉 周肆清]
2. 數(shù)據(jù)庫原理與應(yīng)用——基于SQL Server 2016 [主編 彭浩 黃勝 鄒競(jìng)]
3. Access數(shù)據(jù)庫基礎(chǔ)教程實(shí)驗(yàn)指導(dǎo)與習(xí)題集 [主 編 饒拱維 楊貴茂 吳華光]
4. Access 數(shù)據(jù)庫基礎(chǔ)教程 [主編 饒拱維 楊貴茂 郭其標(biāo)]
5. 數(shù)據(jù)庫原理及應(yīng)用（MySQL版） [主編 高亮 韓玉民]
6. 數(shù)據(jù)庫技術(shù)與應(yīng)用（SQL Server 2008版）（第二版） [主編 嚴(yán)暉 王小玲]
7. 數(shù)據(jù)庫技術(shù)與應(yīng)用實(shí)踐教程（SQL Server 2008）（第二版） [主編 嚴(yán)暉 周肆清]
8. Access 2010數(shù)據(jù)庫應(yīng)用教程（第二版） [主編 李亞]
9. Access 2010數(shù)據(jù)庫實(shí)用教程 [主編 張明 宣繼濤]
10. 數(shù)據(jù)庫原理及應(yīng)用——SQL Server 2012 [主編 賴玲 李祥琴 胡秀]
11. 數(shù)據(jù)庫原理及應(yīng)用上機(jī)指導(dǎo)與習(xí)題解答——SQL Server 2012 [主編 王婭紛 賴玲 李祥琴 胡]
12. Visual FoxPro數(shù)據(jù)庫程序設(shè)計(jì)教程（第四版） [主編 王鳳領(lǐng)]
13. 數(shù)據(jù)庫系統(tǒng)原理與應(yīng)用 [主編 沈祥玖 張?jiān)繻
14. Access 2010數(shù)據(jù)庫程序設(shè)計(jì) [主編 紀(jì)澍琴 于雪晶]
15. Access 2010實(shí)做教程 [主編 王益斌 趙欣]
16. 數(shù)據(jù)庫系統(tǒng)原理與應(yīng)用（第三版）（SQL Server 2012） [主編 劉淳]
17. 數(shù)據(jù)庫技術(shù)（第二版）——Access 2010及其應(yīng)用系統(tǒng)開發(fā) [李禹生 李承犁 劉淵源 等編著]
18. Access 2010數(shù)據(jù)庫技術(shù)基礎(chǔ)及應(yīng)用 [主編 饒拱維 楊貴茂]
19. 數(shù)據(jù)庫技術(shù)及應(yīng)用開發(fā)學(xué)習(xí)輔導(dǎo) [李云峰 李婷 編著]
20. Access應(yīng)用技術(shù)基礎(chǔ)教程（2010版） [主編 何春林 宋運(yùn)康]
21. Access應(yīng)用技術(shù)實(shí)驗(yàn)指導(dǎo)（2010版） [主編 何春林 宋運(yùn)康]
22. Access 2010數(shù)據(jù)庫技術(shù)與應(yīng)用教程習(xí)題及實(shí)驗(yàn)指導(dǎo) [主編 陳朝華 肖東]
23. Access 2010數(shù)據(jù)庫技術(shù)與應(yīng)用教程 [主編 趙義霞 季軍杰]
24. Visual FoxPro程序設(shè)計(jì) [主編 何櫻]
25. Visual FoxPro程序設(shè)計(jì)教程（第二版）習(xí)題集與解答 [主編 何振林 歐劍]
26. Visual FoxPro程序設(shè)計(jì)教程（第二版） [主編 何振林 趙亮]
27. 數(shù)據(jù)庫技術(shù)及應(yīng)用開發(fā) [李云峰 李 婷 編著]
28. Access 數(shù)據(jù)庫案例教程（第二版） [主編 應(yīng)紅]
29. Access 2010數(shù)據(jù)庫應(yīng)用教程 [主編 王迤冉 彭海云]
30. 數(shù)據(jù)庫技術(shù)與應(yīng)用（SQL Server 2008版）實(shí)踐教程 [主編 王小玲 嚴(yán)暉]