How to import and export database in MySQL

As a web developer or a site owner, you may need to move the database from one server to another. There may be various reasons for that, for example, your intent is to make a backup, restore the site from last known good state, move a web application from development to production server, or any other similar task.

If that's the case, then you should know how to properly import or export the database in both MySQL or MariaDB. This article describes how to perform such a task in an efficient and reliable way.

Importing MySQL Database

The two most common ways to import the database are outlined below. The solution that you choose depends on what functionality is available in your hosting package.

If you have access to the cPanel control panel, it's very likely that the phpMyAdmin is available for you as well. However, if you are using some custom server or a cloud instance, then the option #2 may be an optimal choice.

Option #1: phpMyAdmin

Please navigate to the phpMyAdmin tool in your web site control panel, then select the desired database in left column, as shown in the image below:

After that you need to click on the "Import" button in the top menu on the main content area:

In most cases, you do not need to toggle any options there and you can import a file with the default settings.

Then, you have to open the desired SQL file in the "File to import" section. There should be a button titled as "Browse" or "Choose File", the name may vary depending on your browser.

When the SQL file is selected, you need to scroll the page down and click on the "Go" button in the bottom right corner. This will trigger the import process and the data will be written to your database.

On a success, you should see the following screen in your browser:

Option #2: mysql tool

This method is for advanced users, you can choose it if you know how to work with the console. Please see the following statement, there is our example of how to call the "mysql" command in order to import the database.

mysql -u username -p database < exportdata.sql

For the meaning of each parameter, see the description below:

username - name of the user that has necessary permissions to import the database.

database - name of the database into which you are willing to import the data.

"<" - this character instructs the mysql command line tool to read content from a file.

exportdata.sql - this is the file that must be imported into your specific database.

It's important to note that some of the hosting providers may have built custom control panels. And, in such a case, you may need to review the official documentation provided by such a hosting company, or optionally, you can contact the support team and ask for help with managing your database.

Exporting MySQL Database

Please read the information below on how to export MySQL or MariaDB database. You can choose the option that is most suitable and convenient for you.

Option #1: phpMyAdmin

You need to open the phpMyAdmin tool in the control panel of your hosting provider, after that, you can select the desired database in the left column. Please see how the screen looks on the image below:

Then you need to click on the "Export" button in the top menu on the main content area:

You can choose the "Quick" export method there, it should work fine in most cases. However, you may switch to the "Custom" export method and configure additional options that are relevant to your specific case.

When you are ready, you have to click on the "Go" button in the bottom right corner. This will trigger the export process and the data will be returned to you in a file.

Each browser has unique file download manager, as a result, after clicking the "Go" button, you may be presented with the save file dialog, or the download may start automatically.

Also, if the database is large and has too many records, the download process may take some time and you will have to wait until it's completed.

Option #2: mysqldump tool

Also, you can export the database into a file by using the "mysqldump" command line tool. Please see below how to use it properly:

mysqldump -u username -p database > exportdata.sql

You can read description of each parameter below:

username - name of the user that has necessary permissions to export the database.

database - name of the database from which you'll be exporting the data.

">" - this character instructs the mysql command line tool to write the output into a file.

exportdata.sql - this is the file that will contain data exported from your specific database.

Conclusion

As you have probably noticed in this guide, it's very easy to import or export the database in both MySQL and MariaDB. As long as you have access to the phpMyAdmin tool or the SSH connection is available, you can perform the desired MySQL operation in just a few minutes.

However, if you encounter any issues while working with the MySQL tool on the specific server, feel free to post your questions or notes in the comment form below, your experience would be beneficial for the others.