With all MariaDB’s power under the hood, “MySQL vs MariaDB” was suddenly a legitimate topic of conversation. The two databases are so similar that MariaDB negates the need to upgrade mysql, because it can usually replace MySQL in a straight swap. Here’s an in-depth comparison of the two, their relation to Plesk and how to set it up.
So, what’s MySQL?
You may have thought that you might like to upgrade MySQL, so this might be one of the first questions that comes to mind. The short answer is, it’s an open source relational database management system that has its roots in SQL, or Structured Query Language. The long answer is that MySQL began life in 2008.
A Swedish company with the catchy name of MySQL AB came up with it, before Sun bought it in 2010. It’s backed up by Oracle and runs on the usual suspects: Linux, UNIX and Windows. Although it’s versatile enough to be used in all kinds of settings, it’s come to be best known to use with web apps and online publishing.
MySQL is a part of the LAMP web development platform, or ‘stack,’ which is the common term used for a bunch of software components used together for a particular purpose. LAMP is an acronym that looks like this:
- Linux
- Apache Web server
- MySQL, the relational database management system
- PHP, the object-oriented scripting language (Perl or Python are also sometimes used in place of PHP).
We can only guess that if Apache web server had been named Inuit, then LAMP would have been LIMP, and we wonder whether sales would have taken a nosedive as a result? But jokes aside, anyone can use MySQL thanks to its GNU General Public License (GPL). However, Oracle insists that enterprise users pay for the privilege of using their offering in commercial settings.
What is MariaDB?
How comparable is performance when we’re talking about MariaDB vs MySQL? Well first you need to know what MariaDB is. It’s a close relative MySQL, a software fork of MySQL that its developers came up with in 2009. After Oracle Corp picked up MySQL they wanted to create a stand-in.
MariaDB has SQL in its DNA and allows for ACID-style data processing with assured atomicity, durability, isolation, and consistency for transactions. It has lots of features (which developers at MariaDB Corp have worked hard to ensure are similar). They include support for JSON APIs, replication of parallel data and a variety of storage engines, like MariaDB, Aria, ColumnStore, InnoDB, MyRocks, Spider, TokuDB, and Cassandra.
But there are some areas that will be incompatible between versions. For instance, from version 10.1 on, MariaDB stores JSON data using a different format to MySQL 5.7. To get around this, users who want to copy columns of JSON objects from MySQL to MariaDB can either convert them to the format that the latter uses or run statement-based replication jobs with SQL.
MariaDB Corp. offers training, migration services, remote management, and various tailored products to subscribers. MariaDB Foundation, which was set up in 2012, looks after the source code to make sure that the software remains open source.
MySQL vs MariaDB comparison
| MySQL | MariaDB | |
| Database Structure | MySQL is an open-source relational database management system (RDBMS). As with every other relational database, MySQL users work with staple constituents such as tables, constraints, triggers, roles, stored procedures, and views. Each row, or record is identified with a primary key, and foreign keys are used to make sure that related tables can refer to each other accurately. | MariaDB is a fork of MySQL, so the database structure and indexes of MariaDB are the same as they are with MySQL. The upshot of this is that your applications will still work if you switch, because the way that the data is organised doesn’t need to change. |
| Indexes | MySQL also allows hash indexes and InnoDB engine utilises inverted lists for FULLTEXT indexes. | Most MySQL and MariaDB indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are held in B-trees. The indexes on spatial data types use R-trees so they are an exception. |
| Databases Deployment | MySQL is written in C and C++ and binaries are available for these systems: Microsoft Windows, OS X, Linux, AIX, BSDi, FreeBSD, HP-UX, IRIX, NetBSD, Novell Netware, and lots more. | MariaDB is written in C, C++, Bash, and Perl and has binaries for these systems: Microsoft Windows, Linux, OS X, FreeBSD, OpenBSD, Solaris, and others.
Since MariaDB works as a straight swap for MySQL, you should have no trouble uninstalling MySQL, installing MariaDB in its place, then connecting (so long as the file versions are the same). Please be aware that you should run mysql_upgrade to conclude the upgrade process. |
| Replication / Clustering | Replication with MySQL is asynchronous. One server acts as a master to the others’ slaves. You can replicate all databases, selected databases or even selected tables within a database.
MySQL Cluster is a technology providing shared-nothing (there isn’t one point of failure) clustering and auto-sharding (partitioning) for the MySQL database management system. Internally, MySQL Cluster uses synchronous replication through a two-phase commit mechanism to ensure that data is written to many nodes. This contrasts with what is normally termed “MySQL Replication”, which is asynchronous. |
MariaDB offers master-master and master-slave replication as well. MariaDB uses the Galera Cluster for multi-master. From MariaDB 10.1 onwards, Galera is included with MariaDB. To enable clustering you only need to activate the configuration parameters. |
| Support | MySQL developers and support engineers give round-the-clock coverage for patches, updates and fixing bugs. This is part of Oracle’s lifetime support arrangement.
Oracle offers MySQL Premier Support, Extended Support, and Sustaining Support depending upon your needs. |
MariaDB ‘s engineers give round the clock support under the enterprise subscription for mission-critical production systems. Support staff are well versed in both MariaDB and MySQL. |
| Connectors | MySQL provides a variety of database connectors which include C, C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, and Erlang. | MariaDB provides various database connectors which include ADO.NET, C, C++, D, Java, JavaScript, ODBC, Perl, PHP, Python, Ruby, and Visual Studio plug-in. |
MySQL and MariaDB under Plesk
Plesk keeps its own databases (which can sit locally on the same server as Plesk or remotely) for things like webmail service, its customers’ websites and their apps. It offers integration for MySQL, MariaDB and PostgreSQL database management engines and provides each of them with their own management tools.
Plesk has its own MySQL database on the local server that’s the key component of keeping it running. The good news is that if you have any version after and including 11.5 you can use MariaDB as a replacement. MariaDB vs MySQL performance shouldn’t concern you as MariaDB performs equally well.
Replacing MySQL with MariaDB in Plesk
- Backup the databases sitting on your MySQL server with one of these commands:
- for backing up for backing up all databases:
# mysqldump -uadmin -p`< /etc/psa/.psa.shadow ` --all-databases | gzip > /root/mysql.all.dump.sql.gz
- for just backing up the data which Plesk needs:
# mysqldump -uadmin -p`< /etc/psa/.psa.shadow ` --databases mysql psa apsc | gzip > /root/mysql.mysql-psa-apsc.dump.sql.gz
- Configure the MariaDB repository on your server. Make sure to choose a version of MariaDB which is equivalent to the version of MySQL which you are using. Plesk supports Maria DB versions 5.5, 10.0, and 10.1.
Note: after you’ve installed MariaDB, don’t disable this repository on your server. Plesk needs it to be enabled for updates.
- If the Watchdog Plesk extension is installed on your server then stop it, and also stop any other monitoring services that might start the mysql service after you’ve stopped it manually.
- Install MariaDB by following one of the methods mentioned here
- Turn on Watchdog and any other services that you stopped in step three.
- Tell Plesk about the changes in the MySQL component:
plesk sbin packagemng --set-dirty-flag
- (Optional) If you get any MySQL errors following this update, restore the backup you created in step 1:
zcat /root/mysql.all.dump.sql.gz | mysql -uadmin -p`< /etc/psa/.psa.shadow
with
6 Comments
I WANT TO TRY MARIADB ON HP-UX 11iv3 POSSIBLE?
Best contact our support about that, Jeff: https://support.plesk.com/hc/en-us
which one is in ubuntu 18? mariaDB or MYSql?
Hey Kingsley,
Seems Ubuntu 18 has both MariaDB and MySQL 🙂 You are also able to change to other versions . Hope this helps!
How can i replace MariaDB with MySQL
Hi Vlad,
Feel free to submit a request with our dedicated support to assist you on your way, hope this helps.