mysql修改数据库存储路径（绝对可以避免你踩坑）

为什么要更改mysql的默认存储路径?原因很简单，在生产环境下，mysql的数据、索引都会很大，而mysql的默认存储路径是/val/lib/mysql,这就出现了问题，在分配磁盘的时候，一般/home的存储空间是最多的，那么我们的mysql就必须放在/home下面才能支撑更大的数据存储。

1. 查看当前mysql数据库存放位置

• 首先，mysql采用默认方式已经安装成功，并启动
• 其次，登录mysql，执行select @@datadir查看数据库路径
  • 一般来说，mysql数据库的存储路径为：/var/lib/mysql

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

2. 更改数据库存储路径

• 拷贝原数据路径到目的路径

cp -R /var/lib/mysql /mysqldata/

• 更改新的目的数据库权限，用于service启动

chown -R mysql:mysql /mysqldata/mysql

• 修改mysql配置文件：
  • 配置文件为：/etc/my.cnf；
  • 修改[mysql]中的socket=/mysqldata/mysql/mysql.sock
  • 修改[mysqld]中的datadir=/mysqldata/mysql; socket=/mysqldata/mysql/mysql.sock，其他保持不变

以下是一个示例：数据拷贝到了/Tempdata/cluster/目录下。

 For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html


[mysql]
socket=/Tempdata/cluster/mysql/mysql.sock


[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

datadir=/Tempdata/cluster/mysql
socket=/Tempdata/cluster/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

• 重启服务

systemctl restart mysqld.service

• 登录查看是否已经完成

mysql> select @@datadir;
+-------------------+
| @@datadir         |
+-------------------+
| /mysqldata/mysql/ |
+-------------------+
1 row in set (0.00 sec)

3. 错误排查

在我们重新配置mysql配置文件时，重新启动服务，可能会遇到错误。接下来我将讲述如何进行查错。

总共有三种方式进行查错：

• 第一种：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.

• 第二种：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.

• 第三种：查看日志文件，可以通过查看/etc/my.cnf配置文件中的log地址

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@localhost ~]# cat /var/log/mysqld.log
...
2020-03-13T18:53:16.099273Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2020-03-13T18:53:16.099347Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2020-03-13T18:53:16.099357Z 0 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
2020-03-13T18:53:16.099369Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-03-13T18:53:16.718886Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-03-13T18:53:16.718953Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-03-13T18:53:16.718962Z 0 [ERROR] Failed to initialize builtin plugins.
2020-03-13T18:53:16.718966Z 0 [ERROR] Aborting

4. 解决方案

这里以我自己遇到的一个问题为例，其他的请查看技术文档。

• 解决办法

[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))

• 也可以永久关闭：主要是修改/etc/selinux/config配置文件中SELINUX=enforcing为SELINUX=disabled

[root@localhost ~]# cp -r /etc/selinux/config /etc/selinux/config.bak

[root@localhost ~]# sed -i 's/SELINUX=enforcing/\SELINUX=disabled/' /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=disabled
# 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