How to Export a MySQL Database

If you are moving your Web site to a new server or just backing up your MySQL database, you will need to export a MySQL database. To do this you can either use the phpMyAdmin interface or the SSH command line. This guide will give you all you need to know to get you through either.

Instructions

Export Using PhpMyAdmin

1. Log into the phpMyAdmin interface on your Web space. You will need the username and password assigned by your Web-hosting provider.

2. Select the database you wish to export the data from in the drop-down database selection box on the left.

3. Click the Export tab toward the top of the interface. This will take you to a rather daunting Export screen, with a lot of options.

4. Click "Select All." This makes sure all the tables in your database are included in the exported file.

5. Select "SQL" in the Export box and make sure "Structure" is selected, then make sure the "Add Auto_Increment" and "Enclose Table and Field Name With Backquotes" boxes are ticked.

6. Make sure "Data" is selected, check "Hexadecimal for Binary Field" and set the Export type to "Insert."

7. Select "Save as File," then click "Go." You will be prompted to save the database to your computer.

Export Using SSH

1. Log into your Web space using an appropriate SSH client. You will need the username and password assigned to you by your Web-hosting provider.

2. Enter the following into the command line, using your own database name, file name, username and password instead of the words in capitals:
   mysql -uUSERNAME -pPASSWORD DATABASENAME > MYDATABASE.sql

   (For example, if my username was "silly," my password "computer," my database name "MyDB" and I wanted my back up to be called "MyDBBack.sql," I would type:
   mysql -usilly -pcomputer MyDB > MyDBBack.sql)

3. Close your SSH program, then log into your Web space using an FTP client.

4. Find the .sql file you created on your Web space and download it to one of your own physical drives.

5. Close your FTP program. You now have a file with the contents of your database.

Tips & Warnings

• Use the phpMyAdmin GUI unless you absolutely must use the SSH command line. It is much simpler.

• Back up your databases regularly.

• Be careful when probing around your Web space using the SSH command line; it's a powerful tool and you can cause yourself a lot of problems if you aren't careful.

• Don't play around too much in the Export section of phpMyAdmin. You can choose to compress the file if you wish, or change the filename, but changing most of the other options will change which data is exported, or how.