MySql入門(mén)--基礎(chǔ)知識(shí)

一、Mysql 物理文件組成

（一）日志文件

1、錯(cuò)誤日志：Error Log

錯(cuò)誤日志記錄了MyQL Server 運(yùn)行過(guò)程中所有較為嚴(yán)重的警告和錯(cuò)誤信息，以及MySQLServer 每次啟動(dòng)和關(guān)閉的詳細(xì)信息。在默認(rèn)情況下，系統(tǒng)記錄錯(cuò)誤日志的功能是關(guān)閉的，錯(cuò)誤信息被輸出到標(biāo)準(zhǔn)錯(cuò)誤輸出（stderr），如果要開(kāi)啟系統(tǒng)記錄錯(cuò)誤日志的功能，需要在啟動(dòng)時(shí)開(kāi)啟-log-error 選項(xiàng)。錯(cuò)誤日志的默認(rèn)存放位置在數(shù)據(jù)目錄下，以hostname.err 命名。但是可以使用命令：--log-error[=file_name]，修改其存放目錄和文件名。

為了方便維護(hù)需要，有時(shí)候會(huì)希望將錯(cuò)誤日志中的內(nèi)容做備份并重新開(kāi)始記錄，這時(shí)候就可以利用MySQL 的FLUSH LOGS 命令來(lái)告訴MySQL 備份舊日志文件并生成新的日志文件。備份文件名以“.old”結(jié)尾。

2、二進(jìn)制日志：Binary Log & Binary Log Index

二進(jìn)制日志，也就是我們常說(shuō)的binlog，也是MySQL Server 中最為重要的日志之一。當(dāng)我們通過(guò)“--log-bin[=file_name]”打開(kāi)了記錄的功能之后，MySQL 會(huì)將所有修改數(shù)據(jù)庫(kù)數(shù)據(jù)的query 以二進(jìn)制形式記錄到日志文件中。當(dāng)然，日志中并不僅限于query 語(yǔ)句這么簡(jiǎn)單，還包括每一條query 所執(zhí)行的時(shí)間，所消耗的資源，以及相關(guān)的事務(wù)信息，所以binlog是事務(wù)安全的。

和錯(cuò)誤日志一樣，binlog 記錄功能同樣需要“--log-bin[=file_name]”參數(shù)的顯式指定才能開(kāi)啟，如果未指定file_name，則會(huì)在數(shù)據(jù)目錄下記錄為mysql-bin.******（*代表0～9 之間的某一個(gè)數(shù)字，來(lái)表示該日志的序號(hào)）。

binlog 還有其他一些附加選項(xiàng)參數(shù)：

“--max_binlog_size”設(shè)置binlog 的最大存儲(chǔ)上限，當(dāng)日志達(dá)到該上限時(shí)，MySQL 會(huì)重新創(chuàng)建一個(gè)日志開(kāi)始繼續(xù)記錄。不過(guò)偶爾也有超出該設(shè)置的binlog 產(chǎn)生，一般都是因?yàn)樵诩磳⑦_(dá)到上限時(shí)，產(chǎn)生了一個(gè)較大的事務(wù)，為了保證事務(wù)安全，MySQL 不會(huì)將同一個(gè)事務(wù)分開(kāi)記錄到兩個(gè)binlog 中。

“--binlog-do-db=db_name”參數(shù)明確告訴MySQL，需要對(duì)某個(gè)（db_name）數(shù)據(jù)庫(kù)記錄binlog，如果有了“--binlog-do-db=db_name”參數(shù)的顯式指定，MySQL 會(huì)忽略針對(duì)其他數(shù)據(jù)庫(kù)執(zhí)行的query，而僅僅記錄針對(duì)指定數(shù)據(jù)庫(kù)執(zhí)行的query。

“--binlog-ignore-db=db_name”與“--binlog-do-db=db_name”完全相反，它顯式指定忽略某個(gè)（db_name）數(shù)據(jù)庫(kù)的binlog 記錄，當(dāng)指定了這個(gè)參數(shù)之后，MySQL 會(huì)記錄指定數(shù)據(jù)庫(kù)以外所有的數(shù)據(jù)庫(kù)的binlog。

