How to Set Up MySQL Master‑Slave Replication

Preparation: two virtual machines running CentOS 5.5 with identical MySQL versions, IP 192.168.1.101 (master) and 192.168.1.105 (slave).

Replication principle: the master writes all data‑changing operations to a binary log; the slave reads this log and replays the same operations, achieving data consistency.

Step 1 – Enable binary logging on the master

Open the MySQL configuration file ( # vi /etc/my.cnf) and add the following lines:

log-bin=mysql-bin

binlog_format=mixed

server-id=101

Save and restart MySQL ( # service mysqld restart). Perform the same changes on the slave, using server-id=105, then restart MySQL.

Step 2 – Create a replication user on the master

Log into MySQL on the master: # mysql -u root -p111111 Execute:

GRANT replication slave ON *.* TO 'slave'@'%' IDENTIFIED BY '111111';

Step 3 – Record the master’s binary‑log file and position

Run SHOW MASTER STATUS; on the master and note the values of File and Position. Do not perform further writes to the master before the slave is configured.

Step 4 – Configure the slave

Log into MySQL on the slave: # mysql -u root -p111111 Stop any existing replication: STOP SLAVE; Set the master connection parameters (replace with your actual values):

CHANGE MASTER TO MASTER_HOST='192.168.1.101',
MASTER_USER='slave',
MASTER_PASSWORD='111111',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=12345;

Start the slave: START SLAVE; Step 5 – Verify replication

Execute on the slave: SHOW SLAVE STATUS\G If both Slave_IO_Running and Slave_SQL_Running show Yes, the replication is successful; otherwise repeat the previous steps.

Images in the original article illustrate configuration files and command outputs.