LAMP 2018MariaDBMySQL

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 newer MySQL 5.8 and MariaDB 10.3 (not yet in GA status as of this writing) versions.

Tested versions:

  • MySQL 5.5.60
  • MySQL 5.6.40
  • MySQL 5.7.22
  • MySQL 8.0.11
  • MariaDB 10.0.35
  • MariaDB 10.1.32
  • MariaDB 10.2.14
  • MariaDB 10.3.6 RC

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

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 boost boost-devel bzip2-devel \
cracklib-devel git gnutls-devel groonga-devel gzip java-devel \
jemalloc-devel Judy-devel krb5-devel libaio-devel libcurl-devel \
libevent-devel libstemmer-devel libxml2-devel lz4-devel lzo-devel \
mecab-devel msgpack-devel ncurses ncurses-devel ncurses-libs numactl-devel \
openssl-devel pam-devel pcre-devel pcre2-devel perl-DBD-MySQL perl-DBI \
pkgconfig systemd-devel systemtap-sdt-devel unixODBC-devel valgrind-devel \
xinetd zeromq-devel zlib-devel setroubleshoot-server \
libtirpc libtirpc-devel rpcgen

The rpcgen package is not currently available for CentOS (nor is it needed), but it may be required and available in the near future for supporting newer versions of glibc and certain versions of MySQL, so I’m leaving it here.

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

sudo yum remove mariadb
sudo dnf install binutils-devel bison boost boost-devel bzip2-devel \
cracklib-devel git gnutls-devel groonga-devel gzip java-devel \
jemalloc-devel Judy-devel krb5-devel libaio-devel libcurl-devel \
libevent-devel libstemmer-devel libxml2-devel lz4-devel lzo-devel \
mecab-devel msgpack-devel ncurses ncurses-devel ncurses-libs numactl-devel \
openssl-devel pam-devel pcre-devel pcre2-devel perl-DBD-MySQL perl-DBI \
pkgconfig systemd-devel systemtap-sdt-devel unixODBC-devel valgrind-devel \
xinetd zeromq-devel zlib-devel setroubleshoot-server \
libtirpc libtirpc-devel rpcgen

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

sudo dnf remove mariadb
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
printf \
"deb https://packages.groonga.org/debian/ stretch main\ndeb-src https://packages.groonga.org/debian/ stretch 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 krb5 libaio curl libevent libxml2 lz4 msgpack-c ncurses pcre pcre2 \
perl-dbi pkg-config unixodbc valgrind zeromq zlib openssl boost-libs inetutils \
lzo perl-dbd-mysql boost numactl libstemmer pam cracklib rpcsvc-proto \
judy systemtap

I’ve placed Judy and SystemTap last for this distribution, because as of this writing, they are currently unavailable via pacman for the most recent version of Arch. If this is still the case, remove those from the above command and install them via the AUR as follows. (Also note that I found rpcsvc-proto was a necessary package for MySQL 5.7+ although it wasn’t needed in my previous guides.)

For Judy, do:

cd ~
git clone https://aur.archlinux.org/judy.git
cd judy
makepkg -Acs
sudo pacman -U *.pkg.tar.xz
sudo mv *.pkg.tar.xz /usr/local/src/
cd ~
rm -rf judy

For SystemTap, do:

sudo pacman -S python-setuptools
cd ~
git clone https://aur.archlinux.org/systemtap.git
cd systemtap
gpg --recv-key 7E83610126DCC2E8
makepkg -Acs
sudo pacman -U *.pkg.tar.xz
sudo mv *.pkg.tar.xz /usr/local/src/
cd ~
rm -rf systemtap

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

ICU

This section only applies to older versions of Arch Linux (released before this article was written). But I’m leaving it here in case the issue shows up again on future 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, instance name, and port on the first line with whatever you wish to install.

TMPVER="5.7.22" && TMPINSTNAME="mysql57" && TMPPORT="3307"
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.60" && TMPINSTNAME="mysql55" && TMPPORT="3305"
TMPVER="5.6.40" && TMPINSTNAME="mysql56" && TMPPORT="3306"
TMPVER="8.0.11" && TMPINSTNAME="mysql80" && TMPPORT="3308"

