首页 > 代码库 > 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.backupOpen 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.backupOpen 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