南山草舍

0%

修改Mariadb数据库存储路径

发表于 分类于

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

关闭数据库服务

1
systemctl stop mariadb.service

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

1
2
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

1
2
3
4
5
6
7
8
9
10
11
[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中配置项

1
ProtectHome=false

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
[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
--------------

参考链接：https://stackoverflow.com/questions/38529205/mariadb-cannot-start-after-update-warning-cant-create-test-file-home-mysql