Putting MySQL database file on external drive

Currently hosting websites on a Raspberry Pi 4, with all web data already on external SSD drive. But then why not put the database files also on the SSD? Since there’s no point to have fast SSD if DB query still capped by crappy SD card read/write speed.

I’d create a custom file /etc/mysql/mariadb.conf.d/99-local.cnf with your changes in it. That would override the original file without having to change it or worrying about it getting overwritten during a software upgrade.

# This is /etc/mysql/mariadb.conf.d/99-local.cnf
datadir = /ssd/mysql

The external SSD drive must be mounted before the mariadb server starts, otherwise it will fail. You can verify the changes by using mysql command SHOW VARIABLES LIKE '%datadir%';

root@buggy-sunshine:~# mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 247
Server version: 10.5.10-MariaDB-0ubuntu0.21.04.1 Ubuntu 21.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE '%datadir%';
| Variable_name | Value       |
| datadir       | /ssd/mysql/ |
1 row in set (0.004 sec)

MariaDB [(none)]>

Leave a Reply

Your email address will not be published. Required fields are marked *