How to Export a MySQL Database?
To export a MySQL Database, we can use the mysqldump command or the MYSQL Workbench GUI as well.
MySQL Workbench
In the MySQL Workbench, go to the Server toolbar and select Data Export.
This opens the Export utility
Here we can select the:
- database(s) to export.
- Whether to export in a Single file i.e. a single SQL script(recommended) or as a separate file for each table.
- We can select whether we want the database with the structure and data or just the data or just the Structure.
- Whether we wish to copy the Triggers, Procedures, etc as well or not.
- We also have Advanced Options like setting the TimeZone, keywords, what kind of quotation marks the script uses, etc.
We can also select the folder in which the dump file will be generated.
Once we select all the required options, we can click on the Start Export button, which will generate our database dump file or files based on the options we have selected. We can then use the generated dump file or files to create a new database.
Using 'mysqldump' command
We can also use the command prompt to generate a database dump. For this, we use the mysqldump command. The basic syntax is
mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]
We have a lot of options for using this command. The complete list is given here.
To generate a dump using this method, please ensure to open a "command prompt (cmd)" window and not the MySQL command-line client.
A simple example of a db dump is
mysqldump -u root -p sakila > C:\dumps\sakila_dump.sql
This would generate a dump file in the folder specified.
