3

This question already has an answer here:

I do not have enough space on my root partition to store the database.

$ df -h
Filesystem                         Size  Used Avail Use% Mounted on
/dev/mapper/ManjaroVG-ManjaroRoot   29G   21G  6.4G  77% /
dev                                1.9G     0  1.9G   0% /dev
run                                1.9G  852K  1.9G   1% /run
tmpfs                              1.9G   76K  1.9G   1% /dev/shm
tmpfs                              1.9G     0  1.9G   0% /sys/fs/cgroup
tmpfs                              1.9G  596K  1.9G   1% /tmp
/dev/mapper/ManjaroVG-ManjaroHome  197G   54G  133G  29% /home
/dev/sda1                          247M   88M  147M  38% /boot
tmpfs                              383M  8.0K  383M   1% /run/user/1000

Changing datadir in my.cnf to a new location caused a permission problem ([Warning] Can't create test file /home/u/tmp/mysql/acpfg.lower-test )

How is it possible change the directory where MariaDB/MySQL stores the database under Linux (for example to /home/u/tmp/mysql)?

marked as duplicate by slm Aug 23 '14 at 12:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • For an unknown reason, moving the data to another partition did not work either. So for a quick fix, instead of messing with llvm or gparted, I reduced my /swapfile size. – Daishi Jan 17 '18 at 11:47
4

You can either reconfigure MySQL to look for the data directory in a different location, or bind mount a new location over the original.

Make sure that the mysql service is stopped before you carry out these changes.

Then, move all the files and sub-directories from the original location into your new location.

Reconfigure MySQL

edit /etc/my.cnf and change datadir to:

datadir=/home/u/tmp/mysql

or...

Bind Mount

Use a bind mount to mount your new location over the original:

mount --bind /home/u/tmp/mysql /var/lib/mysql

Once you're happy that everything works, edit your /etc/fstab to make it permanent:

/home/u/tmp/mysql /var/lib/mysql  none  bind 0 0

File Permissions

Regardless of which method you choose, you'll need to ensure that the permissions on your new location are correct, as follows:

The top level directory (/home/u/tmp/mysql) and everything below should be owned by user and group mysql (assuming mysql runs as these on Arch Linux):

# chown -R mysql. /home/u/tmp/mysql

All files are:

# find /home/u/tmp/mysql/ -type f -exec chmod 0660 {} \;

All directories are:

# find /home/u/tmp/mysql/* -type d -exec chmod 0700 {} \;

The top level directory is:

# chmod 0755 /home/u/tmp/mysql
  • datadir in /etc/mysql/my.cnf is causing an [Warning] Can't create test file /home/u/tmp/mysql/acpfg.lower-test. The same problem I get also with mount --bind /home/u/tmp/mysql /var/lib/mysql. Is necessary to change permission or groups? – user977828 Aug 22 '14 at 11:28
  • Yes, the permissions on the new directory must be identical to the old. If you're using SELinux then you must relabel the new directory too. – garethTheRed Aug 22 '14 at 12:47
  • Which is best way to copy the old permission to the new directory? – user977828 Aug 22 '14 at 13:14
  • Also, it should be worth mentioning that the mysql service needs to be stopped before doing the changes. – Ramesh Aug 22 '14 at 15:43
  • @garethTheRed, yeah. However, even before the reconfiguration of my.cnf it is better to stop the service :) – Ramesh Aug 22 '14 at 15:49
1

Settings like this go into the MySQL configuration file (e.g. in /etc/mysql/my.cnf; the exact location depends on your system). The setting in question is datadir, see http://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_datadir for more info.

Not the answer you're looking for? Browse other questions tagged or ask your own question.