Creating Automatic Mysql Database Backups

[danw]

I wanted to get nightly backups of the database that runs my site, so hunted something down: http://www.restkultur.ch/personal/wolf/scripts/db_backup/

A very cool script (which can be automated with Cron)!

Dan

[dbmasters]

doesn't DA do backups automatically?

[skidawg]

Yes it does.

Go into your control panel (http://www.domainname.com:2222). Click on the MySQL Management link under the Your Account section. There will be a table showing all of the databases that you have created on this domain. Just click the Download link in the Download Backup column. That will download a sql dump of your database in gzipped format.

This is a very handy feature that I use all the time to download my database backups.

[danw]

Originally posted by skidawg@Dec 13 2004, 03:44 PM
Go into your control panel (http://www.domainname.com:2222). Click on the MySQL Management link under the Your Account section. There will be a table showing all of the databases that you have created on this domain. Just click the Download link in the Download Backup column. That will download a sql dump of your database in gzipped format.


Yes, you can manually download a copy of your database. But I don't want to do that every night. I update my site almost daily, so to have a copy of the database that's about that old, I'd have to do a download every. single. day. See how old that'd get? With the above-mentioned software, a daily/nightly backup is saved for me--with no intervention from me.

[skidawg]

Well, if you look at the that backup file name, you will see that it is a static name. Because of this I have created a script that runs on my system at home and downloads the database backup file every day.

If you just grab http://www.domainname.com:2222/CMD_DB/database_name.gz everyday, that will be your backup. the database_name (in database_name) is the name of your database as DirectAdmin knows it.

I use wget on Linuux to download this file. If you are using Linux, wget is probably already installed. If you are using windows, then you can download it (and get other information about it) from http://www.gnu.org/software/wget/wget.html

When you do this, it will require you to use the same login name and password as you use to get into your control panel. Since I use wget, it allows me to pass this information within my backup script, so that I don't have to do anything.

When I get up each morning, it has downloaded the database backup file (as well as a few other files that I wanted to backup).

HTH,

[thevillageinn]

Originally posted by skidawg@Dec 13 2004, 06:35 PM
Well, if you look at the that backup file name, you will see that it is a static name. Because of this I have created a script that runs on my system at home and downloads the database backup file every day.


Do your daily backups happen automatically or do you run a script to do that too? I don't have a daily backup, rather I only have the backup file I create by manually following the backup prompts in the DA panel.

[skidawg]

Since I run it on a Linux box, I just set up a cron job on my system at home that will execute a script. The script downloads several files, including the database backup file, everynight.

By doing this in a cron job, everything is completely automatic. In fact it usually runs at 3:30 in the morning, when I am asleep. Since I am running it in a cron job, the cron daemon will email any errors or other output from this script.

The script that I use is pretty simplistic and ugly, but it works for what I need. Basically, the scipt I use is this:


#!/bin/bash

# set current date
now=`date +%Y%m%d`

# create backup directory on local system
mkdir /var/www/html/backups/$now

cd /var/www/html/backups/$now/
wget http://www.domainname.com:2222/CMD_DB/database_name.gz &> /dev/null
wget http://www.domainname.com/otherfile.log &> /dev/null
wget http://www.domainname.com/otherfile2.dat &> /dev/null
wget http://www.domainname.com/prek/otherfile3.hits &> /dev/null

wget http://www.domainname2.com:2222/CMD_DB/database_name.gz &> /dev/null
wget http://www.domainname2.com/file.log &> /dev/null
wget http://www.domainname2.com/file2.hits &> /dev/null

echo "The daily backups are now done."


All the usernames and passwords that are needed are stored in a .netrc file in my home directory on the same Linux box where this script runs.

I have also expanded my script so it will do a weekly download of the entire site backup (created automatically in DirectAdmin), but that is a little more in depth for posting in this forum. If you are interested, I can try to make my script a little more generic and post it on my website for you to view.

HTH,

[thevillageinn]

I appreciate the info you've provided.

I am running one linux box here at home, and will be putting another online sometime after the new year, so your script (or ideas from it) will come in handy at that time.

However, what I still don't understand is whether you are downloading backup files which are created by DA automatically for you on a nightly basis, or whether you/your script are creating the backup files and then downloading them locally. It appears that your script is simply pulling a few different specific files, but I don't know anything about shell scripts.

[skidawg]

Basically what my script does is automatically download a couple of files like I have shown in the above post.

I have set up DirectAdmin to automatically do a complete backup of all sites under my resellers account on Saturday morning.

The script will then check to see if it is Saturday morning. If it is, it will use ftp to download all of the backup files. If it is not Saturday, it will not do this.

I will go through my script later tonight and mark it up with comments and then post it for you to look at/use if you wish.

[Joe]

Man, where were you when I needed a programmer?? (Like there's ever a time I DONT) :)

Nice script Doug!

[skidawg]

OK, for anyone interested, I have posted my backup script on my website. Feel free to use it, copy it, use it for inspiration, or just laugh at it. I have tried to comment everything that I am doing in the scipt to make it easier to modify/update/fix.

Check it out at http://www.dscweb.net/backup_sites.sh.txt

<standard disclaimer>
As with everything, make sure you understand what this script is doing before you try to use it, and then use it at your own risk. I am not responsible if you try to use this script and anything unexpected or unfortunate happens. :blink:
</standard disclaimer>

HTH,

[D9r]

Cool. Thanks for posting that.

Regarding your 'how to build a PVR' section, I was thinking to myself just the other day that one could do exactly that, and it wouldn't be that difficult either.

[skidawg]

Hopefuly that script will help someone out.

As for the PVR section, I startted researching that to build this summer, but I had a few unexpected expenses come up, so I haven't had the chance to buy the hardware yet. You could build one with much lower end hardware than I am/was looking at, so that would cut your hardware costs down quite a bit as well. I am wanting something a little beefier so that I can do some other things with it (like video editing, encoding, etc.).

I have a friend that built one and he said it was pretty simple.

In my opinion, the greatest reason to do this is the learning experience. If you just want something that will record your television shows, then go get a TiVo. If you are wanting to challenge yourself a little bit, and give yourself complete control over what it does, then build your own and utilize Open Source (http://www.opensource.org/) software (ie Linux, MythTV (http://www.mythtv.org/), and some others that I have listed (http://www.skidawg.org/pvr)) to do it, but that's just my opinion B)

[thevillageinn]

thanks for the script link. I'm definitely going to d/l it and use it for inspiration.

[skidawg]

Your Welcome Dan. :)

[mrshiney]

So if you don't have a reseller account, how can you generate a DB backup file on a daily basis? Got CRON?

[skidawg]

The database downloads are not just for resellers accounts, but for all accounts under DirectAdmin. You should be able to use the script (or part of it) above to download your own backups.

Specifically, check out posts #3, 4, and 5 above.

HTH,

[dchakrab]

I'm also interested in *how*, exactly, you set DirectAdmin to create the daily backup. This question's been asked above, but the answer seemed more targeted to getting your home machine to grab that backup regularly.

How do you make DirectAdmin create the daily dump in the first place?

Dave.

[skidawg]

Dave,

You don't have to set anything up. This is done automatically within DirectAdmin.

Just go into your Control Panel, click MySQL Management under Your Account. Then under the Download Backup column, click the Download link.

That is it. There is nothing that has to be set up by you in order to make this work.

HTH,

[mrshiney]

Originally posted by skidawg@Jan 11 2006, 03:24 PM
Dave,

You don't have to set anything up. This is done automatically within DirectAdmin.

Just go into your Control Panel, click MySQL Management under Your Account. Then under the Download Backup column, click the Download link.

That is it. There is nothing that has to be set up by you in order to make this work.

HTH,
Quoted post


Are you saying the daily backup happens automatically? I was the under the impression that you had to either make the backup from the DA tool or use a script in a cron job.

If anyone has such a script; post it...

[skidawg]

Originally posted by mrshiney@Jan 12 2006, 09:13 AM
Are you saying the daily backup happens automatically? I was the under the impression that you had to either make the backup from the DA tool or use a script in a cron job.

If anyone has such a script; post it...
Quoted post


That is exactly what I am saying. You have to do nothing to have the DA make the backup. The only thing you have to do is write a script (or do it manually) that will download the backup file when you want to.

Look at post #3 (http://www.hostpc.com/forums/index.php?showtopic=1700&view=findpost&p=8619) in this topic to see info about manually downloading this backup file.

I automitically download my backups onto my Linux box on a daily basis. For the script that I use to download this, check out my other posts in this thread, particularily posts 5 (http://www.hostpc.com/forums/index.php?showtopic=1700&view=findpost&p=8626), 7 (http://www.hostpc.com/forums/index.php?showtopic=1700&view=findpost&p=8631), 11 (http://www.hostpc.com/forums/index.php?showtopic=1700&view=findpost&p=8666) in this thread. Those talk about the script that I use to do these backups. Post 11 (http://www.hostpc.com/forums/index.php?showtopic=1700&view=findpost&p=8666) even has a link for the script that I use.

HTH,

[edmicman]

Sorry to dig this up from the past, but this thread sounds like it covers something that I want to do, but like the past few posts, I'm not seeing how to do it.

I want to create a weekly full DA backup, and then have that file downloaded to my linux server at home. I can download the file without a problem, but how to you make DA automatically create a complete backup on a schedule?? In DirectAdmin, under Site Backup, all I see is a way to manually create a backup. How do you schedule it? Thanks for any info!

[skidawg]

I just checked a non-reseller's account that I have (I am assuming that is what you have), and it doesn't look like you have the option. It looks like having DA create your backups via a cron job, is only available in reseller's account (and probably the administrator access).

You could possible look into creating your own script that tar's things up and upload that to run within your cron. Not sure what you would need, but I think that it should work for you. Not sure if that you get all the setting files that are included with a DA backup, but it would at least allow you to backup your site (except the database).

HTH,

[edmicman]

Ahhh, thanks for the info! It'd be nice if I could grab at least all the site files plus the databases and email, but I guess I'll have to work around setting something else up. Thanks again!

[skidawg]

I am not sure about the email, but I think you can do it.

Basically, what I would do would be to create a script that would tar up the following directories and make that file available for download.
/home/<username>/domains
/home/<username>/Maildir
/home/<username>/imap

The username would be the same as you use to log into your control panel.

Then upload that shell script and set up a cron job to execute it whenever you wanted it to run.

Before you do much on the server side, you might want to have someone at the helpdesk verify that you are only doing things that you are allowed to do (to make sure your script doesn't accidentally break something on the server). You should also verify that the server side paths that I gave are correct. I just guessed at what they would be.

Then set up a script on your system at home to automatically download this file, as well as your database backups. See other posts in this thread about how to do this, but specifically look at #3 (http://www.hostpc.com/community/showpost.php?p=7554&postcount=3), #5 (http://www.hostpc.com/community/showpost.php?p=7561&postcount=5), #7 (http://www.hostpc.com/community/showpost.php?p=7566&postcount=7), and #11 (http://www.hostpc.com/community/showpost.php?p=7601&postcount=11).

Of course, the description of the script to run home that I have given, relies on having a Linux/BSD/Mac box at home. If you only have windows at home, you can write a batch file that would do everything, but I have not done that before.

HTH,