Build MySQL Replication Environment

The post demonstrates how to build replication environment with 2 new MySQL servers which are running on 64-bit Ubuntu 14.04 LTS machines.

• Master: {MySQL: 5.7.5, IP Address: 192.168.0.100}
• Slave  : {MySQL: 5.7.5, IP Address: 192.168.0.101}

 

Configure the Master

Backup MySQL configuration file.

cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

Open my.cnf, set server-id and log-bin, and change bind-address.

server-id=1
log-bin=/var/log/mysql/mysql-bin.log
bind-address=192.168.0.100

Restart master MySQL server to apply these settings.

service mysql restart

Create Replication user.

mysql -u root -p --prompt='master>'
master> create user repl_user@192.168.0.101;
master> grant Replication Slave on *.* to repl_user@192.168.0.101 identified by 'password';

Lock the Master, note binary log name and position.

master> flush tables with read lock;
master> show master status; # In this post, the binary log file name is mysql-bin.000001, the position is 439.

Create a data snapshot using mysqldump. (start another session and run the followoing command, and then copy the db.dump to the Slave)

mysqldump -u root -p --all-databases > db.dump

Release the read lock.

master> unlock tables;

 

Configure the Slave

Backup MySQL configuration file.

cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

Open my.cnf, set server-id and change bind-address.

server-id=2
bind-address=192.168.0.101

Restart slave MySQL server.

service mysql restart

Set the Master configuration.

mysql -u root -p --prompt='slave>'
slave> change master to master_host='10.110.77.181', master_user='repl_user', master_password='OpsMgr2007R2', master_log_file='mysql-bin.000001', master_log_pos=439;
slave> start slave;

 

Check if replication works

On the Master, create a database and a table and add a row.

master> create database test_replication;
master> use test_replication;
master> create table t1 (Id int not null primary key);
master> insert into t1 values (777);

 

On the Slave, check if the replication works.

slave> show databases;
slave> select * from test_replication.t1;