From:http://blog.chinaunix.net/uid-24373487-id-4223322.html
Original address: MySql ibdata1 file too large how to reduce EMAILWHT
MySql ibdata1 file too large how to zoom out
MYSQL InnoDB If it is a shared tablespace, the ibdata1 file is getting bigger and larger, reaching more than 30 g, emptying some useless tables:
truncate TABLE xxx;
then optimize table xxx; no effect
because the shared table space does not work.
MySQL ibdata1 store data, indexes, etc., is the most important data of MySQL.
If the data is not stored separately, the size of the file can easily be on the G, or even dozens of G. For some applications, this is not a good fit. So make this file smaller.
unable to automatically shrink, must data export, delete ibdata1, then data import, compare cumbersome, so need to change each table separate file.
Workaround: The data file is stored separately (how the shared tablespace is changed to a separate tablespace file for each table).
The steps are as follows:
1) Backing up the database
back up all databases, execute commands
#mysqldump-Q-uroot-ppassword--add-drop-table--all-databases >/home/backup/all.sql
After you finish this step, stop the database service.
#service mysqld Stop
2) Locate the My.ini or my.cnf file
execute under Linux
#/usr/libexec/mysqld--verbose--help | grep-a 1 ' Default options '
Default Options is read from the following files in the given order:
/etc/mysql/my.cnf/etc/my.cnf ~/.my.cnf
The Windows environment allows you to:
mysqld--verbose--help > Mysqlhelp.txt
Notepad mysqlhelp.txt
Find the default options inside, and you can see the order of finding My.ini to find the real directory
3) Modify the MySQL configuration file
Open My.ini or my.cnf file
under [mysqld], add the following configuration
innodb_file_per_table=1
Verify that the configuration is in effect and you can restart MySQL to perform
#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>
See if the innodb_file_per_table variable is on
4) Delete the original data file
Delete the original ibdata1 file and log file ib_logfile*, delete the application database folder under/var/lib/mysql directory (MySQL folder do not delete)
5) Restore the database
Start the database service
enter MySQL Server from the command line
restore all databases, execute commands
#service mysqld Start
#mysql-uroot-pocs
After these steps, you can see that the new Ibdata1 file is only dozens of M, and the data and indexes become small IBD files for a single table, which are under the folder of the corresponding database.
# ll
Total 295028
drwx------2 mysql mysql 36864 Apr 14:16 glpi
drwx------2 mysql mysql 36864 Feb 13:45 glpi-1
-rw-rw----1 mysql mysql 10485760 Apr 14:27 ibdata1
-rw-rw----. 1 mysql mysql 270532608 Apr 14:14 ibdata1-1
-rw-rw----1 mysql mysql 5242880 Apr 14:27 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Apr 14:14 ib_logfile0_bak
-rw-rw----1 mysql mysql 5242880 Apr 14:28 ib_logfile1
-rw-rw----. 1 mysql mysql 5242880 Apr 22:50 ib_logfile1_bak
drwx------2 mysql mysql 4096 Apr 14:16 mrbs
drwx------2 mysql mysql 4096 Apr 12:05 mrbs-1
drwx------. 2 mysql mysql 4096 Apr 14:16 MySQL
srwxrwxrwx 1 mysql mysql 0 Apr 14:16 mysql.sock
drwx------2 mysql mysql 12288 Apr 14:16 ocsweb
drwx------2 mysql mysql 12288 Nov ocsweb-1
# ll Mrbs
Total 808
-rw-rw----1 mysql mysql April 14:16 db.opt
-rw-rw----1 mysql mysql 10492 Apr 14:16 mrbs_area.frm
-rw-rw----1 mysql mysql 98304 Apr 14:16 mrbs_area.ibd
-rw-rw----1 mysql mysql 9264 Apr 14:16 mrbs_entry.frm
-rw-rw----1 mysql mysql 131072 Apr 14:16 mrbs_entry.ibd
-rw-rw----1 mysql mysql 9442 Apr 14:16 mrbs_repeat.frm
-rw-rw----1 mysql mysql 98304 Apr 14:16 mrbs_repeat.ibd
-rw-rw----1 mysql mysql 8888 Apr 14:16 mrbs_room.frm
-rw-rw----1 mysql mysql 114688 Apr 14:16 mrbs_room.ibd
-rw-rw----1 mysql mysql 8688 Apr 14:16 mrbs_users.frm
-rw-rw----1 mysql mysql 98304 Apr 14:16 mrbs_users.ibd
-rw-rw----1 mysql mysql 8658 Apr 14:16 mrbs_variables.frm
-rw-rw----1 mysql mysql 98304 Apr 14:16 mrbs_variables.ibd
-rw-rw----1 mysql mysql 8738 Apr 14:16 mrbs_zoneinfo.frm
-rw-rw----1 mysql mysql 98304 Apr 14:16 mrbs_zoneinfo.ibd
# ll Mrbs-1
Total
-rw-rw----1 mysql mysql April 12:05 db.opt
-rw-rw----1 mysql mysql 10492 Apr 12:05 mrbs_area.frm
-rw-rw----1 mysql mysql 9264 Apr 12:05 mrbs_entry.frm
-rw-rw----1 mysql mysql 9442 Apr 12:05 mrbs_repeat.frm
-rw-rw----1 mysql mysql 8888 Apr 12:05 mrbs_room.frm
-rw-rw----1 mysql mysql 8688 Apr 12:05 mrbs_users.frm
-rw-rw----1 mysql mysql 8658 Apr 12:05 mrbs_variables.frm
-rw-rw----1 mysql mysql 8738 Apr 12:05 mrbs_zoneinfo.frm
Go MYSQL ibdata1 file too large how to zoom out
