Since there seem to be a lot of questions about backups, I thought that I would provide a short UNIX shell script for creating a backup of your MySQL database.

Here is the script (please note that you will have to modify the areas marked with <> to match your configuration - most of the information can be found in your wp-admin.php file):

#!/bin/bash

# NOTE: Need to make this file executable (chmod 740 dbdump.sh) in UNIX

HOST="<your web hostname>"
DB="<your MySQL database name>"
USR="your MySQL db admin user name"
PWD="your MySQL db admin password"
# RETAINDAYS is how many days you want to keep the backups
RETAINDAYS="4"

DUMPPATH="<the directory name where you want to put the backups>"
DBFILE="$DUMPPATH/$DB-`date +%Y-%m-%d-%H-%M`.sql"
HOPT="--host=$HOST"
if [ "$HOST" = "" ]; then
  HOPT=""
fi
mysqldump $HOPT --user=$USR --password=$PWD --single-transaction $DB > $DBFILE
echo "$DBFILE dumped"

# The following line removes DB dump files that are older than RETAINDAYS
find $DUMPPATH -name "*.sql" -mtime +$RETAINDAYS -execdir rm {} + ;

Place the script above inside a file called dbdump.sh (or whatever else you want to call it, just make sure it ends in .sh) and upload it to your host.  I would recommend creating a directory under your web root (mine is called html, but I've seen it called public_html on other hosts).

You will need to change the permissions of the dbdump.sh file (from an application such as PuTTY) so that it can be executed using the following command at the prompt:
   chmod 740 dbdump.sh (if you used a different file name then that will need to be used instead)

You can also change the file permissions if you use FileZilla by right-clicking on the file and choosing "File Permissions..." and then enter 740 in the text box at the bottom.

You can now manually execute the file (from an application such as PuTTY) by typing the following command at the prompt:
  bash dbdump.sh

If you want, you can also schedule the database dump on a regular basis via the cron (a UNIX job scheduler) that you should have available from your host.

Tip: Try to run all of your backups at the same time, so that there is consistency between all of the data.

I'm sure this works, but why not use one of the WP plugins that does exactly this.

A few reasons:

- At the time that I wrote this, I was unaware of any plugins that did this.
- Plugins can affect your site negatively.  There are many threads where people discuss that their site is broken only to find that disabling a plugin will fix the problem.  I'm sure that the plugins you mention won't cause a problem, but you never know.
- Plugins can create security holes for hackers.
- I didn't have to spend any time researching the various plugins to find their pros and cons.  It literally took me about 5-10 minutes to write this and test it.
- Since I wrote the code, I can modify it to do anything that I want.
- But mainly it is because I'm a Type A personality!

FYI: I went to checkout UpdraftPlus and thought that the following was kind of funny (as long as it doesn't happen to you):

Q: My site was hacked, and I have no backups! I thought UpdraftPlus was working! Can I kill you?
A: No, there's no warranty or guarantee, etc. It's completely up to you to verify that UpdraftPlus is creating your backups correctly. If it doesn't then that's unfortunate, but this is a free plugin.

FYI: I went to checkout UpdraftPlus and thought that the following was kind of funny (as long as it doesn't happen to you):

Quote

Q: My site was hacked, and I have no backups! I thought UpdraftPlus was working! Can I kill you?
A: No, there's no warranty or guarantee, etc. It's completely up to you to verify that UpdraftPlus is creating your backups correctly. If it doesn't then that's unfortunate, but this is a free plugin.

That is pretty much how I felt towards Justhost when I found that the contents of the symbiostock_rf file missing and that they did not have a back up of it and their attitude.   You always assume your back up is working until you need it then find it isn't 

Is exporting a database to the hard drive the same as backing it up?

Yes.

When you say type into the prompt are you talking about a DOS prompt or some prompt somewhere else?

The script that I wrote is for UNIX, so it would be at the UNIX prompt.

A similar script could be written for Windows, but my host is UNIX based.

#!/bin/bash

# NOTE: Need to make this file executable (chmod 740 dbdump.sh) in UNIX

HOST="<your web hostname>"
DB="<your MySQL database name>"
USR="your MySQL db admin user name"
PWD="your MySQL db admin password"
# RETAINDAYS is how many days you want to keep the backups
RETAINDAYS="4"

DUMPPATH="<the directory name where you want to put the backups>"
DBFILE="$DUMPPATH/$DB-`date +%Y-%m-%d-%H-%M`.sql"
HOPT="--host=$HOST"
if [ "$HOST" = "" ]; then
  HOPT=""
fi
mysqldump $HOPT --user=$USR --password=$PWD --single-transaction $DB > $DBFILE
echo "$DBFILE dumped"

# The following line removes DB dump files that are older than RETAINDAYS
find $DUMPPATH -name "*.sql" -mtime +$RETAINDAYS -execdir rm {} + ;

Thank  you proving the code, Shazam
Does this procedure copy files to another directory on the server, or to your local computer? Or can you send it also to some cloud drive?

Something like Lightroom backup, so if the database becomes corrupt, you can restore it from the dump?

cool thank you

For those looking for an easy approach to a db backup, we have been using the WP-DBManager plugin on several WP sites for a while. It backs up onto the server, and also emails us a gzipped copy, every 3 days.

For a couple of other MySQL db's (non-WP) we run a simple shell script on a weekly cron schedule, which also emails the backups.