MariaDB MariaDB MySQL vs Oracle MySQL vs Percona MySQL

Centmin Mod currently uses MariaDB MySQL during initial installs. Right now there are no automated options for switching or using by default alternative MySQL versions like Oracle MySQL or Percona MySQL. However, both Oracle MySQL and Percona MySQL are on the long term to do list of supported MySQL database options eventually. See Centmin Mod Development dashboard on Centmin Mod Github repository here. But for now only MariaDB MySQL is supported.

The reason why MariaDB MySQL is only default option right now is due to the fact that Centmin Mod LEMP stack installer does auto optimisation of the default MariaDB MySQL server options and settings based on detected server environment's resources available like cpu, memory and disk I/O measured performance and then auto adjusts those settings in /etc/my.cnf. No other control panel of LEMP or LAMP stack solution out there as far as I know auto tune MySQL settings out of the box like this so they have it easier to support various MySQL versions as they are mainly just default options used and leave optimisation to the end user to do. Because Percona and Oracle MySQL server have slight differences in variable names and functions or have additional variable options related to optimal performance it makes tracking the development and changes for all three versions of MariaDB, Oracle MySQL and Percona very time consuming for the purposes for auto tuned optimisation. While I do track Percona MySQL most of the time, I don't track Oracle MySQL as frequent as I do MariaDB MySQL server. So for now MariaDB is the ideal default MySQL server to install.

MariaDB MySQL vs Oracle MySQL

MariaDB MySQL is an almost drop-in replacement for Oracle MySQL so most management and features are the same. You can see the below links for side by side similarities and differences between MariaDB and Oracle MySQL.

• MariaDB versus MySQL - Features
• MariaDB versus MySQL - Compatibility
  
• Incompatibilities and Feature Differences Between MariaDB 10.2 and MySQL 5.7
  
• Differences Between the MySQL and MariaDB Query Optimizer
  
• Optimizer Feature Comparison Matrix
  
• MySQL vs. MariaDB: Reality Check
• Comparing Oracle MySQL, Percona Server and MariaDB
• MariaDB vs MySQL. A Comparative
• MySQL vs MariaDB vs Percona Server: Security Features Comparison

From above link it may not be obvious but there's some differences between Oracle MySQL 5.7 and MariaDB that some other control panels are dealing with like cPanel/WHM in that MariaDB 10.1 and below do not support Full JSON support, Oracle MySQL X Plugin or Delayed MySQL replication. MariaDB 10.2 and newer has added Full JSON support (though data storage differs with MariaDB True Text vs MySQL's binary format) and as at MariaDB 10.2.3 added Delayed MySQL replication support. However, MariaDB doesn't support MySQL X Plugin. Centmin Mod 123.08stable stable uses MariaDB 10.0.x branch while 123.09beta01 uses MariaDB 10.3.x branch with privately testing of MariaDB 10.4.x upgrade compatibility right now.

Another difference though is in their respective supported and offered clustering options. So if you require MySQL Cluster, then MariaDB isn't an option. Just note for any clustering setup you would require a minimum of at least 3-5 MySQL servers to run.

• MySQL Cluster (NDB Cluster) is available in Oracle MySQL Cluster & Percona Cluster
• However, MariaDB only offers MariaDB Galera Cluster which is different from MySQL (NDB Cluster)

For differences in MariaDB vs MySQL system variables/options see below link:

• System Variable Differences between MariaDB and MySQL

Relevant Manuals

• MariaDB Documentation
• MySQL :: MySQL 5.7 Reference Manual
• MySQL :: MySQL 5.6 Reference Manual
• Percona Server 5.7 - Documentation

Migrating From Oracle MySQL To MariaDB MySQL

Safest way to import data into a Centmin Mod MariaDB MySQL server instance is to do a mysqldump backup of MySQL databases to a sql file and import that sql file into Centmin Mod MariaDB MySQL server instance. Which essentially involves the 2 commands below run in SSH session.

backup - run on source server

Code (Text):

mysqldump -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -u mysqlusername -p mysqldatabasename > /path/to/mysqldatabasename_backup_date.sql

restore - run on target destination server after you transfer the sql file from source to destination server

Code (Text):

mysql -u mysqlusername -p mysqldatabasename < /path/to/mysqldatabasename_backup_date.sql

---

These mysqldump steps might have already been part of your general data migration guide into Centmin Mod LEMP stack in the first place with outlined guides like:

• Centmin Mod Site Data Migration Guide
• How To Transfer cPanel/WHM Sites To Centmin Mod LEMP

General notes for migrating from Oracle MySQL to MariaDB MySQL can be found at links below:

• Migrating to MariaDB

 

MySQL 8.0 Note

Just a note for MySQL 8, be aware that even minor version rollback/downgrades are not supported. Which means it's more important to have regular full all databases on server backups as only way to downgrade even MySQL 8 minor versions i.e. 8.0.18 to 8.0.17 is do to a full all databases on server restore from pre-upgrade backups !

• MySQL :: MySQL 8.0 Reference Manual :: 2.12 Downgrading MySQL
• MySQL 8 Minor Version Upgrades Are ONE-WAY Only - Percona Database Performance Blog

Centmin Mod 123.09beta01 and newer officially only support MariaDB MySQL servers from 5.5, 10.1, 10.2, 10.3 and 10.4. With planned support for Oracle MySQL 8 and Percona MySQL 8 way into the future as outlined in dev dashboard at centminmod/centminmod.