MySQL Backups on Cloud Sites

I’ve been working with The Rackspace Cloud hosting for some time now, actually since back when it was Mosso. Where I absolutely love the service, the platform and the scalability, the lack of SSH really hurts at times when I need to work on Cloud Sites vs Cloud Servers.  Mostly it’s the times when I need to move, copy, backup large DB’s. After some trial and error I wrote some simple PHP code to handle 2 common tasks I find myself doing.  Those are Backing up and Transferring/copying databases for testing between sites.

Backups
If you just want to make a quick timestamped backup, tar it and store it in your account for reference (or further action) this works great:

$result = shell_exec('FILE=`date +%y-%m-%d_%H-%M`;cd /mnt/stor.../domain/web/; mysqldump --opt --single-transaction -h hostname -u username --password="password" database_name > $FILE.sql; tar czpf $FILE.tar $FILE.sql; rm -f $FILE.sql;');

That will create a database dump, store it in your /web folder tar it up and remove the .sql temp file.

—

Transfers
Additionally if you need to take a development database and move it to your live database (or pull one back, etc), the following code work great for me.

$result = shell_exec('cd /mnt/stor.../domain/web/;mysqldump --opt --single-transaction -h hostname1 -u username1 --password="password1" database_name1 > TRANSFER.sql; mysql -h hostname2 -u username2 --password="password2" database_name2 < TRANSFER.sql;');

This will connect to your source database and create a TRANSFER.sql file in your /web folder, then connect to your destination database and do a full insert. the —opt —single-transaction makes this go really smooth and fast. *note, that this assumes you already have your destination database created.  this code does not create the database for you, nor does it auto-delete the transfer.sql file.  However using a bit of the code from the backup line above im sure you could figure that much out with not too much work.

Happy databasing.