Ibdata1 file size method to shrink MySQL

If you have the use of InnoDB to store your MySQL table, use the default settings should have a very headache problem, in the MySQL data directory has a default only 10MB called Ibdata1 file The growth of night and night you are tired of it? It contains all the index and data information stored by the InnoDB engine, unfortunately MySQL does not have the ability to shrink the InnoDB table when it is designed, which is why you do not have the slightest reduction in the size of the 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 pit Dad's problem in the new version of MySQL has been innodb_file_per_table this option to solve, when the option is opened, each InnoDB table index and data will press *. IBD naming is stored in each database, but this option is not enabled by default.

As mentioned above, you can not shrink InnoDB data file, you have no way to open the innodb_file_per_table option on the machine directly add this option to let him work, you have to install MySQL to add this option, or according to the three ways described in this article to operate, Whatever you do, make sure you've backed up the entire database directory, and don't forget to stop some of the MySQL-related services.

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

The

modifies the table engine
1. Execute ALTER table table_name Engine=myisam for each INNODB table;
2. Stop MySQL service;
3. Remove InnoDB related files ibdata1,
4. Modify the parameters in My.cnf, add innodb_file_per_table;
5. Start the MySQL service;
6. Change those tables that you have 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. Delete 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 MySQL service;
4. Remove InnoDB related files ibdata1,
5. Modify the parameters in 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 ...
◦set Foreign_key_checks=1;
Export the entire database
This is my usual, although he and the disk and time, but is indeed the easiest:

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 MySQL service;
3. Rename the MySQL data folder;
4. Modify the parameters in My.cnf, add innodb_file_per_table
5.mysql_install_db mysqld;
6. Open MySQL service;
7. Enter MySQL console execution:
◦set foreign_key_checks=0
◦source all-databases.sql;
◦set Foreign_key_checks=1;
8. Restart database test OK, let's take the reward.