replication 관련 명령어 모음

리플리케이션에 사용되는 명령어 정리

MASTER

-- master / slave 구분
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

--  master 상태 확인
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      155 | test         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

-- master 의 binary log 를 purge 시킨다.
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

-- 데이터 플러쉬
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

SLAVE

-- slave 시작
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

-- slave 중지
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

-- slave 리셋 (replication 을 위한 position 정보를 삭제)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

-- master 의 채널 정보까지 삭제
mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status;
Empty set (0.00 sec)

-- master 채널 정보 지정
mysql> CHANGE MASTER TO
    ->      MASTER_HOST = '10.1.5.11',
    ->      MASTER_PORT = 3306,
    ->      MASTER_USER = 'test',
    ->      MASTER_PASSWORD = 'test',
    ->      MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.1.5.11
                  Master_User: test
                  Master_Port: 3306
... ...