mysql物理文件組成

一：MySQL 與其他數據庫比較

1）功能比較

作為一個成熟的數據庫系統管理系統，現在已經具備通用數據庫管理系統的相關功能

2）易用性比較

對于普通用戶操作難度相對簡單易用原則，從安裝方面來說，MySQL安裝包僅僅只有100MB左右，安裝難易程度要比Oracl，數據庫簡單很多，不論是通過已經編譯好的二進制分發包還是源碼編譯安裝，都比較簡單

3）性能比較

MySQL非常優異表現，而且其他商用的通用數據庫系統中，只有Oracle數據庫能相提并論。

4可靠性）MySQL數據庫發展過程三個原則， 簡單，高效，可靠。

二：MySQL主要場景

1）web網站系統

MySQ;是開放源代碼，可以免費使用

2）日志記錄系統

MySQL數據庫插入和查詢都非常高效，使用MyISAM存儲時候 兩者可以互不鎖定，達到很高效的并發能力，例如，登陸日志，操作日志，都非常應用場景。

3）數據倉庫系統

隨著數據量增長，需要空間越來越大，使數據統計分析變得越來越低，解決思路

采用高性能主機提高運算，用高端存儲設備提高I\O性能，2就是通過數據復制多臺使用大容量硬盤上以提高性能運算能力，但是存儲空間有一定限制，3可以通過水平拆分來存放數據，MySQL都有較大優勢，通過簡單復制功能，可以將數據從一臺主機復制到另外一臺主機、

三：物理文件組成

日志文件：錯誤日志 查詢日志 慢查詢日志 事物日志

日志記錄這mysql數據庫運行期間發生變化，相當于記錄mysql數據庫連接客戶的連接狀況

SQL語句執行情況和錯誤信息等，當數據意外損壞時，可以通過錯誤日志看出原因，并且日志進行數據恢復。

錯誤日志：在mysql中默認是開啟的，錯誤日志存儲在mysql數據庫目錄中

錯誤日志信息可以自己進行配置，錯誤日志可以通過log-error和存儲位置log-warninge定義

是否將警示信息定義至錯誤日志中，

服務器運行過程錯誤信息，事件調度器運行一個事件產生，在從服務器啟動服務器進程產生的信息。

因此mysql提供兩組命令，分別查看系統設置和運行狀態。 
1、查看系統設置： 
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] 
SHOW VARIABLES：shows the values of MySQL system variables. 
2、運行狀態： 
SHOW [GLOBAL | SESSION] STATUS [like_or_where] 
SHOW STATUS：provides server status information.

 如何修改系統配置

方法1：配置文件設置my.cnf

如：binlog_cache_size = 1M

方法2：set globalbinlog_cache_size = 1048576;

使用set 將安全級別改為2

3：查看mysql版本

Mysql -V     mysql> status;   select version ();

4 錯誤日志狀態

注 log_error 定義錯誤文件路徑

   Log__error_verbosity  安全級別

 查看錯誤日志

 Tail  /usr/local/mysql/data/mysqld.err

二：刪除錯誤日志

數據庫管理員可以刪除很久以前的錯誤日志，為保證服務器硬盤空間，可以使用、mysqladmin -u root  flush-logs  FLUSH LOGS開啟新的錯誤日志

二進制日志

主要用于記錄修改數據或有可能引起數據改變的mysql語句，并且記錄語句發生時間，執行時長，操作數據等等。一般體積上限為1G

首先看到二進制是關閉的OFF 需要在vim /etc/my.conf進行配置

Bin_log=/usr/local/mysql/data/log_bin  開啟

一般設置為512或者1個G 達到上限時 mysql重新創建一個日志開始記錄

為保證mysql安全 不會將同一個事物分開記錄倆個binlog 中

事務日志

事物日志表將一個事物任務提交，對包含在事物中的多條語句要么執行，要么全部不執行。

非事物表則不支持此操作，處理中如果遇到錯誤，在錯誤前語句執行成功，之后則執行不成功

 指定二進制日志類型

 Mysql復制主要有三種方式 基于SQL語句復制 混合模式復制  基于行復制

  三 通過編輯my.conf中log-bin 選擇開啟二進制日志

