centos7 mariadb5.5.60 修改数据库文件路径

yum安装mariadb的时候会有默认路径，我们希望修改数据库datadir和日志log-error路径，例如生产系统，需要将这些数据放到比较大的磁盘下。

1、安装mariadb（client和server）

[root@node2 ~]# yum install mariadb mariadb-server -y

注意：先卸载mariadb-lib，否者yum安装mariadb、 mariadb-server后，可能没有/etc/my.cnf文件

卸载完检查/var/lib/mysql中的文件，有就删除：rm -rf /var/lib/mysql，防止重装mariadb，保留原来的密码

2、启动mariadb，设置root初始密码

[root@node2 ~]# systemctl start mariadb
[root@node2 ~]# mysql_secure_installation

3、停止mariadb，修改配置文件/etc/my.cnf

[root@node2 ~]# systemctl stop mariadb

修改datadir=/var/lib/mysql和log-error=/var/log/mariadb/mariadb.log两行

vim /etc/my.cnf

[mysqld]
#datadir=/var/lib/mysql
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
log-error=/data/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

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

注意：不要修改socket和pid-file路径，否者容易出错

4、将/var/lib/mysql下面的文件复制到/data/mysql，/var/log/mariadb/复制到/data/log/mariadb

[root@node2 ~]# mkdir /data
[root@node2 ~]# cp -a /var/lib/mysql/ /data/
[root@node2 ~]# mkdir /data/log/
[root@node2 ~]# cp -a /var/log/mariadb/ /data/log/
[root@node2 ~]# chown -R mysql:mysql /data/

注意：如果开启了SELinux和防火墙，需要关闭SELinux和firewall或者设置/data/、/data/log/的security context，开启3306端口

5、修改security context（如果开启了selinux，需要，否则跳过）

查看/var/lib/mysql 的security context

[root@node2 ~]# ls -Z /var/lib/mysql/
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 aria_log.00000001
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 aria_log_control
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 ibdata1
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_logfile0
-rw-rw----. mysql mysql system_u:object_r:mysqld_db_t:s0 ib_logfile1
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 mysql
drwx------. mysql mysql system_u:object_r:mysqld_db_t:s0 performance_schema

修改、恢复/data/mysql，/data/log的security context

[root@node2 ~]# semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
[root@node2 ~]# restorecon -R -v /data/mysql
[root@node2 ~]# semanage fcontext -a -t mysqld_db_t "/data/log(/.*)?"
[root@node2 ~]# restorecon -R -v /data/log

security contest设置参考

6、启动mariadb、新建test库测试

[root@node2 ~]# systemctl start mariadb

先查看/data/mysql目录

[root@node2 ~]# ll /data/mysql/
total 28700
-rw-rw----. 1 mysql mysql    16384 Dec 28 10:43 aria_log.00000001
-rw-rw----. 1 mysql mysql       52 Dec 28 10:43 aria_log_control
-rw-rw----. 1 mysql mysql 18874368 Dec 28 10:43 ibdata1
-rw-rw----. 1 mysql mysql  5242880 Dec 28 11:19 ib_logfile0
-rw-rw----. 1 mysql mysql  5242880 Dec 28 10:38 ib_logfile1
drwx------. 2 mysql mysql     4096 Dec 28 10:38 mysql
drwx------. 2 mysql mysql     4096 Dec 28 10:38 performance_schema

新建test库后再查看，新增了test文件夹

[root@node2 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-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)]> create database test;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> quit
Bye

[root@node2 ~]# ll /data/mysql/
total 28700
-rw-rw----. 1 mysql mysql    16384 Dec 28 10:43 aria_log.00000001
-rw-rw----. 1 mysql mysql       52 Dec 28 10:43 aria_log_control
-rw-rw----. 1 mysql mysql 18874368 Dec 28 10:43 ibdata1
-rw-rw----. 1 mysql mysql  5242880 Dec 28 11:19 ib_logfile0
-rw-rw----. 1 mysql mysql  5242880 Dec 28 10:38 ib_logfile1
drwx------. 2 mysql mysql     4096 Dec 28 10:38 mysql
drwx------. 2 mysql mysql     4096 Dec 28 10:38 performance_schema
drwx------. 2 mysql mysql       20 Dec 28 11:27 test

查看/data/log/mariadb/mariadb.log，日志时间一致，证明修改生效

[root@node2 ~]# tail -f /data/log/mariadb/mariadb.log
181228 11:19:29 InnoDB: Initializing buffer pool, size = 128.0M
181228 11:19:29 InnoDB: Completed initialization of buffer pool
181228 11:19:29 InnoDB: highest supported file format is Barracuda.
181228 11:19:29  InnoDB: Waiting for the background threads to start
181228 11:19:30 Percona XtraDB (http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 1597945
181228 11:19:30 [Note] Plugin 'FEEDBACK' is disabled.
181228 11:19:30 [Note] Server socket created on IP: '0.0.0.0'.
181228 11:19:30 [Note] Event Scheduler: Loaded 0 events
181228 11:19:30 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
^C