“--binlog-ignore-db=db_name”與“--binlog-do-db=db_name”兩個(gè)參數(shù)有一個(gè)共同的概念需要大家理解清楚，參數(shù)中的db_name 不是指query 語(yǔ)句更新的數(shù)據(jù)所在的數(shù)據(jù)庫(kù)，而是執(zhí)行query 的時(shí)候當(dāng)前所處的數(shù)據(jù)庫(kù)。不論更新哪個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)，MySQL 僅僅比較當(dāng)前連接所處的數(shù)據(jù)庫(kù)（通過(guò)use db_name 切換后所在的數(shù)據(jù)庫(kù)）與參數(shù)設(shè)置的數(shù)據(jù)庫(kù)名，而不會(huì)分析query 語(yǔ)句所更新數(shù)據(jù)所在的數(shù)據(jù)庫(kù)。

mysql-bin.index 文件（binary log index）的功能是記錄所有Binary Log 的絕對(duì)路徑，保證MySQL 各種線程能夠順利的根據(jù)它找到所有需要的Binary Log 文件。

3、更新日志：update log

更新日志是MySQL 在較老的版本上使用的，其功能和binlog 基本類(lèi)似，只不過(guò)不是以二進(jìn)制格式來(lái)記錄而是以簡(jiǎn)單的文本格式記錄內(nèi)容。自從MySQL 增加了binlog 功能之后，就很少使用更新日志了。從版本5.0 開(kāi)始，MySQL 已經(jīng)不再支持更新日志了。

4、查詢(xún)?nèi)罩荆?span>query log

查詢(xún)?nèi)罩居涗?span>MySQL 中所有的query，通過(guò)“--log[=fina_name]”來(lái)打開(kāi)該功能。由于記錄了所有的query，包括所有的select，體積比較大，開(kāi)啟后對(duì)性能也有較大的影響，所以請(qǐng)大家慎用該功能。一般只用于跟蹤某些特殊的sql 性能問(wèn)題才會(huì)短暫打開(kāi)該功能。默認(rèn)的查詢(xún)?nèi)罩疚募麨?span>hostname.log。

5、慢查詢(xún)?nèi)罩荆?span>slow query log

顧名思義，慢查詢(xún)?nèi)罩局杏涗浀氖菆?zhí)行時(shí)間較長(zhǎng)的query，也就是我們常說(shuō)的slowquery，通過(guò)設(shè)--log-slow-queries[=file_name]來(lái)打開(kāi)該功能并設(shè)置記錄位置和文件名，默認(rèn)文件名為hostname-slow.log，默認(rèn)目錄也是數(shù)據(jù)目錄。

慢查詢(xún)?nèi)罩静捎玫氖呛?jiǎn)單的文本格式，可以通過(guò)各種文本編輯器查看其中的內(nèi)容。其中記錄了語(yǔ)句執(zhí)行的時(shí)刻，執(zhí)行所消耗的時(shí)間，執(zhí)行用戶，連接主機(jī)等相關(guān)信息。MySQL 還提供了專(zhuān)門(mén)用來(lái)分析滿查詢(xún)?nèi)罩镜墓ぞ叱绦?span>mysqlslowdump，用來(lái)幫助數(shù)據(jù)庫(kù)管理人員解決可能存在的性能問(wèn)題。

6、Innodb 的在線redo 日志：innodb redo log

Innodb 是一個(gè)事務(wù)安全的存儲(chǔ)引擎，其事務(wù)安全性主要就是通過(guò)在線redo 日志和記錄在表空間中的undo 信息來(lái)保證的。redo 日志中記錄了Innodb 所做的所有物理變更和事務(wù)信息，通過(guò)redo 日志和undo 信息，Innodb 保證了在任何情況下的事務(wù)安全性。Innodb 的redo日志同樣默認(rèn)存放在數(shù)據(jù)目錄下，可以通過(guò)innodb_log_group_home_dir 來(lái)更改設(shè)置日志的存放位置，通過(guò)innodb_log_files_in_group 設(shè)置日志的數(shù)量。

