0

I have a vps with ubuntu 9 server.

I need to backup my MySql database.

Can MySql make backups automatically? If so, how?

If not, how should I do it then?

The website is a classifieds website (PHP, MySql etc)

Thanks

Anonymous12345
  • 1,022
  • 2
  • 13
  • 18

2 Answers2

3

Generally you just write a script to it and put make a cron entry for the script so it runs as often as you want. There are a bunch of examples here.

Database Backup Crash Course for a Programmer:

  • You want the dumps to either be run from another server (at least) or be copied over to another server. Ideally they go to a different geographic location.
  • Another option is to do it all locally, and then have your tape backup be the backup of those dumps. With this, make sure you time it right. For example, if you dump happens right after the tape back up (and you backup daily) and then your server dies right before a tape backup starts, your data will be near 48 hours old. This is a little confusing, but if you draw a timeline it makes sense (or maybe a loop with a sleep statement if you are programmer :-) ). So basically, make sure the dump runs before the tape backup.
  • It is always good to have multiple versions of a backup.

Bad:

outfile = ''
i = 0
while backup:
    version = i % 7;
    //In this case, the dump is from from the previous iteration of loop
    Tape_Backup(outfile); 
    DB_Dump(outfile=strcat('dump.' + version + '.tar.gz'));
    Sleep(1 day);

Good:

outfile = ''
i = 0
while backup:
    version = i % 7;
    DB_Dump(outfile=strcat('dump.' + version + '.tar.gz'));
    Tape_Backup(outfile);
    Sleep(1 day);
Community
  • 1
Kyle Brandt
  • 83,619
  • 74
  • 305
  • 448
  • Might be something wrong with that pseudo code, but hopefully you get the ideas. – Kyle Brandt May 28 '10 at 11:53
  • The better way to do it, methinks, would be to drop the backup loop and put it in a cronjob. That way you have better control over the timing. You also don't have ever-shifting times of day that it runs; it's not shifting by the duration of DB_Dump and Tape_backup. – Kevin M May 28 '10 at 13:06
  • Kevin M: The code was just kind of a silly way of making my point for a programmer, I didn't mean actually use a while loop ;-) – Kyle Brandt May 28 '10 at 18:38
0

You make database backups of MySQL databases by using the mysqldump command. You would simply create a cron entry that would execute the mysqldump command.

Will
  • 826
  • 2
  • 9
  • 19