Sometimes the only way you can restore a database is with the SSH command line using PuTTY. Here are some helpful hints & tricks to getting your website back up and running in no time.
Why Would I Need To Restore My Database?
There are many instances where you would need to fully restore your website database. Two main instances are website crashes and hackers. Website hackers may involve other steps outside of utilizing SSH, but for the purpose of this article we will deal with the SSH backup only. On some web servers you will need to enable SSH. In order to do so you should contact your web host for more information.
Restore A Database: Step By Step
In order to restore your website you MUST have backups available. If you are not familiar with backing up your website fully, please read my cPanel Backup article.
The First Step
Make sure that you upload your sql file to your web server root. In your backup zip file from cPanel you should have a directory named “mysql“. You will find your database file within this directory. The name of your file will be similar to “databasename.sql“. The “web server root” refers to the “public_html” or “www” directory on your server. The root is usually the directory that you are automatically logged into using your FTP client. If you have not created a database, you may need to if your old one has failed for whatever reason.
The Second Step: Getting Started
How you perform your SSH backup will depend upon the operating system that your personal computer has installed. If you are using Windows (any version), I suggest you use PuTTY. For MAC & and Linux distros, you can use the built in command line system. You will need to be familiar with it in order to work this tutorial. For the purpose of this tutorial we will use PuTTY. If you are on a MAC or Linux jump down to step number three.
Now Let’s Enter Our Information
Now we will need to enter some information so that PuTTY knows where and what to find on your server. You first must find out what your web server IP address is or you can try to use your domain name. If you do not know either you can find out from your host. If you are entering your domain name you should drop the www. So, for example your domain name would look like “mydomain.com“. You should enter your domain/IP in the top box that is highlighted in this screen shot.
Once you enter the host name, click the “Open” button to open the PuTTY command line interface. You should not need to change anything else before clicking the button. Always be sure that the “SSH” is ticked in the “Connection Type” box before clicking the open button.
After you click the “Open” button you may get a message asking if you trust the network security. Click the yes button to proceed.
Step Three: Working In Command Line
In this step we will go over what and how to enter your information to call up the correct directory and find your uploaded sql file. Using PuTTY once you have clicked the “Open” button you will see a command line interface that looks similar or just like the below screen shot.
Now you should see something similar to:
Run this command replacing username with the actual username on your database and database with your database name. Also be sure to replace file.sql with the proper name of your sql file.
You will be prompted for the database password. This is the password that is used on just the database that you are performing your import into. Once you enter the password you should see this screen:
Wrapping It Up Neatly
Your database should now be imported. To check it login to your cPanel and navigate to Databases > MYSQL Databases and check to see that it is finished. Alternately you can also go into phpMyAdmin and check that all the tables are there by navigating to Databases > phpMyAdmin and finding the correct database, clicking on it and viewing the tables.
Why Would I Need To Restore My Database?
There are many instances where you would need to fully restore your website database. Two main instances are website crashes and hackers. Website hackers may involve other steps outside of utilizing SSH, but for the purpose of this article we will deal with the SSH backup only. On some web servers you will need to enable SSH. In order to do so you should contact your web host for more information.
Restore A Database: Step By Step
In order to restore your website you MUST have backups available. If you are not familiar with backing up your website fully, please read my cPanel Backup article.
The First Step
Make sure that you upload your sql file to your web server root. In your backup zip file from cPanel you should have a directory named “mysql“. You will find your database file within this directory. The name of your file will be similar to “databasename.sql“. The “web server root” refers to the “public_html” or “www” directory on your server. The root is usually the directory that you are automatically logged into using your FTP client. If you have not created a database, you may need to if your old one has failed for whatever reason.
The Second Step: Getting Started
How you perform your SSH backup will depend upon the operating system that your personal computer has installed. If you are using Windows (any version), I suggest you use PuTTY. For MAC & and Linux distros, you can use the built in command line system. You will need to be familiar with it in order to work this tutorial. For the purpose of this tutorial we will use PuTTY. If you are on a MAC or Linux jump down to step number three.
- Download PuTTY from the website above if you have not already.
- Install PuTTY following the prompts.
- Open PuTTY. Find where you installed it and double click the icon.
Now Let’s Enter Our Information
Now we will need to enter some information so that PuTTY knows where and what to find on your server. You first must find out what your web server IP address is or you can try to use your domain name. If you do not know either you can find out from your host. If you are entering your domain name you should drop the www. So, for example your domain name would look like “mydomain.com“. You should enter your domain/IP in the top box that is highlighted in this screen shot.
Once you enter the host name, click the “Open” button to open the PuTTY command line interface. You should not need to change anything else before clicking the button. Always be sure that the “SSH” is ticked in the “Connection Type” box before clicking the open button.
After you click the “Open” button you may get a message asking if you trust the network security. Click the yes button to proceed.
Step Three: Working In Command Line
In this step we will go over what and how to enter your information to call up the correct directory and find your uploaded sql file. Using PuTTY once you have clicked the “Open” button you will see a command line interface that looks similar or just like the below screen shot.
- Enter in your login name for your web server. If you do not know it contact your host. Hit the enter button on your keyboard to proceed.
- You will be asked for a password, enter it when prompted. Keep in mind that you will not see characters being typed in the command line as you type them, this is normal. After you type your password, hit the enter button to proceed.
- Now call your “public_html” directory by using the cd command. Your directory structure may be different from what I’ve mentioned here. Always make sure that you have a space between cd and the first part of the directory structure.
Code:
cd /home/yourdomain/public_htmlRun this command replacing username with the actual username on your database and database with your database name. Also be sure to replace file.sql with the proper name of your sql file.
Code:
mysql -p -u username database_name < file.sqlYou will be prompted for the database password. This is the password that is used on just the database that you are performing your import into. Once you enter the password you should see this screen:
Wrapping It Up Neatly
Your database should now be imported. To check it login to your cPanel and navigate to Databases > MYSQL Databases and check to see that it is finished. Alternately you can also go into phpMyAdmin and check that all the tables are there by navigating to Databases > phpMyAdmin and finding the correct database, clicking on it and viewing the tables.