MySQL 8.0.32 에서 테스트 하였습니다.

// sysdocu 계정에 전체 DB 의 모든 권한 부여
mysql> grant all privileges on *.* to sysdocu@localhost;

// sysdocu 계정에 전체 DB 의 모든 권한 제거
mysql> revoke all privileges on *.* from sysdocu@localhost;

// sysdocu 계정에 한개 DB 의 모든 권한 부여
mysql> grant all privileges on sysdocudb.* to sysdocu@localhost;

// sysdocu 계정에 한개 DB 의 모든 권한 제거
mysql> revoke select on sysdocudb.* from sysdocu@localhost;

* 추가 테스트
- 계정에 모든 DB 접근 권한을 주고, 하나의 DB 권한을 취소하는건 안됩니다. (쿼리 생략)

- 계정에 모든 DB 접근 권한을 주고, 하나의 액션만 취소하는건 됩니다. (액션 단위는 가능한듯함)

mysql> grant all privileges on *.* to sysdocu@localhost;

mysql> revoke select on *.* from sysdocu@localhost;

sysdocu 계정에 select 권한을 제거한 뒤, sysdocu 계정으로 재 로그인하여 select 쿼리를 실행하면 권한이 없다고 출력됩니다.

mysql> select * from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'sysdocu'@'localhost' for table 'user'

권한을 부여한 그대로의 룰 ({DB명}.{TABLE명}) 로만 권한 제거가 가능한듯 합니다.

[ 현재 auto_increment 확인 ]

아래 예제는 list 테이블의 auto_increment 값을 확인하는 쿼리이며, Auto_increment 컬럼에서 다음 순번을 확인할 수 있습니다.

mysql> show table status like 'list';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| list | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |        3855011 | 2023-01-17 08:29:02 | NULL        | NULL       | utf8mb3_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

[ auto_increment 초기화 ]

mysql> alter table [테이블명] auto_increment='1';

설정 후, 다음 insert 쿼리의 값은 1 번으로 입력됩니다.

본 매뉴얼에서는 PostgreSQL 동기화 방식중 마스터 서버 로그를 이용해 동기화 하는 Streaming 방식으로 진행합니다.

이 방식은 단점이 하나 있는데 마스터 서버와 슬레이브 서버간의 네트워크 단절 시간이 길어지면 마스터 서버의 WAL 파일이 덮어 씌워져 (구버전에서는 파일 개수 지정, 신버전에서는 용량 지정 가능) 슬레이브 서버로 데이터를 전송하기도 전에 옛날 데이터는 지워져 버리는 경우가 생길 수 있습니다. 이경우 다시 Replication 을 구성해야 하지만 Streaming 방식은 9.0 이상에서 지원되는 최신 기술이고 속도가 빠른 장점이 있어 본 매뉴얼의 동기화 방식으로 채택하였습니다. 일반적으로 많이 사용하는 방식 이기도 합니다.

