Data Management

Back up your MySQL databases with this web-based tool

If you're looking for a simple way to back up and restore your MySQL databases, MySQLDumper is the tool for you. Here's how to install and use MySQLDumper.

Once databases are up and running and connected to whatever application uses them all is well, but what happens when disaster strikes? If you haven't backed up those databases, a nightmare situation will ensue. To that end, you must have a solid backup plan in place. One tool that makes backing up MySQL databases a snap is MySQLDumper.

MySQLDumper is a web-based, PHP- and Perl-based tool. Databases can be dumped into a backup file and then easily restored. MySQLDumper is perfectly suited for shared hosting workspaces where shell access isn't an option. MySQLDumper is released under the GNU Open Source License and is free to use. Let's walk through the process of installing and using this handy tool.

Installing MySQLDumper

Requirements

The installation requirements are Apache, MySQL, and PHP. A basic Linux Apache MySQL PHP (LAMP) server should easily meet all of these requirements. Once you meet the requirements, follow the steps for installation, which will be a breeze if you've installed tools like phpMyAdmin.

Step 1: Download and extract the MySQLDumper file

Download the latest release and move the file into your server's document root . I am installing on a Ubuntu 12.04 machine, so the file will be placed in /var/www. With the file in its proper place, extract it with the following steps:

  1. Open a terminal window.
  2. Issue the command sudo unzip MySQLDumperXXX.zip (XXX is the release number).
  3. Rename the newly created file with the command sudo mv msdXXX msd (XXX is the release number).

Step 2: Create permissions and directories

There are certain sub-folders that have to be created and permissions re-assigned. Here are the commands to complete this step from within the /var/www/msd directory:

  • sudo mkdir work
  • sudo chmod ugo+w work
  • sudo mkdir work/config
  • sudo chmod ugo+w work/config
  • sudo mkdir work/log
  • sudo chmod ugo+w work/log
  • sudo mkdir work/backup
  • sudo chmod ugo+w work/backup/
  • sudo chmod ugo+w config.php
Step 3: Create a database

Yes, a tool that is used to back up databases requires a database. To make this step easy, I prefer to take advantage of the MySQL Workbench tool. Create a database called "msd" using the UTF default collation.

Step 4: Install MySQLDumper It's time to fire up your browser and point it to http://ADDRESS_OF_SERVER/msd and start the installation. The first screen will require you to select the language for the installation. Then, click the Install button. The next screen you should see is the database information screen (Figure A). Figure A

At the bottom of the screen, you can see MySQLDumper detected the databases on my machine. (Click the image to enlarge.)
Fill out the necessary information and click the Connect To MySQL button. If the connection is successful, you will be taken to one of two screens. If you have already taken care of the directories and permissions issues, you'll immediately be taken to the MySQLDumper main window (Figure B). If you haven't taken care of those issues, you will land on a page instructing you on how to complete the installation by creating the necessary directories and permissions. Figure B

There is still one installation step left. (Click the image to enlarge.)
From the main window, you will notice a warning labled Urgently Recommended. You need to protect the MySQLDumper directory with a password. Do this by clicking the Create Directory Protection button. This will open a new screen (Figure C) that allows you to set up the password protection. Figure C

You have different encryption options to choose from here. (Click the image to enlarge.)

Using MySQLDumper

Here's how to back up a database using MySQLDumper:

  1. Select the database to be backed up from the Database drop-down.
  2. Click Backup from the left navigation.
  3. Enter a comment (if necessary) in the comment field (Figure D).
  4. Click Start New Backup.

Figure D

If you check the box for Select Tables, you will be prompted to select which tables you'd like to back up. (Click the image to enlarge.)
When the backup completes, you will be presented with a link that allows you to download the database (Figure E). Figure E

You can also opt to edit the database or get an overview of the database. (Click the image to enlarge.)
Click the Continue button to move to the database overview screen. This screen (Figure F) displays all of the current backups available on the MySQLDumper system. Figure F

From this screen you can upload a database to the system. (Click the image to enlarge.)

Restoring is just as simple:

  1. Select the database you want to restore from the Database drop-down in the left navigation.
  2. Click Restore from the left navigation.
  3. Select the backup you wish to restore (Figure G).
  4. Click the Restore button.

Figure G

You can select specific tables to restore if you don't need to restore the entire database. (Click the image to enlarge.)

By regularly employing MySQLDumper, you will have a solid set of backups to ensure your databases are always functional.

About

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website getjackd.net.

3 comments
jlwallen
jlwallen

If you're hoping MySQL Dumper will work in Windows, you're in luck. The requirements are as such: Windows Server working Perl + PHP IIS 6 I hope that helps! Jack

compdave7681
compdave7681

