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

2023-09-03 17:12| 来源: 网络整理| 查看: 265

为什么要更改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

【本文地址】

公司简介

联系我们