LAMP 2017MariaDBMySQL

MySQL/MariaDB – Building a LAMP Server

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 2.5 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 due to new/improved features, better security, and better performance in general. Since many hosts and web applications use MySQL by default, you should test any application you develop against both.


Choosing a Version

At the very least, I recommend testing against MySQL 5.7/5.6 and MariaDB 10.2/10.1 as of this writing. Some older web applications were designed for MySQL 5.5. And you may want to start testing the latest development builds as well.

Tested versions:

  • MySQL 5.5.58
  • MySQL 5.6.38
  • MySQL 5.7.20
  • MySQL 8.0.3 rc
  • MariaDB 10.0.33
  • MariaDB 10.1.29
  • MariaDB 10.2.11
  • MariaDB 10.3.2 alpha

Important: bleeding-edge distributions such as 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, especially for Arch Linux. If you carefully follow the steps here, you will be able to install all the versions listed on every distribution I tested.


Prerequisites

Install some prerequisites. Note that not all of these may be needed depending on your build options. This is in addition to the packages mentioned on the Development Tools post.

CentOSFedoraUbuntuDebianopenSUSEArch Linux

Some of the following require that the EPEL repository be enabled, which it is if you’ve followed this series of articles.

sudo yum install -y \
https://packages.groonga.org/centos/groonga-repository-1.0.1-0.noarch.rpm
sudo yum install binutils-devel bison bzip2-devel git gnutls-devel gzip \
java-devel jemalloc-devel Judy-devel krb5-devel libaio-devel libcurl-devel \
libevent-devel libxml2-devel lz4-devel mecab-devel msgpack-devel ncurses \
ncurses-devel ncurses-libs pcre-devel pcre2-devel perl-DBI pkgconfig \
systemtap-sdt-devel unixODBC-devel valgrind-devel zeromq-devel zlib-devel \
groonga-devel openssl-devel systemd-devel boost-devel xinetd lzo-devel \
perl-DBD-MySQL boost numactl-devel libstemmer-devel pam-devel cracklib-devel \
setroubleshoot-server

You also should remove the default installation of MariaDB if it exists:

sudo yum remove mariadb*
sudo dnf install binutils-devel bison bzip2-devel git gnutls-devel gzip \
java-devel jemalloc-devel Judy-devel krb5-devel libaio-devel libcurl-devel \
libevent-devel libxml2-devel lz4-devel mecab-devel msgpack-devel ncurses \
ncurses-devel ncurses-libs pcre-devel pcre2-devel perl-DBI pkgconfig \
systemtap-sdt-devel unixODBC-devel valgrind-devel zeromq-devel zlib-devel \
groonga-devel openssl-devel systemd-devel boost-devel xinetd lzo-devel \
perl-DBD-MySQL boost numactl-devel libstemmer-devel pam-devel cracklib-devel \
setroubleshoot-server
sudo apt-get install binutils-dev bison libbz2-dev git libgnutls-dev \
gzip default-jdk-headless libjemalloc-dev libjudy-dev libkrb5-dev libaio-dev \
libcurl4-openssl-dev libevent-dev libxml2-dev liblz4-dev libmecab-dev \
libmsgpack-dev libncurses5 libncurses5-dev libncursesw5-dev libpcre2-dev \
libpcre3-dev libdbi-perl pkg-config systemtap-sdt-dev unixodbc-dev valgrind \
libzmq-dev zlib1g-dev libgroonga-dev openssl libsystemd-dev libboost-dev xinetd \
liblzo2-dev libdbd-mysql-perl libnuma-dev libstemmer-dev libpam0g-dev libcrack2-dev
printf \
"deb https://packages.groonga.org/debian/ jessie main\ndeb-src https://packages.groonga.org/debian/ jessie main" \
| sudo tee /etc/apt/sources.list.d/groonga.list
sudo apt-get update
sudo apt-get install --allow-unauthenticated groonga-keyring
sudo apt-get update
sudo apt-get install binutils-dev bison libbz2-dev git gnutls-dev gzip \
default-jdk-headless libjemalloc-dev libjudy-dev libkrb5-dev libaio-dev \
libcurl4-openssl-dev libevent-dev libxml2-dev liblz4-dev libmecab-dev \
libmsgpack-dev libncurses5 libncurses5-dev libncursesw5-dev libpcre2-dev \
libpcre3-dev libdbi-perl pkg-config systemtap-sdt-dev unixodbc-dev valgrind \
libzmq5-dev zlib1g-dev libgroonga-dev openssl libsystemd-dev libboost-dev \
xinetd liblzo2-dev libdbd-mysql-perl libnuma-dev libstemmer-dev libpam0g-dev \
libcrack2-dev
sudo zypper install binutils-devel bison bzip2 git libgnutls-devel gzip \
java-devel jemalloc-devel judy-devel krb5-devel libaio-devel libcurl-devel \
libevent-devel libxml2-devel liblz4-devel msgpack-devel libncurses5 ncurses-devel \
pcre-devel pcre2-devel perl-DBI pkg-config systemtap-sdt-devel unixODBC-devel \
valgrind-devel zeromq-devel zlib-devel openssl-devel systemd-devel boost-devel \
xinetd lzo perl-DBD-mysql libnuma-devel pam-devel cracklib-devel
sudo pacman -S binutils bison bzip2 git gnutls gzip jdk8-openjdk \
jemalloc judy krb5 libaio curl libevent libxml2 lz4 msgpack-c ncurses \
pcre pcre2 perl-dbi pkg-config systemtap unixodbc valgrind zeromq zlib openssl \
boost-libs inetutils lzo perl-dbd-mysql boost numactl libstemmer pam cracklib

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 since virtually no web servers seem to use that storage engine anyway.

