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:
- 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.
- 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.
- Usernames and Password
You will need the username and password to your Web
server AND the username and password to your MySQL database.
- 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:
- Launch PuTTY. Once the program is launched, ensure
that the box for SSH is selected:
[ ensure SSH
is selected ]
- 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 kirupa.com
servers:
[ enter your
host name ]
- 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.
- 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 ]
- 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 |
 |
|
