Build MySQL Replication Environment

The post demonstrates how to build replication environment with 2 new MySQL servers which is running on 64-bit U Buntu 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=1log-bin=/var/log/mysql/mysql-bin.logbind-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 [email protected]  192.168. 0.101 ; master> Grant Replication Slave on * * to [email protected]192.168. 0.101 ' Password ';

Lock the Master, note binary log name and position.

master> Flush tables with the Read Lock;masterfile name is Mysql-bin. 000001 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=2bind-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=' /c4>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> user test_replication;masterint NULL primary key); master> INSERT into T1 values (777);

On the Slave, check if the replication works.

Slave> Show Databases;slaveSelect * from Test_replication.t1;

Build MySQL Replication Environment