MySQL different database location [duplicate]

Question

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)?

Answer 1

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

Answer 2

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.