📓 Creating a Test Database: Exporting and Importing Databases with MySQL Workbench

In this lesson, we'll learn how to export and import a database using MySQL Workbench. In the process, we'll create a test database for the To Do List. You will also use these instructions to export your database to include in your independent project for this course section.

As you are working through the following steps, make sure that the MySQL Workbench application window is expanded to fit your entire screen. MySQL's user interface doesn't resize well to smaller windows causing some buttons to be hidden out of view.

Creating a Test Database

Exporting `to_do_list_with_mysqlconnector`

Let's create a test database for our To Do List.

Launch MySQL Workbench and open your local instance so that the Navigator window is visible.
Select the Administration tab from the bottom of the Navigator window.
Select Data Export from the list of available options.

In the "Navigator" window, select the "Administration" tab and then select "Data Export".

Select the to_do_list in the left hand Tables to Export window. Choose Dump Structure Only in the drop down on the right.
Select Export to Self-Contained File in the Export Options.
Choose a file name and a place to export your database to. For now, the name doesn't matter — we just need to be able to find it so we can import it again. For the independent project, you'll want to name your database dump file firstname_lastname.sql, where firstname is your first name and lastname is your last name.
Optionally, you can check the box to Include Create Schema. Doing so, will include the SQL command to create a database with the same name as the one you are exporting. Do not do this now as you follow along with these instructions to create a test database, and do not do this for the independent project.
Click the Start Export button at the bottom right corner. If it's not visible, try expanding the MySQL Workbench window.

We now have a .sql file that we can use to recreate our to_do_list_with_mysqlconnector database.

To export a database, select the database from those listed, select the option to export to self contained file (including the desired target location), and then select "Start Export".

Importing `to_do_list_with_mysqlconnector`

In the Navigator > Administration window, select Data Import/Restore.
In Import Options select Import from Self-Contained File.
Navigate to the file we just created.
Under Default Schema to be Imported To, select the New button.
- Enter the name of your database with _test appended to the end.
- In this case to_do_list_with_mysqlconnector_test.
- Click Ok.
Navigate to the tab called Import Progress and click Start Import at the bottom right corner of the window.

After you are finished with the above steps, reopen the Navigator > Schemas tab. Right click and select Refresh All. Our new test database will appear.

To import a database, select "Import from Self-Contained File", then select "new" under the section called "Default Schema to be Imported To" and in the input that appears put in the name of your database with "_test" appended to the end. Finally, select "Start Import" on in the window tab called "Import Progress".

Important Note

Test databases should always have the same name as the production database with _test appended to the end. They must also contain the exact same structure as the development database, including the same tables, rows and so on. To ensure they're the same, we always create our test databases by using our development database as a template.

With our test database ready to go, now we can connect our test project to the test database and update our teardown method to empty it.