CentOS 7 - MariaDB 10.4.18 DB를 mariabackup 을 이용 백업해서 복구 하는 테스트 입니다.
운영중인 서비스에서 하기가 부담스러워서 Virtual Box에 CentOS 7과 MariaDB 10.4.X를 설치 해서 검증을 하는 작업입니다.
mariabackup을 이용해서 상용 DB Full 백업은 완료 후 테스트용 가상 머신에 내려받아 놓은 상태이며, 운영중인 MariaDB와 비슷한 버전인 10.4.27 버전으로 설치 완료된 상태 입니다.
지난 버전의 MariaDB 설치 방법은 아래 링크에서 확인 할 수 있습니다.
https://opensrc.tistory.com/218
CentOS7 에 MariaDB 지난 버전(10.4.27) 설치
운영중인 시스템에 mariabackup 을 통해서 받은 백업에 대해 복구 테스트를 진행해야 하는 상황 운영중인 시스템은 CentOS 7, MariaDB 10.4.18 로 운영중이어서 최대한 비슷한 환경으로 맞춰서 테스트 진
opensrc.tistory.com
1. 운영환경과 동일한 설정 적용
스크립트등 운영에 적용을 위해 운영과 최대한 동일한 환경으로 맞춘 후 작업 하려고 합니다.
작업 후 추가
mariadb datadir이 최초 설치 했을때는 /var/lib/mysql 로 지정되어 있습니다.
운영환경에서는 데이터 크기 문제로 별도의 물리적 파티션에 지정하여 사용하였기 때문에 /data/mysql 로 지정되어 있습니다.
백업 및 복원 스크립트 작성을 염두에 두고 동일한 경로로 지정하기 위해 datadir을 /data/mysql로 변경하면서 몇가지 작업이 추가 되었습니다.
아래 작업 내용에서는 /data/mysql 디렉토리를 미리 생성하고 소유자 권한을 mysql:mysql로 수정하는 등 작업을 했지만 백업 데이터를 풀어 놓고 다시 작업해야 했습니다.
운영 환경
- MariaDB 데이터 디렉토리 : /data/mysql (복원 할 때 생성해야 함)
- MariaDB 로그 디렉토리 : /data/mysql-logs
[root@DEVSVR01 data]# systemctl status mariadb
● mariadb.service - MariaDB 10.4.27 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: inactive (dead)
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
[root@DEVSVR01 data]#
[root@DEVSVR01 data]#
[root@DEVSVR01 data]# systemctl start mariadb
[root@DEVSVR01 data]# systemctl status mariadb
● mariadb.service - MariaDB 10.4.27 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: active (running) since Fri 2022-12-02 11:08:48 KST; 7s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 62859 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 62815 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 62813 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 62826 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─62826 /usr/sbin/mysqld
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] InnoDB: 10.4.27 started; log sequence number 60943; transaction id 20
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] Plugin 'FEEDBACK' is disabled.
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] InnoDB: Buffer pool(s) load completed at 221202 11:08:48
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] Server socket created on IP: '::'.
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] Reading of all Master_info entries succeeded
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] Added new Master_info '' to hash table
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] /usr/sbin/mysqld: ready for connections.
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: Version: '10.4.27-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Dec 02 11:08:48 DEVSVR01 systemd[1]: Started MariaDB 10.4.27 database server.
[root@DEVSVR01 data]#maradb 를 처음 설치하고 아무런 조치를 하지 않으면 root 권한으로 비밀번호 입력 하지 않고 접속이 가능합니다.
백업 복원 후 가상머신을 제거 할 것 이기 때문에 별다른 조치를 하지 않고 바로 진행했습니다.
MariaDB 실행 및 접속
[root@DEVSVR01 data]# systemctl status mariadb
● mariadb.service - MariaDB 10.4.27 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: inactive (dead)
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
[root@DEVSVR01 data]#
[root@DEVSVR01 data]#
[root@DEVSVR01 data]# systemctl start mariadb
[root@DEVSVR01 data]# systemctl status mariadb
● mariadb.service - MariaDB 10.4.27 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: active (running) since Fri 2022-12-02 11:08:48 KST; 7s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 62859 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 62815 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 62813 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 62826 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─62826 /usr/sbin/mysqld
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] InnoDB: 10.4.27 started; log sequence number 60943; transaction id 20
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] Plugin 'FEEDBACK' is disabled.
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] InnoDB: Buffer pool(s) load completed at 221202 11:08:48
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] Server socket created on IP: '::'.
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] Reading of all Master_info entries succeeded
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] Added new Master_info '' to hash table
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: 2022-12-02 11:08:48 0 [Note] /usr/sbin/mysqld: ready for connections.
Dec 02 11:08:48 DEVSVR01 mysqld[62826]: Version: '10.4.27-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Dec 02 11:08:48 DEVSVR01 systemd[1]: Started MariaDB 10.4.27 database server.
[root@DEVSVR01 data]# mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.27-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)]>
MariaDB [(none)]> show global variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| fb_ebmp |
| information_schema |
| lcs |
| mysql |
| oneshot_sms |
| performance_schema |
| server_audit |
+--------------------+
7 rows in set (0.031 sec)
MariaDB [(none)]> quit
Bye
[root@DEVSVR01 data]#기본 설정된 datadir은 /var/lib/mysql 입니다.
2. mariabackup 설치
데이터 복원을 위해 새로 설치한 CentOS 가상 머신에 mariabackup 을 설치 해야 합니다.
[root@DEVSVR01 ~]# yum install MariaDB-backup
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.kakao.com
* epel: mirror-jp.misakamikoto.network
* extras: mirror.kakao.com
* updates: mirror.navercorp.com
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-backup.x86_64 0:10.4.27-1.el7.centos will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=================================================================================================================================================================================
Package Arch Version Repository Size
=================================================================================================================================================================================
Installing:
MariaDB-backup x86_64 10.4.27-1.el7.centos mariadb 6.6 M
Transaction Summary
=================================================================================================================================================================================
Install 1 Package
Total download size: 6.6 M
Installed size: 27 M
Is this ok [y/d/N]: y
Downloading packages:
MariaDB-backup-10.4.27-1.el7.centos.x86_64.rpm | 6.6 MB 00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : MariaDB-backup-10.4.27-1.el7.centos.x86_64 1/1
Verifying : MariaDB-backup-10.4.27-1.el7.centos.x86_64 1/1
Installed:
MariaDB-backup.x86_64 0:10.4.27-1.el7.centos
Complete!이전 게시물에서 mariadb.repo에 버전을 지정했기 때문에 해당 버전에 맞는 mariabackup 이 설치 되었습니다.
3. 백업 데이터 복원
미리 운영 DB 에서 백업 받은 풀 데이터를 다운받아 두었습니다.
[root@DEVSVR01 ~]# cd /usr/download
[root@DEVSVR01 download]# ls -al
total 11994704
drwxr-xr-x. 3 root root 79 Dec 2 09:02 .
drwxr-xr-x. 14 root root 171 Dec 2 09:02 ..
drwx------. 8 root root 4096 Nov 27 04:43 PROD_full_20221127040001
-rw-r--r--. 1 root root 12282569817 Dec 1 20:12 PROD_full_20221127040001.tar.gz
[root@DEVSVR01 download]# cd PROD_full_20221127040001
[root@DEVSVR01 PROD_full_20221127040001]# ls -al
total 418276
drwx------. 8 root root 4096 Nov 27 04:43 .
drwxr-xr-x. 3 root root 79 Dec 2 09:02 ..
-rw-r-----. 1 root root 442368 Nov 27 04:43 aria_log.00000001
-rw-r-----. 1 root root 52 Nov 27 04:43 aria_log_control
-rw-r-----. 1 root root 391 Nov 27 04:43 backup-my.cnf
drwx------. 2 root root 12288 Nov 27 04:43 XXXXX
-rw-r-----. 1 root root 213909504 Nov 27 04:43 ibdata1
-rw-r-----. 1 root root 213909504 Nov 27 04:00 ibdata2
-rw-r-----. 1 root root 0 Nov 27 04:43 ib_logfile0
drwx------. 2 root root 4096 Nov 27 04:43 lcs
drwx------. 2 root root 4096 Nov 27 04:43 mysql
drwx------. 2 root root 4096 Nov 27 04:43 xxxxxxxxx
drwx------. 2 root root 20 Nov 27 04:43 performance_schema
drwx------. 2 root root 256 Nov 27 04:43 xxxxxxxxx
-rw-r-----. 1 root root 40 Nov 27 04:43 xtrabackup_binlog_info
-rw-r-----. 1 root root 85 Nov 27 04:43 xtrabackup_checkpoints
-rw-r-----. 1 root root 634 Nov 27 04:43 xtrabackup_info
[root@DEVSVR01 PROD_full_20221127040001]#
미리 만들어 놓은 datadir 때문에 복원을 하면서 에러가 발생합니다. /data/mysql를 삭제 한 후 다시 진행 했습니다.
[root@DEVSVR01 download]# which mariabackup
/usr/bin/mariabackup
[root@DEVSVR01 download]# mariabackup --copy-back --target-dir=/usr/download/PROD_full_20221127040001 --datadir=/var/lib/mysql --user root
mariabackup based on MariaDB server 10.4.27-MariaDB Linux (x86_64)
[00] 2022-12-02 12:13:55 Original data directory /var/lib/mysql is not empty!
[root@DEVSVR01 download]# mariabackup --copy-back --target-dir=/usr/download/PROD_full_20221127040001 --datadir=/data/mysql --user root
mariabackup based on MariaDB server 10.4.27-MariaDB Linux (x86_64)
[00] 2022-12-02 12:14:24 Original data directory /data/mysql is not empty!
[root@DEVSVR01 download]# rm -rf /data/mysql
[root@DEVSVR01 download]# mariabackup --copy-back --target-dir=/usr/download/PROD_full_20221127040001 --datadir=/data/mysql --user root
mariabackup based on MariaDB server 10.4.27-MariaDB Linux (x86_64)
[01] 2022-12-02 12:14:34 Copying ibdata1 to /data/mysql/ibdata1
... 생략...
[01] 2022-12-02 13:24:10 Copying ./aria_log_control to /data/mysql/aria_log_control
[01] 2022-12-02 13:24:10 ...done
[01] 2022-12-02 13:24:10 Copying ./aria_log.00000001 to /data/mysql/aria_log.00000001
[01] 2022-12-02 13:24:10 ...done
[01] 2022-12-02 13:24:10 Copying ./xtrabackup_info to /data/mysql/xtrabackup_info
[01] 2022-12-02 13:24:11 ...done
[00] 2022-12-02 13:24:11 completed OK!
[root@DEVSVR01 download]#
복원된 데이터를 확인하고 root 계정으로 생성된 데이터를 mysql:mysql 로 오너쉽을 변경합니다.
(서비스와 연관된 디렉토리 명은 xxxx로 마스킹 했습니다.)
[root@DEVSVR01 download]# ls -al /data
total 0
drwxr-xr-x. 4 root root 37 Dec 2 12:14 .
dr-xr-xr-x. 18 root root 236 Nov 30 18:46 ..
drwx------. 8 root root 212 Dec 2 13:24 mysql
drwxr-xr-x. 3 mysql mysql 18 Dec 2 11:57 mysql-logs
[root@DEVSVR01 download]# ls -al /data/mysql
total 418260
drwx------. 8 root root 212 Dec 2 13:24 .
drwxr-xr-x. 4 root root 37 Dec 2 12:14 ..
-rw-r-----. 1 root root 442368 Dec 2 13:24 aria_log.00000001
-rw-r-----. 1 root root 52 Dec 2 13:24 aria_log_control
drwx------. 2 root root 12288 Dec 2 12:59 xxxxx
-rw-r-----. 1 root root 213909504 Dec 2 12:14 ibdata1
-rw-r-----. 1 root root 213909504 Dec 2 12:14 ibdata2
drwx------. 2 root root 4096 Dec 2 12:59 xxxx
drwx------. 2 root root 4096 Dec 2 12:57 mysql
drwx------. 2 root root 4096 Dec 2 12:59 xxxxxx
drwx------. 2 root root 20 Dec 2 13:24 performance_schema
drwx------. 2 root root 256 Dec 2 13:24 xxxxxx
-rw-r-----. 1 root root 634 Dec 2 13:24 xtrabackup_info
[root@DEVSVR01 download]# chown -R mysql:mysql /data/mysql
[root@DEVSVR01 download]# ls -al /data/mysql
total 418260
drwx------. 8 mysql mysql 212 Dec 2 13:24 .
drwxr-xr-x. 4 root root 37 Dec 2 12:14 ..
-rw-r-----. 1 mysql mysql 442368 Dec 2 13:24 aria_log.00000001
-rw-r-----. 1 mysql mysql 52 Dec 2 13:24 aria_log_control
drwx------. 2 mysql mysql 12288 Dec 2 12:59 xxxx
-rw-r-----. 1 mysql mysql 213909504 Dec 2 12:14 ibdata1
-rw-r-----. 1 mysql mysql 213909504 Dec 2 12:14 ibdata2
drwx------. 2 mysql mysql 4096 Dec 2 12:59 xxxxx
drwx------. 2 mysql mysql 4096 Dec 2 12:57 mysql
drwx------. 2 mysql mysql 4096 Dec 2 12:59 xxxxxx
drwx------. 2 mysql mysql 20 Dec 2 13:24 performance_schema
drwx------. 2 mysql mysql 256 Dec 2 13:24 xxxxxx
-rw-r-----. 1 mysql mysql 634 Dec 2 13:24 xtrabackup_info
[root@DEVSVR01 download]#
/etc/my.cnf 를 수정해서 운영환경과 동일하게 맞추겠습니다.
[root@DEVSVR01 lib]# vi /etc/my.cnf
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[mysqld]
# DB의 데이터가 생성될 기본 경로
datadir=/data/mysql
# 소켓파일 경로(로컬서버 접속에 사용), 리모트 서버에는 ip와 port를 이용해서 TCP/IP 프로토콜로 접근
socket=/data/mysql/mysql.sock
# 타입존 설정
default-time-zone='+9:00'
## General LOG
general_log=1
log_output='FILE'
general_log_file=/data/mysql-logs/logs/mysql_history.log
## Slow LOG
slow_query_log=1
slow_query_log_file=/data/mysql-logs/logs/mysql_slow.log
long_query_time=10
## Error LOG
log-error=/data/mysql-logs/logs/mysql_error.log
## Binary LOG
log-bin=/data/mysql-logs/logs/mysql_binary
binlog_cache_size=2M
max_binlog_size=50M
expire_logs_days=2
[client-server]
#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d
~
~
~
~
~[mysqld] 부분을 추가 하고 몇가지 설정을 추가 했습니다.
중요한 것은 위에서 복사한 경로를 지정 하는 datadir = /data/mysql 입니다. (저장 공간등의 이슈가 없으면 설치시 지정된 디렉토리 그대로 진행해도 관계 없습니다.)
3. 복원된 데이터로 mariadb 실행
[root@DEVSVR01 download]# systemctl start mariadb
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
[root@DEVSVR01 download]#
[root@DEVSVR01 download]# journalctl -xe
... 생략 ...
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [Note] /usr/sbin/mysqld (mysqld 10.4.27-MariaDB-log) starting as process 2831 ...
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [Warning] Can't create test file /data/mysql/DEVSVR01.lower-test
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: /usr/sbin/mysqld: Can't create file '/data/mysql-logs/logs/mysql_error.log' (errno: 13 "Permission denied")
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] mysqld: File '/data/mysql/aria_log_control' not found (Errcode: 13 "Permission denied")
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] mysqld: Got error 'Can't open file' when trying to use aria control file '/data/mysql/aria_log_control'
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] Plugin 'Aria' init function returned error.
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [Note] InnoDB: Using Linux native AIO
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] Plugin 'InnoDB' init function returned error.
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [Note] Plugin 'FEEDBACK' is disabled.
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] Failed to initialize plugins.
Dec 02 15:11:47 DEVSVR01 mysqld[2831]: 2022-12-02 15:11:47 0 [ERROR] Aborting
Dec 02 15:11:47 DEVSVR01 systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Dec 02 15:11:47 DEVSVR01 systemd[1]: Failed to start MariaDB 10.4.27 database server.
-- Subject: Unit mariadb.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mariadb.service has failed.
--
-- The result is failed.
Dec 02 15:11:47 DEVSVR01 systemd[1]: Unit mariadb.service entered failed state.
Dec 02 15:11:47 DEVSVR01 systemd[1]: mariadb.service failed.
Dec 02 15:11:47 DEVSVR01 polkitd[582]: Unregistered Authentication Agent for unix-process:2782:16916940 (system bus name :1.134, object path /org/freedesktop/PolicyKit1/Authenti
[root@DEVSVR01 download]#퍼미션 문제로 정상 실행이 안됩니다.
journalctl -xe 명령으로 원인을 찾아 보면
/usr/sbin/mysqld: Can't create file '/data/mysql-logs/logs/mysql_error.log' (errno: 13 "Permission denied") Dec 02 [ERROR] mysqld: File '/data/mysql/aria_log_control' not found (Errcode: 13 "Permission denied") Dec 02 15:11:47 [ERROR] mysqld: Got error 'Can't open file' when trying to use aria control file '/data/mysql/aria_log_control'
mysql_error.log,
aria_log_control 등의 파일에 퍼미션이 없어서 실행이 안된다고 나옵니다.
[root@DEVSVR01 download]# ls -al /data/
total 0
drwxr-xr-x. 4 root root 37 Dec 2 12:14 .
dr-xr-xr-x. 18 root root 236 Nov 30 18:46 ..
drwx------. 8 mysql mysql 212 Dec 2 13:24 mysql
drwxr-xr-x. 3 mysql mysql 18 Dec 2 11:57 mysql-logs
[root@DEVSVR01 download]# chmod 755 /data/mysql
[root@DEVSVR01 download]# ls -al /data/
total 0
drwxr-xr-x. 4 root root 37 Dec 2 12:14 .
dr-xr-xr-x. 18 root root 236 Nov 30 18:46 ..
drwxr-xr-x. 8 mysql mysql 212 Dec 2 13:24 mysql
drwxr-xr-x. 3 mysql mysql 18 Dec 2 11:57 mysql-logs
[root@DEVSVR01 download]# ls -al /data/mysql
total 418260
drwxr-xr-x. 8 mysql mysql 212 Dec 2 13:24 .
drwxr-xr-x. 4 root root 37 Dec 2 12:14 ..
-rw-r-----. 1 mysql mysql 442368 Dec 2 13:24 aria_log.00000001
-rw-r-----. 1 mysql mysql 52 Dec 2 13:24 aria_log_control
drwx------. 2 mysql mysql 12288 Dec 2 12:59 xxxxxxx
-rw-r-----. 1 mysql mysql 213909504 Dec 2 12:14 ibdata1
-rw-r-----. 1 mysql mysql 213909504 Dec 2 12:14 ibdata2
drwx------. 2 mysql mysql 4096 Dec 2 12:59 xxxxxxx
drwx------. 2 mysql mysql 4096 Dec 2 12:57 mysql
drwx------. 2 mysql mysql 4096 Dec 2 12:59 xxxxxxx
drwx------. 2 mysql mysql 20 Dec 2 13:24 performance_schema
drwx------. 2 mysql mysql 256 Dec 2 13:24 xxxxxxx
-rw-r-----. 1 mysql mysql 634 Dec 2 13:24 xtrabackup_info
[root@DEVSVR01 download]#권한 및 퍼미션을 설정해 줘도 안됩니다.
구글링 구글링
4. SELinux 로 보호되는 mysql 컨텍스트 해제
원인은 SELinux로 mysql 관련 디렉토리들이 보호되고 있어서 생긴 문제였습니다.
자세한 내용은 MariaDB 공식 문서를 확인 해주세요
https://mariadb.com/kb/en/selinux/
SELinux
SELinux is a Linux kernel module that provides a framework for configuring mandatory access control (MAC). Learn how we can help configure your framework.
mariadb.com
문서의 내용을 보면 semanage 라는 파이썬 패키지를 통해 관리 가능하다고 나옵니다.
[root@DEVSVR01 download]# semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
-bash: semanage: command not found
[root@DEVSVR01 download]# semanage
-bash: semanage: command not found
[root@DEVSVR01 download]#아래 링크에서 semanage 패키지를 설치 하는 방법이 나옵니다.
https://ko.linux-console.net/?p=528#gsc.tab=0
[root@DEVSVR01 download]# yum provides /usr/sbin/semanage
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.kakao.com
* epel: mirror-jp.misakamikoto.network
* extras: mirror.kakao.com
* updates: mirror.navercorp.com
policycoreutils-python-2.5-34.el7.x86_64 : SELinux policy core python utilities
Repo : base
Matched from:
Filename : /usr/sbin/semanage
[root@DEVSVR01 download]# yum install policycoreutils-python
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.kakao.com
* epel: mirror-jp.misakamikoto.network
* extras: mirror.kakao.com
* updates: mirror.navercorp.com
Resolving Dependencies
... 생략 ...
Installed:
policycoreutils-python.x86_64 0:2.5-34.el7
Dependency Installed:
audit-libs-python.x86_64 0:2.8.5-4.el7 checkpolicy.x86_64 0:2.5-8.el7 libcgroup.x86_64 0:0.41-21.el7 libsemanage-python.x86_64 0:2.5-14.el7 python-IPy.noarch 0:0.75-6.el7
setools-libs.x86_64 0:3.3.8-4.el7
Complete!
[root@DEVSVR01 download]#mysql에서 허용된 정책을 살펴 보고 동일한 정책으로 /data/mysql, /data/mysql-logs 디렉토리를 추가 합니다.
(중간에 디렉토리 경로를 안써주고 추가한 fcontext 는 -d 옵션으로 삭제 했습니다. 참고용으로 기록 남깁니다.)
[root@DEVSVR01 download]# semanage fcontext --list | grep mysqld_db_t
/var/lib/mysql(-files|-keyring)?(/.*)? all files system_u:object_r:mysqld_db_t:s0
[root@DEVSVR01 download]# semanage fcontext -d "(-files|-keyring)?(/.*)?"
[root@DEVSVR01 download]# semanage fcontext --list | grep mysqld_db_t
/var/lib/mysql(-files|-keyring)?(/.*)? all files system_u:object_r:mysqld_db_t:s0
(-files|-keyring)?(/.*)? all files system_u:object_r:mysqld_db_t:s0
[root@DEVSVR01 download]# semanage fcontext -d "/data/mysql(-files|-keyring)?(/.*)?"
[root@DEVSVR01 download]# semanage fcontext -d "/data/mysql-logs(-files|-keyring)?(/.*)?"
[root@DEVSVR01 download]# semanage fcontext --list | grep mysqld_db_t
/var/lib/mysql(-files|-keyring)?(/.*)? all files system_u:object_r:mysqld_db_t:s0
/data/mysql(-files|-keyring)?(/.*)? all files system_u:object_r:mysqld_db_t:s0
/data/mysql-logs(-files|-keyring)?(/.*)? all files system_u:object_r:mysqld_db_t:s0
[root@DEVSVR01 download]#
mysqld 는 정상 실행 되지만 접속하려고 하면 mysql.sock을 /var/lib/mysql/mysql.sock 에서 찾고 있습니다.
/etc/my.cnf를 수정 하면서
[mysqld] 섹션에
socket=/data/mysql/mysql.sock 라고 수정 했습니다.
이것은 mariadb 서버가 실행 될 때 참고 하는 섹션입니다.
cli에서 mysql 을 접속 하는 것은 mysql client 로 서버에 접속하는 행위 이므로 mysql.sock 파일의 위치를 찾지 못하는 것 입니다.
[root@DEVSVR01 download]# systemctl start mariadb
[root@DEVSVR01 download]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@DEVSVR01 download]# vi /etc/my.cnf.d/
enable_encryption.preset mysql-clients.cnf server.cnf
[root@DEVSVR01 download]# vi /etc/my.cnf.d/mysql-clients.cnf
#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#
[mysql]
socket=/data/mysql/mysql.sock
[mysql_upgrade]
[mysqladmin]
[mysqlbinlog]
[mysqlcheck]
[mysqldump]
[mysqlimport]
[mysqlshow]
[mysqlslap]
~
~
~
~
~
~
~
:wq/etc/my.cnf.d/mysql-clients.conf 에 [mysql] 섹션에 해당 소켓의 위치를 지정해 줍니다.
관리가 불편하면 /etc/my.cnf 파일 하단에 [mysql] 테그를 추가하고 해당 위치에 추가 해도 됩니다.
이외에 innodb 관련 설정들 몇가지가 달라서 mariadb 서버 실행에 에러가 발생했었습니다.
See "systemctl status mariadb.service" and "journalctl -xe" for details. 메시지 대로
journalctl -xe 명령으로 어디서 에러가 발생했는지 살펴 보시면서 하나씩 해결 하면 됩니다.
6. mariadb 실행 및 데이터 복원 확인
[root@DEVSVR01 download]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.4.27-MariaDB-log 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)]> show databases;
+--------------------+
| Database |
+--------------------+
| fb_ebmp |
| information_schema |
| lcs |
| mysql |
| oneshot_sms |
| performance_schema |
| server_audit |
+--------------------+
7 rows in set (0.031 sec)
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 user, host, password from user;
+---------------+--------------+-------------------------------------------+
| User | Host | Password |
+---------------+--------------+-------------------------------------------+
| mariadb.sys | localhost | |
| root | localhost | *보안삭제 |
| mysql | localhost | invalid |
... 생략 ...
+---------------+--------------+-------------------------------------------+
26 rows in set (0.001 sec)
MariaDB [mysql]>
MariaDB [(none)]> quit
Bye
[root@DEVSVR01 download]#정상 복원이 완료 되었습니다.
7. 외부에서 접근하기 위해서는 OS 방화벽 설정이 필요합니다.
[root@DEVSVR01 download]# vi /etc/firewalld/zones/public.xml
<?xml version="1.0" encoding="utf-8"?>
<zone>
<short>Public</short>
<description>For use in public areas. You do not trust the other computers on networks to not harm your computer. Only selected incoming connections are accepted.</description>
<service name="ssh"/>
<service name="dhcpv6-client"/>
<!-- mariadb start -->
<rule family="ipv4">
<port protocol="tcp" port="3306"/>
<accept/>
</rule>
<!-- mariadb end -->
</zone>
~
~
~
~
~
~
~
~
~
[root@DEVSVR01 download]# firewall-cmd --reload
success
[root@DEVSVR01 download]#
끝.
'DataBase' 카테고리의 다른 글
| [MariaDB] 그룹(ROLE) 기반 권한 부여 (1) | 2024.08.09 |
|---|---|
| [MariaDB] 가상 컬럼을 이용한 인덱스 생성 (0) | 2024.07.19 |
| CentOS7 에 MariaDB 지난 버전(10.4.27) 설치 (0) | 2022.12.02 |
| MySQL root 계정의 패스워드 분실 했을 경우 바꾸는 방법 (0) | 2020.11.25 |
| [MySQL] MySQL 5.7 설치 후 초기 root 계정 비밀번호 변경 하기 (4) | 2019.05.09 |