MySQL/MariaDB – Building a LAMP Server (2019)

Post navigation

This is the sixth article in a series on building the ultimate LAMP Server. This covers installing MySQL and MariaDB from source. You can skip the sections for versions you don’t wish to support. Only install what you need. Even though I am assigning different ports for every installation to avoid conflicts, you may not want to set them all to automatically start in order to conserve resources; instead starting each database manually as needed.

If you’ve been following this series of posts, the ports I used here shouldn’t conflict with anything. But if they do, you may need to use different ports.

Estimated time (this post only): 15 minutes to 3 hours* depending on how many versions you install

MySQL vs. MariaDB

MySQL has been the #1 DBMS for web applications for years. But since its acquisition by Oracle, MariaDB has become a favored replacement on many distributions. I personally prefer MariaDB but many hosts and web applications use MySQL by default, so you should test any application you develop against both.

Choosing a Version

At the very least, I recommend testing against MySQL 8.0/5.7 and MariaDB 10.3/10.2 as of this writing. Some older web applications were designed for MySQL 5.5/5.6. And you may want to start testing the newer MariaDB 10.4 (not in GA status as of this writing).

Tested versions:

MySQL 5.5.62

MySQL 5.6.43

MySQL 5.7.25

MySQL 8.0.15

MariaDB 10.0.38

MariaDB 10.1.38

MariaDB 10.2.22

MariaDB 10.3.13

MariaDB 10.4.2

Important: bleeding-edge distributions such as Fedora and Arch Linux will almost certainly have difficulties compiling older versions of MySQL/MariaDB. I’m including instructions for workarounds as much as possible, but I can’t guarantee results. If you carefully follow the steps here, you will be able to install all the versions listed on every distribution I tested.

Prerequisites

Here are a few steps you should take. This assumes you’ve followed the steps in the Development Tools post.

Important: as of this writing, I ran into an error under openSUSE for some versions of MariaDB with the SSL certificate when downloading AWS SDK for C++. This is a known bug which occurs if you have built OpenSSL from source. To fix this, revert to the system default version of OpenSSL by doing the following and logging back in:

MySQL will fail to compile without Boost. MariaDB also uses Boost if available, but only for OQGraph support at this time as far as I know. The documentation on OQGraph for MariaDB is outdated (it even says so on their website) and the forums are filled with people having trouble installing it. I was able to successfully build MariaDB with OQGraph support easily on some systems, but on others it took a lot of trial and error. In the end, I decided not to include instructions for it in this guide.

Systems with SELinux (like CentOS and Fedora) may have issues starting some versions of MySQL/MariaDB without a few workarounds. That’s where setroubleshoot-server (which I recommended installing earlier) will come in handy. I’m going to make this as easy as possible without compromising security.

Create a user for running MySQL/MariaDB:

sudo groupadd mysql
sudo useradd -r -g mysql -s /bin/false mysql

Download Source

The source files will take up a lot of disk space! So I highly recommend just installing one version at a time, proceeding all the way to the end of this article where you will do some cleanup. Then you can come back to this section and download/install the next version. You can check the usage of all your partitions with:

df -h

If you are running low on disk space, there are several options. You could delete some of your source files from /usr/local/src but I don’t recommend that. If this is a virtual machine, you could expand the size of the virtual disk and extend the partition. Or add a hard drive and extend the partition. More information on this can be found in my article on managing a Linux virtual machine (can be easily modified to apply to a physical machine as well).

If you are going to install multiple instances of MySQL and/or MariaDB, it is important that you set a different install directory, port, and socket for each installation. If you don’t, you will only be able to run one installation at a time. I used the major and minor version numbers to differentiate each installation. For example: for MySQL 8.0, I set an installation directory of /opt/mysql80, a port number of 3308, and a socket of /tmp/mysql80.sock. For MariaDB 10.3, I set an installation directory of /opt/mariadb103, a port number of 33103, and a socket of /tmp/mariadb103.sock. If this is acceptable to you, you can simply copy/paste the commands below or edit them as desired.

