MySql入門--基礎知識

一、Mysql 物理檔案組成

（一）日誌檔案

1、錯誤日誌：Error Log

錯誤日誌記錄了MyQL Server 執行過程中所有較為嚴重的警告和錯誤資訊，以及MySQLServer 每次啟動和關閉的詳細資訊。在預設情況下，系統記錄錯誤日誌的功能是關閉的，錯誤資訊被輸出到標準錯誤輸出（stderr），如果要開啟系統記錄錯誤日誌的功能，需要在啟動時開啟-log-error 選項。錯誤日誌的預設存放位置在資料目錄下，以hostname.err 命名。但是可以使用命令：--log-error[=file_name]，修改其存放目錄和檔名。

為了方便維護需要，有時候會希望將錯誤日誌中的內容做備份並重新開始記錄，這時候就可以利用MySQL 的FLUSH LOGS 命令來告訴MySQL 備份舊日誌檔案並生成新的日誌檔案。備份檔名以“.old”結尾。

 

2、二進位制日誌：Binary Log & Binary Log Index

二進位制日誌，也就是我們常說的binlog，也是MySQL Server 中最為重要的日誌之一。當我們透過“--log-bin[=file_name]”開啟了記錄的功能之後，MySQL 會將所有修改資料庫資料的query 以二進位制形式記錄到日誌檔案中。當然，日誌中並不僅限於query 語句這麼簡單，還包括每一條query 所執行的時間，所消耗的資源，以及相關的事務資訊，所以binlog是事務安全的。

和錯誤日誌一樣，binlog 記錄功能同樣需要“--log-bin[=file_name]”引數的顯式指定才能開啟，如果未指定file_name，則會在資料目錄下記錄為mysql-bin.******（*代表0～9 之間的某一個數字，來表示該日誌的序號）。

binlog 還有其他一些附加選項引數：

“--max_binlog_size”設定binlog 的最大儲存上限，當日志達到該上限時，MySQL 會重新建立一個日誌開始繼續記錄。不過偶爾也有超出該設定的binlog 產生，一般都是因為在即將達到上限時，產生了一個較大的事務，為了保證事務安全，MySQL 不會將同一個事務分開記錄到兩個binlog 中。

“--binlog-do-db=db_name”引數明確告訴MySQL，需要對某個（db_name）資料庫記錄binlog，如果有了“--binlog-do-db=db_name”引數的顯式指定，MySQL 會忽略針對其他資料庫執行的query，而僅僅記錄針對指定資料庫執行的query。

“--binlog-ignore-db=db_name”與“--binlog-do-db=db_name”完全相反，它顯式指定忽略某個（db_name）資料庫的binlog 記錄，當指定了這個引數之後，MySQL 會記錄指定資料庫以外所有的資料庫的binlog。

“--binlog-ignore-db=db_name”與“--binlog-do-db=db_name”兩個引數有一個共同的概念需要大家理解清楚，引數中的db_name 不是指query 語句更新的資料所在的資料庫，而是執行query 的時候當前所處的資料庫。不論更新哪個資料庫的資料，MySQL 僅僅比較當前連線所處的資料庫（透過use db_name 切換後所在的資料庫）與引數設定的資料庫名，而不會分析query 語句所更新資料所在的資料庫。

mysql-bin.index 檔案（binary log index）的功能是記錄所有Binary Log 的絕對路徑，保證MySQL 各種執行緒能夠順利的根據它找到所有需要的Binary Log 檔案。

 

3、更新日誌：update log

更新日誌是MySQL 在較老的版本上使用的，其功能和binlog 基本類似，只不過不是以二進位制格式來記錄而是以簡單的文字格式記錄內容。自從MySQL 增加了binlog 功能之後，就很少使用更新日誌了。從版本5.0 開始，MySQL 已經不再支援更新日誌了。

 

4、查詢日誌：query log

查詢日誌記錄MySQL 中所有的query，透過“--log[=fina_name]”來開啟該功能。由於記錄了所有的query，包括所有的select，體積比較大，開啟後對效能也有較大的影響，所以請大家慎用該功能。一般只用於跟蹤某些特殊的sql 效能問題才會短暫開啟該功能。預設的查詢日誌檔名為hostname.log。

 

5、慢查詢日誌：slow query log

顧名思義，慢查詢日誌中記錄的是執行時間較長的query，也就是我們常說的slowquery，透過設--log-slow-queries[=file_name]來開啟該功能並設定記錄位置和檔名，預設檔名為hostname-slow.log，預設目錄也是資料目錄。

