How to Back Up on a Rental Server Without Additional Fees

Differences Between Cloud and Rental Hosting Services, and Their Advantages

Until now, I have primarily used cloud services like Oracle Cloud, Google Cloud, and AWS. These services are highly flexible, allowing you to do a wide range of things. For example, you can create your own server, install your favorite software, and customize it as you wish. However, this level of freedom requires a certain amount of knowledge. You need to handle server settings and security management yourself, which can be a bit challenging for beginners.

On the other hand, I have recently started using rental hosting services that are easy to use. Compared to cloud services, these hosting services offer less flexibility, but they have a significant advantage—they are simple to use. The setup is straightforward, and you don’t have to deal with complicated tasks, making them very user-friendly for beginners.

However, there is one thing to be aware of. Some rental hosting services may charge additional fees for taking backups. Many of them provide automated backup services, but using these features often comes at an extra cost.

How to Back Up by Yourself

This time, I’ll show you how to back up by yourself without paying those extra fees. Even with a rental server, you can easily take backups using a tool called rsync!

What is rsync?

You might be wondering, “What is rsync?” Simply put, rsync is a tool for efficiently copying and synchronizing files and folders. For instance, if you want to back up important data on your computer or server, rsync allows you to easily and efficiently copy that data to another location without unnecessary duplication.

What’s great about rsync is that it doesn’t just copy files as a whole; it can copy only the changed parts (differences). This means that even if you have a large number of files or big data, rsync will only transfer the necessary parts quickly, without copying everything again. This saves both time and data.

How to Use It

“Okay, rsync sounds useful, but how do you actually use it?” you might be thinking. Don’t worry, rsync is surprisingly simple. Let’s take a look at how to use it.

Here is a basic rsync command:

rsync -avz /path/to/source/ /path/to/destination/

This command copies the contents of the /path/to/source/ folder to /path/to/destination/. Here’s a simple explanation of each option:

• -a: Archive mode, which preserves file permissions, timestamps, and other attributes.
• -v: Verbose mode, which shows what is being copied (useful if you want to know which files are being transferred).
• -z: Compresses the data during transfer, which reduces the transfer time.

Let’s Look at a More Concrete Example

For instance, suppose you want to back up important data from a rental server to your home computer. In such a case, you would use a command like this:

rsync -avz /home/directory_to_backup/ user@home_IP_address:/home/user/backup/

With this, the data on the rental server will be safely copied to your home computer. As I mentioned earlier, the strength of rsync lies in copying only the “differences,” which saves both time and data.

FTP Alone Is Not Enough! For a Complete WordPress Backup, Don’t Forget the Database

Many people running WordPress sites may be backing up their files via FTP. However, backing up only the files is not sufficient for WordPress. Important data, such as post and page content, comments, and plugin settings, are all stored in the database. Without this database, you cannot fully restore your site with just the files.

The key here is to back up both the files and the database. Especially if you’re using a rental server, it’s crucial to back up your database regularly.

Backing Up with SSH

To efficiently back up WordPress, you’ll typically need a server environment that allows SSH connections. Using SSH makes it easy to automate file and database backups. Many servers support SSH connections, so it’s a feature worth taking advantage of.

Using VS Code

Visual Studio Code (VS Code) is a very convenient tool when working with servers via SSH. By using the Remote – SSH extension in VS Code, you can easily edit files on the server and run commands directly from the terminal, making backup tasks smoother.

However, it can be challenging to connect to a server with VS Code when using rental hosting services with certain restrictions. For example, issues with permissions or security settings may prevent VS Code’s remote features from working properly.

How to Use TeraTerm for SSH Connections

Therefore, in this guide, we’ll proceed by using TeraTerm instead for SSH connections. TeraTerm is a commonly used SSH client for Windows—it’s simple, lightweight, and versatile. With TeraTerm, you can safely connect to even restricted servers and carry out the necessary backup tasks.

What to Do After Connecting via SSH

There are many detailed guides on how to use SSH clients like PuTTY or TeraTerm, so we won’t cover that here. Instead, we’ll focus on how to back up WordPress site files after logging into the rental server via SSH.

Typically, when you log in via SSH, you will start in your home directory.

The web directory is located under the home directory, and it contains WordPress-related files. Basically, all WordPress files are within the web directory, so it’s essential to first back up this entire directory.

