MySQL on a MicroVM

Run a MySQL server in a MicroVM for lightweight database workloads. Since MicroVM is minimalist, MySQL needs to be configured to conserve resources.

Preparing the Image

Make sure the MicroVM image already has MySQL installed:

# Mount the image
sudo mount -o loop rootfs.img /mnt/microvm

# Debian/Ubuntu
sudo chroot /mnt/microvm bash -c '
  apt update && apt install -y mysql-server mysql-client
  mysql_install_db --user=mysql --datadir=/var/lib/mysql
'

# Alpine
sudo chroot /mnt/microvm bash -c '
  apk add mysql mysql-client
  mysql_install_db --user=mysql --datadir=/var/lib/mysql
'

sudo umount /mnt/microvm

Configuring MySQL

Create an optimized configuration file for MicroVM:

# Mount the image again
sudo mount -o loop rootfs.img /mnt/microvm

sudo tee /mnt/microvm/etc/mysql/conf.d/microvm.cnf << 'EOF'
[mysqld]
# Resource optimization for MicroVM
bind-address = 0.0.0.0
port = 3306

# Memory tuning
innodb_buffer_pool_size = 64M
innodb_log_file_size = 16M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Connection
max_connections = 50
thread_cache_size = 8

# Query cache
query_cache_type = 0

# Table cache
table_open_cache = 256

# Temp tables
tmp_table_size = 8M
max_heap_table_size = 8M

# Logging
slow_query_log = 0
general_log = 0

# Disable expensive features
skip-name-resolve
symbolic-links = 0
EOF

sudo umount /mnt/microvm

Running the MicroVM with MySQL

qemu-system-x86_64 \
  -M microvm -enable-kvm -cpu host -m 512m -smp 2 \
  -kernel vmlinuz \
  -append "console=ttyS0 root=/dev/vda rw" \
  -drive id=rootfs,file=rootfs.img,format=raw,if=none,cache=none,aio=linux \
  -device virtio-blk-device,drive=rootfs \
  -netdev user,id=net0,hostfwd=tcp::3306-:3306 \
  -device virtio-net-device,netdev=net0 \
  -nographic -serial stdio

Setting Up Inside the Guest

# Init MySQL
mysql_install_db --user=mysql --datadir=/var/lib/mysql

# Start MySQL
mysqld_safe --user=mysql &

# Wait a few seconds, then secure the installation
mysql_secure_installation

# Create a database and a user
mysql -u root -p << 'SQL'
CREATE DATABASE app_db;
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'%';
FLUSH PRIVILEGES;
SQL

Auto Start MySQL

Using an init script (Alpine)

sudo mount -o loop rootfs.img /mnt/microvm

sudo tee /mnt/microvm/etc/init.d/mysqld << 'INITEOF'
#!/sbin/openrc-run

MYSQLD=/usr/sbin/mysqld
PIDFILE=/run/mysqld/mysqld.pid
LOGFILE=/var/log/mysql/mysqld.log

depend() {
    need net
    after firewall
    keyword timeout 300
}

start() {
    ebegin "Starting MySQL"
    mkdir -p /run/mysqld
    chown mysql:mysql /run/mysqld
    start-stop-daemon --start --background \
        --make-pidfile --pidfile $PIDFILE \
        --exec $MYSQLD -- --user=mysql
    eend $?
}

stop() {
    ebegin "Stopping MySQL"
    start-stop-daemon --stop --pidfile $PIDFILE
    eend $?
}
INITEOF

sudo chmod +x /mnt/microvm/etc/init.d/mysqld
sudo chroot /mnt/microvm rc-update add mysqld default

sudo umount /mnt/microvm

Using systemd (Debian/Ubuntu)

sudo chroot /mnt/microvm systemctl enable mysql

Port Forwarding

Access MySQL from the host via port forwarding:

# QEMU command with port forwarding
-netdev user,id=net0,hostfwd=tcp::3306-:3306

# From the host, connect to MySQL
mysql -h 127.0.0.1 -P 3306 -u app_user -p

Backup Database

# Inside the guest
mysqldump -u root -p --all-databases > /data/backup.sql

# or with a file copy
cp /var/lib/mysql/app_db/*.ibd /data/mysql-backup/

# From the host via SSH
ssh -p 2222 root@localhost "mysqldump -u root -p app_db" > local_backup.sql

Monitoring

# Status MySQL
mysqladmin -u root -p status

# Check the connections
mysql -u root -p -e "SHOW PROCESSLIST;"

# Check the database size
mysql -u root -p -e "
  SELECT table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
  FROM information_schema.tables
  GROUP BY table_schema;
"

Troubleshooting

MySQL will not start

# Check the log
cat /var/log/mysql/mysqld.log

# Check whether the data directory has been initialized
ls /var/lib/mysql/

# Re-initialize if needed
mysql_install_db --user=mysql --datadir=/var/lib/mysql

Out of memory

# Reduce the buffer pool
innodb_buffer_pool_size = 32M

# Reduce max connections
max_connections = 20

# Disable performance_schema
performance_schema = 0

Slow query

# Enable the slow query log for debugging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1