MariaDB vs. MySQL?

What are the main differences between MariaDB and MySQL?

In the world of relational databases, MariaDB and MySQL are two of the best-known options.

Both are based on the same fundamental structure, but there are some important differences between them that are worth understanding. In this blog post, we'll explore the key differences between MariaDB and MySQL in more detail to help you make the right choice for your database needs.

Historical background

Origin of MySQL

MySQL was created in 1994 by the two Swedish developers Michael Widenius and David Axmark. It was originally developed as an open source database management system based on the SQL structuring language. MySQL quickly became one of the most popular relational databases on the market and was widely used in various web applications.

Birth of MariaDB

MariaDB was created in 2009 as a fork of MySQL after Oracle took over MySQL. The lead developer of MySQL, Michael Widenius, was concerned about the future development of MySQL under Oracle's rule and decided to create MariaDB as an alternative. MariaDB retained many of the features of MySQL, but also added new features and emphasised the community involvement and openness of the project.

To summarise, MariaDB is a fork of MySQL that arose out of concerns about the future of MySQL under Oracle's control. MariaDB has evolved into a popular database solution that continues the tradition of openness and community involvement.

Key differences between MariaDB and MySQL

Function comparison

    CREATE TABLE Beispiel (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    datum DATETIME
    );

When comparing MariaDB and MySQL in terms of features, both are very similar as MariaDB is a fork of MySQL and has many features in common. For example, some additional features of MariaDB include optimised storage engines and additional data types.

Leistung und Geschwindigkeit

SELECT * FROM Beispiel WHERE name = 'Max' ORDER BY datum DESC LIMIT 10;

The performance and speed comparison between MariaDB and MySQL shows that MariaDB is often faster than MySQL due to the optimisations and improvements that have been implemented in MariaDB. This can make a significant difference, especially with large databases and complex queries.

Compatibility and community support

Compatibility with existing systems

MariaDB is designed to be fully compatible with MySQL. This means that you can easily migrate your MySQL database to MariaDB without having to make major changes to your existing systems. MariaDB also provides support for the use of MySQL drivers and connections, making migration even easier.

Community, documentation and support

Both MariaDB and MySQL have an active community that is continuously working on the development and improvement of the databases. MariaDB offers extensive documentation to help users with installation, configuration and troubleshooting. In addition, both databases have professional support options for organisations that want extra security.

There are also numerous forums for MariaDB and MySQL where users can ask questions, share knowledge and solve problems. This community support is a great advantage for companies that rely on these databases.

Licensing and cost implications

Differences in open source licences

A key difference between MariaDB and MySQL lies in the open source licences they use. MariaDB uses the GPL licence, while MySQL is under the proprietary Oracle licence. This means that MariaDB is freely available as open source software and can be further developed by the community, while MySQL has certain restrictions in terms of use and redistribution.

Effects on the total cost of ownershipn

The choice between MariaDB and MySQL can have a significant impact on a company's total cost of ownership. As MariaDB is an open source solution, there are usually no licence costs for its use. In contrast, using MySQL, which is covered by a proprietary licence, can lead to higher costs, especially if additional functions or support are required.

However, the total cost of ownership not only includes licence fees, but also aspects such as maintenance costs, employee training, implementation costs and potential risks associated with software selection.

Installation and basic configuration of MariaDB

The installation of MariaDB and MySQL can vary depending on the operating system. For most Linux distributions, MariaDB or MySQL can be installed using the package manager. Once installed, the basic configuration of both systems is similar, with MariaDB endeavouring to be a seamless replacement for MySQL.

Connection to MariaDB/MySQL in PHP

PHP offers several ways to connect to MariaDB and MySQL databases, including the MySQLi extension and PDO (PHP Data Objects). The use of PDO is recommended due to its database independence and security features such as prepared statements.

PDO example:

$host = 'localhost';
$db = 'testdb';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
$pdo = new PDO($dsn, $user, $pass, $options);
echo "Verbindung erfolgreich hergestellt";
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

This code works for both MariaDB and MySQL. The differences between the two systems are not in the connection establishment, but in other areas.

Differences in storage engines

MariaDB

MariaDB supports a variety of storage engines, including some developed specifically for MariaDB:

• Aria: Designed as a better MyISAM, with support for crash safety and ACID transactions.
• TokuDB: Offers compression and is optimised for large data volumes.
• Spider: Enables the sharding of data across multiple servers.

MySQL

• InnoDB (Standard): Provides full ACID transactions, row-level locking and foreign keys.

New functions in MariaDB

MariaDB continuously introduces new features that are not available in MySQL, including

• Window functionsn and Common Table Expressions (CTE) for complex queries.
• Virtual columns: Allow calculated values to be used in queries without saving them in the database.

Example of window functions in MariaDB:

SELECT
name,
salary,
AVG(salary) OVER (PARTITION BY department) as avg_department_salary
FROM employees;

This feature makes it possible to display the average salary per department together with the salaries of individual employees, a functionality that was not available in MySQL prior to version 8.0.

Conclusion

While MariaDB and MySQL are similar in many basic functionalities, MariaDB offers additional optimisations, features and storage engines that can make it more attractive for certain applications. The decision between MariaDB and MySQL ultimately depends on the specific requirements of the project, the desired performance and the security requirements.

For developers working with PHP, it is important to know that both database systems are well supported and the choice often depends on personal preference or specific project requirements.