其中 DIR參數指定二進制文件存儲路徑 filename參數指定二級制文件文件名，形式為 filename.number 形式為 000001. 000002 等 每次重啟mysql服務或運行 flush logs；都會生成一個新的二進制文件

查看二進制文件信息

查看二進制日志信息的命令：

語法格式：SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

#查看所有的二進制信息

mysql> show binlog events\G;

#查看指定日志的二進制信息

mysql> show binlog events in 'mysql-bin.000013'\G;

 刪除二進制日志信息

長時間不清理二進制文件，影響磁盤空間，刪除之后可能導致數據庫奔潰無法恢復，所以刪除二進制首先和數據庫備份

語法形式：

mysql> PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }

其中TO 'log_name'表示把這個文件之前的其他文件都刪除掉，也可使用BEFORE datetime_expr指定把哪個時間之前的二進制文件刪除了。

也可以指定時間來刪除二進制日志

命令行下查看二進制日志：

由于無法使用cat等方式直接打開并查看二進制日志；所以必須使用mysqlbinlog命令。但是當正在執行mysql讀寫操作時建議不要使用此打開正在使用的二進制日志文件

刪除所以二進制日志

3 事物日志

可以幫助提高事務的效率。使用事務日志，存儲引擎在修改表的數據時只需要修改其內存拷貝，再把修改行為記錄到持久在硬盤上的事務日志中，而不用每次都將修改的數據本身持久到磁盤。mysql會默認提供多種存儲引擎,你可以通過下面的查看:
查看你的mysql現在已提供什么存儲引擎:
mysql> show engines;
create table 庫名.表名 engine = innodb;
這樣就可以將表的引擎變更為innodb引擎了。
也可以在創建表之后通過下面語句來變更：
alter table庫名.表名engine =innodb;

在事務提交時innodb是否同步日志從緩沖區到文件中，當這個值為1（默認值）之時，在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新，性能會很差造成大量的磁盤I/O但這種方式最安全

刷寫的概念

設為1當然是最安全的，但性能頁是最差的（相對其他兩個參數而言，但不是不能接受）。如果對數據一致性和完整性要求不高，完全可以設為2,如果只最求性能，例如高并發寫的日志服務器，設為0來獲得更高性能

慢查詢日志

記錄了語句執行的時刻，執行所消耗的時間，執行用戶，連接主機等相關信息。

慢查詢日志的作用：

慢查詢日志是用來記錄執行時間超過指定時間的查詢語句。通過慢查詢日志，可以查找出哪些查詢語句的執行效率很低，以便進行優化。一般建議開啟，它對服務器性能的影響微乎其微，但是可以記錄mysql服務器上執行了很長時間的查詢語句。

啟動和設置查詢日志

slow_query_log： off關閉狀態  on開啟狀態
slow_query_log_file  慢查詢日志存放地點

long_query_time選項來設置一個時間值，時間以秒為單位，可以精確到微秒。如果查詢時間超過了這個時間值（默認為10秒），這個查詢語句將被記錄到慢查詢日志中, 設置為0的話表示記錄所有的查詢。

如果不指定存儲路徑，慢查詢日志默認存儲到mysql數據庫的數據文件下，如果不指定文件名，默認文件名為hostname-slow.log

修改my.cnf文件：

方法2：通過登錄mysql服務器直接定義，方式如下：

mysql>set global slow_query_log=1;  #開啟慢查詢日志

Query OK, 0 rowsaffected (0.35 sec)

mysql>set session long_query_time=0.0001; #更改時間（當前session中，退出則重置）

Query OK, 0 rowsaffected (0.00 sec)

mysql>set global long_query_time=0.0001; #更改時間（全局中，重啟服務則重置）

mysql> SHOW VARIABLES LIKE 'long%';  #查詢定義時間

查看慢查詢日志

mysql> use mysql

mysql> selectuser,host from user where user="root";

3)數據文據

在MySQL 中每一個數據庫都會在定義好（或者默認）的數據目錄下存在一個以數據庫名字命名的文件夾，用來存放該數據庫中各種表數據文件。

mysql> show create table 表名;

create table 庫名.表名 engine = innodb;  這樣就可以將表的引擎變更為innodb引擎了。

