Creating LAMP VM – MySQL/MariaDB

This is part of a series of posts on creating your own LAMP virtual machine for development purposes. Here we will cover the installation of MySQL and MariaDB. I plan to use the PDO abstraction layer which allows us to develop PHP applications for multiple databases (which will be covered in the next post of this series). You can safely 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.

Important note: because these instructions will cover the installation of many databases, we will be assigning a different TCP port for each. I have taken great pains to ensure these do not conflict with any other standard ports used by any software in-use today. But if you have something else installed on your VM that does happen to use one of these ports, you may need to alter your configuration accordingly.

Estimated time (this post only): 2 hours, 30 minutes
* if installing everything

MySQL

MySQL has been the #1 DBMS for web applications for years. However, it is quickly losing support due to frustration with the direction Oracle has taken the open-source version. As it is still what many web applications use by default, I wanted to install at least the last two versions of it side by side.

MySQL – The Easy Way

If you only need to develop for one version of MySQL, you can simply install it with yum. You can view the guide for yourself: http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

If you do choose this easy method, skip the rest of the MySQL sections below.

MySQL Prerequisites

First make sure we have some packages we will need and setup a user for MySQL to run as:

sudo yum install ncurses ncurses-libs ncurses-devel gcc-c++ perl-DBI cmake libaio-devel bison git
sudo useradd -r mysql

You also should remove the default installation of MariaDB (we will install it again later if desired):

sudo yum remove mariadb*

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

sudo rm -f /etc/my.cnf

Now let’s install each version of MySQL using a different directory, TCP port, and socket path.

MySQL 5.1

Although it is nearing its end-of-life, this version is still technically supported. Personally, I wouldn’t recommend developing for it anymore, but if you need to for some reason, follow the instructions in this section to install it.

Follow the links on the MySQL website from your graphical browser on your host system to get the source code TAR file for MySQL 5.1 and save it to your shared directory we set up earlier.

Extract the archive to the working directory. Then configure, make, and install. Change directory and file names in the first set of commands as needed:

cd /usr/local/src
sudo tar -zxvf /media/sf_Temp/mysql-5.1.73.tar.gz
cd mysql-5.1.73

sudo CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" \
./configure --prefix=/opt/mysql51 --exec-prefix=/opt/mysql51 --enable-assembler \
--with-big-tables --with-mysqld-user=mysql \
--with-named-curses-libs=/usr/lib64/libncurses.so \
--with-mysqld-ldflags=-all-static --with-ssl \
--with-tcp-port=3301 --with-unix-socket-path=/tmp/mysql51.sock

sudo make
sudo make install
sudo mkdir /opt/mysql51/var
sudo cp support-files/my-medium.cnf /opt/mysql51/my.cnf
sudo cp support-files/mysql.server /etc/init.d/mysql51
sudo chmod +x /etc/init.d/mysql51
sudo chkconfig --add mysql51 # skip if you don't want this version to load at startup
cd /opt/mysql51
sudo chown -R mysql .
sudo chgrp -R mysql .
sudo bin/mysql_install_db --user=mysql --basedir=/opt/mysql51 --datadir=/opt/mysql51/var
sudo chown root lib/mysql
sudo service mysql51 start
sudo bin/mysql_secure_installation

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

Test the installation (enter password when prompted):

bin/mysqladmin version -u root -p
bin/mysqlshow -u root -p
bin/mysql -e "SELECT Host,User,Password FROM user" mysql -u root -p

Populate the timezone table and restart MySQL:

bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | bin/mysql -u root -p mysql
sudo service mysql51 restart

MySQL 5.5

This is more useful than 5.1 since some servers out there are still running the 5.5 branch of MySQL. But since we want to install it alongside 5.6 (and possibly the development 5.7 version), we will need to install it from source as well, again assigning it its own directory and port.

Follow the links on the MySQL website from your graphical browser on your host system to get the source code TAR file for MySQL 5.5 and save it to your shared directory we set up earlier.