慢查詢日誌採用的是簡單的文字格式，可以透過各種文字編輯器檢視其中的內容。其中記錄了語句執行的時刻，執行所消耗的時間，執行使用者，連線主機等相關資訊。MySQL 還提供了專門用來分析滿查詢日誌的工具程式mysqlslowdump，用來幫助資料庫管理人員解決可能存在的效能問題。

 

6、Innodb 的線上redo 日誌：innodb redo log

Innodb 是一個事務安全的儲存引擎，其事務安全性主要就是透過線上redo 日誌和記錄在表空間中的undo 資訊來保證的。redo 日誌中記錄了Innodb 所做的所有物理變更和事務資訊，透過redo 日誌和undo 資訊，Innodb 保證了在任何情況下的事務安全性。Innodb 的redo日誌同樣預設存放在資料目錄下，可以透過innodb_log_group_home_dir 來更改設定日誌的存放位置，透過innodb_log_files_in_group 設定日誌的數量。

 

（二）資料檔案

在MySQL 中每一個資料庫都會在定義好（或者預設）的資料目錄下存在一個以資料庫名字命名的資料夾，用來存放該資料庫中各種表資料檔案。不同的MySQL 儲存引擎有各自不同的資料檔案，存放位置也有區別。多數儲存引擎的資料檔案都存放在和MyISAM 資料檔案位置相同的目錄下，但是每個資料檔案的副檔名卻各不一樣。如MyISAM 用“.MYD”作為副檔名，Innodb 用“.ibd”，Archive 用“.arc”，CSV 用“.csv”，等等。

1、“.frm”檔案

與表相關的後設資料（meta）資訊都存放在“.frm”檔案中，包括表結構的定義資訊等。不論是什麼儲存引擎，每一個表都會有一個以表名命名的“.frm”檔案。所有的“.frm”檔案都存放在所屬資料庫的資料夾下面。

2、“.MYD”檔案

 “.MYD”檔案是MyISAM 儲存引擎專用，存放MyISAM 表的資料。每一個MyISAM 表都會有一個“.MYD”檔案與之對應，同樣存放於所屬資料庫的資料夾下，和“.frm”檔案在一起。

3、“.MYI”檔案

“.MYI”檔案也是專屬於MyISAM 儲存引擎的，主要存放MyISAM 表的索引相關資訊。對於MyISAM 儲存來說，可以被cache 的內容主要就是來源於“.MYI”檔案中。每一個MyISAM表對應一個“.MYI”檔案，存放於位置和“.frm”以及“.MYD”一樣。

4、“.ibd”檔案和ibdata 檔案

這兩種檔案都是存放Innodb 資料的檔案，之所以有兩種檔案來存放Innodb 的資料（包括索引），是因為Innodb 的資料儲存方式能夠透過配置來決定是使用共享表空間存放儲存資料，還是獨享表空間存放儲存資料。獨享表空間儲存方式使用“.ibd”檔案來存放資料，且每個表一個“.ibd”檔案，檔案存放在和MyISAM 資料相同的位置。如果選用共享儲存表空間來存放資料，則會使用ibdata 檔案來存放，所有表共同使用一個（或者多個，可自行配置）ibdata 檔案。ibdata 檔案可以透過innodb_data_home_dir 和innodb_data_file_path兩個引數共同配置組成， innodb_data_home_dir 配置資料存放的總目錄， 而innodb_data_file_path 配置每一個檔案的名稱。當然， 也可以不配置innodb_data_home_dir 而直接在innodb_data_file_path 引數配置的時候使用絕對路徑來完成配置。innodb_data_file_path 中可以一次配置多個ibdata 檔案。檔案可以是指定大小，也可以是自動擴充套件的，但是Innodb 限制了僅僅只有最後一個ibdata 檔案能夠配置成自動擴充套件型別。當我們需要新增新的ibdata 檔案的時候，只能新增在innodb_data_file_path配置的最後，而且必須重啟MySQL 才能完成ibdata 的新增工作。不過如果我們使用獨享表空間儲存方式的話，就不會有這樣的問題，但是如果要使用裸裝置的話，每個表一個裸裝置，可能造成裸裝置數量非常大，而且不太容易控制大小，實現比較困難，而共享表空間卻不會有這個問題，容易控制裸裝置數量。我個人還是更傾向於使用獨享表空間儲存方式。當然，兩種方式各有利弊，看大家各自應用環境的側重點在那裡了。

上面僅僅介紹了兩種最常用儲存引擎的資料檔案，此外其他各種儲存引擎都有各自的資料檔案，讀者朋友可以自行建立某個儲存引擎的表做一個簡單的測試，做更多的瞭解。

 

