Backing Up and Copying Large WordPress Installs

Backing up or copying a large WordPress website can be a challenge due to the large amount of data. Often websites need to be copied for backup purposes but also to test new configurations and software.

This method uses FTP to do the file transferring, and no doubt there's a better way to transfer the data directly between the source and destination servers rather than using one's PC in the middle. Next update!

Database

PHPmyAdmin, a popular tool for database management, makes the job of copying data easy, but it won't accept or export to SQL files larger than 50MB, compressed or uncompressed.

The answer is to log on to the server using a terminal session. For MySQL dumps it's easier to use the root user.

mysqldump -u root [database name] > [SQL file name].sql

This can be a file many gigabytes in size, so compress it using the following:

gzip [SQL file name].sql

The file is converted to [SQL file name].sql.gz and it will be much smaller than the original. Download this file to your computer.

If you have WSL installed on Windows, or your PC is Linux powered the data conversion can be done locally. Alternatively, upload it to your destination server.

Processing the SQL file on the destination server will save some processing time, so if it's not used for production work at all do the data changes on the destination server. Having said that, the impact processing has on the server can be reduced using the "nice" command and using the "&" suffix, which means to run the command in the background. For example, compressing a large SQL file use the following:

nice -n19 gzip [SQL file name].sql &

Before importing to the new environment some of the data and references will need to be changed. The new environment probably won't have the same URL for example, and this can be changed by using Linux's sed, a serial editor application.

First uncompress the SQL file using the following command:

gunzip [SQL file name].sql.gz

Next use the uncompressed version to update any data that needs changing.

sed -i 's#https://oldurl.com#https://newurl.com#g' [SQL file name].sql

The "#" is used as a delimiter and saves many "/" being escaped from their normal meaning. SED will make the changes and store them in the original SQL file. For those interested the command is broken down as shown below:

• -i: Edits the file in place.
• s: The substitute command.
• #: The chosen delimiter.
• g: The global flag, which replaces all occurrences on each line, not just the first.

Next, the SQL file needs to be imported. If the SQL file was edited locally, compress it using the same gzip command as before and then upload it to the destination server.

Once the SQL file is on the new server, decompress it, and then import it to the destination database using the following. As before this is easiest done from the root user.

mysql -u root [database name] < [SQL file name].sql

The public_html Folder

This can be even more data than the SQL file. Images and unforeseen cache files can severely affect the file size.

tar -czf [Backup file name] --exclude="[exclude files]" [public_html folder] 

Once done your backup is complete.

Restoring the WordPress Installation

The Database

As for backing up the database PHPmyadmin will not be useful as its file upload size probably won't be enough to handle the large amount of data. It can be used to clear out the database or alternatively create a new one using CPanel and put the data into that.

Using the command below to restore the database. You will be prompted for the password.

gunzip < [backupfile.sql.gz] | mysql -u [username] -p [database_name]

File Recovery

Upload the tar.gz file to the new server and use the following command to restore the files. Use the rm command to clean up the folder if desired. All files and sub folders are removed, preserving public_html in this example. The -f parameter turns off the prompting for each folder and file deletion.

rm -rf public_html/*

Next, run tar to extract the files to the public_html folder. Existing files will be overwritten. Files in the folder and not in the back up will be left intact.

tar -xf [backup-filename.tar.gz] -C /public_html

File and Folder Permissions

For the correct serving of website files, the permissions need to be correct. The owner should be the username of the account.

For folders check the permissions are 755, which is rwxr-xr-x, corresponding to Owner, Group and World with permissions Read, Write and eXecute. A dash means the permission is not enabled.

For files the permissions are 644, which is rw-r--r--, corresponding to Owner, Group and World with permissions Read, Write and eXecute.

To change the folders use:

find /home/username/public_html -type d -exec chmod 755 {} \;

And for the files use:

find /home/username/public_html -type f -exec chmod 644 {} \;

The WordPress website is now restored and should be tested for basic functionality before leaving it alone.

Job done!