What MySQL Commands Are Used to Import/Export an SQL File Over SSH?

Home » Knowledge Base » Website Management – Advanced » What MySQL Commands Are Used to Import/Export an SQL File Over SSH?

Our MySQL servers offer great flexibility when it comes to importing and exporting SQL files to and from your database. We allow three distinct ways of achieving this task. You can either use the Database Manager sectionof our Control Panel, you may opt to use the phpMyAdmin utility instead, or you can connect straight to our servers via an SSH connection and issue MySQL commands to import/export your SQL file.

In this article, we will focus on that third option and will show you how you can use Terminal or a similar application to establish an SSH connection. Then, we will go over how to use the MySQL command line to import or export your data.

 

Am I Allowed to Use the SSH Functionality?

Before you can start issuing MySQL commands, you first need to connect to our servers via a secure shell (SSH) connection. As a security precaution, we allow only our web hosting servers to communicate with the database servers. In other words, you cannot issue MySQL commands from your own computer directly to the database server. Instead, you need to log onto our web server first and then issue your MySQL commands from there. The only exception to this rule is our array of VPS hosting packages.

SSH is a fairly advanced feature and as such, it is not available on all of our shared hosting plans. You need to be using either our Web Pro Plus or Max Pack Plus hosting plan. Clients who own a Semi-Dedicated web server can also take full advantage of our SSH functionality. SSH is currently not available on our Basic and free website hosting service.

How Do I Connect to the Server via SSH?

To connect to our servers via SSH, follow the steps below:

  1. Go to the SSH Manager section of our Control Panel.
  2. Make a note of the SSH information that is provided. If you are new to the SSH Manager and you do not have an SSH account set up yet, you can refer to our SSH Manager overview article for guidance.
The SSH Manager provides you with the information you need to establish an SSH connection to the server.
The SSH Manager provides you with the information you need to establish an SSH connection to the server.
  1. Once you have obtained your SSH information, open your SSH client of choice. Linux and macOS users can use the Terminal app. If you are running the latest version of Windows 10, you can use Windows PowerShell to initiate an SSH connection. Or, if your Windows installation is outdated, you can use a third party SSH client like PuTTY.
  2. Enter the following command into your SSH client. Be sure to replace client-id with your Client ID number and server-address with one of the hostnames found in the SSH Manager section of the Control Panel.
ssh client-id@server-address -p 2222
Our shared hosting platform uses a non-standard port for SSH connections, so you should make sure that it is correctly specified in your command.
Our shared hosting platform uses a non-standard port for SSH connections, so you should make sure that it is correctly specified in your command.
  1. Execute the connection command and enter your SSH password when prompted. If the connection is successfully established, you should be connected to our server and you should see your Client ID number as your user ID.
Success! The SSH connection was successful and we are ready to start issuing MySQL commands to the server.
Success! The SSH connection was successful and we are ready to start issuing MySQL commands to the server.

 

What Is the SSH MySQL Command to Export a Database?

In order to create a MySQL dump (backup) of your database, you need to issue the following MySQL command:

mysqldump -h database-address -u database-user -p database-name > save-directory

The above command has four placeholders that you need to update before you can successfully run it:

  • you need to replace database-address with the address of the database server that is powering your MySQL database.
  • you need to replace database-user with the username for your database.
  • you need to replace database-name with the name of your database. On our shared platform, the database-user and database-name are always identical.
  • lastly, you need to replace save-directory with the full path to the place where the database dump should be saved.

If you need help filling in the first three placeholders, you can consult our in-depth Database Manager guide where we outline where the required information can be found.

As for the save-directory placeholder, you need to choose a folder where the database backup should be saved. In our example, we will create a new folder called backup-files using the File Manager section and we will store the MySQL dump there:

The File Manager is an easy tool for organizing your files and folders on the server.
The File Manager is an easy tool for organizing your files and folders on the server.

We are creating the backup-files folder in the /home/www/ directory, so the full directory path should be /home/www/backup-files/. But as part of the full path we also need to include the name of the MySQL dump file that we wish to create. In our case, we will be calling the MySQL dump database-backup.sql. When we append this file name to the full directory, we get the complete version of the save path for our backup: /home/www/backup-files/database-backup.sql

With all of the information now available, the Terminal command for exporting our example database is complete:

mysqldump -h pdb3.awardspace.net -u 2001576_test -p  2001576_test > /home/www/backup-files/database-backup.sql

When we press Enter, the command will be executed and we will be prompted to enter the password for the MySQL database that we are exporting:

Exporting a database via SSH takes a single command.
Exporting a database via SSH takes a single command.

Like most Linux commands, we do not get any sort of confirmation message that the command was carried out successfully. Instead, we can regard the lack of any error messages as proof that everything has gone well. And sure enough, if we check the File Manager we can find our shiny new MySQL backup ready and waiting:

Success! Our backup file was created successfully and we are free to download it for safekeeping.
Success! Our backup file was created successfully and we are free to download it for safekeeping.

 

