This post was originally published on this site
WordPress is a very simple web application. It is made up of a number of PHP files and a database, typically a MySQL database. The files are the actual web application and the database is where all the information such as users, blog posts, pages and other data is stored.
The WordPress setup is so simple that you can easily do a manual WordPress backup, within just a few minutes. This also means that you can easily migrate or restore your WordPress website to another server, a procedure which we will explain in detail in this article. Knowing how to restore a WordPress website is also handy so you can test restore your WordPress backups.
Note: This aim of this article is to help you better understand how WordPress works. Even though you can script and automate your own WordPress backup and restore solution we recommend you to use an automated WordPress backup service such as BlogVault, which allows you to stores your WordPress backup files offsite and is a reliable solution, unlike manual backups. You can also read our BlogVault review for more information about it.
Let’s get started with the WordPress restore.
Step 1: Create a MySQL Database & User for Your WordPress Restore
First you need to create a new MySQL database to where you will restore the backed up data. You also need to create a MySQL user which will be used to restore the data into the database and by WordPress to connect to the new database.
Creating a MySQL Database and User with phpMyAdmin
Follow the below procedure to connect to the MySQL server using the root (admin) account and create a new database wpdb and a new user wpuser that will be used to both restore and access the WordPress database.
Note: the database name, username and password in this procedure are just an example and you should use your own.
Login to phpMyAdmin.
Click on the Databases tab and create a new database called wpdb.
Once the database is created click on the Privileges tab and click Add user account.
Enter wpuser as username, localhost as Host Name and wpp45Sw0rd! in Password. These credentials are just an example. Change the username and password to whatever you like.
Note: The Host Name is used to specify from where the MySQL user can connect to the server. When using localhost, it means that the user will be used to connect from the same server, i.e. the WordPress PHP files and the database are hosted on the same server.
Scroll down and check the option Grant all privileges on database “wpdb”
Click Go to create the new MySQL user and grant all privileges to the user on the new wpdb
Logout from phpMyAdmin.
At this stage you have created the new database for WordPress and a MySQL user to connect to the database and restore the backup data to it. Follow the procedure below Restoring the WordPress database using phpMyAdmin to restore a database backup that is up to 2MB. If you have a large WordPress database backup, follow the procedure Restoring a WordPress database with the Big Dump Tool which is further down this article.
Creating a MySQL Database and User Using MySQL Commands
Follow the below procedure to connect to the MySQL server using the root (admin) account and create a new database wpdb and a new user wpuser that will be used to both restore and access the WordPress database.
Note: the database name, username and password in this procedure are just an example and you should use your own.
Login to my SQL by using the command
Enter the password once asked for it.
Create the database with the name of wpdb by issuing the command
Create the new MySQL user wpuser with password wpp45Sw0rd! by issuing the command
Grant full privileges access on the database wpdb to the user wpuser by issuing the command
The localhost in the command above is used to specify from where the MySQL user can connect to the server. When using localhost, it means that the user will connect from the same server, i.e. the WordPress PHP files and the database are hosted on the same server.
Reload all privileges to make sure the newly created ones come into effect by issuing the command
Terminate the connection to the MySQL server with
Step 2: Restore the WordPress Database
Now that you have created the WordPress database and MySQL user it is time to connect to MySQL with the new username and restore the data into the database. If your WordPress database backup is larger than 2MB follow the procedure Restoring Big WordPress databases with BigDump tools which is further below in this article..
Restoring the WordPress Database with phpMyAdmin
To restore the backed up WordPress database into the newly created database follow the below procedure:
Login to phpMyAdmin using the newly created user wpuser.
Click on the database name in the left hand side tree and click on the Import
Click the Choose File and browse to the WordPress database backup file, which typically has a *.sql extension.
Click Go to start the import of data to the new database. When the import is complete you will be alerted that the import was successful as seen in the below screenshot.
Restoring the WordPress Database Using MySQL Commands
To restore the backed up WordPress database into the newly created database you only need to issue one command, which is below:
Once you issue the command you will be prompted for the password and once the password is entered, the restore will take place. Here is an explanation of all the switches in the command:
-u is used to specify the user
-p is used to be prompted for the password
wpdb is the name of the database to which you will be restoring the backup
sql is the name of the WordPress database backup file
When the import completes successfully, the normal prompt is returned as shown in the below screenshot. If there are any errors they will be reported.
Restoring a WordPress Database Larger Than 2 MB
Restoring a large WordPress database backup can be tricky because when using phpMyAdmin you cannot upload files that are bigger than 2MB. Also, if you are using the command line, the MySQL server will stop the process after a certain time as a precautionary measure. To circumvent this you have to manually configure the server and increase its timeout values etc.
Thought you can avoid doing all the manual work and use a tool called BigDump instead. It is basically a tool that staggers the SQL queries therefore allows you to restore big MySQL database backups without requiring you to change the MySQL server’s configuration. To restore a WordPress database backup using the BigDump tool simply follow the below procedure:
Download the BigDump tool.
Extract the file php from the zip file and open it with a text editor.
Configure the database connection details in lines 41 to 44. In this case we are using the same database details used in the above examples:
$db_server = ‘localhost’;
$db_name = ‘wpdb’;
$db_username = ‘wpuser’;
$db_password = ‘wpp45Sw0rd!’;
Save the file and rename it to something random, such as myrestore5544.php.
Use FTP to upload the big dump file and the database backup file (*.sql) to the root of your website.
Using a web browser access the file on your website, for example https://www.example.com/myrestore5544.php. Since I uploaded the backup file sql in the same directory of the big dump tool, it automatically recognizes the file and gives me the option to restore the database, as highlighted in the below screenshot.
Click Start Import to import the database. Note that the duration of the process depends on the server’s response time and also on the size of the WodPress database backup. Therefore the larger the backup is, the longer it will take to complete.
Once the database is restored you will be prompted by the tool, as shown in the below screenshot. Should there be any errors you will be alerted about them as well.
Step 3: Copy the WordPress PHP Files
Once you have restored the WordPress database and configured the right privileges, it is time to restore the PHP files that make up WordPress. The process is simple; upload / copy all the files to the new server by using either FTP or SSH. Check the database connection details in the wp-config.php file to ensure that the new connection details are used to connect to the newly restored WordPress backup.
You do not need to copy log, backup and temporary files since they are not needed by WordPress. Typically such files have log, tmp and bak extensions and they are created by plugins and themes for logging and other operational purposes.
STEP 4: IMPORTANT – WordPress Security
The last step is very important. Before you proceed you should:
Delete the big dump tool from your website root directory.
Delete the WordPress database backup file (*.sql) from your website root directory.
Restrict the privileges of the MySQL user on the WordPress database. Read How to Apply Secure WordPress Database Privileges for more information.
Apply the correct file permissions on all the WordPress PHP files and directories.
BONUS TIP: Create the WordPress Database Automatically
If you do not want to manually create the MySQL database and user you can install WordPress and it will create them for you. Once WordPress is installed all you need to do is restore the database and copy the wp-content directory, and the restore is done.
The post Restoring WordPress from a Backup appeared first on WP White Security.
Related posts:
How to Grant Remote Access Privileges to a WordPress MySQL Database
WordPress Backup Blueprints to Backup Your WordPress Manually
Creating a MySQL Database for WordPress