vim, git, aws and other three-letter words

Software Development Notes

MySQL: speed up unit tests by moving data to memory (Ubuntu)

There are several ways to speed up slow unit tests which interact with the database:

It is better to try other listed approaches and I think of the last method as a quick temporary hack, but here it is:

In Ubuntu there is also a problem with apparmor which will not allow mysql to read from /dev/shm. To fix this it is recommended to add the following to the /etc/apparmor.d/usr.sbin.mysqld: 

/dev/shm/mysql/ r,
/dev/shm/mysql/** rwk,

But it doesn't work for me and I disabled apparmor for mysql (not recommended): 

sudo mv /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable

Below are shell scripts to move MySQL data to /dev/shm and back, restore backed up data and check db state.

Move db to memory script

#!/bin/sh
#Check if run as root
if [ `whoami` != root ]
then
    echo "You must be root to do that!"
    exit 1
fi

service mysql stop
if [ ! -s /var/lib/mysql.backup ]
then
    cp -pRL /var/lib/mysql /var/lib/mysql.backup
fi
mv /var/lib/mysql /dev/shm/mysql
chown -R mysql:mysql /dev/shm/mysql
ln -s /dev/shm/mysql /var/lib/mysql
chown -h mysql:mysql /var/lib/mysql
service mysql start

Move db to disk script

#!/bin/sh
#Check if run as root
if [ `whoami` != root ]
then
    echo "You must be root to do that!"
    exit 1
fi

service mysql stop
rm /var/lib/mysql
if [ ! -s /dev/shm/mysql ]
then
    cp -pRL /var/lib/mysql.backup /var/lib/mysql
else
    mv /dev/shm/mysql /var/lib/mysql
fi
service mysql start

Restore db backup script

#!/bin/sh
#Check if run as root
if [ `whoami` != root ]
then
    echo "You must be root to do that!"
    exit 1
fi

service mysql stop
if [ ! -s /var/lib/mysql.backup ]
then
    exit -1
fi
rm /var/lib/mysql
cp -pRL /var/lib/mysql.backup /var/lib/mysql
rm -rf /dev/shm/mysql
service mysql start

Check db state script

#!/bin/sh
#Check if run as root
if [ `whoami` != root ]
then
    echo "You must be root to do that!"
    exit 1
fi

if [ -L /var/lib/mysql ]
then
    echo "Mem db"
    exit 0
else
    echo "File db"
    exit 1
fi

Tags: mysql

Tags