Technical

How to access the Ubuntu /var/lib/mysql subdirectory

Boot Kernel
Written by omanjali

How to access the Ubuntu /var/lib/mysql subdirectory

How to access the Ubuntu /var/lib/mysql subdirectory.Over time, databases expand, occasionally taking up more space on the file system. When they are on the same partition as the rest of the operating system, you may also have I/O contention. Redundancy and other desirable properties can be provided through RAID, network block storage, and other devices. This tutorial will walk you through moving MySQL’s data directory, whether you’re doing it to add extra space, assess performance-enhancing options, or utilise other storage capabilities.

Step 1:MySQL Data Directory relocation

$ mysql -u root -p

Give the MySQL root password when prompted. Next, pick the data directory from the MySQL prompt:

mysql> select @@datadir;

Output
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

This output shows that MySQL is set up to utilise the default data directory, which is /var/lib/mysql/, thus we need to change that directory. After making sure of it, type escape to exit the monitor.

Before making any modifications to the data directory, MySQL will be shut down to ensure the data’s integrity:

$ sudo systemctl stop mysql

Use the following command to confirm your success since systemctl doesn’t always show the results of service management commands:

$ sudo systemctl status mysql

If the last line of the report indicates that the server is stopped, you can be certain it has been shut down:

We’ll use rsync to transfer the current database directory to the new destination after the server has been shut down. While-v gives verbose output so you can watch the progress, the -a flag preserves the permissions and other directory attributes.

$ sudo rsync -av /var/lib/mysql /mnt/volume-nyc1-01

When rsync is complete then change the name of the folder with a .bak.

$ sudo mv /var/lib/mysql /var/lib/mysql.bak

Also Read:Booting Kernel

Step 2 .Identifying the Location of the New Data

There are numerous techniques to override configuration settings in MySQL. The default setting for the datadir in the /etc/mysql/mysql.conf.d/mysqld.cnf file is /var/lib/mysql. This file should be modified to reflect the new data directory:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change the path that follows the line that starts with datadir= to reflect the new location.

Step 3 .Setting Up Access Control Rules For AppArmor

$ sudo nano /etc/apparmor.d/tunables/alias

Restart AppArmor for the changes to take effect.

$ sudo systemctl restart apparmor

Step 4:Relaunching MySQL

The next step is to launch MySQL, but doing so will result in an additional error. This time, the error occurs because the script mysql-systemd-start checks for the existence of a directory, -d, or a symbolic link, -L, that matches two default paths. Instead of an AppArmor problem. If they’re not located, it fails:

$ sudo mkdir /var/lib/mysql/mysql -p

$sudo systemctl start mysql
$sudo systemctl status mysql

Start the MySQL monitor to confirm that the new data directory is truly in use.

$ mysql -u root -p

Take this chance to make sure your database is operating properly after restarting MySQL and making sure it is using the new location. You can delete the backup data directory once you’ve ensured the accuracy of any current data:

$ sudo rm -Rf /var/lib/mysql.bak

$ sudo systemctl restart mysql
$ sudo systemctl status mysql

Conclusion

In this lesson, we’ve relocated the MySQL data directory and modified Ubuntu’s AppArmor ACLs to reflect the change. The procedures provided here should work regardless of the underlying technology, even if we were utilising a Block Storage device, to redefine the position of the data directory.

Also Read:Boot Kernel

About the author

omanjali

Leave a Comment