How to change MySQL data directory?

Solution 1:

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.

Solution 2:

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.