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)

314 03:55:06 localhost.localdomain systemd[1]: Failed to start MySQL Server.
314 03:55:06 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
314 03:55:06 localhost.localdomain systemd[1]: mysqld.service failed.
314 03:55:06 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart.
314 03:55:06 localhost.localdomain systemd[1]: Stopped MySQL Server.
314 03:55:06 localhost.localdomain systemd[1]: start request repeated too quickly for mysqld.service
314 03:55:06 localhost.localdomain systemd[1]: Failed to start MySQL Server.
314 03:55:06 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
314 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.
314 03:55:04 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
314 03:55:04 localhost.localdomain systemd[1]: mysqld.service failed.
314 03:55:05 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart.
314 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.
314 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.
314 03:55:06 localhost.localdomain mysqld[73992]: Initialization of mysqld failed: 0
314 03:55:06 localhost.localdomain systemd[1]: mysqld.service: control process exited, code=exited status=1
314 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.
314 03:55:06 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
314 03:55:06 localhost.localdomain systemd[1]: mysqld.service failed.
314 03:55:06 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart.
314 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.
314 03:55:06 localhost.localdomain systemd[1]: start request repeated too quickly for mysqld.service
314 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.
314 03:55:06 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
314 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=enforcingSELINUX=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

  • 1
    点赞
  • 29
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL数据库中,可以通过两种方法保存图片。第一种方法是将图片保存的路径存储数据库中,这意味着在数据库中创建一个字段,用来保存图片的路径。这样,每当需要显示图片时,只需要从数据库中获取图片路径并在页面上显示即可。第二种方法是将图片以二进制数据流的形式直接写入数据库字段中,这意味着在数据库中创建一个字段,用来保存图片的二进制数据。这样,每当需要显示图片时,可以从数据库中读取二进制数据,并将其转换为图片显示出来。 在使用MySQL数据库保存图片时,可以使用JSP编程模式来实现。首先,需要建立一个数据库表来存储图片信息,表中可以包含一个字段来保存图片的路径或者二进制数据。然后,在JSP中可以使用数据库访问技术来处理图片信息,将图片保存到数据库或者从数据库中读取并显示出来。 具体实现的步骤如下: 1. 创建一个用来存储图片信息的数据库表,其中包含一个字段用来保存图片的路径或者二进制数据。 2. 在JSP中连接数据库,并使用相应的语句将图片信息保存到数据库表中,或者从数据库表中读取图片信息。 3. 将图片保存到数据库时,可以使用二进制流的形式,将图片转换为字节数组,并将字节数组保存到数据库的字段中。 4. 从数据库中读取图片时,可以将存储的二进制数据转换为图片,并在页面上显示出来。 需要注意的是,保存图片到数据库或者从数据库中读取图片时,需要使用相应的数据库访问技术和编程语言来实现。具体的实现方式可以根据具体的项目需求和技术栈进行选择。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [mysql存储图片](https://blog.csdn.net/weixin_53370274/article/details/121942385)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *2* *3* [MySQL如何存储图片](https://blog.csdn.net/WYL2724/article/details/7558912)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值