Systems with SELinux (primarily RedHat derivatives like CentOS and Fedora) may have issues starting some versions of MySQL/MariaDB without a few workarounds. That’s why I’m recommending that you install setroubleshoot-server as it will make patching SELinux very simple. I’m going to make this as easy as possible without compromising security (most guides will tell you to completely disable SELinux, but that’s a bad idea). At this time, I have had no issues with AppArmor.

Create a user for running MySQL/MariaDB:

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

If a my.cnf file exists in the /etc/ directory it will override the settings for your MySQL/MariaDB installations, probably preventing them from starting. So delete it. This usually occurs if you had a previous MySQL/MariaDB installation installed as part of the installation of the operating system:

sudo rm -f /etc/my.cnf

ICU

As of this writing, this section only applies to Arch Linux. I did not find it necessary on any other distribution. The problem is that MariaDB fails to compile with ICU (which provides Unicode support) version 59 or newer (at least on Arch Linux, and possibly other distributions). If you aren’t sure, just skip this section for now and come back here if you encounter errors while compiling that look similar to:

/usr/include/unicode/umachine.h:347:13: error: ‘char16_t’ does not name a type
  typedef char16_t UChar;
/usr/include/unicode/uversion.h:167:55: error: ‘UChar’ does not name a type
  u_versionFromUString(UVersionInfo versionArray, const UChar *versionString);

If you are certain you need this, do the following exactly.

sudo ln -s locale.h /usr/include/xlocale.h
cd ~
wget http://download.icu-project.org/files/icu4c/58.2/icu4c-58_2-src.tgz
tar zxvf icu4c-58_2-src.tgz
mkdir icu/bld
cd icu/bld
../source/configure --prefix=/usr/local && make
sudo make install
cd ~
sudo mv icu /usr/local/src/
rm icu4c-58_2-src.tgz

The downside to this is that ICU does not provide an uninstall target, so after doing this you will be permanently stuck with this version of ICU as your default unless you manually remove the files. I will provide a method for doing so at the end of this article, but it could be risky on some distributions. I will try to make this as safe as possible.


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 5.7, I set an installation directory of /opt/mysql57, a port number of 3307, and a socket of /tmp/mysql57.sock. For MariaDB 10.2, I set an installation directory of /opt/mariadb102, a port number of 33102, and a socket of /tmp/mariadb102.sock. If this is acceptable to you, you can simply copy/paste the commands below or edit them as mentioned.

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 and instance name on the first line with whatever you wish to install.

