Website Migration-003 MySQL Migration for WordPress

4 weeks ago

Ruian Ding

4 minutes

In this post, I’ll walk through the process of migrating my WordPress database from a Windows server to a Linux server, along with setting up MySQL on the Linux server.

1. Backing Up the Existing Database on Windows

Before migrating, I needed to backup the WordPress database from the Windows server.

Exporting MySQL Database

I used the MySQL command line to export the database. First, navigate to the MySQL installation directory:

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

Then, run the following command to export the database:

.\mysqldump.exe -u ruiand -p ruianweb > C:\ruianweb_backup.sql

Transferring the Backup File to the Linux Server

Using SCP, I transferred the backup file from the Windows server to the Linux server:

scp C:\ruianweb_backup.sql root@{ipaddress}:/data/sqlbackup/

2. Configuring MySQL on the Linux Server

Logging into MySQL

After installing MySQL on the Linux server, I logged in using the root account:

sudo mysql -u root -p

Since I hadn’t set a password for the root user earlier, I configured it immediately:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
FLUSH PRIVILEGES;

Creating a New Database

I created a new database for WordPress:

CREATE DATABASE ruianweb;

Creating a User and Granting Permissions

Next, I created the ruiand user (to match the WordPress configuration) and granted all privileges on the new database:

CREATE USER 'ruiand'@'localhost' IDENTIFIED BY '<my_password>';
GRANT ALL PRIVILEGES ON ruianweb.* TO 'ruiand'@'localhost';
FLUSH PRIVILEGES;

Exiting MySQL

After the setup was done, I exited the MySQL console:

EXIT;

3. Importing the Database Backup

Importing the Database

I tried importing the database backup on the Linux server:

mysql -u ruiand -p ruianweb < /data/sqlbackup/ruianweb_backup.sql

Unfortunately, I encountered the following error:

ERROR: ASCII '\0' appeared in the statement...

To fix this, I added the --binary-mode=1 option:

mysql --binary-mode=1 -u ruiand -p ruianweb < /data/sqlbackup/ruianweb_backup.sql

However, I still faced the same issue. Suspecting a character set problem (as my blog contains Chinese text), I re-exported the database using UTF-8 encoding:

mysqldump --default-character-set=utf8 -u ruiand -p ruianweb > ruianweb_backup.sql

Despite this, the error persisted. Further investigation revealed that the backup file was in UTF-16 encoding. I used iconv to convert it to UTF-8:

iconv -f UTF-16 -t UTF-8 /data/sqlbackup/ruianweb_backup.sql -o /data/sqlbackup/ruianweb_backup_utf8.sql

But the import still failed with syntax errors. At this point, I suspected the issue was due to cross-platform differences.

4. Using MySQL Workbench for Backup

Instead of using mysqldump, I switched to MySQL Workbench for a graphical backup:

Backing Up the Database

a. Open MySQL Workbench and connect to the database.

b. Go to Data Export, select the ruianweb database, and export it as an SQL file.

c. Ensure you select Export to self-contained file.

Transferring and Importing

I then used SCP to transfer the new backup file to the Linux server and successfully imported it without any errors.

5. Verifying the Database Connection

After importing the database, I verified that WordPress could connect to the new MySQL database.

Updating wp-config.php

I ensured that the database settings in WordPress were correctly configured:

define( 'DB_NAME', 'ruianweb' );
define( 'DB_USER', 'ruiand' );
define( 'DB_PASSWORD', '{password}' );
define( 'DB_HOST', 'localhost' );

Restarting Apache and Testing

Finally, I restarted Apache:

sudo systemctl restart apache2

I then accessed https://www.ruianding.com/blog to confirm that everything was running smoothly.

This post covered the steps I took to successfully migrate my WordPress database to the Linux server, along with troubleshooting key issues during the migration process.

#MIGRATION