Replication Setup in MySQL

-- On source server
-- Edit my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

-- Create replication user
CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

-- Get binary log status
SHOW BINARY LOG STATUS;
-- Note File and Position

-- On replica server
-- Edit my.cnf
[mysqld]
server-id=2

-- Configure replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source_ip',
  SOURCE_USER='replica',
  SOURCE_PASSWORD='password',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=154;

-- Start replication
START REPLICA;
SHOW REPLICA STATUS\G

-- DETAILED_TAB:
-- Check replication lag
SHOW REPLICA STATUS\G
-- Look for Seconds_Behind_Source

-- Skip replication errors
STOP REPLICA;
SET GLOBAL SQL_REPLICA_SKIP_COUNTER = 1;
START REPLICA;

-- Reset replication
STOP REPLICA;
RESET REPLICA ALL;

-- Semi-synchronous replication
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

-- Read from replica, write to source pattern
-- Application logic:
-- Writes: connect to source
-- Reads: connect to replica (load balance)