When expanding MySQL replication topology, we often face a dilemma: creating new slaves from existing slaves while maintaining direct master connection. The standard approach using FLUSH TABLES WITH READ LOCK causes unacceptable downtime for busy systems.

For MySQL 5.6+ with GTID enabled, this becomes significantly easier:

# On slave B:
STOP SLAVE;
SHOW SLAVE STATUS\G
# Note Retrieved_Gtid_Set and Executed_Gtid_Set values

# Create backup without locking:
xtrabackup --backup --slave-info --target-dir=/path/to/backup
xtrabackup --prepare --target-dir=/path/to/backup

# On new slave C:
systemctl stop mysql
mv /var/lib/mysql /var/lib/mysql.old
xtrabackup --copy-back --target-dir=/path/to/backup
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

# Configure replication:
mysql> CHANGE MASTER TO
       MASTER_HOST='master_A',
       MASTER_USER='repl_user',
       MASTER_PASSWORD='password',
       MASTER_AUTO_POSITION=1;
mysql> START SLAVE;

For pre-5.6 versions or without GTID:

# On slave B:
FLUSH TABLES WITH READ LOCK;
SHOW SLAVE STATUS\G
# Note Master_Log_File and Read_Master_Log_Pos
# In another session:
xtrabackup --backup --slave-info --target-dir=/path/to/backup
UNLOCK TABLES;

# Prepare backup:
xtrabackup --prepare --target-dir=/path/to/backup

# On new slave C (same filesystem steps as above)

# Configure replication:
mysql> CHANGE MASTER TO
       MASTER_HOST='master_A',
       MASTER_USER='repl_user',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000123',
       MASTER_LOG_POS=456789;
mysql> START SLAVE;

For automation in production environments, consider this bash script approach:

#!/bin/bash
# On slave B:
MYSQL_USER="backup_user"
MYSQL_PASS="secure_password"
BACKUP_DIR="/path/to/backup"

# Get replication info before lock
MASTER_INFO=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep -E "Master_Log_File|Read_Master_Log_Pos")
LOG_FILE=$(echo "$MASTER_INFO" | grep Master_Log_File | awk '{print $2}')
LOG_POS=$(echo "$MASTER_INFO" | grep Read_Master_Log_Pos | awk '{print $2}')

# Brief lock for consistency
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "FLUSH TABLES WITH READ LOCK; SYSTEM /usr/bin/xtrabackup --backup --slave-info --target-dir=$BACKUP_DIR; UNLOCK TABLES;"

# Prepare backup (can be done on target server)
xtrabackup --prepare --target-dir=$BACKUP_DIR

echo "Replication coordinates for master setup:"
echo "MASTER_LOG_FILE='$LOG_FILE'"
echo "MASTER_LOG_POS=$LOG_POS"

After setup, verify replication status:

mysql> SHOW SLAVE STATUS\G
# Check for:
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Seconds_Behind_Master: 0 or decreasing

When setting up MySQL replication in a multi-tier environment, a common scenario involves creating new slaves (replica C) from existing slaves (replica B) while maintaining direct master (server A) connections. The primary challenge arises when the master database is production-critical and cannot tolerate extended downtime for backup operations.

The most efficient approach combines XtraBackup with proper coordination of replication coordinates:

# On slave B (source slave):
1. Run XtraBackup without locking (use --safe-slave-backup):
   xtrabackup --backup --slave-info --safe-slave-backup --target-dir=/path/to/backup

2. Prepare the backup:
   xtrabackup --prepare --target-dir=/path/to/backup

# On new slave C:
1. Copy and restore the backup:
   rsync -avz /path/to/backup/ user@C:/var/lib/mysql/

2. Extract replication coordinates:
   cat /var/lib/mysql/xtrabackup_binlog_info
   # Output format: master-bin.000123 10785623

3. Configure replication to master A:
   CHANGE MASTER TO
     MASTER_HOST='A',
     MASTER_USER='repl_user',
     MASTER_PASSWORD='password',
     MASTER_LOG_FILE='master-bin.000123',
     MASTER_LOG_POS=10785623;

For environments where XtraBackup isn't available, consider these options:

MySQL Clone Plugin (5.7.17+)

# On slave C:
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

# On slave B:
SET GLOBAL clone_valid_donor_list = 'B:3306';

# On slave C:
CLONE INSTANCE FROM 'repl_user'@'B':3306
IDENTIFIED BY 'password';

GTID-Based Replication

When using GTIDs, the process simplifies significantly:

# On slave C after restoring data:
CHANGE MASTER TO
  MASTER_HOST='A',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;

• Use --compress with XtraBackup for large databases
• Consider network bandwidth limitations when transferring backups
• Monitor slave lag during initial sync
• Adjust slave_parallel_workers for faster replication

For frequent slave creation, implement a script like:

#!/bin/bash
# Automated slave provisioning script
SOURCE_SLAVE="B"
MASTER="A"
BACKUP_DIR="/backups"
NEW_SLAVE="C"

# Create backup
ssh $SOURCE_SLAVE "xtrabackup --backup --slave-info --safe-slave-backup --stream=xbstream --target-dir=./" | \
  ssh $NEW_SLAVE "mkdir -p $BACKUP_DIR && xbstream -x -C $BACKUP_DIR"

# Prepare backup
ssh $NEW_SLAVE "xtrabackup --prepare --target-dir=$BACKUP_DIR"

# Get coordinates
COORDS=$(ssh $NEW_SLAVE "cat $BACKUP_DIR/xtrabackup_binlog_info")
BINLOG=$(echo $COORDS | awk '{print $1}')
POSITION=$(echo $COORDS | awk '{print $2}')

# Configure replication
mysql -h $NEW_SLAVE <