Options for reducing size of a MySQL database
Got any indexes you don't actually use?
Ok... this post is OOOOLD .. but.. I think if is complete, is better
When I have to move a mysql database to another partition I do:
/etc/init.d/mysql stop
rsync -avz /var/lib/mysql/ /mnt/anotherdisk/mysql/
mv /var/lib/mysql/ /var/lib/mysql_original/
ln -s /var/lib/mysql/ /mnt/anotherdisk/mysql/
/etc/init.d/mysql start
After one day/week/month/WhenIRememberOrCan, I do a backup of /var/lib/mysql_original/ and then remove the folder.
If you're already considering moving the data to a larger partition, you can selectively move the larger tables and symlink them back into the database directory. There are some downsides to this, but they're noted in the documentation. This has the benefit of being easily backed out (assuming your tables still fit in the old partition).
Symbolic Links in MySQL
If most of your data is log aggregates, you basically have three options:
- Review your aggregation algorithm to see if it is designed to grow with the data, or designed to stay a fixed size.
- Acquire more storage.
- Stop storing logs in the database.
Are you prepared to rewrite the application?
OPTIMIZE TABLE will rebuild a table to remove "holes". This may save quite a lot of space, or none at all depending on how optimal they are already. This is however, very slow on a large table, AND USES QUITE A LOT OF TEMPORARY SPACE.
Dropping indexes, adding PACK_KEYS to the tables, these will reduce the size of the indexes, but again, this involves a rebuild AND USES TEMPORARY SPACE.
Have you looked at the size of the indexes vs. data?
Sounds like your server is so full you won't really be able to do any work.
SOLUTION: Use monitoring to ensure that in the future, your server never gets this full, as it's a lost cause by the time you reach this stage.
In the short term, transfer the whole lot off on to a bigger box.