Choosing Between MySQL and MariaDB - A Comprehensive Guide Including Setup Instructions
Learn the differences between MySQL and MariaDB and how to set up a database with either.
Publish date: 3/21/2024
Learn the differences between MySQL and MariaDB and how to set up a database with either.
Publish date: 3/21/2024
When it comes to database management systems (DBMS), MySQL and MariaDB stand out as notable contenders. Originating from a shared lineage, they have evolved distinct philosophies and functionalities over time.
In this article, we'll compare the two, including the background story of why MariaDB even exists in the first place, and explain how you can set up a database using either.
Let's dive in.
MySQL's journey began in the mid-90s, rapidly ascending to become the preferred database system for web applications worldwide.
Its straightforward design and reliability made it a fundamental component of the early internet infrastructure.
However, the landscape began to shift when Sun Microsystems acquired MySQL AB in 2008, leading to Oracle Corporation's takeover of Sun in 2009.
At the time, it essentially validated that open source was a real business model. This series of acquisitions raised substantial concerns within the open-source community regarding MySQL's future autonomy and commitment to open-source principles.
In a strategic move to preserve the open-source ethos of MySQL, Monty Widenius, a co-founder of MySQL AB, initiated a fork of MySQL, thus birthing MariaDB in 2009.
That decisive action wasn't just about creating an alternative, either; it was about establishing a sanctuary for innovation, community engagement, and open-source development, free from the uncertainties of corporate ownership.
Back in 2011 when Monty was asked in an interview why he made MariaDB, he replied:
Two reasons:
I want to ensure that the MySQL code base (under the name of MariaDB) will survive as open source, in spite of what Oracle may do. As Oracle is now moving away from Open Source to Open Core (see my blog) it was in hindsight the right thing to do!
I want to ensure that the MySQL developers have a good home where they can continue to develop MySQL in an open source manner. This is important as if the MySQL ecosystem would lose the original core developers there would be no way for the product to survive. This is also in hindsight proven to be important as Oracle has lost almost all of the original core developers, fortunately most of them have joined the MariaDB project.
Since its inception, MariaDB has embarked on a path of rapid evolution, integrating a slew of enhancements, performance optimizations, and new functionalities, all the while ensuring that it remains highly compatible with MySQL.
However, It should be noted that while MySQL and MariaDB are very similar, they aren't technically 1:1 compatible, which is why a CMS like Ghost, for example, specifically states they only support MySQL 8 in production.
Now that you know the full story, let me explain how you could set up a MySQL or MariaDB database.
The purpose of a database is simple; to store data.
In the real world, this can result in storing a lot of different types of data.
It could be customer data, or it could be something like WordPress posts. It doesn't really matter.
In the following example, we'll use Debian Bookworm (12), install both MySQL and MariaDB (take your choice), create a database, install a simple web server and PHP, and then pull some information from our database and display it.
First up, always start with the latest updates for your system:
sudo apt update && sudo apt upgrade
Next, Apache will serve as the web server in our LAMP stack, so we'll need to install it first:
sudo apt install apache2
Then check if Apache is running:
sudo systemctl status apache2
PHP is crucial for dynamic web content.
So we'll need to install PHP along with its common extensions:
sudo apt install php libapache2-mod-php php-mysql
To verify PHP is installed successfully, run:
php -v
To install MySQL on Debian 12, you'll first need to add the MySQL repository (which you can find here):
sudo apt install gnupg
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
Then run:
sudo apt update
And next run:
sudo apt install mysql-server
It's much easier to install MariaDB on Debian.
You'll simply run:
sudo apt install mariadb-server
If you want to install latest version of MariaDB, you can find the repository and installation instructions here.
Regardless of your choice between MySQL and MariaDB, the following steps remain consistent.
First log into the SQL console:
sudo mysql -u root -p
Or if you're using MariaDB:
sudo mariadb -u root -p
Then create a new user and database, and grant privileges:
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
CREATE DATABASE testdb;
GRANT ALL PRIVILEGES ON testdb.* TO 'user'@'localhost';
FLUSH PRIVILEGES;
USE testdb;
CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255));
INSERT INTO test_table (data) VALUES ('Hello, World!');
EXIT;
You should replace the above information with your own desired username, password, and database name.
To test our database, we'll create a simple PHP script to test pulling data.
In your web server's root directory (which should be /var/www/html), create a file named test.php by running the following command:
nano /var/www/html test.php
Then copy and paste the following inside, updating the information according to the user, password, and database you just made:
<?php $conn = new mysqli("localhost", "user", "password", "testdb"); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $result = $conn->query("SELECT data FROM test_table"); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { echo "Data: " . $row["data"]. "<br>"; } } else { echo "0 results"; } $conn->close(); ?>
Finally, navigate to http://your_server_ip/test.php in your web browser to see the output.
If successful, you should see "Data: Hello, World!" displayed when you load the page.
Congrats! You're now the proud owner of a database.
In conclusion, both MySQL and MariaDB, provide excellent solutions for managing data in web applications. But overall, you could consider MariaDB an improved iteration of MySQL.
We hope you found the article valuable. If you're looking for reliable infrastructure for your database, website, or application, then please consider xTom.
We provide anywhere from shared hosting, to VPS, or dedicated servers, and even colocation. For a full list of what we have to offer, take a look here.
For a cost-effective, reliable, and scalable database infrastructure, our NVMe VPS line here works perfectly.
Thanks for reading!