MySQL更改datadir后Permission denied问题

[flyfrank]

由于MySQL默认的的数据存放的路径/var/lib/mysql所在磁盘空间已满，需要更改datadir并迁移数据，操作步骤如下：
（1）在/home/deploy目录下新建数据存放目录并更改目录权限

cd /home/deploy
mkdir -p mysql/data
chown -R mysql:mysql mysql

（2）停止数据库并拷贝MySQL数据文件到新目录

systemctl stop mysqld.service
cp -R /var/lib/mysql/* /home/deploy/mysql/data/

（3）修改拷贝后的数据的所属用户及组为mysql

cd mysql/data
chown -R mysql:mysql ./*

（4）修改/etc/my.cnf配置文

#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
datadir=/home/deploy/mysql/data
socket=/home/deploy/mysql/data/mysql.sock

（5）启动MySQL

systemctl start mysqld.service

操作完成后启动MySQL失败

[root@localhost mysql]# systemctl start mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

查看报错详情

[root@localhost mysql]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: start-limit) since Wed 2020-12-23 10:53:17 CST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 4565 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 4542 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 27243 (code=exited, status=0/SUCCESS)

Dec 23 10:53:16 localhost.localdomain systemd[1]: Failed to start MySQL Server.
Dec 23 10:53:16 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
Dec 23 10:53:16 localhost.localdomain systemd[1]: mysqld.service failed.
Dec 23 10:53:17 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart.
Dec 23 10:53:17 localhost.localdomain systemd[1]: Stopped MySQL Server.
Dec 23 10:53:17 localhost.localdomain systemd[1]: start request repeated too quickly for mysqld.service
Dec 23 10:53:17 localhost.localdomain systemd[1]: Failed to start MySQL Server.
Dec 23 10:53:17 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
Dec 23 10:53:17 localhost.localdomain systemd[1]: mysqld.service failed.
[root@localhost mysql]# journalctl -xe | grep mysql
-- Subject: Unit mysqld.service has begun start-up
-- Unit mysqld.service has begun starting up.
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: mysqld: Can't change dir to '/home/deploy/mysql/data/' (Errcode: 13 - Permission denied)
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: 2020-12-23T02:53:12.946535Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: 2020-12-23T02:53:12.946736Z 0 [Warning] Can't create test file /home/deploy/mysql/data/localhost.lower-test
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: 2020-12-23T02:53:12.946786Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.27) starting as process 4453 ...
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: 2020-12-23T02:53:12.949569Z 0 [Warning] Can't create test file /home/deploy/mysql/data/localhost.lower-test
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: 2020-12-23T02:53:12.949581Z 0 [Warning] Can't create test file /home/deploy/mysql/data/localhost.lower-test
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: 2020-12-23T02:53:12.949642Z 0 [ERROR] failed to set datadir to /home/deploy/mysql/data/
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: 2020-12-23T02:53:12.949655Z 0 [ERROR] Aborting
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: 2020-12-23T02:53:12.949682Z 0 [Note] Binlog end
Dec 23 10:53:12 localhost.localdomain systemd[1]: mysqld.service: control process exited, code=exited status=1
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: Initialization of mysqld failed: 0
Dec 23 10:53:12 localhost.localdomain mysqld[4450]: 2020-12-23T02:53:12.949738Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-- Subject: Unit mysqld.service has failed
-- Unit mysqld.service has failed.

[flyfrank]

Can't change dir to '/home/deploy/mysql/data/' (Errcode: 13 - Permission denied)

根据启动报错信息，是/home/deploy/mysql/data/没有权限导致，检查目录权限

[root@localhost deploy]# ll /home
drwx------.  7 deploy dev 180 Dec 23 10:16 deploy

[root@localhost deploy]# ll /home/deploy
drwxr-xr-x  3 mysql  mysql 18 Dec 23 10:16 mysql

[root@localhost deploy]# ll /home/deploy/mysql
drwxr-xr-x 22 mysql mysql 4096 Dec 23 10:59 data

其它用户缺少/home/deploy目录的x执行权限

再次回顾一下rwx权限作用：

• r(Read，读取，权限值为4)：对文件而言，具有读取文件内容的权限；对目录来说，具有浏览目 录的权限。
• w(Write,写入，权限值为2)：对文件而言，具有新增、修改文件内容的权限；对目录来说，具有删除、移动目录内文件的权限。
• x(eXecute，执行，权限值为1)：对文件而言，具有执行文件的权限；对目录了来说该用户具有进入目录的权限。

由于deploy目录所属用户为deploy，用户组为dev，目前同组用户及其它用户没有任何权限。在deploy目录下存放mysql数据文件，需要对deploy增加其它用户的可读及执行权限，mysql才能访问deploy下面的目录：

cd /home
chmod 755 deploy

重启MySQL，服务正常启动。

[mvpbang]

很棒的分析，攒