TMPVER="5.7.20" && TMPINSTNAME="mysql57"
cd ~
if [[ `wget -S --spider https://cdn.mysql.com/Downloads/MySQL-$(echo $TMPVER | \
sed -e 's/\.[^\.]*$//')/mysql-boost-${TMPVER}.tar.gz  2>&1 | \
grep 'HTTP/1.1 200 OK'` ]]; \
then wget https://cdn.mysql.com/Downloads/MySQL-$(echo $TMPVER | \
sed -e 's/\.[^\.]*$//')/mysql-boost-${TMPVER}.tar.gz; \
else wget https://cdn.mysql.com/Downloads/MySQL-$(echo $TMPVER | \
sed -e 's/\.[^\.]*$//')/mysql-${TMPVER}.tar.gz; fi

Examples of other versions you may wish to install (and I have tested on all distributions listed in this guide):

TMPVER="5.5.58" && TMPINSTNAME="mysql55"
TMPVER="5.6.38" && TMPINSTNAME="mysql56"
TMPVER="8.0.3-rc" && TMPINSTNAME="mysql80"

(Optionally) check the MD5 checksum of the download against what is shown on the download page:

md5sum mysql*.tar.gz

Extract and setup your build directory:

tar zxvf mysql-*.tar.gz
rm mysql-*.tar.gz
mkdir -v mysql-${TMPVER}/bld

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).

TMPVER="10.2.11" && TMPINSTNAME="mariadb102"
cd ~
wget \
https://downloads.mariadb.org/interstitial/mariadb-${TMPVER}/source/mariadb-${TMPVER}.tar.gz

Examples of other versions you may wish to install (and I have tested on all distributions listed in this guide):

TMPVER="10.0.33" && TMPINSTNAME="mariadb100"
TMPVER="10.1.29" && TMPINSTNAME="mariadb101"
TMPVER="10.3.2" && TMPINSTNAME="mariadb103"

(Optionally) check the MD5 checksum of the download against what is shown on the download page:

md5sum mariadb*.tar.gz

Extract and setup your build directory:

tar zxvf mariadb-${TMPVER}.tar.gz
rm mariadb-${TMPVER}.tar.gz
mkdir -v mariadb-${TMPVER}/bld

The most recent versions of the MariaDB 10.1 branch and newer now 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 absolutely 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.)

wget https://github.com/lz4/lz4/archive/v1.7.5.tar.gz
tar zxvf v1.7.5.tar.gz
rm v1.7.5.tar.gz

Copy this version of the LZ4 source files to each version of MariaDB 10.1 or newer as follows.

cp -rv ~/lz4-1.7.5 mariadb-${TMPVER}/storage/mroonga/vendor/groonga/vendor/

Patches

You can probably skip this step unless you are installing MariaDB on the latest version of Arch Linux, though this could change in the future.

I tried very hard to avoid having to apply third-party patches to the source code, but some rolling release distributions would not compile MariaDB even with all the other workarounds I’ve listed. Most notably Arch Linux. The fix is simple, though it took me longer than I care to admit to find. I found the patch here (I don’t know why they haven’t fixed the issue in any release version).

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 patched 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 obviously I can’t guarantee compatibility with future versions.)

cd ~
wget \
https://supremerulerofearth.com/wp-content/uploads/2017/11/mariadb-patches.tar.gz
tar zxvf mariadb-patches.tar.gz
cp mariadb-10.3-client-mysql.cc.patch mariadb-${TMPVER}/client/
cd mariadb-${TMPVER}/client/
patch < mariadb-10.3-client-mysql.cc.patch

Install

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

cmake ../ -L | 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.

MySQL 5.5MySQL 5.6MySQL 5.7MySQL 8.0MariaDB 10.0MariaDB 10.1MariaDB 10.2MariaDB 10.3

