If you care about the data being stored on your Apple computer, then you probably already use Time Machine as a backup solution. But if you're a developer or somebody else using MySQL, then you will soon or later reach the maximum capacity of your backup HDD. Why, just because the transaction logs (usually a file called ibdata) grows day after day and Time Machine seems not to be able to only backup the difference between 2 versions of this file.

I've faced this issue by the past and managed to solve it, but here it is, once again and because I've reinstalled my LAMP stack, my backup HDD is crying again and I decided to make a simple note on the topic.

Well, there are 2 solutions to deal with this problem :

• in Time Machine, ignore the folder where your data are stored (set by the datadir variable of your MySQL configuration)
• use one ibdata file per table (that's not possible to use one per database) 

If like me you don't want to ignore it from your automated backup, then just go for the second solution, which is detailed below :

Database(s) backup

 $ mysqldump -uroot -p --all-databases > all_databases.sql 

Or if you want to zip it at the same time

 $ mysqldump -uroot -p --all-databases | gzip > all_databases.sql.gz

Note : if like me you've created a lot of database on your system, this might be the time to delete some of them...

Check that you backup file has been correctly written on your system

 $ ls -lh
 $ total 1426728
 $ -rw-r--r-- 1 alafon staff 697M 12 jui 14:59 all_databases.sql.gz

Note : Also, you should copy the whole MySQL data folder as an other backup

 $ sudo cp -R /usr/local/mysql/data /usr/local/mysql/data_old

Drop your databases

Many solution to drop your database, so feel free to use your favorite one. From the MySQL CLI tool :

 show databases;
 drop database xxxx;

Stop your server

In my case, I'm using a homebrew installation, so that's pretty simple :

 $ mysql.server stop 

Configure it to use one file for each table

By default, there might not be any MySQL configuration file on your system, so just create it in the default location (/etc/my.cnf), and add the following :

 [mysqld]
 innodb_file_per_table 

 Delete the ibdata files

Remember : to know where your data are located, have a look at the datadir variable (mysql -uroot -p -e "show variables;" | grep datadir should help)

 $ ls -l /usr/local/var/mysql/
 $ ....

 $ rm /usr/local/var/mysql/ib*

Start your server again and re-import your backup

 $ mysql.server start

 $ mysql -uroot -p < all_databases.sql 

or if you did zip the dump

 $ gunzip < all_databases.sql.gz | mysql -uroot -p 

Last but not least

Unless you want to keep them during your entire life, you can frequently remove (I'm not talking about ignoring it) the /usr/local/mysql/data folder from the Time Machine viewer (obviously, after a manual MySQL dump...). As far as I'm concerned the purpose of using Time Machine in this case, is not to restore a 6 months old version of your MySQL data, it's just there to deal with a recent crash...

Tags: Backup , Mac OS X , MySQL , Time Machine