redhalt更改mysql存储路径_修改Mariadb数据库存储路径

拿到手的服务器大量空间在/home分区下，没有在/下，考虑到后期数据库增长量，/分区空间可能不够，决定把Mariadb数据库数据存储路径修改到/home分区下；以下为修改过程

关闭数据库服务

systemctl stop mariadb.service

新建新的存储路径，并移动之前的文件

mkdir /home/data

mv /var/lib/mysql/ /home/data/

修改配置文件

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf #如果是新装的数据库，可以复制一个模板配置文件过来

注：datadir上面的五行是设置默认字符集为utf8mb4，否则emoji表情是无法正确存储的。

vim /etc/my.cnf

[client]

socket = /home/data/mysql/mysql.sock

[mysqld]

init_connect='SET collation_connection = utf8mb4_unicode_ci'

init_connect='SET NAMES utf8mb4'

character_set_server=utf8mb4

collation-server=utf8mb4_unicode_ci

skip-character-set-client-handshake=true

datadir=/home/data/mysql/

报错

报错:“[Warning] Can’t create test file /home/data/mysql/sthbb_rwdcdb_36_123.lower-test”.

解决方式: 修改/usr/lib/systemd/system/mariadb.service中配置项

ProtectHome=false

验证

[root@sthbb_rwdcdb_36_123 data]# mysql -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 3

Server version: 10.1.36-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show global variables like "%datadir%";

+---------------+-------------------+

| Variable_name | Value |

+---------------+-------------------+

| datadir | /home/data/mysql/ |

+---------------+-------------------+

1 row in set (0.00 sec)

MariaDB [(none)]> status

--------------

mysql Ver 15.1 Distrib 10.1.36-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 3

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server: MariaDB

Server version: 10.1.36-MariaDB MariaDB Server

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8mb4

Db characterset: utf8mb4

Client characterset: utf8mb4

Conn. characterset: utf8mb4

UNIX socket: /home/data/mysql/mysql.sock

Uptime: 2 min 29 sec

Threads: 1 Questions: 5 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.033

--------------

注：本文转自http://islocal.cc/arlo/1b10686d/