“MySQL done better” is one way to describe MariaDB. It’s a fork of that popular open source database project, launched by one of MySQL’s own creators—but with a different development team, many powerful features included by default rather than only available as add-ons, and many performance, usability, and security improvements that aren’t guaranteed to show up in MySQL.
MariaDB is often billed as a “drop-in replacement” for MySQL, especially as the MariaDB project is kept in close sync with the original. That said, it’s sort of like saying a Mazda is a drop-in replacement for a Subaru. They’re both cars, and they’re both driven about the same way, but the features they offer and the ways they’re implemented are different enough to warrant attention.
In this article we’ll walk through the steps needed to bring up a standard MariaDB server installation. Along the way we’ll pay particular attention to things you need to keep in mind when upgrading from a previous MariaDB installation, or migrating a MySQL database instance to MariaDB.
Note that the discussion in this article focuses on the community open-source edition of MariaDB, not the for-pay editions aimed at enterprise users. Those include features like OLTP, clustering, and data adapters to services like Apache Kafka and Apache Spark.
Where to download MariaDB 10.3
The MariaDB Foundation maintains its own download site for MariaDB binaries. All supported operating systems are on the same page, with various options available for each OS. You can fetch the source code and compile MariaDB from source if you prefer, but the vast majority of the time you shouldn’t need to do this.
How to install MariaDB 10.3
The process for setting up MariaDB will vary depending on the target operating system.
- Microsoft Windows users can download either an .MSI package or a .ZIP file. The .MSI is for installing formally on a system; the .ZIP archive can be unpacked into any directory and run directly from there. However, the .ZIP archive version requires that you perform any configuration manually; the .MSI package installs and configures MariaDB via a GUI.
- Linux users have their choice of gzipped .TAR files, .DEB packages for Ubuntu/Debian-based distributions, or .RPM packages for Red Hat/Fedora-based distributions. Note that if you install MariaDB’s provided .DEB./RPM files, you’ll need to configure an appropriate repository for your Linux install first. It’s also possible to install MariaDB from a Linux distribution’s default repository, but you may not be able to obtain the most recent version of MariaDB there.
- MacOS users can obtain MariaDB by using the Homebrew package management system, which contains precompiled binaries. Note that some components (the CONNECT and OQGRAPH database engines) are not included in the prebuilt binary package, and so need to be compiled from source with the
--build-from-sourcecommand line switch.
MariaDB also provides Docker images for Linux. Odds are you’ll still need to configure MariaDB’s options by way of a configuration file, and find a place to store its data other than the default path on the host used by the container. For instance, if you want to launch a MariaDB container on the host at /my/own/datadir, you could use this launch command:
$ docker run --name some-mariadb -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mariadb:tag
The MariaDB page on Docker Hub, from which the above example was taken, has more details on how to accomplish this.
How to upgrade to MariaDB from a previous version
If you’re upgrading from a previous version of MariaDB, you have two general strategies for the upgrade.
- In-place upgrade. This involves replacing the existing MariaDB binaries with a newer version.
- Export and re-import. This involves dumping the contents of the database from the old version, and importing them into the new version.
In either case, you should ensure that the databases you’re upgrading from don’t have lingering compatibility issues. Check the ”Upgrading MariaDB” document to see if there are issues specific to the versions you’re migrating from.
Because MariaDB has some slight architectural differences across platforms, the upgrade process will vary depending on where it’s being performed.
Microsoft Windows
On Windows, the best strategy is to install the new version of MariaDB in parallel with the old one, then decommission the old one as needed.
MariaDB supports having any number of editions running in parallel, as long as they have different major/minor version numbers. For example, you could have versions 10.0 and 10.2 running side by side, but not versions 10.2.6 and 10.2.7. In the latter case, installing 10.2.7 would automatically overwrite and update the 10.2.6 installation. The one exception to this rule is when one instance is the 32-bit version and the other is the 64-bit version—those can coexist even if they share the same minor version number, because the 32-bit and 64-bit editions are technically different products.
If you’re installing on Windows using the .MSI package, the installer will attempt to detect existing instances of MySQL or MariaDB and offer you the opportunity to upgrade them. Any existing data is left in its original location.
If you want to perform the upgrade manually after installing a new version, use the mysql_upgrade command-line tool in the new version to upgrade data from the old version. Yes, that’s mysql_upgrade, because it performs upgrades from MySQL instances as well as MariaDB, and because the choice of command name helps preserve compatibility with scripts written for MySQL.
Linux
On Linux systems, the upgrade process requires that you uninstall the old version of MariaDB before installing the new one. Before you do this, make a full backup of your existing database using the mysqldump tool if you’re upgrading from MySQL, or using the mariabackup tool if you’re migrating from an earlier version of MariaDB.
For more details about running multiple instances of MariaDB side-by-side on the same system, see this MariaDB Knowledge Base article.
How to configure MariaDB
The sheer number of configuration options for MariaDB can be overwhelming, but mercifully many of them aren’t needed in the initial phases of the installation or upgrade process.
In many cases, the MariaDB installer will automatically set up MariaDB to start at boot time and to spin up immediately after the installer script is finished. If you need to accomplish this manually, you can do so with the following commands:
systemctl start mariadb.service systemctl enable mariadb.service
Another thing MariaDB does by default when installed is automatically create a system account with limited privileges, mysql, for the MariaDB process to run in. This saves you the trouble of having to set it up yourself.
If you have unpacked MariaDB from an archive, rather than installed it formally, you may need to run the mysql_install_db script to set up MariaDB’s system tables.
Edit the MariaDB configuration file
The Microsoft Windows graphical installer, and the install scripts for Linux, configure the most important options for MariaDB. With the Windows GUI, you can change the most common options—e.g., the port MariaDB runs under—but sensible defaults are provided automatically for everything.
Those selections are recorded in the configuration file used by MariaDB. Where this file is stored depends on what system you’re running; see the previous link for details.
Chances are you may not need to edit this file to get MariaDB up and running, because again MariaDB picks reasonable defaults for the most common settings. It’s easy to be intimidated by the sheer number of settings that can be addressed here, but for most installs, you likely won’t need to change much of anything out of the box.
MariaDB configuration files are structured such that both MySQL and MariaDB instances can use them interchangeably. It’s also possible to have sections of a configuration file that are used only by a specific version of either product. Still, the best long-term strategy is to have a separate configuration file for each individual instance of MariaDB and MySQL, and to place those files where each server will discover them independent of the other. (See the MariaDB Knowledge Base page on configuration files to learn where to place a config file so that it’s discovered by a specific version of the product.)
Run the secure installation
Unless you’re running MariaDB strictly in a development context for use on a single machine, it’s a good idea to lock it down. MariaDB includes a Perl script, mysql_secure_installation, that secures the server by changing several defaults:
- Forcing a default internal root password for MariaDB. This is most crucial, since MariaDB’s default root password is blank. (The admin decides what that password is; choose a strong one.)
- Removing anonymous user access.
- Disallowing remote root login. There may be circumstances where you want to allow remote users root access, but generally it’s wise to disable this. (The safest way to allow some manner of remote root login is to require people to remote into the server itself, then connect locally to MariaDB.)
- Removing the test database. The test database is included in MariaDB installations as a sample or demonstration, but there is no reason to include it in a production system.
Note that the mysql_secure_installation requires Perl to run. If you’re on a system that doesn’t have a Perl runtime installed, you’ll need to add it before you can use this script.
Major MariaDB configuration options
The vast majority of options for MariaDB 10 don’t need to be set out of the gate. A few, like the above-mentioned client security protocol, should be decided as early on as possible, whether you use the GUI installation wizard or edit the config file by hand. Here are several others:
MariaDB connection protocols
MariaDB can accept incoming connections to the server in multiple ways:
- TCP/IP. This is the most widely used and understood way to connect to a MySQL instance, since it allows both local and remote connections. It’s also the slowest overall, because the traffic has to go through the entire TCP/IP stack. However, that is generally a concern only in scenarios where you need extreme performance. For any situation where you need remote client connectivity, TCP/IP is the easy answer. Finally, you need to pay close attention to the security of the connection. Use the more advanced password security described above, maybe restrict connections to a VPN, and so on.
- Unix sockets/named pipes. These are two roughly equivalent features in Unix/Linux and Windows, respectively. Sockets and pipes work best when the client/application and server are on the same system, because data can be transferred between client and server all the faster. It’s also possible to publish such connections across a network, but it’s cumbersome to do so; you’re better off just using TCP/IP in such cases.
- Shared memory. This is the fastest connection option between server and client, but also the least flexible. It works only if server and client are on the same physical system, and it can be difficult for the server to detect if the client has timed out or terminated, causing unterminated connections to accumulate and waste resources.
Note that on Unix systems, any connections from localhost default to using Unix sockets for the sake of performance.
MariaDB logging options
In addition to the error log, which is always enabled by default, MariaDB can maintain three other kinds of logs. These others are all disabled by default, but can be enabled as needed for debugging during the configuration process.
- General query log. This provides a detailed running summary of MySQL’s activities from startup to shutdown.
- Slow query log. Any queries that take more than x seconds to execute (the number is configurable) can be logged for debugging.
- Binary log. Dubbed the “binlog” for short, this log can be configured to contain binary dumps of all the modifications to database files. For obvious reasons this has a major performance impact, so it shouldn’t be enabled except when trying to debug truly intractable issues.
Individual storage engines, such as MyISAM, may also keep their own logs, but those are not part of MariaDB itself.
By default, logs are not automatically cleaned out, so must be removed periodically by running the mysql-log-rotate script. The one exception to this is the binlog, which by default expires after 30 days. Note that the rotation script may not be available out of the box on anything other than Linux; on Windows, you’ll need to rotate logs manually or by using a script of your own making.
MariaDB storage engines and collation options
MariaDB can work with a number of different storage engines, each of which is optimized for a different workload. For instance, the MyRocks engine is optimized for use on flash storage, while the CONNECT engine can be used to mount plaintext or CSV data as if it were a native MariaDB database.
By and large, though, you’ll want to use the InnoDB engine. InnoDB is the default choice for newly created tables in MariaDB. It’s useful to know why.