Best way to automatically backup MySQL data?
Moderator: General Moderators
- Josh1billion
- Forum Contributor
- Posts: 316
- Joined: Tue Sep 11, 2007 3:25 pm
Best way to automatically backup MySQL data?
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?
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Best way to automatically backup MySQL data?
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.
(#10850)
Re: Best way to automatically backup MySQL data?
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).
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
- Josh1billion
- Forum Contributor
- Posts: 316
- Joined: Tue Sep 11, 2007 3:25 pm
Re: Best way to automatically backup MySQL data?
I didn't understand most of either of your replies. 
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Best way to automatically backup MySQL data?
Look at the docs for mysqldump (or whatever it is called). What OS are you using?
(#10850)
- Josh1billion
- Forum Contributor
- Posts: 316
- Joined: Tue Sep 11, 2007 3:25 pm
Re: Best way to automatically backup MySQL data?
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?
http://www.google.com/search?q=mysqldump
Where are you backing the database up? Locally or on the server (ie. different hd on the server)
Code: Select all
kyle@w3jubuntu:~$ mysqldump tablename > myfile.sql- Josh1billion
- Forum Contributor
- Posts: 316
- Joined: Tue Sep 11, 2007 3:25 pm
Re: Best way to automatically backup MySQL data?
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?
You are going to have to still run some sort of command since you are running windows locally. And want the file locally.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?
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.
- Josh1billion
- Forum Contributor
- Posts: 316
- Joined: Tue Sep 11, 2007 3:25 pm
Re: Best way to automatically backup MySQL data?
If I use mysqldump, how would I go about restoring the backup?
Re: Best way to automatically backup MySQL data?
Here's some code I wrote a long time ago for creating dumps with PHP..
You can restore a backup by doing something like this..
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");
Code: Select all
mysql -uroot -p database_name < backupfile.sql
- Josh1billion
- Forum Contributor
- Posts: 316
- Joined: Tue Sep 11, 2007 3:25 pm
Re: Best way to automatically backup MySQL data?
Nice, that code looks good and I'll give it a try. Thanks Astions and others. 
Re: Best way to automatically backup MySQL data?
i like to use cron job the best