MySQLMariaDB

Check the MySQL download page from any browser to find the current version number of the branch you wish to install. Then on your server download the source archive.

As mentioned earlier, recent versions of MySQL require a specific version of Boost. To make this easy for you I have written an if-then-else statement that will automatically download the source archive that contains Boost (if available), so even if the version installed via your package manager isn’t compatible, this will ensure MySQL will compile. Just copy/paste the following commands into your SSH terminal, replacing the version number, instance name, and port on the first line with whatever you wish to install.

Check the MariaDB download page from any browser to find the current version number of the branch you wish to install. Then on your server download the source archive. Change the version number and instance name on the first line and simply copy/paste the rest (note that as of this writing no version of MariaDB comes with Boost source included, so I am not including an if-then-else statement as I did with MySQL).

MariaDB 10.1 and newer require a specific version of LZ4. This can cause problems when trying to compile, so let’s download the required source files and copy them into our source tree. You only need to do the following three commands once! As of this writing it is critical that you are using LZ4 1.7.5 and no older or newer version! (Future versions of MariaDB may require a different version of LZ4.)

You can’t just download that patch unless you make changes to the paths and line numbers, but I’ve already done the work for you. Just do the following (I take no credit for the fix itself; I’ve only fixed the patch to work with multiple versions of MariaDB). If the patch fails to apply to future versions, you can edit it manually, create your own patch, or just edit client/mysql.cc for yourself. (Despite the listed version number, this patch is backwards-compatible with every version listed here; though I can’t guarantee compatibility with future versions.)

Install

If you wish to see all the cmake options and their defaults, you can do (from the build directory):

cmake ../ -LA | awk '{if(f)print} /-- Cache values/{f=1}'

The following shows the exact configure options I used for each version I installed. Worked with all tested distros.

Important: with some distributions using GCC 7+, you will not be able to compile some versions. This is a known issue. If the make process fails, do rm -rf * and re-run the cmake command below, prepending it with an updated environment to point to another installation of GCC. Example: env "PATH=/opt/gcc6/bin:$PATH" CC=gcc CXX=g++ cmake ../. I had to do this for Fedora+MariaDB 10.1.

The SSL option points to the location of OpenSSL. The following worked for most systems. You could set this to -DWITH_SSL=/opt/openssl to force it to use the OpenSSL version I recommended installing in the previous post or -DWITH_SSL=system or (for most versions) -DWITH_SSL=bundled. So if you run into SSL errors with the options I list below, try one of these other settings.

