Best way to automatically backup MySQL data?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Best way to automatically backup MySQL data?

Post 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?
User avatar
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?

Post 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.
(#10850)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Best way to automatically backup MySQL data?

Post 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).
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Re: Best way to automatically backup MySQL data?

Post by Josh1billion »

I didn't understand most of either of your replies. :?
User avatar
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?

Post by Christopher »

Look at the docs for mysqldump (or whatever it is called). What OS are you using?
(#10850)
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Re: Best way to automatically backup MySQL data?

Post 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.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Best way to automatically backup MySQL data?

Post 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)
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Re: Best way to automatically backup MySQL data?

Post 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?
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Best way to automatically backup MySQL data?

Post 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.
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Re: Best way to automatically backup MySQL data?

Post by Josh1billion »

If I use mysqldump, how would I go about restoring the backup?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Best way to automatically backup MySQL data?

Post 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
 
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Re: Best way to automatically backup MySQL data?

Post by Josh1billion »

Nice, that code looks good and I'll give it a try. Thanks Astions and others. :)
nincha
Forum Contributor
Posts: 191
Joined: Fri Mar 28, 2003 12:30 pm
Location: CA, USA

Re: Best way to automatically backup MySQL data?

Post by nincha »

i like to use cron job the best
Post Reply