解决mariadb/mysql数据目录放在/usr/local或者/home下,启动失败问题.

前言

我近段时间安装mariadb数据库,一般情况下我们都将数据目录放到/data下面,但是我这里有一台主服务器的数据复制的时候,是往/data下复制的,所以只能选择其他的目录.我就在/usr/local下创建了data/mysql目录.启动时就报错.
我写的一篇完整安装mariadb的文章:
linux下卸载,安装mariadb数据库,设置外部访问(二进制包安装和卸载,非docker):安装过程

一、错误截图

[root@localhost system]# systemctl status mariadb.service
● mariadb.service - MariaDB 10.2.43 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2023-02-14 08:58:33 CST; 10s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 13652 ExecStart=/usr/local/mysql/bin/mysqld $MYSQLD_OPTS --basedir=/usr/local/mysql $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 13494 ExecStartPre=/bin/sh -c [ ! -e /usr/local/mysql/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/local/mysql/bin/..; /usr/local/mysql/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 13492 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 13652 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

Feb 14 08:58:32 localhost.localdomain systemd[1]: Starting MariaDB 10.2.43 database server...
Feb 14 08:58:33 localhost.localdomain mysqld[13652]: 2023-02-14  8:58:33 140151958214464 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.2.43-MariaDB-log)...3652 ...
Feb 14 08:58:33 localhost.localdomain mysqld[13652]: 2023-02-14  8:58:33 140151958214464 [Warning] Can't create test file /usr/local/data/mysql/localhos...wer-test
Feb 14 08:58:33 localhost.localdomain mysqld[13652]: 2023-02-14  8:58:33 140151958214464 [ERROR] mysqld: File './mysql-bin.index' not found (Errcode: 30...system")
Feb 14 08:58:33 localhost.localdomain mysqld[13652]: 2023-02-14  8:58:33 140151958214464 [ERROR] Aborting
Feb 14 08:58:33 localhost.localdomain systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Feb 14 08:58:33 localhost.localdomain systemd[1]: Failed to start MariaDB 10.2.43 database server.
Feb 14 08:58:33 localhost.localdomain systemd[1]: Unit mariadb.service entered failed state.
Feb 14 08:58:33 localhost.localdomain systemd[1]: mariadb.service failed.
Hint: Some lines were ellipsized, use -l to show in full.
[root@localhost system]# 

二、解决方法和错误原因

1.进入到mysql的文件目录,并非数据目录

2.进入到mariadb.servic目录

3.修改mariadb.service文件

vim mariadb.service

把这两个值修改一下(修改完后直接wq!)

4.再将系统目录下的给替换掉

cp mariadb.service /usr/lib/systemd/system/
systemctl daemon-reload
systemctl start mariadb.service
systemctl status mariadb.service

5.成功解决,分析原因;

按道理说,不需要修改文件目录下的 mariadb.service文件,只需要修改/usr/lib/systemd/system/ 下的 mariadb.service就行,但是我这里就是不行.
为什么不建议修改文件目录下的,因为在包升级的时候,会被覆盖.

# Prevent writes to /usr, /boot, and /etc
#防止写入/usr、/boot和/etc
ProtectSystem=false

# Prevent accessing /home, /root and /run/user
#防止访问/home、/root和/run/user
ProtectHome=false

当你的数据目录在/home, /root and /run/user 目录下,需要修改成ProtectHome=false
当你的数据目录在/usr、/boot和/etc 目录下,需要修改成ProtectSystem=false
我是直接两个都修改了.修改后会不会有安全问题,等我了解后再发出来,如果有伙伴知道,可以评论或者私信我.

6.mariadb.service解析(不全)

#讲述提醒前言,包含不要再文件目录修改mariadb.service和原因
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/mariadb.service",
# containing
#	.include /usr/lib/systemd/system/mariadb.service
#	...make your changes here...
# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will be parsed
# after the file mariadb.service itself is parsed.
#
# For more info about custom unit files, see systemd.unit(5) or
# https://mariadb.com/kb/en/mariadb/systemd/
#
# Copyright notice:
#
# This file is free software; you can redistribute it and/or modify it
# under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation; either version 2.1 of the License, or
# (at your option) any later version.

[Unit]
#描述
Description=MariaDB 10.2.43 database server
Documentation=man:mysqld(8)
Documentation=https://mariadb.com/kb/en/library/systemd/
#在after=的程序启动之后程序才会启动
After=network.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service
Alias=mysqld.service


[Service]

##############################################################################
## Core requirements
##
#这个Type参数作用是用来标志systemctl是否要跟踪处理服务进程是否启动成功
# simple, exec, forking, oneshot, dbus, notify or idle
Type=notify

# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.
PrivateNetwork=false

##############################################################################
## Package maintainers
##
#用户和组 
User=mysql
Group=mysql

# To allow memlock to be used as non-root user if set in configuration
#允许在配置中设置后以非root用户使用memlock
CapabilityBoundingSet=CAP_IPC_LOCK

# Prevent writes to /usr, /boot, and /etc
#防止写入/usr、/boot和/etc
ProtectSystem=false

# Doesn't yet work properly with SELinux enabled
#不能使用SELinux启用
# NoNewPrivileges=true

PrivateDevices=true

# Prevent accessing /home, /root and /run/user
#防止访问/home、/root和/run/user
ProtectHome=false

# Execute pre and post scripts as root, otherwise it does it as User=
#作为根用户执行预先和后续脚本，否则以用户身份执行=(仅启动时允许)
PermissionsStartOnly=true



# Perform automatic wsrep recovery. When server is started without wsrep,
# galera_recovery simply returns an empty string. In any case, however,
# the script is not expected to return with a non-zero status.
# It is always safe to unset _WSREP_START_POSITION environment variable.
# Do not panic if galera_recovery script is not available. (MDEV-10538)
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "[ ! -e /usr/local/mysql/bin/galera_recovery ] && VAR= || \
 VAR=`cd /usr/local/mysql/bin/..; /usr/local/mysql/bin/galera_recovery`; [ $? -eq 0 ] \
 && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"

# Needed to create system tables etc.
# ExecStartPre=/usr/local/mysql/scripts/mysql_install_db -u mysql

# Start main service
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
# Use the [Service] section and Environment="MYSQLD_OPTS=...".
# This isn't a replacement for my.cnf.
# _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug https://bugzilla.redhat.com/show_bug.cgi?id=547485
ExecStart=/usr/local/mysql/bin/mysqld $MYSQLD_OPTS --basedir=/usr/local/mysql $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

# Unset _WSREP_START_POSITION environment variable.
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"



KillSignal=SIGTERM

# Don't want to see an automated SIGKILL ever
SendSIGKILL=no

# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
#仅重启崩溃的服务器，在故障时也会重启，例如，当 my.cnf 包含未知选项时。
#该服务在中止时应重新启动
Restart=on-abort
RestartSec=5s

UMask=007

##############################################################################
## USERs can override
##
##
## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
## and adding/setting the following under [Service] will override this file's
## settings.

# Useful options not previously available in [mysqld_safe]

# Kernels like killing mysqld when out of memory because its big.
# Lets temper that preference a little.
# OOMScoreAdjust=-600

# Explicitly start with high IO priority
# BlockIOWeight=1000

# If you don't use the /tmp directory for SELECT ... OUTFILE and
# LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.
PrivateTmp=false

# Set an explicit Start and Stop timeout of 900 seconds (15 minutes!)
# this is the same value as used in SysV init scripts in the past
# Galera might need a longer timeout, check the KB if you want to change this:
# https://mariadb.com/kb/en/library/systemd/#configuring-the-systemd-service-timeout
TimeoutStartSec=900
TimeoutStopSec=900

##
## Options previously available to be set via [mysqld_safe]
## that now needs to be set by systemd config files as mysqld_safe
## isn't executed.
##

# Number of files limit. previously [mysqld_safe] open-files-limit
LimitNOFILE=32768

# Maximium core size. previously [mysqld_safe] core-file-size
# LimitCore=

# Nice priority. previously [mysqld_safe] nice
# Nice=-5

# Timezone. previously [mysqld_safe] timezone
# Environment="TZ=UTC"

# Library substitutions. previously [mysqld_safe] malloc-lib with explicit paths
# (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
# Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=

# Flush caches. previously [mysqld_safe] flush-caches=1
# ExecStartPre=sync
# ExecStartPre=sysctl -q -w vm.drop_caches=3

# numa-interleave=1 equalivant
# Change ExecStart=numactl --interleave=all /usr/local/mysql/bin/mysqld......

# crash-script equalivent
# FailureAction=