（二）數(shù)據(jù)文件

在MySQL 中每一個(gè)數(shù)據(jù)庫(kù)都會(huì)在定義好（或者默認(rèn)）的數(shù)據(jù)目錄下存在一個(gè)以數(shù)據(jù)庫(kù)名字命名的文件夾，用來(lái)存放該數(shù)據(jù)庫(kù)中各種表數(shù)據(jù)文件。不同的MySQL 存儲(chǔ)引擎有各自不同的數(shù)據(jù)文件，存放位置也有區(qū)別。多數(shù)存儲(chǔ)引擎的數(shù)據(jù)文件都存放在和MyISAM 數(shù)據(jù)文件位置相同的目錄下，但是每個(gè)數(shù)據(jù)文件的擴(kuò)展名卻各不一樣。如MyISAM 用“.MYD”作為擴(kuò)展名，Innodb 用“.ibd”，Archive 用“.arc”，CSV 用“.csv”，等等。

1、“.frm”文件

與表相關(guān)的元數(shù)據(jù)（meta）信息都存放在“.frm”文件中，包括表結(jié)構(gòu)的定義信息等。不論是什么存儲(chǔ)引擎，每一個(gè)表都會(huì)有一個(gè)以表名命名的“.frm”文件。所有的“.frm”文件都存放在所屬數(shù)據(jù)庫(kù)的文件夾下面。

2、“.MYD”文件

 “.MYD”文件是MyISAM 存儲(chǔ)引擎專(zhuān)用，存放MyISAM 表的數(shù)據(jù)。每一個(gè)MyISAM 表都會(huì)有一個(gè)“.MYD”文件與之對(duì)應(yīng)，同樣存放于所屬數(shù)據(jù)庫(kù)的文件夾下，和“.frm”文件在一起。

3、“.MYI”文件

“.MYI”文件也是專(zhuān)屬于MyISAM 存儲(chǔ)引擎的，主要存放MyISAM 表的索引相關(guān)信息。對(duì)于MyISAM 存儲(chǔ)來(lái)說(shuō)，可以被cache 的內(nèi)容主要就是來(lái)源于“.MYI”文件中。每一個(gè)MyISAM表對(duì)應(yīng)一個(gè)“.MYI”文件，存放于位置和“.frm”以及“.MYD”一樣。

4、“.ibd”文件和ibdata 文件

這兩種文件都是存放Innodb 數(shù)據(jù)的文件，之所以有兩種文件來(lái)存放Innodb 的數(shù)據(jù)（包括索引），是因?yàn)?span>Innodb 的數(shù)據(jù)存儲(chǔ)方式能夠通過(guò)配置來(lái)決定是使用共享表空間存放存儲(chǔ)數(shù)據(jù)，還是獨(dú)享表空間存放存儲(chǔ)數(shù)據(jù)。獨(dú)享表空間存儲(chǔ)方式使用“.ibd”文件來(lái)存放數(shù)據(jù)，且每個(gè)表一個(gè)“.ibd”文件，文件存放在和MyISAM 數(shù)據(jù)相同的位置。如果選用共享存儲(chǔ)表空間來(lái)存放數(shù)據(jù)，則會(huì)使用ibdata 文件來(lái)存放，所有表共同使用一個(gè)（或者多個(gè)，可自行配置）ibdata 文件。ibdata 文件可以通過(guò)innodb_data_home_dir 和innodb_data_file_path兩個(gè)參數(shù)共同配置組成， innodb_data_home_dir 配置數(shù)據(jù)存放的總目錄， 而innodb_data_file_path 配置每一個(gè)文件的名稱(chēng)。當(dāng)然， 也可以不配置innodb_data_home_dir 而直接在innodb_data_file_path 參數(shù)配置的時(shí)候使用絕對(duì)路徑來(lái)完成配置。innodb_data_file_path 中可以一次配置多個(gè)ibdata 文件。文件可以是指定大小，也可以是自動(dòng)擴(kuò)展的，但是Innodb 限制了僅僅只有最后一個(gè)ibdata 文件能夠配置成自動(dòng)擴(kuò)展類(lèi)型。當(dāng)我們需要添加新的ibdata 文件的時(shí)候，只能添加在innodb_data_file_path配置的最后，而且必須重啟MySQL 才能完成ibdata 的添加工作。不過(guò)如果我們使用獨(dú)享表空間存儲(chǔ)方式的話，就不會(huì)有這樣的問(wèn)題，但是如果要使用裸設(shè)備的話，每個(gè)表一個(gè)裸設(shè)備，可能造成裸設(shè)備數(shù)量非常大，而且不太容易控制大小，實(shí)現(xiàn)比較困難，而共享表空間卻不會(huì)有這個(gè)問(wèn)題，容易控制裸設(shè)備數(shù)量。我個(gè)人還是更傾向于使用獨(dú)享表空間存儲(chǔ)方式。當(dāng)然，兩種方式各有利弊，看大家各自應(yīng)用環(huán)境的側(cè)重點(diǎn)在那里了。

