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

Using your web browser,

  1. 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.
  2. Click the tab labeled "Export" and you will be taken to the Export database page.
  3. 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".
  4. 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!

    And the following is an example of why we frown on externally hosted images in documentation pages:

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

    Comments

    bill1821’s picture

    bill1821 commented

    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.

    wwwoliondorcom’s picture

    wwwoliondorcom commented

    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.

    dustinschaeffer’s picture

    dustinschaeffer commented

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

    merlin2288’s picture

    merlin2288 commented

    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

    merlin2288’s picture

    merlin2288 commented

    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.

    tommyallen789’s picture

    tommyallen789 commented

    Hey, this image was great, with the new phpmyadmin settings. Now the link does not work.
    I just found this alternative image/link, can someone confirm it is correct/accurate?

    http://stackoverflow.com/questions/10890112/exporting-migrating-out-a-dr...
    Direct link to image used: http://i.stack.imgur.com/0sf7X.png

    Just installed the "Backup & Migrate" module, which hopefully does the job, but thought occasionally I'd still do a backup directly from phpMyAdmin just to be sure.. think that is smart or redundant?

    David.Maung’s picture

    David.Maung commented

    The URL provided for a screenshot of the settings comes up forbidden.

    iwuv’s picture

    iwuv commented

    Visual Image (super tall GIF)

    This is my process to export a compressed DB. It has never failed an import.

    Keep everything else as is, and choose...

    Export Method: Custom
    Output: Compression: [gzipped]
    Format-specific options: Object creation options: Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement

    That's it. Hit the Go button and export.

    Note: I did a Quick Export one time to save a few clicks. When my DB bombed from a bugged module, the import of my backup failed because I did not have Add DROP TABLE checked on export. Don't make this mistake!

    If this method works for others, let's get this added to the documentation.

    kurtz’s picture

    kurtz commented

    I can confirm the export method outlined by cmz. My host is at phpMyAdmin 3.5.4. The import initially failed when I took the "Quick" export option. I had to use the "Custom" export as indicated above.

    iwuv’s picture

    iwuv commented

    Rock on! Anyone else have positive results with this method? Seems rather... essential? To have this info accurate and accessible.

    myAppyness’s picture

    myAppyness commented

    I installed the community edition of mySQL Workbench, the magic ingredient is the import and export feature. It helped me transfer my DB without any hussle. Do remember to enable remote access to your mySQL DB.

    zJoriz’s picture

    zJoriz commented

    Do the same settings apply to D8?

    I've sucessfully recovered sites in the past using Backup And Migrate, but my tries using PHPMyAdmin-backups have never been successful and have cost me more days than I care to remember (and probably years of my lifespan). The last time I went for a last resort and built the entire site from scratch again.
    That's why I know backups are important -- I'd like to do it right this time ; )