[Centos 7] DB (Database) & DB Replication ( 읽기 전용 복제본 )

DB (Database)

   - 가장 큰 목표는 중복을 제거하고, 효율적으로 처리
   - 대부분 관계형 데이터 베이스를 주로 사용함
   - 사용자가 데이터를 DBMS로 전달하고 DBMS에 저장된 패턴으로 데이터를 저장하는 방식
   - 데이터 베이스는 우리가 관리 하는것은 아님 DB엔지니어가 따로 있음
   - 컬럼을 정의하고 속성값을 저장하는 방식은 정형 데이터베이스
   - 요즘은 비정형 데이터베이스를  사용해야 하는데 정형 데이터베이스에서 넘어가기 쉽지 않다.
  

$ DB 설치 및 접속

# yum -y install mariadb-*

# systemctl enable mariadb

# mysql_secure_installation → 비번 설정

# mysql -u root -p mysql

 -h localhost (생략가능, 기본값)
 -u : 접속할 계정명
 -h : 로컬호스트는 생략이 가능하고 ip주소를 입력하면 된다.
 -p : 인증방식은 패스워드 인증방식을 사용하겠다.
  ;(새미클론) : 어디까지 명령문인지 인식시켜줘야 함 
  ctrl + l   : clear

 

$ SQL(Structured Query Language)

   - 구조적 쿼리 언어

   - 관계형 데이터베이스에 정보를 저장하고 처리하기 위한 프로그래밍 언어

   - 데이터베이스 > 테이블 > 컬럼

$ DB backup

물리적인 백업

   - 데이터를 저장하는 파일자체를 백업하는 형식

   - 테이블이나 데이터베이스 자체를 백업

   - 보통 업체에 판매하는 백업솔루션을 온프래미스 환경에서 많이 사용

   - 안정성있게 백업을 하려면 백업프로그램을 사서 관리하는게 좋다.

 

논리적인 백업

   - 똑같이 만들 수 있는 sql 쿼리문 자체를 백업하는것

   - 마이그레이션 등을 효과적으로 사용

   - sql dump를 사용하면 같은 언어를 사용하는 프로그램 간에 어느 정도의 호환성이 있기때문에 mariadb에 정보를 sql 쿼리문으로 저장해서 옮겨준다.

   - 같은 sql언어를 사용한다 해도 db마다 사용하는 쿼리문이 다르기 때문에 완벽하진 않지만 가장 효과적으로 마이그레이션할 때 사용.

 

$ mysqldump를 이용 한 논리적 Backup

# mysqldump [ option ] --all-databases
   - 전체 Database Backup 진행
   - "--all-databases" 옵션을 사용할 경우 백업대상 테이블에 LOCK 설정을 진행 후 작업을 진행한다 
   - 백업 작업 시 새로운 데이터가 생성되거나 하는 것을 방지하기 위해 LOCK 설정을 진행한다.
   - mysqldump에서는 기본값으로 전체 테이블의 LOCK 설정을 진행 후 dump 작업을 수행
   
# mysqldump -u root -p --all-databases > 20220719-all-backup.sql
   - (20220719-all-backup.sql) 파일로 백업
# ll
합계 1452
-rw-r--r--  1 root root 1476379  7월 19 09:37 20220719-all-backup.sql

# mysql -u root -p < 20220719-all-backup.sql 
   - 백업 다시 적용

# mysqldump -u root -p --databases Sample > 20220719_Sample_Backup.sql
   - Sample 데이터베이스만 백업

# mysqldump -u root -p Sample subject > 20220719_Sample.Subject_Backup.sql
   - Sample 데이터베이스의 subject 테이블만 백업

# ll /var/lib/mysql/Sample 
   - 해당 데이터베이스 테이블 정보

$ Database Replication

   - Database Replication이란, DBMS 복제를 말하며, 같은 데이터를 저장하는 DBMS가 2대이상 존재하는 환경구성을 말 한다.

   - Database Replication를 구성하기 위해서는 최소 2대이상의 DBMS가 필요하며, 각각 Master와 Slave로 구분하여 복제 서비스를 구성한다.

 

$ Master DBMS 역할

   - 웹서버의 데이터 변경 요청을 받아들여, 새로운 데이터를 등록하거나 기존 데이터에 대한 수정, 삭제 작업을 수행

(쓰기 전용 DBMS : insert, delete, update SQL 처리)

   - 웹서버로 부터 생성 된 새로운 데이터 등록 및 기존 데이터에 대한 수정, 삭제 작업에 대한 바이너리로그를 생성 후 Slave 서버로 전달

 

$ Slave DBMS 역할

   - 웹서버의 데이터 요청을 받아들여 데이터를 웹서버에게 전달하는 역할 (읽기 전용 DBMS : select SQL 처리)

   - Master Server로 부터 전달받은 바이너리로그를 자신의 Database 정보로 반영

 

$ Replication 구성 시 주의사항

1. Replication를 사용하는 DBMS는 동일한 DBMS로 사용

2. Replication를 사용하는 DBMS는 동일한 버전이 좋으며, 버전이 다른 경우 Slave 서버가 상위 버전이어야 한다.

3. Replication를 시작시 Master 서버, Slave 서버 순으로 시작

 

$ DB Replication 역할 

1. DB Backup의 기능을 수행

   - Master Server에 저장 된 데이터를 Slave Server에 중복저장하므로, DB Backup의 역할을 수행한다.

 

2. DBMS의 이중화

   - 사용자 데이터 처리작업은 Master Server가 처리, 사용자의 데이터 요청 처리는 Slave Server가 담당한다.

   - 이러한 처리는 실제 WEB Server에 저장되는 WEB Page를 구성하는 Code에서 진행 (PHP, JSP, ASP 등)

 