上面僅僅介紹了兩種最常用存儲(chǔ)引擎的數(shù)據(jù)文件，此外其他各種存儲(chǔ)引擎都有各自的數(shù)據(jù)文件，讀者朋友可以自行創(chuàng)建某個(gè)存儲(chǔ)引擎的表做一個(gè)簡(jiǎn)單的測(cè)試，做更多的了解。

（三）Replication相關(guān)文件：

1、master.info 文件：

master.info 文件存在于Slave 端的數(shù)據(jù)目錄下，里面存放了該Slave 的Master 端的相關(guān)信息，包括Master 的主機(jī)地址，連接用戶，連接密碼，連接端口，當(dāng)前日志位置，已經(jīng)讀取到的日志位置等信息。

2、relay log 和relay log index

mysql-relay-bin.xxxxxn 文件用于存放Slave 端的I/O 線程從Master 端所讀取到的Binary Log 信息，然后由Slave 端的SQL 線程從該relay log 中讀取并解析相應(yīng)的日志信息，轉(zhuǎn)化成Master 所執(zhí)行的SQL 語(yǔ)句，然后在Slave 端應(yīng)用。

mysql-relay-bin.index 文件的功能類(lèi)似于mysql-bin.index ，同樣是記錄日志的存放位置的絕對(duì)路徑，只不過(guò)他所記錄的不是Binary Log，而是Relay Log。

3、relay-log.info 文件：

類(lèi)似于master.info，它存放通過(guò)Slave 的I/O 線程寫(xiě)入到本地的relay log 的相關(guān)信息。供Slave 端的SQL 線程以及某些管理操作隨時(shí)能夠獲取當(dāng)前復(fù)制的相關(guān)信息。

2.1.4 其他文件：

1、system config file

MySQL 的系統(tǒng)配置文件一般都是“my.cnf”，Unix/Linux 下默認(rèn)存放在"/etc"目錄下，Windows 環(huán)境一般存放在“c:/windows”目錄下面?！?span>my.cnf”文件中包含多種參數(shù)選項(xiàng)組（group），每一種參數(shù)組都通過(guò)中括號(hào)給定了固定的組名，如“[mysqld]”組中包括了mysqld服務(wù)啟動(dòng)時(shí)候的初始化參數(shù)，“[client]”組中包含著客戶端工具程序可以讀取的參數(shù)，此外還有其他針對(duì)于各個(gè)客戶端軟件的特定參數(shù)組，如mysql 程序使用的“[mysql]”，mysqlchk使用的“[mysqlchk]”，等等。如果讀者朋友自己編寫(xiě)了某個(gè)客戶端程序，也可以自己設(shè)定一個(gè)參數(shù)組名，將相關(guān)參數(shù)配置在里面，然后調(diào)用mysql 客戶端api 程序中的參數(shù)讀取api讀取相關(guān)參數(shù)。

2、pid file

pid file 是mysqld 應(yīng)用程序在Unix/Linux 環(huán)境下的一個(gè)進(jìn)程文件，和許多其他Unix/Linux 服務(wù)端程序一樣，存放著自己的進(jìn)程id。

3、socket file