(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-*${TMPVER}.tar.gz
rm mysql-*${TMPVER}.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.14" && TMPINSTNAME="mariadb102" && TMPPORT="33102"
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.35" && TMPINSTNAME="mariadb100" && TMPPORT="33100"
TMPVER="10.1.32" && TMPINSTNAME="mariadb101" && TMPPORT="33101"
TMPVER="10.3.6" && TMPINSTNAME="mariadb103" && TMPPORT="33103"

(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 --no-check-certificate 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 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. I found the patch here.

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

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

If you skip this step, you will know you need the patch because the make process will give an error like:

mysql.cc:2708:46: error: ‘CPFunction’ was not declared in this scope

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 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 worked for some systems. You could change this to -DWITH_SSL=/opt/openssl to force it to use the OpenSSL version I recommended installing in the previous post. Or you can omit the SSL option altogether. This version of MySQL has a lot of issues compiling with newer versions of OpenSSL, so don’t be surprised if you are forced to do without or compile an older version to build it against. 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=${TMPPORT} \
-DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DSYSCONFDIR=/etc \
-DWITH_SSL=system

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.

The SSL option points to the location of OpenSSL. The following worked for some systems. You could change this to -DWITH_SSL=/opt/openssl to force it to use the OpenSSL version I recommended installing in the previous post. Or you can omit the SSL option altogether. This version of MySQL has a lot of issues compiling with newer versions of OpenSSL, so don’t be surprised if you are forced to do without or compile an older version to build it against. 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=${TMPPORT} \
-DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DINSTALL_SECURE_FILE_PRIVDIR=/opt/${TMPINSTNAME}/mysql-files \
-DSYSCONFDIR=/etc \
-DWITH_SSL=system

The SSL option points to the location of OpenSSL. The following worked for some systems. You could change this to -DWITH_SSL=/opt/openssl to force it to use the OpenSSL version I recommended installing in the previous post. Or you can omit the SSL option altogether. This version of MySQL has a lot of issues compiling with newer versions of OpenSSL, so don’t be surprised if you are forced to do without or compile an older version to build it against. 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=${TMPPORT} \
-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=/etc \
-DWITH_SSL=system

The SSL option points to the location of OpenSSL. The following worked for most systems. You could change this to -DWITH_SSL=/opt/openssl to force it to use the OpenSSL version I recommended installing in the previous post. Or you can omit the SSL option altogether. 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=${TMPPORT} \
-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=/etc \
-DWITH_SSL=system

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.

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 worked for some systems. You could change this to -DWITH_SSL=/opt/openssl to force it to use the OpenSSL version I recommended installing in the previous post. Or you can omit the SSL option altogether. This version of MariaDB has a lot of issues compiling with newer versions of OpenSSL, so don’t be surprised if you are forced to do without or compile an older version to build it against. 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=${TMPPORT} \
-DMYSQL_UNIX_ADDR=/tmp/${TMPINSTNAME}.sock \
-DWITH_JEMALLOC=yes -DWITH_PCRE=yes \
-DINSTALL_SYSCONFDIR=/etc \
-DWITH_SSL=system

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.

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 worked for some systems. You could change this to -DWITH_SSL=/opt/openssl to force it to use the OpenSSL version I recommended installing in the previous post. Or you can omit the SSL option altogether. This version of MariaDB has a lot of issues compiling with newer versions of OpenSSL, so don’t be surprised if you are forced to do without or compile an older version to build it against. 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=${TMPPORT} \
-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 \
-DINSTALL_SYSCONFDIR=/etc \
-DWITH_SSL=system

The SSL option points to the location of OpenSSL. The following worked for most systems. You could change this to -DWITH_SSL=/opt/openssl to force it to use the OpenSSL version I recommended installing in the previous post. Or you can omit the SSL option altogether. 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=${TMPPORT} \
-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 \
-DINSTALL_SYSCONFDIR=/etc \
-DWITH_SSL=system

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 worked for most systems. You could change this to -DWITH_SSL=/opt/openssl to force it to use the OpenSSL version I recommended installing in the previous post. Or you can omit the SSL option altogether. 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=${TMPPORT} \
-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 \
-DINSTALL_SYSCONFDIR=/etc \
-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. Bottom line is that compiling older versions of MySQL/MariaDB with SSL support may require you to manually compile an older version of OpenSSL, apply a patch, or just give up and compile it without SSL support.

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

Now compile (may take a while):

make VERBOSE=1

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 in theory. But the current versions of Arch Linux and Fedora will not process the SysV init scripts provided by older versions of MySQL and MariaDB! The same could hold true of other distributions in the future. 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) as well as Fedora.

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

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

printf '[client-server]\n!includedir /etc/my.cnf.d\n' \
| sudo tee /etc/my.cnf
sudo mkdir /etc/my.cnf.d

Create a configuration file that will set the desired port and socket for this branch of MySQL/MariaDB:

MySQLMariaDB
TMPTMPVER=$(echo ${TMPVER} | cut -f1,2 -d'.')
printf "[mysqld-${TMPTMPVER}]\nport = ${TMPPORT}\nsocket = /tmp/${TMPINSTNAME}.sock\n" | \
sudo tee /etc/my.cnf.d/00-mysql-${TMPTMPVER}-port.cnf

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.

printf "\nmysqlx_port = ${TMPPORT}0\n" | \
sudo tee -a /etc/my.cnf.d/00-mysql-${TMPTMPVER}-port.cnf
TMPTMPVER=$(echo ${TMPVER} | cut -f1,2 -d'.')
printf "[mariadb-$TMPTMPVER]\nport = $TMPPORT\nsocket = /tmp/${TMPINSTNAME}.sock\n" | \
sudo tee /etc/my.cnf.d/00-mariadb-${TMPTMPVER}-port.cnf

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 5.7+ (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):

[mysqld]
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
server-id       = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

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.5 & 5.6MySQL 5.7+MariaDB 10.0MariaDB 10.1+

Create the init script.

cd ~/mysql-${TMPVER}/bld
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

Create the init script.

cd ~/mariadb-${TMPVER}/bld
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 /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}

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

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 (and patch) 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 \
mariadb-10.3-client-mysql.cc.patch \
mariadb-patches.tar.gz

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.