Here’s the scenario: you need to restore a large MySQL Database to your WordPress website. PHYMyAdmin won’t let you do it, so it needs to be done via Command Line and SSH. Here are the steps to get it done.
The general idea is to upload the backup database to a new database and leave the old database alone (in case we need to roll back). Once the upload/restore is done, we swap the database names/passwords in wp-config.php with the new database.
How to Restore the MySQl Database in WordPress
- Get ahold of the MySQL database backup file. Preferably, it should be in .sql format. I’m going to use the name mysqldbbackup.sql as the backup SQL filename for this tutorial. Now upload that file to your website’s root directory. So if your website root directory is /home/winner/public_html/, then you’re going to upload it to simply /home/winner/ and skip the public_html directory.
- In Cpanel, create a new database and a new user and make sure you assign the user to the database and give all permissions. For the sake of this tutorial, I’m going to use dbwinneruser and dbwinnerdb as the new username and database names where you’re going to restore the database.
- Download and Open Putty and SSH into your server. Once you’re at the prompt type in cd /home/winner so you’re now inside the same directory where you uploaded the mysqldbbackup.sql file from Step 1.
- This is where the magic happens. Run the command below inside Putty to restore the database backup into the new database you created in Cpanel. You will be prompted for the dbwinneruser password you selected after running the command below…
mysql -p -u dbwinneruser dbwinnerdb < mysqldbbackup.sql
- Once you see the command prompt return, it’s done. There is no “finish” message. Login to PHPMyAdmin and look around the new database and verify things look ok. Once they are…
- Go to your WordPress Config file in /home/winner/public_html/wp-config.php and change the database name, user and password to the new ones you created. Clear any caches on your website and refresh the home page. Everything should be fully restored now and WordPress will be using the new database you created.
- (Optional). If you were emotionally attached to the old database name/user, you can delete the old database in Cpanel and use PHPMyAdmin to copy the new database with the old name using the Operations tab. Just remember you need to re-attach the db user and assign permission once it’s copied so it will work.
That’s it! You’ve successfully restored a very large MySQL database backup using the Command Line CLI and Putty.