How to import and export the database?

Updated on December 3, 2021

Maintaining a backup of your site’s database is of utmost importance. Database import and export is a basic procedure in software development. Data dumps can be used to backup and restore important information. It can also be used to migrate the data to a new server or staging environment.

Database backups can be simply exported and imported using the command line and any other database management tool. This is handy for creating personal backups of your database or if any changes are required.

This KB will explain how to export a MySQL database and import a SQL database backup to your server.

1) Export the Database

First, you must connect your server via SSH or SFTP using Master User or Application User.

Note: You can see the instructions on how to connect your application via SSH and SFTP from our knowledge base article.

a) Export via command line

  • Go to cd applications/application_folder_name/public_html/
  • Type the below command to take the database backup:
mysqldump -u database_username -p database_name > backup.sql

Note: It will take a few seconds or minutes, depending on the size of the database.

  • To verify the database: tail backup.sql
  • You will get following result:
/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;
— Dump completed on 2021-09-15 14:53:00

b) Export via phpMyAdmin

If your server does not have phpMyAdmin installed, kindly follow our KB on how to install and connect phpMyAdmin.

  • Connect phpMyAdmin using your database credentials.
  • From the left column, click on the database name.
  • Click on the Export tab.
  • Format: It is recommended to keep it to default SQL selection.
  • Click Go.
Export via phpMyAdmin

The database will be downloaded to your local machine.

2) Importing the Database

Before importing the database, it is recommended to drop all the database tables; otherwise, you will receive an error or a duplicate table(s) message.

a) Drop Database Tables

  • Connect phpMyAdmin using your database credentials.
  • From the left column, click on the database name.
  • Click Check all
  • With the Selected drop-down menu, select Drop.
Drop Database Tables
  • On the next page, click Yes to execute the query.
click Yes to execute the query

b) Import via Command Line

  • Go to cd applications/application_folder_name/public_html/
  • Type the below command to import the database backup:
mysql -u database_username -p database_name > backup.sql

Note: It will take a few seconds or minutes, depending on the size of the database.

c) Import via phpMyAdmin

  • Connect phpMyAdmin using your database credentials.
  • From the left column, click on the database name.
  • Click on the Import tab.
  • Browser your computer and choose the database file.
  • Format: Keep it to default SQL selection because you have exported the database in the .sql extension.
  • Click Go.
Import via phpMyAdmin

We hope the steps mentioned above are easy to understand, and you have learned the different ways to export and import the database.

Need More Help!

Type in your question at the knowledge base website or comment below

Leave a Comment

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