If something fails, you can rm -rf * and configure again with different options. If you run into errors removing the files in the bld directory you can reboot and set the variables again (example: TMPVER="8.0.15" && TMPINSTNAME="mysql80" && TMPPORT="3308"

Normally it isn’t recommended to pass the -fpermissive flag since it allows nonconformant code to compile without erroring out. However, these older versions of MySQL had nonconforming code so you can’t compile with a newer compiler without this flag.

Normally it isn’t recommended to pass the -fpermissive flag since it allows nonconformant code to compile without erroring out. However, these older versions of MySQL had nonconforming code and so you can’t compile with a newer compiler without this flag.

Normally it isn’t recommended to pass the -fpermissive flag since it allows nonconformant code to compile without erroring out. However, these older versions of MariaDB had nonconforming code and so you can’t compile with a newer compiler without this flag.

Normally it isn’t recommended to pass the -fpermissive flag since it allows nonconformant code to compile without erroring out. However, these older versions of MariaDB had nonconforming code and so you can’t compile with a newer compiler without this flag.

Note on SSL: although I have configured all instances with SSL support, I didn’t use direct SSL connections to MySQL/MariaDB. Instead, I left the firewall blocking all external MySQL/MariaDB TCP connections, relying on SSH forwarding or phpMyAdmin. If you wish to setup direct SSL connections from a remote system, check out this article and this one for more information.

Now compile (may take a while):

make VERBOSE=1

Optionally, test before installing. Since 2017 make test will fail for current versions of MariaDB 10.2+. See this bug report for details. So don’t bother running the following command for MariaDB 10.2+.

make test

Assuming all tests passed (if run), you can now install. With the latest version of GCC 8, I had a single failure for MySQL 8.0: "89 – routertest_router_config_generator (Failed)". But it didn’t seem to effect anything so I assume it’s an issue with the test itself.

sudo -E env "PATH=$PATH" make install

Post-Installation Tasks

Create Options Files

Create a basic configuration file that does nothing except point to an include directory that will contain our custom configuration files (note that if my.cnf already exists, this will override its contents, so if you had a previous MySQL/MariaDB instance installed, you may want to backup the file first). This is done automatically by most versions of MySQL, but it doesn’t hurt to do it once just in case.

If using the X protocol, you need to set a port for it as well. Do not do this unless you have compiled the X protocol! Currently it is only necessary for MySQL 8.0, but is optional with 5.7 as well. If you add this to the config file but haven’t compiled X protocol support, the service will refuse to start.

The X protocol is not currently supported by any version of MariaDB, so no need to add the mysqlx_port setting as we did for MySQL 8.0 (this may change in the future).

(Optionally) set other configuration options in a separate config file. You may wish to skip this and just stick with defaults or alter options as desired. Keep in mind these options will apply to all MySQL/MariaDB instances and should probably be tweaked based on the amount of RAM you have as well as how many services you plan to run simultaneously. For an overview of how to set options (including how to set separate options for each instance) see this article. If you choose to do this, one method would be to do something like:

sudo nano /etc/my.cnf.d/99-my.cnf

And paste the following (based on my-medium.cnf which was included in older versions of MySQL/MariaDB):

Initialize Database

There are a few things you should do now to secure the installation and setup services for starting/stopping MySQL/MariaDB. Remember that copying the init scripts will fail for Arch Linux and possibly other distributions, but if you followed my instructions above, everything should still work.

On some distributions, the /tmp directory may have permissions set that allow any user to delete all files in it. Since we installed the Unix socket file here (which is the recommended method), this could cause problems. To make sure this file can’t be accidentally deleted by anyone other than root and the MySQL user itself:

Start MySQL/MariaDB

Now start MySQL/MariaDB:

sudo systemctl daemon-reload
sudo systemctl start ${TMPINSTNAME}

Create Custom Init Script

Modern distributions use systemd for their init systems rather than SysV. Most have backwards support for SysV init scripts, which is what older versions of MySQL and MariaDB use. You shouldn’t normally have to write your own init script. But it is possible that some distributions may not initialize the SysV scripts. As of this writing I found this necessary for MySQL 5.5/5.6 under Arch Linux as well as MariaDB 10.0 under Fedora, Ubuntu, and Arch Linux. The same could hold true of other distributions in the future. So if MySQL < 5.7 or MariaDB < 10.1 won’t start normally, create a systemd-compatible init script.

SELinux Fixes

Important: Here’s where SELinux can become an issue if enabled. There are a lot of guides out there on troubleshooting SELinux issues like this one and this one and this one. This fix probably isn’t necessary anymore, but in the early days of MariaDB 10.3 development, it wouldn’t compile with an SELinux policy package, which caused major issues on Fedora. So I’m leaving these instructions in case the issue resurfaces. First, set SELinux to permissive mode:

sudo setenforce 0
sudo systemctl restart ${TMPINSTNAME}

If you are using SELinux, do:

sudo cat /var/log/messages | grep setroubleshoot

If the above shows something similar to the following, then you need to apply a fix to SELinux:

Assuming there were no more messages logged by setroubleshoot (there shouldn’t be), you are good to go. So stop the service, set SELinux back to enforcing mode, start the service again, and change back to the installation directory:

Diagnosing Startup Issues

If you run into difficulty getting multiple instances to run simultaneously, it is most likely due to a port conflict. Assuming you have netstat installed (part of the net-tools package), the easiest way to diagnose port conflicts is to stop all running instances of MySQL/MariaDB then start them one by one. After starting each one, run:

netstat -vatn

This will show you all ports which are listening on the system until you determine which two instances are conflicting. Then just edit the corresponding configuration file in /etc/my.cnf.d to set a different port.

Another common issue is if you have conflicting configuration files elsewhere on your system. That’s why I recommended creating a separate config file for each major version as described above. Use nano to edit each file until you find the problem.

Of course, it could be something else entirely, but if you’ve followed my instructions carefully, everything should be working on all the distributions I listed.

Test and Secure Database

You can now do the following (especially useful for MariaDB 10.2+):

cd './mysql-test' ; sudo perl mysql-test-run.pl
cd ..

The only failure I encountered under any of my tested distributions was with rocksdb.2pc_group_commit, which is a known bug.

Whether or not you ran the above test, do the following to show some system tables:

This is the point at which you can try running make test for MariaDB 10.2+ from your build directory. All tests should pass now (except a possible timeout with the PS test, depending on your distribution and system). The instructions below assume you are still in the base directory for your installation.

Now secure the installation:

MySQL < 5.7 & MariaDBMySQL 5.7+

You can go with the defaults for the questions you will be asked (keep hitting <Enter>), setting the MySQL root password when requested:

sudo bin/mysql_secure_installation

Unlike with older versions, you don’t just want to hit <Enter> for everything here since doing so will be the same as answering "No" rather than "Yes." Instead, answer "y" to everything, including setting a new password:

Cleanup

If you are completely done installing all versions of MariaDB, you can also delete the LZ4 source files and the patch I mentioned (if downloaded).

rm -rf lz4-1.7.5

And the patch I mentioned (if downloaded):

rm mariadb-*.tar.gz mariadb*.patch

Starting/Stopping

At this point I highly recommend rebooting the server and testing that each version of MySQL/MariaDB starts and stops as expected:

sudo reboot

To set any of the installed versions to automatically start or not start on boot (change "mysql80" to match your service name, such as "mariadb103"). I am not using the variable in these examples since it will be unset next time you log on:

Connecting to Database

With this setup, any application on the server will be able to access the database with no issue. For remote access (another computer on your network or your host system in the case of a virtual machine), you will want to manage it with a third-party utility. There are several options. phpMyAdmin (which we will install later) is a common option. But you can also use MySQL Workbench (though I’ve had trouble with it connecting to current versions of MariaDB). If you are accessing it from a Windows machine, I recommend HeidiSQL using an SSH tunnel. There’s also SQLyog, but the Community Edition doesn’t support SSH tunnels so you would have to purchase the commercial version.

The following screenshots give an example of setting up a connection for HeidiSQL via an SSH tunnel (note that you will need PuTTY installed):

Final Notes

The only (minor) downside to this method is that man pages will not be installed to a system default location. This was done intentionally in order to avoid conflicts. You could create symlinks in /usr/local/man/ with something like sudo ln -s /opt/mysql80/man/man8/mysqld.8 /usr/local/man/man8/mysql80.8, or just do man /opt/mysql80/man/man8/mysqld.8. I don’t see this as a big deal since there is better documentation online anyway.

With this setup, it would be technically possible to have all versions of both MySQL and MariaDB running simultaneously, though obviously this would be a drain on resources.

This is the most difficult part of building a LAMP server from source, especially on rolling release distributions such as Arch Linux. I’ve provided as many workarounds as I know at this time. The documentation on compiling MySQL/MariaDB on Arch Linux is virtually non-existent. Most users just rely on the AUR. That doesn’t work for our purposes because we want several side-by-side installations. I suppose you could manually edit the scripts in the AUR versions for prefix, port, socket, etc. But even with that the AUR versions don’t always compile successfully. This is why I don’t normally recommend rolling release distros for servers. They may be great for educational purposes, but the reality is that by upgrading binaries and libraries to the latest bleeding-edge versions, you risk a lot of incompatibilities with older software.

In the next article we will address installing other common databases, though this is entirely optional depending on your needs.