MySQL CI

MySQL-CI uses a ramdisk (tmpfs) to temporarly store the databases. this can be used for Continuous Integration like running automated tests with jenkins. To not damage the daily business, this daemon has it's own socket and listens also on a seperate port. Therefore a standard MySQL installation is needed.

This guide describes the manual steps based on a default MySQL-Installation. Attached you'll find also a package to automatically install on a debian based system.

Precondition for manual installation

• Installed and running MySQL Server
• root access
• tmpfs support in kernel
• Configuration in /etc/mysql
• Boot scripts in /etc/init.d

Copy all needed files to a new location:

[email protected]:/root # cp -rp /etc/mysql/conf.d /etc/mysql/conf.d-ci [email protected]:/root # cp -p /etc/mysql/my.cnf /etc/mysql/my-ci.cnf [email protected]:/root # cp -p /etc/mysql/debian.cnf /etc/mysql/debian-ci.cnf [email protected]:/root # cp -p /etc/mysql/debian-start /etc/mysql/debian-ci-start [email protected]:/root # cp -p /etc/init.d/mysql /etc/init.d/mysql-ci

Create needed directories and change the permissions:

[email protected]:/root # mkdir -p /var/log/mysql-ci [email protected]:/root # mkdir -p /var/run/mysqld-ci [email protected]:/root # mkdir -p /var/lib/mysql-ci [email protected]:/root # chown mysql /var/log/mysql-ci /var/run/mysqld-ci [email protected]:/root # chown mysql.mysql /var/lib/mysql-ci (You can also create all folders in one step by just give them seperated by a space.)

File: /etc/mysql/my-ci.cnf

Change all «mysql» strings in any path definitions to «mysql-ci». Here just the most important parts: [client] port = 3307 socket = /var/run/mysqld-ci/mysqld.sock

[mysqld_safe] socket = /var/run/mysqld-ci/mysqld.sock

[mysqld] datadir = /var/lib/mysql-ci/ tmpdir = /var/lib/mysql-ci/tmp general_log_file = /var/lib/mysql-ci/log/mysql.log

log_bin = /var/lib/mysql-ci/log/mysql-bin.log

!includedir /etc/mysql/conf.d-ci/

File: /etc/mysql/debian-ci.cnf

Change the socket parameter from «mysql» to «mysql-ci»: [client] socket = /var/run/mysqld-ci/mysqld.sock ... [mysql_upgrade] socket = /var/run/mysqld-ci/mysqld.sock

File: /etc/mysql/debian-ci-start

Change here all references to the configuration files to the mysql-ci ones: ... MYSQL="/usr/bin/mysql --defaults-file=/etc/mysql/debian-ci.cnf" MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian-ci.cnf" MYUPGRADE="/usr/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian-ci.cnf" MYCHECK="/usr/bin/mysqlcheck --defaults-file=/etc/mysql/debian-ci.cnf" ...

File: /etc/mysql/conf.d-ci/performance.cnf

Change - or maybe disable - the «slow_query_log_file» to the mysql-ci log dir: ... slow_query_log_file = /var/log/mysql-ci/mysql-slow.log ...

Inside the Startup-Script some more changes are needed. All references to the old configuration files has to be changed, but also functions to mount and unmount the ramdisk and to initialize a blank database is needed. These steps shows always some lines above and below are shown.

Header and additional information

Change this values on top of the startscript:

!/bin/bash

#

BEGIN INIT INFO

Provides: mysql-ci

… CONF=/etc/mysql/my-ci.cnf MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/my-ci.cnf --defaults-extra-file=/etc/mysql/debian-ci.cnf" TMPFS_SIZE="4G" _UID="mysql" _GID="mysql"

priority can be overriden and "-s" adds output to stderr

ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql-ci -i"

Modify existing funcitons

#

