TheWell: Mounting MySQL in RAM

TheWell är den mer tekniska sidan av oss. Här kan du hitta samlade tips och erfarenheter från vår vardag. Här har Axel Verstin skrivit en guide för hur du sätter upp och kör MySQL från RAM.


Preface

Mounting anything in RAM means losing persistence between boots, i.e you lose the data when you turn your computer off.
This document is only meant to be used in order to speed up situations where the data isn’t important. We could put in the effort of writing changes to the data back to disk on shutdown, but you’d still lose everything in case of a power failure or any other system failure.

Setup

This guide assumes that you’re using a modern Linux kernel, MySQL 5.7.22, and a distribution that uses SystemD. Mounting in RAM means that you run a greater risk of running out of RAM to use, so make sure that you have enough swap space so that the kernel doesn’t kill your applications if you would run out. Having the same sized swap as RAM, or more, is a good idea.

Initial MySQL configuration

You’ll need to to create a setup for MySQL that contains the users and access privilieges that you need. In case you want a clean slate, I’ll show how to create it. First ensure that MySQL isn’t running.

$ systemctl stop mysql.service

Move your(if any) previous data to a backup directory, in case anything goes wrong and you want the data back. The default data directory is /var/lib/mysql, but in case you have configurated any other location, back up that location instead.

$ mv /var/lib/mysql /var/lib/mysql.backup

Then initialize MySQL. Here we use the insecure option because you’ll change the root password anyways, and it’s easier.

$ mysqld --initialize-insecure

This creates a clean installation without any schemas, and no password for the root user. The first thing we’ll need to do is to set the root password. Here we set it to the computers hostname, use whatever password you want to.

$ mysql -uroot -sse "SET PASSWORD = PASSWORD('$HOSTNAME')"

Now we can login as root. We needed to create a user called pwrp in order to run our projects.

$ MYSQL_PWD=$HOSTNAME mysql -uroot -sse "CREATE USER 'pwrp'@'localhost' IDENTIFIED BY 'pwrp'"
$ MYSQL_PWD=$HOSTNAME mysql -uroot -sse "GRANT ALL PRIVILEGES ON *.* TO 'pwrp'@'localhost' IDENTIFIED BY 'pwrp'"
$ MYSQL_PWD=$HOSTNAME mysql -uroot -sse "FLUSH PRIVILEGES"

Try to login as the created user.

$ MYSQL_PWD=pwrp mysql -upwrp

If successful, we have our initial configuration. Turn off MySQL.

$ systemctl stop mysql.service

Then move the configuration to a new location so that it doesn’t get overwritten.

$ mv /var/lib/mysql /var/lib/mysql.configuration

Creating the ramdisk

This entails mounting a tmpfs filesystem in a location of a certain size. In order to persist the filesystem across boots, an entry in fstab will be created.

We’ll use a configuration where the ”partition” is a gigabyte large, and there are 5000 inodes. If you have many tables or if you extend the partition’s size, you may need to change the number of inodes.

It’ll be mounted at /tmp/ramdisk, you may change this if you wish, the location is irrelevant.

First create a backup of /etc/fstab

$ cp /etc/fstab /etc/fstab.backup

Then add the new entry for the tmpfs.

Make sure that you use double arrows in the below command, otherwise you’d overwrite it.

$ echo "tmpfs /tmp/ramdisk tmpfs rw,noatime,mode=1777,size=1g,nr_inodes=5K 0 0" >> /etc/fstab

Now the tmpfs is mounted when you start your computer by SystemD.

In order for you not to have to reboot your computer at this point in time, you can mount it by hand.

$ mount --types tmpfs --options rw,noatime,mode=1777,size=1g,nr_inodes=5K --source tmpfs --target /tmp/ramdisk

If you want to try different options, add the remount option to the mount command and it will remount with the new options. To unmount, use the umount command on the target directory.

Copying the MySQL configuration to the ramdisk at boot

This entails creating a SystemD service unit that will copy the configuration we created earlier to our specified mountpoint, i.e /tmp/ramdisk.

Open the file /etc/systemd/system/create_mysql_ramdisk.service in your favorite editor, and paste the following content into it(Exclude the square/hash/sharp/pound marks).

# [Unit] # Description=Setup for MySQL server ramdisk
# Before=mysql.service
#
# [Service] # Type=oneshot
# TimeoutSec=1200
# ExecStart=/bin/rm -rf /tmp/ramdisk/mysql
# ExecStart=/bin/cp -rp /var/lib/mysql.configuration /tmp/ramdisk/mysql
#
# [Install] # RequiredBy=mysql.service

Make sure you keep the -p flag to the copy command, because it preserves the file permissions, MySQL won’t function otherwise. The -f flag to rm is so rm doesn’t return an error code because the directory won’t exist at boot, it’s just there in case you want to recreate the configuration at runtime.

Enable the unit to start at boot. This also creates the symlinks, marking it as a dependency for MySQL.

$ systemctl enable create_mysql_ramdisk.service

Start it.

$ systemctl start create_mysql_ramdisk.service

You should now have your earlier configuration in /tmp/ramdisk. Ensure the proper permissions are in order.

Configure MySQL to use the ramdisk

You need to edit the data directory location from where MySQL previously looked for its files, which probably is /var/lib/mysql, to the new location /tmp/ramdisk/mysql.

This configuration may be located in /etc/mysql/my.cnf, or in a configuration file under /etc/mysql/mysql.conf.d

Edit the line

# datadir = /var/lib/mysql

To be

# datadir = /tmp/ramdisk/mysql

Start MySQL

Now start MySQL and check that everything is working and that user privileges are set.

If you want MySQL to start at boot, you need to enable it.

$ systemctl enable mysql.service

$ systemctl start mysql.service
$ MYSQL_PWD=$HOSTNAME mysql -uroot

You are done!

2018-12-06