Slimming MySQL Data File ibdata1

For MySQL Data File ibdata1 slimming MySQL after running for a period of time, ibdata1 files will increase the size, even if the table data is deleted, the ibdata1 volume will not decrease. Due to limited hard disk space, the disk space continues to expand and is close to crashing. Today, when exporting data, I found that the disk is full. I have reserved the usage for one month and it will be full in one week. I will try to slim down ibdata1 below. 1. System Environment Linux Ubuntu 13.04 64bit server ~ Uname-aLinux 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/issueUbuntu 13.04 \ n \ lMySQL: 5.5.31-0ubuntu0. 13.04.1 ~ Mysql -- versionmysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2 hard drive: 36G + 4G + 4G + 36G

~ Df-hFilesystem Size Used Avail Use % Mounted on/dev/mapper/server3 -- vg-root 36G 31G 3.2G 91%/none 4.0 K 0 4.0 K 0%/sys/fs/ cgroupudev 4.1G 1.1G 3.0G 26%/devtmpfs 824 M 280 K 823 M 1%/runnone 5.0 M 0 5.0 M 0%/run/locknone 4.1G 3.4G 729 M 83%/run/shmnone 100 M 0 100 M 0%/run/user/dev/vda1 228 M 30 M 187 M 14%/boot192.168.1.10: /home/amg/data 36G 13G 21G 39%/home/amg/dataMySQL ibdata1 space: 20G ~ Cd/var/lib/mysql ~ Ls-ldrwxr-xr-x 2 mysql 4096 Aug 2 CBdrwxr-xr-x 2 mysql 4096 Jun 24 08 conandrwxr-xr-x 2 mysql 4096 Jun 2 dbwordpress- rwxr-xr-x 1 root 0 May 23 debian-5.5.flag-rwxr-xr-x 1 mysql 20101201920 Aug 2 ibdata1-rwxr-xr-x 1 mysql 5242880 Aug 2 ib_logfile0-rwxr-xr-x 1 mysql 5242880 Aug 2 ib_logfile1drwxr-xr-x 2 mysql 4096 Jun 26 Macrodrwxr-xr-x 2 mysql root 4096 May 23 mysql-rwxr-xr-x 1 root 6 May 23 mysql_upgrade_infodrwxr-xr-x 2 mysql 4096 May 23 performance_schemadrwxr-xr-x 2 mysql 4096 May 23 phpmyadmindrwxr-xr-x 2 mysql root 4096 May 23 48 testdrwxr-xr-x 2 mysql 4096 Jul 22 09 TFdrwxr-xr-x 2 mysql 4096 Jun 2 wordpress business data table mysql> show tables; + --------------- + | Tables_in_CB | + ----------------- + | NSpremium | cb_hft | cb_hft_20130801 | bytes | + ----------------- + 4 rows in set (0.00 sec)

 

2. An ibdata1 file occupies 20 GB. 1. MySQL is set by default. Data is not separated by tablespace. All table data is stored in the ibdata1 file. 2. for business operations, a cb_hft table is generated every day and renamed at night. Rename table cb_hft TO cb_hft_20130801; create table cb_hft like cb_hft_20130801; 3. Export data every week and drop the TABLE. However, after the drop operation, ibdata1 will not decrease. As the data accumulation increases, ibdata1 will not be enough to use the root space. 3. solution 1). The exported data is now in the database and has two data tables, cb_hft_20130801 and cb_hft_20130802, which are exported to/run/shm,/dev respectively.

~ 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-hFilesystem Size Used Avail Use % Mounted on/dev/mapper/server3 -- vg-root 36G 31G 3.2G 91%/none 4.0 K 0 4.0 K 0%/sys/fs/ cgroupudev 4.1G 3.7G 368 M 92%/devtmpfs 824 M 280 K 823 M 1%/runnone 5.0 M 0 5.0 M 0%/run/locknone 4.1G 3.4G 729 M 83%/run/shmnone 100 M 0 100 M 0%/run/user/dev/vda1 228 M 30 M 187 M 14%/boot192.168.1.10: the/home/amg/data 36G 13G 21G 39%/home/amg/data Tables account for 3.4G and 3.7G respectively. Log onto mysql to delete the CB database ~ Mysql-uroot-p ~ Drop database CB export other database data ~ Cd/run/shm ~ Mysqldump-uroot-p-R-q -- all-databases> others. SQL

 

