22nd Dec 2020

Difference between MySQL vs MariaDB: Detailed Comparison

Web

Written By, Akshay Shah

HISTORY

To understand the difference between MySQL and MariaDB we need to understand MySQL and MariaDB first.

  • MySQL: MySQL is an open-source Relational Database Management System (RDBMS). MySQL was developed by the Swedish company, MySQL AB on 23 May 1995 and acquired by Sun Microsystem in 2008. After 2 years, the Sun Microsystem was acquired by Oracle Corporation in 2010. MySQL has a Dual License Policy which is GPLv2 and Proprietary Licenses.
  • MariaDB: MariaDB is also an open-source Relational Database Management System (RDBMS). was developed by MariaDB Foundation in 2009 as a Drop-in replacement. It has only a single license GNU General Public License which is free and open-source.

AUTHENTICATION

Database authentication is a process of confirming that a user who is trying to access is authorized, for that purpose MySQL and MariaDB both provide a different mechanism.

  • MySQL: MySQL provides a Superuser Account at root level ‘root’@’localhost’. Single authentication method per user.
  • MariaDB: Provides multiple authentication methods per account. mysql_nativ_password and unix_socket auth plugin. Unix_socket matches your OS uid with a MySQL user. mysql_nativ_password auth creates an “INVALID” password change with SET PASSWORD().

STORAGE ENGINES

The Storage Engines is an underlying software component for DBMS that is used to create, read, update, and delete CRUD data from a database.

DB EnginesMySQL 8MariaDB 10Specification
InnoDB/XtraDBInnoDB is the standard storage subsystem of MySQL (version 5.5 onwards). Up until version 10.1, MariaDB availed of InnoDB’s fork – XtraDB – as the standard database engine. Ever since the release of version 10.2, InnoDB is also used as the only storage subsystem of MariaDB. It offers transaction-oriented read and writing access.
MyISAMMyISAM – the standard storage subsystem of old MySQL versions – offers quick access to database tables and indices.
MEMORYMEMORY is a storage subsystem for temporary data. As a table definitions remain on the hard drive, data is retained on the working memory. MEMORY is suited for in-memory databases with quick access times and low latency.
CSVThis engine saves data as data fields in CSV format are separated by commas.
ArchiveThis database engine is designed for long-term storage of large amounts of data. It offers various compression algorithms enabling space-saving data retention.
BLACKHOLEBLACKHOLE serves as an inspector of SQL Statements. Instead of saving data, BLOCKHOLE simply logs any SQL syntax elements.
MergeMerge, as its name already suggests, facilitates the merging of numerous MyISAM tables with identical structures into one, joint table.
Federated/FederatedXFederated lets MySQL access tables from other servers via remote configuration. MariaDB avails of its fork: FederatedX.
ColumnStoreColumnStore is all about transferring InfiniDB, which allows for column-based processing of petabyte-sized data.
AriaSince 2007, Aria has grown into a crash-proof alternative to MyISAM.
CassandraCassandra offers MariaDB users a database engine managing large, structured sets of data. This software follows a non-relational approach (NoSQL) and is designed to provide high availability and safety against any failures (i.e. without any single point of failure).
CONNECTThe CONNECT storage engine facilitates local and remote access to various external data sources such as Dbase, CSV, DOS, FMT, and XML.
MroongaMroonga is a column-based database engine that offers a full-text search in diverse languages including Chinese, Japanese, and Korean.
MyRocksMyRocks has been created for quick, low-latency data storage and offers a distinctly improved compression than that of InnoDB.
OQGRAPHThe open query computation engine GRAPH (OQGRAPH for short) processes data from hierarchical database structures into complex graphs.
SequenceThis storage engine lets its users generate ascending and descending numerical sequences of positive integers including initial and end values as well as increments.
SphinxSESphinx Storage Engine (SphinxSE for short) is an alternative option to the integrated full-text searches offered by MariaDB. This is made possible through Sphinx’s search daemon search.
SpiderSpider is a database engine with an integrated sharding function (partitioning). Spider supports database partitions and eXtended Architecture (XA). In this way, various resources can interact with the same transaction. Tables in different database systems are therefore treated as if they belonged to the same system.
TokuDBTokuDB is used to process large amounts of data and is therefore intended for the so-called big data sets.

CLUSTERS

