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.

Backing Up/Restoring MySQL Databases

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 kirupa.com 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.

The following commands will need to be typed into your command line. There are numerous ways to arrange your commands, and if you refer to other sites, you will find the commands to be a little different than what I present. Don't worry - the examples on this site work, and I'm sure an alternate method will also work.

Backing up Database
The command for backing up your database is the following:

  mysqldump -u username -p database_name > /path/backup.sql

If you want to backup your database, type the above command in your command line with the above text in blue modified to suit your username, database name, and path database should be saved to.

After you have typed the above command, press Enter. Your database won't be backed up yet. You will first be prompted for your password. The password you will need to input is the password for your MySQL database. After you input your password and press Enter, your database will be successfully backed up to the location you specified.

For example, if I have the username blarg, database name halo, and am interested in saving the database into the same, root location I am currently in, here is how my command would look:

  mysqldump -u blarg -p halo > backup.sql

After doing the above, the backup.sql file in my example will be located in the root location on my server:

Restoring your Database
Now that you have your database backed up, you may find yourself needing to restore your database! The command for restoring your database is the following:

  mysql -u username -pPassword database_name < path/db.sql

Note the lack of a space between -p and Password. Also, the password is for the MySQL database. For example, using the previous values for username, database name, and path, my version of the command will look like the following:

  mysql -u blarg -pPassword halo < backup.sql

In my example, the database halo will now receive all the data from the file backup.sql.

If you are interested in restoring a compressed database, you will have to uncompress the file first. The following command combines both the steps of uncompressing your file and transferring its contents to a database:

  gunzip < backup.sql.gz | mysql -u blarg -pPassword db_name

I talk about compression in the following section.

Compression
When you backup a large database, you will find that the .sql file is fairly large. If you archive your databases for future use, you may appreciate the built-in compression features of MySQL.

There are several compression methods you can use, but I will be explaining how to use the GZ compression format. In my backup of the kirupaForum database, I was able reduce my file size by about 50% by compressing the data to the GZ compression.

If you want to compress your existing .sql file, you will need to use the following command:

  gzip -X path/to/backup.sql

In the above command, X is a number between 1 and 9 that specifies the level of compression used. The higher your number, the more compressed (smaller) your file will be.

For example, this is the command I use to compress my data:

  gzip -9 forum_backup.sql

If you want to directly compress data from a mySQL database without having to gzip separately like you did above, you would employ the following command: command:

  mysqldump -u username -p db_name | gzip > backup.sql.gz

After you type the above and press enter, you should be prompted for your password. Enter your password, and you should be all set.

Likewise, to uncompress a compressed file, you will use the following command:

  gunzip backup.sql.gz

After you uncompress your file, you will see a larger backup.sql file that can then be imported into your database using the methods I outlined towards the top of this page.

	Extra Information
bzip compression - by njs12345 There is another method of compression commonly found on Unix/Linux systems that can be more effective than gzip called bzip2. For example, linux 2.6.8.1 is 43637kb in it's gzip form, but it is 34794kb in it's bzip form.. In general, it's usage is similar to gzip, but instead of typing gzip -9, you would type:   bzip2 -z - njs12345

I hope the information helped.