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?
/media
implies), but I believe this can do weird things with permissions. You might want to investigate that, and/or use/etc/fstab
to mount the drive at boot.