How to change MySQL data directory?

Question

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.

you would have to copy the current data to the new directory and to change your my.cnf your MySQL.

[mysqld]
datadir=/your/new/dir/
tmpdir=/your/new/temp/

You have to copy the database when the server is not running.

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

First you should stop the mysql server. e.g.

# /etc/init.d/mysql stop

After that you should copy the old data directory (e.g. /var/lib/mysql) incl. privileges to your new directory via

# cp -R -p /var/lib/mysql /new/data/dir

now you can change in /etc/mysql/my.cnf the data new and restart the server

# /etc/init.d/mysql restart