Add a MySQL Slave Without Downtime Using mysqldump or XtraBackup

Background

The production environment runs MySQL 5.5 with one master (192.168.18.212) and one slave (192.168.18.213). To increase read capacity a second slave (192.168.18.214) must be added without stopping the MySQL service, so the operation is performed during a low‑traffic window.

Method 1 – Logical Backup with mysqldump

MySQL replication requires binary logging, therefore log-bin must be enabled on the master. Example my.cnf on the master:

# vi /etc/mysql/my.cnf
server-id = 1          # unique ID
log-bin = mysql-bin    # enable binlog
auto-increment-increment = 1
auto-increment-offset = 1
slave-skip-errors = all

Create a replication user on the master

mysql> GRANT ALL ON *.* TO 'sync'@'192.168.18.%' IDENTIFIED BY 'sync';

Configure the new slave (example my.cnf on 192.168.18.214):

# vi /etc/mysql/my.cnf
server-id = 3          # unique ID for this slave
log-bin = mysql-bin
auto-increment-increment = 1
auto-increment-offset = 1
slave-skip-errors = all

Dump the master database with a consistent snapshot and routine definitions:

# mysqldump -uroot -p123 \
    --routines \
    --single-transaction \
    --master-data=2 \
    --databases weibo > weibo.sql

Key options: --routines – exports stored procedures and functions. --single-transaction – creates a consistent InnoDB snapshot without locking tables. --master-data=2 – writes the CHANGE MASTER TO statement as a comment so the binlog file and position can be extracted later.

Transfer the dump file to the new slave # scp weibo.sql [email protected]:/home/root/ Generate new data on the master to verify replication

mysql> CREATE TABLE test_tb (id INT, name VARCHAR(30));

Import the dump on the slave

# mysql -uroot -p123 -e 'CREATE DATABASE weibo;'
# mysql -uroot -p123 weibo < /home/root/weibo.sql

Extract the binlog file and position from the dump

# head -25 weibo.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

Configure replication on the slave and start it

mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.18.212',
    MASTER_USER='sync',
    MASTER_PASSWORD='sync',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=107;
mysql> START SLAVE;

Verify replication with SHOW SLAVE STATUS\G. Both Slave_IO_Running and Slave_SQL_Running should be Yes. A SHOW TABLES on the slave will list test_tb, confirming that the new table was replicated.

Method 2 – Physical Backup with Percona XtraBackup (Recommended)

This method copies the data files directly, avoiding table locks and providing much faster backup for large datasets.

Reset the existing slave configuration on 192.168.18.214:

mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> DROP DATABASE weibo;

Create a physical backup on the master using innobackupex: # innobackupex --user=root --password=123 ./ The command creates a timestamped directory (e.g., 2015-07-01_16-49-43 ) containing the InnoDB data files and metadata such as xtrabackup_info .

Copy the backup directory to the new slave

# scp -r 2015-07-01_16-49-43 [email protected]:/home/root/

Replace the slave’s data directory with the backup

# sudo rm -rf /var/lib/mysql/
# sudo mv /home/root/2015-07-01_16-49-43/ /var/lib/mysql
# sudo chown -R mysql:mysql /var/lib/mysql
# sudo /etc/init.d/mysql start
# ps -ef | grep mysqld   # confirm the server is running

Create new data on the master to test replication

mysql> CREATE TABLE test_tb2 (id INT, name VARCHAR(30));

Obtain the binlog file and position from the backup’s xtrabackup_info . Example excerpt:

binlog_pos = filename 'mysql-bin.000001', position 429

Configure the slave to start replication from that point

mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.18.212',
    MASTER_USER='sync',
    MASTER_PASSWORD='sync',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=429;
mysql> START SLAVE;

Verify replication with SHOW SLAVE STATUS\G. Both IO and SQL threads should report Yes. SHOW TABLES on the slave will list test_tb and test_tb2, confirming successful synchronization.

Both approaches achieve the goal of adding a new slave, but XtraBackup is preferred for large datasets because it performs a non‑blocking physical copy.