How to Import and Export a MySQL Database

This article provides a quick guide on how to import and export a MySQL database. We’ll be guiding you through all you need to know to run these simple tasks.

You can create MySQL databases, set up accounts for managing them and assign privileges from your  ScalaHosting account through the MySQL databases tool in the Spanel’s Databases section.

How to Import and Export a MySQL Database

And here’s how to import and export the databases.

How to Import a MySQL Database

There would probably be an occasion where you’d want to import a MySQL database into your server. You can do that through either of these two waysphpMyAdmin or using the command line. Use this guide to get started.

Method 1: Using PhpMyAdmin

PhpMyAdmin is a free PHP software tool that allows webmasters to administer and manage MySQL over the Web. You can easily import a MySQL database through its web interface.

Here’s how.

Log in to your Spanel account. By default, the login URL is /spanel of your root domain. For instance,  https://[yourdomain]/spanel.

How to Import and Export a MySQL Database

Click on the phpMyAdmin in the Databases section of the Spanel.

How to Import and Export a MySQL Database

Spanel will redirect you to the phpMyAdmin web interface. Click on Import on the top menu bar.

How to Import and Export a MySQL Database

Select the file you want to import.

Before that, compress the file as gzip, bzip2 or zip, or leave it uncompressed. Save it on your computer using this nomenclature — .[format].[compression] — example, .sql.zip

How to Import and Export a MySQL Database

Select the file format and click on the Go button to begin the import.

How to Import and Export a MySQL Database

The process will start running, and you’ll see the imported database after completion.

Method 2: Using Command Line

Importing a MySQL database through the command line requires a secure shell (SSH) access. SSH allows you to remotely log in to your hosting account to perform tasks through text commands.

It enables webmasters to perform tasks as if they’re right in front of their servers. Follow this guide to import a MySQL database to your server remotely.

Log in to your FTP client to transfer the file you want to import to your hosting account. Let’s assume you saved the file as dbimport.sql

How to Import and Export a MySQL Database

Log in to your hosting account using SSH. 

PuTTy is a popular SSH client. Install the software on your computer, if you don’t have it already, to create an SSH connection.

How to Import and Export a MySQL Database

Change to the directory where you uploaded the file, in this case, dbimport.sql; by typing the command “cd.” 

Use this command to import the file to your database: mysql -u username -p dbname < dbimport.sql then press Enter.

Username and dbname in the above command should be your database’s username and name, respectively.

How to Export a MySQL Database

Webmasters export their MySQL databases for a lot of reasons. For some, it could be for backup, while others might be exporting the database to transfer to another server.

Whatever the case, there are also two ways you could do this.  And here’s how to get started.

Method 1: Using PhpMyAdmin

To export your database using PhpMyAdmin, then log in to your Spanel account.

On the Spanel interface, locate the phpMyAdmin tool in the Databases section and click on it.

How to Import and Export a MySQL Database

Click on Export on the top bar of the phpMyAdmin interface.

How to Import and Export a MySQL Database

Click on the “Custom” radio button to list all the databases on the server. Choose the export format, then select the database you intend to export.

How to Import and Export a MySQL Database

Configure the export options and click on the “Go” button to export the file to your computer.

Method 2: Using Command Line

Using phpMyAdmin places a limit on the file size you can export. The command line provides a safe and efficient way to export both small and large databases.

Here’s how to export a database using the command line.

Log in to your server remotely using SSH. If you’ve physical access to the server, open the DOS or command terminal window to access the command line.

How to Import and Export a MySQL Database

Windows Terminal 1.0

Use this command mysqldump –routines -u username -p dbname > dbexport.sql  to export the database. Replace “username” and “dbname” in the command with the username and database name of the database you want to export. 

Replace dbexport.sql with the file name and format you want to export the database to. When prompted, enter your password to run the export. The database export will reside in your server in the file name and format you specified. 

Connect remotely to the server using FTP to download the file to your local computer.

Conclusion

Exporting and importing MySQL databases is beneficial for backing up and restoration. Failure to backup could put your data at risk. Thankfully, there are two quick and safe options for doing that.

So, you’ve no excuse not to back up your database file.

Rado

Author

Working in the web hosting industry for over 13 years, Rado has inevitably got some insight into the industry. A digital marketer by education, Rado is always putting himself in the client's shoes, trying to see what's best for THEM first. A man of the fine detail, you can often find him spending 10+ minutes wondering over a missing comma or slightly skewed design.

Write a Comment

Required*