Automatically backing up the sql database?

What’s the easiest free way to have the sql databases automatically backed up once a day, once a week and once a month please ?

Any help appreciated.

Dez.

Apologies for jumping in late, but I think what’s happening is;

You are using the mysqldump on your local computer to access a database on some server somewhere - this should cause no problems if the server accepts remote connections (not all do). But, from your error message, it looks like you’re trying to login in as “username@localhost” and without a password. I would have thought a password was needed and the username suggests that the “user” trying to access the database is something running on the same server as the database.

Hi, thanks for your input above, it’s appreciated. I’m trying desperately to solve this, but still can’t ;-( I’m trying this in the crons :

mysqldump -u username -p password databasename | gzip | uuencode FileName.sql.gz | mail -s "Daily Backup of databases" "emailaddress"

But when the emails come through, the backup is empty and around the same time, I get emails saying :

Enter password: mysqldump: Got error: 1045: Access denied for user 'username'@'localhost' (using password: NO) when trying to connect
/bin/sh: IxsL4OtV]: command not found

After all the work, it’s almost complete, so if anyone can lend a hand and prevent me pulling out any more hair, please help if you can :slight_smile:

Dez.

windows scheduled task (or cron job, if you prefer) running mysqldump

Thanks for that info, it’s appreciated. Have been looking through Google etc, for details on MySqlDump, but must admit, most of the sources seem to assume that the person reading it, has intermediate knowledge of databases and crons, which I don’t have, Having said that, I have setup one cron with my site, so still looking for something that can somehow explain it all in complete newbie language.

First, construct and test the mysqldump command to generate the dump file you want in the location you want it.

Then, read the wiki page on crontab to get the syntax to run a command once a day, once a week and once a month.

Then, combine the two. This is just pasting part #1 after part #2.

You’ll end with a crontab entry like:

0 * * * * mysqldump -uusername -ppassword dbname > /path/to/backupfile.sql

Pretty sure this is going to come out as a dumb question, but I was wondering: Is simply copying the files (MYD, FRM etc) not a reliable way of backing up/transferring tables? Whenever I’ve tried it it seems to work. I’m sure there’s a very good reason why it’s not usually suggested, but I’d like to know anyway.

You might be copying out of date (updates not yet flushed to disk), or even partially-written (i.e. corrupt) files.

This is only safe if you shut down MySQL, fsync to force the OS to flush buffered writes to disk, then make your copy.

Thanks Dan, have read through the wiki entry, and understood at least half of it :slight_smile: but, one thing, how would I adjust that code :

0 * * * * mysqldump -uusername -ppassword dbname > /path/to/backupfile.sql

to save the backup to my pc ?

The helps appreciated.

Dez.

If you run that code on the server, it can’t save on your PC. I can’t run a program on my PC that saves a file on your PC.

You can run mysqldump locally on your own PC, and have it connect to the database remotely.

Or you can make the backup on the server, and then transfer it to your PC. FTP, SCP or rsync to transfer it.

Yep, definitely knew that you couldn’t save a file to my pc, but have been reading up on this quite a bit and still can’t seem to find the way to do it - on the link below :

it says :

“This article assumes that you have MySQL installed locally on a Windows, Unix or Linux machine and have administrative privileges on that machine. This article also assumes that you have at least a small amount of exposure to MySQL and the SQL language syntax.”

If so, I assume I need to install MySQL onto my machine ?

When an article says “this article assumes…” they’re telling you the conditions under which the commands they give you apply. If you have different conditions, then you need to figure some things out on your own. It doesn’t mean you have to replicate their situation.

You do not need to run the mysql server on your own computer to be able to back up to it there. mysqldump is a separate program and can connect to remote hosts.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Have you thought about how you want to do it? Make the backup on the server and copy the backup, or make the backup locally with a local copy of mysqldump?

Thanks Dan - I’m just trying to setup a system, where a backup automatically happens, so that the database is saved onto my pc every 8 hours. (if the pc isn’t on at the time, then it will save it to it, the next time the pc is switched on).

Alright.

  1. Start by downloading mysql so that you can get a copy of mysqldump.

  2. Then write the command to make the backup and test it. I linked the manual for mysqldump in the last post and gave some examples earlier. It’ll probably be something like this:

mysqldump -h yoursite.com -uusername -ppassword databasename > c:\\path\	o\\backup.sql

Your username and password really should be right after the -u and -p without a space. The last part is where you want to save the file.

  1. Then you run Windows Task Scheduler and create a task so that it runs that command every 8 hours.

Thanks Dan, your perseverance is appreciated, but when you say install mysql - whereabouts is the best place to get that for free please ?

Is it one of the ones on the link below ? If so, which one ? Quite a lot of choices there ? :slight_smile:

http://www.mysql.com/downloads/mysql/

yes that link will do. download newest version of 5.1.x in my opinion for where you are starting from. it will be the current production release for example. others are still beta.

Thanks for that, but they’re all 5.1 xx’s there ?

Have we lost Dan ?