7

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?

1
  • I don't use automounting (which to me /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.
    – goldilocks
    Commented Nov 19, 2017 at 22:14

3 Answers 3

3

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

1
  • 1
    Please accept your own answer with a click on the tick on its left side. Only this will finish the question and it will not pop up again year for year.
    – Ingo
    Commented Dec 21, 2019 at 16:32
1

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.

0

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

1
  • Please edit your answer and provide the link to the other site Commented Aug 6, 2020 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.