Wavey
Posts: 3
Joined: Sun Dec 09, 2012 7:35 pm

Change MYSQL Data Directory

Mon Dec 17, 2012 9:00 pm

I've successfully installed apache, php and mysql on my Raspbian wheezy pi. All is up and running well.

My next little project will use the pi for logging data, not huge amounts, but probably more than will fit on my SD card. So I'd like to relocate where MySql keeps it's databases to a directory mounted from my NAS.

I found some useful instructions for ubuntu http://www.ubuntugeek.com/how-to-change ... ctory.html
The steps are basically as follows
- Stop the mysql server
- copy the existing DB files to the new location
- change the my.cnf datadir to the new location
- restart mysql

Unfortunately mysql won't restart. If I change the my.cnf location back to the original location it all works fine. The Ubuntu instructions do discuss some changes required to apparmor, but I didn't think that that was implemented yet in the wheezy release.

Any thoughts on what to do or where to go looking appreciated.

waldo
Posts: 12
Joined: Sat Sep 01, 2012 5:55 am

Re: Change MYSQL Data Directory

Tue Dec 18, 2012 11:14 pm

Compare permissions on the two directories. Are there differences? My top guess is that the mysql user doesn't have permissions on the new directory, thus mysql fails to launch.

good luck,
waldo

madman_xxx
Posts: 38
Joined: Tue Dec 18, 2012 5:03 pm
Contact: Website

Re: Change MYSQL Data Directory

Wed Dec 19, 2012 2:57 pm

You would have to post logs for me to be sure, but that's most certainly apparmor, which does not allow using unauthorised data folder. If that's the case, you need to modify /etc/apparmor.d/usr.sbin.mysqld:

Code: Select all

[...]
# New values on my system, database files were moved to /mnt/external/db
  /mnt/external/db/mysql/ r,
  /mnt/external/db/mysql/** rwk,
# Original values
#  /var/lib/mysql/ r,
#  /var/lib/mysql/** rwk,
[...]
Hope this helps...

tdav
Posts: 2
Joined: Thu Dec 20, 2012 4:47 am

Re: Change MYSQL Data Directory

Thu Dec 20, 2012 5:02 am

I am also trying to change my database path and the server fails to start. I don't think is apparmor.
All is see in there is.

root@raspberrypi:/etc/apparmor.d# ls -all
total 12
drwxr-xr-x 2 root root 4096 Dec 15 12:20 .
drwxr-xr-x 96 root root 4096 Dec 19 22:00 ..
-rw-r--r-- 1 root root 1647 Nov 2 04:21 lightdm-guest-session

I have changed permissions on the new directory like it says on the numerous guides around.

Which log is going to help the most?

Any other ideas?

tdav
Posts: 2
Joined: Thu Dec 20, 2012 4:47 am

Re: Change MYSQL Data Directory

Thu Dec 20, 2012 7:45 am

I think I got it working with a new data directory. I just had a new empty database. I didn't need to backup. I copied ONLY the database folder '/var/lib/mysql/mysql' not the entire directory '/var/lib/mysql' which wasn't working for me. This is my first time using mysql so I'm not sure if this will fully function the way I did it but at least I can now get the server to come back up.

The steps I took

$ sudo /etc/init.d/mysql stop
$ sudo mkdir /path/to/new/datadir
$ sudo cp -R /var/lib/mysql/mysql /path/to/new/datadir
$ sudo nano /etc/mysql/my.cnf
datadir = /path/to/new/datadir
$ sudo chown -R mysql:mysql /path/to/new/datadir
$ sudo /etc/init.d/mysql start

Now the server starts back up. I have not done any other testing yet but it looks like I am getting somewhere.

Hope this helps the OP.

alekamos
Posts: 2
Joined: Sun Apr 21, 2013 6:05 pm

Re: Change MYSQL Data Directory

Sun Apr 21, 2013 6:14 pm

I end up in this post becouse i can't find the /etc/apparmor.d/usr.sbin.mysqld file..so i can't restart mysql server..and the last post of tdav doesn't work for me becouse i copy the entire directory '/var/lib/mysql'.
Where i can find /etc/apparmor.d/usr.sbin.mysqld? in /etc/apparmor.d/ i see just the file that tdav seen..

alekamos
Posts: 2
Joined: Sun Apr 21, 2013 6:05 pm

Re: Change MYSQL Data Directory

Wed Apr 24, 2013 3:55 pm

Anyone knows location of usr.sbin.mysqld on raspbian?

MicWit
Posts: 91
Joined: Fri Nov 01, 2013 5:20 am

Re: Change MYSQL Data Directory

Wed Jan 15, 2014 7:34 am

After I change the location of the data dir for mysql, it wont boot at start up. In the sys log is the line:

Jan 15 18:17:29 serverpi /etc/init.d/mysql[2190]: ERROR: The partition with /media/data/mysql is too full!

There is no way that the drive is full, its nearly empty, so is this a bug??? Something to do with permissions??

MicWit
Posts: 91
Joined: Fri Nov 01, 2013 5:20 am

Re: Change MYSQL Data Directory

Fri Jan 17, 2014 5:25 am

Solved my issue. It was not starting as the pi boots, but would start when I manually tell it to.

The issue was that the external drive was auto mounting, I needed to set a manual mount so that it exists from the start of boot, not once everything is loaded! To do this I used webmin to "save and mount at boot". This way the drive exists when mysql tries to run. If anyone wants more details let me know.

enthor
Posts: 1
Joined: Fri Mar 07, 2014 3:31 pm

Re: Change MYSQL Data Directory

Fri Mar 07, 2014 3:38 pm

Hi,

I also encountered the same problem as you did when moving the datadir with populated content.
You can work around this issue by making a backup of your DB's to reload them later when the new datadir is setup.
You might want to follow tdav's solution by doing the following:

Code: Select all

$ mysqldump -uroot -p --all-databases > alldb.sql
$ sudo /etc/init.d/mysql stop
$ sudo mkdir /path/to/new/datadir
$ sudo cp -R /var/lib/mysql/mysql /path/to/new/datadir
$ sudo nano /etc/mysql/my.cnf
datadir = /path/to/new/datadir
$ sudo chown -R mysql:mysql /path/to/new/datadir
$ sudo /etc/init.d/mysql start
$ mysql -u root -p < alldb.sql
This just backup all your DB's to restore them when the new setup is working.

Lance,

RichardT
Posts: 1
Joined: Fri Jul 17, 2015 1:26 am

Re: Change MYSQL Data Directory

Fri Jul 17, 2015 1:35 am

Thanks, tdav. I have been following all sorts of stupid, misleading posts on websites to achieve a simple task of moving my database files and all failed miserably. Your concise solution did exactly what I wanted. Hope you get to read this.

ghecu
Posts: 5
Joined: Wed Nov 28, 2018 2:20 pm

Re: Change MYSQL Data Directory

Wed Nov 28, 2018 3:00 pm

After trying most of posts it didn't not work just because of "*.cnf" file
I able to make it work with Raspbian 9.4 stretch

$ sudo /etc/init.d/mysql stop
$ sudo mkdir /path/to/new/datadir
$ sudo cp -R /var/lib/mysql/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

Iohy
Posts: 2
Joined: Tue Jan 08, 2019 2:49 pm

Re: Change MYSQL Data Directory

Thu Jan 17, 2019 11:34 am

Thanks a lot ghecu for that update, really help me to do in my new fresh installation of MySQL. Just a quotation in one of the steps:

$ 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

It is necessary to copy the hole set of data files including the ones in the /var/lib/mysql/ folder, if not the service will fail when try to start it again.

Return to “Networking and servers”