How to change MySQL data directory?

Question

Is it possible to change my default MySQL data directory to another path? Will I be able to access the databases from the old location?

Answer 1

We had to move MySQL into /home directory because it was mapped from another physical disc. In order to make live simpler, instead of changing directory in my.cnf, we have mapped the new directory /home/mysql in the place of the original directory /var/lib/mysql. It works for us like a charm (tested on CentOS 7.1).

Create the new dir and set correct permissions.

mkdir -p /home/mysql
chmod 755 /home/mysql
chown mysql:mysql /home/mysql

Stop MySQL if running.

systemctl stop mysql

Move the data dir.

mv -f /var/lib/mysql/* /home/mysql

Create a permanent mount and execute it.

echo "/home/mysql /var/lib/mysql none    bind   0 0" >> /etc/fstab
mount -a

Restart the server.

systemctl start mysql

Answer 2

Quick and easy to do:

# Create new directory for MySQL data
mkdir /new/dir/for/mysql

# Set ownership of new directory to match existing one
chown --reference=/var/lib/mysql /new/dir/for/mysql

# Set permissions on new directory to match existing one
chmod --reference=/var/lib/mysql /new/dir/for/mysql

# Stop MySQL before copying over files
service mysql stop

# Copy all files in default directory, to new one, retaining perms (-p)
cp -rp /var/lib/mysql/* /new/dir/for/mysql/

Edit the /etc/my.cnf file, and under [mysqld] add this line:

datadir=/new/dir/for/mysql/

If you are using CageFS (with or without CloudLinux) and want to change the MySQL directory, you MUST add the new directory to this file:

/etc/cagefs/cagefs.mp

And then run this command:

cagefsctl --remount-all

Answer 3

If you are using SE linux, set it to permissive mode by editing /etc/selinux/config and changing SELINUX=enforcing to SELINUX=permissive

Answer 4

1. Stop MySQL using the following command:

   sudo /etc/init.d/mysql stop
   

2. Copy the existing data directory (default located in /var/lib/mysql) using the following command:

   sudo cp -R -p /var/lib/mysql /newpath
   

3. edit the MySQL configuration file with the following command:

   sudo gedit /etc/mysql/my.cnf   # or perhaps /etc/mysql/mysql.conf.d/mysqld.cnf
   

4. Look for the entry for datadir, and change the path (which should be /var/lib/mysql) to the new data directory.

5. In the terminal, enter the command:

   sudo gedit /etc/apparmor.d/usr.sbin.mysqld
   

6. Look for lines beginning with /var/lib/mysql. Change /var/lib/mysql in the lines with the new path.

7. Save and close the file.

8. Restart the AppArmor profiles with the command:

   sudo /etc/init.d/apparmor reload
   

9. Restart MySQL with the command:

   sudo /etc/init.d/mysql restart
   

10. Now login to MySQL and you can access the same databases you had before.

Answer 5

If you want to do this programmatically (no manual text entry with gedit) here's a version for a Dockerfile based on user1341296's answer above:

FROM spittet/ruby-mysql
MAINTAINER [e-mail]

RUN cp -R -p /var/lib/mysql /dev/shm && \
    rm -rf /var/lib/mysql && \
    sed -i -e 's,/var/lib/mysql,/dev/shm/mysql,g' /etc/mysql/my.cnf && \
    /etc/init.d/mysql restart

Available on Docker hub here: https://hub.docker.com/r/richardjecooke/ruby-mysql-in-memory/

Answer 6

First stop your mysql

sudo service mysql stop

copy mysql data to the new location.

sudo cp -rp /var/lib/mysql /yourdirectory/

if you use apparmor, edit the following file and do the following

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Replace where /var/lib/ by /yourdirectory/ then add the follwoing if no exist to the file

    /yourdirectory/mysql/ r,
    /yourdirectory/mysql/** rwk,

Save the file with the command

:wq

Edit the file my.cnf

sudo vim /etc/mysql/my.cnf

Replace where /var/lib/ by /yourdirectory/ then save with the command

:wq

finally start mysql

sudo service mysql start

@see more about raid0, optimization ici