Importing a database with SSH

Forgetting SSH

Migrating a website can be a real chore. Even when you’re familiar with the site, things can pop up to ruin your day for a little while. In my case recently it was a few sizeable databases from some large projects.

Somehow one of those – an e-commerce site – had grown up to almost 200mb. I didn’t build this one, I hasten to add. But I did find myself having to move it before giving it an overhaul. For some reason, importing the SQL file only added the usual WordPress tables even after removing a lot of excess from the database.

I’ve hit this problem before while updating a staging site, and knew that importing with SSH would solve the problem quickly. If only I could remember how.

Getting started with SSH

SSH stands for Secure Shell and allows you to connect securely to a remote network, even over an unsecured connection. Previously I’ve used PuTTY, a free SSH client, when using a Windows computer. I haven’t yet found a decent client on Mac however, so had to do this via Terminal.

To connect by SSH you’ll need to know the following:

• The hostname / IP address of the server you’re connecting to
• The Client Shell username and Client Shell password
• The Server Root password.

You will also need to make sure SSH access is enabled on your server, which your hosting company should be able to help you with.

Step by step

It’s useful before you start to upload your SQL file to the root directory of your website. That way you don’t need to break off and do this later.

To login with SSH run the following command, using your server IP/hostname and Client Shell username. In this example I have used csusername as the Client Shell username.

ssh csusername@192.168.50.4

Enter your Client Shell password when prompted, and then issue the su command to switch users. This will allow you to execute commands with the privileges of another user.

su

You will be prompted for a password again; this time use the Server Root password. Now you should be able to navigate to the root of the website where you stored your SQL file.

cd /var/www/vhosts/SITENAME/

Once in the root of the website you can import the SQL file, using the database credentials that you would ordinarily use to login to your database. These are available in your wp-config.php file. In this example I have used dbusername, dbpassword and dbname.

mysql -u dbusername -pdbpassword dbname < sql-filename.sql

Notice that in the above example there is no space after -p. This is intentional, but can cause issues if you have certain characters in your password. If that happens, use the following command:

mysql -u dbusername -p'dbpassword' dbname < sql-filename.sql