Migrate MySQL to MariaDB
Here is how I've migrated one WordPress instance from MySQL to MariaDB at Ubuntu Server 22.04 on LXC on Ubuntu 22.04 VPS on DigitalOcean.
Backup the Existing Databases and Settings
In this case I need to export just one data base. This is wp_example_com.
sudo mysqldump --all-databases > ~/tmp/all_data_bases_master_data.sql # Export all DBs just in case
sudo mysqldump --databases 'wp_example_com' > wp_example_com.sql # Export the target DB also create a copy
sudo cp -R /etc/mysql ~/tmp/ # Create a copy of the existing settings
--master-datais deprecated in MariaDB so it is not used within the dump command.
Remove the Existing MySQL Server and Client
sudo systemctl stop mysql
sudo apt update
sudo apt remove mysql-client
sudo apt remove mysql-client-8.0
sudo apt remove mysql-client-core-8.0
sudo apt remove mysql-server
sudo apt remove mysql-server-8.0
sudo apt remove mysql-server-core-8.0
sudo apt remove mysql-common
sudo apt autoremove
sudo apt autoclean
sudo deluser mysql
sudo rm -rf /var/lib/mysql* /etc/mysql
Install MariaDB Server and Client
sudo apt install mariadb-server mariadb-client mariadb-common automysqlbackup
sudo mysql_secure_installation # switch to socket authentication
Try to login as root or use the mysqladmin command.
sudo mysql
mysqladmin --print-defaults
Import the Databases into MariaDB
In this step the things actually didn't went fluently as I've expected, so here is what I've finally done: create the database and the relevant user within the MariaDB's CLI. Solve the ERROR 1273 (HY000) at line 22: Unknown collation: 'utf8mb4_0900_ai_ci' and finally import the database.
sudo mysql
CREATE DATABASE wp_example_com;
CREATE OR REPLACE USER 'wp_example_com_user'@'localhost' IDENTIFIED BY 'strong-passwd';
GRANT ALL PRIVILEGES ON wp_example_com.* TO 'wp_example_com_user'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SHOW DATABASES;
exit
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' wp_example_com.sql
sudo mysql 'wp_example_com' < wp_example_com.sql
Conclusion
The migration was not difficult process and the time I've writing this article was much longer than the actual executive of the migration steps took. My first impression is that, the WordPress initial instance response much faster. After the DB migration I've updated WordPress to version 6.0 and also about 20 plugins – everything went pretty well.
References
- HEVO: How to Migrate Data from MySQL to MariaDB?
- DigitalOcean: How To Install MariaDB on Ubuntu 22.04
- DB Administration: MySQL to MariaDB: unknown collation utf8mb4_0900_ai_ci
- Stack Overflow: Unknown collation: 'utf8mb4_unicode_520_ci'
MariaDB vs. MySQL for Enterprises
