There’s an easy way to export your entire database, including both the tables and the
data, in MySQL Workbench.
Let’s see how we can do that.
I’ll assume you have MySQL Workbench open and are connected to the database you want
to export.
I’m on MySQL Workbench 8.0, on a Mac, but the process should be the same on Windows.
To export a database in MySQL Workbench, we use a tool called Data Export.
You can find this in two places.
You can go to the Server menu on the top of the screen and click Data Export, or click
on the Administration tab on the left panel and click Data Export.
A new tab will open called Data Export, which looks like this.
On the left is a list of schemas on your MySQL server.
I’ve got quite a few here.
You can use this panel to select the schemas you want to export.
In this example, I’ll just select one, which is called “complex”.
You can select one or more.
Once you select the schema, you’ll see all of the tables in that schema shown on the
right, automatically selected.
Deselect any you don’t want to export.
For this example, we want to include them all, so we’ll leave them all selected.
Just below these panels is the count of selected tables, which shows 2 for me.
To the right is an important drop-down.
It currently says “Dump Structure and Data”.
The structure is the code to create the objects, such as Create Table statements.
The data is what’s inside the tables, which will be Insert statements.
By default, it’s selected as both structure and data.
You can change this to structure only, or data only, but for this example, we want the
whole database, so we’ll leave it as data and structure.
In the Objects to Export section, you can choose to include stored procedures and functions,
events, and triggers.
Check those boxes if you want to include those in your export.
I don't have any in this schema so I’ll leave them unchecked.
In the Export options section, you can specify how you want your exported files to be created.
You can choose between “Export to dump project folder, which creates a separate SQL file
for each table.
The other option is to “Export to a self-contained file”.
This is a single SQL file and is the default selection and the option we will use.
This means we get a single file that can be run.
If you prefer separate files, you can choose the other option, but in my experience, the
single self-contained file works well most of the time.
In either selection, you can choose the folder and filename you want to save the export to.
For this example, we’ll keep it as the default.
We’ll remember this name and location as we’ll open the file when it is generated,
but we can come back here if we forget it.
You can also indicate if you want to include the statements inside a single transaction,
which allows you to undo all of the work in one unit if you have an error.
You can also include the Create Schema command, also known as the Create Database command,
if you want.
I’ll leave these both unchecked.
On the top right of the tab is a button called Advanced Options.
Click this and you can see a range of options you can set for your export.
I won’t go through all of them, as each checkbox has a name and a short description
of what it does.
You can take a look at this list and change any of them depending on your situation.
One option that I’ll change is the quote-names option.
It’s checked by default, and this means that backtick characters will be added to
object names.
I’ve named my objects using simple names without spaces or anything that can cause
issues, so I don’t need the backticks.
Click Return when you’re done.
On the main Data Export panel, click Start Export.
The screen will change to another tab within this Data Export screen, showing the export.
The export will run and then will be completed.
This export only took a couple of seconds because it’s a small database.
But your export may take a lot longer than this.
Here we can see the export is finished.
Your database has now been exported!
Let’s take a look at the SQL file that was generated.
The filename was shown on the previous step, and it’s also shown in the output here.
In this example, it’s Users, BB, dumps, and this Dump SQL file.
Let’s open the file.
We can close this Data Export tab if we want, but we’ll leave it open for now in case
we want to refer to it later.
Open the SQL file in MySQL Workbench by going to File, then Open SQL Script.
I’ll browse to the location where the file was saved, and then open it.
We can see the file is opened.
We’ve got some comments at the start here about the file, and as we scroll down, we
can see the tables are dropped and created, and then data is inserted.
So, our SQL file is generated, and it was exported from MySQL Workbench.
I hope you found this video useful.
If you want to see more videos on SQL and databases, including tips on using MySQL Workbench,
check out the other videos on my channel.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.