socket 文件也是在Unix/Linux 環(huán)境下才有的，用戶在Unix/Linux 環(huán)境下客戶端連接可以不通過(guò)TCP/IP 網(wǎng)絡(luò)而直接使用Unix Socket 來(lái)連接MySQL。

二、User、schema、database概念

In MySQL:

·         server instance == not identified with catalog, just a set of databases

·         database == schema == catalog == a namespace within the server.

·         user == named account, who is can connect to server and use (but can not own - no concept of ownership) objects in one or more databases

·         to identify any object you need (database name + object name)

In Oracle:

·         server instance == database == catalog == all data managed by same execution engine

·         schema == namespace within database, identical to user account

·         user == schema owner == named account, identical to schema, who can connect to database, who owns the schema and use objects possibly in other schemas

·         to identify any object you need (schema name + object name)

三、MySQL默認(rèn)數(shù)據(jù)庫(kù)mysql、information_schema等

l  mysql

數(shù)據(jù)庫(kù)mysql：這個(gè)是mysql的核心數(shù)據(jù)庫(kù)，主要負(fù)責(zé)存儲(chǔ)數(shù)據(jù)庫(kù)的用戶、權(quán)限設(shè)置、關(guān)鍵字等mysql自己需要使用的控制和管理信息。不可以刪除，如果對(duì)mysql不是很了解，也不要輕易修改這個(gè)數(shù)據(jù)庫(kù)里面的表信息。

l  information_schema

information_schema數(shù)據(jù)庫(kù)是MySQL自帶的，它提供了訪問(wèn)數(shù)據(jù)庫(kù)元數(shù)據(jù)的方式。什么是元數(shù)據(jù)呢？元數(shù)據(jù)是關(guān)于數(shù)據(jù)的數(shù)據(jù)，如數(shù)據(jù)庫(kù)名或表名，列的數(shù)據(jù)類(lèi)型，或訪問(wèn)權(quán)限等。有些時(shí)候用于表述該信息的其他術(shù)語(yǔ)包括“數(shù)據(jù)詞典”和“系統(tǒng)目錄”。

在 MySQL中，把 information_schema 看作是一個(gè)數(shù)據(jù)庫(kù)，確切說(shuō)是信息數(shù)據(jù)庫(kù)。其中保存著關(guān)于MySQL服務(wù)器所維護(hù)的所有其他數(shù)據(jù)庫(kù)的信息。如數(shù)據(jù)庫(kù)名，數(shù)據(jù)庫(kù)的表，表欄的數(shù)據(jù)類(lèi)型與訪問(wèn)權(quán)限等。在INFORMATION_SCHEMA中，有數(shù)個(gè)只讀表。它們實(shí)際上是視圖，而不是基本表，因此，你將無(wú)法看到與之相關(guān)的任何文件。

l  performance_schema

MySQL 5.5新增一個(gè)存儲(chǔ)引擎：命名PERFORMANCE_SCHEMA ,主要用于收集數(shù)據(jù)庫(kù)服務(wù)器性能參數(shù)。MySQL用戶是不能創(chuàng)建存儲(chǔ)引擎為PERFORMANCE_SCHEMA的表

performance_schema提供以下功能：

1.提供進(jìn)程等待的詳細(xì)信息，包括鎖、互斥變量、文件信息；

2.保存歷史的事件匯總信息，為提供MySQL服務(wù)器性能做出詳細(xì)的判斷；

3.對(duì)于新增和刪除監(jiān)控事件點(diǎn)都非常容易，并可以隨意改變mysql服務(wù)器的監(jiān)控周期，例如（CYCLE、MICROSECOND）

通過(guò)以上得到的信息，DBA能夠較明細(xì)得了解性能降低可能是由于哪些瓶頸？

Performance的開(kāi)啟很簡(jiǎn)單，在my.cnf中[mysqld]加入performanc_schema，檢查性能數(shù)據(jù)庫(kù)是否啟動(dòng)的命令:

SHOW VARIABLES LIKE ‘performance_schema’;

若是返回的 值為ON，則說(shuō)明性能數(shù)據(jù)庫(kù)正常開(kāi)啟狀態(tài)。