Normally it isn’t recommended to pass the -fpermissive flag to G++ 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.

The SSL option points to the location of OpenSSL. MySQL 5.5 would not compile with the version of OpenSSL I recommended installing in the previous post. If this fails for some reason (i.e. your system uses a newer version of OpenSSL), you can either download and compile an older version of OpenSSL or omit the -DWITH_SSL option altogether. See the note below if you aren’t sure if you need SSL support.

cd ~/mysql-${TMPVER}/bld
cmake ../ -DCMAKE_CXX_FLAGS=-fpermissive \
-DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/opt/${TMPINSTNAME} \
-DMYSQL_TCP_PORT=3305 -DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DSYSCONFDIR=/opt/${TMPINSTNAME} -DWITH_SSL=system

Normally it isn’t recommended to pass the -fpermissive flag to G++ 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.

The SSL option points to the location of OpenSSL. The following will point to the OpenSSL version I recommended installing in the previous post. Alternately, you could use -DWITH_SSL=system (which may be necessary on some platforms). See the note below if you aren’t sure if you need SSL support.

cd ~/mysql-${TMPVER}/bld
cmake ../ -DCMAKE_CXX_FLAGS=-fpermissive \
-DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/opt/${TMPINSTNAME} \
-DENABLE_DOWNLOADS=1 -DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DINSTALL_SECURE_FILE_PRIVDIR=/opt/${TMPINSTNAME}/mysql-files \
-DSYSCONFDIR=/opt/${TMPINSTNAME} -DWITH_SSL=/opt/openssl

The SSL option points to the location of OpenSSL. The following will point to the OpenSSL version I recommended installing in the previous post. Alternately, you could use -DWITH_SSL=system (which may be necessary on some platforms). See the note below if you aren’t sure if you need SSL support.

cd ~/mysql-${TMPVER}/bld
cmake ../ -DBUILD_CONFIG=mysql_release \
-DCMAKE_INSTALL_PREFIX=/opt/${TMPINSTNAME} -DENABLE_DOWNLOADS=1 \
-DMYSQL_TCP_PORT=3307 -DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DINSTALL_SECURE_FILE_PRIVDIR=/opt/${TMPINSTNAME}/mysql-files \
-DINSTALL_MYSQLKEYRINGDIR=/opt/${TMPINSTNAME}/keyring \
-DWITH_BOOST=../boost -DWITH_SYSTEMD=1 \
-DSYSTEMD_SERVICE_NAME=${TMPINSTNAME} \
-DSYSTEMD_PID_DIR=/opt/${TMPINSTNAME}/var/run \
-DSYSCONFDIR=/opt/${TMPINSTNAME} -DWITH_SSL=/opt/openssl

The SSL option points to the location of OpenSSL. The following will point to the OpenSSL version I recommended installing in the previous post. Alternately, you could use -DWITH_SSL=system (which may be necessary on some platforms). See the note below if you aren’t sure if you need SSL support.

cd ~/mysql-${TMPVER}/bld
cmake ../ -DBUILD_CONFIG=mysql_release \
-DCMAKE_INSTALL_PREFIX=/opt/${TMPINSTNAME} -DENABLE_DOWNLOADS=1 \
-DMYSQL_TCP_PORT=3308 -DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DINSTALL_SECURE_FILE_PRIVDIR=/opt/${TMPINSTNAME}/mysql-files \
-DINSTALL_MYSQLKEYRINGDIR=/opt/${TMPINSTNAME}/keyring \
-DWITH_BOOST=../boost -DWITH_SYSTEMD=1 \
-DSYSTEMD_SERVICE_NAME=${TMPINSTNAME} \
-DSYSTEMD_PID_DIR=/opt/${TMPINSTNAME}/var/run \
-DSYSCONFDIR=/opt/${TMPINSTNAME} -DWITH_SSL=/opt/openssl

Normally it isn’t recommended to pass the -fpermissive flag to G++ 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.

Important: with some distributions using GCC 7, you will not be able to compile this version of MariaDB. 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 ../ (note that the path update is crucial; I found that simply updating the CC and CXX variables even to the full path of the other GCC installation would still fail).

