拿到手的服务器大量空间在/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/
Be First to Comment