This covers installing MySQL and MariaDB from source. Skip the sections for versions you don’t wish to support.
Estimated time (this post only): 45 minutes to 6 hours
* depending on how many versions you install
Contents
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.5/10.4 as of this writing. Some older web applications were designed for MySQL 5.5/5.6.
Prerequisites
Here are a few steps you should take only once. This assumes you followed the steps in the last post.
Create a user for running MySQL/MariaDB:
sudo groupadd mysql sudo useradd -r -g mysql -s /bin/false mysql
On some distributions, the /tmp
directory may have permissions set that allow any user to delete all files in it. Fix this:
sudo chmod +t /tmp
Create a symlink for gtar. Not needed for all distributions, so if you get an error that it already exists, just ignore it.
sudo ln -s tar /usr/bin/gtar
Download LZ4 1.7.5 (most likely only needed for MariaDB 10.1). Newer versions of MariaDB should be able to compile with your system-provided LZ4.
cd ~ wget -c -t 10 --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
Create a basic configuration file that does nothing except point to an include directory that will contain our custom configuration files.
printf '[client-server]\n!includedir /etc/my.cnf.d\n' \ | sudo tee /etc/my.cnf sudo mkdir /etc/my.cnf.d
Optional Prerequisites
Optionally, do the following to enable additional features. You can safely skip this in most cases. Again, only do this once if desired.
TMPVER="2.7.0" && TMPTMPVER="2.7" cd ~ wget -c -t 10 \ https://www.cosc.canterbury.ac.nz/greg.ewing/python/Pyrex/Pyrex-0.9.9.tar.gz tar zxvf Pyrex-0.9.9.tar.gz cd Pyrex-0.9.9 sudo python2 ./setup.py install sudo pip install pycrypto sudo pip install paramiko cd ~ wget -c -t 10 \ https://launchpad.net/bzrtools/stable/2.6.0/+download/bzrtools-2.6.0.tar.gz tar zxvf bzrtools-2.6.0.tar.gz cd bzrtools sudo python2 ./setup.py install cd ~ wget -c -t 10 \ https://launchpad.net/bzr/${TMPTMPVER}/${TMPVER}/+download/bzr-${TMPVER}.tar.gz tar zxvf bzr-${TMPVER}.tar.gz cd bzr-${TMPVER} sudo python2 ./setup.py install cd ~ rm bzr-${TMPVER}.tar.gz bzrtools-2.6.0.tar.gz Pyrex-0.9.9.tar.gz sudo mv bzr-${TMPVER} bzrtools Pyrex-0.9.9 /usr/local/src/
GETTMPVER="0.21" && TMPVER="1.16" cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/gettext/gettext-${GETTMPVER}.tar.gz tar zxvf gettext-${GETTMPVER}.tar.gz cd gettext-${GETTMPVER} ./configure --prefix=/usr/local make && make check sudo make install cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/libiconv/libiconv-${TMPVER}.tar.gz tar zxvf libiconv-${TMPVER}.tar.gz cd libiconv-${TMPVER} ./configure --prefix=/usr/local make && make check sudo make install sudo ldconfig cd ~ rm libiconv-${TMPVER}.tar.gz sudo mv libiconv-${TMPVER} /usr/local/src/ cd gettext-${GETTMPVER} make distclean ./configure --prefix=/usr/local make sudo make install cd ~ rm gettext-${GETTMPVER}.tar.gz sudo mv gettext-${GETTMPVER} /usr/local/src/
TMPVER="1.0.5" cd ~ wget -c -t 10 \ https://sourceforge.net/projects/judy/files/judy/Judy-${TMPVER}/Judy-${TMPVER}.tar.gz/download \ -O Judy-${TMPVER}.tar.gz tar zxvf Judy-${TMPVER}.tar.gz cd judy-${TMPVER} ./configure --prefix=/usr/local make && make check sudo make install cd ~ rm Judy-${TMPVER}.tar.gz sudo mv judy-${TMPVER} /usr/local/src/
cd ~ wget https://package.perforce.com/perforce.pubkey gpg -n --import --import-options import-show perforce.pubkey sudo rpm --import https://package.perforce.com/perforce.pubkey rm perforce.pubkey sudo nano /etc/yum.repos.d/perforce.repo
In this file paste the following:
[perforce] name=Perforce baseurl=http://package.perforce.com/yum/rhel/8/x86_64 enabled=1 gpgcheck=1
And install:
sudo dnf install helix-p4d
TMPVER="2.7.0" && TMPTMPVER="2.7" cd ~ wget -c -t 10 \ https://www.cosc.canterbury.ac.nz/greg.ewing/python/Pyrex/Pyrex-0.9.9.tar.gz tar zxvf Pyrex-0.9.9.tar.gz cd Pyrex-0.9.9 sudo python2 ./setup.py install sudo pip install pycrypto sudo pip install paramiko cd ~ wget -c -t 10 \ https://launchpad.net/bzrtools/stable/2.6.0/+download/bzrtools-2.6.0.tar.gz tar zxvf bzrtools-2.6.0.tar.gz cd bzrtools sudo python2 ./setup.py install cd ~ wget -c -t 10 \ https://launchpad.net/bzr/${TMPTMPVER}/${TMPVER}/+download/bzr-${TMPVER}.tar.gz tar zxvf bzr-${TMPVER}.tar.gz cd bzr-${TMPVER} sudo python2 ./setup.py install cd ~ rm bzr-${TMPVER}.tar.gz bzrtools-2.6.0.tar.gz Pyrex-0.9.9.tar.gz sudo mv bzr-${TMPVER} bzrtools Pyrex-0.9.9 /usr/local/src/
GETTMPVER="0.21" && TMPVER="1.16" cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/gettext/gettext-${GETTMPVER}.tar.gz tar zxvf gettext-${GETTMPVER}.tar.gz cd gettext-${GETTMPVER} ./configure --prefix=/usr/local make && make check sudo make install cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/libiconv/libiconv-${TMPVER}.tar.gz tar zxvf libiconv-${TMPVER}.tar.gz cd libiconv-${TMPVER} ./configure --prefix=/usr/local make && make check sudo make install sudo ldconfig cd ~ rm libiconv-${TMPVER}.tar.gz sudo mv libiconv-${TMPVER} /usr/local/src/ cd gettext-${GETTMPVER} make distclean ./configure --prefix=/usr/local make sudo make install cd ~ rm gettext-${GETTMPVER}.tar.gz sudo mv gettext-${GETTMPVER} /usr/local/src/
cd ~ wget https://package.perforce.com/perforce.pubkey gpg -n --import --import-options import-show perforce.pubkey sudo rpm --import https://package.perforce.com/perforce.pubkey rm perforce.pubkey sudo nano /etc/yum.repos.d/perforce.repo
In this file paste the following:
[perforce] name=Perforce baseurl=http://package.perforce.com/yum/rhel/8/x86_64 enabled=1 gpgcheck=1
And install:
sudo dnf install helix-p4d
TMPVER="2.1.27" cd ~ wget -c -t 10 \ https://github.com/cyrusimap/cyrus-sasl/releases/download/cyrus-sasl-${TMPVER}/cyrus-sasl-${TMPVER}.tar.gz tar zxvf cyrus-sasl-${TMPVER}.tar.gz cd cyrus-sasl-${TMPVER} ./configure make sudo make install cd ~ rm cyrus-sasl-${TMPVER}.tar.gz sudo mv cyrus-sasl-${TMPVER} /usr/local/src/
GETTMPVER="0.21" && TMPVER="1.16" cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/gettext/gettext-${GETTMPVER}.tar.gz tar zxvf gettext-${GETTMPVER}.tar.gz cd gettext-${GETTMPVER} ./configure --prefix=/usr/local make && make check sudo make install cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/libiconv/libiconv-${TMPVER}.tar.gz tar zxvf libiconv-${TMPVER}.tar.gz cd libiconv-${TMPVER} ./configure --prefix=/usr/local make && make check sudo make install sudo ldconfig cd ~ rm libiconv-${TMPVER}.tar.gz sudo mv libiconv-${TMPVER} /usr/local/src/ cd gettext-${GETTMPVER} make distclean ./configure --prefix=/usr/local make sudo make install cd ~ rm gettext-${GETTMPVER}.tar.gz sudo mv gettext-${GETTMPVER} /usr/local/src/
cd ~ wget -qO - https://package.perforce.com/perforce.pubkey | sudo apt-key add - sudo nano /etc/apt/sources.list.d/perforce.list
In this file paste the following:
deb http://package.perforce.com/apt/ubuntu focal release
The focal repository isn’t complete as of this writing, so you may need to replace focal
with bionic
. And install:
sudo apt update sudo apt install helix-p4d
TMPVER="2.1.27" cd ~ wget -c -t 10 \ https://github.com/cyrusimap/cyrus-sasl/releases/download/cyrus-sasl-${TMPVER}/cyrus-sasl-${TMPVER}.tar.gz tar zxvf cyrus-sasl-${TMPVER}.tar.gz cd cyrus-sasl-${TMPVER} ./configure make sudo make install cd ~ rm cyrus-sasl-${TMPVER}.tar.gz sudo mv cyrus-sasl-${TMPVER} /usr/local/src/
GETTMPVER="0.21" && TMPVER="1.16" cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/gettext/gettext-${GETTMPVER}.tar.gz tar zxvf gettext-${GETTMPVER}.tar.gz cd gettext-${GETTMPVER} ./configure --prefix=/usr/local make && make check sudo make install cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/libiconv/libiconv-${TMPVER}.tar.gz tar zxvf libiconv-${TMPVER}.tar.gz cd libiconv-${TMPVER} ./configure --prefix=/usr/local make && make check sudo make install sudo ldconfig cd ~ rm libiconv-${TMPVER}.tar.gz sudo mv libiconv-${TMPVER} /usr/local/src/ cd gettext-${GETTMPVER} make distclean ./configure --prefix=/usr/local make sudo make install cd ~ rm gettext-${GETTMPVER}.tar.gz sudo mv gettext-${GETTMPVER} /usr/local/src/
wget -qO - https://adoptopenjdk.jfrog.io/adoptopenjdk/api/gpg/key/public \ | sudo apt-key add - sudo add-apt-repository --yes https://adoptopenjdk.jfrog.io/adoptopenjdk/deb/ sudo apt update sudo apt install adoptopenjdk-8-hotspot
cd ~ wget -qO - https://package.perforce.com/perforce.pubkey | sudo apt-key add - sudo nano /etc/apt/sources.list.d/perforce.list
In this file paste the following:
deb http://package.perforce.com/apt/ubuntu focal release
The focal repository isn’t complete as of this writing, so you may need to replace focal
with bionic
. And install:
sudo apt update sudo apt install helix-p4d
TMPVER="1.74.0" && TMPTMPVER="1_74_0" cd ~ wget -c -t 10 \ https://dl.bintray.com/boostorg/release/${TMPVER}/source/boost_${TMPTMPVER}.tar.gz tar zxvf boost_${TMPTMPVER}.tar.gz cd boost_${TMPTMPVER} ./bootstrap.sh --prefix=/opt/boost ./b2 headers && ./b2 sudo ./b2 install cd .. rm boost_${TMPTMPVER}.tar.gz sudo mv boost_${TMPTMPVER} /usr/local/src/
GETTMPVER="0.21" && TMPVER="1.16" cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/gettext/gettext-${GETTMPVER}.tar.gz tar zxvf gettext-${GETTMPVER}.tar.gz cd gettext-${GETTMPVER} ./configure --prefix=/usr/local make && make check sudo make install cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/libiconv/libiconv-${TMPVER}.tar.gz tar zxvf libiconv-${TMPVER}.tar.gz cd libiconv-${TMPVER} ./configure --prefix=/usr/local make && make check sudo make install sudo ldconfig cd ~ rm libiconv-${TMPVER}.tar.gz sudo mv libiconv-${TMPVER} /usr/local/src/ cd gettext-${GETTMPVER} make distclean ./configure --prefix=/usr/local make sudo make install cd ~ rm gettext-${GETTMPVER}.tar.gz sudo mv gettext-${GETTMPVER} /usr/local/src/
cd ~ wget -c -t 10 \ https://www.perforce.com/downloads/perforce/r20.1/bin.linux26x86_64/helix-core-server.tgz mkdir helix-p4 tar zxvf helix-core-server.tgz -C helix-p4 cd helix-p4 sudo mv p4* /usr/local/bin/ cd ~ rm -rf helix-p4
GETTMPVER="0.21" && TMPVER="1.16" cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/gettext/gettext-${GETTMPVER}.tar.gz tar zxvf gettext-${GETTMPVER}.tar.gz cd gettext-${GETTMPVER} ./configure --prefix=/usr/local make && make check sudo make install cd ~ wget -c -t 10 \ https://ftp.gnu.org/pub/gnu/libiconv/libiconv-${TMPVER}.tar.gz tar zxvf libiconv-${TMPVER}.tar.gz cd libiconv-${TMPVER} ./configure --prefix=/usr/local make && make check sudo make install sudo ldconfig cd ~ rm libiconv-${TMPVER}.tar.gz sudo mv libiconv-${TMPVER} /usr/local/src/ cd gettext-${GETTMPVER} make distclean ./configure --prefix=/usr/local make sudo make install cd ~ rm gettext-${GETTMPVER}.tar.gz sudo mv gettext-${GETTMPVER} /usr/local/src/
cd ~ wget -c -t 10 \ https://www.perforce.com/downloads/perforce/r20.1/bin.linux26x86_64/helix-core-server.tgz mkdir helix-p4 tar zxvf helix-core-server.tgz -C helix-p4 cd helix-p4 sudo mv p4* /usr/local/bin/ cd ~ rm -rf helix-p4
Download Source
The source files will take up a lot of disk space! So I 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
. If this is acceptable to you, you can simply copy/paste the commands below or edit them as desired.
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.
MySQL requires 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="8.0.22" && TMPINSTNAME="mysql80" && TMPPORT="3308" 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 -c -t 10 \ https://cdn.mysql.com/Downloads/MySQL-$(echo $TMPVER | \ sed -e 's/\.[^\.]*$//')/mysql-boost-${TMPVER}.tar.gz; \ else wget -c -t 10 \ 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.6.50" && TMPINSTNAME="mysql56" && TMPPORT="3306" TMPVER="5.7.32" && TMPINSTNAME="mysql57" && TMPPORT="3307"
(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.
TMPVER="10.5.6" && TMPINSTNAME="mariadb105" && TMPPORT="33105" cd ~ wget -c -t 10 \ 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.1.47" && TMPINSTNAME="mariadb101" && TMPPORT="33101" TMPVER="10.2.34" && TMPINSTNAME="mariadb102" && TMPPORT="33102" TMPVER="10.3.25" && TMPINSTNAME="mariadb103" && TMPPORT="33103" TMPVER="10.4.15" && TMPINSTNAME="mariadb104" && TMPPORT="33104"
(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. You could skip the last command for MariaDB 10.2+.
tar zxvf mariadb-${TMPVER}.tar.gz rm mariadb-${TMPVER}.tar.gz mkdir -v mariadb-${TMPVER}/bld cp -rv lz4-1.7.5 mariadb-${TMPVER}/storage/mroonga/vendor/groonga/vendor/
Patches
I found this necessary for MariaDB 10.1 through 10.3 under most distributions (except openSUSE). If not needed, skip this!
You will know you need the patch if the make process gives an error like:
mysql.cc:2708:46: error: ‘CPFunction’ was not declared in this scope
I found the patch here. You can’t just download that patch unless you make changes to the paths, but I’ve already done the work for you. Just do the following (I take no credit for the patch itself; I’ve only fixed it to work with multiple versions of MariaDB). If the patch fails to apply, you can edit it manually, create your own patch, or just edit client/mysql.cc
for yourself.
cd ~ wget -c -t 10 \ https://supremerulerofearth.com/downloads/client-mysql.cc.tar.gz tar zxvf client-mysql.cc.tar.gz cp client-mysql.cc.patch mariadb-${TMPVER}/client/ cd mariadb-${TMPVER}/client patch < client-mysql.cc.patch rm -rf ~/mariadb-${TMPVER}/bld/*
Install
If you wish to see all the cmake options and their defaults, you can do:
cmake ~/mariadb-${TMPVER}/ -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 ../
.
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.
Normally it isn’t recommended to pass the -fpermissive
flag since it allows nonconformant code. However, these older versions of MySQL had nonconforming code so you need this flag.
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
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
I was unable to get this version of MariaDB to compile with support for Cassandra no matter which version of Boost I installed. It wasn’t worth spending a lot of time on since I didn’t need this feature, so I passed an option to disable the Cassandra plugin. If you need Cassandra support, you could try omitting -DPLUGIN_CASSANDRA=NO
, which may work on some distributions.
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=ON -DWITH_INNODB_BZIP2=ON \ -DWITH_INNODB_LZ4=ON -DWITH_INNODB_LZMA=ON -DWITH_INNODB_LZO=ON \ -DWITH_PCRE=bundled -DINSTALL_SYSCONFDIR=/etc \ -DZLIB_INCLUDE_DIR=/usr/include \ -DGLIBTOOLIZE_EXECUTABLE=/usr/bin/libtoolize \ -DGTAR_EXECUTABLE=/usr/bin/tar \ -DGRN_WITH_BUNDLED_LZ4=ON -DLIBLZ4_VERSION=1.7.5 \ -DLZ4_LIBS=storage/mroonga/vendor/groonga/vendor/lz4-1.7.5 \ -DPLUGIN_CASSANDRA=NO -DWITH_SSL=bundled
I was unable to get these versions of MariaDB to compile with support for Cassandra no matter which version of Boost I installed. It wasn’t worth spending a lot of time on since I didn’t need this feature, so I passed an option to disable the Cassandra plugin. If you need Cassandra support, you could try omitting -DPLUGIN_CASSANDRA=NO
, which may work on some distributions.
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=ON -DWITH_INNODB_BZIP2=ON \ -DWITH_INNODB_LZ4=ON -DWITH_INNODB_LZMA=ON -DWITH_INNODB_LZO=ON \ -DWITH_PCRE=bundled -DINSTALL_SYSCONFDIR=/etc \ -DZLIB_INCLUDE_DIR=/usr/include \ -DGLIBTOOLIZE_EXECUTABLE=/usr/bin/libtoolize \ -DGTAR_EXECUTABLE=/usr/bin/tar \ -DPLUGIN_CASSANDRA=NO -DWITH_SSL=system
If this version fails to compile with Cassandra, you can try adding the option -DPLUGIN_CASSANDRA=NO
.
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=ON -DWITH_INNODB_BZIP2=ON \ -DWITH_INNODB_LZ4=ON -DWITH_INNODB_LZMA=ON -DWITH_INNODB_LZO=ON \ -DWITH_PCRE=bundled -DINSTALL_SYSCONFDIR=/etc \ -DZLIB_INCLUDE_DIR=/usr/include \ -DGLIBTOOLIZE_EXECUTABLE=/usr/bin/libtoolize \ -DGTAR_EXECUTABLE=/usr/bin/tar \ -DWITH_SSL=system
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. This will take a while.
export LC_ALL=C LANG=C make -j$(nproc) VERBOSE=1
Don’t bother running the following command for MariaDB 10.2+. See this bug report for details.
make test
Now complete the installation:
cd ~/mysql-${TMPVER}/bld sudo make package sudo tar -zxvf mysql-${TMPVER}*.tar.gz -C /opt sudo mv -v /opt/mysql-${TMPVER}* /opt/${TMPINSTNAME}
cd ~/mariadb-${TMPVER}/bld sudo make package sudo tar -zxvf mariadb-${TMPVER}*.tar.gz -C /opt sudo mv -v /opt/mariadb-${TMPVER}* /opt/${TMPINSTNAME}
Post-Installation Tasks
Create Options Files
Create a configuration file that will set the desired port and socket for this branch of MySQL/MariaDB:
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/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):
[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
Create some sub-directories and set some permissions:
cd /opt/${TMPINSTNAME} sudo mkdir mysql-files sudo mkdir -p var/run sudo chmod 750 mysql-files sudo chown -R mysql:mysql . sudo scripts/mysql_install_db --user=mysql
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 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 scripts/mysql_install_db --user=mysql
Start MySQL/MariaDB
MySQL 5.6 still uses SysV init scripts. Most distributions will still start/stop MySQL 5.6, but may not be able to enable/disable it. This means it won’t start up on boot without a workaround.
cd /opt/${TMPINSTNAME} sudo mkdir -pv /etc/init.d sudo cp support-files/mysql.server /etc/init.d/${TMPINSTNAME}
Important: Arch Linux no longer contains systemd-sysv-generator
, so it won’t generate a SysV init script wrapper automatically. Other distributions may remove the generator in the future. To work around this, create your own wrapper. Only do this if necessary.
sudo touch /usr/lib/systemd/system/${TMPINSTNAME}.service sudo nano /usr/lib/systemd/system/${TMPINSTNAME}.service
In this file paste the following. Edit as necessary.
[Unit] SourcePath=/etc/init.d/mysql56 Description=A sysV init script After=remote-fs.target systemd-journald-dev-log.socket [Service] Type=forking Restart=no TimeoutSec=5min IgnoreSIGPIPE=no KillMode=process GuessMainPID=no RemainAfterExit=yes ExecStart=/etc/init.d/mysql56 start ExecStop=/etc/init.d/mysql56 stop
Copy the systemd service.
cd ~/mysql-${TMPVER}/bld sudo cp scripts/${TMPINSTNAME}.service /usr/lib/systemd/system/ sudo cp scripts/${TMPINSTNAME}@.service /usr/lib/systemd/system/
Copy the systemd service.
cd /opt/${TMPINSTNAME} sudo cp support-files/systemd/mariadb.service \ /usr/lib/systemd/system/${TMPINSTNAME}.service sudo cp support-files/systemd/mariadb@.service \ /usr/lib/systemd/system/${TMPINSTNAME}@.service
To start MySQL/MariaDB:
sudo systemctl daemon-reload sudo systemctl start ${TMPINSTNAME}
SELinux Fixes
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 development there were problems with SELinux, especially 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:
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
You can now do the following, which will take a while (may only be useful for MariaDB 10.2+):
cd /opt/${TMPINSTNAME} cd './mysql-test' ; sudo perl mysql-test-run.pl
Whether or not you ran the above test, do the following to show some system tables:
cd /opt/${TMPINSTNAME} sudo bin/mysqladmin -u root version sudo bin/mysqladmin -u root variables sudo bin/mysqlshow -u root sudo bin/mysql -e "SELECT User, Host, plugin FROM mysql.user" mysql -u root
Now secure the installation:
You can go with the defaults for the questions you will be asked (keep hitting <Enter>), setting the MySQL root password when requested:
cd /opt/${TMPINSTNAME} 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:
cd /opt/${TMPINSTNAME} sudo bin/mysql_secure_installation
Populate the timezone table:
cd /opt/${TMPINSTNAME} bin/mysql_tzinfo_to_sql \ /usr/share/zoneinfo | bin/mysql -u root -p mysql
Restart the service:
sudo systemctl restart ${TMPINSTNAME}
Cleanup
cd ~/mysql-${TMPVER}/bld sudo mv -v mysql-${TMPVER}*.tar.gz /usr/local/src/ cd ~ sudo rm -rf mysql-${TMPVER}
cd ~/mariadb-${TMPVER}/bld sudo mv -v mariadb-${TMPVER}*.tar.gz /usr/local/src/ cd ~ sudo rm -rf mariadb-${TMPVER}
If you’re done compiling all versions of MariaDB…
rm client-mysql.cc.* rm -rf lz4-*
Starting/Stopping
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"):
sudo systemctl enable mysql80 sudo systemctl disable mysql80
And you can start/stop/restart or check status as follows:
sudo systemctl start mysql80 sudo systemctl stop mysql80 sudo systemctl restart mysql80 sudo systemctl status mysql80
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.
Later we will address installing other common databases, though this is entirely optional depending on your needs.