Import & Export MySQL Database

Sometimes you may need to move/copy the database from one server to another or localhost. There are 3 ways to do that.

  1. Use phpMyAdmin
  2. Use MySQL Workbench
  3. Use Command line

1] Use phpMyAdmin
For first 2 option, GUI is available so we can simply use it.

Export Database using phpMyAdmin
For this open PhpMyAdmin and log in. Select Database that you want to export. Click on the export tab. phpMyAdmin provides various options for exporting the database. We can also export it in CSV, JSON, XML or some other formats too. Most probably you are interested in SQL. phpMyAdmin also allow us to export only selected tables or all of the tables; or just structures of table only or export with data.

Import Database using phpMyAdmin
For importing database select database on which you want to import this database. Open import tab. Select file that was exported. Here we can also add the .zip file. Change character set if you want. Select format of data that contains in the file and click on import button.

Import operation in PHPMyAdmin

2] Use MySQL Workbench
MySQL Workbench is a tool created by MySQL team. We can perform all operations that can be performed by phpMyAdmin. It was usually used to perform the remote operation on the server. You can download it from https://dev.mysql.com/downloads/workbench/

Export Database using phpMyAdmin
For this open MySQL Workbench. Connect to the database on which you want to perform operations. From right side, Navigation menu clicks on data export. Select database(s) that you want to export. We can also structure, data, trigger, Store procedures, functions, or events too.

workench export

Import Database using phpMyAdmin
After connecting database server. Click on Data Import/Restore from navigation. Select the .sql file that you want to import. Workbench also allows us to import only some of the objects from an exported file. Click on start import to import database.

3] Use Command line
For this, you need to open mysql.exe.

Export Database using Command line
For exporting data use mysqldump. In this command specify username & password of the database that you want to export. We also need to specify a database name that we want to export. After this use > redirection operator and specify output file path.

Import Database using Command line
For importing database we need to use mysql.exe. Here again, we need to pass username & password. and using < redirection operator specify the output file for importing data.

mysqldump -u [username] -p [database_name] > [output.sql]
mysql -u [username] -p [database_name] < [output.sql]

Leave a Reply

Your email address will not be published. Required fields are marked *