How to Migrate MySQL to MariaDB in 2 Methods Easily?

MariaDB is a newer alternative to MySQL that provides additional features and improved handling capabilities for complex data while being lighter than MySQL. Some organizations prefer MariaDB over MySQL because of its compatibility with MySQL, community-driven development model, and open-source nature, and switching from MySQL to MariaDB can help them work more efficiently with their data in certain cases.

Basically, we can see MariaDB as an upgrade of MySQL, which can use the same tools and drivers, so the migration from MySQL to MariaDB is so simple that one only needs to upgrade or export MySQL to MariaDB. But this comes with strings attached. We will take a detailed look at the migration and the details required.

You can check out the currently running version of MySQL using the following command: 

mysql -h localhost -e 'SHOW VARIABLES LIKE "version"G'

Method 1: Upgrade MySQL to MariaDB

You can upgrade from MySQL 5.0 or older version, the same base version like MySQL 5.5/5.6 to MariaDB 10.0, or MySQL 5.7 to MySQL 10.2 using mysql_uppgrade, which runs automatically if you use a MariaDB package.

Upgrade MySQL 5.0 or Older to MariaDB

1.     Check the incompatibilities between source MySQL and the target MariaDB.

2.     Install MariaDB on Windows/Linux/Mac.

3.     Convert the tables.

mysql_upgrade –verbose

Or convert with a specific TCP/IP port.

mysql_upgrade --host=xxx.x.x.x --port=xxxx --protocol=tcp

Connect with a socket.

mysql_upgrade -- socket=xxx.x.x.x -- protocol=socket

Upgrade MySQL to MariaDB on CentOS 6/7

1.     Schedule accurate downtime in the maintenance windows.

2.     Backup MariaDB files and databases, and prepare a DR plan.

Tip: Take a Full/Incremental/Log Backup and save another offsite backup copy with Vinchin Backup & Recovery easily!

3.     Log into the cPanel's web host manager>Home>Software >MySQL/MariaDB Upgrades> type mysql upgrade in the search box> click the MySQL/MariaDB Upgrades link> choose the version of MariaDB> Next.

4.     Read and check the warnings and click Continue.

5.     Wait for the loading> Unattended Upgrade> Continue.

6.     Waif for the upgrade process.

Upgrade MySQL 5.1-5.5 to MariaDB 5.5 on CentOS 6

Upgrade MySQL 5.1-5.5 to MariaDB first, then upgrade to MariaDB 10.0.

1.     Schedule an accurate maintenance window for the upgrade.

2.     Backup files and databases, and prepare a DR plan.

3.     Stop MySQL service.

service mysqld stop

4.     Install MariaDB 5.5 repository on CentOS 6.

cat < /etc/yum.repos.d/MariaDB.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/5.5/centos6-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

EOF

5.     Clean the cache for the yum repository.

yum clean all

6.     Remove MySQL packages.

yum remove mysql-server mysql

7.     Install the MariaDB server and client.

yum install MariaDB-server MariaDB-client -y

8.     Start MySQL service.

service mysql start

9.     Upgrade.

mysql_upgrade

10.  Confirm the result.

mysql -e 'SHOW VARIABLES LIKE "version"G'

Upgrade MySQL 5.1-5.5 to MariaDB 5.5 on Ubuntu 14.04

1.     Schedule accurate downtime in the maintenance windows.

2.     Backup files and databases, and prepare a DR plan.

3.     Install MariaDB 5.5 repository.

sudo apt-get install software-properties-common -y

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db

sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu trusty main'

sudo apt-get update

4.     Stop the service.

sudo service mysql stop

5.     Change the MySQL configuration file name.

sudo mv /etc/mysql/my.cnf{,.bak}

6.     Install the MariaDB server. At the same time, a dialog will appear to change the MySQL root password, either change it or leave it blank.

sudo apt-get install mariadb-server -y

7.     Show the MySQL service status.

sudo service mysql status

8.     Check if the upgrade succeeds.

sudo mysql_upgrade

Upgrade 5.5 to MariaDB 10.0 on CentOS 6

1.     Schedule accurate downtime in the maintenance windows.

2.     Backup files and databases, and prepare a DR plan.

3.     Remove all packages of MariaDB.

yum remove MariaDB-*

4.     Remove the current MariaDB repository.

rm -f /etc/yum.repos.d/MariaDB*.repo

5.     Create a yum repository for MariaDB 10.0.

cat < /etc/yum.repos.d/MariaDB.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.0/centos6-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

EOF

6.     Clean the cache.

yum clean all

7.     Install the MariaDB server and client.

yum install MariaDB-server MariaDB-client

8.     Start service for MySQL.

service mysql start

9.     Upgrade.

mysql_upgrade

Upgrade MySQL 5.5 to MariaDB 10.1 on Debian 9

1.     Configure innodb_fast_shutdown to 0 to make a backup and ensure all data is written to the InnoDB files.

