Centos7下修改MySQL5.7数据库文件存放路径过程

1 查看当前Mysql数据存储路径，如下可知当前数据存放路径为/var/lib/mysql

#################### 查看/etc/my.cnf 配置文件内容 #######################
[root@localhost ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
 
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
############### 查看/var/lib/mysql路径下文件列表 ####################
[root@localhost ~]# ls /var/lib/mysql
auto.cnf    client-cert.pem  ibdata1      ibtmp1      mysql.sock.lock     public_key.pem   sys
ca-key.pem  client-key.pem   ib_logfile0  mysql       performance_schema  server-cert.pem  test
ca.pem      ib_buffer_pool   ib_logfile1  mysql.sock  private_key.pem     server-key.pem

2 停止mysqld服务，创建新的mysql数据存放路径/home/data

################# 停止mysqld服务 #####################
[root@localhost ~]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
[root@localhost ~]#
 
########### 创建新的mysql数据存放路径 #################
[root@localhost ~]# mkdir -p /home/data/
[root@localhost ~]# ls /home/data/
[root@localhost ~]#

3 修改/etc/my.cnf，在[mysqld]选项组下配置新的文件路径

[root@localhost ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
 
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/home/data/mysql
socket=/home/data/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@localhost ~]#

4 移动/var/lib/mysql整个目录到新的文件夹/home/data/

[root@localhost ~]# mv /var/lib/mysql /home/data/
[root@localhost ~]# ls /home/data/mysql/
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  performance_schema  public_key.pem   server-key.pem  test
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  mysql        private_key.pem     server-cert.pem  sys
[root@localhost ~]#

注意：我这使用了mv来移动mysql文件夹，请保持一致
5 启动mysqld服务，解决因selinux开启导致的启动错误

[root@localhost ~]# service mysqld start
Redirecting to /bin/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.

可以看到系统报错，这是因为没有关闭selinux导致的，关闭selinux然后重新启动mysqld服务，成功

[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce
Permissive
[root@localhost ~]#
[root@localhost ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@localhost ~]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-04-18 04:06:21 EDT; 3min 25s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1964 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1947 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1967 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1967 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
 
Apr 18 04:06:20 localhost.localdomain systemd[1]: Starting MySQL Server...
Apr 18 04:06:21 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]#

6 使用mysql -u root -p来登录mysql，发生因未配置[client]选项组中参数导致的错误

[root@localhost ~]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@localhost ~]#

如上，发生了错误
7 修改/etc/my.cnf，修改[client]选项组下的配置信息

[root@localhost ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
 
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/home/data/mysql
socket=/home/data/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
[client]
socket=/home/data/mysql/mysql.sock
 
[root@localhost ~]#

如上所示，添加了[client]选项组下面的socket配置信息
8 重启mysqld服务(service mysqld restart)，使用mysql -u root -p登录mysql并测试成功

[root@localhost ~]#
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select * from student;
+-----+------+
| id  | name |
+-----+------+
| 111 | AAA  |
| 222 | BBB  |
+-----+------+
2 rows in set (0.00 sec)
 
mysql>

9 重启操作系统，发现mysqld服务启动启动时发生错误

[root@localhost ~]# service mysqld status
Redirecting to /bin/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 Sat 2020-04-18 04:27:25 EDT; 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1749 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 1732 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 
Apr 18 04:27:24 localhost.localdomain systemd[1]: Failed to start MySQL Server.
Apr 18 04:27:24 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
Apr 18 04:27:24 localhost.localdomain systemd[1]: mysqld.service failed.
Apr 18 04:27:25 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart.
Apr 18 04:27:25 localhost.localdomain systemd[1]: Stopped MySQL Server.
Apr 18 04:27:25 localhost.localdomain systemd[1]: start request repeated too quickly for mysqld.service
Apr 18 04:27:25 localhost.localdomain systemd[1]: Failed to start MySQL Server.
Apr 18 04:27:25 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
Apr 18 04:27:25 localhost.localdomain systemd[1]: mysqld.service failed.

上面启动失败是selinux在Linux重启后重新启用了造成的
10 修改selinux配置文件/etc/selinux/config，让selinux永久工作在permissive模式下

[root@localhost ~]# vi /etc/selinux/config
[root@localhost ~]# cat /etc/selinux/config
 
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
# SELINUX=enforcing
SELINUX=permissive
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

11 再次重启操作系统，查看mysqld状态，并登录测试数据库，一切正常

keycer@LAPTOP-8JTG7IOM MINGW64 ~/Desktop
$ ssh root@192.168.43.4
root@192.168.43.4's password:
Last login: Sat Apr 18 04:27:29 2020 from laptop-8jtg7iom
[root@localhost ~]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-04-18 04:34:46 EDT; 27s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1205 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1162 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1228 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1228 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Apr 18 04:34:45 localhost.localdomain systemd[1]: Starting MySQL Server...
Apr 18 04:34:46 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from student;
+-----+------+
| id  | name |
+-----+------+
| 111 | AAA  |
| 222 | BBB  |
+-----+------+
2 rows in set (0.00 sec)
mysql>