추가 정보는 공식 홈페이지 Documents (https://www.postgresql.org/docs/) 를 참고해 주세요.

버전에 따라 옵션 이름이 다른 경우가 있으므로 해당 버전의 Document 를 찾아서 보는것이 좋습니다.

여기에서는 최소한의 옵션을 이용하여 동기화를 해보도록 하겠습니다.

[ 테스트 환경 ]

- OS : RockyLinux 9.0

- 서버 수량 : 2대 (마스터 192.168.1.2 / 슬레이브 192.168.1.3)

- 사용 버전 : PostgreSQL 13.7

* 데이터 안전을 위해 작업전 백업은 필수 입니다.

1. PostgreSQL 설치

(모든 서버에서)

# yum -y update

# yum -y install postgresql postgresql-server

부팅시 데몬이 자동 구동 되도록 허용합니다.

# systemctl enable postgresql

PostgreSQL 설치시 자동 생성된 postgres 계정으로 데이터베이스를 초기화 합니다.

# sudo -u postgres /usr/bin/initdb -D /var/lib/pgsql/data

초기화가 완료 되었으면 데몬을 시작합니다.

# systemctl start postgresql

2. 테스트 DB 생성 및 데이터 입력

(마스터 서버에서)

postgres 계정으로 postgresql 에 접속합니다.

# sudo su - postgres

$ psql
psql (13.7)
Type "help" for help.

postgres=# 

테스트를 위해 임의의 데이터베이스, 테이블을 생성하고 데이터를 입력합니다.

postgres=# CREATE DATABASE sysdocudb;

postgres=# CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL);

postgres=# INSERT INTO users (name, email) VALUES ('CDH', 'cdh@tistory.com'), ('CJE', 'cje@tistory.com');

입력된 데이터를 확인합니다.

postgres=# SELECT * FROM users;

 id | name |      email      
----+------+-----------------
  1 | CDH  | cdh@tistory.com
  2 | CJE  | cje@tistory.com
(2 rows)

PostgreSQL 명령행을 빠져나갈 경우 아래와 같이 실행합니다.

postgres=# \q

3. 계정 생성

(마스터 서버에서)

복제를 위해 데이터베이스에 접근 할 수 있는 Replication 전용 계정을 생성합니다.

postgres=# CREATE ROLE repluser WITH REPLICATION PASSWORD '12345678' LOGIN;

생성된 계정을 확인합니다.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repluser  | Replication                                                | {}

4. 마스터 서버 설정

PostgreSQL 설정 파일에서 생성한 계정의 권한을 줍니다.

root 사용자로 전환 후 아래 파일에 인증 및 권한 설정을 추가합니다.

# vi /var/lib/pgsql/data/pg_hba.conf

- 첫번째줄 : 모든 IP 에서 원격 접근 허용

- 두번째줄 : 위에서 생성했던 replication 전용 계정과 접근 허용할 슬레이브 서버 IP

다른 설정 파일에서 복제 관련 옵션을 아래 값으로 설정합니다.

# vi /var/lib/pgsql/data/postgresql.conf

- listen_addresses : 접속을 허용할 서버 아이피 주소 (client 아이피 아님 주의)

- wal_level : Replication의 레벨을 설정합니다. replica로 설정하면 replication을 위한 모든 WAL 데이터를 생성합니다.

설정 적용을 위해 postgresql 데몬을 재시작 합니다.

# systemctl restart postgresql

또는 postgres 계정으로 데몬을 재시작

# sudo su - postgres
$ pg_ctl reload
server signaled

5. 슬레이브 서버 설정

(슬레이브 서버에서)

마스터 서버에 데이터를 한차례 전부 가져와야 합니다.

아래는 명령 수행 전 확인사항입니다.

- 슬레이브 서버의 설정 파일 백업 (postgresql.conf)

   # cp -arp /var/lib/pgsql/data/postgresql.conf /root/

- 슬레이브 서버의 data 디렉토리는 비워 놓기

   # rm -rf /var/lib/pgsql/data/*

- 마스터 서버에서 방화벽 확인 (TCP 5432)

모두 확인 되었으면 postgres 계정으로 데이터 복사 명령을 수행합니다.

# sudo -u postgres pg_basebackup -h 192.168.1.2 /var/lib/pgsql/data -U repluser -v -P --wal-method=stream --write-recovery-conf

별도로 백업했었던 설정 파일을 복원하고 내용을 수정합니다.

data 디렉토리를 복사해왔기 때문에 conf 파일도 바뀌었기 때문입니다.

# cp -arp /root/postgresql.conf /var/lib/pgsql/data/

# vi /var/lib/pgsql/data/postgresql.conf

- primary_conninfo : 마스터 서버에 접근하기 위한 replication 계정 정보

# systemctl restart postgresql

아래 파일이 자동 생성되며 동기화가 시작됩니다.

/var/lib/pgsql/data/standby.signal

마스터 서버에서 insert 쿼리를 실행하면 슬레이브 서버에서도 데이터 확인이 가능합니다.

마스터 서버에서 상태값 호출로 확인하는 방법도 있습니다.

# SELECT * FROM pg_stat_activity where usename='repluser';
 datid | datname |  pid   | leader_pid | usesysid | usename  | application_name |  client_addr   | client_hostname | client_port |         backend_start         | xact_start |          query_start       
   |         state_change          | wait_event_type |  wait_event   | state  | backend_xid | backend_xmin |                  query                  | backend_type 
-------+---------+--------+------------+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+------------+----------------------------
---+-------------------------------+-----------------+---------------+--------+-------------+--------------+-----------------------------------------+--------------
       |         | 109299 |            |    16393 | repluser | walreceiver      | 192.168.1.3 |                 |       38854 | 2023-03-13 16:55:25.630835+09 |            | 2023-03-13 16:55:25.644428+
09 | 2023-03-13 16:55:25.644449+09 | Activity        | WalSenderMain | active |             |              | START_REPLICATION 0/12000000 TIMELINE 1 | walsender
(1 row)

MySQL (MariaDB) 에는 여러 로그가 있지만 그 중 general log 에 사용하기 적합한 logrotate 설정법 입니다.

binary log 파일은 my.cnf 환경 설정 파일에서 보관 일수를 정할 수 있기 때문에 디스크를 많이 사용하지 않지만

general log 는 제한없이 하나의 파일에 계속 데이터가 누적되기 때문입니다.

아래와 같이 설정을 하면 mysql 재시작 없이 자동적으로 설정한 값에 의해 로그 파일이 분리, 삭제 됩니다.

우선 logrotate 설정 파일이 모여 있는 디렉토리로 이동 후 mysql 설치된 디렉토리의 샘플 mysql-log-rotate 파일을 가져와서 수정 합니다.

# cd /etc/logrotate.d

# cp -arp /usr/local/mysql/support-files/mysql-log-rotate .

# vi mysql-log-rotate

패스워드와 파일 변경 후 flush 할 로그 파일을 명시합니다.

여러개의 로그파일을 flush 하고자 할 경우 아래와 같이 띄어쓰기로 구분하여 사용합니다.

--local flush-error-log flush-engine-log flush-general-log flush-slow-log

설정이 잘 되었는지 미리 확인 (실행) 하는 방법은 아래와 같습니다.

# logrotate -f /etc/logrotate.d/mysql-log-rotate

참고로 flush-general-log 등의 옵션은 DB 버전에 따라 제공되거나 그렇지 않습니다.

테스트 환경은 MariaDB 10.6.9 이며 flush-general-log 옵션이 확인되었지만,

mysql 8.0.26 버전에서는 해당 옵션이 확인되지 않아서 명령 실행이 불가능 했습니다.

percona-xtrabackup 패키지는 MySQL 또는 MariaDB 서버의 서비스 중지 없이 Replication 을 구성하기 위한 명령어 입니다.

아래 내용은 CentOS 8 환경에서 MariaDB 10.6.9 버전으로 테스트 하였습니다.

사용자 계정이나 패스워드, 디렉토리 등은 사용자 환경에 따라 다르므로 명령 실행시 확인 후 실행하시기 바랍니다.

- master 서버 : 192.168.10.2

- slave 서버 : 192.168.10.3

- 참고1 : percona-xtrabackup 패키지는 MySQL 과 MariaDB 둘 다 가능합니다. 다만 설치 버전에 따라 지원가능한 DB 버전이 다르며 MySQL 은 xtrabackup, MariaDB 는 mariabackup 이라는 명령을 사용하는것이 큰 차이점 입니다.

(redo 로그를 기록하는 파일이 다름)

- LINK : xtrabackup 을 이용한 MySQL 8.0.28 Replication 구성하기

1. 설치

(인터넷 연결 가능할 경우)

master 서버와 slave 서버에 xtrabackup 을 설치 합니다.

# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# yum -y install percona-xtrabackup-24

(인터넷 연결 불가의 경우)

사설망에서 DB를 운영할 경우 서버에 없는 패키지는 설치 파일을 다운 받아 복사하여 설치하면 됩니다.

서버 환경에 따라 필요한 패키지가 다를 수 있으며 이는 아래 rpm 명령으로 의존성 확인이 가능합니다.

# wget https://vault.centos.org/centos/8/AppStream/x86_64/os/Packages/libev-4.24-6.el8.x86_64.rpm

# wget https://vault.centos.org/centos/8/BaseOS/x86_64/os/Packages/perl-DBI-1.641-1.el8.x86_64.rpm

# wget https://vault.centos.org/centos/8/AppStream/x86_64/os/Packages/perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64.rpm

# wget https://vault.centos.org/centos/8/BaseOS/x86_64/os/Packages/rsync-3.1.3-12.el8.x86_64.rpm

# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-80-8.0.28-21.1.el8.x86_64.rpm 

필요한 패키지를 다운로드 한 후 DB 서버로 옮깁니다. (scp 또는 rsync 등으로 복사하면 됩니다. 방법은 생략)

그리고 아래 순서에 따라 설치합니다.

# rpm -ivh libev-4.24-6.el8.x86_64.rpm

# rpm -ivh perl-DBI-1.641-1.el8.x86_64.rpm

# rpm -ivh perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64.rpm

# rpm -ivh rsync-3.1.3-12.el8.x86_64.rpm

# rpm -ivh percona-xtrabackup-80-8.0.28-21.1.el8.x86_64.rpm

2. 원본 데이터 백업

(master 서버에서)

# mariabackup --defaults-file=/etc/my.cnf --backup --user=root --password=12345678 --target-dir=/mariabackup --no-lock --port=3306 --socket=/tmp/mysql.sock

백업이 완료되면 /mariabackup 디렉토리가 생성되며 하단에 백업 파일들이 존재하게 됩니다.

data 디렉토리 내 파일들과 mariabackup 관련된 로그가 있습니다.

3. 데이터 이전

(master 서버에서)

master 서버에 백업된 데이터를 slave 서버로 복사합니다.

# rsync -avzPog /mariabackup root@192.168.10.3:/

4. 데이터 복원

(slave 서버에서)

mysqld 데몬을 중지하고 기존 data 디렉토리를 삭제 합니다. (mv 명령으로 디렉토리 이름 변경 권장)

가져온 데이터를 복구하는 과정에서 기존 data 디렉토리내 파일이 있으면 실행이 되지 않기 때문입니다.

백업중 작성된 redo 로그를 데이터에 적용합니다.

# mariabackup --prepare --target-dir=/mariabackup

> --use-memory=10G 와 같이 본 작업에 사용할 시스템 메모리양을 어느정도 할당 해주면 빠른처리가 가능합니다.

> redo 로그 적용 도중에 아래와 같은 에러 메세지가 출력 될 경우 --innodb_force_recovery=1 옵션을 추가 합니다.

2022-10-14  8:47:54 0 [Note] InnoDB: Set innodb_force_recovery=1 to ignore corrupted pages.
2022-10-14  8:47:54 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
[00] FATAL ERROR: 2022-10-14 08:47:54 mariabackup: innodb_init() returned 11 (Generic error).

하지만 백업 과정에서 문제가 생겼을 수 있으므로 master 서버에서 몇 번 더 백업을 반복 시도해 보는것도 좋습니다.

백업 파일을 이동합니다. (--copy-back 를 사용하면 복사)
# mariabackup --move-back --datadir=/usr/local/mysql/data --target-dir=/mariabackup

root 로 복원하였기 때문에 복원된 파일의 권한이 root 로 되어 있습니다.

mysql 가동을 위해 data 디렉토리의 권한을 설정을 합니다.

# chmod 755 /usr/local/mysql/data
# chown mysql.mysql -R /usr/local/mysql/data

# systemctl start mysqld

5. 로그 포지션 설정 및 동기화 시작

백업본의 xtrabackup_binlog_info 에 있는 mysql-bin 파일명과 position 값 참고하여
replication 을 적용합니다.

(slave 서버에서)

# cat /usr/local/mysql/data/xtrabackup_info |grep binlog_pos

binlog_pos = filename 'binlog.000002', position '943'    // 출력된 내용을 참고

# mysql -p

mysql> stop slave;

mysql> reset slave;

mysql> change master to master_host='192.168.10.2', master_user='repl',
master_password='12345678', master_log_file='binlog.000002',
master_log_pos=943;

mysql> start slave;

정상적으로 동기화 된 것을 확인할 수 있습니다.

mysql> show slave status \G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

1. 필수 패키지 설치

# dnf -y update
# dnf -y install cmake ncurses-devel openssl openssl-devel

작업이 잘 수행 되도록 아래 명령 실행 하고, 서버 부팅시에도 selinux 비활성화를 위해 설정을 수정해 줍니다.

# setenforce 0

# vi /etc/selinux/config

SELINUX=disabled

2. MariaDB 계정 추가

# groupadd -g 400 mysql
# useradd -u 400 -g 400 -d /usr/local/mysql -s /bin/false mysql

3. MariaDB 설치

# cd /usr/local/src
# wget https://mirror-mariadb.nzbilisim.net/mariadb/pub/mariadb/mariadb-10.9.2/source/mariadb-10.9.2.tar.gz
# tar xvfz mariadb-10.9.2.tar.gz
# cd mariadb-10.9.2
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DWITH_INNOBASE_STORAGE_ENGINE=1 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1
# make
# make install

4. MariaDB 환경 설정

환경 설정 파일이 없어도 아래 '5번 항목' 에서 기본 DB 생성 후 MariaDB 구동은 가능하지만

my.cnf 파일을 생성하면 서버 사양 및 운영 방식에 알맞게 환경을 조정할 수 있습니다.

아래는 InnoDB 구성을 위한 예 입니다.

# vi /etc/my.cnf

* 옵션 설명은 아래 URL 참고

https://yakolla.tistory.com/61

https://bae9086.tistory.com/175 

https://systemv.tistory.com/48

5. MariaDB 초기 데이터 베이스 생성

생성해두었던 my.cnf 파일을 읽어 환경에 맞게 기본 DB 를 생성합니다.

MariaDB 는 10.4 버전부터 새로운 암호화 방식이 추가 되었습니다. (MySQL 5.7 이후 버전과 동일)

그 이전 버전과 같은 암호화 방식을 사용하려는 경우 --auth-root-authentication-method=normal 옵션을 추가하여 data 디렉토리를 생성하면 됩니다.

(예 : 계정 패스워드 변경시)

- 구버전 : update user set...

- 신버전 : alter user...

# ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

DB 생성시 아래와 같은 메세지가 출력된 경우

chown: cannot access '/auth_pam_tool_dir': 그런 파일이나 디렉터리가 없습니다
Cannot change ownership of the '/auth_pam_tool_dir' directory
to the 'mysql' user. Check that you have the necessary permissions and try again.

다음과 같이 조치하고, data 디렉토리 삭제 및 mysql_install_db 명령을 다시 실행합니다.

# mkdir /auth_pam_tool_dir
# touch /auth_pam_tool_dir/auth_pam_tool
# chown mysql.mysql /auth_pam_tool_dir -R
# chmod 770 /auth_pam_tool_dir -R

6. MariaDB 구동

데몬 구동을 쉽게 하기 위해 명령어를 복사하고, 서버 부팅시 자동으로 데몬이 구동 되도록 설정합니다.

# cp -arp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# chkconfig --add mysqld
# chkconfig mysqld on

# /etc/init.d/mysqld start

* 이후 작업 (필요에 따라 설정)

1) mysql 명령을 경로에 상관없이 사용하도록 설정 추가