Extract the archive to the working directory. Then configure, make, and install. Change directory and file names in the first set of commands as needed:

cd /usr/local/src
sudo tar -zxvf /media/sf_Temp/mysql-5.5.45.tar.gz
cd mysql-5.5.45

sudo cmake . -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/opt/mysql55 \
-DCURSES_LIBRARY=/usr/lib64/libncurses.so -DMYSQL_DATADIR=/opt/mysql55/data \
-DMYSQL_TCP_PORT=3305 -DMYSQL_UNIX_ADDR=/tmp/mysql55.sock \
-DWITH_SSL=bundled -DWITH_INNOBASE_STORAGE_ENGINE=1

sudo make
sudo make install
sudo cp support-files/my-medium.cnf /opt/mysql55/my.cnf
sudo cp support-files/mysql.server /etc/init.d/mysql55
sudo chmod +x /etc/init.d/mysql55
sudo chkconfig --add mysql55 # skip if you don't want this version to load at startup
cd /opt/mysql55
sudo chown -R mysql .
sudo chgrp -R mysql .
sudo scripts/mysql_install_db --user=mysql --basedir=/opt/mysql55 --datadir=/opt/mysql55/data
sudo chown root lib/plugin
sudo service mysql55 start
sudo bin/mysql_secure_installation

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

Test the installation (enter password when prompted):

bin/mysqladmin version -u root -p
bin/mysqlshow -u root -p
bin/mysql -e "SELECT Host,User,Password FROM user" mysql -u root -p

Populate the timezone table and restart MySQL:

bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | bin/mysql -u root -p mysql
sudo service mysql55 restart

MySQL 5.6

As of this writing, this is the current general availability release of MySQL. You may have noticed I am using port 330x (where x corresponds to MySQL 5.x) for each version of MySQL, and 3306 happens to be the default port for MySQL. A happy coincidence for the purposes of this guide.

Follow the links on the MySQL website from your graphical browser on your host system to get the source code TAR file for MySQL 5.6 and save it to your shared directory we set up earlier.

Extract the archive to the working directory. Then configure, make, and install. Change directory and file names in the first set of commands as needed. You will notice this is the exact same process as for 5.5:

cd /usr/local/src
sudo tar -zxvf /media/sf_Temp/mysql-5.6.26.tar.gz
cd mysql-5.6.26

sudo cmake . -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/opt/mysql56 \
-DCURSES_LIBRARY=/usr/lib64/libncurses.so -DMYSQL_DATADIR=/opt/mysql56/data \
-DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql56.sock \
-DWITH_SSL=bundled -DWITH_INNOBASE_STORAGE_ENGINE=1

sudo make
sudo make install
sudo cp support-files/my-default.cnf /opt/mysql56/my.cnf
sudo cp support-files/mysql.server /etc/init.d/mysql56
sudo chmod +x /etc/init.d/mysql56
sudo chkconfig --add mysql56 # skip if you don't want this version to load at startup
cd /opt/mysql56
sudo chown -R mysql .
sudo chgrp -R mysql .
sudo scripts/mysql_install_db --user=mysql --basedir=/opt/mysql56 --datadir=/opt/mysql56/data
sudo chown root lib/plugin
sudo service mysql56 start
sudo bin/mysql_secure_installation

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

Test the installation (enter password when prompted):

bin/mysqladmin version -u root -p
bin/mysqlshow -u root -p
bin/mysql -e "SELECT Host,User,Password FROM user" mysql -u root -p

Populate the timezone table and restart MySQL:

bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | bin/mysql -u root -p mysql
sudo service mysql56 restart

MySQL 5.7

You can also install the development version (not yet in GA status) if you want to develop for the next version of MySQL. Download from the MySQL website again. Then do the following. Note that 5.7 requires a specific version of boost to build, which is why we have to append some options to the cmake line below. The long timeout I added for it is because I encountered a timeout issue with a 150 Mbps connection (though this was likely due to temporary issues with the download server itself). The make process took especially long with this version in my testing (seriously, take a nap):