For example, unless you have made custom settings, the web directory includes WordPress themes, plugins, uploaded image files, and more. In other words, by backing up the entire web directory, you can cover all the WordPress files.

In the next step, I will explain how to back up this web directory using specific commands.

Command to Save the web Directory on the Backup PC

First, I will explain the specific command to transfer the web directory to the backup PC. In this example, the backup PC is assumed to be running Ubuntu 24.04.

Backup Destination Information:

• IP Address: 123.123.123.123 (or domain name)
• OS: Ubuntu 24.04

To connect via SSH and back up the entire web directory using rsync, use the following command:

rsync -avz /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

Command Details

• yourbackupuser: This is the username to log in to the backup PC (IP address: 123.123.123.123). Replace this with the username you created when installing Ubuntu 24.04. For example, if you log in to your home PC with the username “john,” this part would be john@123.123.123.123.
• /home/yourbackupuser/backup/: This is the directory on the backup PC. Specify the path of the folder where you want to save the backup. Replace yourbackupuser with the username on the backup PC. For example, if the username is john, it would be /home/john/backup/.

Explanation of Options

• -a: Archive mode, which preserves file permissions, timestamps, and other attributes during copying.
• -v: Verbose output, which shows what is being copied (useful for confirming which files are being transferred).
• -z: Compresses the data during transfer to shorten the transfer time.

With this command, the WordPress files on the rental server will be safely transferred to the backup PC.

Creating the Backup Directory and Setting Permissions

Before performing the backup, you need to create a directory for the backup on the backup PC (Ubuntu 24.04). Proceed with the following steps.

Create a Directory on the Backup PC

First, log in to the backup PC via SSH and create a directory to store the backup. Here, I will create a directory named backup.

mkdir -p /home/yourbackupuser/backup

Replace yourbackupuser with the username on the backup PC.

Setting Permissions

To set the appropriate permissions for the created directory, execute the following command:

chmod 700 /home/yourbackupuser/backup

chmod 700 gives the owner of the directory read, write, and execute permissions only. This is an important step to ensure the security of the backup directory.

With this, the backup destination is prepared, and you can safely transfer data.

How to Set Up SSH Authentication Keys

Next, I will explain how to use SSH authentication keys to securely back up data without having to enter a password each time. By setting this up, you can automate the rsync command and make the backup process more efficient.

Generate SSH Keys on the Backup Source (Rental Server)

First, generate SSH keys on the backup source (the rental server) by running the following command:

ssh-keygen -t rsa

You will be prompted to enter a filename and passphrase, but if you have no specific reason, you can simply press Enter to proceed.

Copy the SSH Public Key to the Backup PC

Next, copy the generated public key to the backup PC. You can easily do this using the following command:

ssh-copy-id yourbackupuser@123.123.123.123

In this case, yourbackupuser is the username you use to log in to the backup PC.

Testing the Connection

Once the setup is complete, check if you can connect to the backup PC via SSH.

ssh yourbackupuser@123.123.123.123

If you can connect without any issues, you can use rsync for backups without entering a password from the next time onward.

Supplemental Information

The general syntax of the ssh-copy-id command is as follows:

ssh-copy-id [options] [username]@[host]

A specific example would look like this:

ssh-copy-id -i [path_to_public_key] [username]@[hostname_or_IP_address] -p [port_number]

Explanation of Each Part:

• -i [path_to_public_key]: Specifies the public key file to copy. If omitted, it defaults to ~/.ssh/id_rsa.pub or ~/.ssh/id_ed25519.pub.
• [username]@[hostname_or_IP_address]: The username and hostname (or IP address) used to connect to the remote server.
• -p [port_number]: The port number to use for SSH connection. The default is 22, but specify it if it has been changed.

Example:

For instance, if you want to copy the public key to a server with IP address 123.123.123.123 using user yourbackupuser on port 2222, use the following command:

ssh-copy-id -i ~/.ssh/id_ed25519.pub yourbackupuser@123.123.123.123 -p 2222

As you can see, the ssh-copy-id command is simple, but you can add options as needed to use it flexibly.

The ssh-keygen -t rsa command can be executed from any directory. When executed, it typically creates keys in the .ssh folder in your home directory (~/.ssh/). Unless you specify a different location, the private key will be saved as id_rsa and the public key as id_rsa.pub in that folder.

