How To Set Up Master Slave Replication in MySQL

About MySQL replication

Setup

sudo apt-get install mysql-server mysql-client

Step One—Configure the Master Database

sudo nano /etc/mysql/my.cnf

bind-address            = 127.0.0.1

bind-address            = 12.34.56.789 

server-id               = 1

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = newdatabase

sudo service mysql restart

mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

USE newdatabase;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysqldump -u root -p --opt newdatabase > newdatabase.sql

UNLOCK TABLES;

QUIT;

Step Two—Configure the Slave Database

CREATE DATABASE newdatabase;

EXIT;

mysql -u root -p newdatabase < /path/to/newdatabase.sql

sudo nano /etc/mysql/my.cnf

server-id               = 2

relay-log               = /var/log/mysql/mysql-relay-bin.log

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = newdatabase

sudo service mysql restart

CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

1. It designates the current server as the slave of our master server.


2. It provides the server the correct login credentials


3. Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

START SLAVE;

SHOW SLAVE STATUS\G

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;