MySQL 5.6 gtid replication configuration and failover simple test
mysql 5.6 gtid replication 구성
테스트할땐 역시 mysqld_multi가 편리하네요.
1. 일단 바이너리 엔진 준비해주고..원하는 위치에 두고 심볼릭 링크 생성
/db/multi/mysql-5.6.19-linux-glibc2.5-x86_64/
ln -s mysql-5.6.19-linux-glibc2.5-x86_64/ 5.6
2. configuration my.cnf 수정
- log-bin # 필수
- log-slave-updates # 필수
- gtid-mode=on # GTID 모드
- enforce-gtid-consistency # 여기까지 필수. transactionally safe하지 않는 애는 실패한다. 나중에 자세히 살펴봅시다.
- master-info-repository=TABLE # master.info File로 관리하던거 mysql.slave_master_info 테이블에 담음
- relay-log-info-repository=TABLE # relay-log.info File로 관리하던거 mysql.slave_relay_log_info테이블에 담음
- slave-parallel-workers=2 # slave parallel-worker 사용가능. worker정보는 mysql.slave_worker_info에서 확인가능
[mysqld1]
datadir=/data1/multi/5.6/data1
log-error=multi_mysql56m1.err
port = 3366
socket = /tmp/mysql56m1.sock
pid-file = /data1/multi/5.6/data1/mysql56m2.pid
log-bin=mysql56m1-bin
binlog_format=mixed
server-id = 561
expire_logs_days=30
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
[mysqld2]
datadir=/data1/multi/5.6/data2
log-error=multi_mysql56m2.err
port = 3367
socket = /tmp/mysql56m2.sock
pid-file = /data1/multi/5.6/data2/mysql56m2.pid
log-bin=mysql56m2-bin
binlog_format=mixed
server-id = 562
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
slave-parallel-workers=2
3. multi instance들이 사용할 data directory 생성
mkdir -p /data1/multi/5.6/data1
mkdir -p /data1/multi/5.6/data2
/db/multi/5.6/scripts/mysql_install_db --user=mysql --basedir=/db/multi/5.6 --datadir=/data1/multi/5.6/data1
/db/multi/5.6/scripts/mysql_install_db --user=mysql --basedir=/db/multi/5.6 --datadir=/data1/multi/5.6/data2
4. mysql instance 시작
./bin/mysqld_multi --defaults-file=/db/multi/5.6/conf/my.cnf start
./bin/mysqld_multi --defaults-file=/db/multi/5.6/conf/my.cnf stop
5. @MASTER replication user 생성
- @MASTER
mysql> GRANT REPLICATION SLAVE ON *.* TO replication@127.0.0.1 IDENTIFIED BY 'replication';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> show master statusG
*************************** 1. row ***************************
File: mysql56m1-bin.000002
Position: 549
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 141c523b-4747-11e4-98fb-000c298227a2:1-2
1 row in set (0.00 sec)
mysql>
6. Replication 구성
- 6.1 @SLAVE
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT = 3366, MASTER_USER='replication', MASTER_PASSWORD='replication', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: replication
Master_Port: 3366
Connect_Retry: 60
Master_Log_File: mysql56m1-bin.000001
Read_Master_Log_Pos: 539
Relay_Log_File: mysql56m2-relay-bin.000002
Relay_Log_Pos: 757
Relay_Master_Log_File: mysql56m1-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: 549
Relay_Log_Space: 975
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: 561
Master_UUID: 141c523b-4747-11e4-98fb-000c298227a2
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 141c523b-4747-11e4-98fb-000c298227a2:1-2
Executed_Gtid_Set: 141c523b-4747-11e4-98fb-000c298227a2:1-2
Auto_Position: 1
1 row in set (0.00 sec)
mysql>
- 6.2 @MASTER show slave hots;
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 562 | | 3367 | 561 | 8e95986b-4747-11e4-98fe-000c298227a2 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
7. 리플리케이션 동작 확인
- 7.1 @MASTER
mysql> show master statusG
*************************** 1. row ***************************
File: mysql56m1-bin.000001
Position: 539
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 141c523b-4747-11e4-98fb-000c298227a2:1-2
1 row in set (0.00 sec)
mysql> create database reptest;
Query OK, 1 row affected (0.00 sec)
mysql> use reptest;
Database changed
mysql> CREATE TABLE `tt` ( `no` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`no`) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into tt(name) values ('hihi');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tt;
+----+------+
| no | name |
+----+------+
| 1 | hihi |
+----+------+
1 row in set (0.00 sec)
mysql> show master statusG
*************************** 1. row ***************************
File: mysql56m1-bin.000001
Position: 1262
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 141c523b-4747-11e4-98fb-000c298227a2:1-5
1 row in set (0.00 sec)
- 141c523b-4747-11e4-98fb-000c298227a2:1-5
- 정확하게 트랜잭션 개수만큼 gtid 가 증가한다. 지금까지 5개. (grant, flush, create databse, create table, insert)
- 7.2 @SLAVE
mysql> show slave statusG
...
Retrieved_Gtid_Set: 141c523b-4747-11e4-98fb-000c298227a2:1-5
Executed_Gtid_Set: 141c523b-4747-11e4-98fb-000c298227a2:1-5
Auto_Position: 1
1 row in set (0.00 sec)
mysql> select * from reptest.tt;
+----+------+
| no | name |
+----+------+
| 1 | hihi |
+----+------+
1 row in set (0.00 sec)
8. Failover 시나리오 간단히 테스트해보자.
- 8.1. MASTER CRASHED
[mysql@myvm1 5.6]$ kill -9 30829 30551
- 8.2. SLAVE -> new MASTER reset slave all
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 127.0.0.1
Master_User: replication
Master_Port: 3366
Connect_Retry: 60
Master_Log_File: mysql56m1-bin.000001
Read_Master_Log_Pos: 1262
Relay_Log_File: mysql56m2-relay-bin.000002
Relay_Log_Pos: 757
Relay_Master_Log_File: mysql56m1-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes