为什么要更改mysql的默认存储路径?原因很简单,在生产环境下,mysql的数据、索引都会很大,而mysql的默认存储路径是/val/lib/mysql,这就出现了问题,在分配磁盘的时候,一般/home的存储空间是最多的,那么我们的mysql就必须放在/home下面才能支撑更大的数据存储。
1. 查看当前mysql数据库存放位置
- 首先,mysql采用默认方式已经安装成功,并启动
- 其次,登录mysql,执行
select @@datadir
查看数据库路径- 一般来说,mysql数据库的存储路径为:
/var/lib/mysql
- 一般来说,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