Out of disk space in MySQL?
My MySQL server ran out of disk space. Instantaneously there was only one solution to increase the overall server capacity, but that increased the price of the server just double. To reduce to price I started reading about adding a separate volume to the digitalocean server.
Adding a separate volume just act as a separate harddisk adding to the system. This would reduce the size and price of the server. Follow the steps to separate the disc space from your server.
Step 1 — Type the command given below to find the current data directory.
select @@datadir;
Now stop the SQL service in the by the following command:
systemctl stop mysql
Step 2 — Now move the data directory that you got from the MySQL command to the mounted volume. Use rsync command to sync the data with same permissions for example:
rsync -av /var/lib/mysql /mnt/volume-nyc1–01
Step 3 — Edit the file mysqld.cnf which is placed in /etc/mysql/mysql.conf.d/mysqld.cnf by default. In the file update the variable [datadir] with the new location of the directory example:
. . .
datadir=/mnt/volume-nyc1–01/mysql
. . .
Step 4 — Now setup the AppArmor access control rule by editing the file /etc/apparmor.d/tunables/alias. At the end of the file add the following line:
alias /var/lib/mysql/ -> /mnt/volume-nyc1–01/mysql/
After editing the file restart the apparmor service by
systemctl restart apparmor
Step 5 — It’s all done now just restart the MySQL service and you are good to go. Just remeber create a dummy directory mysql for the service to start because of the systemd checks for the directory to exists before starting the service.
mkdir /var/lib/mysql/mysql -p
Now restart the mysql service
systemctl restart mysql.
Here is the digitalocean blog to follow —
