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_KEY_CHECKS=0;
          SOURCE db_name.tbl_name.sql;
          SOURCE db_name1.tbl_name1.sql;
          –– SOURCE other files here…
          SET FOREIGN_KEY_CHECKS=1;

Note: This method is quite tedious too as you have to keep track on all tables and all dumped files… Yeah, I don’t like it either.
Dump the Whole Database

In fact, this is the method I used to solve the problem. It requires much more space and time but it’s maybe the easiest one. So here it is:

   1. Dump all databases by calling:

          /usr/bin/mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers > all-databases.sql

   2. Stop the MySQL server;
   3. Rename or remove (in case you’ve already backed it up) the MySQL data directory and create an empty one with the same name and permissions;
   4. Make the appropriate changes in my.cnf;
   5. Re-initialize the database with the following command (replace the ‘mysqld‘ with the login of the user your MySQL server runs as) (10x, Påven):

          sudo -u mysqld mysql_install_db

   6. Start the MySQL server;
   7. Get into the ‘mysql‘ console and type:

          SET FOREIGN_KEY_CHECKS=0;
          SOURCE all-databases.sql;
          SET FOREIGN_KEY_CHECKS=1;

   8. Restart the MySQL server. (10x, czaby)

At this point everything should be fine and you can test it by starting again the services that use MySQL. If not…
Troubleshoot Me

   1. Stop the MySQL server;
   2. Remove its data directory and put the backed up one (you’ve made a backup as mentioned above, haven’t you ?);
   3. Start the MySQL server again;
   4. Let me know what went wrong;

So… that’s it for today. Have fun !