mysql下隱藏檔問題

標籤：

ibdata1 & mysql-bin 問題：磁碟空間警示，經查發現ibdata1和mysql-bin日誌佔用空間太多（其中ibdata1超過120G，mysql-bin超過80G）
原因：ibdata1是儲存格式，在INNODB類型資料狀態下，ibdata1用來隱藏檔的資料和索引，而庫名的檔案夾裡的那些表檔案只是結構而已。
innodb儲存引擎有兩種資料表空間的管理方式，分別是：
1）共用資料表空間（可拆分為多個小的資料表空間檔案），這個是我們目前多數資料庫使用的方法；
2）獨立資料表空間，每一個表有一個獨立的資料表空間（磁碟檔案）
對於兩種管理方式，各有優劣，具體如下：
①共用資料表空間：
優點：可以將資料表空間分成多個檔案存放到不同的磁碟上（資料表空間檔案大小不受表大小的限制，一個表可以分布在不同步的檔案上）。
缺點：所有資料和索引存放在一個檔案中，則隨著資料的增加，將會有一個很大的檔案，雖然可以把一個大檔案分成多個小檔案，但是多個表及索引在資料表空間中混合儲存，這樣如果對於一個表做了大量刪除操作後資料表空間中將有大量空隙。對於共用資料表空間管理的方式下，一旦資料表空間被分配，就不能再回縮了。當出現臨時建索引或是建立一個暫存資料表的動作表空間擴大後，就是刪除相關的表也沒辦法回縮那部分空間了。
②獨立資料表空間：在設定檔（my.cnf）中設定： innodb_file_per_table
特點：每個表都有自已獨立的資料表空間；每個表的資料和索引都會存在自已的資料表空間中。
優點：資料表空間對應的磁碟空間可以被收回（Drop table操作自動回收資料表空間，如果對於刪除大量資料後的表可以通過:alter table tbl_name engine=innodb;回縮不用的空間。
缺點：如果單表增加過大，如超過100G，效能也會受到影響。在這種情況下，如果使用共用資料表空間可以把檔案分開，但有同樣有一個問題，如果訪問的範圍過大同樣會訪問多個檔案，一樣會比較慢。如果使用獨立資料表空間，可以考慮使用分區表的方法，在一定程度上緩解問題。此外，當啟用獨立資料表空間模式時，需要合理調整innodb_open_files參數的設定。
解決:
1）ibdata1資料太大：只能通過dump，匯出建庫的sql語句，再重建的方法。
2）mysql-bin Log太大：
①手動刪除：
刪除某個日誌：mysql>PURGE MASTER LOGS TO ‘mysql-bin.010′;
刪除某天前的日誌：mysql>PURGE MASTER LOGS BEFORE ’2010-12-22 13:00:00′;
②在/etc/my.cnf裡設定只儲存N天的bin-log日誌
expire_logs_days = 30 //Binary Log自動刪除的天數 MySql ibdata1檔案