cd /usr/local/src
sudo tar -zxvf /media/sf_Temp/mysql-5.7.7-rc.tar.gz
cd mysql-5.7.7-rc

sudo cmake . -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/opt/mysql57 \
-DCURSES_LIBRARY=/usr/lib64/libncurses.so -DMYSQL_DATADIR=/opt/mysql57/data \
-DMYSQL_TCP_PORT=3307 -DMYSQL_UNIX_ADDR=/tmp/mysql57.sock \
-DWITH_SSL=bundled -DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DDOWNLOAD_BOOST=1 -DDOWNLOAD_BOOST_TIMEOUT=3600 -DWITH_BOOST=./boost

sudo make
sudo make install
sudo cp support-files/my-default.cnf /opt/mysql57/my.cnf
sudo cp support-files/mysql.server /etc/init.d/mysql57
sudo chmod +x /etc/init.d/mysql57
sudo chkconfig --add mysql57 # skip if you don't want this version to load at startup
cd /opt/mysql57
sudo chown -R mysql .
sudo chgrp -R mysql .
sudo bin/mysqld --initialize-insecure --user=mysql
sudo service mysql57 start
bin/mysql -u root

Now set a new root password manually as follows (replacing “newpwd” with whatever password you want):

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpwd';
exit

Now continue the post-installation tasks:

sudo bin/mysql_ssl_rsa_setup
sudo bin/mysql_secure_installation

Unlike with the other 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 “yes” to everything except setting a new root password. This is critical because at least with the version I tested, this script seemed to corrupt the root password, which is why you set it manually earlier. Now, test that everything worked and populate the time zone table:

bin/mysqladmin version -u root -p
bin/mysqlshow -u root -p
bin/mysql -e "SELECT Host,User,HEX(authentication_string) FROM user" mysql -u root -p

bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | bin/mysql -u root -p mysql
sudo service mysql57 restart

Starting/Stopping MySQL

By following the above steps you can easily add/remove any version of MySQL during startup by doing:

sudo chkconfig mysqlxx on
sudo chkconfig mysqlxx off

And you can start/stop/restart:

sudo service mysqlxx start
sudo service mysqlxx stop
sudo service mysqlxx restart

MariaDB

MariaDB is quickly becoming a preferred alternative to MySQL. It is based on the same source code, which makes it highly compatible, but includes many new features as well as security and performance improvements. It likely won’t be long before it becomes common on most shared hosts since Red Hat/CentOS 7 now use it by default (as do openSUSE, Slackware, Fedora, etc.). If you want all the gory details, you can check out their website, but the short version is that MariaDB 5.5 is comparable to MySQL 5.5 and MariaDB 10.0 is arguably comparable to MySQL 5.6. I say arguably because MariaDB 10 adds a lot of features which make it stand even further apart from MySQL than previous versions (presumably why they have changed the version number). For most developers, this shouldn’t cause any issues. But if you are developing a web application, you should definitely start testing against MariaDB 10.0 (or even 10.1) now. I wouldn’t bother developing for 5.5 because distributions didn’t start using MariaDB over MySQL until 10.0 was released.

Do not discount the importance of supporting MariaDB! Over the last few years a few small companies by the names of Google, Wikipedia, Tumblr, and many others have transitioned to it to replace MySQL.

MariaDB – The Easy Way

As with MySQL, you can easily install a single instance of the latest general availability release of MariaDB from a repository with yum. Just visit this site and follow the links for your version of CentOS. If you have MySQL installed and/or want to test more than one version of MariaDB, this isn’t recommended as it may cause conflicts that can’t be resolved by simply modifying my.cnf.

MariaDB Prerequisites

If you followed the prerequisite instructions for MySQL you probably have most of this already, but there is at least one extra package you need for MariaDB 10.0:

sudo yum install jemalloc-devel ncurses ncurses-libs ncurses-devel gcc-c++ perl-DBI cmake libaio-devel bison git

