MySQL - MySQL Replica with GTID
6 minute read
I simply ran MySQL as a container and implemented replicas.
1. Write Docker Compose File
version: '3.1'
services:
master:
image: mysql:8.0
container_name: mysql_master
environment:
MYSQL_ROOT_PASSWORD: rootpassword
MYSQL_DATABASE: testdb
ports:
- "3306:3306"
command: --server-id=1 --gtid-mode=ON --enforce-gtid-consistency=ON --log-bin=mysql-bin --binlog-format=ROW --default-authentication-plugin=mysql_native_password
slave:
image: mysql:8.0
container_name: mysql_slave
environment:
MYSQL_ROOT_PASSWORD: rootpassword
MYSQL_DATABASE: testdb
ports:
- "3307:3306"
command: --server-id=2 --gtid-mode=ON --enforce-gtid-consistency=ON --log-bin=mysql-bin --binlog-format=ROW --default-authentication-plugin=mysql_native_password
2. Run Docker Container
docker-compose up -d
3. Set Master(Source)
connect master db.
docker exec -it mysql_master mysql -uroot -prootpassword
create replication user and grant permission
CREATE USER 'replica_user'@'%' IDENTIFIED WITH mysql_native_password BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;
-- check Master status
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 883 | | | ebf8005f-292f-11ef-9015-0242c0a8a703:1-9 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
remenber file(mysql-bin.000003) and position(883).
4. Set Slave(replica)
connect slave db.
docker exec -it mysql_slave mysql -uroot -prootpassword
master settings in slave.
CHANGE MASTER TO MASTER_HOST='mysql_master', MASTER_USER='replica_user', MASTER_PASSWORD='replica_password', MASTER_AUTO_POSITION=1;
START SLAVE;
-- check Slave status
SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: mysql_master
Master_User: replica_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003 <--- master file
Read_Master_Log_Pos: 1444
Relay_Log_File: d41561b21952-relay-bin.000003
Relay_Log_Pos: 1660
Relay_Master_Log_File: mysql-bin.000003
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: 1444
Relay_Log_Space: 3042467
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: ebf8005f-292f-11ef-9015-0242c0a8a703
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: ebf8005f-292f-11ef-9015-0242c0a8a703:1-11
Executed_Gtid_Set: ebf8005b-292f-11ef-ae61-0242c0a8a702:1-6,
ebf8005f-292f-11ef-9015-0242c0a8a703:1-11
Auto_Position: 1
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)
5. Test
# on Master
USE testdb;
CREATE TABLE test_table (id INT PRIMARY KEY, data VARCHAR(100));
INSERT INTO test_table (id, data) VALUES (1, 'Hello World'), (2, 'Replication Test');
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test_table |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test_table;
+----+------------------+
| id | data |
+----+------------------+
| 1 | Hello World |
| 2 | Replication Test |
+----+------------------+
2 rows in set (0.00 sec)
# on Slave
USE testdb;
SELECT * FROM test_table;
mysql> SELECT * FROM test_table;
+----+------------------+
| id | data |
+----+------------------+
| 1 | Hello World |
| 2 | Replication Test |
+----+------------------+
2 rows in set (0.00 sec)
Issue
Last_IO_Error: Error connecting to source 'replica_user@master:3306'. This was attempt 2/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.`
- MySQL 8.0에서 기본 인증 플러그인 'caching_sha2_password'를 사용시 보안 연결을 요구하여 발생
- SSL을 사용한 보안 연결을 설정하여 해결
- 여기서는 로컬에서 간단한 실습을 위해 `mysql_native_password`를 사용
Last_IO_Error: Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it).`
- Master와 Slave의 MySQL 서버 ID가 동일하여 발생
- 각 서버가 고유한 서버 ID를 가져야 함
- docker compose에서 각 서버에 고유한 서버ID를 할당
- Master의 Postion 정보를 사용하지 않는 이유
MASTER_AUTO_POSITION을 활성화하면 GTID를 사용하여 자동으로 위치를 추적하고 복제를 설정한다. 그러면 file과 position을 수동으로 설정할 필요가 없다.
binlog position 기반이라면 file과 position을 명시적으로 설정해주어야 한다.
I feedback.
Let me know what you think of this article in the comment section below!
Let me know what you think of this article in the comment section below!
comments powered by Disqus