Build MySQL Replication Environment

最新推荐文章于 2025-08-09 18:20:04 发布

weixin_30788619

最新推荐文章于 2025-08-09 18:20:04 发布

阅读量100

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/jeffreyf/p/build-mysql-replication-environment.html

本文详细介绍如何使用两台运行64位Ubuntu 14.04 LTS的MySQL服务器(版本5.7.5)构建主从复制环境。文章包括配置主服务器、备份配置文件、创建复制用户、锁定主服务器及创建数据快照等步骤，并指导如何在从服务器上设置主服务器配置并启动复制进程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

转载于:https://www.cnblogs.com/jeffreyf/p/build-mysql-replication-environment.html