# vi /etc/profile

PATH="$PATH:/usr/local/mysql/bin"

# source /etc/profile

2) firewalld 방화벽 설정 (서버 외부에서 DB 바로 접근 필요시)

# firewall-cmd --zone=public --add-port=3306/tcp

3) MySQL root 패스워드 업데이트

mysql> use mysql;

mysql> alter user root@localhost identified by 'NEWPASSWD';

mysql> flush privileges;

sysbench 는 설치된 MySQL DB 서버의 성능을 확인하는 도구 입니다.

아래는 Rocky Linux 8.6 환경에서 테스트 하였습니다.

1. 설치

# yum -y install sysbench


2. 테스트 DB 생성

데이터를 쓰고 읽을 테스트용 DB 를 하나 만들어 줍니다.
mysql> create database test;
Query OK, 0 rows affected (0.01 sec)

3. 테스트 데이터 준비

# sysbench \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=12345678 \
--mysql-db=test \
--table-size=1000000 \
--tables=5 \
/usr/share/sysbench/oltp_read_only.lua \
prepare

(결과)
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 1000000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 1000000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 1000000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...

* 참고1

명령 실행시 마지막줄 prepare 는 sysbench 에서 지원하는 명령어이며 사용 가능한 명령어 종류는 아래와 같습니다.
prepare, prewarm, run, cleanup, help

