MariaDB 데이터 저장소 변경

현재 mysql 데이터베이스 저장 경로 확인하기

• root 계정으로 로그인
• select @@datadir 기본 데이터 저장소 확인

[root@localhost mariadb]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \\g.
Your MariaDB connection id is 3
Server version: 10.11.10-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)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

MariaDB [mysql]>

Mariadb 서비스 중단

systemctl stop mariadb

변경할 신규 저장 디렉토리 생성

# 신규 경로의 디렉토리 생성
[root@localhost data]# mkdir -p /data1/mysql/data/
[root@localhost data]# mkdir -p /data1/mysql/log/
[root@localhost data]# mkdir -p /data1/mysql/tmp/

# mysql 복사
[root@localhost data]# cp -r /var/lib/mysql/* /data1/mysql/data/

[root@localhost data]# pwd
/data1/mysql/data
[root@localhost data]# ls -lrt
total 123324
-rw-r-----. 1 root root       936 Dec 27 01:38 ib_buffer_pool
-rw-r-----. 1 root root         9 Dec 27 01:38 ddl_recovery.log
-rw-r-----. 1 root root        52 Dec 27 01:38 aria_log_control
-rw-r-----. 1 root root    417792 Dec 27 01:38 aria_log.00000001
-rw-r-----. 1 root root  12582912 Dec 27 01:38 ibdata1
-rw-r-----. 1 root root 100663296 Dec 27 01:38 ib_logfile0
-rw-r-----. 1 root root         0 Dec 27 01:38 multi-master.info
-rw-r-----. 1 root root         6 Dec 27 01:38 localhost.pid
-rw-r-----. 1 root root  12582912 Dec 27 01:38 ibtmp1
drwx------. 2 root root        20 Dec 27 01:38 performance_schema
-rw-r--r--. 1 root root        16 Dec 27 01:38 mysql_upgrade_info
srwxr-xr-x. 1 root root         0 Dec 27 01:38 mysql.sock
drwx------. 2 root root      4096 Dec 27 01:38 mysql
drwx------. 2 root root        20 Dec 27 01:38 test
drwx------. 2 root root      8192 Dec 27 01:38 sys

# mysql 권한 부여
chown -R mysql:mysql /data1

Mariadb 설정 파일 변경

[client]
port		= 23306
socket          = /data1/mysql/data/mysql.sock

[mysqld]
port		= 23306
socket          =/data1/mysql/data/mysql.sock
datadir		=/data1/mysql/data
tmpdir=/data1/mysql/tmp

#log
slow_query_log = 1
slow_query_log_file = /data1/mysql/log/mysql-slow-query.log
long_query_time=3
log-error = /data1/mysql/log/mysql.err

innodb_data_home_dir = /data1/mysql/data

innodb_log_group_home_dir=/data1/mysql/log

Mariadb 재기동

systemctl start mariadb

MariaDB에 접속해서 select @@datadir 기본 데이터 저장소 확인

MariaDB [mysql]> select @@datadri;
ERROR 1193 (HY000): Unknown system variable 'datadri'
MariaDB [mysql]> select @@datadir;
+--------------------+
| @@datadir          |
+--------------------+
| /data1/mysql/data/ |

 

[ERROR] InnoDB: File /data1/mysql/log/ib_logfile0 was not found

정상적으로 실행이 안 될 경우.

[root@localhost data]# systemctl status mariadb
● mariadb.service - MariaDB 10.11.10 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: failed (Result: exit-code) since Fri 2024-12-27 01:48:24 EST; 1min 23s ago
     Docs: man:mariadbd(8)
           <https://mariadb.com/kb/en/library/systemd/>
  Process: 34779 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 35432 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 35266 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 35264 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 35432 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

Dec 27 01:48:24 localhost.localdomain systemd[1]: Starting MariaDB 10.11.10 database server...
Dec 27 01:48:24 localhost.localdomain systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Dec 27 01:48:24 localhost.localdomain systemd[1]: mariadb.service: Failed with result 'exit-code'.
Dec 27 01:48:24 localhost.localdomain systemd[1]: Failed to start MariaDB 10.11.10 database server.

mysql.err 로그 파일을 확인 한다. 아래와 같은 에러 발생 시 datadir경로의 ib_logfile0 파일을 삭제한 뒤 Mraiadb 서비스를 재 기동한다

2024-12-27  1:47:36 0 [ERROR] InnoDB: File /data1/mysql/log/ib_logfile0 was not found
2024-12-27  1:47:36 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

 

[ERROR] InnoDB: redo log file ‘./ib_logfile1’ exists. Creating system tablespace with existing redo log files is not recommended. Please delete all redo log files before creating new system tablespace.

https://bobcares.com/blog/mariadb-redo-log-file-exists/

https://stackoverflow.com/questions/12627604/mysql-start-up-issues-error-the-server-quit-without-updating-pid-file