centos系统修改MySQL默认存储路径导致无法启动的问题

hzy_5g

已于 2022-04-23 10:46:13 修改

阅读量7.1k

点赞数 2

CC 4.0 BY-SA版权

分类专栏：数据库使用文章标签：数据库开发

于 2022-04-23 10:43:34 首次发布

本文链接：https://blog.youkuaiyun.com/hzy_5g/article/details/124360147

数据库使用专栏收录该内容

2 篇文章

订阅专栏

本文指导如何更改MySQL默认数据存储路径，包括修改配置、迁移数据、处理SELinux权限问题。遇到启动失败时，通过查看错误信息并调整SElinux安全上下文来确保服务正常运行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

更改MySQL默认存储数据存储路径，需要先修改配置，再拷贝数据，然后重启。

1. 为了保证数据的准确性最好是停止mysql 服务，或者把mysql 表的写进行加锁。

mysqladmin -u root -p shutdown

采用命令查询mysql进程：ps aux | grep mysql | grep -v grep 所有有关mysql的进程都关掉了。

2、修改配置文件

/etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

假设新的数据存储路径为 /data/mysqldata

修改配置为：

[mysqld]

#datadir=/var/lib/mysql

datadir=/data/mysqldata

socket=/var/lib/mysql/mysql.sock

user=mysql

3.将原数据全部拷贝到新路径

cp -a /var/lib/mysql/* /test/mysql/data/

4.更改新路径宿主

chown mysql:mysql /test/mysql/data

5. 重启mysql服务 systemctl start mysqld

可能遇到问题：

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

以下三种查看报错信息方式：

1. systemctl status mysqld.service

[root@localhost ~]# 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 六 2020-03-14 03:55:06 CST; 14s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 73992 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 73975 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 72217 (code=exited, status=0/SUCCESS)
 
3月 14 03:55:06 localhost.localdomain systemd[1]: Failed to start MySQL Server.
3月 14 03:55:06 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
3月 14 03:55:06 localhost.localdomain systemd[1]: mysqld.service failed.
3月 14 03:55:06 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart.
3月 14 03:55:06 localhost.localdomain systemd[1]: Stopped MySQL Server.
3月 14 03:55:06 localhost.localdomain systemd[1]: start request repeated too quickly for mysqld.service
3月 14 03:55:06 localhost.localdomain systemd[1]: Failed to start MySQL Server.
3月 14 03:55:06 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
3月 14 03:55:06 localhost.localdomain systemd[1]: mysqld.service failed.

2. journalctl -xe

[root@localhost ~]# journalctl -xe
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has failed.
-- 
-- The result is failed.
3月 14 03:55:04 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
3月 14 03:55:04 localhost.localdomain systemd[1]: mysqld.service failed.
3月 14 03:55:05 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart.
3月 14 03:55:05 localhost.localdomain systemd[1]: Stopped MySQL Server.
-- Subject: Unit mysqld.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has finished shutting down.
3月 14 03:55:05 localhost.localdomain systemd[1]: Starting MySQL Server...
-- Subject: Unit mysqld.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has begun starting up.
3月 14 03:55:06 localhost.localdomain mysqld[73992]: Initialization of mysqld failed: 0
3月 14 03:55:06 localhost.localdomain systemd[1]: mysqld.service: control process exited, code=exited status=1
3月 14 03:55:06 localhost.localdomain systemd[1]: Failed to start MySQL Server.
-- Subject: Unit mysqld.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has failed.
-- 
-- The result is failed.
3月 14 03:55:06 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
3月 14 03:55:06 localhost.localdomain systemd[1]: mysqld.service failed.
3月 14 03:55:06 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart.
3月 14 03:55:06 localhost.localdomain systemd[1]: Stopped MySQL Server.
-- Subject: Unit mysqld.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has finished shutting down.
3月 14 03:55:06 localhost.localdomain systemd[1]: start request repeated too quickly for mysqld.service
3月 14 03:55:06 localhost.localdomain systemd[1]: Failed to start MySQL Server.
-- Subject: Unit mysqld.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has failed.
-- 
-- The result is failed.
3月 14 03:55:06 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
3月 14 03:55:06 localhost.localdomain systemd[1]: mysqld.service failed.

3、查看日日志，查看日志后可以看到日志中的ERROR信息，不懂的地方可以翻译一下，方便理解，寻找有用的信息。日志路径可通过/etc/my.cnf文件查看

[ERROR] InnoDB: Operating system error number 13 in a file operation.
[ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
[ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
[ERROR] InnoDB: Plugin initialization aborted with error Generic error
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Failed to initialize builtin plugins.
[ERROR] Aborting
                    ↓↓↓↓↓↓
[错误] InnoDB:文件操作中的操作系统错误号13。
[错误] InnoDB:该错误意味着mysqld没有访问该目录的权限。
[错误] InnoDB: os_file_get_status()在上失败。/ibdata1。无法确定文件权限
[错误] InnoDB:插件初始化中止，出现错误一般错误
[错误]插件“InnoDB”初始化函数返回错误。
[错误]插件“InnoDB”注册为存储引擎失败。
[错误]初始化内置插件失败。
[错误]中止

解决方案：

这可能是由于SElinux的安全上下文导致的，可尝试setenforce 0 命令关闭selinux以验证该问题。

[root@localhost ~]# getenforce    //查看selinux状态
Enforcing
[root@localhost ~]# setenforce 0    //临时关闭selinux，重启后失效
 
[root@localhost ~]# getenforce
Permissive
[root@localhost ~]# systemctl start mysqld     //启动mysql服务成功
[root@localhost ~]# ss -antulp | grep :3306
tcp    LISTEN     0      80     [::]:3306               [::]:*                   users:(("mysqld",pid=74057,fd=17))

如此，可证明确实是由selinux导致的，可尝试：

1、查看一下原本的数据目录的该属性

SELinux安全上下文查看方法（超详细）：http://c.biancheng.net/view/1149.html

[root@localhost ~]# ls -Z /var/lib/mysql
#使用选项-Z查看文件和目录的安全上下文
-rw-r--r--．root root system_u：object_r：mysqld_db_t：s0 install.log
-rw-r--r--．root root system_u：object_r：mysqld_db_t：s0 install.log.syslog

2、确定了原来数据目录属性为mysqld_db_t，将你的新datadir属性更改为mysqld_db_t：

chcon -R -t mysqld_db_t <你的新datadir>

3、修改完后，查看你的新datadir的属性，ls -Z <你的新datadir>

若发现存在部分目录属性还不是mysqld_db_t，使用命令(chcon -R -t mysqld_db_t <你的目录>)继续修改

4、使用systemctl start mysqld重启即可。