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 Engines | MySQL 8 | MariaDB 10 | Specification |
|---|---|---|---|
| InnoDB/XtraDB | ✔ | ✔ | InnoDB 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. |
| MyISAM | ✔ | ✔ | MyISAM – the standard storage subsystem of old MySQL versions – offers quick access to database tables and indices. |
| MEMORY | ✔ | ✔ | MEMORY 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. |
| CSV | ✔ | ✔ | This engine saves data as data fields in CSV format are separated by commas. |
| Archive | ✔ | ✔ | This database engine is designed for long-term storage of large amounts of data. It offers various compression algorithms enabling space-saving data retention. |
| BLACKHOLE | ✔ | ✔ | BLACKHOLE serves as an inspector of SQL Statements. Instead of saving data, BLOCKHOLE simply logs any SQL syntax elements. |
| Merge | ✔ | ✔ | Merge, as its name already suggests, facilitates the merging of numerous MyISAM tables with identical structures into one, joint table. |
| Federated/FederatedX | ✔ | ✔ | Federated lets MySQL access tables from other servers via remote configuration. MariaDB avails of its fork: FederatedX. |
| ColumnStore | ✔ | ColumnStore is all about transferring InfiniDB, which allows for column-based processing of petabyte-sized data. | |
| Aria | ✔ | Since 2007, Aria has grown into a crash-proof alternative to MyISAM. | |
| Cassandra | ✔ | Cassandra 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). | |
| CONNECT | ✔ | The CONNECT storage engine facilitates local and remote access to various external data sources such as Dbase, CSV, DOS, FMT, and XML. | |
| Mroonga | ✔ | Mroonga is a column-based database engine that offers a full-text search in diverse languages including Chinese, Japanese, and Korean. | |
| MyRocks | ✔ | MyRocks has been created for quick, low-latency data storage and offers a distinctly improved compression than that of InnoDB. | |
| OQGRAPH | ✔ | The open query computation engine GRAPH (OQGRAPH for short) processes data from hierarchical database structures into complex graphs. | |
| Sequence | ✔ | This storage engine lets its users generate ascending and descending numerical sequences of positive integers including initial and end values as well as increments. | |
| SphinxSE | ✔ | Sphinx 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. | |
| Spider | ✔ | Spider 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. | |
| TokuDB | ✔ | TokuDB 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 development very fast.
- MySQL
- InnoDB cluster which consists of:
- Group Replication (available as a plugin)
- MySQL Shell
- MySQL Router
- InnoDB cluster which consists of:
- MariaDB
- MariaDB Galera Cluster:
- Galera 4 (available in a separate package)
- MariaDB Galera Cluster:
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].
- (Data-at-Rest) Encryption
- 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
- Transparent Data Encryption (TDE)
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
| Roles | MySQL 8 (April 2018) | MariaDB 10.0 (Mar 2014) |
| Check constraints | MySQL 8 (April 2018) | MariaDB 10.2 (Mar 2017) |
| Default value expressions | MySQL 8 (April 2018) | MariaDB 10.2 (Mar 2017) |
| Common table expressions | MySQL 8 (April 2018) | MariaDB 10.2 (Mar 2017) |
| Window functions | MySQL 8 (April 2018) | MariaDB 10.2 (Mar 2017) |
| Instant add column | MySQL 8 (Jul 2018) | MariaDB 10.3 (May 2018) |
| Encrypted log files | MySQL 8 (Jan 2019) | MariaDB 10.1 (Oct 2015) |
Software
| Database | MySQL | MariaDB Enterprise Server |
| Database router/proxy | MySQL Router* | MariaDB MaxScale |
| Backup/restore tool | MySQL Enterprise Backup | MariaDB Enterprise Backup |
| Monitoring | MySQL Monitor | SQL Diagnostic Manager |
| Window functions | MySQL 8 (April 2018) | MariaDB 10.2 (Mar 2017) |
| SQL IDE | MySQL Workbench* | SQLyog |
| *Available without enterprise subscription | ||
Plugins
| Auditing | MySQL Enterprise Audit | MariaDB Enterprise Audit |
| Clustering | MySQL Group Replication* | MariaDB Enterprise Cluster |
| Data masking | MySQL Enterprise Data Masking | MariaDB MaxScale |
| Database Firewall | MySQL Enterprise Firewall | MariaDB MaxScale |
| Encryption functions | MySQL Enterprise Encryption | Yes* |
| Federation | N/A | MariaDB Enterprise Federation |
| HashiCorp Vault plugin | Yes | Yes |
| PAM/LDAP authentication | MySQL Enterprise Security | Yes* |
| Thread pool | MySQL Enterprise Thread Pool | Yes* |
| *Available without enterprise subscription | ||
Schema
| MySQL 8 | MariaDB 10.5 | |
| Generated Columns | ✔ | ✔ |
| Views | ✔ | ✔ |
| Invisible columns | - | ✔ |
| Sequence | - | ✔ |
| Temporal tables | - | ✔ |
Instant Schema Changes
| MySQL 8 | MariaDB 10.5 |
|---|---|
| Add Columns(Last position) | Add Columns (Any position) |
| Set/drop column default value | Set/drop column default value |
| Add enum/set option | Add enum/set option |
| Rename table | Rename 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 8 | MariaDB 10.5 | |
| Functional indexes | ✔ | - |
| Descending indexes | ✔ | - |
| Invisible indexes | ✔ | - |
Queries
| MySQL 8 | MariaDB 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 8 | MariaDB 10.5 | |
|---|---|---|
| Table/row compression | ✔ | ✔ |
| Column compression | - | ✔ |
| Partitioning | ✔ | ✔ |
| ROLLUP | ✔ | ✔ |
| Parallel query | - | ✔ |
| Query result caching (via Redis) | - | ✔ |
| Read/write splitting | - | ✔ |
| Distributed SQL | - | ✔ |
High availability
| MySQL 8 | MariaDB 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 8 | MariaDB 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 8 | MariaDB 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 8 | MariaDB 10.5 | |
|---|---|---|
| JSON filter/rule definitions | ✔ | ✔ |
| Output to Syslog | - | ✔ |
| XML/JSON audit log formats | ✔ | - |
| Compressed and encrypted audit logs | - | ✔ |
Security – advanced protection
| MySQL 8 | MariaDB 10.5 | |
|---|---|---|
| Dynamic data masking | - | ✔ |
| Dynamic data obfuscation | - | ✔ |
| Database firewall | ✔ | ✔ |
| Query throttling | - | ✔ |
| Query result limiting | - | ✔ |
Database firewall
| MySQL 8 | MariaDB 10.5 |
|---|---|
| Dynamic data masking | Whitelisting |
| - | Blacklisting |
Checks for
| Checks for
|
KEY DIFFERENCES
| Parameter | MySQL | MariaDB |
|---|---|---|
| Storage Engines | FreeBSD Linux OS X Solaris Windows | FreeBSD Linux Solaris Windows |
| Cache/indexes | The 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 Pool | The 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 Replication | MySQL'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/Extensions | The new MariaDB features are not provided in MySQL. | MariaDB comes with new features and extensions including the JSON, WITH, and KILL statements. |
| Missing Features | The 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 code | MySQL uses some proprietary code in its Enterprise Edition. | MariaDB doesn't allow access to this proprietary content and is a closed source. |
| Data Masking | Yes | No |
| Dynamic columns | Yes | No |
| Monitoring | MySQL workbench | MySQL workbench |
| Routing | MySQL Router | MariaDB 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.
BENCHMARKING TOOLS
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.
You may also like
Top Laravel Development Companies in India to Watch Out For in 2024
Today’s modern-day consumers demand aesthetically pleasing...
Laravel 11: Suite of New Features Unlocked!
Web application development is at an all time high. And, so is the PhP framework giant- Laravel. Wit...
Why Your Business Need to Hire a Professional Web Development Company?
Businesses of all walks and sizes hinge on web development c...
