Lesson Tuesday

Now that we've practiced creating databases in our MySQL server, let's also learn how to export them to a file. This will allow us to migrate those databases to a new host. It will also allow us to include our databases, their tables, and data in our GitHub repositories.

Exporting Databases

We can export your MySQL database into a file that can later be re-loaded into phpMyAdmin to recreate your database and its data with the following steps:

  • Start MAMP servers and click Open WebStart page in the MAMP window.
  • In the website you're taken to, select phpMyAdmin from the Tools dropdown.
  • Open phpMyAdmin and select your database from the left side.
  • Select the Export tab near the top of the screen.
  • Change the Export Method from Quick to Custom.
  • Choose Select All to ensure all database tables are included.
  • In the Output section, choose to output the database to a file.
  • Under Compression choose None or zipped. (Do NOT use gzipped; it has common import errors!)
  • Under Format leave SQL selected.
  • In Format-specific options leave the default selections intact.
  • Under Object creation options check the box for Add CREATE DATABASE/USE. This will make sure your database is created with the correct name when you later import it back into phpMyAdmin.
  • Under Data creation options leave the defaults intact, but make sure the radio button under Syntax to use when inserting data is set to both of the above.
  • Finally, click Go.

Adding Exported Databases to Repositories

  • After following instructions above, phpMyAdmin will download a file called database\name.sql.zip_.
  • Move this file from your default download folder into the top level of your project folder.
  • Add, commit, and push this file to your project's GitHub repository.

Importing a Previously-Exported Database

We can later load an exported database back into phpMyAdmin with the following steps:

  • Start MAMP and click Open WebStart page in the MAMP window.
  • In the website you're taken to, select phpMyAdmin from the Tools dropdown.
  • Select the Import tab.
  • Note that it's important to make sure you're not importing to a database that already exists, so make sure that a database with the same name as the one you're importing isn't already present.
  • Select your database file, and click Go.

You may be asked to include your exported data with this week's code review, so make sure to practice this process in class before Friday!

Importing Using a Script

If you want, you can choose to use the script located in this repository. Follow the instructions in the README to run the script. This is an advanced concept, so don't feel pressure to incorporate it into your workflow.