By default, MySQL stores its binary logs at /var/lib/mysql (that is true for most MySQL installations. If you can’t find it there, google it)
Here is a quick overview of what we are going to cover in this tutorial:
- Modify MySQL configuration file to point to the new directory.
- Move current binary logs files to the new directory after stopping MySQL service.
- Set correct permissions for the new directory.
- Allow MySQL to access the new directory in AppArmor in case you have it installed.
- Start MySQL service again.
Modify MySQL Configuration File
The first step is to modify the MySQL configuration which is located at /etc/mysql/my.cnf
Execute the following command to edit the file:
sudo nano /etc/mysql/my.cnfNext, look for the section labeled with [mysqld] and add the following code below it:
log-bin=/path/to/new/directory/mysql/binlog
log-bin-index=/path/to/new/directory/mysql/binlog.indexStop MySQL Service and Move Current Binary Log Files
Now, we need to move the current binary logs to the new directory. However, to make sure that will not cause any issues, we have to stop MySQL service first using the following command:
sudo systemctl stop mysqlIf the above code did not work, then try this:
sudo systemctl stop mysqldNow, move the files using this command:
sudo mv /var/lib/mysql/binlog* /path/to/new/directory/mysql/Start MySQL Service
Now, you can start MySQL service using the following command.
sudo systemctl start mysqlOr if that did not work then use this command.
sudo systemctl start mysqldFinally, use this command to check if MySQL is running
sudo systemctl status mysql
OR
sudo systemctl status mysqldYou should see a message like this:
If it is not active (running), then there is an issue. Most of the time it is due to AppArmor which blocks MySQL access to the new binary logs directory I will show you how to fix it in the next step
Disable AppArmor for MySQL
While it is not recommended to disable it, that is the fastest solution. To do it run the next commands.
sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/and,
sudo apparmor_parser -R /etc/apparmor.d/disable/usr.sbin.mysqldNow, try to start MySQL service again, if it started then AppArmor was the reason. Thus, it is recommended to give MySQL access to the new binary logs directory in AppArmor and then re-enable MySQL profile in AppArmor for added security.
Configure AppArmor to Allow MySQL to Access the new Directory
First, open the file /etc/apparmor.d/usr.sbin.mysqld and add the new directory with necessary permissions
sudo nano /etc/apparmor.d/usr.sbin.mysqldat the end of the file inside the curly brackets add the following line
/path/to/new/directory/mysql/** rwk,Replace /path/to/new/directory/mysql/ with your path.
Next, to re-enable MySQL profile remove the symbolic link to the profile in /etc/apparmor.d/disable/usr.sbin.mysqld Then load the profile using the -a option.
sudo rm /etc/apparmor.d/disable/usr.sbin.mysqld
cat /etc/apparmor.d/usr.sbin.mysqld | sudo apparmor_parser -aReload AppArmor for the changes to take effect using the following command.
sudo systemctl reload apparmor.serviceFinally, download and install apparmor-profiles tool to bring MySQL to be compliant and remove any enforcement. Use the following command to install it
sudo apt install apparmor-profilesRun the following command to make MySQL on complain mode:
sudo aa-complain /etc/apparmor.d/usr.sbin.mysqldConclusion
In conclusion, modifying the MySQL configuration file and relocating the binary log files can be necessary to ensure efficient log management and system performance. By following the steps outlined in this guide, you can successfully configure MySQL to store its binary logs in a new directory. Remember to stop the MySQL service, move the existing binary logs, and update the AppArmor configuration to grant MySQL access to the new directory. While disabling AppArmor is not recommended for security reasons, it can serve as a temporary solution. To ensure optimal security, it is recommended to configure AppArmor to allow MySQL access and then re-enable the MySQL profile. By following these steps, you can effectively manage your MySQL binary logs and maintain a secure and well-functioning database system.