When you have a website with heavy traffic, the number of requests that reach the database is very high and the speed of the website depends on how fast the server can get results from the database. At the same time, the database server has to write data in the tables too. Thus, it has to do simultaneous operations of reading/write. That’s where the clustering of database servers comes into the picture. In database clustering, there is a group of servers to handle the workload more than a single server. Thus, it can provide data redundancy, load balancing features, etc. Also, this makes the web application very fast.

  • MySQL
    • InnoDB cluster which consists of:
      • Group Replication (available as a plugin)
      • MySQL Shell
      • MySQL Router
  • MariaDB
    • MariaDB Galera Cluster:
      • Galera 4 (available in a separate package)

ROUTING AND PROXY

A database proxy is a software that handles connectivity between two sides. The proxy is a middle layer between a database and an application. The application connects to a proxy, which then forwards connections into the database. proxies make database clusters appear like single databases by hiding the “behind-the-scenes-plumbing” from the application.

The Database Router is a middleware that provides transparent routing between applications and servers. It was used for providing high availability and scalability by effectively routing database traffic to appropriate backend Servers.

  • MySQL
    • MySQL Router (GPLv2) [CE/EE]
    • 3rd Party ProxySQL
  • MariaDB
    • maxscale (2.X versions are using the BSL license)

SECURITY AND ENCRYPTION

  • MySQL:MySQL provides various mechanisms for data security and encryption out-of-box.
    • (Data-at-Rest) Encryption
      • MySQL System Tablespace Encryption
      • General Tablespace Encryption
      • Undo log
      • Redo log
      • Binary and relay log encryption
      • Audit log
      • Keyring
        • Keyring_file
        • Keyring_encrypted_file [E]
        • Keyring_okv [E]
        • Keyring_aws [E]
        • HashiCorp Vault Keyring [E]
    • Data Masking
      • MySQL Enterprise Data Masking and De-Identification [EE only].
      • 3rd Party
        • Inexpensive Data Masking for MySQL with ProxySQL [CE].
  • MariaDB:
    • Transparent Data Encryption (TDE)
      • Everything including all tables
      • Individual tables
      • Everything, excluding individual tables
    • Key Management and Encryption Plugin
      • Data-at-rest with Encryption Key Management
      • File Key Management
      • AWS Key Management
      • Eperi Key Management
      • Plugin API
    • Data Masking
      • Data Masking by using MaxScale Proxy

COMPATIBILITY

Until MariaDB 5.5, MariaDB was released as drop-in-replacement for the equivalent MySQL version, with some limitations. From MariaDB 10.0, it is usually still very easy to upgrade from MySQL.

  • MariaDB’s data files are generally binary compatible with those from the equivalent MySQL version.
    • All filenames and paths are generally the same.
    • Data and table definition files (.frm) files are binary compatible.
  • MariaDB’s client protocol is binary compatible with MySQL’s client protocol.
    • All client APIs and structs are identical.
    • All ports and sockets are generally the same.
    • All MySQL connectors (PHP, Perl, Python, Java, .NET, MyODBC, Ruby, MySQL C connector, etc) work unchanged with MariaDB.
    • There are some installation issues with PHP5 that you should be aware of (a bug in how the old PHP5 client checks library compatibility).

NOTE: Check here for more on the incompatibility between currently supported MariaDB versions.

BACKUP AND RECOVERY

Database backup and recovery is a software that duplicates the database information and data is created and stored in a backup server just to be on the safe side.

  • MySQL
    • mysqldump
    • mysqlpump
    • MySQL Enterprise Backup [EE]
    • The Clone Plugin (8.0.17)
    • 3rd Party
      • Percona ExtraBackup [CE]
      • Mydumper
  • MariaDB
    • Mysqldump
    • Mariabackup
    • 3rd Party
      • Mydumper

DIFFERENCE BETWEEN MySQL 8 vs MariaDB 10.5

Releases

MySQL 5.6 (Feb 2013)MariaDB 5.1 (Feb 2010)
MySQL 5.7 (Oct 2015)MariaDB 5.2 (Nov 2010)
MySQL 8.0 (Apr 2018)MariaDB 5.3 (Feb 2012)
MariaDB 5.5 (Apr 2012)
MariaDB 10.0 (Mar 2014)
MariaDB 10.1 (Oct 2016)
MariaDB 10.2 (May 2017)
MariaDB 10.3 (May 2018)
MariaDB 10.4 (Jun 2019)
MariaDB 10.5 (Jun 2020)

Updates

