mariadb datadir change location raspberry pi

Question

I've got a raspberry pi 3 and have an external HDD which I have yet to mount but what directory I make depends a little on how best I can move the datadir location.

So at the moment I have the basic install from apt-get install MariaDB and ive added a new user and then also installed phpmyadmin, everything is good.

But I'd like the data to be stored on the external hdd so it doesn't burn up the sd card.

I've read through a few online instructions, all with bad grammar/spelling and older versions of the software. For example they say to edit /etc/MySQL/my.cnf but the .cnf files seem to be different and I'm not 100% which files (or all files) need changing with the datadir and socket edits.

Which .cnf files need to be edited please?

Edit If I add to /etc/MySQL/my.cnf the following [client-server] datadir=/my-new-directory socket=/my-new-directory/MySQL.sock port=3306 socket=/my-new-directory/MySQL.sock

as described at https://www.tecmint.com/change-default-mysql-mariadb-data-directory-in-linux/

This would then be overwritten by the other .cnf files that are read after.

Answer 1

Be aware that although mariadDB loads configuration details from various my.cnf files it can also load them from other files with different names.

That means that if you make a change in one of the my.cnf files, it may get overwritten by another file of a different name. To make the change stick, you need to change it in the right (last loaded) config file - or, maybe, change it in all of them.

So how do you find all the config files that might be loaded? Instead of looking for my.cnf files, try running:

grep -r datadir /etc/mysql/

This will find all the places in which datadir is mentioned. In my case, it produces this answer:

/etc/mysql/mariadb.conf.d/50-server.cnf:datadir     = /var/lib/mysql 

When I edit that file (/etc/mysql/mariadb.conf.d/50-server.cnf) to change the value for datadir (in my case to /media/USBHDD2/shared/mysql ), it works, whereas changing it in my.cnf does not.

However, you then have to make sure that the hard drive is mounted before mariadDB starts up, otherwise the startup will fail. And also make sure that the ownership of the directory and files on your mounted drive is mysql:mysql - a problem if the filestructure is ntfs rather than EXT4. see my own problem and solution here