MySQL 데이터 디렉토리 설정 변경

OS : Debian 10 64Bit

MySQL : 8.0.23 Community Server 64Bit

 

mysql 서버의 데이터가 커지면서 새로운 디스크를 추가 하여 /opt/home/ 에 마운트를 하였고, mysql 의 데이터 디렉토리를 이동 시키기로 하였다.

 

root@freecatz-pe-kr:/# service mysql stop

root@freecatz-pe-kr:/# cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.ori

root@freecatz-pe-kr:/# vi /etc/mysql/mysql.conf.d/mysqld.cnf

#datadir=/var/lib/mysql datadir=/opt/home/mysql-data

root@freecatz-pe-kr:/# mkdir -p /opt/home/mysql-data

root@freecatz-pe-kr:/# cp -rp /var/lib/mysql/ /opt/home/mysql-data/

root@freecatz-pe-kr:/# chown -R mysql:mysql /opt/home/mysql-data/

root@freecatz-pe-kr:/# service mysql start

Job for mysql.service failed because the control process exited with error code.

See "systemctl status mysql.service" and "journalctl -xe" for details.

 

root@freecatz-pe-kr:/# tail -f /var/log/mysql/error.log mysqld: File './binlog.index' not found (OS errno 13 - Permission denied) 2021-01-29T00:02:16.735790Z 0 [Warning] [MY-010091] [Server] Can't create test file /opt/home/mysql-data/mysqld_tmp_file_case_insensitive_test.lower-test 2021-01-29T00:02:16.735876Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 13864 2021-01-29T00:02:16.738999Z 0 [Warning] [MY-010091] [Server] Can't create test file /opt/home/mysql-data/mysqld_tmp_file_case_insensitive_test.lower-test 2021-01-29T00:02:16.739012Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /opt/home/mysql-data/ is case insensitive 2021-01-29T00:02:16.739722Z 0 [ERROR] [MY-010119] [Server] Aborting 2021-01-29T00:02:16.739861Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23) MySQL Community Server - GPL.

 

root@freecatz-pe-kr:/# cp /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/usr.sbin.mysqld.ori

root@freecatz-pe-kr:/# vi /etc/apparmor.d/usr.sbin.mysqld

# Allow data dir access #  /var/lib/mysql/ r, #  /var/lib/mysql/** rwk,   /opt/home/mysql-data/ r,   /opt/home/mysql-data/** rwk,

 

 

root@freecatz-pe-kr:/# service mysql start

root@freecatz-pe-kr:/# service mysql status

● mysql.service - MySQL Community Server

        Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

          Active: active (running) since Fri 2021-01-29 09:15:13 KST; 10s ago

            Docs: man:mysqld(8)

                      http://dev.mysql.com/doc/refman/en/using-systemd.html

        Process: 14741 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

      Main PID: 14789 (mysqld)

           Status: "Server is operational"

            Tasks: 38 (limit: 4915)

         CGroup: /system.slice/mysql.service

                        └─14789 /usr/sbin/mysqld

 

1월 29 09:15:13 freecatz-pe-kr systemd[1]: Starting MySQL Community Server...

1월 29 09:15:13 freecatz-pe-kr systemd[1]: Started MySQL Community Server.

 

 

 

root@freecatz-pe-kr:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@datadir;
+-----------------------+
| @@datadir             |
+-----------------------+
| /opt/home/mysql-data/ |
+-----------------------+
1 row in set (0.00 sec)

mysql> quit;