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.
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.
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).
# Description=Setup for MySQL server ramdisk
# [Service] # Type=oneshot
# 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
$ 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
# datadir = /tmp/ramdisk/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!