Specific Details:

• Private key location: /home/username/.ssh/id_rsa (by default)
• Public key location: /home/username/.ssh/id_rsa.pub

You can also specify a different location when running the command. In that case, follow the prompt to choose the directory.

Typically, keys generated with ssh-keygen are saved as ~/.ssh/id_rsa and this key is automatically used for default SSH connections. Therefore, if you haven’t specified otherwise, the rsync command will also use this key by default.

Thus, if you have saved the key in the default location (~/.ssh/id_rsa), you can run the following command without specifying the key:

rsync -avz /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

If the SSH key is saved in a non-default location or if you want to use a specific key, use the -e option to explicitly specify it. For example, if you saved the key as my_custom_key, use the following command:

rsync -avz -e "ssh -i /path/to/my_custom_key" /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

In summary, you don’t need to specify the key if using the default key, but if using a custom key, you need to specify it with the -e option.

However, we encountered a problem. It seems that due to certain restrictions, the ssh-copy-id command cannot be used. The following error is displayed:

-bash: ssh-copy-id: command not found

Solution: Manually Copy the SSH Public Key

Generate SSH Keys (If Not Created Yet)

First, generate SSH keys on the backup source server.

ssh-keygen -t rsa

The generated public key is usually saved in ~/.ssh/id_rsa.pub.

Manually Copy the Public Key to the Backup PC

If ssh-copy-id is not available, you need to manually add the public key to the ~/.ssh/authorized_keys file on the backup PC.

First, check the contents of the public key:

cat ~/.ssh/id_rsa.pub

Copy the displayed public key.

Next, connect to the backup PC via SSH:

ssh yourbackupuser@123.123.123.123

Once logged in to the backup PC, add the public key to ~/.ssh/authorized_keys. If the ~/.ssh directory does not exist, create it:

mkdir -p ~/.ssh
echo "public key contents" >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
chmod 700 ~/.ssh

Now the public key is properly set up, and you can connect via SSH without a password from the next time onward.

However, it seems that you can connect using the command ssh yourbackupuser@123.123.123.123.

The authenticity of host ‘60.82.66.64 (60.82.66.64)’ can’t be established.
ECDSA key fingerprint is SHA256:67KE/wgp+T5CHdx/sz4teS1MhT7VrrjL/UH9FXMgCdE.
ECDSA key fingerprint is MD5:cd:bb:21:a1:12:9e:5f:21:74:e5:82:3a:f0:05:1b:24.
Are you sure you want to continue connecting (yes/no)?

However, you may have a question if you’re already using SSH connections from other PCs on the LAN to this backup destination PC, and an authorized_keys file already exists. The concern is whether creating it again might prevent connections from other PCs on the LAN. Here’s a summary of the situation and the solution:

In fact, you only need to add the public key to that file. Follow these steps:

Method to Add a Public Key to an Existing authorized_keys File

Display the Public Key on the Source Server

First, run the following command on the backup source server to view the public key content:

cat ~/.ssh/id_rsa.pub

Copy the displayed public key content.

Add the Public Key to authorized_keys on the Destination PC

Since you can already SSH into the backup destination PC, connect to it and add the public key to the authorized_keys file with this command:

echo "public key content" >> ~/.ssh/authorized_keys

This will append the public key.

Check File Permissions

If necessary, verify and set the permissions for authorized_keys:

chmod 600 ~/.ssh/authorized_keys
chmod 700 ~/.ssh

This method essentially adds a second connection information. The public key should now be correctly added to the destination PC, allowing passwordless connections.

When adding a new public key to the authorized_keys file, overwriting it could potentially block existing connections from PCs on the LAN. However, by appending to the authorized_keys file, both existing and new keys are authorized, allowing connections from all PCs.

To avoid affecting existing connections, append the public key to the file. The steps are essentially the same as explained above:

Steps to Append a Public Key to the authorized_keys File

1. Display the public key on the source server
2. Append the public key to authorized_keys on the destination PC
3. Check permissions

Using >> means append, which adds the new key to the existing authorized_keys without overwriting.