（三）Replication相關檔案：

1、master.info 檔案：

master.info 檔案存在於Slave 端的資料目錄下，裡面存放了該Slave 的Master 端的相關資訊，包括Master 的主機地址，連線使用者，連線密碼，連線埠，當前日誌位置，已經讀取到的日誌位置等資訊。

2、relay log 和relay log index

mysql-relay-bin.xxxxxn 檔案用於存放Slave 端的I/O 執行緒從Master 端所讀取到的Binary Log 資訊，然後由Slave 端的SQL 執行緒從該relay log 中讀取並解析相應的日誌資訊，轉化成Master 所執行的SQL 語句，然後在Slave 端應用。

mysql-relay-bin.index 檔案的功能類似於mysql-bin.index ，同樣是記錄日誌的存放位置的絕對路徑，只不過他所記錄的不是Binary Log，而是Relay Log。

3、relay-log.info 檔案：

類似於master.info，它存放透過Slave 的I/O 執行緒寫入到本地的relay log 的相關資訊。供Slave 端的SQL 執行緒以及某些管理操作隨時能夠獲取當前複製的相關資訊。

2.1.4 其他檔案：

1、system config file

MySQL 的系統配置檔案一般都是“my.cnf”，Unix/Linux 下預設存放在"/etc"目錄下，Windows 環境一般存放在“c:/windows”目錄下面。“my.cnf”檔案中包含多種引數選項組（group），每一種引數組都透過中括號給定了固定的組名，如“[mysqld]”組中包括了mysqld服務啟動時候的初始化引數，“[client]”組中包含著客戶端工具程式可以讀取的引數，此外還有其他針對於各個客戶端軟體的特定引數組，如mysql 程式使用的“[mysql]”，mysqlchk使用的“[mysqlchk]”，等等。如果讀者朋友自己編寫了某個客戶端程式，也可以自己設定一個引數組名，將相關引數配置在裡面，然後呼叫mysql 客戶端api 程式中的引數讀取api讀取相關引數。

2、pid file

pid file 是mysqld 應用程式在Unix/Linux 環境下的一個程式檔案，和許多其他Unix/Linux 服務端程式一樣，存放著自己的程式id。

3、socket file

socket 檔案也是在Unix/Linux 環境下才有的，使用者在Unix/Linux 環境下客戶端連線可以不透過TCP/IP 網路而直接使用Unix Socket 來連線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預設資料庫mysql、information_schema等

l  mysql

資料庫mysql：這個是mysql的核心資料庫，主要負責儲存資料庫的使用者、許可權設定、關鍵字等mysql自己需要使用的控制和管理資訊。不可以刪除，如果對mysql不是很瞭解，也不要輕易修改這個資料庫裡面的表資訊。

l  information_schema

information_schema資料庫是MySQL自帶的，它提供了訪問資料庫後設資料的方式。什麼是後設資料呢？後設資料是關於資料的資料，如資料庫名或表名，列的資料型別，或訪問許可權等。有些時候用於表述該資訊的其他術語包括“資料詞典”和“系統目錄”。

在 MySQL中，把 information_schema 看作是一個資料庫，確切說是資訊資料庫。其中儲存著關於MySQL伺服器所維護的所有其他資料庫的資訊。如資料庫名，資料庫的表，表欄的資料型別與訪問許可權等。在INFORMATION_SCHEMA中，有數個只讀表。它們實際上是檢視，而不是基本表，因此，你將無法看到與之相關的任何檔案。

l  performance_schema

MySQL 5.5新增一個儲存引擎：命名PERFORMANCE_SCHEMA ,主要用於收集資料庫伺服器效能引數。MySQL使用者是不能建立儲存引擎為PERFORMANCE_SCHEMA的表

performance_schema提供以下功能：

1.提供程式等待的詳細資訊，包括鎖、互斥變數、檔案資訊；

2.儲存歷史的事件彙總資訊，為提供MySQL伺服器效能做出詳細的判斷；

3.對於新增和刪除監控事件點都非常容易，並可以隨意改變mysql伺服器的監控週期，例如（CYCLE、MICROSECOND）

透過以上得到的資訊，DBA能夠較明細得了解效能降低可能是由於哪些瓶頸？

Performance的開啟很簡單，在my.cnf中[mysqld]加入performanc_schema，檢查效能資料庫是否啟動的命令:

SHOW VARIABLES LIKE ‘performance_schema’;

若是返回的 值為ON，則說明效能資料庫正常開啟狀態。