binary log file position based replication

개요

• 데이터베이스 변경분을 binarylog에 event로 기록한다.
  • inndb 기준, 1개의 event는 1개의 transaction 이다.
• slave는 master의 binarylog를 읽고 slave의 로컬 데이터베이스에서 binarylog의 event를 실행한다.
• 각 slave는 모든 binarycontents를 받는다. 또한 binarylog에서 어떤 구문을 수행할지에 대해서는 slave에서 결정한다.
  • slave의 i/o thread는 로컬에 binary log를  저장하며 relay log 라고 한다.
  • slave의 sql thread는 relay log 의 내용중에서 약속된 event 만 실행한다.
• event를 특정해서 적용하는것은 불가능 하며 특정 데이터베이스 및 테이블을 선정하는것은 가능하다.
• 각 slave는 현재 진행중인 binarylog의 좌표를 기록한다. 파일이름 위치 등등.
• 하나의 master에 복수개의 slave가 연결될 수 있으며 같은 내용에 대해 서로 다르게 처리될 수 있다. 이것은 slave가 제어하며 master의 작업에는 영향을 주지 않는다.
• 작업이 중단된 경우 추후 재개할때 처리된 부분부터 처리를 재개할 수 있다.
• master와 slave는 unique-id( server-id ) 로 구성되어야 한다.
• master의 정보는 host, log file name, position 등을 파일내에 저장하며 "CHANGE MASTER TO" 구문을 통해 변경이 가능한다.

 

master 설정

 

binary log file posiiton basedreplication 을 하기 위해서는 binary logging 을 활성화하고 unique server-id 를 설정해야 한다.

master의 my.cnf 에 아래와 같이 설정을 한다.

[mysqld]



# log file name

log-bin=mysql-bin

server-id=1



# 내구성을 위해 아래 옵션 사용이 추천된다. 단 성능하락이 올 수 있음

# transaction log

innodb_flush_log_at_trx_commit=1



# binary log

sync_binlog=1





# disable, accept connection from only unix socket

# skip-networking

replication 사용자 생성

각 slave 에서는 master 노드에 mysql 계정을 통해 접속을 하며 그 계정은 replication slave 권한을 가지고 있어야 한다.

mysql> CREATE USER 'test'@'%' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';

mysql> flush privileges;

 

master binary log 파일 및 position얻기

slave 에서 replication 을 시작하기 위해서는 master binary log file 및 position 을 얻어야 한다. 두가지 방법을 생각할 수 있다.

첫번째 방법은 데이터에 준비가 이미 끝났다면 'show master status' 쿼리를 실행한다.

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000006 |      154 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 

두번째 장법은 write session 을 모두 종료하고 mysqldump 등의 백업을 통해 binary log 파일 및 position 정보를 얻고 백업을 이용하여 slave 의 초기 데이터를 적재한 이후 replication 을 시작한다.

$ ./bin/mysqldump --all-databases --opt --triggers --routines --master-data=2 --single-transaction --default-character-set=utf8 --passwd=test --user=test > backup.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

$ cat backup.sql | grep CHANGE

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=154;

 

 

data snapshot 을 선택하기

replication 을 시작하는 시점에서 schema object 는 전부 생성되어 있지만 데이터가 없다면 skip 해도되는 단계이다.  데이터가 있다면 replication 을 하기 위해서는 초기데이터 적재가 필요하다. 

 

mysqldump 

dump 하기에 앞서 mysqldump 를 수행하는 중간에 데이터베이스가 변경되면 안되기 때문에 모든 table 의 데이터를 flush 하고 관련 transaction 을 강제 종료한 이후에 read lock 잡는 과정이 필요하다.

(session1)

mysql> flush tables with read lock;

쿼리 수행이후 session 을 종료하면 lock 이 풀리기 때문에 session 을 유지한다.
다음은 mysqldump 를 이용하여 모든 데이터를 sql 파일에 기록한다.

(session2)

$ mysqldump --all-databases --master-data=2 --user=test --password=test --host=127.0.0.1 \

  --single-transaction --flush-privileges \

  --add-drop-database --add-drop-table --add-drop-trigger --all-tablespaces --routines --complete-insert --triggers> alldump.sql



(이상하게 '\' 를 이용하여 개행된 명령어 사용이 안됨... 한줄로 고쳐서 실행할 것을 추천)





파일의 내용중 '-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=6604;' 는 별도로 기록해둔다.

혹시 위 내용을 일괄적용하려 한다면 '--master-data=2' 를 '--master-data' 로 변경하면 주석이 없어져 바로 적용가능하다.

 

dump가 끝나고 생성된 alldump.sql 파일을 slave 서버에 적용한다.

$ mysql --user=root --password=root --host=127.0.0.1 < alldump.sql 

mysql: [Warning] Using a password on the command line interface can be insecure.

chlee@dev2:~/mysql

$ mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.7.27-log MySQL Community Server (GPL)



Copyright (c) 2000, 2019, 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> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

| test               |

+--------------------+

5 rows in set (0.00 sec)



mysql> use test

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_test |

+----------------+

| n1             |

| n2             |

| n3             |

| n4             |

| n5             |

| n6             |

+----------------+

6 rows in set (0.00 sec)



mysql>

 

모두 성공적으로 끝났다면 session1 에서 lock 을 해제한다.

(session1)

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

 

coldbackup

slave 가 설치되어 있지않고 mysql에 저장된 데이터가 많다면 mysqldump 에 비해 효율적인 방법이다. 

mysql 의 '--datadir' 경로의 모든 데이터를 slave 의 '--datadir' 경로에 복사한 후 slave 를 기동한다.

하지만 'server-id' 의 값을 다르게 설정하도록 한다.

 

mysql master 서버를 shutdown 한다. 

$ $MYSQL_HOME/bin/mysqladmin -h 127.0.0.1 -uroot -p shutdown

root 의 password 는 'mysqld --initialize ' 혹은 이후 'alter user root@... ' 에서 확인한다.

 

 

# 압축하기

$ tar cvfz $mysql_data.tar.gz $MYSQL_HOME/data





# datadir 은 다음 쿼리로 확인한다.

mysql> show variables like '%datadir%';

+---------------+-------------------------+

| Variable_name | Value                   |

+---------------+-------------------------+

| datadir       | /home/chlee/mysql/data/ |

+---------------+-------------------------+

1 row in set (0.01 sec)

 

slave 설정

replication 을 위한 slave 설정은 데이터의 초기 적재가 모든 끝나고 master 와 slave 의 데이터가 동일하거나 적어도 schema object 가 같다는 전재한다.

물론 앞서 기술된 과정모두를 성곡적으로 수행 했다고 가정한다. 다음은 my.cnf 설정이다.

 

[mysqld]



server-id=2

 

slave 서버를 기동한다.

$ $MYSQL_HOME/bin/mysqld_safe --defaults-file=$MYSQL_HOME/etc/my.cnf --user=chlee &

chlee@dev2:~/mysql

$ 2019-08-06T10:41:28.706317Z mysqld_safe Logging to '/home/chlee/mysql/data/dev2.err'.

2019-08-06T10:41:28.725560Z mysqld_safe Starting mysqld daemon with databases from /home/chlee/mysql/data

slave 에 접속하여 다음 작업을 수행한다.

mysql>  CHANGE MASTER TO

    ->     MASTER_HOST='192.168.56.3',

    ->     MASTER_USER='test',

    ->     MASTER_PASSWORD='test',

    ->     MASTER_LOG_FILE='mysql-bin.000001',

    ->     MASTER_LOG_POS=6604;

Query OK, 0 rows affected, 2 warnings (0.02 sec)





mysql> start slave

    -> ;

Query OK, 0 rows affected (0.00 sec)



mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.3

                  Master_User: test

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 6604

               Relay_Log_File: dev2-relay-bin.000002

                Relay_Log_Pos: 320

        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: 6604

              Relay_Log_Space: 526

              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: dc8b732e-b825-11e9-ab35-080027bb6b82

             Master_Info_File: /home/chlee/mysql/data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave 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:

1 row in set (0.00 sec)

 

"Slave_IO_Running: Yes" , "Slave_SQL_Running: Yes" 를 확인했다면 일단 성공한 셈이다.

 

이제 master 에서의 변경이 slave 에서도 반영되는지 확인해 보자.

 

(master)

mysql> use test;

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> create table t1( i1 int );

Query OK, 0 rows affected (0.01 sec)



mysql> commit;

Query OK, 0 rows affected (0.00 sec)



mysql>





(slave)

mysql> use test;

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_test |

+----------------+

| n1             |

| n2             |

| n3             |

| n4             |

| n5             |

| n6             |

| t1             |

+----------------+

7 rows in set (0.00 sec)



mysql>

table t1 을 master 생성했을때 slave 에도 생성되는것을 볼 수 있다.