주로 사용하는 옵션
- prepare : 테스트를 준비합니다. test 데이터베이스에 테스트할 임시 데이터를 넣어 놓습니다.
- run : 실제적인 퍼포먼스 테스트를 진행 합니다.
- cleanup : 테스트 하면서 생성된 테이블 및 레코드를 삭제합니다.

* 참고2

/usr/share/sysbench 디렉토리에는 데이터 insert, read, delete 등의 테스트를 할 수 있는 파일이 준비되어 있습니다.

아래에서 순차적으로 실행해 보도록 하겠습니다.

(확인)
테스트 DB 에 테이블 생성 및 데이터 입력이 확인되었습니다.

mysql> show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1            |
| sbtest2            |
| sbtest3            |
| sbtest4            |
| sbtest5            |
+--------------------+
5 rows in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.75 sec)


4. 테스트 실행

# sysbench \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=12345678 \
--mysql-db=test \
--table-size=1000000 \
--tables=5 \
/usr/share/sysbench/oltp_read_only.lua \
run

(결과)
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            145838
        write:                           0
        other:                           20834
        total:                           166672
    transactions:                        10417  (1041.42 per sec.)
    queries:                             166672 (16662.68 per sec.)  // 초당 16,662 개의 read 쿼리가 실행되었습니다.
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0010s
    total number of events:              10417

