A primer

On several of our servers, the MySQL innodb data file was configured with auto extend, and we noticed that this was getting bigger for the one which was configured to be autoextended. Very recently on one of our production server, the MySQL failed due to a file size limitation of 3.9G, when the OS refused to let MySQL increase the size. By referring to the MySQL Documenation, we thought it would be a nightmare for us.

How we faced it

With an average knowledge in shell scripting, we thought to give it a trial, by using the console client and the supporting tools, along with the shell utilities. Get the raw pack Tar BZ2, Tar GZ, Zip. Please note that this should only be used by an experienced administrator in a root console.

Walk Through config

There is a config file, which is sourced by all the other scripts, which contains a security check which tests proper configurations, as well as the UID to ensure to an extend that no damage will happen. Though there is no guarantee that this will not do havok on another machine.

inno_clean

It should have been named it as dbdump, but started with this name and have gone too far with the implementaions that we feel at home with this name. The script does identify all the databases, and identifies the tables one by one creating a tempdir/dblist.txt and tempdir/dbname/tables.txt and follows by creating tempdir/dbname/tablename.create.sql and tempdir/dbname/tablename.data.sql. The whole operation is done for each and every database

rewrite

Yes as the name suggests, this is what it does, by scanning the dblist, moving through all databases, it checks for keyword ‘TYPE=InnoDB’, /*if you have a different version of MySQL it may be different (Tested with 4.1 and 4.12)*/, adds up to a alter.sql, a statement to alter the table type to InnoDB, uses sed to change the TYPE=InnoDB to TYPE=MyISAM. Here and there some echo statements have been spread out to show what is going on. Non InnoDB tables are not modified.

dbremove

This can only be run after the rewrite since we are depending on existence of alter.sql in the dbname/ folder for taking a decision whether the db should be dropped and recreated after truncating the innodb data file.
So as said this is the most dangerous part (it has been tested over). Just scans the dblist, checks for alter.sql files and if found, issues a forced drop dbname to the running mysql. This echoes status messages, if you have too many databases, then better redirect the output to a file, and tail it in another window.

Manual Part

If you have reached this far, then stop the MySQL service now!. Make sure everything is stable, and no service watch or watch dog or procautostart is kicking MySQL back into action. Since we want MySQL out of our way till we are done with this section. Now move all your innodb data files, and the logs out to a backup area (it is safe to delete them since it wont be of much use after the next step). Now modify your my.cnf to have the exact ibdata files; It would be better if you can stop some services for the time and increase some values in the mysqld variable section like table_cache etc. But not necessary. Once you think the tweaks are over, start the MySQL service, wait till it is ready by writing the innodb data file to the full configured size.

restore db

Yea! Now lets restore the databases back into the MySQL. Just like the dbremove, this will skip any MyISAM only databases, but will create the others and create the tables as MyISAM, populate with clean sequential data, and then convert into InnoDB using the alter.sql created in the rewrite section.

Conclusion

This worked for us, and our 3.9g and 2.4g secondary file is now 200 * 3 plus 50M autoextend, stays there after the restore also. On our development machine with 45 databases, having roughly 100 to 140 tables, most of them InnoDB, the whole process took around 15 minutes. And we are planning to automate everything even the MySQL part, such that the network can be disabled while the script kit is being run. And we are ready to shut down the service for this amount of time if it can keep our data files size under control.

MySQL’s Suggestion

Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:

• Use mysqldump to dump all your InnoDB tables
• Stop the server
• Remove all the existing tablespace files
• Configure a new tablespace
• Restart the server
• Import the dump files