mysql主从复制配置（基于日志点）

今天药忘吃喽~ 2022-07-14 08:56 158阅读 0赞

MySQL主从是基于binlog日志，所以在安装好数据库后就要开启binlog。这样好处是，一方面可以用binlog恢复数据库，另一方面可以为主从做准备。

查看日志开启状态： show variables like 'log\_bin';

**一、主从配置**

**1.master**

\[mysqld\]

server-id=1 \#主从复制ID必须不一样

log-bin=mysql-master-bin \#开启binlog日志，指定日志名称

binlog\_format=MIXED  \#binlog日志格式

**2.slave**

\[mysqld\]

server-id=2

log-bin=mysql-slave-bin

binlog\_format=MIXED

relay\_log\_recovery = 1 \# 当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。

log\_slave\_updates =1  \#表示slave将复制事件写进自己的二进制日志。当设置log\_slave\_updates时，你可以让slave扮演其它slave的master。此时，slave把SQL线程执行的事件写进行自己的二进制日志(binary log)，然后，它的slave可以获取这些事件并执行它，slave可以是其它slave的master，从而扩散master的更新。

主从可选参数：

read\_only=ON  \#只读

expire\_logs\_days=7 \#binlog过期清理时间

max\_binlog\_size=100m \#binlog每个日志文件大小，默认1G

binlog-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可

binlog-ignore-db=不需要备份的数据库苦命，如果备份多个数据库，重复设置这个选项即可

slave-skip-errors = all      \#跳过主从复制出现的错误

master-connect-retry =60 \# 用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒

配置完成，重启mysql服务。

向主库添加几条数据，查看master的日志文件和日志点：

![Center][]

**二、主从复制配置步骤**

1.在master端建立复制用户

2.备份master端数据，并在slave端恢复。备份是指定--master-data参数

3.在slave端使用change master命令配置复制

**三、配置过程**

环境：master  192.168.0.109  slave  192.168.0.110   mysql版本： 5.7.12

1.在master建立同步账号

>grant  replication slave on \*.\* to 'repltest'@'192.168.0.%'  identified by 'Repltest123\#@!'

建议使用如下创建：

>create user 'repltest'@'192.168.0.%'  identified by 'Repltest123\#@!';

>grant  replication slave on \*.\* to 'repltest'@'192.168.0.%';

2.使用dump备份数据在slave恢复

主库备份：

mysqldump -- single-transaction  -- master-data =2 -- triggers  \-- routines  \-- all-databases   -uroot -p>all.sql

\--routines：导出存储过程和函数

\-- triggers ：导出触发器

\--single\_transaction：导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables;而lock-tables是锁住一张表不能写操作，直到dump完毕。

\--master-data：默认等于1，将dump起始(change master to)binlog点和pos值写到结果中，等于2是将change master to写到结果中并注释。

从库恢复：

将all.sql复制到从库机器

mysql -uroot -p<all.sql

注： 推荐使用 xtrabackup 方式进行数据备份恢复，可以参考: [http://write.blog.csdn.net/postedit/51340330][http_write.blog.csdn.net_postedit_51340330]

3.建立同步复制

在slave执行：

mysql> change master to master\_host='192.168.0.109',

>master\_user='repltest',

>master\_password='Repltest123\#@!',

>master\_log\_file='mysql-master-bin.000001',

>master\_log\_pos=1283;

使用 show slave status \\G 命令查看slave配置；

![Center 1][]

开启slave同步：

mysql>start slave;

![Center 2][]

链接成功。

我们在主上添加些数据，看看是否正常通过到slave，并观察 master和slave的日志文件和日志点是否同步一致。

reset slave;    \#清除从连接信息

4.查看同步线程

show processlist \\G

![Center 3][]

**四、主从管理的系统视图**

在 performance\_schema中有如下视图对主从复制状态信息查看：

![Center 4][]

1.replication\_applier\_configuration：存储主从同步的链接配置

![Center 5][]

由于我们只有一个主，默认名称channel\_name为空，可以管理多个不同主链接。

desired\_delay 指定主从延时同步时间，单位秒，默认0 不延时，可以通过如下命令修改，需重启

change msater to master\_delay=3600

replication\_applier\_status  同步状态信息

replication\_applier\_status\_by\_coordinator 用于多线程复制协调

replication\_applier\_status\_by\_worker    多线程复制线程

replication\_connection\_configuration  同步连接信息，ip、port等

replication\_connection\_status 链接状态

replication\_group\_members

replication\_group\_member\_stats

[Center]: /images/20220714/3bbc60695000444cb95e17267ade004a.png
[http_write.blog.csdn.net_postedit_51340330]: http://write.blog.csdn.net/postedit/51340330
[Center 1]: /images/20220714/1f48f3d80c524262bc5e67a1dbd41d6c.png
[Center 2]: /images/20220714/ba321395ecc34b4699419e389936144f.png
[Center 3]: /images/20220714/e12eca0a18a54bdf9930d69beee7f13a.png
[Center 4]: /images/20220714/0cf39de3c9ae40aaa43384b6eac1e42e.png
[Center 5]: /images/20220714/78ba7434c72e4f9a9f632110bab5c54b.png