Latency (ms):
         min:                                    0.91
         avg:                                    0.96
         max:                                    1.87
         95th percentile:                        1.01
         sum:                                 9995.05

Threads fairness:
    events (avg/stddev):           10417.0000/0.00
    execution time (avg/stddev):   9.9950/0.00

(결과2)

oltp_insert.lua 파일로 쓰기 테스트를 한 경우는 아래와 같은 결과가 나왔습니다.

    transactions:                        56036  (5602.54 per sec.)
    queries:                             56036  (5602.54 per sec.)  // 초당 5,602 개의 write 쿼리가 실행되었습니다.

(결과3)

oltp_delete.lua 파일로 삭제 테스트를 한 경우는 아래와 같은 결과가 나왔습니다.

    transactions:                        87458  (8744.05 per sec.)
    queries:                             87458  (8744.05 per sec.)  // 초당 8,744 개의 records 를 삭제 하였습니다.

5. 테스트 데이터 삭제

테스트를 마쳤으면 용량 확보를 위해 테스트 데이터를 삭제 합니다.

# sysbench \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=12345678 \
--mysql-db=test \
--table-size=1000000 \
--tables=5 \
/usr/share/sysbench/oltp_read_only.lua \
cleanup

(결과)
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...

테스트 DB 도 같이 삭제해 줍니다.

mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)

1. 필수 패키지 설치

# dnf -y update
# dnf -y install cmake ncurses-devel gcc* openssl openssl-devel libtirpc-devel
# dnf -y install epel-release
# dnf config-manager --set-enabled powertools
# dnf -y install rpcgen

작업이 잘 수행 되도록 아래 명령 실행 하고, 서버 부팅시에도 selinux 비활성화를 위해 설정을 수정해 줍니다.

# setenforce 0

# vi /etc/selinux/config

SELINUX=disabled 

2. MySQL 계정 추가

# groupadd -g 400 mysql
# useradd -u 400 -g 400 -d /usr/local/mysql -s /bin/false mysql

3. MySQL 설치

# cd /usr/local/src
# wget https://ftp.kaist.ac.kr/mysql/Downloads/MySQL-8.0/mysql-8.0.30.tar.gz
# tar xvfz mysql-8.0.30.tar.gz
# cd mysql-8.0.30
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DWITH_SSL=system -DMYSQL_TCP_PORT=3306 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_COLLATION=utf8_general_ci -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/include/boost -DFORCE_INSOURCE_BUILD=1
# make
# make install

4. MySQL 환경 설정

환경 설정 파일이 없어도 아래 '5번 항목' 에서 기본 DB 생성 후 MySQL 구동은 가능하지만

my.cnf 파일을 생성하면 서버 사양 및 운영 방식에 알맞게 환경을 조정할 수 있습니다.

아래는 InnoDB 구성을 위한 예 입니다.

# vi /etc/my.cnf

* 옵션 설명은 아래 URL 참고

https://yakolla.tistory.com/61

https://bae9086.tistory.com/175 

https://systemv.tistory.com/48

5. MySQL 초기 데이터 베이스 생성 및 권한 설정

생성해두었던 my.cnf 파일을 읽어 환경에 맞게 기본 DB 를 생성합니다.

# /usr/local/mysql/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql
# chown -R mysql.mysql /usr/local/mysql/
# chmod 711 /usr/local/mysql
# chmod 700 /usr/local/mysql/data
# chmod 751 /usr/local/mysql/bin/
# chmod 750 /usr/local/mysql/bin/*
# chmod 755 /usr/local/mysql/bin/mysql
# chmod 755 /usr/local/mysql/bin/mysqldump

6. MySQL 구동

데몬 구동을 쉽게 하기 위해 명령어를 복사하고, 서버 부팅시 자동으로 데몬이 구동 되도록 설정합니다.

# cp -arp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# chkconfig --add mysqld
# chkconfig mysqld on

# /etc/init.d/mysqld start

* 이후 작업 (필요에 따라 설정)

1) mysql 명령을 경로에 상관없이 사용하도록 설정 추가

# vi /etc/profile

PATH="$PATH:/usr/local/mysql/bin"

# source /etc/profile

2) firewalld 방화벽 설정 (서버 외부에서 DB 바로 접근 필요시)

# firewall-cmd --zone=public --add-port=3306/tcp

3) MySQL root 패스워드 업데이트

mysql> use mysql;

mysql> alter user root@localhost identified by 'NEWPASSWD';

