A database is one of the most crucial components to consider while creating a website. Whether you use WordPress, Magento, Joomla, or other PHP frameworks like Laravel, CakePHP, or CodeIgniter, you’ll almost certainly need to incorporate a database to hold website data.
In this post, we’ll look at how to export and import MySQL, one of the most widely used database management systems. This information will come in helpful if you need to move your database to a new server or just backup your database.
There are two straightforward methods for exporting and importing MySQL databases:
- Use phpMyAdmin
- Use SSH command line
Now we will explain each method in detail below, so read on!
How to Export and Import MySQL Data Using phpMyAdmin
Exporting
Make sure you’re logged in to your cPanel area before using phpMyAdmin to export an existing MySQL database. Once inside, type phpMyAdmin into the top right search field and select the option that appears:
- A new browser window will open, and you’ll need to choose the database you want to export next. If there are many databases, make sure you select the correct one. Once the database has been selected, click “Export” and pick Quick from the “Export Method” drop-down menu. If you want to re-import the database somewhere else, choose “SQL” format next. Finally, press the Go button.
- All you have to do now is wait a few minutes (or seconds, if your database is small) for the export process to finish. You should be able to locate the.sql file in your Downloads folder now.
Importing
- Again, you will need to go to your cPanel and navigate to phpMyAdmin
- Prepare your SQL database file that you’d like to import
- Now that you have the SQL file ready, go back to phpMyAdmin, and select the empty database (If you haven’t created it yet, go back to cPanel > MySQL Database Wizard). After selecting the database, go ahead and click the “Import” button on the top bar. Next, click “Choose File” to select the .sql file. Then scroll down and click Go.
How to Use the SSH Command Line to Export and Import MySQL
Note that cPanel SSH access must be enabled in order to export and import MySQL from the command line. To connect to your server through Terminal or PuTTY, make sure you know the basics of SSH commands. You’ll also need database credentials, which include the database name, user, and password.
Exporting
First, open your terminal or PuTTY and login to your hosting account. Once logged in, navigate to the directory where you wish the SQL database to be exported and type in this command below:
mysqldump -u DBUSERNAME -p DBNAME > dbbackup.sql
Make sure to replace the DBUSERNAME with the correct database username and DBNAME with the correct database name.
Here’s the breakdown of the command:
mysqldump is the initial command to export the MySql database.
-u clarifies the username of MySQL database
-p specifies the need to use the database password
dbbackup.sql : the result of the export
After typing the command above, you will then be asked to enter the database password. If the password is correct, the export progress will start. Once finished, type in the command ls and the dbbackup.sql should be visible.
Here’s a screenshot taken from Terminal for reference:
Importing
Luckily, importing a MySQL database via SSH includes almost the same exact steps as exporting one. Once you are logged in to your server via Terminal or PuTTY, navigate to the directory where you store the SQL file that you wish to import to an existing empty database. Then run this command:
mysql -u DBUSERNAME -p DBNAME < dbbackup.sql
Don’t forget to replace the DBUSERNAME and DBNAME to the correct database username and name accordingly.
Command Breakdown:
mysql is the initial command to import the MySql database.
-u clarifies the username of MySQL database
-p specifies the need to use the database password
dbbackup.sql : the SQL file that will be imported
You will then be prompted to input the correct password and afterward the import progress will be initiated. Depending on the database size, it can take a few seconds or minutes for the database import to finish.
Here’s another screenshot that can help you:
Conclusion
Now that you’ve seen how simple it is to import or export a MySQL database, there’s no reason to be concerned if you ever need to do so. It is always suggested for beginners to export or import databases using phpMyAdmin because it is lot easier and GUI based. For those of you who are more skilled, simply type the commands above into your terminal and voila! In no time, your SQL database will be exported/imported!