EVDE
Posts: 3
Joined: Tue Sep 01, 2015 3:24 pm

Putting Mysql database file on external hard drive

Tue Sep 01, 2015 3:32 pm

Hello everyone,

I have a question,

Currently i'm restricted to using max. 16 gb for my project (since that's the capacity of my micro SD card), however, i need more.
So I want to buy an external hard drive (1TB or something). Now i am wondering how i can get mysql to use database files (i believe with the .fmr extension) stored on that hard drive, instead of the default /var/lib/mysql/.

I have a Raspberry Pi 1 B+ and i run Raspbian OS (newest version).

User avatar
DougieLawson
Posts: 35373
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Putting Mysql database file on external hard drive

Wed Sep 02, 2015 12:02 am

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
Update my.cnf
Restart MySQL
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

EVDE
Posts: 3
Joined: Tue Sep 01, 2015 3:24 pm

Re: Putting Mysql database file on external hard drive

Wed Sep 02, 2015 12:05 pm

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
Update my.cnf
Restart MySQL
Thanks!

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Putting Mysql database file on external hard drive

Sun Apr 07, 2019 9:40 am

I realize that this is an old posting, but I have just installed MariaDB on my Pi 3b running Stretch. Before I start any database development I would like to have the data stored/accessed on an external HD or USB stick.

With the passage of time and the MariaDB MySQL replacement on Raspbian Stretch, will the above posted comments still work? If not, then what is the best/right way?

Thanks...RDK

User avatar
DougieLawson
Posts: 35373
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Putting Mysql database file on external hard drive

Sun Apr 07, 2019 10:19 am

Still works - the data directory is still defined in my.cnf.
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

User avatar
rpdom
Posts: 14460
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: Putting Mysql database file on external hard drive

Sun Apr 07, 2019 12:00 pm

I prefer not to touch my.cnf. Instead I create a file in /etc/mysql/conf.d/ (I usually call it local.cnf) with a [mysqld] section and any new value I want. That will override the settings in my.cnf.

The advantage of doing it that way is if a software upgrade comes along that installs a new version of my.cnf I don't get all the prompts about "changes have been made to a configuration file. Accept, Ignore, show differences" etc.

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Putting Mysql database file on external hard drive

Sun Apr 07, 2019 12:11 pm

Douglas.....All of the files in the concatenation list in this file (/etc/mysql/my.cnf), shown below, were either empty or did not contain the directory information
# 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.
except /etc/mysql/mariadb.conf.d/50-server.cnf which has these lines:
#
# 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
[server]
# this is only for the mysqld standalone daemon
[mysqld]
#
# * 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
skip-external-locking
# 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
Is this the file where I should change the datadir = /var/lib/mysql to point to my USB Stick?

Thanks...RDK

User avatar
rpdom
Posts: 14460
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: Putting Mysql database file on external hard drive

Sun Apr 07, 2019 12:58 pm

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

[mysqld]
datadir = /media/pi/datastore
The USB store must be mounted before the mariadb server starts, otherwise it will fail.

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Putting Mysql database file on external hard drive

Sun Apr 07, 2019 1:56 pm

Great, I actually understand what you have proposed. Now how do I insure that it is mounted before MariaDB starts? The USB stick is defined in the /etc/fstab. Is that sufficient?...RDK

Andyroo
Posts: 3344
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Putting Mysql database file on external hard drive

Sun Apr 07, 2019 3:16 pm

RDK wrote:
Sun Apr 07, 2019 1:56 pm
Great, I actually understand what you have proposed. Now how do I insure that it is mounted before MariaDB starts? The USB stick is defined in the /etc/fstab. Is that sufficient?...RDK
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.

Did you use the UUID for the stick or it’s name? It could change name if another stick is detected first where as the UUID will not change till it’s reformatted (and maybe not even then but I’ve not tested that). If you do not know the devices UUID, you can get it via:

Code: Select all

