更改 MySQL tmpdir 会终止 InnoDB 功能

更改 MySQL tmpdir 会终止 InnoDB 功能

我有一个查询一直失败,因为我没有足够的可用临时空间。因此,我在 的不同分区上创建了一个目录/data/tmpchmod将此目录改为777,并更新my.cnf为:tmpdir = /data/tmp。我能够顺利重新启动守护进程,并且日志中没有报告任何错误。这是重新启动守护进程后显示的所有日志:

Mar 17 16:45:05 Casala-Dev-LX01 /etc/mysql/debian-start[3166]: Upgrading MySQL tables if necessary.
Mar 17 16:45:05 Casala-Dev-LX01 /etc/mysql/debian-start[3169]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Mar 17 16:45:05 Casala-Dev-LX01 /etc/mysql/debian-start[3169]: Looking for 'mysql' as: /usr/bin/mysql
Mar 17 16:45:05 Casala-Dev-LX01 /etc/mysql/debian-start[3169]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Mar 17 16:45:05 Casala-Dev-LX01 /etc/mysql/debian-start[3169]: This installation of MySQL is already upgraded to 5.1.41, use --force if you still need to run mysql_upgrade
Mar 17 16:45:05 Casala-Dev-LX01 /etc/mysql/debian-start[3176]: Checking for insecure root accounts.
Mar 17 16:45:05 Casala-Dev-LX01 /etc/mysql/debian-start[3180]: Triggering myisam-recover for all MyISAM tables
Mar 17 16:45:05 Casala-Dev-LX01 kernel: [93900.335357] type=1503 audit(1300380305.417:117):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_1.MYI"
Mar 17 16:45:05 Casala-Dev-LX01 kernel: [93900.335453] type=1503 audit(1300380305.417:118):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_1.MYI"
Mar 17 16:45:05 Casala-Dev-LX01 kernel: [93900.335553] type=1503 audit(1300380305.417:119):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_1.MYI"
Mar 17 16:45:05 Casala-Dev-LX01 kernel: [93900.335591] type=1503 audit(1300380305.417:120):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_1.MYI"
Mar 17 16:45:05 Casala-Dev-LX01 kernel: [93900.335628] type=1503 audit(1300380305.417:121):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_1.MYI"
Mar 17 16:45:05 Casala-Dev-LX01 kernel: [93900.335731] type=1503 audit(1300380305.417:122):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_1.MYI"
Mar 17 16:45:05 Casala-Dev-LX01 kernel: [93900.335893] type=1503 audit(1300380305.417:123):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_1.MYI"
Mar 17 16:55:32 Casala-Dev-LX01 kernel: [94526.730200] type=1503 audit(1300380932.157:125):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_0.MYI"
Mar 17 16:55:32 Casala-Dev-LX01 kernel: [94526.753333] type=1503 audit(1300380932.177:126):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_0.MYI"
Mar 17 16:55:40 Casala-Dev-LX01 kernel: [94534.677693] type=1503 audit(1300380940.107:127):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_0.MYI"
Mar 17 16:55:40 Casala-Dev-LX01 kernel: [94534.837802] type=1503 audit(1300380940.267:128):  operation="mknod" pid=3164 parent=1 profile="/usr/sbin/mysqld" requested_mask="c::" denied_mask="c::" fsuid=104 ouid=104 name="/data/tmp/#sql_c54_0.MYI"

但是,当我登录数据库时,InnoDB 表不起作用。我一直收到错误,ERROR 1286 (42000): Unknown table engine 'InnoDB'。MyISAM 表工作正常。我读到这里这可能是为 指定的大小的问题innodb_buffer_pool_size,所以我尝试了一系列大小,一直到32M。然而,无论这个设置如何,守护进程都会继续正确启动,InnoDB 引擎会默默失败(通过 确认SHOW ENGINES;),我继续变得更加沮丧。

答案1

扩展 DerfK 的 apparmor 建议,检查你的 apparmor 配置

乌本图:

/etc/apparmor.d/usr.bin.mysqld

添加

/data/tmp/ r,  
/data/tmp/* rw

到目录列表。

相关内容