* WEB Server 이중화 구현은 기본적으로 L4 Switch 장비등을 구현하게 된다. (Load Balancing, FailOver)

* WEB Server 이중화를 구현 할 경우 WEB Server에 대한 부하도를 낮출 수 있지만, DBMS에서 처리하는 부하도를 낮출 수는 없으므로 DB Replication를 함께 구현하는 것이 좋다.

 

DB 이중화 작업

   - Master 192.168.1.100

   - Slave   192.168.1.150

 

$ mariadb 설치 및 비밀번호 설정

   - [ Master / Slave ] 서버 둘 다 진행

# yum -y install mariadb-*

# systemctl start mariadb

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):  
OK, successfully used password, moving on...
   - 현재 mysql 패스워드 입력
   - 처음 설치시 그냥 엔터 

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y 
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
    - mysql root 패스워드를 설정

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!
    - 익명사용자 사용 삭제

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y  
 ... Success!
    - localhost외에 다른 ip에서 root 아이디로 원격접속 가능하게 할지

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y 
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
    -mysql에 기본적으로 설정된 test 디비 삭제 여부

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!
   - root 비번 및 권한 적용
   
Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

   - 모르겠으면 그냥 root 비번만 설정하고 나머지 'Enter'

$ Master DB 시스템 설정 파일

# systemctl stop mariadb
   - 시스템 설정 작업 시 데몬이 켜져 있으면 적용 오류 날 수 있으므로 끄고 작업
   
# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
   - 바이너리 로그 경로
   - 쿼리 수행을 로그로 남기는 것, 로그 백업으로 사용되어 복구로 쓰일 수 있고 
   Replication 사용 시 동기화에 사용
   
server-id=1
   - 이중화를 위한 서버 식별 ID 설정
   
datadir=/var/lib/mysql
   - DB의 데이터가 셍성될 기본 경로
   
socket=/var/lib/mysql/mysql.sock
   - 소켓파일 경로(로컬서버 접속에 사용)
   - 리모트 서버에는 ip와 port를 이용해서 TCP/IP 프로토콜로 접근
   
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
   - 심볼릭 링크 비활성화 
   
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
   - 로그 에러를 기록하는 파일 경로
   
pid-file=/var/run/mariadb/mariadb.pid
   - MariaDB 서버가 자신의 프로세스 ID를 기록하는 파일 경로 
   - 유닉스 혹은 리눅스에서만 사용

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

# firewall-cmd --permanent --add-service=mysql
# firewall-cmd --reload
   - 방화벽 허용
   
# systemctl enable mariadb
# systemctl start mariadb
   - 데몬 재실행

$ Master DB 설정

# mysql -u root -p

MariaDB [(none)]> create database testDB;
   - database 생성

MariaDB [(none)]> grant all privileges on testDB.* to itbank@'%' identified by 'itbank';

MariaDB [(none)]> grant replication slave on *.* to Rep_user@'%' identified by 'itbank';
   - 권한 설정
   
MariaDB [(none)]> flush privileges;
   - 권한, 사용자 계정 정보등이 변경되었을 경우 사용

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      695 |                           |                               |
+------------------+ ----------+--------------+------------------+
MariaDB [(none)]> exit

# mysqldump -u root -p --all-databases > all_Backup.sql
   - 종료 후 데이터베이스 전체백업파일 생성

$ Slave DB 시스템 설정 파일

# systemctl stop mariadb
   - 시스템 설정 작업 시 데몬이 켜져 있으면 적용 오류 날 수 있으므로 끄고 작업
   
# vi /etc/my.cnf

[mysqld]
server-id=2
replicate-do-db='testDB'
   - master쪽에서 복제 할 DB명
   
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

$ Slave DB 설정 

# scp 192.168.1.100:/root/all_Backup.sql ./
   - Master 서버에서 백업 파일 받아오기
   
# systemctl start mariadb
# systemctl enable mariadb

# mysql -u root -p < all_Backup.sql
   - 백업파일 적용
   
# mysql -u root -p
MariaDB [(none)]> change master to
    -> master_host='192.168.1.100',
    -> master_password='itbank',
    -> master_user='Rep_user',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=695;
Query OK, 0 rows affected (0.00 sec)
   - DB 로그인 후 master 서버와 연결 및 통신을 위해 slave 서버가 사용하는 피라미터들을 변경
   
MariaDB [(none)]> exit
# systemctl restart mariadb
# netstat -antp | grep mysqld
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3058/mysqld
tcp        0      0 192.168.1.150:35578     192.168.1.100:3306      ESTABLISHED 3058/mysqld
   - 연동 되있는지 확인
   
#  mysql -u root -p -e "show processlist\G" 
   - 하나의 명령문만보고 바로 빠져나옴 
   - \G : 행단위로 끊어서 보겠다는 의미
   
Enter password:
*************************** 1. row ***************************
      Id: 1
    User: system user
    Host:
      db: NULL
 Command: Connect
    Time: 172
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 2
    User: system user
    Host:
      db: NULL
 Command: Connect
    Time: 172
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 4
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: NULL
    Info: show processlist
Progress: 0.000

#  mysql -u root -p -e "show slave status\G"
Enter password:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: Rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 695
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: testDB
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 695
              Relay_Log_Space: 825
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

$ Master 서버에서 확인

# mysql -u root -p -e "show processlist\G"
Enter password:
*************************** 1. row ***************************
      Id: 4
    User: Rep_user
    Host: 192.168.1.150:35578
      db: NULL
 Command: Binlog Dump
    Time: 109
   State: Master has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 5
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: NULL
    Info: show processlist
Progress: 0.000