Import Data to a MySQL Database
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.
Preparation
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:
DROP DATABASE FruitShop;
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
Ensure that the MANAGEMENT tab is selected in the left menu.
Click on the Data Export link.
Configure & Run the Import
The Data Import screen will appear.
Select Import from Self–Contained File and navigate to (or enter) the file to import.
In this case, because my script will create the database, I can leave Default Target Schema blank. Also, because I'm importing both the structure and data, I select Dump Structure and Data.
If you only need to import data, select Dump Data Only. If you only want to import the database structure (without data), select Dump Structure Only.
Once done, click Start Import.
The Result
The database will now be imported. Once completed, the Import Progress screen should read Import Completed.
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
SHOW DATABASES;
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 SHOW TABLES):
USE FruitShop;
SHOW TABLES;
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):
DESCRIBE Fruit;