登錄mysql，創建一個數據庫如testdb，并在數據庫中創建一個表

以myisam存儲引擎創建一個測試表tb2

.frm”文件

與表相關的元數據（meta）信息都存放在“.frm”文件中，包括表結構的定義信息等。不論是什么存儲引擎（MySQL常用的兩個存儲引擎是MyISAM和InnoDB），每一個表都會有一個以表名命名的“.frm”文件。

MyISAM數據庫表文件：.MYD文件：表數據文件；.MYI文件：索引文件

MYD”文件

“.MYD”文件是MyISAM 存儲引擎專用，存放MyISAM 表的數據。每一個MyISAM 表都會有一個“.MYD”文件與之對應，同樣存放于所屬數據庫的文件夾下，和“.frm”文件在一起。

MYI”文件

“.MYI”文件也是專屬于MyISAM 存儲引擎的，主要存放MyISAM 表的索引相關信息。

InnoDB采用表空間（tablespace）來管理數據，存儲表數據和索引。

.ibd文件：單表表空間文件，每個表使用一個表空間文件（file per table），存放用戶數據庫表數據和索引。

.ibd”文件和ibdata 文件

這兩種文件都是存放Innodb 數據的文件，之所以有兩種文件來存放Innodb 的數據（包括索引），是因為Innodb 的數據存儲方式能夠通過配置來決定是使用共享表空間存放存儲數據，還是獨享表空間存放存儲數據。

共享表空間以及獨占表空間都是針對數據的存儲方式而言的。

共享表空間:  某一個數據庫的所有的表數據，索引文件全部放在一個文件中。

獨占表空間:  每一個表都將會生成以獨立的文件方式來進行存儲，每一個表都有一個.frm表描述文件，還有一個.ibd文件。 其中這個文件包括了單獨一個表的數據內容以及索引內容。

所有的數據和索引存放到一個文件中，多個表及索引在表空間中混合存儲，這樣對于一個表做了大量刪除操作后表空間中將會有大量的空隙，特別是對于統計分析，日值系統這類應用最不適合用共享表空間。

獨立表空間：

優點：
1．每個表都有自已獨立的表空間。
2．每個表的數據和索引都會存在自已的表空間中。
3．可以實現單表在不同的數據庫中移動。
4．空間可以回收
查看當前數據庫的表空間管理類型

ON代表獨立表空間管理，OFF代表共享表空間管理

Innodb共享表空間配置：

768/64=12

innodb_data_home_dir = "/path/" 數據庫文件所存放的目錄

innodb_log_group_home_dir = "/path/" 日志存放目錄

innodb_data_file_path=ibdata1:10M:autoextend 設置一個可擴展大小的尺寸為10MB的數據文件（共享數據文件），名為ibdata1。沒有給出文件的位置，所以默認的是在MySQL的數據目錄內。

innodb_file_per_table=1｜0   //1為使用獨占表空間，0 為使用共享表空間

在/etc/my.cnf文件中設置6400頁而當前ibdata1為768頁

需要計算768/64=12
啟動mysql，成功！

計算公式：64pages相當于1M，1page是16KB

調用存儲過程

查看表中行數：

對INNODB，直接用命令show table status查看某個表的表空間占用情況

MySQL數據庫中每個表占用的空間、表記錄的行數的話，可以打開MySQL的 information_schema 數據庫。在該庫中有一個 TABLES 表，這個表主要字段分別是：

TABLE_SCHEMA : 數據庫名
TABLE_NAME：表名
ENGINE：所使用的存儲引擎
TABLE_ROWS：記錄數
DATA_LENGTH：數據大小
INDEX_LENGTH：索引大小

master.info 文件

包括Master 的主機地址，連接用戶，連接密碼，連接端口，當前日志位置，已經讀取到的日志位置等信息。

 elay 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 線程以及某些管理操作隨時能夠獲取當前復制信息

 socket 文件也是在Unix/Linux 環境下才有的，用戶在Unix/Linux 環境下客戶端連接可以不通過TCP/IP 網絡而直接使用Unix Socket 來連接MySQL。

mysql有兩種連接方式，常用的一般是tcp
mysql  –h  mysql主機ip  -uroot  -pxxx
mysql  -S  /path /mysql.sock