為MySQL數據文件ibdata1瘦身 MySQL在運行一段時間後，ibdata1的文件會增長大小，就算刪除了表的數據，ibdata1的體積也不會減小。由於硬盤空間有限，這樣一直膨脹下去磁盤空間接近崩潰。今天在導出數據的時候就發現了，磁盤竟然滿了，明明預留了1個月的用量，1周就占滿了,下面就要給ibdata1做個瘦身。 1. 系統環境 Linux Ubuntu 13.04 64bit server ~ uname -a Linux d2 3.8.0-21-generic #32-Ubuntu SMP Tue May 14 22:16:46 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux ~ cat /etc/issue Ubuntu 13.04 \n \l MySQL: 5.5.31-0ubuntu0.13.04.1 ~ mysql --version mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2 硬盤：36G+4G+4G+36G
~ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/server3--vg-root 36G 31G 3.2G 91% / none 4.0K 0 4.0K 0% /sys/fs/cgroup udev 4.1G 1.1G 3.0G 26% /dev tmpfs 824M 280K 823M 1% /run none 5.0M 0 5.0M 0% /run/lock none 4.1G 3.4G 729M 83% /run/shm none 100M 0 100M 0% /run/user /dev/vda1 228M 30M 187M 14% /boot 192.168.1.10:/home/amg/data 36G 13G 21G 39% /home/amg/data MySQL的ibdata1占用空間:20G ~ cd /var/lib/mysql ~ ls -l drwxr-xr-x 2 mysql mysql 4096 Aug 2 19:38 CB drwxr-xr-x 2 mysql mysql 4096 Jun 24 23:08 conan drwxr-xr-x 2 mysql mysql 4096 Jun 2 00:52 dbwordpress -rwxr-xr-x 1 root root 0 May 23 00:48 debian-5.5.flag -rwxr-xr-x 1 mysql mysql 20101201920 Aug 2 20:08 ibdata1 -rwxr-xr-x 1 mysql mysql 5242880 Aug 2 20:08 ib_logfile0 -rwxr-xr-x 1 mysql mysql 5242880 Aug 2 19:38 ib_logfile1 drwxr-xr-x 2 mysql mysql 4096 Jun 26 09:03 Macro drwxr-xr-x 2 mysql root 4096 May 23 00:48 mysql -rwxr-xr-x 1 root root 6 May 23 00:48 mysql_upgrade_info drwxr-xr-x 2 mysql mysql 4096 May 23 00:48 performance_schema drwxr-xr-x 2 mysql mysql 4096 May 23 00:53 phpmyadmin drwxr-xr-x 2 mysql root 4096 May 23 00:48 test drwxr-xr-x 2 mysql mysql 4096 Jul 22 14:09 TF drwxr-xr-x 2 mysql mysql 4096 Jun 2 01:04 wordpress 業務數據表 mysql> show tables; +-----------------+ | Tables_in_CB | +-----------------+ | NSpremium | | cb_hft | | cb_hft_20130801 | | cb_hft_20130802 | +-----------------+ 4 rows in set (0.00 sec)
2. 發現問題 ibdata1單個文件占用20G大小。 1. MySQL默認設置，沒有按表空間分離數據，所有的表的數據都被放到ibdata1文件中。 2. 業務操作，每天會產生一張表cb_hft，晚上的時候對表進行重命名。 RENAME TABLE cb_hft TO cb_hft_20130801; create table cb_hft like cb_hft_20130801; 3. 每周會把數據導出，同時drop表。但drop後，ibdata1不會減少，隨著數據的積累ibdata1越來越大，根空間已經不夠用了。 3. 解決問題 1). 導出數據 現在數據庫中，有兩個數據表，cb_hft_20130801,cb_hft_20130802，分別導出到/run/shm, /dev
~ cd /dev ~ mysqldump -uroot -p CB cb_hft_20130802 > export_cb_hft_20130802.sql ~ cd /run/shm ~ mysqldump -uroot -p CB cb_hft_20130801 > export_cb_hft_20130801.sql ~ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/server3--vg-root 36G 31G 3.2G 91% / none 4.0K 0 4.0K 0% /sys/fs/cgroup udev 4.1G 3.7G 368M 92% /dev tmpfs 824M 280K 823M 1% /run none 5.0M 0 5.0M 0% /run/lock none 4.1G 3.4G 729M 83% /run/shm none 100M 0 100M 0% /run/user /dev/vda1 228M 30M 187M 14% /boot 192.168.1.10:/home/amg/data 36G 13G 21G 39% /home/amg/data 兩張表分別占了，3.4G，3.7G。 登陸mysql刪除CB數據庫 ~ mysql -uroot -p ~ drop database CB 導出其他數據庫數據 ~ cd /run/shm ~ mysqldump -uroot -p -R -q --all-databases > others.sql
2). 修改配置文件/etc/mysql/my.cnf 對每張表使用單獨的數據文件存儲innodb_file_per_table
停止mysql服務器 ~ sudo /etc/init.d/mysql stop #清空所有數據文件 ~ sudo rm -rf /var/lib/mysql/* 修改配置文件 ~ sudo vi /etc/mysql/my.cnf [mysqld] innodb_file_per_table 重新構建數據庫實例 ~ /usr/bin/mysql_install_db ~ ls /var/lib/mysql mysql performance_schema test #啟動MySQL ~ sudo /etc/init.d/mysql start
3). 恢復其他數據庫
~ mysql < /run/shm/others.sql ~ mysql -umysql -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Macro | | TF | | conan | | dbwordpress | | mysql | | performance_schema | | phpmyadmin | | test | | wordpress | +--------------------+ 10 rows in set (0.01 sec) #查看ibdata1大小 ~ ls -l /var/lib/mysql drwx------ 2 mysql mysql 4096 Aug 2 21:33 CB drwx------ 2 mysql mysql 4096 Aug 2 21:23 conan drwx------ 2 mysql mysql 4096 Aug 2 21:23 dbwordpress -rw-rw---- 1 mysql mysql 18874368 Aug 2 21:34 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Aug 2 21:34 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Aug 2 21:34 ib_logfile1 drwx------ 2 mysql mysql 4096 Aug 2 21:23 Macro drwx------ 2 mysql root 4096 Aug 2 21:23 mysql drwx------ 2 mysql mysql 4096 Aug 2 21:19 performance_schema drwx------ 2 mysql mysql 4096 Aug 2 21:23 phpmyadmin drwx------ 2 mysql root 4096 Aug 2 21:19 test drwx------ 2 mysql mysql 4096 Aug 2 21:23 TF drwx------ 2 mysql mysql 4096 Aug 2 21:23 wordpress
4). 恢復CB數據庫
mysql> create database CB; Query OK, 1 row affected (0.00 sec) ~ mysql --database CB < /run/shm/export_cb_hft_20130801.sql ~ mysql --database CB < /dev/export_cb_hft_20130802.sql ~ mysql --database CB < /dev/export_NSpremium.sql #查看ibdata1大小：還是出初始值沒有增長 ~ ls -l /var/lib/mysql drwx------ 2 mysql mysql 4096 Aug 2 21:33 CB drwx------ 2 mysql mysql 4096 Aug 2 21:23 conan drwx------ 2 mysql mysql 4096 Aug 2 21:23 dbwordpress -rw-rw---- 1 mysql mysql 18874368 Aug 2 22:01 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Aug 2 22:01 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Aug 2 22:01 ib_logfile1 drwx------ 2 mysql mysql 4096 Aug 2 21:23 Macro drwx------ 2 mysql root 4096 Aug 2 21:23 mysql drwx------ 2 mysql mysql 4096 Aug 2 21:19 performance_schema drwx------ 2 mysql mysql 4096 Aug 2 21:23 phpmyadmin drwx------ 2 mysql root 4096 Aug 2 21:19 test drwx------ 2 mysql mysql 4096 Aug 2 21:23 TF drwx------ 2 mysql mysql 4096 Aug 2 21:23 wordpress #查看CB庫目錄：所有的數據都保存在自己單獨的數據文件 ~ ls -l /var/lib/mysql/CB -rw-rw---- 1 mysql mysql 9928 Aug 2 21:33 cb_hft_20130801.frm -rw-rw---- 1 mysql mysql 7159676928 Aug 2 22:08 cb_hft_20130801.ibd -rw-rw---- 1 mysql mysql 9928 Aug 2 22:09 cb_hft_20130802.frm -rw-rw---- 1 mysql mysql 7805599744 Aug 2 22:38 cb_hft_20130802.ibd -rw-rw---- 1 mysql mysql 61 Aug 2 21:30 db.opt
剛才設置的innodb_file_per_table參數已經起作用了，當我們再導出表drop後，對應的數據文件idb就會被刪除，系統硬盤空間使用就會在正常值范圍內。 查看表數據
mysql> show tables; +-----------------+ | Tables_in_CB | +-----------------+ | cb_hft_20130801 | | cb_hft_20130802 | +-----------------+ 2 rows in set (0.00 sec) mysql> select count(1) from cb_hft_20130801; +----------+ | count(1) | +----------+ | 21063172 | +----------+ 1 row in set (1 min 1.46 sec) #刪除表 ~ drop table cb_hft_20130801; #查看數據文件 ~ ls -l /var/lib/mysql/CB -rw-rw---- 1 mysql mysql 9928 Aug 2 22:09 cb_hft_20130802.frm -rw-rw---- 1 mysql mysql 7805599744 Aug 2 22:38 cb_hft_20130802.ibd -rw-rw---- 1 mysql mysql 61 Aug 2 21:30 db.opt -rw-rw---- 1 mysql mysql 9274 Aug 2 22:52 NSpremium.frm -rw-rw---- 1 mysql mysql 98304 Aug 2 22:53 NSpremium.ibd
drop後，數據就一起被刪除了。 經過對MySQL的調優，ibdata1已經被瘦身！數據庫又可以繼續正常的穩定的工作了。
