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