MariaDB 10.0

The instructions for compiling MariaDB 10.0 from source will be essentially similar to those for MySQL 5.6 above with a few minor differences. We will of course be using a different port number in order to avoid possible conflicts in case MySQL is still running when you start it.

Follow the links on the MariaDB website from your graphical browser on your host system to get the source code TAR file for MariaDB 10.0 and save it to your shared directory we set up earlier.

Now extract the archive to the working directory. Then configure, make, and install. Change directory and file names in the first set of commands as needed.

cd /usr/local/src
sudo tar -zxvf /media/sf_Temp/mariadb-10.0.20.tar.gz
cd mariadb-10.0.20

sudo cmake . -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/opt/mariadb100 \
-DCURSES_LIBRARY=/usr/lib64/libncurses.so -DMYSQL_DATADIR=/opt/mariadb100/data \
-DMYSQL_TCP_PORT=33100 -DMYSQL_UNIX_ADDR=/tmp/mariadb100.sock \
-DWITH_SSL=bundled -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_JEMALLOC=yes

sudo make
sudo make install
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/mariadb100
sudo chkconfig --add mariadb100 # skip if you don't want this version to load at startup
cd /opt/mariadb100
sudo chown -R mysql .
sudo chgrp -R mysql .
sudo scripts/mysql_install_db --user=mysql --basedir=/opt/mariadb100 --datadir=/opt/mariadb100/data
sudo chown root lib/plugin
sudo service mariadb100 start
cd bin
sudo ./mysql_secure_installation

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

Test the installation (enter password when prompted):

./mysqladmin version -u root -p
./mysqlshow -u root -p
./mysql -e "SELECT Host,User,Password FROM user" mysql -u root -p

Populate the timezone table and restart MySQL:

./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -u root -p mysql
sudo service mariadb100 restart

MariaDB 10.1

You can also install the development version (not yet in GA status) if you want to develop for the next version of MariaDB. Download the source code from the MariaDB website again. Then do the following (as usual changing directory and file names as needed):

cd /usr/local/src
sudo tar -zxvf /media/sf_Temp/mariadb-10.1.6.tar.gz
cd mariadb-10.1.6

sudo cmake . -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=/opt/mariadb101 \
-DCURSES_LIBRARY=/usr/lib64/libncurses.so -DMYSQL_DATADIR=/opt/mariadb101/data \
-DMYSQL_TCP_PORT=33101 -DMYSQL_UNIX_ADDR=/tmp/mariadb101.sock \
-DWITH_SSL=bundled -DWITH_INNOBASE_STORAGE_ENGINE=1

sudo make
sudo make install
sudo cp support-files/my-medium.cnf /opt/mariadb101/my.cnf
sudo cp support-files/mysql.server /etc/init.d/mariadb101
sudo chmod +x /etc/init.d/mariadb101
sudo chkconfig --add mariadb101 # skip if you don't want this version to load at startup
cd /opt/mariadb101
sudo chown -R mysql .
sudo chgrp -R mysql .
sudo scripts/mysql_install_db --user=mysql --basedir=/opt/mariadb101 --datadir=/opt/mariadb101/data
sudo chown root lib/plugin
sudo service mariadb101 start
cd bin
sudo ./mysql_secure_installation

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

Test the installation (enter password when prompted):

./mysqladmin version -u root -p
./mysqlshow -u root -p
./mysql -e "SELECT Host,User,Password FROM user" mysql -u root -p

Populate the timezone table and restart MySQL:

./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -u root -p mysql
sudo service mariadb101 restart

Starting/Stopping MariaDB

By following the above steps you can easily add/remove any version of MariaDB during startup by doing:

sudo chkconfig mariadbxxx on
sudo chkconfig mariadbxxx off

And you can start/stop/restart:

sudo service mariadbxxx start
sudo service mariadbxxx stop
sudo service mariadbxxx restart

Leave a Reply