Shrink MySQL ibdata1 file size method

IBDATA1 is a data file in the MySQL database, you will find that the larger it comes, let me introduce the Ibdata1 file size method for shrinking MySQL

If you have to use InnoDB to store your MySQL table, use the default settings should encounter a very headache problem, in the MySQL data directory has a default only 10MB called Ibdata1 file The growth of night and night is annoying you? It contains all the indexes and data stored in the InnoDB engine, but unfortunately MySQL does not shrink the InnoDB table when it is designed, which is why there is no reduction in the size of this file when you delete,truncate,drop these tables. And you have no way to visually see which database occupies a large number of ibdata1, this problem in the new version of MySQL has been innodb_file_per_table this option to solve, when this option is turned on, each InnoDB table index and data will press *. IBD names are stored in individual databases, but this option is not turned on by default.

As said above, you can't shrink the InnoDB data file, you can not be in a machine without opening the innodb_file_per_table option to directly add this option to let him work, you have to install the MySQL to add this option, or follow the three ways described in this article to operate, No matter what you do, make sure you've backed up the entire database directory, and don't forget to stop some MySQL-related services.

The first two methods need to get a list of all the InnoDB in the MySQL DB instance, which is easy if you're using more than 5.0 versions: SELECT table_schema,table_name from TABLES WHERE ENGINE = ' InnoDB ';

Modify the table engine
1. Perform ALTER table table_name Engine=myisam for each INNODB table;
2. Stop the MySQL service;
3. Remove InnoDB related files ibdata1, etc.
4. Modify the parameters in My.cnf, add innodb_file_per_table;
5. Start the MySQL service;
6. Change those tables that you just modified back to Innodb:alter table table_name Engine=innodb;
Export InnoDB table
1. Export all mysqldump tables using the InnoDB command, for example: mysqldump –add-drop-table–extended-insert–disable-keys–quick ' db_name ' –tables ' tbl_name ' > ' Db_name.tbl_name.sql '
2. Remove these tables:  
? SET foreign_key_checks=0;
? DROP TABLE Db_name.tbl_name;
? DROP TABLE db_name1.tbl_name1;
? ––drop other tables ...
? SET Foreign_key_checks=1;
3. Stop the MySQL service;
4. Remove InnoDB related files ibdata1 etc.
5. Modify the parameters in the my.cnf, add innodb_file_per_table;
6. Start the MySQL service;
7. Import table under MySQL console:  
? SET foreign_key_checks=0;
? SOURCE Db_name.tbl_name.sql;
? SOURCE Db_name1.tbl_name1.sql;
? ––source other files here ...
? SET Foreign_key_checks=1;
Export Entire database
This is what I used to do, although he and the disk and time, but it is the simplest:

1. Export All data:/usr/bin/mysqldump––extended-insert––all-databases––add-drop-database––disable-keys–– Flush-privileges––quick––routines––triggers > All-databases.sql
2. Stop the MySQL service;
3. Rename the MySQL data folder;
4. Modify the parameters in the MY.CNF, add the innodb_file_per_table;
5.mysql_install_db reinitialization of Mysqld;
6. Turn on the MySQL service;
7. Go to MySQL console to execute:
? SET foreign_key_checks=0;
? SOURCE All-databases.sql;
? SET Foreign_key_checks=1;
8. Restart the database test OK, let's get the reward.

Shrink MySQL ibdata1 file size method