MySQL: Reducing ibdata1(MySQL ibdata1文件瘦身)
If you use the InnoDB storage engine for (some of) your MySQL tables, you’ve probably already came across a problem with its default configuration. As you may have noticed in your MySQL’s data directory (in Debian/Ubuntu – /var/lib/mysql) lies a file called ‘ibdata1′. It holds almost all the InnoDB data (it’s not a transaction log) of the MySQL instance and could get quite big. By default this file has a initial size of 10Mb and it automatically extends. Unfortunately, by design InnoDB data files cannot shrinked. That’s why DELETEs, TRUNCATEs, DROPs, etc. can’t will not reclaim the space used by the file. Instead any freed regions are marked as unused and can be used later. Theoretically speaking the file could reach the maximum size allowed by the filesystem if no limit is set in the my.cnf file (in Debian/Ubuntu it’s located in /etc/mysql/my.cnf). Guess what ? It’s not set by default. However you can configure your InnoDB engine as described MySQL’s Reference Manual. Additionally you can force the server to create an *.ibd for each newly created InnoDB table by using the ‘innodb_file_per_table‘ option (quite intuitive, huh ? ).
As mentioned above, you cannot shrink InnoDB data files. Additionally, you cannot make much changes in the settings of a InnoDB data file. So if you haven’t configured InnoDB properly right after the installation, you’ll probably have a pretty large ibdata1 file. There are three ways to reclaim your free space, but before doing so backup your whole MySQL data directory… just in case. And don’t forget to stop any services using MySQL databases.
In order to use the first two methods you should have a list of all InnoDB tables in your MySQL instance. You can easily create one if your MySQL version is 5.0+ by using the special database called ‘INFORMATION SCHEMA‘. Just invoke this query:
SELECT `TABLE_SCHEMA`,`TABLE_NAME` FROM TABLES WHERE ENGINE = ‘InnoDB’;
Changing Table Engines
1. Invoke ‘ALTER TABLE `table_name` ENGINE=MyISAM‘ for each InnoDB table;
2. Stop the MySQL server;
3. Remove InnoDB data files;
4. Make the appropriate changes in your my.cnf;
5. Start the server again;
6. Invoke ‘ALTER TABLE `table_name` ENGINE=InnoDB‘ for those tables again;
Note: Any foreign key information is lost when changing the engine to MyISAM. You should save the output of ‘SHOW CREATE TABLE `table_name`‘ for each of those tables and recreate the foreign keys manually. So, that method sucks !
Dump InnoDB Tables
1. Use mysqldump to dump all InnoDB tables, for example:
mysqldump ––add-drop-table ––extended-insert ––disable-keys ––quick ‘db_name’ ––tables ‘tbl_name’ > ‘db_name.tbl_name.sql’
2. Drop those tables using:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE db_name.tbl_name;
DROP TABLE db_name1.tbl_name1;
–– DROP other tables here…
SET FOREIGN_KEY_CHECKS=1;
3. Stop the MySQL server;
4. Remove InnoDB data files;
5. Make the appropriate changes in my.cnf;
6. Start the MySQL server;
7. Re-import the tables. You’d better get into the ‘mysql’ console and issue the following commands:
SET FOREIGN_K
