修改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/