Is it possible to use MariaDB data on an external USB hard drive?

Question

I have a rpi3 and successfully installed MariaDB / PHP7 / Nginx server combination. How ever i want to keep the data on a USB HD. Security of the data is not critical, but the amount of writes to the database I think would kill the SD card. I have tried everything I can find with google and all fails. Here is a typical attempt:

mysql -u root -p

MariaDB [(none)]> select user,host,password from mysql.user;
+------------+-----------+-------------------------------------------+
| user       | host      | password                                  |
+------------+-----------+-------------------------------------------+
| root       | localhost | *7E35362A73107D0435687FF2D1554FFE3FCD066F |
| phpmyadmin | localhost | *82643D8502051BE5EF3A019095B32218F0001EF7 |
+------------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
+--------------------+
4 rows in set (0.01 sec)

cp R -p /var/lib/mysql /media/pi/usb_hd/database/mysql-data

edit my.cnf

nano /etc/mysql/my.cnf

and add

[mysqld]
datadir=/media/pi/usb_hd/database/mysql-data
socket=/media/pi/usb_hd/database/mysql-data/mysql.sock

[client]
port=3306
socket=/media/pi/usb_hd/database/mysql-data/mysql.sock

journalctl -xn reports

> -- Logs begin at Sun 2017-11-19 11:17:01 GMT, end at Sun 2017-11-19 12:22:19 GMT. -- Nov 19 12:22:12 raspberrypi kernel: w1_master_driver
> w1_bus_master1: Family 0 for 00.3c0000000000.1d is not registered. Nov
> 19 12:22:17 raspberrypi systemd[1]: Starting MariaDB database
> server...
> -- Subject: Unit mariadb.service has begun start-up
> -- Defined-By: systemd
> -- Support: https://www.debian.org/support
> --
> -- Unit mariadb.service has begun starting up. Nov 19 12:22:19 raspberrypi mysqld[2711]: 2017-11-19 12:22:19 1995948032 [Note]
> /usr/sbin/mysqld (mysqld 10.1.23-MariaDB-9+deb9u1) starting as process
> 2711 ... Nov 19 12:22:19 raspberrypi mysqld[2711]: 2017-11-19 12:22:19
> 1995948032 [Warning] Can't create test file
> /media/pi/usb_hd/database/mysql-data/raspberrypi.lower-test Nov 19
> 12:22:19 raspberrypi mysqld[2711]: [112B blob data] Nov 19 12:22:19
> raspberrypi mysqld[2711]: 2017-11-19 12:22:19 1995948032 [ERROR]
> Aborting Nov 19 12:22:19 raspberrypi systemd[1]: mariadb.service: Main
> process exited, code=exited, status=1/FAILURE Nov 19 12:22:19
> raspberrypi systemd[1]: Failed to start MariaDB database server.
> -- Subject: Unit mariadb.service has failed
> -- Defined-By: systemd
> -- Support: https://www.debian.org/support
> --
> -- Unit mariadb.service has failed.
> --
> -- The result is failed. Nov 19 12:22:19 raspberrypi systemd[1]: mariadb.service: Unit entered failed state. Nov 19 12:22:19
> raspberrypi systemd[1]: mariadb.service: Failed with result
> 'exit-code'.

and systemctl -l status mariadb

> ● mariadb.service - MariaDB database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Sun 2017-11-19 12:27:36 GMT; 5h 59min ago
  Process: 617 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 501 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POS
  Process: 469 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 438 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 617 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

Nov 19 12:27:32 raspberrypi systemd[1]: Starting MariaDB database server...
Nov 19 12:27:36 raspberrypi mysqld[617]: 2017-11-19 12:27:36 1995472896 [Note] /usr/sbin/mysqld (mysqld 10.1.23-MariaDB-9+deb9u1) starting as process 617 ...
Nov 19 12:27:36 raspberrypi mysqld[617]: 2017-11-19 12:27:36 1995472896 [Warning] Can't create test file /media/pi/usb_hd/database/mysql-data/raspberrypi.lower-test
Nov 19 12:27:36 raspberrypi mysqld[617]: [112B blob data]
Nov 19 12:27:36 raspberrypi mysqld[617]: 2017-11-19 12:27:36 1995472896 [ERROR] Aborting
Nov 19 12:27:36 raspberrypi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Nov 19 12:27:36 raspberrypi systemd[1]: Failed to start MariaDB database server.
Nov 19 12:27:36 raspberrypi systemd[1]: mariadb.service: Unit entered failed state.
Nov 19 12:27:36 raspberrypi systemd[1]: mariadb.service: Failed with result 'exit-code'.

What is is tring to tell me and how do I fix it?

Answer 1

I tried all of the above, and all are valid, but my solution seems to be to make pi a member of group mysql e.g. usermod -a -G mysql pi

Answer 2

I had a same problem. Then I mounted external hdd to /database and configured /etc/fstab to mount the drive at boot. Then it started working.

Answer 3

Copied this from a different website.

I ran into the same issue and deleted the files /var/lib/mysqld/ib_logfile0 and /var/lib/mysql/ib_logfile1 as mentioned in