2) modify the configuration file/etc/mysql/my. cnf and use a separate data file storage innodb_file_per_table for each table.

Stop mysql Server ~ Sudo/etc/init. d/mysql stop # Clear all data files ~ Sudo rm-rf/var/lib/mysql/* modify the configuration file ~ Sudo vi/etc/mysql/my. cnf [mysqld] innodb_file_per_table re-build the database instance ~ /Usr/bin/mysql_install_db ~ Ls/var/lib/mysqlmysql performance_schema test # Start MySQL ~ Sudo/etc/init. d/mysql start

 

3). Restore other databases

~ 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) # view ibdata1 size ~ Ls-l/var/lib/mysqldrwx ------ 2 mysql 4096 Aug 2 CBdrwx ------ 2 mysql 4096 Aug 2 conandrwx ------ 2 mysql 4096 Aug 2 dbwordpress-rw ---- 1 mysql 18874368 Aug 2 ibdata1-rw-rw ---- 1 mysql 5242880 Aug 2 ib_logfile0-rw-rw ---- 1 mysql 5242880 Aug 2 ib_logfile1drwx ------ 2 mysql 4096 Aug 2 Macrodrwx ------ 2 mysql root 4096 Aug 2 mysqldrwx ------ 2 mysql 4096 Aug 2 performance_schemadrwx ------ 2 mysql 4096 Aug 2 phpmyadmindrwx ------ 2 mysql root 4096 Aug 2 testdrwx ------ 2 mysql 4096 Aug 2 TFdrwx ------ 2 mysql 4096 Aug 2 wordpress

 

4). Restore the CB database

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 # Check whether the size of ibdata1 has not increased ~ Ls-l/var/lib/mysqldrwx ------ 2 mysql 4096 Aug 2 CBdrwx ------ 2 mysql 4096 Aug 2 conandrwx ------ 2 mysql 4096 Aug 2 dbwordpress-rw ---- 1 mysql 18874368 Aug 2 ibdata1-rw-rw ---- 1 mysql 5242880 Aug 2 ib_logfile0-rw-rw ---- 1 mysql 5242880 Aug 2 ib_logfile1drwx ------ 2 mysql 4096 Aug 2 Macrodrwx ------ 2 mysql root 4096 Aug 2 mysqldrwx ------ 2 mysql 4096 Aug 2 performance_schemadrwx ------ 2 mysql 4096 Aug 2 phpmyadmindrwx ------ 2 mysql root 4096 Aug 2 testdrwx ------ 2 mysql 4096 Aug 2 TFdrwx ------ 2 mysql 4096 Aug 2 wordpress # view the CB library directory: all data is stored in a separate data file ~ Ls-l/var/lib/mysql/CB-rw ---- 1 mysql 9928 Aug 2 cb_hft_20130801.frm-rw-rw ---- 1 mysql 7159676928 Aug 2 cb_hft_20130801.ibd-rw-rw ---- 1 mysql 9928 Aug 2 cb_hft_20130802.frm-rw-rw ---- 1 mysql 7805599744 Aug 2 cb_hft_20130802.ibd-rw-rw ---- 1 mysql 61 Aug 2 db. opt

 

The innodb_file_per_table parameter just set has taken effect. After we export the table drop, the corresponding data file idb will be deleted, and the system hard disk space will be used within the normal range. View table data

Mysql> show tables; + --------------- + | Tables_in_CB | + ----------------- + | cb_hft_20130801 | bytes | + ----------------- + 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) # delete a table ~ Drop table cb_hft_20130801; # View data files ~ Ls-l/var/lib/mysql/CB-rw ---- 1 mysql 9928 Aug 2 cb_hft_20130802.frm-rw-rw ---- 1 mysql 7805599744 Aug 2 cb_hft_20130802.ibd-rw-rw ---- 1 mysql 61 Aug 2 db. opt-rw ---- 1 mysql 9274 Aug 2 NSpremium. frm-rw ---- 1 mysql 98304 Aug 2 NSpremium. ibd

 

After drop, the data is deleted. After tuning MySQL, ibdata1 has been reduced! The database can continue to work normally and stably.