tmpfs for MySQL in Ubuntu

To speed up local development or continuous integration tests, you can move MySQL to RAM. You will loose all your data on each reboot, as RAM is volatile storage. I tried the following for MySQL on Ubuntu 16 and 18. I wouldn't be surprised if you find it to work just fine in other environments as well. Here are the steps: 1. Add this line to /etc/fstab:

tmpfs /var/mysql-tmpfs tmpfs rw,gid=116,uid=110,size=1424M,nr_inodes=50k,mode=0700 0 0

2. Add these lines to /etc/apparmor.d/usr.sbin.mysqld:

  /var/mysql-tmpfs/ rwkm,
  /var/mysql-tmpfs/data/ rwkm,
  /var/mysql-tmpfs/data/** rwkm,
  /var/mysql-tmpfs/tmp/ rm,
  /var/mysql-tmpfs/tmp/** rwkm,

3. Modify these lines in /etc/mysql/mysql.conf.d/mysqld.cnf to read:

datadir		= /var/mysql-tmpfs/data
tmpdir		= /var/mysql-tmpfs/tmp

4. Execute these commands:

sudo service mysql stop
sudo mkdir -p /var/mysql-tmpfs
sudo mount -a
sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld 
sudo cp -rp /var/lib/mysql/data /var/mysql-tmpfs/
sudo mkdir -p /var/mysql-tmpfs/tmp && sudo chown mysql:mysql -R /var/mysql-tmpfs
sudo service mysql start

The last three lines are needed after every reboot, because the machine starts with an empty /var/mysql-tmpfs directory. Do tweet me @Gogowitsch if you have feedback of any kind.