mysql主从复制(master-slave)_Mysql主从复制原理及搭建案例

# Mysql主从复制原理及搭建案例

## 简介

Mysql 主从复制(Master-Slave Replication)作为构建大型应用程序的基础，可以用于数据备份、DB负载均衡等。由一台主服务器(Master)和一台/多台从服务器(Slave)构成，其工作原理可以概括为3步：

* master将变更记录到二进制日志文件(binary log)

* slave将master上的binary log拷贝到它的中继日志(relay log)

* slave重做中继日志(即执行二进制日志文件)，将数据变更反应到slave上

如下图所示:


* 在每个事务完成前，master将数据变更记录到二进制日志文件，完成后通知引擎提交事务

* slave将master的二进制日志拷贝到自己的中继日志，slave会开启一个I/O线程读取日志，直到与master一致才会睡眠以等待新的事件。I/O线程负责将master日志写入自己的中继日志

* SQL线程读取slave上中继日志并重做日志

## Master产生binlog的几种类型

### Row 基于行

详细记录了每一行数据的变更，不会出现处理存储过程、函数的情况，但是如果一句SQL批量处理了大量数据将会产生大量的日志行

### Statement 基于SQL语句

保存了执行的每句SQL，不需要记录每行数据的变化，直接在slave执行对应的SQL即可；但若master与slave版本不一致，一些函数可能不能被正确执行

### Mixed 混合方式，默认

Mysql根据执行的每条SQL自动区分对待记录的日志形式

## Demo

下面是搭建一个简单master-slave案例。

### 环境信息

操作系统：Ubuntu 14.04.2

Mysql版本：5.5

Master服务器IP：120.24.11.11

### 测试数据准备

```sql

#在Master创建测试用DB

create database masterdb default charset utf8 collate utf8_general_ci;

#在Master创建专门用于复制的用户

insert into mysql.user(Host,User,Password) values('%','repluser',password('123456'));

#实际环境注意仅对指定的IP赋权

grant replication slave on *.* to 'repluser'@'%' identified by '123456';

flush privileges;

#插入测试数据

use masterdb;

create table user(name varchar(30));

insert into user values('user1');

```

### 开启Master binlog

编辑/etc/mysql/my.cnf，在[mysqld]区添加

```

server-id = 1

log_bin = /var/log/mysql/mysql-bin.log

binlog_do_db = masterdb

binlog_ignore_db = mysql,information_schema,moon,jjc,jjcdev

```

其中，server-id表示master的服务器ID，可以自定义，不要与master-slave群中服务器重复即可；log_bin表示二进制日志文件位置；binlog_do_db表示需要产生binlog的schema，binlog_ignore_db表示需要忽略的schema

配置后重启，让配置生效

```shell

service mysql restart

```

### 同步数据

拷贝Master数据到Slave，让Master和Slave数据保持一致

```mysql

flush tables with read lock; #不要退出终端，否则锁将失效

```

记录当前master的binlog文件和偏移量，分别为[mysql-bin.000002]和[107]，后续配置slave节点需要用到


在另一个terminal将数据down下来，这里使用mysqldump

```mysql

mysqldump -uroot -pa123456 --databases masterdb > masterdb.sql; #将数据down下来

mysql -uroot -proot -h192.168.179.132 < masterdb.sql; #将数据导入到slave

```

接下来释放锁

```mysql

unlock tables;

```

### 配置从节点

修改slave的 /etc/mysql/my.cnf，配置

```

server-id = 2

log-slave-updates = 1

```

log-slave-updates用于允许备库将其重放的事件也记录到二进制日志中，可以将备库作为其他从库的主库

重启mysql使配置生效，然后在slave上配置master相关信息

```mysql

CHANGE MASTER TO

MASTER_HOST='120.24.11.11',

MASTER_USER='repluser',

MASTER_PASSWORD='123456',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000002',

MASTER_LOG_POS=107,

MASTER_CONNECT_RETRY=10;

```

以上信息主要提供了一个master的用户用于拷贝数据，而MASTER_LOG_FILE和MASTER_LOG_POS就是上面保存的binlog文件和偏移量，表示将从对应的binlog文件的指定位置开始拷贝

接下来启动slave

```mysql

start slave;

show slave status\G;

```

如下图，当Slave_IO_Running和Slave_SQL_Running为Yes时表示slave已经在正常工作


Slave启动后，在master可以看到slave的I/O线程创建的连接，如下图：

一个通过repluser创建的连接


扫码或搜索 codercyj 关注微信公众号, 结伴学习, 一起努力