Backing Up/Restoring
MySQL Databases
       by kirupa  ::  17 March 2004

If you happen to have data stored in a MySQL database, it is good for you to learn how to backup and restore the database. While MySQL is pretty reliable, random human/comp errors, corrupted data, unwanted intruders, etc. could cause irreparable damage to the data contained in your database. It's better for you to be prepared, and this tutorial will provide you with the information you need to protect your data!

There are two ways of backing up your data. You can use a program such as phpMyAdmin and use a graphical web-based interface to back up your database, or you can use a slightly more ancient command-line prompt. I will explain how to use the command line prompt method because the phpMyAdmin method times out when backing up large databases. Also, while the command line method is a bit more complicated, it does, in my view, provide you with greater control over your data.

What you Need
I will be explaining how to backup and restore your database using SSH, so you will need the following:

  1. SSH Access
    Make sure you have SSH access to your MySQL database. If you are not the server administrator, you should contact your web host to enable that feature for you.
  2. Database Name
    You will need the name of your database. If you are not sure what your database's name is, you can quickly find out using PHPMyAdmin.
  3. Usernames and Password
    You will need the username and password to your Web server AND the username and password to your MySQL database.
  4. SSH Client (PuTTY!)
    You will need a program that acts as an intermediary between you and your server. While there are a host of programs you can use, I will be using the popular program PuTTY. It's a free download, so grab it from the following link:

Download PuTTY

If you are new to SSH, I recommend you download PuTTY from the above link because my screenshots and instructions will refer to PuTTY.

Getting In
Now that you are setup, let's get started:

  1. Launch PuTTY. Once the program is launched, ensure that the box for SSH is selected:

[ ensure SSH is selected ]

  1. In the field for Host Name, enter the URL (host name) or IP address of your site. You don't have to include the www or http:// part of your site's domain name.
    For example, here is the host name for the servers:

[ enter your host name ]

  1. Press the Open button towards the bottom of the same window. If everything worked, you should be prompted with the text "login as:" Enter your main administrator/site username here. Once you have that entered press Enter.
  2. After you enter you have entered your username and pressed Enter, you will be prompted for your site's password. Enter your password and press Enter.
    If everything worked well and all of your data was correct, you will find yourself at the command prompt where you can input further commands. You should see something similar to the following screenshot:

[ your PuTTY screen now ]

  1. If you don't see something similar to the above screen, ensure that you entered your information correctly. If you are certain you made no mistakes, you should contact your host to ensure that your account has the required privileges to use SSH.

For the most part, you will be logged into the root location of your server. You can use an FTP program such as SmartFTP to find out exactly where the root folder and other related folders are located on your server.

In the next page, I will provide the commands for backing up and restoring your database, and I will also provide some extra commands that you may find useful.


page 1 of 2


SUPPORTERS:'s fast and reliable hosting provided by Media Temple.