CentOS7修改MySQL5.7的数据存放路径

2021年08月10日 141点热度 0人点赞 0条评论

原来状态

MySQL安装后默认安装在/var/lib/mysql目录下
配置文件为/etc/my.cnf
根据场景,需要将/var/lib/mysql移动到/home/data/mysql

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

移动过程

1、停止mysql服务

[root@master init.d]# systemctl stop mysqld
[root@master init.d]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Sun 2020-10-18 16:25:03 CST; 14s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 238583 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 238560 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 238585 (code=exited, status=0/SUCCESS)

Aug 11 23:04:58 master.zimdc systemd[1]: Starting MySQL Server...
Aug 11 23:04:59 master.zimdc systemd[1]: Started MySQL Server.
Oct 18 16:24:51 master.zimdc systemd[1]: Stopping MySQL Server...
Oct 18 16:25:03 master.zimdc systemd[1]: Stopped MySQL Server.

2、创建新目录

mkdir /home/data/mysql

3、移动数据

mv /var/lib/mysql/* /home/data/mysql/

4、修改新路径owner

chown mysql:mysql -R /home/data/mysql/

5、修改配置文件/etc/my.cnf

[mysqld]
...
datadir=/home/data/mysql
socket=/home/data/mysql/mysql.sock
...

[client]
...
# 不增加这一行,服务可以启动,本地可以连接,但navicat连接不了
socket=/home/data/mysql/mysql.sock
...

6、启动数据库

[root@master data]# 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@master data]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: start-limit) since Sun 2020-10-18 16:35:58 CST; 11s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 116767 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 116666 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 238585 (code=exited, status=0/SUCCESS)

Oct 18 16:35:58 master.zimdc systemd[1]: Failed to start MySQL Server.
Oct 18 16:35:58 master.zimdc systemd[1]: Unit mysqld.service entered failed state.
Oct 18 16:35:58 master.zimdc systemd[1]: mysqld.service failed.
Oct 18 16:35:58 master.zimdc systemd[1]: mysqld.service holdoff time over, scheduling restart.
Oct 18 16:35:58 master.zimdc systemd[1]: start request repeated too quickly for mysqld.service
Oct 18 16:35:58 master.zimdc systemd[1]: Failed to start MySQL Server.
Oct 18 16:35:58 master.zimdc systemd[1]: Unit mysqld.service entered failed state.
Oct 18 16:35:58 master.zimdc systemd[1]: mysqld.service failed.

7、查看错误原因

[root@master keepalived]# journalctl -xe

Oct 18 16:45:04 master.zimdc systemd[1]: mysqld.service holdoff time over, scheduling restart.
Oct 18 16:45:04 master.zimdc systemd[1]: start request repeated too quickly for mysqld.service
Oct 18 16:45:04 master.zimdc 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.
Oct 18 16:45:04 master.zimdc systemd[1]: Unit mysqld.service entered failed state.
Oct 18 16:45:04 master.zimdc systemd[1]: mysqld.service failed.
Oct 18 16:45:04 master.zimdc setroubleshoot[154023]: SELinux is preventing /usr/sbin/mysqld from write access on the directory mysql. For complete SELinux messages run: sealert -l a0b50a36-99fd-4649-a81a-2ed658f1f9dc
Oct 18 16:45:04 master.zimdc python[154023]: SELinux is preventing /usr/sbin/mysqld from write access on the directory mysql.

                                             *****  Plugin catchall (100. confidence) suggests   **************************

                                             If you believe that mysqld should be allowed write access on the mysql directory by default.
                                             Then you should report this as a bug.
                                             You can generate a local policy module to allow this access.
                                             Do
                                             allow this access for now by executing:
                                             # ausearch -c 'mysqld' --raw | audit2allow -M my-mysqld
                                             # semodule -i my-mysqld.pp

根据提示,是SELinux阻止了服务的启动,关闭SELinux来解决

[root@manager ~]# setenforce 0
[root@manager ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

8、再次启动

[root@master ~]# systemctl start mysqld
[root@master ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-10-18 16:50:16 CST; 2min 14s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3068 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 2156 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3239 (mysqld)
    Tasks: 29
   CGroup: /system.slice/mysqld.service
           └─3239 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Oct 18 16:50:03 master.zimdc systemd[1]: Starting MySQL Server...
Oct 18 16:50:16 master.zimdc systemd[1]: Started MySQL Server.

至此,数据存放路径修改工作完成;

浮生一程

读书-行路-悦人-识物