mysql> flush privileges; 

4) 새 계정 추가

mysql> create database sysdocudb;

mysql> create user 'sysdocu'@'localhost' identified by '12345678';
mysql> grant all privileges on sysdocudb.* to 'cc'@'localhost';
mysql> flush privileges;

아래와 같은 쿼리로 특정 DB 의 테이블별 용량 및 레코드 수를 조회할 수 있습니다.

모든 DB 의 테이블별로 조회를 하고자 할 경우 아래 파란색 부분을 제외하면 됩니다.

mysql> SELECT table_name, (data_length + index_length)/1024/1024 AS total_mb, table_rows FROM information_schema.TABLES WHERE table_schema='sysdocu';

[결과]

+------------+------------+------------+
| table_name | total_mb   | table_rows |
+------------+------------+------------+
| contents   | 0.50000000 |       4126 |
| sites      | 0.01562500 |         10 |
+------------+------------+------------+
3 rows in set (0.001 sec)

percona-xtrabackup 패키지는 MySQL 또는 MariaDB 서버의 서비스 중지 없이 Replication 을 구성하기 위한 명령어 입니다.

아래 내용은 CentOS 8 환경에서 MySQL 8.0.28 버전으로 테스트 하였습니다.

사용자 계정이나 패스워드, 디렉토리 등은 사용자 환경에 따라 다르므로 명령 실행시 확인 후 실행하시기 바랍니다.

- master 서버 : 192.168.10.2

- slave 서버 : 192.168.10.3

- 참고1 : xtrabackup 8.0 미만 버전에서는 innobackupex 라는 명령이 있었으나

              xtrabackup 8.0 버전부터 xtrabackup 명령으로 통합되었습니다.

- 참고2 : 설치된 패키지 버전을 자세히 보면 지원하는 DB 버전을 알 수 있습니다.

              percona-xtrabackup-80-8.0.28 은 MySQL 8.0.28 버전까지 백업이 가능합니다.

- 참고3: percona-xtrabackup 패키지는 MySQL 과 MariaDB 둘 다 가능합니다. 다만 설치 버전에 따라 지원가능한 DB 버전이 다르며 MySQL 은 xtrabackup, MariaDB 는 mariabackup 이라는 명령을 사용하는것이 큰 차이점 입니다.

(redo 로그를 기록하는 파일이 다름)

- LINK : mariabackup 을 이용한 MariaDB 10.6.9 Replication 구성하기

1. 설치

(인터넷 연결 가능할 경우)

master 서버와 slave 서버에 xtrabackup 을 설치 합니다.

# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# yum -y install percona-xtrabackup-80

(인터넷 연결 불가의 경우)

사설망에서 DB를 운영할 경우 서버에 없는 패키지는 설치 파일을 다운 받아 복사하여 설치하면 됩니다.

서버 환경에 따라 필요한 패키지가 다를 수 있으며 이는 아래 rpm 명령으로 의존성 확인이 가능합니다.

# wget https://vault.centos.org/centos/8/AppStream/x86_64/os/Packages/libev-4.24-6.el8.x86_64.rpm

# wget https://vault.centos.org/centos/8/BaseOS/x86_64/os/Packages/perl-DBI-1.641-1.el8.x86_64.rpm

# wget https://vault.centos.org/centos/8/AppStream/x86_64/os/Packages/perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64.rpm

# wget https://vault.centos.org/centos/8/BaseOS/x86_64/os/Packages/rsync-3.1.3-12.el8.x86_64.rpm

# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-80-8.0.28-21.1.el8.x86_64.rpm 

필요한 패키지를 다운로드 한 후 DB 서버로 옮깁니다. (scp 또는 rsync 등으로 복사하면 됩니다. 방법은 생략)

그리고 아래 순서에 따라 설치합니다.

# rpm -ivh libev-4.24-6.el8.x86_64.rpm

# rpm -ivh perl-DBI-1.641-1.el8.x86_64.rpm

# rpm -ivh perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64.rpm

# rpm -ivh rsync-3.1.3-12.el8.x86_64.rpm

# rpm -ivh percona-xtrabackup-80-8.0.28-21.1.el8.x86_64.rpm

2. 원본 데이터 백업

(master 서버에서)

# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=12345678 --backup --target-dir=/xtrabackup --no-lock --port=3306 --socket=/tmp/mysql.sock

백업이 완료되면 /xtrabackup 디렉토리가 생성되며 하단에 백업 파일들이 존재하게 됩니다.