sudo blkid
Edit: Sorry forgot to say UUID is better if you only have one partition type on the stick. As the data is in the device Superblock (so it can be changed by a format thinking about it) you could run into an issue when you have two mounts with different file systems but the same ID. Better to have one partition per stick in my mind...
Need Pi spray - these things are breeding in my house...

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Putting Mysql database file on external hard drive

Mon Apr 08, 2019 7:06 am

Andyroo: Thanks for the idea. Currently in my /etc/fstab I have this line:

Code: Select all

/dev/sda1 /mnt/usbdrive vfat defaults,dmask=000,fmask=111 0 0 
which is configured to work for SAMBA read/write file sharing. If I understand you, and in practice it works like this, if I change USB sticks it does not matter as it used the one it finds. If I have two USB sticks then it does not always find the right one to be mapped to /mnt/usbdrive.

I'm assuming that, your UUID technique, will force it to always use the USB stick with that UUID for the mapping? At least on this Pi and for my ideas with MariaDB that is what I should be doing. What would be the equivalent statement using the UUID to that I have shown above? I don't want to lose SAMBA functionality.

The blkid command shows this for my current USB stick
/dev/sda1: LABEL="PIV2DEV" UUID="91FF-0CA6" TYPE="vfat" PARTUUID="c3072e18-01"
Thanks...Rob

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Putting Mysql database file on external hard drive

Sat May 04, 2019 4:12 pm

I added "nofail" to my mount statement in the fstab and also converted it to use the UUID. That has been working now for several weeks. and I have started using MariaDB and setting up users, etc, but no databases.

Today I tried to make the changes suggested by rpdom on April 7 so I could have my databases on a USB memory stick:
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

[mysqld]
datadir = /media/pi/datastore
The USB store must be mounted before the mariadb server starts, otherwise it will fail.
I did a reboot and started getting error messages when I did a "sudo mysql"
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
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
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[1]: Starting MariaDB 10.1.37 database server...
May 04 09:31:28 Dev mysqld[631]: 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[631]: 2019-05-04 9:31:29 1995657008 [Warning] Can't create test file /media/pi/datastoremnt/usbdri
May 04 09:31:29 Dev mysqld[631]: [120B blob data]
May 04 09:31:29 Dev mysqld[631]: 2019-05-04 9:31:29 1995657008 [ERROR] Aborting
May 04 09:31:29 Dev systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
May 04 09:31:29 Dev systemd[1]: Failed to start MariaDB 10.1.37 database server.
May 04 09:31:29 Dev systemd[1]: mariadb.service: Unit entered failed state.
May 04 09:31:29
Dev systemd[1]: 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

User avatar
rpdom
Posts: 14460
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: Putting Mysql database file on external hard drive

Sat May 04, 2019 4:33 pm

Either the permissions are wrong on the mounted drive and directories, or the server is trying to start before the partition is mounted.

Try checking that the partition is mounted (df -h), and if it is, try starting the server: sudo systemctl start mariadb

If it still fails to start, it is most likely because of permissions.

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Putting Mysql database file on external hard drive

Sat May 04, 2019 5:39 pm

Thanks. The partition (usb stick) is mounted since I can access (via Simba) other files on it, both before and after I tried to restart the service. What should the permissions be set to?....RDK

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Putting Mysql database file on external hard drive

Sun May 05, 2019 11:10 am

I followed the steps in the Jan 2019 entry at this reference (https://lb.raspberrypi.org/forums/viewt ... 8#p1397915) and it seems to be working:
$ 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
However, I got lots of error messages like this for the CHOWN statement:
chown: changing ownership of '/mnt/usbdrive/MySQL/mysql/columns_priv.MYD': Opera tion not permitted
, but the service started ok and I have successfully created a new DB on my USB Stick....RDK

User avatar
rpdom
Posts: 14460
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: Putting Mysql database file on external hard drive

Sun May 05, 2019 2:22 pm

The chown errors are because you are using a VFAT file system and it doesn't support Linux permissions.

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Putting Mysql database file on external hard drive

Sun May 05, 2019 5:22 pm

Thanks, I've got a lot to learn....Rob

Return to “Networking and servers”