ubuntu20.04安装MySQL 8.0.36 改变binlog日志路径报错

现象

1、具体操作

#修改binlog文件路径
[root@ubuntu2004 ~]#vim /etc/mysql/mysql.conf.d/mysqld.cnf
......
server-id               = 1
#skip-log-bin
log_bin=/data/binlog/mysql-bin
......

#修改目录权限
[root@ubuntu2004 ~]#mkdir /data/binlog
[root@ubuntu2004 ~]#chown -R mysql. /data/binlog/

#启动失败
[root@ubuntu2004 ~]#systemctl restart mysql.service 
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

2、查看mysql的error日志及系统日志

#查看error日志
[root@ubuntu2004 ~]#tail -f /var/log/mysql/error.log 

......

mysqld: File '/data/binlog/mysql-bin.index' not found (OS errno 13 - Permission denied)
2024-05-16T05:29:38.101591Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-05-16T05:29:38.101845Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36-0ubuntu0.20.04.1)  (Ubuntu).
2024-05-16T05:29:38.608331Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.36-0ubuntu0.20.04.1) starting as process 2878
mysqld: File '/data/binlog/mysql-bin.index' not found (OS errno 13 - Permission denied)
2024-05-16T05:29:38.612044Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-05-16T05:29:38.612246Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36-0ubuntu0.20.04.1)  (Ubuntu).
2024-05-16T05:29:39.117496Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.36-0ubuntu0.20.04.1) starting as process 2887
mysqld: File '/data/binlog/mysql-bin.index' not found (OS errno 13 - Permission denied)
2024-05-16T05:29:39.121540Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-05-16T05:29:39.121862Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36-0ubuntu0.20.04.1)  (Ubuntu).
2024-05-16T05:29:39.594729Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.36-0ubuntu0.20.04.1) starting as process 2896
mysqld: File '/data/binlog/mysql-bin.index' not found (OS errno 13 - Permission denied)
2024-05-16T05:29:39.598224Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-05-16T05:29:39.598842Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36-0ubuntu0.20.04.1)  (Ubuntu).


#出现了AppArmor拒绝访问的错误。这表明AppArmor限制可能影响MySQL的正常运行。以下日志表明AppArmor拒绝了MySQL尝试打开binlog目录的请求
[root@ubuntu2004 ~]#tail -f /var/log/syslog

.....

May 16 13:11:55 ubuntu2004 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
May 16 13:11:55 ubuntu2004 systemd[1]: mysql.service: Failed with result 'exit-code'.
May 16 13:11:55 ubuntu2004 systemd[1]: Failed to start MySQL Community Server.
May 16 13:11:56 ubuntu2004 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 1.
May 16 13:11:56 ubuntu2004 systemd[1]: Stopped MySQL Community Server.
May 16 13:11:56 ubuntu2004 systemd[1]: Starting MySQL Community Server...
May 16 13:11:56 ubuntu2004 kernel: [ 7982.103985] audit: type=1400 audit(1715836316.190:641): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test" pid=78888 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0
May 16 13:11:56 ubuntu2004 kernel: [ 7982.342185] audit: type=1400 audit(1715836316.426:642): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test" pid=78889 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=113 ouid=113
May 16 13:11:56 ubuntu2004 kernel: [ 7982.344150] audit: type=1400 audit(1715836316.430:643): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/mysqld_tmp_file_case_insensitive_test.lower-test" pid=78889 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=113 ouid=113
May 16 13:11:56 ubuntu2004 kernel: [ 7982.345987] audit: type=1400 audit(1715836316.430:644): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/var/lib/mysql/binlog.index" pid=78889 comm="mysqld" requested_mask="wrc" denied_mask="wrc" fsuid=113 ouid=113
May 16 13:11:56 ubuntu2004 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
May 16 13:11:56 ubuntu2004 systemd[1]: mysql.service: Failed with result 'exit-code'.
May 16 13:11:56 ubuntu2004 systemd[1]: Failed to start MySQL Community Server.
May 16 13:11:56 ubuntu2004 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 2.
May 16 13:11:56 ubuntu2004 systemd[1]: Stopped MySQL Community Server.
May 16 13:11:56 ubuntu2004 systemd[1]: Starting MySQL Community Server...
.....

解决方法

1、根据新的错误消息，编辑MySQL的AppArmor配置文件

#（通常位于/etc/apparmor.d/usr.sbin.mysqld），以添加允许MySQL访问binlog目录的规则。在你的情况下，可能需要添加类似以下的规则：
[root@ubuntu2004 ~]# vim /etc/apparmor.d/usr.sbin.mysqld 

......

# Allow execution of server binary
  /usr/sbin/mysqld mr,
  /usr/sbin/mysqld-debug mr,

# Allow plugin access
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,

# Allow error msg and charset access
  /usr/share/mysql/ r,
  /usr/share/mysql/** r,

# Allow var/lib dir access
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /data/binlog/ r,							#添加允许MySQL访问/data/binlog目录的规则
  /data/binlog/** rwk,					#添加允许MySQL访问/data/binlog目录的规则
# Allow var/lib files dir access
  /var/lib/mysql-files/ r,
  /var/lib/mysql-files/** rwk,


# Allow keyring dir access
  /var/lib/mysql-keyring/ r,
  /var/lib/mysql-keyring/** rwk,

# Allow log file access
  /var/log/mysql.err rw,
  /var/log/mysql.log rw,
  /var/log/mysql/ r,
  /var/log/mysql/** rw,

# Allow read access to mecab files
  /var/lib/mecab/dic/ipadic-utf8/** r,

# Allow read access to OpenSSL config
  /etc/ssl/openssl.cnf r,
  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>
}

2、生效apparmor配置

[root@ubuntu2004 ~]# sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

[root@ubuntu2004 ~]# systemctl restart mysql.service

3、验证结果

[root@ubuntu2004 ~]#systemctl restart mysql.service 
[root@ubuntu2004 ~]#
[root@ubuntu2004 ~]#mysql -e "show variables like 'log_bin'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
[root@ubuntu2004 ~]#mysql -e "show variables like 'log_bin_basename'"
+------------------+------------------------+
| Variable_name    | Value                  |
+------------------+------------------------+
| log_bin_basename | /data/binlog/mysql-bin |
+------------------+------------------------+
[root@ubuntu2004 ~]#mysql -e "show master logs"
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       204 | No        |
| mysql-bin.000002 |       180 | No        |
| mysql-bin.000003 |       157 | No        |
+------------------+-----------+-----------+

[root@ubuntu2004 ~]#ls /data/binlog/ -l
total 16
-rw-r----- 1 mysql mysql 204 May 16 13:23 mysql-bin.000001
-rw-r----- 1 mysql mysql 180 May 16 13:29 mysql-bin.000002
-rw-r----- 1 mysql mysql 157 May 16 13:44 mysql-bin.000003
-rw-r----- 1 mysql mysql  90 May 16 13:44 mysql-bin.index