SMBs

Import and export databases using phpMyAdmin

phpMyAdmin offers one of the easiest ways to import and export databases on a MySQL environment. Follow along with Jack Wallen's phpMyAdmin tutorial.

For anyone who works with MySQL, phpMyAdmin is a must-have tool. phpMyAdmin makes the task of managing your MySQL databases easier and more efficient, and it allows you to manage those available databases from anywhere. With phpMyAdmin, you can create, edit, back up, import, export, and delete databases, as well as manage database tables.

phpMyAdmin's ability to import and export databases makes it much easier to recover from disaster and even migrate a database from one server to another. Since the process is done through a web-based interface, you don't have to sit in front of the server to recover your database.

The phpMyAdmin export process allows you to export into these formats:

  • SQL
  • CVS
  • CodeGen
  • CVS for Microsoft Excel
  • Microsoft Word 2000
  • JSON
  • LaTeX
  • MediaWiki Table
  • OpenDocument Spreadsheet
  • OpenDocument Text
  • PDF
  • PHP Array
  • Texy! Text
  • YAML

Our example for this tutorial

Let's say you have a web-based tool such as WordPress, and you want to export the database so you can transfer the install to another server. You've already moved over the necessary theme files and such, and all you need now is the database. The next step is to export the database from one server, and then on the new server, import that database to the freshly installed WordPress instance. (I am assuming you have phpMyAdmin installed on source and target servers.)

Warning: It's always best to run this process on a test environment before going live. You never know when something could go sideways, and then you could wind up with a dead site.

Exporting databases

The first step in this process is to export the working database from the source server.

  1. Log in to the phpMyAdmin instance on the source server.
  2. Click the Export tab (Figure A).
  3. From the database drop-down (left navigation), select the wordpress database.
  4. Choose between a Quick or a Custom export. The Quick method should work most of the time, though if you need to get more granular with your export options, click Custom (Figure B). If you choose Custom, you can configure the following:
    • Tables: Choose which tables to export.
    • Output: Choose the template, character set, and compression for the export.
    • Format: Choose to display comments, enclose the export in a transaction, disable foreign key checks, dump table (structure, data, or both).
    • Object creation options: Add statements.
    • Data dump options: Use INSERT DELAYED or INSERT IGNORE statements, select the function to use when dumping data, select the syntax to use when inserting data, maximal length of created query.
  5. Select the format from the Format drop-down (for a wordpress to wordpress export/import, select SQL).
  6. Click the Go button.
  7. Save the file (which, by default, should be named wordpress.sql).

You should now have a file on your local disk called wordpress.sql.

Figure A

Even with the Quick option, you can select your database's output format. (Click the image to enlarge it.)
Figure B

This is a portion of the Custom export screen. (Click the image to enlarge it.)

Importing databases

The import process is just as simple. These steps can be used for either a database exported from phpMyAdmin or a database dumped using another tool (such as the mysqldump command).

You need to make sure there isn't already a database on the target server with the same name. With your database on your local drive, here are the steps you should follow to import that file with phpMyAdmin.

  1. Log in to phpMyAdmin.
  2. Create an empty database from the Databases tab (Figure C).
  3. Click the Import tab.
  4. Click the Browse button.
  5. Locate the file to be imported.
  6. Click Open or OK, depending on your browser.
  7. Select the proper collation from the drop-down.
  8. Select the format of the import file.
  9. Click Go.

After the import process is complete, check the site to make sure everything is good.

Figure C

Make sure the collation of the newly created database matches what is used in the backup file to be imported. (Click the image to enlarge.)

Conclusion

If you're looking for one of the easiest methods of importing and exporting databases on a MySQL environment, you cannot go wrong with phpMyAdmin. Not only does phpMyAdmin offer an incredibly easy to use interface, it also enables you to migrate databases from source to target quickly and with very little hassle.

About

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website getjackd.net.

1 comments