Changing data directory for mysql(mariadb)

gvenkat1994 · 2014-06-27 14:56:22

I want to shift my mysql default data directory from /var/lib/mysql to a folder in my home folder, because the databases I will be creating will be over 100Gb, and my root folder is on a 32 Gb SSD. Could anyone please give me proper detailed steps on how to accomplish this. I tried to do this once following some blog online and it messed up my whole mysql installation(I had to reinstall everything).

firecat53 · 2014-06-27 15:30:54

I'd create a partition or directory on the big drive and mount it to /var/lib/mysql in /etc/fstab. Much easier!

Scott

gvenkat1994 · 2014-06-27 15:47:40

This is kinda temporary though. Say I follow your advice, do I just create a partition and then mount it in fstab? No permission changes etc ?

firecat53 · 2014-06-27 16:16:06

You have make sure permissions are initially set correctly, but then it will just be automatically mounted by fstab.one time setup, then you don't have to think about it anymore.

gvenkat1994 · 2014-06-27 16:27:38

I'm not entirely sure how to go about it. There is a /var/lib/mysql directory. Also, I just noticed that mysqld.sock and mysqld.pid is in a different directory(/run/mysqld). How di I set the permissions correctly?

Spider.007 · 2014-06-27 16:30:33

You must have searched really hard before asking here!

Have a look at datadir / socket / pid_file @ my.cnf

gvenkat1994 · 2014-06-28 03:08:23

In my my.cnf file there is no datadir option the socket and pid options point to /run/mysqld. I'm a little confused because of this. There is a datadir option separately for InnoDB, but it is commented out. Also I am planning to use MyISAM.

Spider.007 · 2014-06-29 09:17:49

Why would datadir be for innodb only? https://dev.mysql.com/doc/refman/5.0/en … ld_datadir

gvenkat1994 · 2014-06-29 13:58:35

No before the datadir line there is a line that says Uncomment the following lines if you are using InnoDb. That is what confused me. Also, when I changed that option it doesn't have any effect. The data is still stored in /var/lib/mysql

omnibrain · 2014-11-06 09:27:45

I'm having the same issue. Any help would be greatly appreciated.

lufti · 2015-05-03 17:48:27

I hope this aint counting as necromancy.

I found this thread but I also found the solution, so if anyone stumble into the same issues.

Here the basics:

If you do not use the default location, just put into /etc/mysql/my.conf (or whereever your conf is):
datadir=/path/to/datadir

Otherwise, it will always default to /var/lib/mysql (maybe this will change)

Have a nice day,
lufti.

nadman10 · 2015-08-22 22:13:32

lufti wrote:

I hope this aint counting as necromancy.
I found this thread but I also found the solution, so if anyone stumble into the same issues.
Here the basics:
If you do not use the default location, just put into /etc/mysql/my.conf (or whereever your conf is):
datadir=/path/to/datadir
Otherwise, it will always default to /var/lib/mysql (maybe this will change)
Have a nice day,
lufti.

I think he already tried that. I am in the same boat and am still having issues. Even after adding a "datadir" entry, mysqld is still trying to use /var/lib/mysql

Arch Linux

#1 2014-06-27 14:56:22

Changing data directory for mysql(mariadb)

#2 2014-06-27 15:30:54

Re: Changing data directory for mysql(mariadb)

#3 2014-06-27 15:47:40

Re: Changing data directory for mysql(mariadb)

#4 2014-06-27 16:16:06

Re: Changing data directory for mysql(mariadb)

#5 2014-06-27 16:27:38

Re: Changing data directory for mysql(mariadb)

#6 2014-06-27 16:30:33

Re: Changing data directory for mysql(mariadb)

#7 2014-06-28 03:08:23

Re: Changing data directory for mysql(mariadb)

#8 2014-06-29 09:17:49

Re: Changing data directory for mysql(mariadb)

#9 2014-06-29 13:58:35

Re: Changing data directory for mysql(mariadb)

#10 2014-11-06 09:27:45

Re: Changing data directory for mysql(mariadb)

#11 2015-05-03 17:48:27

Re: Changing data directory for mysql(mariadb)

#12 2015-08-22 22:13:32

Re: Changing data directory for mysql(mariadb)

Board footer