Thanks!DougieLawson wrote:The directory where MySQL stores its data is defined in /etc/mysql/my.cnf
Shut down MySQL
Move every file and subdirectory to a new part of your filesystem
except /etc/mysql/mariadb.conf.d/50-server.cnf which has these lines:# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
Is this the file where I should change the datadir = /var/lib/mysql to point to my USB Stick?#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
# See the examples of server my.cnf files in /usr/share/mysql/
# this is read by the standalone daemon and embedded servers
# this is only for the mysqld standalone daemon
# * Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
Code: Select all
# This is /etc/mysql/mariadb.conf.d/99-local.cnf [mysqld] datadir = /media/pi/datastore
Should be as long as you add the nofail option to the mount. This will let the Pi boot continue if the USB stick fails or is not in the machine.
Code: Select all
Code: Select all
/dev/sda1 /mnt/usbdrive vfat defaults,dmask=000,fmask=111 0 0
Thanks...Rob/dev/sda1: LABEL="PIV2DEV" UUID="91FF-0CA6" TYPE="vfat" PARTUUID="c3072e18-01"
I did a reboot and started getting error messages when I did a "sudo mysql"RDK, that's the correct file for mariadb (it's changed since mysql).
I'd create a custom file /etc/mysql/mariadb.conf.d/99-local.cnf with your changes it. That would over ride the original file without having to change it or worrying about it getting overwritten during a software upgrade.
Code: Select all
# This is /etc/mysql/mariadb.conf.d/99-local.cnf
datadir = /media/pi/datastore
The USB store must be mounted before the mariadb server starts, otherwise it will fail.
Dev:~ $ sudo mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")
I then checked on the status of the mariadb service "systemctl status mariadb.service"
● mariadb.service - MariaDB 10.1.37 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Sat 2019-05-04 09:31:29 MDT; 5min ago
Process: 631 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Process: 546 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && syste
Process: 531 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 518 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Main PID: 631 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"
May 04 09:31:26 Dev systemd: Starting MariaDB 10.1.37 database server...
May 04 09:31:28 Dev mysqld: 2019-05-04 9:31:28 1995657008 [Note] /usr/sbin/mysqld (mysqld 10.1.37-MariaDB-0+deb9u1) star
May 04 09:31:29Dev mysqld: 2019-05-04 9:31:29 1995657008 [Warning] Can't create test file /media/pi/datastoremnt/usbdri
May 04 09:31:29 Dev mysqld: [120B blob data]
May 04 09:31:29 Dev mysqld: 2019-05-04 9:31:29 1995657008 [ERROR] Aborting
May 04 09:31:29 Dev systemd: mariadb.service: Main process exited, code=exited, status=1/FAILURE
May 04 09:31:29 Dev systemd: Failed to start MariaDB 10.1.37 database server.
May 04 09:31:29 Dev systemd: mariadb.service: Unit entered failed state.
May 04 09:31:29
Dev systemd: mariadb.service: Failed with result 'exit-code'.
Which as indicated is not running and failed to both start and restart. I commented out the statements in the custom file (/etc/mysql/mariadb.conf.d/99-local.cnf ) I created and rebooted. MariaDB service started and it would appear I'm back to with the default files.
What did I do wrong or what should I do to get my plan to work?.....RDK
However, I got lots of error messages like this for the CHOWN statement:$ sudo /etc/init.d/mysql stop
$ sudo mkdir /path/to/new/datadir
$ sudo cp -R /var/lib/mysql/* /path/to/new/datadir
$ sudo nano /etc/mysql/my.cnf --> should be sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
datadir = /path/to/new/datadir
$ sudo chown -R mysql:mysql /path/to/new/datadir
$ sudo /etc/init.d/mysql start
, but the service started ok and I have successfully created a new DB on my USB Stick....RDKchown: changing ownership of '/mnt/usbdrive/MySQL/mysql/columns_priv.MYD': Opera tion not permitted