Thursday, July 31, 2014

Install MariaDB with Custom Data Directory in Ubuntu

Here are the steps to install and configure MariaDB (v10.1.0) (a fork of MySQL) with custom data directory in Ubuntu (14.04). The MySQL server will be run as the current logged in user. You can have user as mysql, but then you will have to figure out permissions and such. If you do not want to change the default data directory then it's better to go with mysql-server package from the repository.

I was not able to get MySQL to start after changing the data directory and adding overrides in apparmor. I guess it has to do with the data directory location and permission. Next I tried installing MySQL from source so that I can specify custom directories during install time, but the build fails with this stupid ../libperfschema.a( undefined reference to `__gxx_personality_v0' error. But MariaDB builds and works like a champ.

1. Clone MariaDB source code from github or get the latest release version.
tar xzf mariadb-10.1.0.tar.gz
cd server-mariadb-10.1.0
2. Install dependencies.
sudo apt-get install libboost-dev libjemalloc-dev libjudy-dev bison flex libevent-dev liblzo2-dev liblz4-dev libaio-dev libpam-dev valgrind binutils-dev libatomic-ops-dev
Also check to see if any libraries are not found during the configure process. If so install the dev versions.
3. We will install MariaDB to /opt/mysql/ with data directory as say /home/username/files/mysql. The data directory corresponds to /var/lib/mysql.
cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql -DMYSQL_DATADIR=/home/username/files/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_bin -DMYSQL_UNIX_ADDR=/tmp/mysql.sock
sudo make install
4. Add /opt/mysql/bin and /opt/mysql/support-files to .bashrc and source ~/.bashrc the paths.
5. Create data directory if not exists and install data.
cd && mkdir -p ~/files/mysql && cd files
sudo chown -R username:groupname mysql #logged in user and group
sudo chmod 777 mysql #rw to all. But since it resides inside your home dir, others cannot get into it.
# Install db
sudo /opt/mysql/scripts/mysql_install_db --datadir=/home/username/files/mysql --basedir=/opt/mysql
# Start server
mysql.server start
# Secure the installation with root password, removing test data etc.
sudo /opt/mysql/bin/mysql_secure_installation
6. Login as root.
mysql -uroot -p
You can further configure mysql server using my.cnf file. Example files customized for the current installation will be located at /opt/mysql/support-files. You can copy any of the cnf file to /etc/mysql/my.cnf and make necessary change there. After that restart the mysql server. You can check if the right config file is loading by using strace.
strace mysqld
# .. output will contain
# stat("/etc/my.cnf", 0x7fff4759dc20) = -1 ENOENT (No such file or directory)
# stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=4913, ...}) = 0
# open("/etc/mysql/my.cnf", O_RDONLY) = 3
# ...
If you want to auto start MySQL on system startup, you can move mysql.server to /etc/init.d/ and update runlevel script execution.
sudo cp /opt/mysql/support-files/mysql.server /etc/init.d/mysql && cd /etc/init.d/
# enable auto start
sudo update-rc.d mysql defaults
# disable auto start of mysql
sudo update-rc.d mysql disable
# remove auto start of mysql altogether
sudo update-rc.d mysql remove
Basically having data directory on an encrypted filesystem would be very useful and is recommended (by me) and in such cases, do not auto start MySQL as the data will be unavailable unless you mount it manually. So is the case with your web sever data directory.