RolesMySQL 8 (April 2018)MariaDB 10.0 (Mar 2014)
Check constraintsMySQL 8 (April 2018)MariaDB 10.2 (Mar 2017)
Default value expressionsMySQL 8 (April 2018)MariaDB 10.2 (Mar 2017)
Common table expressionsMySQL 8 (April 2018)MariaDB 10.2 (Mar 2017)
Window functionsMySQL 8 (April 2018)MariaDB 10.2 (Mar 2017)
Instant add columnMySQL 8 (Jul 2018) MariaDB 10.3 (May 2018)
Encrypted log filesMySQL 8 (Jan 2019)MariaDB 10.1 (Oct 2015)

Software

DatabaseMySQLMariaDB Enterprise Server
Database router/proxyMySQL Router*MariaDB MaxScale
Backup/restore toolMySQL Enterprise BackupMariaDB Enterprise Backup
MonitoringMySQL MonitorSQL Diagnostic Manager
Window functionsMySQL 8 (April 2018)MariaDB 10.2 (Mar 2017)
SQL IDEMySQL Workbench*SQLyog
*Available without enterprise subscription

Plugins

AuditingMySQL Enterprise AuditMariaDB Enterprise Audit
ClusteringMySQL Group Replication*MariaDB Enterprise Cluster
Data maskingMySQL Enterprise Data MaskingMariaDB MaxScale
Database FirewallMySQL Enterprise Firewall MariaDB MaxScale
Encryption functionsMySQL Enterprise EncryptionYes*
FederationN/AMariaDB Enterprise Federation
HashiCorp Vault pluginYesYes
PAM/LDAP authenticationMySQL Enterprise SecurityYes*
Thread poolMySQL Enterprise Thread PoolYes*
*Available without enterprise subscription

Schema

MySQL 8MariaDB 10.5
Generated Columns
Views
Invisible columns-
Sequence-
Temporal tables-

Instant Schema Changes

MySQL 8MariaDB 10.5
Add Columns(Last position)Add Columns (Any position)
Set/drop column default valueSet/drop column default value
Add enum/set optionAdd enum/set option
Rename tableRename table
-Rename column
-Drop column
-Drop column and reorder columns
-Modify column to allow NULL
-Change table auto_increment
-Drop constraint and foreign key
-Drop index

Indexes

MySQL 8MariaDB 10.5
Functional indexes-
Descending indexes-
Invisible indexes-

Queries

 MySQL 8MariaDB 10.5
UNION [ALL|DISTINCT]-
INTERSECT and EXCEPT [ALL|DISTINCT]-
INTERSECT and EXCEPT [ALL|DISTINCT]-
ROLLUP
ROLLUP with GROUPING and ORDER BY -
WITH RECURSIVE
WITH RECURSIVE CYCLE -
INSERT/REPLACE RETURNING-
Statistical aggregate functions (e.g., STDDEV)
Linear regression aggregate functions (e.g., CORR)-
Window functions
Inverse distribution window functions (e.g., MEDIAN)-

Scalability and performance

 MySQL 8MariaDB 10.5
Table/row compression
Column compression-
Partitioning
ROLLUP
Parallel query -
Query result caching (via Redis)-
Read/write splitting-
Distributed SQL-

High availability

 MySQL 8MariaDB 10.5
Multi-master clustering✔ (Group Replication)✔(Cluster)
Semi/async replication
Semi/async replication w/ automatic failover-
Connection migration -
Session restore-
Transaction replay-

Disaster recovery

 MySQL 8MariaDB 10.5
Online backups
Online partial restore
Compressed and encrypted backups
Point-in-time restore
Point-in-time rollback (i.e., Flashback) -
Delayed replicas

Security

 MySQL 8MariaDB 10.5
Encrypted tables and logs
Reload SSL/TLS context
Password expiration
Password reuse policies-
Roles and privileges
Partial revokes✔Limited to the schema (i.e., grant global, revoke schema)- (MariaDB 10.6)

Security – auditing

 MySQL 8MariaDB 10.5
JSON filter/rule definitions
Output to Syslog
-
XML/JSON audit log formats -
Compressed and encrypted audit logs-

Security – advanced protection

 MySQL 8MariaDB 10.5
Dynamic data masking-
Dynamic data obfuscation-
Database firewall
Query throttling-
Query result limiting-

Database firewall

MySQL 8MariaDB 10.5
Dynamic data maskingWhitelisting
-Blacklisting
Checks for

  • Query

