CentOS 7系统修改mariadb的数据目录

ゝ一世哀愁。 2022-04-08 12:19 253阅读 0赞

### 1、起因 ###

安装MySQL/MariaDB数据库时，使用了默认的数据目录空间。  
今天创建数据表时失败，报错如下：

ERROR 3 (HY000): Error writing file './esdb/news.frm' (Errcode: 28)

原因是MySQL/MariaDB数据库的数据目录所在的分区空间已经用完。

[root@test ~]# df -h
    Filesystem                    Size  Used Avail Use% Mounted on
    /dev/mapper/VolGroup-lv_root   50G   47G     0 100% /
    devtmpfs                      3.9G     0  3.9G   0% /dev
    tmpfs                         3.9G   84K  3.9G   1% /dev/shm
    tmpfs                         3.9G  202M  3.7G   6% /run
    tmpfs                         3.9G     0  3.9G   0% /sys/fs/cgroup
    /dev/sda1                     477M  141M  307M  32% /boot
    /dev/mapper/datavg-lv_tpdata   98G   38G   56G  41% /data
    tmpfs                         783M   16K  783M   1% /run/user/42
    tmpfs                         783M     0  783M   0% /run/user/0
    [root@test ~]#

操作系统安装到了根分区，独立的数据分区是/data

### 2、解决办法 ###

将MySQL/MariaDB数据库的数据目录迁移到独立的数据分区。

（1）查看MySQL/MariaDB数据库的数据目录

MariaDB [(none)]> select @@datadir;
    +-----------------+
    | @@datadir       |
    +-----------------+
    | /var/lib/mysql/ |
    +-----------------+
    1 row in set (0.00 sec)
    
    MariaDB [(none)]>

所以数据目录是 /var/lib/mysql/

[root@test ~]# ll /var/lib/mysql/
    total 61492
    -rw-rw---- 1 mysql mysql    16384 Apr 19  2018 aria_log.00000001
    -rw-rw---- 1 mysql mysql       52 Apr 19  2018 aria_log_control
    drwx------ 2 mysql mysql     4096 Dec 13 18:04 esdb
    -rw-rw---- 1 mysql mysql 52428800 Dec 13 16:55 ibdata1
    -rw-rw---- 1 mysql mysql  5242880 Dec 13 16:55 ib_logfile0
    -rw-rw---- 1 mysql mysql  5242880 Dec 13 16:55 ib_logfile1
    drwx------ 2 mysql mysql     4096 May 23  2018 metastore
    drwx------ 2 mysql mysql     4096 Apr 19  2018 mysql
    srwxrwxrwx 1 mysql mysql        0 Apr 19  2018 mysql.sock
    drwx------ 2 mysql mysql     4096 Apr 19  2018 performance_schema
    drwx------ 2 mysql mysql     4096 May 14  2018 test
    [root@test ~]#

（2）创建新的数据目录

[root@test ~]# mkdir /data/mysql
    [root@test ~]# chown -R mysql:mysql /data/mysql/
    [root@test ~]# chmod 777 /data/mysql/

（3）停库

[root@test ~]# systemctl stop mariadb

（4）修改配置文件

[root@test ~]# vi /etc/my.cnf
    [root@test ~]# cat /etc/my.cnf
    [mysqld]
    character-set-server=utf8
    #datadir=/var/lib/mysql
    #socket=/var/lib/mysql/mysql.sock
    datadir=/tpdata/mysql
    socket=/tpdata/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
    pid-file=/var/run/mariadb/mariadb.pid
    
    #
    # include all files from the config directory
    #
    !includedir /etc/my.cnf.d
    
    [client]
    default-character-set=utf8
    socket=/tpdata/mysql.sock
    [mysql]
    default-character-set=utf8
    [root@test ~]#

（5）复制数据文件

[root@test ~]# cp -a /var/lib/mysql/* /tpdata/mysql/

（6）启动数据库

[root@test ~]# systemctl start mariadb
    [root@test ~]# systemctl status mariadb
    ● mariadb.service - MariaDB database server
       Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
       Active: active (running) since Thu 2018-12-13 18:32:59 CST; 2s ago
      Process: 27889 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
      Process: 27858 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
     Main PID: 27888 (mysqld_safe)
       CGroup: /system.slice/mariadb.service
               ├─27888 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
               └─28057 /usr/libexec/mysqld --basedir=/usr --datadir=/tpdata/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-
    
    Dec 13 18:32:57 test systemd[1]: Starting MariaDB database server...
    Dec 13 18:32:57 test mysqld_safe[27888]: 181213 18:32:57 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
    Dec 13 18:32:57 bigdata05-test mysqld_safe[27888]: 181213 18:32:57 mysqld_safe Starting mysqld daemon with databases from
    Dec 13 18:32:59 bigdata05-test systemd[1]: Started MariaDB database server.
    [root@test ~]#