CentOS 7 下修改 MySQL 数据库存放位置

MySQL 安装后有默认的数据库存储路径,有时候因数据盘空间限制问题,我们需要将数据存储到空间较大的磁盘或目录下,因此需要修改 MySQL 数据存储路径。现将 MySQL 数据库存放在 /data/mysql 下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 52795
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> show variables like '%datadir%'; //查询当前数据库在磁盘上的存储路径
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.03 sec)

mysql> quit
Bye
[root@localhost ~]# ls /var/lib/mysql/
auto.cnf ib_logfile1 mysql RPM_UPGRADE_HISTORY
ibdata1 localhost.localdomain.err mysql.sock RPM_UPGRADE_MARKER-LAST
ib_logfile0 localhost.localdomain.pid performance_schema test
[root@localhost ~]# systemctl stop mysqld //停止MySQL服务
[root@localhost ~]# cp -R /var/lib/mysql /data/ //复制数据至目标目录
[root@localhost ~]# find / -name 'my.cnf' //查找数据库配置文件
/usr/my.cnf
[root@localhost ~]# vim /usr/my.cnf

datadir = "/data/mysql" //修改数据库存储路径

:wq! #保存退出

[root@localhost ~]# systemctl start mysqld
Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/localhost.localdomain.pid).
[root@localhost ~]#

问题:Starting MySQL. ERROR! The server quit without updating PID file。可能的原因:

1.可能是 /data/mysql/localhost.localdomain.pid 文件没有写的权限
解决方法:给予权限,执行 “chown -R mysql:mysql /data/mysql” 和 “chmod -R 755 /data/mysql” 然后重新启动 MySQL。

2.可能进程里已经存在 MySQL 进程
解决方法:用命令 “ps -ef | grep mysqld” 查看是否有mysqld进程,如果有使用 “kill -9 进程号” 杀死,然后重新启动 MySQL。

3.可能是第二次在机器上安装 MySQL,有残余数据影响服务的启动。
解决方法:打开 MySQL 的数据目录看看,如果存在 mysql-bin.index,删除即可。

4.SELinux 惹的祸,如果是 CentOS 系统,默认会开启 SELinux
解决方法:关闭 SELinux,打开 /etc/selinux/config,把 SELINUX=enforcing 改为 SELINUX=disabled 后保存退出重启。

本文的问题就在 SELinux 上,在CentOS 7及以上更改 MySQL 的数据目录,必须禁用 SELinux,否则 MySQL 无法启动。

解决办法:

1
2
3
4
5
6
7
8
9
# vim /etc/selinux/config

#SELINUX=enforcing #注释
#SELINUXTYPE=targeted #注释
SELINUX=disabled #增加

:wq! #保存退出

# setenforce 0 #使配置立即生效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[root@localhost ~]# systemctl start mysqld
Starting MySQL.. SUCCESS!
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 52796
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> show variables like '%datadir%'; //可以看到当前数据库在磁盘上的存储路径已更新
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| datadir | /data/mysql/ |
+---------------+--------------+
1 row in set (0.03 sec)

mysql>

Powered by AppBlog.CN     浙ICP备14037229号

Copyright © 2012 - 2020 APP开发技术博客 All Rights Reserved.

访客数 : | 访问量 :