MySQL Replication And How To Achieve It

Replication

Replication is the ability to duplicate data changes that occur on one server to another.

Basic components

Servers	Master, Slave, Relay Slave
Configuration files
Threads	Binlog dump thread, I/O Thread, SQL Thread
Sequential files	Binary Log, Relay Log

Basic uses of Replication

Use	Description
Backup	Run backup operations that require (or not) taking the server offline
Scale out	Add more slaves to improve read throughput
Hot Standby	Provide a replacement for the master to reduce downtime
Data Analysis	Perform resource-intensive operations on a slave to avoid conflicts with other active applications
Debugging	Carry diagnoses of complex queries, and refine database design without risking effects to the production databases.
Development	Provide near-production-quality data for development of new applications that represent actual data values, ranges, and size

Replication – how it works?

Basic rules

Slave can have only one Master
Master can have many Slaves
Each server must have a unique server_id
Slave can propagate changes from its Master and be Master for other Slaves (Relay Slave) – enable log_slave_updates

Basic configuration – Master

1. Changes in .cnf file

[mysqld]

server_id=N

log_bin=mysql-bin

2. Set up a special user

mysql> CREATE USER ‘slaveuser’@’%’ IDENTIFIED BY ‘haslo’;

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slaveuser’@’%’;

mysql> flush privileges;

3. Get coordinates of binary log

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;