3. 主从复制 Replication

## 3.1 架构图

![](http://img.blog.itpub.net/blog/2020/03/25/5e67d36d02a3a35d.png?x-oss-process=style/bb)

## 3.2 参数配置

### 3.2.1 创建启闭脚本

login.sh

```shell
#!/bin/bash
mysql -h127.0.0.1 -uroot -P3306 -p'Yxc@3306'
```

start.sh

```shell
#!/bin/bash
mysqld_safe --defaults-file=/data/mysql/3306/my3306.cnf --user=mysql &
```

shutdown.sh

```shell
#!/bin/bash
/usr/local/mysql/bin/mysqladmin -uroot -P3306 -p'Yxc@3306' -S /data/mysql/3306/mysql3306.sock shutdown
```

### 3.2.2 编辑主节点配置文件

```shell
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
port=3306
socket=/data/mysql/3306/mysql3306.sock
character-set-server=utf8
log-error=/data/mysql/3306/log/error3306.log
pid-file=/data/mysql/3306/mysqld3306.pid
log_bin=mysql_bin
binlog-format=Row
server-id=51
```

[^说明]: 配置后，重启数据库，使其生效

### 3.2.3 创建从节点配置文件

```shell
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
port=3306
socket=/data/mysql/3306/mysql3306.sock
character-set-server=utf8
log-error=/data/mysql/3306/log/error3306.log
pid-file=/data/mysql/3306/mysqld3306.pid
log-bin=mysql_bin
server-id=52
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
```

[^说明]: 分别在两个从节点配置后，重启数据库生效。

## 3.3 创建用户

```mysql
create user 'repl'@'%' identified by 'repl';                  grant replication slave on *.* to 'repl'@'%';
```

[^说明]: 在主节点上创建用于复制的用户即可。

## 3.4 配置复制

主库上执行命令，获取当前binlog日志及位置

```shell
show master status;
```

![](http://img.blog.itpub.net/blog/2020/03/25/8567ec68b472403f.png?x-oss-process=style/bb)

获取file，position信息，在两个从库上分别执行如下脚本

```mysql
CHANGE MASTER TO
MASTER_HOST='192.168.56.5',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql_bin.000002',
MASTER_LOG_POS=154;
```

从库上，启动复制，并查看复制状态

```mysql
start slave;
show slave status;
```

![](http://img.blog.itpub.net/blog/2020/03/25/9b45ad4f94441cd1.png?x-oss-process=style/bb)

[^说明]: 如出现如图字样，表示成功。关于slave的命令，可以通过help slave;

![](http://img.blog.itpub.net/blog/2020/03/25/fd6e568e682878a7.png?x-oss-process=style/bb)

ITPub博客

mysql 学习笔记之主从复制 Replication

最新文章