The SSL option points to the location of OpenSSL. The following will point to the OpenSSL version I recommended installing in the previous post. Alternately, you could use -DWITH_SSL=system (which may be necessary on some platforms). See the note below if you aren’t sure if you need SSL support.

cd ~/mariadb-${TMPVER}/bld
cmake ../ -DCMAKE_CXX_FLAGS=-fpermissive \
-DBUILD_CONFIG=mysql_release \
-DCMAKE_INSTALL_PREFIX=/opt/${TMPINSTNAME} \
-DMYSQL_TCP_PORT=33100 \
-DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DWITH_JEMALLOC=yes -DWITH_PCRE=yes \
-DWITH_SSL=/opt/openssl

Normally it isn’t recommended to pass the -fpermissive flag to G++ 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.

Important: with some distributions using GCC 7, you will not be able to compile this version of MariaDB. 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 ../ (note that the path update is crucial; I found that simply updating the CC and CXX variables even to the full path of the other GCC installation would still fail).

The SSL option points to the location of OpenSSL. The following will point to the OpenSSL version I recommended installing in the previous post. Alternately, you could use -DWITH_SSL=system (which may be necessary on some platforms). See the note below if you aren’t sure if you need SSL support.

cd ~/mariadb-${TMPVER}/bld
cmake ../ -DCMAKE_CXX_FLAGS=-fpermissive \
-DBUILD_CONFIG=mysql_release \
-DCMAKE_INSTALL_PREFIX=/opt/${TMPINSTNAME} \
-DMYSQL_TCP_PORT=33101 \
-DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DINSTALL_SECURE_FILE_PRIVDIR=/opt/${TMPINSTNAME}/mysql-files \
-DINSTALL_MYSQLKEYRINGDIR=/opt/${TMPINSTNAME}/keyring \
-DWITH_SYSTEMD=yes -DWITH_JEMALLOC=yes -DWITH_INNODB_BZIP2=yes \
-DWITH_INNODB_LZ4=yes -DWITH_INNODB_LZMA=yes -DWITH_INNODB_LZO=yes \
-DWITH_PCRE=yes -DGRN_WITH_BUNDLED_LZ4=ON \
-DLZ4_LIBS=storage/mroonga/vendor/groonga/vendor/lz4-1.7.5 \
-DWITH_SSL=/opt/openssl

The SSL option points to the location of OpenSSL. The following will point to the system-provided OpenSSL. Alternately, if you want to try to link against the version I recommended installing in the previous post, you could use -DWITH_SSL=/opt/openssl. See the note below if you aren’t sure if you need SSL support.

cd ~/mariadb-${TMPVER}/bld
cmake ../ \
-DBUILD_CONFIG=mysql_release \
-DCMAKE_INSTALL_PREFIX=/opt/${TMPINSTNAME} \
-DMYSQL_TCP_PORT=33102 \
-DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DINSTALL_SECURE_FILE_PRIVDIR=/opt/${TMPINSTNAME}/mysql-files \
-DINSTALL_MYSQLKEYRINGDIR=/opt/${TMPINSTNAME}/keyring \
-DWITH_SYSTEMD=yes -DWITH_JEMALLOC=yes -DWITH_INNODB_BZIP2=yes \
-DWITH_INNODB_LZ4=yes -DWITH_INNODB_LZMA=yes -DWITH_INNODB_LZO=yes \
-DWITH_PCRE=yes -DGRN_WITH_BUNDLED_LZ4=ON \
-DLZ4_LIBS=storage/mroonga/vendor/groonga/vendor/lz4-1.7.5 \
-DWITH_SSL=system

Although MariaDB 10.2 compiled just fine, I had to revert to using the workarounds I used for 10.0 and 10.1 for . Some systems with GCC 7 required reverting to an older version of GCC by using env "PATH=/opt/gcc6/bin:$PATH" CC=gcc CXX=g++ cmake ../ (or wherever you’ve installed an older version of GCC). And all GCC versions above 4.x required the -fpermissive flag as shown below. Hopefully this will be fixed soon and you will be able to compile without these workarounds. So as of this writing, try the following and if it doesn’t work, do rm -rf * and then pass the new environment to cmake as just mentioned.

