MariaDB 修改存储路径

1、检查 MariaDB 数据库存放目录

1
mysql -u root
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
43
44
45
46
47
48
MariaDB [(none)]> show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| aria_sync_log_dir | NEWFILE |
| basedir | /usr/ |
| binlog_direct_non_transactional_updates | OFF |
| character_sets_dir | /usr/share/mysql/charsets/ |
| datadir | /var/lib/mysql |
| ignore_db_dirs | |
| innodb_data_home_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| lc_messages_dir | |
| plugin_dir | /usr/lib64/mysql/plugin/ |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
| wsrep_data_home_dir | /var/lib/mysql |
| wsrep_dirty_reads | OFF |
+-----------------------------------------+----------------------------+

MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.7.1-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 4
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.7.1-MariaDB-log MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 9 min 2 sec

Threads: 2 Questions: 5 Slow queries: 0 Opens: 16 Open tables: 10 Queries per second avg: 0.009
--------------

查看 datadir 那一行所指的路径 /var/lib/mysql

查看 UNIX socket

2、停止 mariadb 服务

1
systemctl stop mariadb.service

3、创建新的数据库存放目录

1
mkdir /data/mysql_data

4、复制/移动之前存放数据库目录文件,到新的数据库存放目录位置

1
2
3
cp -R /var/lib/mysql/* /data/mysql_data/
or
mv /var/lib/mysql/* /data/mysql_data

5、修改 mariadb 数据库目录权限以及配置文件

1
chown mysql:mysql -R /data/mysql_data/

6、编辑 Mariadb 的配置文件 /etc/my.cnf

1
2
3
4
5
[client-server]
socket=/data/mysql_data/mysql.sock

[mysqld]
datadir=/data/mysql_data/

7、启动数据库服务

1
systemctl start mariadb.service

8、其他

MariaDB 10.7 @CentOS7

/etc/my.cnf 例子

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
43
44
45
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
socket=/data/mysql_data/mysql.sock

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d


[mysqld]
port=3306
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=/data/mysql_data/
max_connections=900

############### binlog
# binlog日志路径,格式为mariadb-log.00000*,递增
log-bin = /data/mysql_data/bin-log/mariadb-log
log-bin-index = /data/mysql_data/bin-log/mariadb-log.index
# binlog日志保留天数
expire-logs-days = 7
server-id = 1
# binlog日志有三种格式,分别是Statement、MiXED、ROW
binlog-format = ROW
# max-binlog-size = 100M # binlog每个日志文件大小
############### binlog end

############### 慢查询
# touch /data/mysql_data/slow_query_log.log
# chown mysql:mysql /data/mysql_data/slow_query_log.log
slow_query_log=on
slow_query_log_file=/data/mysql_data/slow_query_log.log
long_query_time=2
############### 慢查询 end