首页 > 代码库 > Build MySQL Replication Environment

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