Delayed Replication

Delayed replication introduces a lag in the SQL thread execution after data is written to the relay log. Recommended delay is typically 3-6 hours, depending on operational response time requirements.

Key benefits:

• Mitigates physical corruption risks
• Standard replication doesn't address logical corruption

Configuration (execute on slave):

STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY = 300;
START SLAVE;
SHOW SLAVE STATUS \G

Recovery Process for Delayed Replication

1. Detect logical database failure
2. Halt SQL thread and note replay position:

STOP SLAVE SQL_THREAD;
SHOW SLAVE STATUS \G

3. Extract relay log between start position and pre-error event
4. Replay extracted events on slave
5. Recovery options:
   • Promote slave as new master (single database)
   • Export/import affected database to master (multi-database)

Filtered Replication

Configure slaves to replicate specific databases only:

Method 1: Master configuration (rarely used)

binlog_do_db=  # Whitelist
binlog_ignore_db= # Blacklist

Method 2: Slave configuration

replicate_do_db=repl

Semi-Synchronous Replication

Ensures data consistency between master and atleast one slave before committing transactions.

Configuration:

-- Master
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Slave
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

GTID Replication Setup

Global Transaction Identifiers provide transaction-level tracking:

Core parameters:

gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

Configuration steps:

1. Clean environment on all nodes
2. Prepare configuration files with unique server IDs
3. Initialize data
4. Start database services
5. Configure replication:

-- Master
GRANT REPLICATION SLAVE ON *.* TO repl@'10.0.0.%' IDENTIFIED BY '123';

-- Slaves
CHANGE MASTER TO 
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_AUTO_POSITION=1;
START SLAVE;

MHA Environment Configuration

1. Create symbolic links for MySQL binaries
2. Configure SSH key-based authentication between nodes
3. Install required packages on all nodes:

yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

4. Create MHA user on master:

GRANT ALL PRIVILEGES ON *.* TO mha@'10.0.0.%' IDENTIFIED BY 'mha';

5. Install manager software on designated node
6. Prepare MHA configuration file:

[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/binlog
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root

[server1]
hostname=10.0.0.51
port=3306

[server2]
hostname=10.0.0.52
port=3306

[server3]
hostname=10.0.0.53
port=3306

7. Verify configuration:

masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf

8. Start MHA service:

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf \
  --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &