Website Migration-003 MySQL Migration for WordPress
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.