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.

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

show variables like '%datadir%';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

Stop mysql and make sure it has stopped successfully:

sudo systemctl stop mysql

Check the status:

sudo systemctl status mysql

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Sun 2018-09-09 16:26:42 UTC; 9s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 6625 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
  Process: 6582 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 6625 (code=exited, status=0/SUCCESS)
   Status: "SERVER_SHUTTING_DOWN"

Sep 09 15:01:14 mysq-4gb-2vcpu-00 systemd[1]: Stopped MySQL Community Server.
Sep 09 15:01:14 mysq-4gb-2vcpu-00 systemd[1]: Starting MySQL Community Server...
Sep 09 15:01:15 mysq-4gb-2vcpu-00 systemd[1]: Started MySQL Community Server.
Sep 09 16:26:38 mysq-4gb-2vcpu-00 systemd[1]: Stopping MySQL Community Server...
Sep 09 16:26:42 mysq-4gb-2vcpu-00 systemd[1]: Stopped MySQL Community Server.

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

sudo mkdir -pv /data

mkdir: created directory '/data'

sudo chown -R mysql:mysql /data/

Move the files to the new data directory:

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

...
mysql/performance_schema/users_132.sdi
mysql/performance_schema/variables_by_thr_168.sdi
mysql/performance_schema/variables_info_171.sdi
mysql/sys/
mysql/sys/sys_config.ibd

sent 433,827,537 bytes  received 2,842 bytes  173,532,151.60 bytes/sec
total size is 433,710,711  speedup is 1.00

In Ubuntu, if you've enabled AppArmor, you need to configure the Access Control:

echo '
alias /var/lib/mysql/ -> /data/mysql/,
' | sudo tee -a /etc/apparmor.d/tunables/alias

sudo systemctl restart apparmor

Start MySQL server and verify that the data directory has changed:

sudo systemctl start mysql

show variables like '%datadir%';

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)