The SSL option points to the location of OpenSSL. The following will point to the system-provided OpenSSL. Alternately, if you want to try to link against the version I recommended installing in the previous post, you could use -DWITH_SSL=/opt/openssl. See the note below if you aren’t sure if you need SSL support.

cd ~/mariadb-${TMPVER}/bld
cmake ../ -DCMAKE_CXX_FLAGS=-fpermissive \
-DBUILD_CONFIG=mysql_release \
-DCMAKE_INSTALL_PREFIX=/opt/${TMPINSTNAME} \
-DMYSQL_TCP_PORT=33103 \
-DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DINSTALL_SECURE_FILE_PRIVDIR=/opt/${TMPINSTNAME}/mysql-files \
-DINSTALL_MYSQLKEYRINGDIR=/opt/${TMPINSTNAME}/keyring \
-DWITH_SYSTEMD=yes -DWITH_JEMALLOC=yes -DWITH_INNODB_BZIP2=yes \
-DWITH_INNODB_LZ4=yes -DWITH_INNODB_LZMA=yes -DWITH_INNODB_LZO=yes \
-DWITH_PCRE=yes -DGRN_WITH_BUNDLED_LZ4=ON \
-DLZ4_LIBS=storage/mroonga/vendor/groonga/vendor/lz4-1.7.5 \
-DWITH_SSL=system

Note on SSL: although I have configured all instances with SSL support, for this guide I did not setup certificates. Instead, I left the firewall blocking all external MySQL/MariaDB TCP connections, relying on SSH and port forwarding. Many hosts (including DreamHost where this blog is currently hosted) use this method as well. If you wish to setup SSL, check out this article and this one for more information. I will explain this in more detail in a follow-up post.

If something fails, you can simply rm -rf * and configure again with different options.

Now compile (may take a while):

make

Optionally, test before installing. As of 2017, make test will fail for current versions of MariaDB 10.2+. See this bug report for details. You could skip it for now and try running make test after the MariaDB instance is up and running. (Or run mysql-test/mysql-test-run instead, though I’ve found this will often fail when you are installing with non-standard ports as we are.)

make test

Assuming all tests passed (if run), you can now install.

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

Post-Installation Tasks

Create Init Script

Many distributions are moving towards systemd for their init systems. 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. Unless you are using Arch Linux or a derivitative distribution! Although Arch Linux claims to support SysV, I found it would not initialize older versions. In the future, other distributions may also drop support for SysV. You could install SysVinit as a replacement for systemd, but I recommend just writing a systemd-compatible init script instead. The following seemed to work for me for Arch Linux (based on a script I found in the AUR).

Do not do this for MySQL 5.7+ or MariaDB 10.1+!!!

sudo touch /etc/systemd/system/${TMPINSTNAME}.service
sudo chmod 0644 /etc/systemd/system/${TMPINSTNAME}.service
sudo nano /etc/systemd/system/${TMPINSTNAME}.service

In this file, paste the following, then save the file:

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
After=network.target
After=syslog.target

[Service]
User=mysql
Group=mysql

PIDFile=/opt/TMPINST/data/mysqld.pid

ExecStart=/opt/TMPINST/bin/mysqld --pid-file=/opt/TMPINST/data/mysqld.pid

Restart=always

[Install]
WantedBy=multi-user.target

Now replace the string "TMPINST" with whatever you set as the name of this instance:

sudo sed -i 's/TMPINST/${TMPINSTNAME}/g' /etc/systemd/system/${TMPINSTNAME}.service

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:

sudo chmod +t /tmp
MySQL 5.5MySQL 5.6MySQL 5.7+MariaDB 10.0MariaDB 10.1 & 10.2MariaDB 10.3

Copy a default configuration file (if desired) and create the init script.

