← Other topics

Migrate from MySQL to MariaDB (Ubuntu)

Video Notes

Preface

MariaDB is a forked/improved version of MySQL and in most cases it can easily be swapped in place of MySQL.

The process might become more complex, though, if some of the following conditions apply:

  • You’re running a notably older version of MySQL
  • You’ve made a lot of customizations to your MySQL install
  • You’re using edge case features that are incompatible between MySQL and MariaDB

If any of the above situations apply, I suggest studying the following articles before doing an upgrade:

Backup MySQL databases

Step one of migrating from MySQL to MariaDB is making a backup of any existing MySQL databases you have on your server. The following command will accomplish that, creating a SQL dump of your database information to a file called backup.sql in your home directory:

> sudo mysqldump -u root --all-databases > ~/backup.sql

Uninstall MySQL

With backups complete, you can delete MySQL by first stopping the service:

> sudo systemctl stop mysql

And remove all MySQL-related packages using apt (command line utility for managing packages on Linux systems):

> sudo apt remove -y --purge mysql-*

To clean things up, we’ll delete MySQL-related directories:

> sudo rm -rf /usr/bin/mysql /var/lib/mysql /etc/mysql /var/log/mysql

And finally, we’ll remove any packages that were automatically installed because they were dependencies of MySQL:

> sudo apt -y autoremove

MySQL should now be uninstalled.

Install MariaDB

To install MariaDB, first run the following command to update the package index on your server:

> sudo apt -y update

Then install MariaDB:

> sudo apt install -y mariadb-server

The MariaDB service should automatically start upon installation, but you can confirm it’s running with the following command:

> sudo systemctl status mysql

Type q to exit the status prompt.

If for some reason MariaDB is not running, you can start it with:

> sudo systemctl start mysql

After installing MariaDB its suggested you secure it with the command:

> sudo mysql_secure_installation

Here are a summary of responses for each prompt it presents you with:

  • When it asks Enter current password for root (enter for none): leave it blank and hit enter
  • When it asks you if you want to...
    • Change the root password?, enter No.
    • Remove anonymous users?, enter Yes.
    • Disallow root login remotely?, enter Yes.
    • Remove test database and access to it?, enter Yes.
    • Reload privilege tables now?, enter Yes.

Import backup

With MariaDB set up, you can import your MySQL backup:

> sudo mysql -u root < ~/backup.sql

In my tests, importing the backup in its current state produced this error:

ERROR 1273 (HY000) at line 24: Unknown collation: 'utf8mb4_0900_ai_ci'

This happens because with my MySQL install, the default collation was utf8mb4_0900_ai_ci, but in MariaDB the default collation is utf8mb4_unicode_ci (ref). To accommodate this, the following command will update your backup file with the appropriate collation:

> sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' ~/backup.sql > ~/backup.sql.tmp && mv ~/backup.sql.tmp ~/backup.sql

That will resolve the collation error, but if you attempt to import your backup again, you’ll next see this error:

ERROR 1050 (42S01) at line 925: Table 'user' already exists

The reason for this has to do with a difference in how MariaDB and MySQL handle a system-specific user table. Based on the documentation found here, we can fix this error by adding the following two lines to the top of our backup.sql file:

DROP TABLE IF EXISTS `mysql`.`global_priv`;
DROP VIEW IF EXISTS `mysql`.`user`;

With these changes in place, you should now be able to import your backup without any errors:

> sudo mysql -u root < ~/backup.sql

Since our backup pulled in core system databases, it’s a good idea to restart the database service at this point:

> sudo systemctl restart mariadb

Check the results...

With the migration complete, you can check your databases by entering the MySQL/MariaDB command prompt:

> sudo mysql -u root

Listing your databases:e

SHOW DATABASES;

Listing tables in a database:

USE database_name;
SHOW TABLES;

Viewing the data in a table:

SELECT * FROM table_name\G
If this info helped you out, toss a coin in the tip jar?
← Other topics