data 디렉토리 내 파일들과 xtrabackup 관련된 로그가 있습니다.

3. 데이터 이전

(master 서버에서)

master 서버에 백업된 데이터를 slave 서버로 복사합니다.

# rsync -avzPog /xtrabackup root@192.168.10.3:/

4. 데이터 복원

(slave 서버에서)

mysqld 데몬을 중지하고 기존 data 디렉토리를 삭제 합니다. (mv 명령으로 디렉토리 이름 변경 권장)

가져온 데이터를 복구하는 과정에서 기존 data 디렉토리내 파일이 있으면 실행이 되지 않기 때문입니다.

백업중 작성된 redo 로그를 데이터에 적용합니다.

# xtrabackup --prepare --target-dir=/xtrabackup

> --use-memory=10G 와 같이 본 작업에 사용할 시스템 메모리양을 어느정도 할당 해주면 빠른처리가 가능합니다.

> redo 로그 적용 도중에 아래와 같은 에러 메세지가 출력 될 경우 --innodb_force_recovery=1 옵션을 추가 합니다.

2022-10-14  8:47:54 0 [Note] InnoDB: Set innodb_force_recovery=1 to ignore corrupted pages.
2022-10-14  8:47:54 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
[00] FATAL ERROR: 2022-10-14 08:47:54 xtrabackup: innodb_init() returned 11 (Generic error).

하지만 백업 과정에서 문제가 생겼을 수 있으므로 master 서버에서 몇 번 더 백업을 반복 시도해 보는것도 좋습니다.

백업 파일을 이동합니다. (--copy-back 를 사용하면 복사)
# xtrabackup --move-back --datadir=/usr/local/mysql/data --target-dir=/xtrabackup

root 로 복원하였기 때문에 복원된 파일의 권한이 root 로 되어 있습니다.

mysql 가동을 위해 data 디렉토리의 권한을 설정을 합니다.

# chmod 755 /usr/local/mysql/data
# chown mysql.mysql -R /usr/local/mysql/data

# systemctl start mysqld

5. 로그 포지션 설정 및 동기화 시작

백업본의 xtrabackup_binlog_info 에 있는 mysql-bin 파일명과 position 값 참고하여
replication 을 적용합니다.

(slave 서버에서)

# cat /usr/local/mysql/data/xtrabackup_info |grep binlog_pos

binlog_pos = filename 'binlog.000004', position '156'    // 출력된 내용을 참고

# mysql -p

mysql> stop slave;

mysql> reset slave;

mysql> change master to master_host='192.168.10.2', master_user='repl',
master_password='12345678', master_log_file='binlog.000004',
master_log_pos=156;

mysql> start slave;

정상적으로 동기화 된 것을 확인할 수 있습니다.

mysql> show slave status \G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

6. 과정 테스트

서비스 사용중 (무정지) 데이터 유실 없이 리플리케이션이 되는지 확인하기 위해 테스트를 진행했습니다.

1) master 서버 테이블에 1,000개의 레코드를 넣은 상태에서

2) 추가로 1,000개의 레코드를 입력하는 스크립트를 가동하고

3) 레코드가 입력되는 중에 xtrabackup 을 수행했습니다.

4) 1,000개의 레코드 입력이 완료되면 (계속 진행되어도 상관없음) slave 로 데이터를 넘겨서

5) --prepare 옵션으로 실행해주면 로그가 데이터에 적용되는것이 백분율로 보였고

6) 그 데이터를 --copy-back 하여 서비스할 위치로 복사하고 replication 포지션을 맞추고 연동하니

7) slave 서버에도 최종 2,000개의 레코드가 확인되었습니다.

레코드를 삭제할때 아래와 같은 에러 메세지가 출력되었다면

mysql> delete from log where idx='161';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`baudit`.`checklist`, CONSTRAINT `fk_checklist_log1` FOREIGN KEY (`log_idx`) REFERENCES `log` (`idx`))

다른 곳 (checklist 테이블의 log_idx 컬럼) 에서 현재 삭제하려는 데이터 (log 테이블의 idx 컬럼) 를 참조하고 있다는 메세지 입니다.

해결 방법으로는 아래 두가지가 있습니다.

1. 관련 데이터 삭제

순차적으로 checklist 테이블에서 관련 레코드 삭제 후 log 테이블에서 해당 레코드 삭제

2. 참조 무시하고 삭제

mysql> SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from log where idx='161';
Query OK, 1 row affected (0.02 sec)

mysql> SET foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)