Difference between #2 and #1 of Speedup unit tests by moving MySql data to memory (in Ubuntu)

Speedup unit tests by moving MySql data to memory [Ubuntu]
============================================

There are several ways to speedup slow unit tests which interact with database:

 * Refactor code and tests and do not touch db in tests
 * Use sqlite db in memory instead of MySql
 * Use MySql MEMORY engine
 * Move MySql data to memory

> Each method has drawbacks:
> 
> * Not using DB in unit tests means that parts of the code won't be tested.
> * Using SQLite only for tests may induce different behavior. This is
against the very meaning of unit tests.
> * Like SQLite, the MySQL Memory engine is different from InnoDB and MyISAM.
For example, FK are ignored.

> The following process moves **every MySQL database** into a memory
filesystem.
> If you have databases other than the test DB, the copy/restore process may
be slow,
> and **you can loose data** if something goes wrong.
> You've been warned, do not use this when you have important data in any
database of your MySQL server.

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

 * stop mysql
 * move /var/lib/mysql to /dev/shm/mysql
 * link /var/lib/mysql to /dev/shm/mysql
 * start mysql

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 following to the
`/etc/apparmor.d/usr.sbin.mysqld`:

~~~
[sh]
/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
--------------------------------------------

~~~
[sh]
#!/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
--------------------------------------------

~~~
[sh]
#!/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
--------------------------------------------

~~~
[sh]
#!/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
--------------------------------------------

~~~
[sh]
    
#!/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
~~~

Links
--------------------------------------------
[Speedup unit tests by moving MySql data to memory
[Ubuntu]](http://sebgoo.blogspot.com/2012/12/speedup-unit-tests-by-moving-mysql-data.html)

[Unit test application including database is too
slow](http://stackoverflow.com/questions/9500032/unit-test-application-including-database-is-too-slow)

[Force an entire MySQL database to be in
memory](http://stackoverflow.com/questions/4894850/force-an-entire-mysql-database-to-be-in-memory)

[How to run django's test database only in
memory?](http://stackoverflow.com/questions/3096148/how-to-run-djangos-test-database-only-in-memory)

[Script to put mysqld on a ram disk in ubuntu 10.04. Runs on every hudson slave
boot](https://gist.github.com/1152547)

[MySQL tmpdir on
/dev/shm](http://www.indimon.co.uk/2012/mysql-tmpdir-on-devshm/)

[How can I get around MySQL Errcode 13 with SELECT INTO
OUTFILE?](http://stackoverflow.com/questions/2783313/how-can-i-get-around-mysql-errcode-13-with-select-into-outfile)