We transitioned from using sqlite on devel laptops (with inherent very low hassle) to having to run mysql everywhere to as our migrations and some of our complex database operations just didn’t work with sqlite3.

Setting up all the database junk in mysql in annoying. Some developers have laptops with other mysql programs running on them too, and some development machines are shared by multiple projects and multiple developers.

It’s much nicer to just run a “local” mysql. This is relatively easy to do with postgresql, but we are locked into mysql due to corporate desire for someone to buy support from.

Building local clusters for mysql is annoying, but not ultimately that hard. We use several shell scripts, but we think that these could be turned into rake tasks easily, and we intend to do that soon.

These scripts were inheirited from a PHP/Postgresql project call “ITERation” (a TBS.gc.ca project).

There is a Makefile in test/cluster/Makefile

SRCDIR=$(shell cd ../..; pwd) ... %.sh: %.sh.in Makefile sed \ -e 's,@SRCDIR@,'${CortlandSRCDIR}',' \ $< >$@ chmod +x $@ dbflush: etc/initdb.sh etc/rundb.sh etc/shutdb.sh -[ -f run/mysqld/mysqld.pid ] && etc/shutdb.sh rm -rf ${SRCDIR}/db/mysql etc/initdb.sh etc/rundb.sh dbinit: etc/initdb.sh etc/initdb.sh dbstart: etc/rundb.sh etc/rundb.sh

The first is test/cluster/etc/initdb.sh.in.

SRCDIR=@SRCDIR@ USER=${USER} RUNDIR=${SRCDIR}/test/cluster/run LOGDIR=${SRCDIR}/test/cluster/log PIDFILE=${RUNDIR}/mysqld/mysqld.pid ROOTPW=therootpw mkdir -p ${RUNDIR}/mysqld mkdir -p ${SRCDIR}/db/mysql mkdir -p ${LOGDIR}/mysql /usr/bin/mysql_install_db --basedir=/usr --datadir=${SRCDIR}/db/mysql --pid-file=${PIDFILE} --skip-external-locking --socket=${RUNDIR}/mysqld/mysqld.sock --log_bin=${LOGDIR}/mysql/mysql-bin.log /usr/sbin/mysqld --basedir=/usr --datadir=${SRCDIR}/db/mysql --pid-file=${RUNDIR}/mysqld/mysqld.pid --skip-external-locking --socket=${RUNDIR}/mysqld/mysqld.sock --port=3307 --log_bin=${LOGDIR}/mysql/mysql-bin.log & sleep 10 /usr/bin/mysqladmin -h 127.0.0.1 --port=3307 -u root password $ROOTPW echo "update user set host='%' where host='localhost';" | mysql -h 127.0.0.1 -u root --password=$ROOTPW mysql ( echo "CREATE DATABASE application_test;" echo "CREATE DATABASE application_development;" echo "CREATE USER application;" echo "SET PASSWORD FOR application = PASSWORD('nonprivpw');" echo "GRANT SELECT,INDEX,INSERT,UPDATE,DELETE,ALTER,CREATE,DROP ON application_test.* TO 'application'@'%';" echo "GRANT SELECT,INDEX,INSERT,UPDATE,DELETE ON application_development.* TO 'application'@'%';" echo "CREATE USER webuser;" echo "SET PASSWORD FOR webuser = PASSWORD('phppw');" echo "FLUSH PRIVILEGES;" ) | mysql --protocol=socket --socket=${RUNDIR}/mysqld/mysqld.sock -u root --password=$ROOTPW mysql /usr/bin/mysqladmin --protocol=socket --socket=${RUNDIR}/mysqld/mysqld.sock -u root --password=$ROOTPW shutdown

And the script which starts things up for normal things:

SRCDIR=@SRCDIR@ USER=${USER} RUNDIR=${SRCDIR}/test/cluster/run LOGDIR=${SRCDIR}/test/cluster/log SOCKET=${RUNDIR}/mysqld/mysqld.sock mkdir -p ${RUNDIR}/mysqld mkdir -p ${SRCDIR}/db/mysql mkdir -p ${LOGDIR}/mysql ln -s ${SOCKET} ${SRCDIR}/../application.sock /usr/sbin/mysqld --basedir=/usr --datadir=${SRCDIR}/db/mysql --pid-file=${RUNDIR}/mysqld/mysqld.pid --skip-external-locking --socket=${SOCKET} --skip-networking --log_bin=${LOGDIR}/mysql/mysql-bin.log &

The symlink is placed in the dir above because in our case, we have multiple RoR applications that want to read from that database. Slowly we are converting them to RESTful/ActiveResource mechanism.

To access the symlink, the database.yml looks like:

development: adapter: mysql database: application_development username: application password: nonprivpw host: localhost socket: <%= RAILS_ROOT %>/test/cluster/run/mysqld/mysqld.sock