cd ~/mysql-${TMPVER}/bld
sudo cp support-files/my-medium.cnf /opt/${TMPINSTNAME}/my.cnf
sudo cp support-files/mysql.server /etc/init.d/${TMPINSTNAME}
sudo chmod +x /etc/init.d/${TMPINSTNAME}
sudo sed -i 's/Provides: mysql/Provides: ${TMPINSTNAME}/g' \
/etc/init.d/${TMPINSTNAME}

Change to the installation directory, create some sub-directories, and set some permissions:

cd /opt/${TMPINSTNAME}
sudo mkdir mysql-files
sudo chmod 750 mysql-files
sudo chown -R mysql:mysql .
sudo scripts/mysql_install_db --user=mysql

Copy a default configuration file (if desired) and create the init script.

cd ~/mysql-${TMPVER}/bld
sudo cp support-files/my-default.cnf /opt/${TMPINSTNAME}/my.cnf
sudo cp support-files/mysql.server /etc/init.d/${TMPINSTNAME}
sudo chmod +x /etc/init.d/${TMPINSTNAME}
sudo sed -i 's/Provides: mysql/Provides: ${TMPINSTNAME}/g' \
/etc/init.d/${TMPINSTNAME}

Change to the installation directory, create some sub-directories, and set some permissions:

cd /opt/${TMPINSTNAME}
sudo mkdir mysql-files
sudo chmod 750 mysql-files
sudo chown -R mysql:mysql .
sudo scripts/mysql_install_db --user=mysql

Create the init script.

cd ~/mysql-${TMPVER}/bld
sudo cp scripts/${TMPINSTNAME}.service /etc/systemd/system/
sudo cp scripts/${TMPINSTNAME}@.service /etc/systemd/system/

Change to the installation directory, create some sub-directories, and set some permissions:

cd /opt/${TMPINSTNAME}
sudo mkdir mysql-files
sudo mkdir -p var/run
sudo mkdir keyring
sudo chmod 750 mysql-files
sudo chmod 750 keyring
sudo chown -R mysql:mysql .
sudo bin/mysqld --initialize-insecure --user=mysql
sudo bin/mysql_ssl_rsa_setup

Copy a default configuration file (if desired) and create the init script.

cd ~/mariadb-${TMPVER}/bld
sudo cp support-files/my-medium.cnf /opt/mariadb100/my.cnf
sudo cp support-files/mysql.server /etc/init.d/mariadb100
sudo chmod +x /etc/init.d/${TMPINSTNAME}
sudo sed -i 's/Provides: mysql/Provides: ${TMPINSTNAME}/g' \
/etc/init.d/${TMPINSTNAME}

Change to the installation directory, create some sub-directories, and set some permissions:

cd /opt/${TMPINSTNAME}
sudo mkdir mysql-files
sudo chmod 750 mysql-files
sudo chown -R mysql:mysql .
sudo scripts/mysql_install_db --user=mysql

Copy a default configuration file (if desired) and create the init script.

cd /opt/${TMPINSTNAME}
sudo cp support-files/my-medium.cnf /opt/${TMPINSTNAME}/my.cnf
sudo cp support-files/systemd/mariadb.service \
/etc/systemd/system/${TMPINSTNAME}.service
sudo cp support-files/systemd/mariadb@.service \
/etc/systemd/system/${TMPINSTNAME}@.service

Create some sub-directories, and set some permissions:

sudo mkdir mysql-files
sudo chmod 750 mysql-files
sudo chown -R mysql:mysql .
sudo scripts/mysql_install_db --user=mysql

Create the init script.

cd /opt/${TMPINSTNAME}
sudo cp support-files/systemd/mariadb.service \
/etc/systemd/system/${TMPINSTNAME}.service
sudo cp support-files/systemd/mariadb@.service \
/etc/systemd/system/${TMPINSTNAME}@.service

Create some sub-directories and set some permissions:

sudo mkdir mysql-files
sudo chmod 750 mysql-files
sudo chown -R mysql:mysql .
sudo scripts/mysql_install_db --user=mysql

