如何修改MySQL数据库的存储路径及遇到的SELINUX权限问题如何解决
Contents
零背景说明
本文档用于记录在CentOS7.9服务器上，将已经安装的MySQL数据库从默认的存储路径/var/lib/mysql，迁移到新的/data/mysql路径下的操作步骤，以及迁移过程中遇到问题的解决方案。
操作系统信息：CentOS 7.9
MySQL数据库信息：MySQL 5.7.40
一操作步骤
1创建/data/mysql
mkdir -p /data/mysql
2停止MySQL数据库
systemctl stop mysqld
3 cp /var/lib/mysql/* /data/mysql
4修改配置文件/etc/my.cnf
将配置文件中的参数：
datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock
改为
datadir=/data/mysql socket=/data/mysql/mysql.sock
5 启动数据库
systemctl start mysqld
正常情况下，通过执行该命令，就可以将数据库启动起来。但是，这里操作就遇到了一些问题：
数据库启动报错：
[root@dbprimary ~]# 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. [root@dbprimary ~]#
查看MySQL数据库日志如下：
/var/log/mysqld.log2023-08-29T03:22:57.078218Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2023-08-29T03:22:57.078461Z 0 [Warning] Can't create test file /data/mysql/dbprimary.lower-test 2023-08-29T03:22:57.078510Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.40) starting as process 20949 ... 2023-08-29T03:22:57.080412Z 0 [Warning] Can't create test file /data/mysql/dbprimary.lower-test 2023-08-29T03:22:57.080444Z 0 [Warning] Can't create test file /data/mysql/dbprimary.lower-test 2023-08-29T03:22:57.081807Z 0 [Note] InnoDB: PUNCH HOLE support available 2023-08-29T03:22:57.081839Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2023-08-29T03:22:57.081842Z 0 [Note] InnoDB: Uses event mutexes 2023-08-29T03:22:57.081844Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2023-08-29T03:22:57.081848Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.12 2023-08-29T03:22:57.081850Z 0 [Note] InnoDB: Using Linux native AIO 2023-08-29T03:22:57.082046Z 0 [Note] InnoDB: Number of pools: 1 2023-08-29T03:22:57.082159Z 0 [Note] InnoDB: Using CPU crc32 instructions 2023-08-29T03:22:57.084466Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2023-08-29T03:22:57.089854Z 0 [Note] InnoDB: Completed initialization of buffer pool 2023-08-29T03:22:57.093437Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2023-08-29T03:22:57.103527Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation. 2023-08-29T03:22:57.103549Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory. 2023-08-29T03:22:57.103562Z 0 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions 2023-08-29T03:22:57.103570Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error 2023-08-29T03:22:57.704217Z 0 [ERROR] Plugin 'InnoDB' init function returned error. 2023-08-29T03:22:57.704282Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2023-08-29T03:22:57.704299Z 0 [ERROR] Failed to initialize builtin plugins. 2023-08-29T03:22:57.704302Z 0 [ERROR] Aborting 2023-08-29T03:22:57.704338Z 0 [Note] Binlog end 2023-08-29T03:22:57.704431Z 0 [Note] Shutting down plugin 'CSV' 2023-08-29T03:22:57.704687Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
操作系统日志/var/log/messages如下：
Aug 29 11:22:01 dbprimary systemd: Started Session 48 of user root. Aug 29 11:22:52 dbprimary systemd: Starting MySQL Server... Aug 29 11:22:53 dbprimary mysqld: Initialization of mysqld failed: 0 Aug 29 11:22:53 dbprimary systemd: mysqld.service: control process exited, code=exited status=1 Aug 29 11:22:53 dbprimary systemd: Failed to start MySQL Server. Aug 29 11:22:53 dbprimary systemd: Unit mysqld.service entered failed state. Aug 29 11:22:53 dbprimary systemd: mysqld.service failed. Aug 29 11:22:53 dbprimary systemd: mysqld.service holdoff time over, scheduling restart. Aug 29 11:22:53 dbprimary systemd: Stopped MySQL Server. Aug 29 11:22:53 dbprimary systemd: Starting MySQL Server... Aug 29 11:22:54 dbprimary mysqld: Initialization of mysqld failed: 0 Aug 29 11:22:54 dbprimary systemd: mysqld.service: control process exited, code=exited status=1 Aug 29 11:22:54 dbprimary systemd: Failed to start MySQL Server. Aug 29 11:22:54 dbprimary systemd: Unit mysqld.service entered failed state. Aug 29 11:22:54 dbprimary systemd: mysqld.service failed. Aug 29 11:22:54 dbprimary systemd: mysqld.service holdoff time over, scheduling restart. Aug 29 11:22:54 dbprimary systemd: Stopped MySQL Server. Aug 29 11:22:54 dbprimary systemd: Starting MySQL Server... Aug 29 11:22:55 dbprimary mysqld: Initialization of mysqld failed: 0 Aug 29 11:22:55 dbprimary systemd: mysqld.service: control process exited, code=exited status=1 Aug 29 11:22:55 dbprimary systemd: Failed to start MySQL Server. Aug 29 11:22:55 dbprimary systemd: Unit mysqld.service entered failed state. Aug 29 11:22:55 dbprimary systemd: mysqld.service failed. Aug 29 11:22:55 dbprimary systemd: mysqld.service holdoff time over, scheduling restart. Aug 29 11:22:55 dbprimary systemd: Stopped MySQL Server. Aug 29 11:22:55 dbprimary systemd: Starting MySQL Server... Aug 29 11:22:56 dbprimary mysqld: Initialization of mysqld failed: 0 Aug 29 11:22:56 dbprimary systemd: mysqld.service: control process exited, code=exited status=1 Aug 29 11:22:56 dbprimary systemd: Failed to start MySQL Server. Aug 29 11:22:56 dbprimary systemd: Unit mysqld.service entered failed state. Aug 29 11:22:56 dbprimary systemd: mysqld.service failed. Aug 29 11:22:56 dbprimary systemd: mysqld.service holdoff time over, scheduling restart. Aug 29 11:22:56 dbprimary systemd: Stopped MySQL Server. Aug 29 11:22:56 dbprimary systemd: Starting MySQL Server... Aug 29 11:22:57 dbprimary mysqld: Initialization of mysqld failed: 0 Aug 29 11:22:57 dbprimary systemd: mysqld.service: control process exited, code=exited status=1 Aug 29 11:22:57 dbprimary systemd: Failed to start MySQL Server. Aug 29 11:22:57 dbprimary systemd: Unit mysqld.service entered failed state. Aug 29 11:22:57 dbprimary systemd: mysqld.service failed. Aug 29 11:22:57 dbprimary systemd: mysqld.service holdoff time over, scheduling restart. Aug 29 11:22:57 dbprimary systemd: Stopped MySQL Server. Aug 29 11:22:57 dbprimary systemd: start request repeated too quickly for mysqld.service Aug 29 11:22:57 dbprimary systemd: Failed to start MySQL Server. Aug 29 11:22:57 dbprimary systemd: Unit mysqld.service entered failed state. Aug 29 11:22:57 dbprimary systemd: mysqld.service failed. Aug 29 11:23:01 dbprimary systemd: Started Session 49 of user root.
可以看到MySQL启动时，提示有：
2023-08-29T03:22:57.078461Z 0 [Warning] Can't create test file /data/mysql/dbprimary.lower-test
多数是权限问题，数据库在启动过程中，不能创建测试文件，导致数据库启动失败。
二 分析解决问题
1 查看系统SELINUX状态
[root@dbprimary ~]# getenforce Enforcing [root@dbprimary ~]#
2 对比查看新旧存储路径下的权限
[root@dbprimary ~]# ls -Z /var/lib/mysql -rw-r-----. mysql mysql system_u:object_r:mysqld_db_t:s0 auto.cnf -rw-------. mysql mysql system_u:object_r:mysqld_db_t:s0 ca-key.pem -rw-r--r--. mysql mysql system_u:object_r:mysqld_db_t:s0 ca.pem -rw-r--r--. mysql mysql system_u:object_r:mysqld_db_t:s0 client-cert.pem -rw-------. mysql mysql system_u:object_r:mysqld_db_t:s0 client-key.pem -rw-r-----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_buffer_pool -rw-r-----. mysql mysql system_u:object_r:mysqld_db_t:s0 ibdata1 -rw-r-----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_logfile0 -rw-r-----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_logfile1 drwxr-x---. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql drwxr-x---. mysql mysql system_u:object_r:mysqld_db_t:s0 performance_schema -rw-------. mysql mysql system_u:object_r:mysqld_db_t:s0 private_key.pem -rw-r--r--. mysql mysql system_u:object_r:mysqld_db_t:s0 public_key.pem -rw-r--r--. mysql mysql system_u:object_r:mysqld_db_t:s0 server-cert.pem -rw-------. mysql mysql system_u:object_r:mysqld_db_t:s0 server-key.pem drwxr-x---. mysql mysql system_u:object_r:mysqld_db_t:s0 sys [root@dbprimary ~]# [root@dbprimary ~]# ls -Z /data/mysql/ -rw-r-----. mysql mysql unconfined_u:object_r:default_t:s0 auto.cnf -rw-------. mysql mysql unconfined_u:object_r:default_t:s0 ca-key.pem -rw-r--r--. mysql mysql unconfined_u:object_r:default_t:s0 ca.pem -rw-r--r--. mysql mysql unconfined_u:object_r:default_t:s0 client-cert.pem -rw-------. mysql mysql unconfined_u:object_r:default_t:s0 client-key.pem -rw-r-----. mysql mysql unconfined_u:object_r:default_t:s0 ib_buffer_pool -rw-r-----. mysql mysql unconfined_u:object_r:default_t:s0 ibdata1 -rw-r-----. mysql mysql unconfined_u:object_r:default_t:s0 ib_logfile0 -rw-r-----. mysql mysql unconfined_u:object_r:default_t:s0 ib_logfile1 drwxr-x---. mysql mysql unconfined_u:object_r:default_t:s0 mysql drwxr-x---. mysql mysql unconfined_u:object_r:default_t:s0 performance_schema -rw-------. mysql mysql unconfined_u:object_r:default_t:s0 private_key.pem -rw-r--r--. mysql mysql unconfined_u:object_r:default_t:s0 public_key.pem -rw-r--r--. mysql mysql unconfined_u:object_r:default_t:s0 server-cert.pem -rw-------. mysql mysql unconfined_u:object_r:default_t:s0 server-key.pem drwxr-x---. mysql mysql unconfined_u:object_r:default_t:s0 sys [root@dbprimary ~]#
发现权限不同，旧路径/var/lib/mysql下的权限为mysqld_db_t，而新的存储路径/data/mysql权限为default_t。
3 解决方案：禁用SELINUX
[root@dbprimary ~]# setenforce 0 [root@dbprimary ~]# getenforce Permissive [root@dbprimary ~]# systemctl start mysqld [root@dbprimary ~]#
查看MySQL日志和操作系统日志，发现一切正常，MySQL启动正常：
/var/log/mysqld.log2023-08-29T03:25:16.272146Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2023-08-29T03:25:16.273489Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.40) starting as process 21880 ... 2023-08-29T03:25:16.276190Z 0 [Note] InnoDB: PUNCH HOLE support available 2023-08-29T03:25:16.276217Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2023-08-29T03:25:16.276220Z 0 [Note] InnoDB: Uses event mutexes 2023-08-29T03:25:16.276224Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2023-08-29T03:25:16.276227Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.12 2023-08-29T03:25:16.276232Z 0 [Note] InnoDB: Using Linux native AIO 2023-08-29T03:25:16.276468Z 0 [Note] InnoDB: Number of pools: 1 2023-08-29T03:25:16.276548Z 0 [Note] InnoDB: Using CPU crc32 instructions 2023-08-29T03:25:16.277876Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2023-08-29T03:25:16.282723Z 0 [Note] InnoDB: Completed initialization of buffer pool 2023-08-29T03:25:16.284082Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2023-08-29T03:25:16.295988Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2023-08-29T03:25:16.302643Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2023-08-29T03:25:16.302762Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2023-08-29T03:25:16.318286Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2023-08-29T03:25:16.319129Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2023-08-29T03:25:16.319146Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2023-08-29T03:25:16.319693Z 0 [Note] InnoDB: Waiting for purge to start 2023-08-29T03:25:16.369871Z 0 [Note] InnoDB: 5.7.40 started; log sequence number 2755154 2023-08-29T03:25:16.370077Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/ib_buffer_pool 2023-08-29T03:25:16.370217Z 0 [Note] Plugin 'FEDERATED' is disabled. 2023-08-29T03:25:16.371190Z 0 [Note] InnoDB: Buffer pool(s) load completed at 230829 11:25:16 2023-08-29T03:25:16.374702Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. 2023-08-29T03:25:16.374733Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory. 2023-08-29T03:25:16.374738Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2023-08-29T03:25:16.374745Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2023-08-29T03:25:16.375258Z 0 [Warning] CA certificate ca.pem is self signed. 2023-08-29T03:25:16.375296Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory. 2023-08-29T03:25:16.376281Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2023-08-29T03:25:16.376493Z 0 [Note] IPv6 is available. 2023-08-29T03:25:16.376505Z 0 [Note] - '::' resolves to '::'; 2023-08-29T03:25:16.376521Z 0 [Note] Server socket created on IP: '::'. 2023-08-29T03:25:16.383180Z 0 [Note] Event Scheduler: Loaded 0 events 2023-08-29T03:25:16.383319Z 0 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.7.40' socket: '/data/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) /var/log/messagesAug 29 11:25:01 dbprimary systemd: Started Session 51 of user root. Aug 29 11:25:16 dbprimary systemd: Starting MySQL Server... Aug 29 11:25:16 dbprimary systemd: Started MySQL Server. Aug 29 11:26:01 dbprimary systemd: Started Session 52 of user root.
三另外一种解决方案
在不关闭SELINUX的前提下，我们可以通过修改新路径下文件的SELINUX权限和旧路径下的权限保持一致，来解决问题。我们可以通过semanage命令来修改权限，但是该命令默认情况下没有安装，我们需要先安装policycoreutils-python软件包，才能使用该命令。
1 安装policycoreutils-python软件包
[root@dbprimary ~]# yum install policycoreutils-python -y ..... [root@dbprimary ~]#
2 修改权限
[root@dbprimary ~]# ls -Z /data/mysql/ -rw-r-----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 auto.cnf -rw-------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ca-key.pem -rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ca.pem -rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 client-cert.pem -rw-------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 client-key.pem -rw-r-----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_buffer_pool -rw-r-----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ibdata1 -rw-r-----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ib_logfile0 -rw-r-----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ib_logfile1 drwxr-x---. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 mysql drwxr-x---. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 performance_schema -rw-------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 private_key.pem -rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 public_key.pem -rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 server-cert.pem -rw-------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 server-key.pem drwxr-x---. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 sys [root@dbprimary ~]# semanage fcontext -a -t mysqld_db_t "/data(/.*)?" [root@dbprimary ~]# restorecon -Rv /data/ restorecon reset /data/mysql context unconfined_u:object_r:default_t:s0->unconfined_u:object_r:mysqld_db_t:s0 [root@dbprimary ~]# ls -Z /data/ drwxr-x--x. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 mysql [root@dbprimary ~]# ls -Z /data/mysql/ -rw-r-----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 auto.cnf -rw-------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ca-key.pem -rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ca.pem -rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 client-cert.pem -rw-------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 client-key.pem -rw-r-----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_buffer_pool -rw-r-----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ibdata1 -rw-r-----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ib_logfile0 -rw-r-----. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 ib_logfile1 drwxr-x---. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 mysql drwxr-x---. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 performance_schema -rw-------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 private_key.pem -rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 public_key.pem -rw-r--r--. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 server-cert.pem -rw-------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 server-key.pem drwxr-x---. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 sys [root@dbprimary ~]#
修改之后，/data/mysql的SELINUX权限和/var/lib/mysql保持一致了。
3 启动数据库
[root@dbprimary ~]# systemctl start mysqld [root@dbprimary ~]#
正常启动。
四 参考链接
五 补充说明
修改完MySQL的存储路径之后，有可能发现不能直接通过mysql -uroot -p的命令行方式来直接访问数据库。那么此时，可以通过mysql -h127.0.0.1 -uroot -p的命令来访问数据库。
http://www.dailyrazor.com/blog/cant-connect-to-local-mysql-server-through-socket/
和
文章的最后部分有提到ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ 的错误解决办法。