Checks for

  • Missing WHERE clause

  • Statement (e.g., INSERT)

  • DDL

  • Table, column, and/or function

  • Wildcard (*)

  • Time

  • Regex

KEY DIFFERENCES

ParameterMySQLMariaDB
Storage EnginesFreeBSD
Linux
OS X
Solaris
Windows
FreeBSD
Linux
Solaris
Windows
Cache/indexesThe memory storage engine of MySQL is slower compared to that of MariaDB.
With the Memory storage engine of MariaDB, an INSERT statement can be completed 24% faster than in the standard MySQL.
Larger and Faster Connection PoolThe thread pool provided by MySQL cannot support up to 200,000 connections per time.MariaDB comes with an advanced thread pool capable of running faster and supporting up to 200,000+ connections.
Improved ReplicationMySQL's community edition allows a static number of threads to be connected. MySQL's enterprise plan comes with thread capabilities.In MariaDB, replication can be done safer and faster. Updates can also be done 2x faster compared to the traditional MySQL.
New Features/ExtensionsThe new MariaDB features are not provided in MySQL.MariaDB comes with new features and extensions including the JSON, WITH, and KILL statements.
Missing FeaturesThe Enterprise Edition of MySQL uses a proprietary code. Only users of MySQL Enterprise Edition have access to this.MariaDB lacks some of the features provided by the MySQL enterprise edition. To address this, it offers alternative open-source plugins.
Priority codeMySQL uses some proprietary code in its Enterprise Edition.
MariaDB doesn't allow access to this proprietary content and is a closed source.
Data MaskingYesNo
Dynamic columnsYesNo
MonitoringMySQL workbenchMySQL workbench
RoutingMySQL RouterMariaDB MaxScale

PROS AND CONS

  • Pros:
    • MariaDB has been optimized for performance and is much more powerful than MySQL for large data sets. Elegant migration from other database systems to MariaDB is yet another benefit.
    • Switching from MySQL to MariaDB is relatively easy and is a piece of cake for systems administrators.
    • MariaDB provides better monitoring through the introduction of microsecond precision and extended user statistics.
    • MariaDB enhances the KILL command to allow you to kill all queries from a user (KILL USER username) or to kill a query ID (KILL QUERY ID query_id). MariaDB has also switched to Perl-compatible regular expressions (PCRE), which offer more powerful and precise queries than standard MySQL regex support.
    • MariaDB has applied several query optimizations for queries connected with disk access, join operations, sub-queries, derived tables and views, execution control, and even explain statements.
    • MariaDB is a purely open-source, instead of the dual-licensing model that MySQL uses. Some plugins that are available only for MySQL Enterprise customers have equivalent open-source implementations in MariaDB.
    • MariaDB supports significantly more engines (SphinxSE, Aria, FederatedX, TokuDB, Spider, ScaleDB, etc) compared to MySQL.
    • MariaDB offers a cluster database for commercial use, which also enables multi-master replication. Anyone can use it freely and reliance on a MySQL Enterprise system is not required.
  • Cons:
    • The migration of MariaDB back to MySQL has not been possible since release 5.5.36.
    • For new releases of MariaDB, the appropriate libraries (for Debian) will not be deployed in time, which will result in a required upgrade to a newer version due to dependencies.
    • The cluster version of MariaDB is not very stable.

UPGRADE GUIDE

  • Why Migrate to MariaDB
    • First and foremost, MariaDB offers more and better storage engines. NoSQL support, provided by Cassandra, allows you to run SQL and NoSQL in a single database system. MariaDB also supports TokuDB, which can handle Big Data for large organizations and corporate users.
    • MySQL’s usual (and slow) database engines MyISAM and InnoDB have been replaced in MariaDB by Aria and XtraDB respectively. Aria offers better caching, which makes a difference when it comes to disk-intensive operations.
    • MariaDB provides better monitoring through the introduction of microsecond precision and extended user statistics.
    • The cutting-edge features of MariaDB like GIS, dynamic column support, etc, make it a better choice.
    • MariaDB follows good industry standards by releasing security announcements and upgrades at the same time and handling the pre-release secrecy and post-release transparency in a proper way.
    • UPGRADE GUIDE: You can learn here how to upgrade from MySQL to MariaDB in five easy steps.

Conclusion

Undoubtedly MariaDB is quite powerful and provides many features that are extremely useful which are not supported in MySQL. Such features make MariaDB the best choice to be used as the primary database.

Written By,

Web developer at Yudiz Solutions Pvt. Ltd