Start MySQL/MariaDB

Important: Here’s where SELinux can become an issue on RedHat derivatives. There are a lot of guides out there on troubleshooting SELinux issues like this one and this one and this one, but as of this writing, all you need to do is follow my instructions. This shouldn’t normally be necessary, but I discovered that MariaDB 10.3 is not currently compiling with an SELinux policy package, which caused major issues on Fedora (doesn’t hurt to do it on other platforms with SELinux to be safe). First, set SELinux to permissive mode (obviously this will have no effect on systems without SELinux):

sudo setenforce 0

Now start MySQL/MariaDB:

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

SELinux Fixes

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:

setroubleshoot[3421]: SELinux is preventing mysqld from create access on the sock_file mariadb103.sock. For complete SELinux messages run: sealert -l bdcfb071-cbcc-4a84-be7d-e88029593a02

You can run sealert as stated if you want, but the following should be all you have to do:

cd ~
sudo ausearch -c 'mysqld' --raw | audit2allow -M mysql-selinux
sudo semodule -i mysql-selinux.pp

Now restart the service and check the log again to make sure no more errors were returned (note the times on the messages, since the old ones will still appear):

sudo systemctl restart ${TMPINSTNAME}
sudo cat /var/log/messages | grep setroubleshoot

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:

sudo systemctl stop ${TMPINSTNAME}
sudo setenforce 1
sudo systemctl start ${TMPINSTNAME}
cd /opt/${TMPINSTNAME}

Test and Secure Database

Here are a few simple tests you can run from the installation directory:

sudo bin/mysqladmin -u root version
sudo bin/mysqladmin -u root variables
sudo bin/mysqlshow -u root
bin/mysql -e "SELECT User, Host, plugin FROM mysql.user" mysql -u root

This is the point at which you can try running make test for MariaDB 10.2/10.3 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:

sudo bin/mysql_secure_installation

Populate the timezone table:

bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | bin/mysql -u root -p mysql

Restart the service:

sudo systemctl restart ${TMPINSTNAME}

Cleanup

Move the source files to the appropriate directory and delete the SELinux policy package files (if they exist):

cd ~
sudo mv mysql-${TMPVER} /usr/local/src/
sudo mv mariadb-${TMPVER} /usr/local/src/
rm mysql-selinux.pp mysql-selinux.te

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 mariadb-*.tar.gz mariadb*.patch

If you had to install an older version of ICU as mentioned earlier, doing the following should remove it and allow you to revert to the default installation (worked fine on Arch Linux, but I didn’t test it on other distributions). It may be best to skip this unless you plan to install something that requires a newer version of ICU in the future.

cd /usr/local/bin && sudo rm -rfv derb \
genbrk gencfu gencnval gendict genrb \
icu-config icuinfo makeconv pkgdata uconv \
/usr/local/include/unicode
cd /usr/local/lib && sudo rm -rfv icu \
libicu* pkgconfig/icu*
cd /usr/local/sbin && sudo rm -rfv genccode \
gencmn gennorm2 gensprep icupkg \
/usr/local/share/icu
sudo rm -rfv /usr/local/src/icu

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 "mysql57" to match your service name, such as "mariadb102"). I am not using the variable in these examples since it will be unset next time you log on:

sudo systemctl enable mysql57
sudo systemctl disable mysql57

And you can start/stop/restart or check status as follows:

sudo systemctl start mysql57
sudo systemctl stop mysql57
sudo systemctl restart mysql57
sudo systemctl status mysql57

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/mysql57/man/man8/mysqld.8 /usr/local/man/man8/mysql57.8, or just do man /opt/mysql57/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 simultaneous, 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. You can argue that those issues are almost always caused by poor coding on the part of the developers of those third-party programs (and you would be right), such as not properly declaring variables in the correct scope (the main cause of the issues I encountered). But it doesn’t matter where the blame lies. Using a rolling release distro for a production server is just asking for trouble. There’s my drunken rant for the night.

Leave a Reply