2.     Turn off MySQL 5.5.

3.     Take a database backup and store a backup copy on another machine or media.

4.     Upgrade Debian 8 to 9. In the meantime, mysql_update upgrades the MySQL permission tables and checks and marks the table compatibility with MariaDB automatically.

5.     Change my.cnf for more features and restart MySQL.

sudo service mysql restart

Or sudo service mariadb restart

Upgrade MySQL 5.7 to MariaDB 10.2 on Ubuntu 16.04

1.     Schedule accurate downtime in the maintenance windows.

2.     Backup files and databases, and prepare a DR plan.

3.     Check the local root has a password.

mysql -e 'select User,Host,Plugin,authentication_string from user where authentication_string = "" and plugin = "auth_socket";' mysql

4.     Update the MySQL user entry via the password from my.cnf.

ALTER USER 'root'@'localhost' IDENTIFIED BY '$(awk -F"[=']" 'tolower($1)~/password/{print $(NF-1)}' /root/.my.cnf)';

select User,Host,Plugin,authentication_string from user where user = 'root' and host = 'localhost';" mysql

5.     Install the repository for MariaDB 10.2.

sudo apt-get install software-properties-common -y > /dev/null

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

sudo add-apt-repository 'deb [arch=amd64,arm64,i386,ppc64el] http://ftp.osuosl.org/pub/mariadb/repo/10.2/ubuntu xenial main'

sudo apt-get update > /dev/null

6.     Stop service for MySQL.

sudo service mysql stop

7.     Rename MySQL config file.

sudo mv /etc/mysql/my.cnf{,.bak}

8.     Install the MariaDB server. Press enter in the window showing the root password cannot be changed.

sudo apt-get install mariadb-server -y

9.     See the status of MySQL service.

sudo service mysql status

10.  Upgrade.

sudo mysql_upgrade

Method 2: Export MySQL Dump and Import into MariaDB

1.     Add the option under mysqlid section for binary logging.

log-bin=mysql-bin

2.     Restart MySQL.

$ sudo service mysql restart

3.     Backup MySQL database with mysqldump.

$ mysqldump --all-databases --user=root --password --master-data > backupdb.sql

4.     Backup my.cnf optionally.

$ sudo cp /etc/mysql/my.cnf /opt/my.cnf.bak

5.     Stop MySQL service.

$ sudo service mysql stop

Or $ sudo systemctl stop mysql

6.     Remove MySQL packages and config files.

On CentOS, Fedora, or RHEL:

$ sudo yum remove mysql* mysql-server mysql-devel mysql-libs

$ sudo rm -rf /var/lib/mysql

On Debian, Ubuntu, or Mint:

$ sudo apt-get remove mysql-server mysql-client mysql-common

$ sudo apt-get autoremove

$ sudo apt-get autoclean

$ sudo deluser mysql

$ sudo rm -rf /var/lib/mysql

7.     Download and install MariaDB.

8.     Restore my.cnf backup file.

$ sudo cp /opt/my.cnf /etc/mysql/

9.     Start MariaDB.

$ sudo service mariadb start

Or $ sudo /etc/init.d/mariadb start

10.  Import/load MySQL dump file into MariaDB. Input the password.

$ mysql -u root -p < backupdb.sql

11.  Verify the import.

$ mysql -u root -p

MariaDB [(none)]> show databases;

MariaDB [(none)]> use test01;

MariaDB [test01]> select * from pet;

Backup MySQL and MariaDB Together

Before performing any kind of migration, it is always recommended to create a backup of your data to avoid losing important information in case something goes wrong during the migration process.

Vinchin Backup & Recovery is a professional backup solution for 6 databases including MySQL MariaDB, Oracle DB, SQL Server, PostgreSQL, and Postgres Pro, 12 virtualizations, NAS, Linux & Windows servers and files.

You can schedule database backups before migration automatically and deduplicate and compress your database size by 50% to save storage.

You can also select from Full, Differential, Incremental, and Log backups varying with databases as needed via encrypted transmission for security.

Also do not forget to encrypt the backups in the Vinchin server with AES-256 standard and send an offsite backup copy for data recovery in emergencies.

Simplified Database Backup

1.     Download the 60-day free trial of the enterprise edition.

2.     Go to Physical Backup> Database Backup> Backup and select an installed backup agent and the backup source.

3.     Select a backup node and storage on the node for the job. You can select the NAS storage if you have added it to the Vinchin solution.

4.     Set up any desired backup strategies to streamline the process.

5.     Check and submit the job. It will run based on the schedules. Or under Options> Start full or start incr. as selected to run it immediately.

Takeaway

MySQL database users can either upgrade or import to MariaDB depending on the specific MySQL versions. See the opposite MariaDB to MySQL migration. And it always matters to ensure that you have a reliable backup of data before migration to a new system or platform. Use Vinchin Backup & Recovery which provides users with easy operations, various options, affordable prices, and a central management console.