Exporting a database with PHPMyAdmin

Last updated January 27, 2010. Created by johnvsc on September 2, 2006.
Edited by WorldFallz, add1sun, Max Bell. Log in to edit this page.

With regard to the instruction below: leave the checkboxes as they are if already selected or set if needed.

Using your web browser,

Go to the PHPMyAdmin location of the server where the database you wish to export resides. For example: http://www.example.com/phpmyadmin/ for a remote database and http://localhost/phpmyadmin/for a database hosted on your Apache2Triad installation. Select the name of the database you wish to export from the combo box on the left side of the page and you will be taken to that database's main operations page.
Click the tab labeled "Export" and you will be taken to the Export database page.
In the EXPORT group, select the check box:
- Select the check box marked "select all" to select all of the tables in the database for export.
- The radio button selected below should be "SQL".
Within the OPTIONS group; under the STRUCTURE sub group,

Select the check box next to "Structure"
Select the check box labeled "add DROP TABLE"*
Select the check box labeled "Add AUTO_INCREMENT value"
Select the check box labeled "Enclose table and field names with backquotes"

Within the OPTIONS group, under the DATA sub group:

Select the check box marked "Complete inserts".
Select the check box labeled "Use hexadecimal for binary fields" may be selected
Select "INSERT" in the combo box labeled "Export type:"

Below, select the check box labeled "Save as file" group,

then type a name for the database and

click the button labeled "Go".

Select a location to save your database to (preferably something easy to remember, like the database folder of your Drupal installation in htdocs, for example).

***IMPORTANT***
Selecting "add DROP TABLE" means that if the copy of the database created is later imported to another Drupal installation, the tables in the imported copy will overwrite them if they exist in the target database. You should be careful to keep track of your databases, and make sure you only import and export the correct databases for your task!

Note if a radio button other than "None" is enabled in the group "compression". Uncompressed databases may be larger, but are sometimes easier to work with.

This document details that items that should be selected. If it is not in this document above, then it *should/can* be omitted.

Finally, it cannot be stressed enough how important it is to make a backup of both your server files and you database! DO NOT DELETE a database or files off a server unless you are 200% sure that everything works!

For our visual thinkers, here is a screenshot of the above :) http://www.johnvsc.com/files/phpMyAdmin-screenshot.jpg

Comments

empty cache tables

Posted by bill1821 on February 25, 2010 at 6:12am

I want to recommend that it is good to empty the cache tables (especially if you use hosting plans like godaddy.com which have big constraints in uploaded sql files (about 2MB) and there is not zip possibility). The size of the database will decline to about 1/3 of the initial size.

How to select Database fields to Export from MySql ?

Posted by wwwoliondorcom on October 25, 2010 at 8:02am

How to select Database fields to Export from MySql ? Need only usernames and Emails

Hi,

Trying to export database users list I can't select only the USERNAME and USER EMAIL, so can you tell me how to do ?

Thanks a lot.

Backing up MySQL database

Posted by phanophite on July 4, 2011 at 2:13pm

I wrote an article about backing up a MySQL database on my blog. You can view it here:

http://opensourceexperiment.blogspot.com/2011/07/backing-up-mysql-databa...

Settings for phpMyAdmin 3.4.3.2

Posted by merlin2288 on November 4, 2011 at 3:53am

My webhost recently updated the version of phpMyAdmin to 3.4.3.2 and it's quite different visually. Some of the choices are there, but it appears that some of the options are missing.

I've uploaded a screenshot of how I have things setup for database backups, but hoping someone who understands this a little better can take a look.

http://byebyesuckas.com/documents/phpmyadmin_settings_v2.jpg

Thanks in advance for your help.

Dane
merlin2288@gmail.com

Settings I'm Using

Posted by merlin2288 on November 27, 2011 at 3:04pm

I've spent some time trying to figure this issue out on my own since I posted this question a few weeks back. Here is what I've come up with:

http://byebyesuckas.com/documents/phpmyadmin_settings_v2.jpg

I've tested the settings and I'm 99.9% sure I've got things nailed down.

When I say I've "tested" them what I mean is this:
- I exported a copy of the database I used for my main site using the settings I've linked to.
- I took the exported copy of the database and imported into my test installation.
- I then reviewed my test site with the new database content and everything appears to be working as planned.

Hopefully the testing process I used doesn't have holes in it, but if there are questions you have or other ideas for testing please share.