Page 1 of 1
Best way to automatically backup MySQL data?
Posted: Sun Mar 02, 2008 7:57 pm
by Josh1billion
Currently, I regularly back up my database with the Export feature in phpMyAdmin. I back up only around 5 of the tables and don't want to back up the others. However, I have to do the backups manually, which is time-consuming-- what is the best way/software-used to automate this process?
Re: Best way to automatically backup MySQL data?
Posted: Sun Mar 02, 2008 9:04 pm
by Christopher
What OS? You can use the mysqldump utility to export the files. On Unix you can schedule it with a cron and use something like rsync to push/pull it to another system.
Re: Best way to automatically backup MySQL data?
Posted: Mon Mar 03, 2008 9:49 am
by pickle
Croning a backup is definitely the easiest way. Depending on your situation, it'd probably be easiest to just cp/scp the specific database files (.MYI, .MYD, etc).
Re: Best way to automatically backup MySQL data?
Posted: Mon Mar 03, 2008 11:25 am
by Josh1billion
I didn't understand most of either of your replies.

Re: Best way to automatically backup MySQL data?
Posted: Mon Mar 03, 2008 11:35 am
by Christopher
Look at the docs for mysqldump (or whatever it is called). What OS are you using?
Re: Best way to automatically backup MySQL data?
Posted: Mon Mar 03, 2008 2:19 pm
by Josh1billion
I'm using Windows, but the server is on Linux. For backups, I normally just use the "Export" feature of phpMyAdmin, but of course that's manual and takes up some of my time that I could otherwise be using more efficiently.
Re: Best way to automatically backup MySQL data?
Posted: Mon Mar 03, 2008 2:58 pm
by Zoxive
http://www.google.com/search?q=mysqldump
Code: Select all
kyle@w3jubuntu:~$ mysqldump tablename > myfile.sql
Where are you backing the database up? Locally or on the server (ie. different hd on the server)
Re: Best way to automatically backup MySQL data?
Posted: Mon Mar 03, 2008 3:05 pm
by Josh1billion
I normally back up to my own computer. So are you suggesting to use cron to set up that mysqldump command? Then I can go into FTP and retrieve the .sql file?
Re: Best way to automatically backup MySQL data?
Posted: Mon Mar 03, 2008 3:18 pm
by Zoxive
Josh1billion wrote:I normally back up to my own computer. So are you suggesting to use cron to set up that mysqldump command? Then I can go into FTP and retrieve the .sql file?
You are going to have to still run some sort of command since you are running windows locally. And want the file locally.
Now if you were running linux locally you could locally set up a cron job to execute that command and sync the file back to your computer using ssh. You might be able to archive the same thing using putty (Windows ssh client) but I only have used putty on rare occasions.
Re: Best way to automatically backup MySQL data?
Posted: Mon Mar 03, 2008 8:00 pm
by Josh1billion
If I use mysqldump, how would I go about restoring the backup?
Re: Best way to automatically backup MySQL data?
Posted: Mon Mar 03, 2008 8:18 pm
by Benjamin
Here's some code I wrote a long time ago for creating dumps with PHP..
Code: Select all
$now = date("Y-m-d-H-i-s");
exec("/usr/bin/mysqldump --opt -u root --password=$root_pass $database_name > /home/username/backups/$now.sql");
exec("bzip2 /home/username/backups/$now.sql");
You can restore a backup by doing something like this..
Code: Select all
mysql -uroot -p database_name < backupfile.sql
Re: Best way to automatically backup MySQL data?
Posted: Tue Mar 04, 2008 12:11 am
by Josh1billion
Nice, that code looks good and I'll give it a try. Thanks Astions and others.

Re: Best way to automatically backup MySQL data?
Posted: Tue Mar 04, 2008 4:54 pm
by nincha
i like to use cron job the best