What’s the Difference Between MariaDB and MySQL?
MySQL and MariaDB are both open-source database technologies. You can use them to store data in a tabular format with rows and columns. MySQL is the most widely adopted open-source database. And it’s the primary relational database for many popular websites, applications, and commercial products. MariaDB is a modified version of MySQL. MariaDB was made by MySQL’s original development team due to licensing and distribution concerns after MySQL was acquired by Oracle Corporation. Since the acquisition, MySQL and MariaDB have evolved differently. However, MariaDB adopts MySQL’s data and table definition files and also uses identical client protocols, client APIs, ports, and sockets. This is intended to let MySQL users switch to MariaDB hassle-free.
What are the similarities between MariaDB and MySQL?
MySQL is a relational database management system that was released in 1995. In 2009, developers released MariaDB as a code fork of MySQL 5.1.38.
As MariaDB forked from MySQL, there are many similarities in the two relational database management systems. For instance, MariaDB has kept MySQL’s structure, naming conventions, and data definition files. Additionally, it supports all MySQL connectors, connections, and ports. Your MySQL client package works unchanged with MariaDB.
Next we give some more similarities.
ACID compliance
Atomicity, consistency, isolation, and durability (ACID) are the four core principles that ensure the reliability of database transactions. Both MySQL and MariaDB uphold these principles. By complying with ACID, both databases maintain the accuracy and integrity of data.
SQL compatibility
MySQL and MariaDB are relational databases that organize data into tables. MariaDB and MySQL both use SQL to manage and query data. You can use many of the same commands across these systems.
Open-source software
As open-source relational database management systems, MySQL and MariaDB are both collaborative efforts that have come out of a community of developers. Their source code is available to the public.
MySQL database has one fully open-source version, released under the General Public License (GPL). It also has one paid enterprise version that comes with additional features and support. MariaDB is fully open source on GitHub.
Security
MySQL and MariaDB offer similar base security features. They offer encryption, access control mechanisms, user authentication and authorization, and SSL/TLS support. They also allow fine-grained access control so you can give different permission levels to different users.
Key differences: MariaDB vs. MySQL
Since forking from MySQL, MariaDB has continued to expand its capabilities. Some of these changes have led to core functionality, capability, and performance differences.
Here are the significant differences between MySQL and MariaDB.
JSON data handling
MariaDB and MySQL support the retrieving and storing of JSON data. However, they store JSON reports in different ways. MariaDB stores JSON reports in strings, while MySQL stores them as binary objects.
MySQL and MariaDB also do not both support all JSON functions. MariaDB supports JSON_QUERY and JSON_EXISTS, while MySQL doesn’t. Similarly, MySQL supports the native JSON data type JSON_TABLE that MariaDB does not.
However, MariaDB has only supported JSON data types since version 10.2. The JSON data type that MariaDB uses is an alias for LONGTEXT.
Functionality
Since their development pathways have been different, MariaDB and MySQL now offer slightly different functionality.
For example, MySQL provides dynamic columns that let you define multiple data values in a single column and modify the column with functions. Dynamic columns allow data masking to protect sensitive information.
In contrast, MariaDB supports invisible columns in database views. Invisible columns are not listed when a user performs a SELECT statement or asks for a value in an INSERT statement.
User authentication
MySQL has the validate_password component, which you can use to increase password security.
MariaDB does not have this by default but does offer three validator plugins. You can use these password validator plugins to add more protection around passwords. In version 10.4, MariaDB introduced the ed25519 authentication plugin to replace its previously used SHA-1 authentication. This helps to authenticate users and store passwords securely.
Encryption
MySQL and MariaDB both encrypt data at rest and in transit.
MySQL allows admin users to configure and encrypt redo and undo logs, while it doesn’t encrypt temporary tablespace or binary logs.
On the other hand, MariaDB supports binary log encryption and temporary table encryption.
Thread pooling
Thread pooling allows a database to optimize its resources by pairing new connections with pre-existing threads. A high thread pool capacity is vital for applications that want to scale and serve thousands of users in parallel.
MariaDB includes thread pooling in its thread pool plugin, which is part of the community edition. MariaDB offers the ability to manage over 200,000 connections at once.
MySQL has a thread pool plugin on its enterprise version. However, it cannot handle as many connections as once as MariaDB.
When to use MariaDB vs. MySQL
MariaDB is more scalable and offers a higher query speed when compared to MySQL. This makes it good for managing large-sized data. You will also find more features in MariaDB that MySQL doesn’t have, like sequence storage engines and virtual columns. You can also use multiple engines in one table.
However, MySQL has been around for much longer than MariaDB. Some organizations prefer the enterprise support that MySQL offers.
Next we give some considerations when choosing between both the databases.
Storage engines
In database management systems, storage engines are a component that helps to retrieve, update, and store data within the database. The storage engine you want to work with might influence your decision.
You can use multiple storage engines on both MySQL and MariaDB, but MySQL offers fewer options. Some storage engines on MySQL include InnoDB, CSV, Federated, MyISAM, Merge, and Federated.
MariaDB supports some storage engines that MySQL doesn’t, such as XtraDB, Aria, InnoDB, MariaDB ColumnStore, Memory, Cassandra, and Connect.
License
MariaDB and MySQL both have General Public License (GPL) versions. MariaDB is completely under GPL authorization, while MySQL has two available licenses—public and private.
The private MySQL enterprise edition license unlocks additional capabilities, like thread pooling, that accelerate query speed. Companies that want to distribute their MySQL software without distributing the source code under the GPL might prefer MySQL enterprise edition.
Oracle database compatibility
Oracle database compatibility is the ability of the Oracle database to work with an application built with MariaDB or MySQL databases.
If your organization already uses Oracle database products, you may prefer MySQL. Oracle database offers a specific compatibility mode for SQL syntax specific to MySQL. However, MySQL does not support PL/SQL, the application-oriented language that Oracle developed.
Since version 10.3, MariaDB has supported PL/SQL. MariaDB also provides an Oracle syntax compatibility mode for running Oracle database applications without change.
Speed and performance
For many years, MySQL outperformed MariaDB for several use cases. For example, MySQL 8.0 could handle more queries per second than the 10.3 version of MariaDB. However, MariaDB has made many improvements over recent years.
At present, MariaDB is faster in terms of performing queries and in replication tasks. You may prefer MariaDB for performance-critical workloads.
Can you switch from MySQL to MariaDB?
Yes, you can move from MySQL to MariaDB with minimal effort. MariaDB is designed to be backward compatible with MySQL. This means that applications and scripts written for MySQL should work with MariaDB without any significant changes.
Until MariaDB 5.5, MariaDB Server followed the MySQL version numbering schema, aiming for drop-in compatibility with the same major version of MySQL. In 2012, to reflect the growing number of features that were not available in MySQL, MariaDB Server’s version numbering diverged. MariaDB released 10.0, while MySQL released 5.6.
MariaDB Server still retains high levels of compatibility with MySQL. Upgrades from old MySQL versions to even the newest MariaDB versions are supported with an in-place upgrade.
Switching from MySQL to MariaDB follows a standard installation procedure. You run the mysql_upgrade tool to update the MySQL database’s privileges and event tables with MariaDB equivalents.
Summary of differences: MySQL vs. MariaDB
| MySQL |
MariaDB |
|
| JSON |
MySQL stores JSON reports as binary objects. |
MariaDB stores JSON reports in strings. MariaDB’s JSON data type is an alias for LONGTEXT. |
| Oracle database compatibility |
MySQL has a high level of compatibility, but does not support PL/SQL. |
MariaDB has a high level of compatibility and supports PL/SQL since version 10.3. |
| Speed and performance |
MySQL is slightly slower than MariaDB in replication and querying. |
MariaDB is slightly faster than MySQL in replication and querying. |
| Functionality |
MySQL supports super read-only function, dynamic columns, and data masking. |
MariaDB supports invisible columns and temporary table space. |
| Authentication |
MySQL has the validate_password component. |
MariaDB has three password validator plugins. |
| Encryption |
MySQL databases use InnoDB and AES to encrypt data at rest. |
MariaDB supports temporary log encryption and binary log encryption. |
| Storage engines |
MySQL has fewer storage engines than MariaDB. |
MariaDB has more storage engines than MySQL and can use multiple engines in one table. |
| License |
MySQL has two versions: MySQL Enterprise Edition and a GPL version. |
MariaDB is fully under GPL. |
| Thread pooling |
MySQL has thread pooling in its Enterprise Edition. |
MariaDB can manage over 200,000 connections at once, which is more than MySQL. |
How can AWS support your MySQL and MariaDB requirements?
Amazon Relational Database Service (Amazon RDS) is a collection of fully managed services that make it simple to set up, operate, and scale databases in the cloud.
Choose from seven popular engines, two of which are dedicated to MySQL and MariaDB. Amazon RDS for Maria DB supports MariaDB Server versions 10.3, 10.4, 10.5, and 10.6. Amazon RDS for MySQL supports MySQL Community Edition versions 5.7 and 8.0.
Here are ways you can benefit from using Amazon RDS:
- Deploy scalable MariaDB and MySQL cloud databases in minutes with cost-efficient and resizable hardware capacity
- Use existing code, applications, and tools without any change
- Access over 50 CPU, memory, file system, and disk I/O metrics for advanced database monitoring
- Enjoy enhanced availability and durability to elastically scale out beyond the capacity constraints of a single database instance
Get started with MySQL and MariaDB on AWS by creating an account today.
