MicrostockGroup Sponsors


Author Topic: How to Backup Your MySQL Database  (Read 4852 times)

0 Members and 1 Guest are viewing this topic.

ShazamImages

  • ShazamImages.com
« on: September 04, 2013, 09:01 »
+1
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):

Code: [Select]
#!/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.
« Last Edit: September 05, 2013, 06:31 by ShazamImages »


steheap

  • Author of best selling "Get Started in Stock"

« Reply #1 on: September 04, 2013, 09:09 »
0
I'm sure this works, but why not use one of the WP plugins that does exactly this.

One one of my sites I use Updraft Plus that backs up to a Google drive automatically every day, and on the other I use Wordpress Database backup that emails me a copy of the database once a week.

Steve

ShazamImages

  • ShazamImages.com
« Reply #2 on: September 04, 2013, 09:21 »
0
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):

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.

« Reply #3 on: September 04, 2013, 09:40 »
0

« Reply #4 on: September 04, 2013, 09:43 »
0

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  :-X :-X :-X

« Reply #5 on: September 04, 2013, 11:01 »
0
Is exporting a database to the hard drive the same as backing it up?

I'll look more into the code you provided.  When you say type into the prompt are you talking about a DOS prompt or some prompt somewhere else?

Thanks!

« Reply #6 on: September 04, 2013, 11:46 »
0
Is exporting a database to the hard drive the same as backing it up?


Yes.

« Reply #7 on: September 04, 2013, 11:51 »
0
Is exporting a database to the hard drive the same as backing it up?


Yes.

Okay good cause that's what I've been doing.  I just need to start scheduling a time for me to start doing that at probably late at night would be best.

ShazamImages

  • ShazamImages.com
« Reply #8 on: September 04, 2013, 12:03 »
0
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.


« Reply #9 on: September 04, 2013, 12:05 »
0
I'm not exactly sure which one mine is.  It's either Linux or Windows.  I'll have to look into it.

« Reply #10 on: September 05, 2013, 08:44 »
0
Quote
#!/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?

« Reply #11 on: September 05, 2013, 09:11 »
0
It just creates a database dump (backup) in the folder where the script is being executed. It doesn't copy any files.

« Reply #12 on: September 05, 2013, 13:58 »
0
Something like Lightroom backup, so if the database becomes corrupt, you can restore it from the dump?

ShazamImages

  • ShazamImages.com
« Reply #13 on: September 05, 2013, 15:14 »
0
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?

It creates a database dump in a directory of your choice on your web host.

It just creates a database dump (backup) in the folder where the script is being executed.

It actually puts the dump in a directory of your choice (specified by DUMPPATH).

stocked

« Reply #14 on: September 05, 2013, 15:56 »
0
cool thank you :)

« Reply #15 on: September 06, 2013, 00:32 »
0
Is this dump compressed (taking less space than the original database)?
If so, it might be preferrable to create the dump first, and then backup it up onto another server or local computer.
 

« Reply #16 on: September 06, 2013, 01:10 »
0
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.


 

Related Topics

  Subject / Started by Replies Last post
2 Replies
4781 Views
Last post July 25, 2006, 19:14
by nscotta
3 Replies
3600 Views
Last post April 05, 2010, 15:27
by johngriffin
2 Replies
3236 Views
Last post May 18, 2010, 17:53
by johngriffin
3 Replies
4744 Views
Last post March 05, 2014, 11:53
by DonLand
1 Replies
3354 Views
Last post September 15, 2015, 20:46
by Danybot

Sponsors

Mega Bundle of 5,900+ Professional Lightroom Presets

Microstock Poll Results

Sponsors