결국 발송 일자를 yyyyMMdd 형태로 컬럼을 추가 하고 푸시종류 + 발송일자 + 고객 순번으로 Unique Index를 생성하면 되지만 왠지 SCHEDULE_DT와 중복되는 느낌입니다.
가상 컬럼을 이용한 인덱스를 사용해 보겠습니다.
1. 테이블 생성
DROP TABLE IF EXISTS OCCASIONAL_PUSH_STACK;
CREATE TABLE OCCASIONAL_PUSH_STACK (
STACK_SEQ INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '대기 순번',
PUSH_TYPE VARCHAR(20) NOT NULL COMMENT '푸시 종류',
CUST_SEQ INT(11) UNSIGNED NOT NULL COMMENT '고객 순번',
SCHEDULE_DT VARCHAR(14) NOT NULL COMMENT '발송 희망 일시',
REG_DATE DATETIME NOT NULL COMMENT '등록 일시',
PRIMARY KEY (STACK_SEQ)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT='비정기 푸시 발송 대기 테이블';
2. 인덱스 추가
ALTER TABLE OCCASIONAL_PUSH_STACK
ADD STD_DATE VARCHAR(8) AS (LEFT(SCHEDULE_DT, 8)) VIRTUAL AFTER CUST_SEQ,
ADD UNIQUE INDEX (PUSH_TYPE, STD_DATE, CUST_SEQ);
CUST_SEQ 컬럼 다음에 SCHEDULE_DT 테이블의 8자리를 잘라서 가상 컬럼을 만들고, 해당 컬럼을 포함한 유니크 인덱스를 생성한다는 의미 입니다.
VIRTUAL 은 매번 사용 될 때 마다 문자열 연산을 하게 되므로 STORED 로 사용하는게 성능에는 더 유리할 것 같습니다.
3. 테이블 확인
CREATE TABLE `occasional_push_stack` (
`STACK_SEQ` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '대기 순번',
`PUSH_TYPE` varchar(20) NOT NULL COMMENT '푸시 종류',
`CUST_SEQ` int(11) unsigned NOT NULL COMMENT '고객 순번',
`STD_DATE` varchar(8) GENERATED ALWAYS AS (left(`SCHEDULE_DT`,8)) VIRTUAL,
`SCHEDULE_DT` varchar(14) NOT NULL COMMENT '발송 희망 일시',
`REG_DATE` datetime NOT NULL COMMENT '등록 일시',
PRIMARY KEY (`STACK_SEQ`),
UNIQUE KEY `PUSH_TYPE` (`PUSH_TYPE`,`STD_DATE`,`CUST_SEQ`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='비정기 푸시 발송 대기 테이블';
스크립트등 운영에 적용을 위해 운영과 최대한 동일한 환경으로 맞춘 후 작업 하려고 합니다.
작업 후 추가 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]#
[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]#
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]#
1. MySQL 서버 중지
[root@localhost ~]# systemctl stop mysqld
2. MySQL 환경변수 설정
[root@localhost ~]# systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
3. MySQL 서버 가동
[root@localhost ~]# systemctl start mysqld
4. 비번 없이 root 로그인
[root@localhost ~]# mysql -u root
5. root 비밀번호 업데이트
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('새비밀번호')
-> WHERE User = 'root' AND Host = 'localhost';
ERROR 1348 (HY000): Column 'authentication_string' is not updatable
마리아 DB 10.4.12 에서는 위와 같은 방법으로는 에러가 발생합니다.
아래 방법으로 해결 가능합니다.
mysql> ALTER mysql.user 'root'@'localhost' identified by '새비밀번호';
Query OK, 0 rows affected (0.012 sec)
mysql> COMMIT;
mysql> FLUSH PRIVILEGES;
mysql> quit
6. MySQL 중지
[root@localhost ~]# systemctl stop mysqld
7. MySQL 환경변수 초기화
[root@localhost ~]# systemctl unset-environment MYSQLD_OPTS
8. MySQL 서버 시작
[root@localhost ~]# systemctl start mysqld
9. 새 비밀번호로 로그인 확인
[root@localhost ~]# mysql -u root -p
참고로 user 테이블이 변경 된것은 user 테이블의 접근을 쿼리가 아닌 시스템 명령으로 하면 큰 차이는 없을 것 같습니다.
MySQL 공식 문서에서도
alter user 'root'@'localhost' identified by '새비밀번호';
MySQL 5.6 까지는 설치후 콘솔에서 바로 root 로 접속 해서 비밀번호를 변경할 수 있었지만 MySQL 7 (ver 5.7) 부터는 설치 되면서 랜덤스트링을 암호화 한 비밀번호가 자동으로 부여되고 그것을 찾아서 접속해서 원하는 비밀번호를 부여하는 방식으로 바뀐것 같습니다.
왜 그럴까... 생각해 보면 많은 분들이 MySQL을 설치하고 root 비밀번호를 관리 하지 않아서 외부로 부터 쉽게 칩입을 당하는 케이스들 때문에 변경 된 것이라고 생각합니다.
그런데 이런 것을 모르는 상태에서 MySQL 7 버전을 설치 했다가 root 접속을 못해서 난감했던 기억이 나네요.
일단 설치는 위에 올린 링크를 참고 하시고 (이 포스트와 같은 내용이 설치에도 나옵니다.) mysqld-safe --skip-grant-tables 명령을 대신헤서 5.7이상 버전에서는 비밀번호 분실시 어떻게 비밀번호를 변경하는지 확인해 보겠습니다.
[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
2018-02-13T14:49:45.720116Z 1 [Note] A temporary password is generated for root@localhost: tur++-dvf7tI
tur++-dvf7tI 이게 초기 비밀번호 입니다.
임시 비밀번호로 로그인 해서 비번을 바꾸는 내용입니다.
[root@localhost ~]# mysql -u root -p
Enter password : tur++-dvf7tI
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
/* 임시 비번을 변경하기 전에는 아무것도 할 수가 없네요 */
/* 새로운 비밀번호로 변경 */
mysql> alter user 'root'@'localhost' identified by '새비밀번호';
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
비밀번호가 잘 바뀌었는지 확인해 보도록 하겠습니다.
[root@localhost download]# mysql -u root -p
Enter password:변경한비밀번호
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> 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
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> quit
Bye
잘 변경이 된 것 같습니다.
root 계정의 비밀번호를 분실 했을 경우 바꾸는 방법
끝으로 이전 버전에서 root 비밀번호를 분실 했을때 유용하게 이용되던 mysqld-safe 명령에 대해서 알아 보겠습니다.
1. MySQL 서버 중지
[root@localhost ~]# systemctl stop mysqld
2. MySQL 환경변수 설정
[root@localhost ~]# systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
3. MySQL 서버 가동
[root@localhost ~]# systemctl start mysqld
4. 비번 없이 root 로그인
[root@localhost ~]# mysql -u root
5. root 비밀번호 업데이트
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('새비밀번호')
-> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit
6. MySQL 중지
[root@localhost ~]# systemctl stop mysqld
7. MySQL 환경변수 초기화
[root@localhost ~]# systemctl unset-environment MYSQLD_OPTS
8. MySQL 서버 시작
[root@localhost ~]# systemctl start mysqld
9. 새 비밀번호로 로그인 확인
[root@localhost ~]# mysql -u root -p
참고로 user 테이블이 변경 된것은 user 테이블의 접근을 쿼리가 아닌 시스템 명령으로 하면 큰 차이는 없을 것 같습니다.
MySQL 공식 문서에서도
alter user 'root'@'localhost' identified by '새비밀번호';
테이블을 생성하고 데이터를 조작 해야 할 일이 있어서 실 서버의 특정 테이블을 개발서버로 옮겨서 작업 하기로 했다.
실무 담당자의 양해를 구하고 개발이 끝나면 삭제하는 조건으로 실서버에서 원하는 테이블만 추출해서 회사내의 개발 서버로 옮겨서 imp 했더니 아래와 같은 에러가 발생 했다.
IMP-00010: 엑스포트 파일이 유효하지 않고, 헤더가 검증에 실패했습니다 IMP-00000: 임포트가 실패로 끝났습니다
구글링 해보니 dump 받은 파일을 FTP로 전송 할 때 Binary 모드가 아니고 Ascii 모드로 전송하면 저런 에러가 발생 한다는 블로그가 검색 되었지만... 나의 경우는 이문제가 아니고 오라클 버전의 차이때문에 발생하는 문제 였다.
운영서버의 오라클은 11g, 회사 내부의 개발 서버가 Windows 2008 Server라서 11g 64bit 설치중 에러가 발생해서 10g로 설치 했었다.
즉 11g에서 exp 명령으로 받은 dump 파일을 10g에서 imp명령으로 올릴 경우 저런 에러가 발생 하는 것 같다. (반대의 경우는 괜찮 겠지...)
exp또는 imp 명령의 인자로 버전을 지정 할 수도 있다고 하던데... 귀찮아서 패스
개발 서버의 exp 명령으로 실서버에 접속하여 덤프 받아서 개발서버에 import 하기로 했다.
앞서 했던 방법이랑 동일 한 방법 같지만 에러가 발생한 방법은 11g 오라클 클라이언트로 11g에 접속 해서 DB를 덤프 받아서 10g서버의 오라클 클라이언트로 10g서버에 접속해서 import 하는 방법이라면 후자는 10g의 오라클 클라이언트를 이용하여 11g 서버에 접속해서 export 받고 10g의 오라클 클라이언트를 이용하여 10g 서버에 import 하는 것으로 말장난 같지만 확연히 다른 방법이다.
1. TNS정보 추가
10g의 오라클 클라이언트에서 11g를 접속 할 수 있도록 tnsnames.ora 파일에 11g의 TNS정보 추가
