PDA

View Full Version : [ubuntu] MySQL Backup Methods



DanHorniblow
May 31st, 2011, 05:21 PM
Hi, I have a ubuntu 11.04 LAMP server running.
How would I go about backing up my MySQL database, do I need another server to mirror them to?

ruffEdgz
May 31st, 2011, 09:40 PM
You can run another server of MySQL if you wish if you need a bit of 'high availability' but if you just need to have a backup of your database, you can just dump data from the database you want to save into a file and rsync it to another server for safe keeping.


mysqldump -u [username] --password=[password] -C -e --create-options [database name] > backup.sql

The options I am using don't have to be the same as mine but I hope this helps with giving you an idea.

DanHorniblow
May 31st, 2011, 09:58 PM
Brilliant that works perfectly.

Would it be possible to schedule it to run every day, and save each backup as a different name?

ruffEdgz
May 31st, 2011, 11:22 PM
This is a basic way of completing what you requested. Run 'crontab' probably as root then add the following code:


@daily mysqldump -u [username] --password=[password] -C -e --create-options [database name] > /location/of/backup/backup.$(date +%F).sql

This isn't the best practice since this leaves the password out in the open. I would suggest looking at this article if you want to not show your password in the crontab:

http://www.serveridol.com/2010/04/12/mysql-passwordless-root-login/

Hope this helps.

tension_
June 1st, 2011, 08:06 AM
You can use this script work perfect .:)