What Is the SSH MySQL Command to Import a Database?

You can use the following MySQL command to import an .SQL file:

mysql -h database-address -u database-user -p database-name < file-location

For the above command to work, you need to fill the four placeholder values with the correct information:

  • you need to replace database-address with the address of the server that is powering your MySQL database.
  • you need to replace database-user with the username that is assigned to your database.
  • you need to replace database-name with the name of your database. On our shared platform, the database-user and database-name are always identical.
  • finally, you need to replace file-location with the full path to the .SQL dump on your shared hosting account.

You can obtain the necessary database information through the Database Manager section of our Control Panel. If you are not sure exactly which piece of information you need, or if you do not have a database created yet, you can read through our Database Manager overview.

The file-location must be a local file path for your .SQL dump. In our example, we have created a folder called backup-files in the /home/www/ directory and we have placed the our .SQL file called database-backup.sql inside. So our full path will be /home/www/backup-files/database-backup.sql.

With all of the above information at hand, our final MySQL command to import an .SQL file looks like this:

mysql -h pdb3.awardspace.net -u 2001576_test -p  2001576_test < /home/www/backup-files/database-backup.sql

When we run the command by pressing Enter, we will be asked for our database password. Upon providing the database password as well, the command will be executed:

No errors are present, so our .SQL file was sucessfilly imported into the MySQL database!
No errors are present, so our .SQL file was successfully imported into the MySQL database!

 

Conclusion

Admittedly, using the MySQL command line to import and export SQL files may not be the easiest approach to take. Tools like our Database Manager and phpMyAdmin offer one-click solutions that accomplish the same task. What sets these MySQL commands apart is the fact that they give you greater control over the entire process. Additionally, such commands can easily be integrated into comprehensive scripts that can run at set intervals and perform a full backup of your hosting account.

Was this post helpful?

i

Relevant tags:

Connect

Latest posts:

How to Install a MediaWiki Skin

In MediaWiki, skins determine the visual appearance of your wiki website. Skins define how content is presented to users and include such settings for the overall website layout, typography, color scheme, and more. By default, when you install MediaWiki, the platform...

How to Create a Page in MediaWiki

Creating pages in MediaWiki is а core feature that allows you to add content to your MediaWiki website. Whether you are adding details for a project, contributing to a knowledge base, or anything else, learning how to create a page in MediaWiki is crucial for your...

How to Install MediaWiki Extensions

Now that you have installed MediaWiki and learned how to log in to the CMS, it is time to extend MediaWiki's functionality via extensions. MediaWiki extensions are sets of files that add various types of functionality to your MediaWiki website. You can use extensions...

How to Log In to MediaWiki

After you install MediaWiki, it is time to log in to the platform, so you can start working on your new MediaWiki website. To log in, you must open the MediaWiki login page and enter your credentials. Doing so will open the platform’s admin panel. In the following...

How to Install MediaWiki

You can quickly install MediaWiki on any of the AwardSpace web hosting plans. This includes our free web hosting, shared hosting, as well as advanced services such as semi-dedicated hosting and VPS hosting. You can install MediaWiki in a matter of minutes, whichever...



Create a website for free!


Free forever

Our Support Team is Here to Help

 

If you need any questions answered, don't hesitate and contact us. Click the button below and follow the instructions. You can expect an answer within an hour.

 

Contact AwardSpace

 

iNewest knowledge base articles

How to Install a MediaWiki Skin

In MediaWiki, skins determine the visual appearance of your wiki website. Skins define how content is presented to users and include such settings for the overall website layout, typography, color scheme, and more. By default, when you install MediaWiki, the platform...

How to Create a Page in MediaWiki

Creating pages in MediaWiki is а core feature that allows you to add content to your MediaWiki website. Whether you are adding details for a project, contributing to a knowledge base, or anything else, learning how to create a page in MediaWiki is crucial for your...

How to Install MediaWiki Extensions

Now that you have installed MediaWiki and learned how to log in to the CMS, it is time to extend MediaWiki's functionality via extensions. MediaWiki extensions are sets of files that add various types of functionality to your MediaWiki website. You can use extensions...

How to Log In to MediaWiki

After you install MediaWiki, it is time to log in to the platform, so you can start working on your new MediaWiki website. To log in, you must open the MediaWiki login page and enter your credentials. Doing so will open the platform’s admin panel. In the following...

How to Install MediaWiki

You can quickly install MediaWiki on any of the AwardSpace web hosting plans. This includes our free web hosting, shared hosting, as well as advanced services such as semi-dedicated hosting and VPS hosting. You can install MediaWiki in a matter of minutes, whichever...

How to Install an Elgg Theme

After you have installed Elgg core and logged in to the admin dashboard, you can search for themes that can help you customize your new website. Elgg themes are a set of files that help you customize the appearance of your website. Via themes, you can modify website...

Even more web tutorials

Check out our web hosting knowledge base and the WordPress tutorials to learn more, and be better prepared for your website creation and maintenance journey.