MySQL Replication
Replication:
❖ Enables data copy from source server to destination server
❖ Source is called Primary or Master
❖ Destination is called Replica or Slave
❖ Primary and Replica should be on separate servers
❖ Replication is Asynchronous by default
❖ Replica does not need to be connected to Primary at any given
time
❖ Replication Selection - All databases, selected, or even selected
tables
❖ Replication provides high-availability
MySQL Replication
Replication Methods:
❖ Traditional - binary log file position based replication
❖ GTID - Global Transaction Identifier
Binary Log File Position Based Replication:
❖ Replicating events from primary binary log file
❖ Requires the log files and positions to be synced between primary and
replica
GTID Based Replication:
❖ Newer method, does not require working with binary logs and positions
within files
❖ GTID replication guarantee consistency between primary and replica as long
MySQL Replication
Replication Format:
❖ Replication works because events written to the binlog are read from source and
then processed on replica
❖ Events are recorded in the Binary Log File in different formats according to the
type of event
❖ binlog_format is the system global variable that defines which format to use
❖ Statement Based Replication - SBR
➢ binlog_format = STATEMENT
❖ Row Based Replication - RBR
➢ binlog_format = ROW
❖ Mixed Based Replication - MBR
➢ binlog_format = MIXED
❖ Each binary log format has advantages and disadvantages
MySQL Replication
Statement Based Replication: binlog_format = STATEMENT
❖ Replicate entire SQL statements in binary file, this file is copied over to replica
❖ Replica execute all these SQL statements
❖ Less data written to log files, consumes less storage space for log files
❖ Not all statements are replicated as any non-deterministic behavior is difficult to replicate
Row Based Replication: binlog_format = ROW
❖ Replicate only the changed rows
❖ Source write events in binary log that indicates how individual tables rows are changed
❖ All changes are replicated, consume more space
❖ You can’t see which statement was received and executed on replica
Mixed Based Replication: binlog_format = MIXED
❖ Provides best combination of data integrity and performance
❖ Source write events in binary log that indicates how individual tables rows are changed
❖ Recommended format in most cases
MySQL Replication
Generic Replication Setup Requirements:
❖ Binary Logging must be enabled on primary
➢ log-bin
➢ log-bin-index
❖ Unique Server ID for both Primary & Replica
➢ server-id
❖ Dedicated user for replication
➢ replicator or any other name
➢ Should have proper permissions
■ GRANT REPLICATION SLAVE ON *.* TO replicator
❖ Binary Log File Format
➢ binlog_format = STATEMENT|ROW|MIXED
MySQL Replication
Primary Server Setup:
❖ Suggested to create a separate option file i.e [Link]
❖ Enable Binary Logging
➢ log-bin = /var/log/mysql/binlog/prod-bin
➢ log-bin-index = /var/log/mysql/binlog/[Link]
❖ Set Unique Server ID
➢ server-id = 1
❖ Create Dedicated Replication User
➢ Create user replicator IDENTIFIED BY ‘password’ ;
➢ GRANT REPLICATION SLAVE ON *.* TO replicator;
❖ Set Binary Log File Format
➢ binlog_format = MIXED
MySQL Backup & Recovery
ASSIGNMENT: Prepare Primary Server for Replication
ASSIGNEE: Bob, The Intermediate DBA
MySQL Replication
Replica Server Setup:
❖ Suggested to create a separate option file i.e [Link]
❖ Enable Relay Logs
➢ relay-log = /var/log/mysql/relay/replica-bin
➢ relay-log-index = /var/log/mysql/relay/[Link]
❖ Set Unique Server ID
➢ server-id = 2
❖ Skip Replica to auto-start
➢ skip-replica-start
❖ Replica should be read only
➢ read-only
MySQL Backup & Recovery
ASSIGNMENT: Prepare Replica Server for Replication
ASSIGNEE: Bob, The Intermediate DBA
MySQL Replication
Replication Configuration:
❖ Get Binary Log File and Position information - from xtrabackup
❖ Point to new Primary
➢ CHANGE MASTER STATEMENT
❖ Start Replica
➢ START REPLICA
❖ Verify
➢ SHOW REPLICA STATUS
MySQL Backup & Recovery
ASSIGNMENT: Configure Binary Log Position Based Replication
ASSIGNEE: Bob, The Intermediate DBA
MySQL Replication
GTID Replication Requirements:
❖ Both Primary & Replica should have GTID mode on
➢ gtid-mode = ON
❖ Both Primary & Replica should enforce the GTID consistency
➢ enforce-gtid-consistency = ON
MySQL Replication
Traditional Replication:
❖ UNTIL Condition
❖ MASTER_LOG_FILE
❖ MASTER_LOG_POSITION
GTID Replication:
❖ UNTIL Condition
❖ SQL_BEFORE_GTIDS
❖ SQL_AFTER_GTIDS
MySQL Backup & Recovery
ASSIGNMENT: Perform UNTIL CONDITION With GTID
ASSIGNEE: Bob, The Intermediate DBA
HINT:
START REPLICA UNTIL SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS
MySQL Replication
Primary-Replica Failover:
❖ Replica will start in read-write mode
❖ Replica will have binary logging enabled
❖ Reset Replica to delete all the relay log files
❖ Configure Primary to become Replica of new-primary
❖ Configure relay logs for new-replica
❖ Reset Master to start binary logging - optional
❖ Start new-replica in read-only mode
❖ GTID Replication Based Failover
BOB PROGRESS
CHECKPOINT
Bob’s Knowledge Gain:
❖ Replication Methods
❖ Replication Format
❖ General Replication Requirements
❖ Primary Server Requirements
❖ Replica Server Requirements
❖ GTID Replication
❖ Binary Log Position Based Replication
❖ SQL_THREAD
❖ IO_THREAD