MySQL 복제 기초

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

mysqldump --single-transaction --source-data=1 --all-databases \
  -u root -p > source_snapshot.sql

[mysqld]
server_id = 2

mysql -u root -p < source_snapshot.sql

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = '192.168.1.10',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'repl_password',
    SOURCE_AUTO_POSITION = 1;

START REPLICA;

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

SHOW REPLICA STATUS\G

SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';

-- Application connects to replica for reads
SELECT * FROM orders WHERE customer_id = 42;

-- Connect to source for writes
INSERT INTO orders (customer_id, total) VALUES (42, 99.99);

STOP REPLICA;           -- stop both threads
STOP REPLICA IO_THREAD; -- stop only the I/O thread
STOP REPLICA SQL_THREAD; -- stop only the SQL thread

START REPLICA;

-- GTID mode: inject an empty transaction for the failed GTID
SET GTID_NEXT = 'source_uuid:N';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;