This method allows SSH connections from both existing LAN PCs and the newly added backup source server. The previous method also used the append (>>) approach to add the public key to authorized_keys. Using >> preserves existing content while adding the new key, so it doesn’t affect access from other previously configured PCs.

In essence, adding public keys is done safely without removing existing keys, so you can proceed with confidence. The authorized_keys file can hold multiple public keys, one per line, allowing multiple connection configurations simultaneously. This enables different PCs or users to SSH into the same server.

On the other hand, there’s also a method to deliberately overwrite. For instance, if you want to allow only specific public keys and invalidate all existing ones, you can overwrite the authorized_keys file.

Method to Overwrite the authorized_keys File

Overwriting the Public Key

Use the following command to overwrite the public key in the authorized_keys file. This will delete all existing public keys and allow only the new public key:

echo "new public key content" > ~/.ssh/authorized_keys

In this case, > is used for overwriting. All existing content is deleted, leaving only the specified new public key in authorized_keys.

Check Permissions

Don’t forget to check permissions after overwriting:

chmod 600 ~/.ssh/authorized_keys
chmod 700 ~/.ssh

Scenarios for Overwriting

• When you want to use only the new key and disable other connections
• When you want to delete all old, unnecessary keys to enhance security

However, this operation should be done carefully. If the overwritten file doesn’t include necessary keys, there’s a risk of losing connection, so prior confirmation is important.

Regardless of the method used, generating an SSH key on the source server is a fundamental step. Specifically, use the following command to generate an RSA key:

ssh-keygen -t rsa

This process creates an SSH key on the source server, and by adding its public key to the destination server, you can establish SSH connections without a password.

The key generation process is a common, essential step for all SSH connection setups, so it’s important to run this command first to prepare the keys.

Personally, I often edit the authorized_keys file directly with a text editor because I’m accustomed to editing in the directory where the keys are located.

Benefits of Editing with an Editor

• Easy management of multiple public keys: Opening the file with an editor allows you to verify the keys already registered and easily delete unnecessary keys or add new ones. It also helps avoid the risk of accidentally overwriting with the echo command.
• Verification of formatting: You can visually verify the formatting, such as line breaks or extra spaces in the public keys, which helps prevent errors.
• Safe editing: When accessing a remote server, using the echo command incorrectly to overwrite the authorized_keys file may result in deleting existing keys, causing loss of remote access. Using an editor allows you to verify the current contents while making edits, enhancing safety.
• Managing multiple keys and adding comments: By using an editor, you can add comments to each public key, making it easier to identify which key belongs to which device. This is particularly useful when managing keys for multiple devices or users.

Recommended Steps

1. Connect to the remote server via SSH.
2. Open the authorized_keys file in an editor (e.g., nano, vim, vi):

nano ~/.ssh/authorized_keys

3. Add or delete the necessary public keys and save.

Notes

• When editing, it is important to accurately copy the entire line of the public key. If there is a line break in the middle, the key may not be recognized correctly.
• After editing, it is recommended to check the permissions of the file. The authorized_keys file should typically be set to permission 600 (read and write only).

chmod 600 ~/.ssh/authorized_keys

Editing with an editor is convenient because it allows for careful work.

Port Forwarding and Router Configuration

If you want to connect to the backup PC via SSH from home or externally to perform backups, you need to configure port forwarding and router settings. Below, I will explain the specific configuration steps.

1. Opening Ports on the Backup PC (UFW Configuration)

First, if the backup PC (e.g., Ubuntu 24.04) is using a firewall, you need to open the SSH port (usually port 22). Use the following command to open port 22:

sudo ufw allow 22/tcp

This prepares the backup PC to accept SSH connections from external sources.

2. Router Port Forwarding Configuration (for Home)

To remotely access the backup PC within your home network, you also need to configure port forwarding on your router. By setting up port forwarding, you can connect to the backup PC from outside the home network via the internet.

Router Configuration Example (TP-Link)

• Service Name: Assign any name you like (e.g., SSH Backup)
• Device IP Address: 192.168.0.91 (the local IP address of the backup PC)
• External Port: 22 (the default SSH port)
• Protocol: TCP

This configuration allows SSH access to your home PC from the internet.

Dynamic DNS (DDNS) Configuration

Many routers have a Dynamic DNS (DDNS) feature, which allows you to connect using a domain name instead of an IP address. Even if the IP address changes, you can easily access your home PC using the domain name.

Example (TP-Link Router)

• Dynamic DNS Service: Using TP-Link’s DDNS feature
• Domain Name: test.tplinkdns.com

Using this domain name, you can connect via SSH to your home PC through test.tplinkdns.com from outside.

Different Names for Port Forwarding Depending on the Router

The name for the port forwarding function may vary depending on the router. Below are some of the names used by different manufacturers and models for port forwarding:

• Port Forwarding: The most common name.
• Virtual Server: Used to describe the function of forwarding specific ports to an internal server.
• NAT Settings (Network Address Translation): Port forwarding may be done as part of NAT settings, including “Port Forwarding.”
• Application Sharing: Used by some routers to open ports for specific applications.
• Gaming Mode: Found on gaming-oriented routers, this opens specific ports for gaming purposes.

Common Items

Regardless of the name, the following common settings are usually found:

• External Port Number: The port number used for accessing from the internet.
• Internal Port Number: The port number used by the device receiving the communication within the internal network.
• IP Address: The IP address of the device in the internal network that receives the communication (it is recommended to use a static IP address).

Refer to the router’s manual or the help section of the settings screen to find the relevant settings. Even if the name differs, the basic function remains the same, so refer to the appropriate section in the manual for configuration.

Enabling the Firewall and Opening Ports (Ubuntu 24.04)

On Ubuntu 24.04, the firewall (UFW: Uncomplicated Firewall) is disabled by default. To enhance security, it is important to enable the firewall and only open the necessary ports.

1. Enable the Firewall

First, if the firewall is disabled, use the following command to enable it:

sudo ufw enable

Running this command enables the firewall, blocking unnecessary external access.

2. Open Only the Necessary Ports

Next, open only port 22, which is required for backup and SSH connections. This allows SSH connections while restricting access from other ports.

sudo ufw allow 22/tcp

With this, only the minimum necessary port is opened, ensuring security while allowing backup operations.

Additional Explanation to Prevent Misunderstanding of rsync

When beginners use rsync for backup, they might have the following concerns:

1. Will the backup source be overwritten if the destination is empty?

One common misunderstanding is the fear that if the backup destination is empty, the backup source (the rental server) will be deleted. However, there is no need to worry. rsync simply copies data from the source to the destination, and the data on the source is not deleted. Even if the destination is empty, the source will not be affected.

2. First, try copying all the data

If you are concerned, it is recommended to first copy all the data while the destination is empty. This allows you to verify that all files are correctly saved at the destination, giving you confidence to proceed with future backups.

rsync -avz /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

3. The destination is not affected if files are deleted from the source

Even if files are deleted from the source (rental server), the data at the destination (such as a home PC) will not be deleted. In a standard copy operation, rsync only adds data from the source to the destination and does not delete anything at the destination.

4. How to delete unnecessary files from the destination

If you want to ensure the destination matches the source exactly (meaning that files deleted from the source are also removed from the destination), you can use the following option:

rsync -avz --delete /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

With the --delete option, the backup source and destination will be identical. Files deleted from the source will also be removed from the destination. However, this operation is powerful, and once files are deleted, they cannot be recovered, so use it with caution.

Key Points of the –delete Option

Benefit: Files and folders deleted on the server are also deleted from the backup destination, keeping both environments in sync.

Caution: Always check the contents before running the command to avoid accidentally deleting necessary files.

Now you understand the proper way to fully sync the server and the backup destination!

What happens if the file name is the same but the size changes?

rsync detects differences in file size or content even if the file name is the same, and only transfers the modified parts. For example, if you edit a WordPress theme file, those changes are reflected in the backup destination.

Example:
Server file: style.css (10KB)
Backup file: style.css (8KB)

In this case, rsync detects the difference and copies the new style.css from the server to the backup destination. Since only the changed parts are transferred, transfer time and data size are minimized.

How to Prevent Extra Data from Being Left Behind

If a file is deleted from the source but remains at the backup destination, it can waste space and make management more complicated, especially when migrating data to a different location.

Role of the –delete Option

To prevent leaving behind extra data, the –delete option is useful. It ensures that files deleted from the source are also deleted from the destination, so no unnecessary data remains.

So, should you use it or not?

You might wonder whether to use the –delete option. Personally, I use it to avoid leaving unnecessary data.

However, it’s safer not to use –delete during the first backup. This allows you to check the contents of both the source and the destination without the risk of accidentally deleting files from the server.

1. First Backup

For the first backup, use the following command:

rsync -avz /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

Without the –delete option, you can ensure the data is copied correctly.

2. Subsequent Backups

For subsequent backups, use the following command to fully sync the source and destination:

rsync -avz --delete /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

Using this command, files deleted on the server side will also be removed from the backup destination, leaving no unnecessary data behind.

How to Exclude Specific Folders or Files from Backup: The –exclude Option

When using rsync to back up files or folders, you may not need to transfer everything. For instance, cache files or temporary files might not be worth backing up. In such cases, the –exclude option is useful.

With this option, you can exclude specific folders or files from the backup, avoiding the transfer of unnecessary data.

How to Use –exclude

The basic usage is to add the –exclude option to the rsync command and specify the folder or file you want to exclude.

Example: Excluding a Specific Folder

For instance, if you want to exclude the cache folder within the web directory from being backed up, you would use the following command:

rsync -avz --exclude 'cache/' /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

Example: Excluding a Specific File

Next, if you want to exclude a file, such as debug.log, from the backup, you would use:

rsync -avz --exclude 'debug.log' /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

Key Points for the –exclude Option

• You specify the files or folders to exclude by their paths. To exclude an entire directory, add a trailing slash (/) to the directory name.
• You can exclude multiple files or folders by repeating the –exclude option.

Example: Excluding Multiple Folders or Files

rsync -avz --exclude 'cache/' --exclude 'debug.log' /home/users/0/username/web/ yourbackupuser@123.123.123.123:/home/yourbackupuser/backup/

In this command, both the cache folder and the debug.log file are excluded from the backup.

By using the –exclude option, you can avoid backing up unnecessary data, making your backups more efficient. Take advantage of this feature to shorten backup times and reduce the management of unnecessary data!

Side Note: Rclone is Recommended if There Are No Server Restrictions

If there are no restrictions on the server, using Rclone is another option. Rclone is a tool that makes file synchronization and backup incredibly simple, allowing you to smoothly sync data without worrying about detailed options like in rsync.

Rclone is also excellent at integrating with cloud storage services, supporting many platforms such as Google Drive and Dropbox. If your server environment allows it, trying Rclone for more efficient backup management could be a great choice.

Whimsical Blog

What is rclone for cloud storage? | Whimsical Blog A detailed guide on Rclone commands to streamline cloud storage management. Includes extensive explanations and practical examples, making it accessible even fo…

Whimsical Blog

Convenient use of Rclone (changing the image output destination for stable diffusion) | Whimsical Bl… An easy-to-follow guide on using Rclone to seamlessly manage multiple cloud storage services. Discover unexpected and convenient ways to enhance your workflow w…

1. Files Alone Are Not Enough! You Need to Back Up the Database, Too

Do you think you can fully restore your WordPress site with just the files?

In reality, both the files and the database are required for a complete WordPress backup. Each plays a separate role, and one without the other is insufficient.

Role of WordPress Files

WordPress files include themes, plugins, uploaded images, and media files, all of which contribute to the design and functionality of your site. However, the files do not contain posts, pages, comments, or user information.

Role of the Database

The actual content of your WordPress site, including posts, comments, user data, and site settings, is stored in the database. Without the database, all site content would be lost.

Example: A Car Comparison

Think of the WordPress files as the body of a car, and the database as the engine and electronic systems. If either is missing, the car (or site) won’t function properly.

You Need to Back Up the Database, Too!

To keep your WordPress site secure, it’s essential to back up both the files and the database. This way, even if a problem occurs, you can restore the entire site.

2. Preparation: Confirm SSH Access and Database Information

To back up the database, you first need to prepare the information required to access the database. The database information for WordPress is stored in the wp-config.php file.

Step 1: Confirm SSH Access

First, verify that you can connect to the server via SSH. You can use tools like TeraTerm or PuTTY to establish the connection.

Step 2: Check Database Information

Next, you will need the database name, username, and password to back up the database. This information can be found in the wp-config.php file or from your hosting provider’s management console.

Steps:

Find the wp-config.php file
Navigate to the directory where WordPress is installed and find the wp-config.php file. For example, use the following command:

cd /home/users/0/username/web/

Open the wp-config.php file
Next, open the wp-config.php file to check the database information.

nano wp-config.php

Check the database information
In the wp-config.php file, look for the following lines and note them down:

• DB_NAME: The name of the database
• DB_USER: The database username
• DB_PASSWORD: The database password
• DB_HOST: The database host (usually localhost)

Example:

define('DB_NAME', 'your_database_name');
define('DB_USER', 'your_database_user');
define('DB_PASSWORD', 'your_password');
define('DB_HOST', 'localhost');

Now, you have all the necessary information to connect to the database.

Step 3: Verify Database Connection

Next, verify that you can connect to the database. Use the MySQL client and run the following command to log in:

mysql -u your_database_user -p

You will be prompted for the DB_PASSWORD specified in the wp-config.php file. Once you enter the correct password, the connection is established, and you are ready to proceed.

3. Backup Command: Using mysqldump

After confirming the database information, you can proceed to back up the database using the mysqldump command.

Step 1: Basic mysqldump Command

The following command will back up the entire database into a single file.

General Format:

mysqldump -u [username] -p [database_name] > [backup_file_name].sql

Basic Command:

mysqldump -u your_database_user -p your_database_name > backup.sql

Explanation:

• mysqldump: The command used to dump (export) the database.
• -u your_database_user: The username for the database (as seen in wp-config.php).
• -p: Prompts for the password.
• your_database_name: The name of the database to back up.
• > backup.sql: The file where the backup will be saved. In this example, it’s saved as backup.sql.

Step 2: Avoiding Errors with –no-tablespaces Option

In some cases, errors may occur during the backup process. The next step will address how to handle those situations.

mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces

This error occurs due to insufficient privileges related to tablespaces. For many beginners, granting these privileges might be difficult, but using the –no-tablespaces option provides a simple solution. This option excludes tablespace information from the backup, preventing the error.

Modified Command:

mysqldump -u your_database_user -p your_database_name --no-tablespaces > backup.sql

By using this command, you can avoid the error while successfully backing up the entire WordPress database.

Step 3: Enter the Password

When you run the mysqldump command, you will be prompted for the database password. Enter the DB_PASSWORD found in wp-config.php. Alternatively, you may be able to confirm it in the hosting provider’s management panel.

Example:

Enter password:

After entering the password and pressing Enter, the backup will begin.

Step 4: Verify the Backup File

Once the backup is complete, a file named backup.sql should be created in the directory where you ran the command. This file contains the entire WordPress database.

Command to verify the file:

ls -lh backup.sql

This will show the size and creation time of the backup.sql file.

Reason to Use the -h Option to Specify the Host

Usually, the database server is on the same server as the web server, in which case the host is localhost. However, if the database is on a different server, you will need to specify the address of that database server.

In this case, you would use the -h option.

Example: Connecting to a Database by Specifying the Host

For example, use the following command to connect to a database hosted on a different server:

mysqldump -h 123.123.123.123 -u your_database_user -p your_database_name > backup.sql

Even when using a hosting service, you may need to specify the host. For instance, the command might look like this:

mysqldump -h example.lan -u testuser -p test-data > backup.sql

Explanation of Options:

• -h 123.123.123.123: This specifies the IP address or hostname of the database server. In this case, it connects to the database at IP address 123.123.123.123.
• -u your_database_user: Specifies the database username.
• -p: Prompts for the password.
• your_database_name: Specifies the name of the database you want to back up.

When You Need to Specify the Host

There are certain cases where you need to specify the host:

• When the database is hosted on a different server
  For example, if the web server and database server are on different machines, you must specify the database server’s IP address or hostname.
• When connecting to an external database in a cloud environment
  When connecting to a cloud-hosted database, you also need to specify the host. Usually, the database service will provide the hostname or IP address for the connection.

Difference Between localhost and Specifying the Host with -h

• localhost
  If the web server and database server are on the same machine, use localhost as the hostname. In this case, the -h option is not needed.
• Using -h to specify the host
  When the database is hosted on a different server, or when you need to connect via an IP address or hostname, use the -h option.

Using .cnf and Scripts for Regular Backups is Highly Efficient!

To perform regular database backups, the combination of a .cnf file and a script is highly efficient. This method supports multiple databases, making it especially useful if you’re managing multiple sites or applications.

Additionally, by integrating tools like rsync, you can streamline the backup process by synchronizing only the necessary data without duplicating files. This helps reduce unnecessary backup costs.

1. What is a .cnf File?

A .cnf file is a configuration file used to access databases such as MySQL. By storing the username and password in this file, you can avoid entering the password manually each time you run a command.

Why is it convenient?

Normally, you have to input the username and password every time you back up a database or connect to MySQL. However, with a .cnf file, this information is saved in advance, making it easier to run commands.

Example of Convenient Use:

• When it’s troublesome to manually enter the password every time.
• When you want to automate backups using scripts.

2. What is a Script?

A script is a small program that automates the execution of specific commands. In this example, the script is used to automatically execute database backup commands.

Why is a Script Necessary?

Manually entering commands every time is time-consuming. With a script, pre-written commands are executed automatically, making database backups easier. Additionally, you can set the script to run at regular intervals.

Example of Convenient Use:

• When you want automatic backups at a set time every day.
• When you want the system to back up the database without manual operation.

3. How to Use It

Step 1: Prepare the .cnf File

First, create a .cnf file and configure the database connection information (username and password). This file will be used by the script to access the database.

[client_db1]
user=LAA1559852
password=12345678

[client_db2]
user=LAA1559852
password=87654321

[client_db3]
user=LAA1559852
password=abcdefgh

[client_db4]
user=LAA1559852
password=testpass

Step 2: Run the Script

Next, run the script. The script contains the commands to back up the database. When you execute this script, it will use the pre-configured .cnf file to perform the backup smoothly.

#!/bin/bash

# Backup for Database 1
mysqldump --defaults-file=~/web/dbbk/.my.cnf --defaults-group-suffix=_db1 -h mysql304.phy.lolipop.lan --no-tablespaces LAA1559852-sdata > /home/users/0/mods.jp-minokamo/web/dbbk/sdata.sql

# Backup for Database 2
mysqldump --defaults-file=~/web/dbbk/.my.cnf --defaults-group-suffix=_db2 -h mysql308.phy.lolipop.lan --no-tablespaces LAA1559852-tokyo > /home/users/0/mods.jp-minokamo/web/dbbk/tokyo.sql

# Backup for Database 3
mysqldump --defaults-file=~/web/dbbk/.my.cnf --defaults-group-suffix=_db3 -h mysql311.phy.lolipop.lan --no-tablespaces LAA1559852-wpen > /home/users/0/mods.jp-minokamo/web/dbbk/wpen.sql

# Backup for Database 4
mysqldump --defaults-file=~/web/dbbk/.my.cnf --defaults-group-suffix=_db4 -h mysql310.phy.lolipop.lan --no-tablespaces LAA1559852-xyz > /home/users/0/mods.jp-minokamo/web/dbbk/xyz.sql

4. About Security

Since .cnf files and scripts contain database connection information, special care must be taken to ensure their security. To prevent unauthorized access to these files, the following measures are crucial.

Security Measures:

• Set Permissions: Restrict file permissions so that other users cannot view or modify the contents.
• Restrict Web Access: Protect these files from being accessed via the web by using an .htaccess file.

<Files "bkdb.sh">
    Order allow,deny
    Deny from all
</Files>

<Files ".my.cnf">
    Order allow,deny
    Deny from all
</Files>

<FilesMatch "\.sql$">
    Order allow,deny
    Deny from all
</FilesMatch>

5. What are the Benefits?

Using .cnf files and scripts offers several benefits:

• Efficiency: Tasks that used to be performed manually are now automated, saving time and effort.
• Error Prevention: Eliminates mistakes from manual input, ensuring stable backups.
• Enhanced Security: Proper configuration prevents passwords from being displayed in the command line, improving security.

A .cnf file is a configuration file that allows automatic database connections. Using scripts, you can automate backup processes and schedule regular backups. By implementing proper security measures, you can safely and efficiently back up your database.

In some cases, cron commands may not be available on certain servers. However, some servers allow cron settings to be configured via a browser. Once this script is registered, data can be automatically synchronized to your PC or cloud, greatly reducing the effort required for backups.