Replication basics in MySQL

Introduction

Replication helps copy data from one database to another automatically. This keeps data safe and available in many places.

You want a backup copy of your database in case the main one fails.

You need to share data across different locations or servers.

You want to balance the load by sending read requests to copies.

You want to keep a reporting database separate from the main one.

You want to test changes on a copy without affecting the main data.

Syntax

MySQL

CHANGE MASTER TO
  MASTER_HOST='master_host_name',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='log_file_name',
  MASTER_LOG_POS=log_position;

This command sets up the slave to connect to the master database.

You need to create a special user on the master for replication.

Examples

This sets the slave to connect to the master at IP 192.168.1.100 using the user 'repl_user'. It starts reading from the binary log file 'mysql-bin.000001' at position 154.

MySQL

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='secret',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

This command starts the replication process on the slave server.

MySQL

START SLAVE;

This shows the current status of the slave replication, including if it is running and any errors.

MySQL

SHOW SLAVE STATUS\G

Sample Program

This example sets up a slave on the same machine (localhost) with user 'replicator'. It starts replication from the specified log file and position, then starts the slave and shows its status.

MySQL

CHANGE MASTER TO
  MASTER_HOST='localhost',
  MASTER_USER='replicator',
  MASTER_PASSWORD='pass123',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=120;

START SLAVE;

SHOW SLAVE STATUS\G

Important Notes

Make sure the replication user has REPLICATION SLAVE permission on the master.

Binary logging must be enabled on the master for replication to work.

Check the slave status often to catch and fix errors quickly.

Summary

Replication copies data from one database (master) to another (slave) automatically.

Use CHANGE MASTER TO to configure the slave connection to the master.

Start replication with START SLAVE and check status with SHOW SLAVE STATUS.