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=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 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=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=‘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;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;

 

Build MySQL Replication Environment