mysql下存儲文件問題，mysql存儲文件問題

mysql下存儲文件問題，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