phpMyAdmin & MySQL, pt. 2

Part 1 covered configuring and installing (uploading) phpMyAdmin. Part 2 will cover exporting (backup) data from the MySQL database.

This, by far, is what I think is the most important function. A good backup is what stands between you and total data loss. With the backup function in phpMyAdmin, you can backup individual tables or the entire database. Web hosting providers perform their own backups, but I like to make my own.

To perform an export/backup:

On the left side of the page, click the name of your database.
- If you have multiple databases being managed, you will have a drop down menu to select which database you want to manage.
On the right side of the page, click the tab labeled Export.
Choose the table(s) you wish to backup from the list under Export.
- If you want to backup the entire database, click Select All.
- If you want to backup certain tables, click on the table name. To choose multiple tables, hold the Ctrl key while clicking the table name(s).
Choose what format you want the exported file to be in. You can choose:
- SQL: The dumped file will have a .sql extension and can be imported into almost any standard SQL database. This will be the format to use the majority of the time. It can easily be imported using phpMyAdmin or editted with just about any text editor (not Notepad, very garbled).
- LaTeX: This one I’m not very familiar with but I’m open to explanations. Google fails to give me any good answers on what the format really is about.
- Microsoft Excel 2000: Why? I guess if you really want it in a format that can be relatively easy to pull into an MS Access database, this would be the choice to go with.
- Microsoft Word 2000: WHY? I really don’t know why you would want this format. Honestly.
- CSV for Excel: I guess you could use this for the same reasons as you would the Excel 2000 format.
- CSV
- XML
Other choices you may want for a basic backup:
- Structure
  - Add DROP TABLE : During an import, this will delete any table that may currently exist in the database that matches a table name in the backup file.
  - Add IF NOT EXISTS: Definetly check this one. If you don’t, and you import the file but a table name isn’t in the database that is in the backup, your data won’t get restored. This forces the table to be created if one doesn’t exist.
  - Add AUTO_INCREMENT value: Yes and no. If your data has an auto_increment number, like WordPress’ post ID in the wp_post table, you can check this one if you want that number to stay with the data assigned to it. I’d say go ahead and do it if you are mainly going to be importing into an empty database. Beware that it can cause conflicts if you import a table with auto_increment numbers into a table that may already have matching numbers (you can’t have duplicates in the auto_increment column). This is not going to kill your backup if you don’t check this one.
  - Enclose table and field names with backquotes: Check this one. It doesn’t really cause any overhead but it is essential if you have special characters in the table title or field names.
  - SQL export compatibility: If you only plan on importing this back into the MySQL database you are backing up from, leave it set to None. Otherwise, choose what type of database server you plan on importing this into (i.e. MS SQL, Oracle, PostgreSQL, etc.).
- Data (choose one, not both)
  - Complete inserts: This will include the column name for every INSERT, which can make the file easy to read if you manually edit it.
  - Extended inserts: This is the one I’d choose because the INSERT variable and the table name are only used once so the backup file is smaller.
Place a check mark next to Save as file.
- If you don’t do this, the text of the dump (export/backup) will be displayed in the browser and you will have to copy/paste it into a text file.
Leave the File name template set to __DB__ and the saved file will take on the name of the database.
Change the compression to either zipped or gzipped. If the server supports it, bzipped will be a choice.
- Why use compression? If you have a large database, the dump file can be quite large. If it is over the 8MB limit, you will not be able to import it via phpMyAdmin unless you do some editting of the file. This just cuts out that step and increases the chances that you will be able to import the file without editting in the future. The import function supports compressed SQL files.
Click Go and save the file to your local machine.

Yes, I know there are a few more options available for the export process. Those options are not necessary for a basic, yet still fully functional, backup of a database.

If you do just want the structure of your database backed up, but not the data, remove the check mark next to Data but leave it next to Structure (step 5). Also, if you just want the data but not the structure, remove the check mark next to Structure and leave it next to Data.

Part 3 will cover importing your backup or any other SQL script.

30.Jul.05 Tech Tip, phpMyAdmin

You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

2 Responses to “phpMyAdmin & MySQL, pt. 2”

geo | November 10th, 2005 at 11:34 pm
how can i import the file csv in excel format using phpyadmin?
wyckedone | November 13th, 2005 at 9:17 pm
What version of phpMyAdmin are you using? Import has changed in the 2.6.4 branch, especially for text file imports.