Galera集群MariaDB自定义用户下mysql-slow.log权限异常求助

Looks like the core issue here is that even though you've configured MariaDB to run under myuser:mygroup, the slow query log is still being created with mysql:root permissions. This usually happens due to misconfigured systemd service settings (if you're using systemd), missing explicit user directives in your my.cnf, or incorrect directory permissions for the log path.

Here's how to resolve this step by step:

1. Verify the user Directive in my.cnf #

First, make sure your my.cnf explicitly sets the run user for MariaDB under the [mysqld] section. This ensures the mysqld process runs as your custom user, which dictates the ownership of newly created log files.

Open your my.cnf file (typically located at /etc/my.cnf, /etc/mysql/my.cnf, or your custom installation path's conf directory) and add or confirm these lines:

[mysqld]
user=myuser
# ... keep your existing configurations ...
slow_query_log=1
slow_query_log_file=/path/to/your/custom/data/dir/mysql-slow.log  # Ensure this path is within your custom data directory owned by myuser:mygroup

Save the file and proceed to the next step.

2. Fix Systemd Service File (If Using Systemd) #

Most modern Linux distros use systemd to manage MariaDB services. The default systemd service file often hardcodes User=mysql and Group=mysql, which overrides the user directive in my.cnf—this is likely the root cause of your issue.

• Locate the MariaDB systemd service file. It's usually at /usr/lib/systemd/system/mariadb.service or /etc/systemd/system/mariadb.service.
• Open the file with a text editor (e.g., sudo vi /usr/lib/systemd/system/mariadb.service).
• Find the lines starting with User= and Group= and modify them to your custom user/group:

  User=myuser
  Group=mygroup
  

• Ensure PermissionsStartOnly=true is present (this ensures permission checks run before starting the service).
• Save the file and reload systemd to apply changes:

  sudo systemctl daemon-reload
  

3. Ensure Log Directory Permissions Are Correct #

Even if the mysqld process runs as myuser, if the directory where mysql-slow.log is created doesn't have write permissions for myuser:mygroup, the file might be created with incorrect ownership.

Check the directory permissions:

ls -ld /path/to/your/log/directory

If it's not owned by myuser:mygroup, fix it with:

sudo chown -R myuser:mygroup /path/to/your/log/directory

4. Clean Up Existing Log Files and Restart #

Delete the existing mysql-slow.log file (since it has incorrect permissions and will be overwritten anyway):

sudo rm /path/to/your/custom/data/dir/mysql-slow.log

Then restart the MariaDB service:

sudo systemctl restart mariadb

5. Verify the Fix #

After restarting, check the ownership of the newly created log file:

ls -l /path/to/your/custom/data/dir/mysql-slow.log

It should now be owned by myuser:mygroup. You can also check the mysql.err log to confirm there are no more "Could not use mysql-slow.log for logging" errors.

内容的提问来源于stack exchange，提问作者SCG