Don't remember where I got this from but credit goes to where it's deserved, this dumps the databases into a directory structure like so: /MY_Database_Backup/YYYYMMDD/database1.gz It will also automatically delete the old ones. #!/bin/bash # # Modify the variables below to your need # Mysql Credentials MyUSER="mysql_username" MyPASS="mysql_password" MyHOST="myhost" # Owner of mysql backup dir OWNER="owner_of_backup_dir" # Group of mysql backup dir GROUP="group_of_backup_dir" # Which databases to backup DBS="list specific databases with spaces" # Or get all databases #DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')" # DO NOT BACKUP these databases IGGY="" # Backup Dest directory, change this if you have someother location DEST="/my/backup/dir/" # mysqldump parameters # YOU MAY HAVE TO CHANGE THIS PARAMATER # This worked for me DUMP_OPTS="--max_allowed_packet=1G -Q --single-transaction" # Send Result EMail # SEND_EMAIL = 1=yes 0=no SEND_EMAIL=1 NOTIFY_EMAIL="my_email@domain.com" NOTIFY_SUBJECT="MySQL Backup Notification" # Delete old backups # DELETE_OLD_BACKUPS= 1=yes 0=no DELETE_OLD_BACKUPS=1 DELETE_BACKUPS_OLDER_THAN_DAYS=7 # Usually there is no need to modify the variables below # Linux bin paths, change this if it can't be autodetected via which command MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" GREP="$(which grep)" CHOWN="$(which chown)" CHMOD="$(which chmod)" GZIP="$(which gzip)" MAIL="$(which mail)" FIND="$(which find)" DF="$(which df)" # Get hostname HOST="$(hostname)" # Get data in yyyy-mm-dd format NOW="$(date +"%Y%m%d")" # Function for generating Email function gen_email { DO_SEND=$1 TMP_FILE=$2 NEW_LINE=$3 LINE=$4 if [ $DO_SEND -eq 1 ]; then if [ $NEW_LINE -eq 1 ]; then echo "$LINE" >> $TMP_FILE else echo -n "$LINE" >> $TMP_FILE fi fi } # Main directory where backup will be stored if [ ! -d $DEST ]; then mkdir -p $DEST # Only $OWNER.$GROUP can access it! $CHOWN $OWNER:$GROUP -R $DEST $CHMOD 0750 $DEST fi # Create backup directory MBD="$DEST/$NOW" if [ ! -d "$MBD" ]; then mkdir "$MBD" # Only $OWNER.$GROUP can access it! $CHOWN $OWNER:$GROUP -R $MBD $CHMOD 0750 $MBD fi # Temp Message file TMP_MSG_FILE="/tmp/$RANDOM.msg" if [ $SEND_EMAIL -eq 1 -a -f "$TMP_MSG_FILE" ]; then rm -f "$TMP_MSG_FILE" fi set -o pipefail # Start backing up databases for db in $DBS do skipdb=-1 if [ "$IGGY" != "" ]; then for i in $IGGY do [ "$db" == "$i" ] && skipdb=1 || : done fi if [ "$skipdb" == "-1" ] ; then FILE="$MBD/$db.$HOST.$NOW" # do all inone job in pipe, # connect to mysql using mysqldump for select mysql database # and pipe it out to gz file in backup dir $MYSQLDUMP $DUMP_OPTS -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > "$FILE.gz" ERR=$? if [ $ERR != 0 ]; then NOTIFY_MESSAGE="Error: $ERR, while backing up database: $db" else NOTIFY_MESSAGE="Successfully backed up database: $db" fi gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE" echo $NOTIFY_MESSAGE fi done # Empty line in email and stdout gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "" echo "" # Delete old backups if [ $DELETE_OLD_BACKUPS -eq 1 ]; then find "$DEST" -maxdepth 1 -mtime +$DELETE_BACKUPS_OLDER_THAN_DAYS -type d | $GREP -v "^$DEST$" | while read DIR; do gen_email $SEND_EMAIL $TMP_MSG_FILE 0 "Deleting: $DIR: " echo -n "Deleting: $DIR: " rm -rf "$DIR" ERR=$? if [ $ERR != 0 ]; then NOTIFY_MESSAGE="ERROR" else NOTIFY_MESSAGE="OK" fi gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE" echo "$NOTIFY_MESSAGE" done fi # Empty line in email and stdout gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "" echo "" # Add disk space stats of backup filesystem if [ $SEND_EMAIL -eq 1 ]; then $DF -h "$DEST" >> "$TMP_MSG_FILE" fi $DF -h "$DEST" # Sending notification email if [ $SEND_EMAIL -eq 1 ]; then $MAIL -s "$NOTIFY_SUBJECT" "$NOTIFY_EMAIL" < "$TMP_MSG_FILE" rm -f "$TMP_MSG_FILE" fi

henry
henry

Will this work with Windows Jack..??? Sorry I am not a Linux all the time guy but have loaded each verison and kept up with your renditions on same. I would love an introduction to the Sudo things etc that seem to fly off your machines. Henry New Zealand

Editor's Picks