Import Data to a MySQL Database
MySQL Workbench provides an easy way to import data from an external source.
Importing data via MySQL Workbench is a very similar process to exporting data. You can import a whole database. You can import just some tables and/or their data. Or you can import just the data.
Whether you're importing a whole database or just some data, you can do it all via the export/import wizard.
If you're only importing data, you will need to make sure that the database and tables already exist.
If you're importing the database structure (i.e. the script creates the database and/or its tables), you will want to make sure you're not about to overwrite something that you shouldn't. The export script created with the export wizard will drop all tables and create them again, before inserting the data.
Because we just exported our database to an SQL file, we will use that file to create a new database and all its data.
Before we import our database, let's remove the old one first. So this will be like starting from scratch — we are restoring a database to a MySQL server that doesn't currently have that database. Once we've run the file, we will be able to verify that the database and its tables have been created and all data has been inserted.
So go ahead and run the following command:
Refresh the SCHEMAS tab and you should see that your database has disappeared.
Import the Database and/or Data
Use these steps whether you're importing a whole database, a table, or just the data.
When we exported our database to an SQL file, we checked the box that asked Include Create Schema. Because of this, the script will be able to create the database — no need for us to create that first.
Here are the steps involved in importing the whole database and its data. The same steps can be used if you only want to import some data or just the database structure without any data. The only difference is what you choose at step 3.
Start the Import
Configure & Run the Import
Verify that the Database and/or Data has been Imported
If you imported data, you should query the table/s that the data was inserted into to check that the data has imported as expected.
If you imported the database structure, refresh the SCHEMAS tab and you should see the database listed. Navigate through the nodes to verify that the tables exist.
You can also run the following commands:
Display a List of Databases on the Server
Display the Tables on the Default Database
Here, we set the default database to be
FruitShop by using
USE FruitShop. We then ask MySQL to display all tables for the default database (by using
Display Table Structure
You could also find out the structure of a given table by using the following command (simply replace the table name with the table you need the structure of):