MySql innodb如果是共用資料表空間，ibdata1檔案越來越大，達到了30多個G，對一些沒用的表進行清空：
truncate table xxx;
然後optimize table xxx; 沒有效果
因為對共用資料表空間不起作用。
mysql ibdata1存放資料，索引等，是MYSQL的最主要的資料。
如果不把資料分開存放的話，這個檔案的大小很容易就上了G，甚至幾十G。對於某些應用來說，並不是太合適。因此要把此檔案縮小。
無法自動收縮，必須資料匯出，刪除ibdata1，然後資料匯入，比較麻煩，因此需要改為每個表單獨的檔案。
解決方案：資料檔案單獨存放(共用資料表空間如何改為每個表獨立的資料表空間檔案)。
步驟如下：
1）備份資料庫
備份全部資料庫，執行命令
#mysqldump -q -uroot -ppassword --add-drop-table --all-databases >/home/backup/all.sql
做完此步後，停止資料庫服務。
#service mysqld stop
2）找到my.ini或my.cnf檔案
linux下執行 
# /usr/libexec/mysqld --verbose --help | grep -A 1 ‘Default options‘
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
windows環境下可以：
mysqld --verbose --help > mysqlhelp.txt
notepad mysqlhelp.txt
在裡面尋找Default options，可以看到尋找my.ini的順序，以找到真實目錄
3）修改mysql設定檔
開啟my.ini或my.cnf檔案
[mysqld]下增加下面配置
innodb_file_per_table=1
驗證配置是否生效，可以重啟mysql後,執行
#service mysqld restart
#mysql -uroot -ppassword
mysql> show variables like ‘%per_table%‘;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> 
看看innodb_file_per_table變數是否為ON
4）刪除原資料檔案
刪除原來的ibdata1檔案及記錄檔ib_logfile*，刪除/var/lib/mysql目錄下的應用程式資料庫檔案夾(mysql檔案夾不要刪)
5）還原資料庫
啟動資料庫服務
從命令列進入MySQL Server
還原全部資料庫，執行命令
#service mysqld start
#mysql -uroot -pocs < /home/backup/all.sql 
經過以上幾步後，可以看到新的ibdata1檔案就只有幾十M了，資料及索引都變成了針對單個表的小ibd檔案了，它們在相應資料庫的檔案夾下面。
# ll
total 295028
drwx------  2 mysql mysql     36864 Apr 22 14:16 glpi
drwx------  2 mysql mysql     36864 Feb 15 13:45 glpi-1
-rw-rw----  1 mysql mysql  10485760 Apr 22 14:27 ibdata1
-rw-rw----. 1 mysql mysql 270532608 Apr 22 14:14 ibdata1-1
-rw-rw----  1 mysql mysql   5242880 Apr 22 14:27 ib_logfile0
-rw-rw----. 1 mysql mysql   5242880 Apr 22 14:14 ib_logfile0_bak
-rw-rw----  1 mysql mysql   5242880 Apr 22 14:28 ib_logfile1
-rw-rw----. 1 mysql mysql   5242880 Apr 21 22:50 ib_logfile1_bak
drwx------  2 mysql mysql      4096 Apr 22 14:16 mrbs
drwx------  2 mysql mysql      4096 Apr 14 12:05 mrbs-1
drwx------. 2 mysql mysql      4096 Apr 22 14:16 mysql
srwxrwxrwx  1 mysql mysql         0 Apr 22 14:16 mysql.sock
drwx------  2 mysql mysql     12288 Apr 22 14:16 ocsweb
drwx------  2 mysql mysql     12288 Nov 16  2011 ocsweb-1
# ll mrbs
total 808
-rw-rw---- 1 mysql mysql     61 Apr 22 14:16 db.opt
-rw-rw---- 1 mysql mysql  10492 Apr 22 14:16 mrbs_area.frm
-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_area.ibd
-rw-rw---- 1 mysql mysql   9264 Apr 22 14:16 mrbs_entry.frm
-rw-rw---- 1 mysql mysql 131072 Apr 22 14:16 mrbs_entry.ibd
-rw-rw---- 1 mysql mysql   9442 Apr 22 14:16 mrbs_repeat.frm
-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_repeat.ibd
-rw-rw---- 1 mysql mysql   8888 Apr 22 14:16 mrbs_room.frm
-rw-rw---- 1 mysql mysql 114688 Apr 22 14:16 mrbs_room.ibd
-rw-rw---- 1 mysql mysql   8688 Apr 22 14:16 mrbs_users.frm
-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_users.ibd
-rw-rw---- 1 mysql mysql   8658 Apr 22 14:16 mrbs_variables.frm
-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_variables.ibd
-rw-rw---- 1 mysql mysql   8738 Apr 22 14:16 mrbs_zoneinfo.frm
-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_zoneinfo.ibd
# ll mrbs-1
total 88
-rw-rw---- 1 mysql mysql    61 Apr 14 12:05 db.opt
-rw-rw---- 1 mysql mysql 10492 Apr 14 12:05 mrbs_area.frm
-rw-rw---- 1 mysql mysql  9264 Apr 14 12:05 mrbs_entry.frm
-rw-rw---- 1 mysql mysql  9442 Apr 14 12:05 mrbs_repeat.frm
-rw-rw---- 1 mysql mysql  8888 Apr 14 12:05 mrbs_room.frm
-rw-rw---- 1 mysql mysql  8688 Apr 14 12:05 mrbs_users.frm
-rw-rw---- 1 mysql mysql  8658 Apr 14 12:05 mrbs_variables.frm
-rw-rw---- 1 mysql mysql  8738 Apr 14 12:05 mrbs_zoneinfo.frm

文字來源：http://www.xiaobo.li/db/419.html

mysql下隱藏檔問題