Back end

MySQL DB복제

giljabi 2024. 11. 16. 22:43

윈도우 환경에서 데이터베이스 설치, 복제 설정

Mysql8.0.39 database replication

  • master, slave 서버 구성 후 master입력이 slave로 복제되는지 확인
  • slave를 readonly 설정 후 master 장애시 slave에 접속해서 서비스
  • download https://dev.mysql.com/downloads/mysql/
  • master: 3306, slave:3307

mysql master install

X:\home\mysql>mkdir master
X:\home\mysql>tar -xvf mysql-8.0.39-winx64.zip -C master
X:\home\mysql>master\bin\mysqld --initialize-insecure --user=mysql --basedir=\home\mysql\master --datadir=\home\mysql\master\data
X:\home\mysql>del master\data\auto.cnf # 없으면 자동 생성됨, uuid는 중복불가

mysql master my.ini 파일 생성

  • x:\home\mysql\master\my.ini
  • utf8mb4로 설정(이모지폰트도 저장가능: 한글,English, 똠방각하, 😀🚚)
[client]
default-character-set = utf8mb4
port=3306

[mysql] 
default-character-set = utf8mb4 

[mysqldump] 
default-character-set = utf8mb4

[mysqld]
port=3306
basedir=X:/home/mysql/master/
datadir=X:/home/mysql/master/data

character-set-client-handshake = FALSE 
init_connect="SET collation_connection = utf8mb4_general_ci" 
init_connect="SET NAMES utf8mb4" 
character-set-server = utf8mb4 

# Master 서버의 고유 ID. 1로 설정, 기본값이 1로 설정되어 있음, slave는 2로 설정
server-id=1
# 바이너리 로그 활성화
log-bin=mysql-bin
# 복제 형식은 ROW로 설정
binlog-format=ROW
  • 서비스 등록
X:\home\mysql\master\bin>mysqld --install mysqlmaster
Service successfully installed.
X:\home\mysql\master\bin>net start mysqlmaster

rem X:\home\mysql\master\bin>mysqld --remove mysqlmaster
  • 복제사용자 생성
X:\home\mysql\master\bin>mysql -u root
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'replpassword';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql slave install

X:\home\mysql>mkdir slave
X:\home\mysql>tar -xvf mysql-8.0.39-winx64.zip -C slave
X:\home\mysql>slave\bin\mysqld --initialize-insecure --user=mysql --basedir=\home\mysql\slave --datadir=\home\mysql\slave\data
X:\home\mysql>del slave\data\auto.cnf

mysql slave my.ini 파일 생성

  • x:\home\mysql\slave\my.ini
[client]
default-character-set = utf8mb4
port=3307

[mysql] 
default-character-set = utf8mb4 

[mysqldump] 
default-character-set = utf8mb4

[mysqld]
port=3307
basedir=X:/home/mysql/slave/
datadir=X:/home/mysql/slave/data

character-set-client-handshake = FALSE 
init_connect="SET collation_connection = utf8mb4_general_ci" 
init_connect="SET NAMES utf8mb4" 
character-set-server = utf8mb4 

# Master 서버의 고유 ID. 1로 설정, 기본값이 1로 설정되어 있음, slave는 2로 설정
server-id=2
# 바이너리 로그 활성화
log-bin=mysql-bin
# 복제 형식은 ROW로 설정
binlog-format=ROW

# read_only
read_only = 1
# super user read_only
super_read_only = 1
  • 서비스 등록
X:\home\mysql\slave\bin>mysqld --install mysqlslave
Service successfully installed.
X:\home\mysql\slave\bin>net start mysqlslave

rem X:\home\mysql\slave\bin>mysqld --remove mysqlslave

mysql slave replication 설정

  • slave replication 계정, 시작위치 지정
# File, Position을 master와 동일하게 변경한다.
X:\home\mysql\slave\bin>mysql -u root
mysql> change master to master_host='localhost', master_user='repl', master_password='replpassword', master_log_file='mysql-bin.000001', master_log_pos=157;
  • slave db 로그1
2024-09-26T01:22:29.484950Z 8 [System] [MY-010597] [Repl] 'CHANGE REPLICATION SOURCE TO FOR CHANNEL '' executed'. 
Previous state source_host='', source_port= 3306, source_log_file='', source_log_pos= 4, source_bind=''. 
New state source_host='localhost', source_port= 3306, source_log_file='mysql-bin.000001', source_log_pos= 157, source_bind=''.
  • master db, table 생성
X:\home\mysql\master\bin>mysql -u root
mysql> create database sampledb;
Query OK, 1 row affected (0.01 sec)

mysql> use sampledb;
Database changed
mysql> CREATE TABLE test (id INTEGER, name VARCHAR(100));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test(id, name) values(1, 'Hello');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test(id, name) values(2, '한글,English, 똠방각하, 😀🚚');
Query OK, 1 row affected (0.01 sec)
  • slave replication start
mysql> START SLAVE;
  • slave db 로그2
2024-09-26T01:24:54.464244Z 9 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection 
metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection 
options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.

2024-09-26T01:24:54.485469Z 9 [System] [MY-014001] [Repl] Replica receiver thread for channel '': 
connected to source 'repl@localhost:3306' with server_uuid=3879b347-7b9f-11ef-bc88-8cb0e9db0317, server_id=1. 
Starting replication from file 'mysql-bin.000001', position '157'.
  • slave status 확인
# 아래 2개 Running이 Yes이면 정상
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: localhost
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 874
               Relay_Log_File: SAMSUNG-G-relay-bin.000002
                Relay_Log_Pos: 1043
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 874
              Relay_Log_Space: 1257
              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_UUID: 3879b347-7b9f-11ef-bc88-8cb0e9db0317
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

# readonly 확인
mysql> insert into test(id, name) values(2, 'Hello');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

# 이모지폰트도 이상없이 복제됨
mysql> select * from test;
+------+----------------------------------------+
| id   | name                                   |
+------+----------------------------------------+
|    1 | Hello                                  |
|    2 | 한글,English, 똠방각하, 😀🚚               |
+------+----------------------------------------+
2 rows in set (0.00 sec)