Master‑Slave Replication, Backup & Recovery in MySQL – A Complete Step‑by‑Step Guide

1. MySQL Master‑Slave Replication Overview

The purpose of replication is to create real‑time backups and enable read/write separation, where the master handles writes and slaves handle reads, improving data integrity and overall performance.

2. Modify Configuration Files

Ensure each server has a unique server-id (master ID should be smaller than slave IDs). Example configuration snippets:

# 3306 and 3307 represent two machines
# Enable binary logging and set distinct server IDs
log-bin = /data/3306/mysql-bin
server-id = 1

log-bin = /data/3307/mysql-bin
server-id = 3

Verify the settings:

egrep "log-bin|server-id" /data/3306/my.cnf
egrep "log-bin|server-id" /data/3307/my.cnf

Check that binary logging is active:

mysql -uroot -p -S /data/3306/mysql.sock -e "show variables like 'log_bin';"

3. Create Replication Account

Create a dedicated user for replication and grant the necessary privileges:

mysql -uroot -p -S /data/3306/mysql.sock
grant replication slave on *.* to 'rep'@'192.168.200.%' identified by 'nick';
flush privileges;
select user,host from mysql.user;

4. Backup Master and Restore to Slave

Lock the master to obtain a consistent snapshot:

mysql> flush tables with read lock;   # MySQL 5.5 uses this command

Record the binary log position: mysql> show master status; Dump the data (including binlog position) and compress it:

mysqldump -uroot -p -S /data/3306/mysql.sock -A -B --events --master-data=2 | gzip > /opt/rep.sql.gz

On the slave, import the dump and apply the recorded position:

gunzip < /opt/rep.sql.gz | mysql -uroot -p -S /data/3307/mysql.sock

5. Configure Slave and Start Replication

Set the master connection parameters on the slave:

CHANGE MASTER TO
  MASTER_HOST='192.168.200.98',
  MASTER_PORT=3306,
  MASTER_USER='rep',
  MASTER_PASSWORD='nick',
  MASTER_LOG_FILE='mysql-bin.000013',
  MASTER_LOG_POS=410;

Start the slave and verify status:

START SLAVE;
SHOW SLAVE STATUS\G

Key fields to check: Slave_IO_Running = Yes, Slave_SQL_Running = Yes, Seconds_Behind_Master = 0.

6. Read/Write Splitting

On the slave, enable read‑only to prevent writes and create a limited‑privilege user for read operations:

# In /data/3307/my.cnf
[mysqld]
read-only

# Create a read‑only user
grant select,insert,update,delete on *.* to 'suoning'@'localhost' identified by '123';
flush privileges;

If the slave is set to read‑only, attempts to create new users will fail, confirming the protection.

7. Handling Master Failure

Identify the most up‑to‑date slave by inspecting master.info files.

Stop replication threads on all slaves and ensure relay logs are fully processed.

On the chosen slave, reset master info, delete old master.info and relay‑log.info, and promote it to master by adjusting its my.cnf (remove log‑slave‑updates and read‑only).

Restart MySQL on the new master.

Re‑configure the remaining slaves to point to the new master and start replication.

8. Dual‑Master (Master‑Master) Setup

Configure both servers with different auto_increment_offset and auto_increment_increment values to avoid primary‑key collisions:

# Server 3307 (offset 2)
auto_increment_increment = 2
auto_increment_offset = 2

# Server 3306 (offset 1)
auto_increment_increment = 2
auto_increment_offset = 1
log-bin = /data/3306/mysql-bin
log-slave-updates

Dump one server and import it into the other, then set up mutual CHANGE MASTER TO statements pointing to each other.

9. MySQL Backup & Restore

Single‑Database Backup :

mysqldump -uroot -p nick_defailt > /opt/mysql_nick_defailt.bak

Multiple‑Database Backup :

mysqldump -uroot -p -B nick_defailt oldsuo oldsuo_1 | gzip > /opt/mul.sql.gz

Table‑Level Backup :

mysqldump -uroot -p nick_defailt student > /opt/mysql_nick_defailt_student.bak

Key mysqldump Options (e.g., -A, -B, --master-data, --single-transaction, -d, -t, --compact).

10. Incremental Recovery

Prerequisites: binary logging enabled and a recent full backup. To recover a specific point, extract the relevant binlog entries:

mysqladmin -uroot -p -S /data/3306/mysql.sock flush-logs
cp /data/3306/mysql-bin.000030 /server/backup/
mysqlbinlog -d nick mysql-bin.000030 > bin.sql

Then apply the full backup followed by the extracted statements:

mysql -uroot -p -S /data/3306/mysql.sock < /server/backup/mysql_YYYY-MM-DD.sql
mysql -uroot -p -S /data/3306/mysql.sock nick < bin.sql

This restores the database to the state just before the accidental drop.