mysqld_get_param() { /usr/sbin/mysqld --defaults-file=/etc/mysql/my-ci.cnf --print-defaults \ ...

sanity_checks() { # check for config file if [ ! -r /etc/mysql/my-ci.cnf ]; then log_warning_msg "$0: WARNING: /etc/mysql/my-ci.cnf cannot be read. See README.Debian.gz" echo "WARNING: /etc/mysql/my-ci.cnf cannot be read. See README.Debian.gz" | $ERR_LOGGER …

Change start- and stop-calls

Inside the start cach block we need to mount the ramdisk and initialize the database. Also we need to unmount the ramdisk first if it's mounted already: case "${1:-''}" in 'start') mysqld_unmount "silent"; mysqld_mount; mysqld_install_db; sanity_checks; ... # Could be removed during boot test -e /var/run/mysqld-ci || install -m 755 -o mysql -g root -d /var/run/mysqld-ci

# Start MySQL! /usr/bin/mysqld_safe *--defaults-file=/etc/mysql/my-ci.cnf* > /dev/null 2>&1 &

… if mysqld_status check_alive warn; then log_end_msg 0 # Now start mysqlcheck or whatever the admin wants. mysqld_grant output=$(/etc/mysql/debian-ci-start) [ -n "$output" ] && log_action_msg "$output"

Inside the stop catch, we have to stop the MySQL-CI server and after unmount the ramdisk:

'stop') … pidfile=mysqld_get_param pid-file if [ "$r" -ne 0 ]; then # log_end_msg 1 VERBOSE=no [ "$VERBOSE" != "no" ] && log_failure_msg "Error: $shutdown_out" log_daemon_msg "Killing MySQL database server by signal" "mysqld" kill -s 15 cat $pidfile … if test -z "$server_down"; then kill -s 9 cat $pidfile; fi … else mysqld_unmount; log_end_msg 0 fi ;;

Add functions to (un)mount a ramdisk

Add these function somwhere above the main catch block. As best, just below the sanity_checks() function: #

# mysqld_unmount () { set +e LOG=${1-} [ ! -n "${LOG}" ] && log_progress_msg "Unmounting TMPFS: " datadir=mysqld_get_param datadir mount | grep $datadir &>/dev/null if [ $? -eq 0 ]; then umount $datadir [ ! -n "${LOG}" ] && log_progress_msg "unmounted; " else [ ! -n "${LOG}" ] && log_progress_msg "not mounted; " fi set -e }

#

# mysqld_mount () { set +e log_progress_msg "Mounting TMPFS: " datadir=mysqld_get_param datadir mount | grep $datadir &>/dev/null if [ $? -gt 0 ]; then mount -t tmpfs -o size=${TMPFS_SIZE},nr_inodes=10k,mode=0700,uid=${_UID},gid=${_GID} tmpfs $datadir log_progress_msg "mounted; " else log_progress_msg "already mounted; " fi set -e }

Add functions to initialize a database

Add these function to initialize a blank and fresh database just above the main catch-block and below the mount/unmount function from above: #

# mysqld_install_db () { set +e log_progress_msg "Initialize database: " datadir=mysqld_get_param datadir L=grep "$datadir" /etc/mtab if [ ${#L} -eq 0 ]; then set -e log_failure_msg "$datadir not mounted, exiting..." log_end_msg 1 fi mysql_install_db --defaults-file=${CONF} --defaults-extra-file=/etc/mysql/debian-ci.cnf --ldata=$datadir &>/dev/null chown -R ${_UID}.${_GID} $datadir log_progress_msg "installed; " set -e }

#

# mysqld_grant () { set +e _USER=grep user /etc/mysql/debian-ci.cnf | tail -n1 | awk '{print $3}' _PASS=grep password /etc/mysql/debian-ci.cnf | tail -n1 | awk '{print $3}' ${MYADMIN} -u root password "${_PASS}" echo "GRANT ALL ON *.* TO '${_USER}'@'localhost' IDENTIFIED BY '${_PASS}';" | ${MYADMIN/admin/} -u root --password="${_PASS}" #echo "DELETE FROM mysql.user WHERE 'Host' != 'localhost';FLUSH PRIVILEGES;" | ${MYADMIN/admin/} -u root --password="${_PASS}" set -e }

At this point, you must be able to start the new MySQL-CI Server: [email protected]:/root # /etc/init.d/mysql-ci start

And stop it as well: [email protected]:/root # /etc/init.d/mysql-ci stop