Changing the data directory

Your data can grow over time, and when it outgrows the filesystem, you need to add a disk or move the data directory to a bigger volume.

How to do it...

1. Check the current data directory. By default, the data directory is /var/lib/mysql:

mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.04 sec)

2. Stop mysql and make sure it has stopped successfully:

shell> sudo systemctl stop mysql

3. Check the status:

shell> sudo systemctl status mysql

It should show Stopped MySQL Community Server.

4. Create the directory at the new location and change the ownership to mysql:

shell> sudo mkdir -pv /data
shell> sudo chown -R mysql:mysql /data/

5. Move the files to the new data directory:

shell> sudo rsync -av /var/lib/mysql /data

6. In Ubuntu, if you've enabled AppArmor, you need to configure the Access...