How to Set Up MySQL Master‑Slave Replication on Linux: Step‑by‑Step Guide

Two Linux servers are used: the master (public IP 219.223.5.105, internal 192.168.1.75) and the slave (public IP 219.223.5.104, internal 192.168.1.74).

Step 1: Configure the master server

[mysqld]
log-bin=mysql-bin   // enable binary log
server-id=104       // unique ID, usually the last octet of the IP

Step 2: Configure the slave server

[mysqld]
log-bin=mysql-bin
server-id=105       // unique ID for the slave

Step 3: Restart MySQL on both machines

service mysqld restart   # on master (104)
service mysqld restart   # on slave (105)

Step 4: Grant replication privileges on the master

mysql -uroot -proot123
GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

The asterisk pattern *.* grants all databases and tables; '%' allows any client host to connect.

Step 5: Record the master’s binary log position

SHOW MASTER STATUS;

Note the File (e.g., mysql-bin.000001) and Position (e.g., 1743) values; they are required for the slave configuration.

Step 6: Configure the slave to connect to the master

CHANGE MASTER TO
  MASTER_HOST='192.168.1.74',
  MASTER_USER='mysync',
  MASTER_PASSWORD='mysync',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1743;

Step 7: Start the slave replication process

START SLAVE;

Step 8: Verify that the slave is running correctly

SHOW SLAVE STATUS\G
# Expect:
#   Slave_IO_Running: Yes
#   Slave_SQL_Running: Yes

If both flags are “Yes”, replication is successful; any other state indicates a problem.

Step 9: Test the replication

# On the master
CREATE DATABASE xd;
CREATE TABLE student (name VARCHAR(256), age INT, sex VARCHAR(256));
INSERT INTO student VALUES ('wangming',18,'f'), ('xiaobai',20,'m');

# On the slave
SHOW DATABASES;
USE xd;
SELECT * FROM student;

The slave should display the same database, table, and rows as the master.

Common issues and tips

If the master and slave have different table structures, replication may fail.

When Slave_SQL_Running: No, consult troubleshooting guides (e.g., the linked blog post) for error details.

To replicate only specific tables, add replicate-do-table=xd.student on the slave; multiple tables require multiple directives.

Wildcard replication can be set with replicate-wild-do-table=tablename%.