mysql 主主同步原理_MySQL主主数据同步浅析

男娘i 2023-01-11 09:29 234阅读 0赞

MySQL主主同步和主从同步的原理一样，只是双方都是主从角色。

环境

操作系统版本：CentOS7 64位

MySQL版本：mysql5.6.33

节点1IP：192.168.1.205 主机名：edu-mysql-01

节点2IP：192.168.1.206 主机名：edu-mysql-02

MySQL 主从复制官方文档：http://dev.mysql.com/doc/refman/5.6/en/replication.html

注意：

1> 主从服务器操作系统版本和位数要保持一致

2> Master和Slave数据库的版本要一致

3> Master和Slave数据库中的数据要一致

配置

配置之前安装好MySQL

1、安全配置

1> 防火墙

添加mysql通信端口(默认为3306)shell> vim /etc/sysconfig/iptables-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

shell> service  iptables restart

或关闭防火墙shell> service iptables stop

2> 关闭selinuxshell> vi /etc/selinux/config

SELINUX=disabled

将SELINUX的值修改为disabled

2. 节点1配置(192.168.1.205)

2.1 添加数据同步配置shell> vim /etc/my.cnf

在\[mysqld\]中增加以下配置项：

\# 服务器的ID,必须唯一，一般设置自己的IP

server\_id=205\# 复制过滤：不需要备份的数据库(MySQL库一般不同步)

binlog-ignore-db=mysql\# 开启二进制日志功能，名字可以随便取，最好有含义(比如项目名)

log-bin=edu-mysql-bin\# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存

binlog\_cache\_size=1M\# 主从复制的格式(mixed,statement,row,默认格式是 statement)

binlog\_format=mixed\# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。

expire\_logs\_days=7\#\# 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。 \#\# 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致

slave\_skip\_errors=1062\# 作为从服务器时的中继日志

relay\_log=edu-mysql-relay-bin\# log\_slave\_updates 表示 slave 将复制事件写进自己的二进制日志

log\_slave\_updates=1\# 主键自增规则，避免主从同步ID重复的问题

auto\_increment\_increment=2  \# 自增因子(每次加2)

auto\_increment\_offset=1     \# 自增偏移(从1开始)，单数

2.2 Master配置

\# 先重启一下服务

shell> service mysqld restart

\# 登录到mysql

shell> mysql -uroot -p

\# 创建数据库同步用户，并授予相应的权限

mysql> grant replication slave, replication client on \*.\* to 'repl'@'192.168.1.206' identified by 'root123456';

\# 刷新授权表信息

mysql> flush privileges;

\# 查看binlog文件的position(偏移)和File(日志文件)的值，从机上需要用到mysql> show master status;

\+----------------------+----------+--------------+------------------+-------------------+| edu-mysql-bin.000001 |      120 |              | mysql            |                   |

\+----------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

2.3 Slave配置

\# master\_user和master\_password：在206上执行grant replication slave...创建的用户和密码\# master\_log\_file和master\_log\_pos：在206上运行show master status;命令执行结果对应File和Position字段的值mysql> change master to master\_host='192.168.1.206',master\_user='repl', master\_password='root123456', master\_port=3306, master\_log\_file='edu-mysql-bin.000001', master\_log\_pos=439, master\_connect\_retry=30;\# 查看作为从节点的状态信息

mysql> show slave status\\G;\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*               Slave\_IO\_State:                  Master\_Host: 192.168.1.206                  Master\_User: repl                  Master\_Port: 3306                Connect\_Retry: 30              Master\_Log\_File: edu-mysql-bin.000001          Read\_Master\_Log\_Pos: 439               Relay\_Log\_File: edu-mysql-relay-bin.000001                Relay\_Log\_Pos: 4        Relay\_Master\_Log\_File: edu-mysql-bin.000001             Slave\_IO\_Running: No            Slave\_SQL\_Running: No          \# 省略其它配置。。。

由于此时从节点还没有启动，Slave\_IO\_State的值为空，Slave\_IO\_Running和Slave\_SQL\_Running线程为No表示也没有运行。

2.4 启动Slave

注意：要在节点2上创建同步帐户后再启动，否则会报连不上master错误

\# 启动从节点，开始工作接收主节点发送事件(数据库数据变更的所有事件)mysql> start slave;\# 此时再查看slave节点的状态

mysql> show slave status\\G;\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*               Slave\_IO\_State: Waiting for master to send event                  Master\_Host: 192.168.1.206                  Master\_User: repl                  Master\_Port: 3306                Connect\_Retry: 30              Master\_Log\_File: edu-mysql-bin.000001          Read\_Master\_Log\_Pos: 439               Relay\_Log\_File: edu-mysql-relay-bin.000002                Relay\_Log\_Pos: 287        Relay\_Master\_Log\_File: edu-mysql-bin.000001             Slave\_IO\_Running: Yes            Slave\_SQL\_Running: Yes              Replicate\_Do\_DB:            \# ...省略其它配置

3. 节点2配置(192.168.1.206)

3.1 添加数据同步配置shell> vim /etc/my.cnf

· 1

在\[mysqld\]中增加以下配置项：

server\_id=206

binlog-ignore-db=mysqllog-bin=edu-mysql-bin

binlog\_cache\_size=1M

binlog\_format=mixed

expire\_logs\_days=7

slave\_skip\_errors=1062

relay\_log=edu-mysql-relay-bin

log\_slave\_updates=1\#ID自增从2开始，双数

auto\_increment\_increment=2

auto\_increment\_offset=2

3.2 Master配置\# 先重启一下服务

shell> service mysqld restart

\# 登录到mysql

shell> mysql -uroot -p

\# 创建数据库同步用户，并授予相应的权限(只允许repl用户从192.168.1.205上登录)

mysql> grant replication slave, replication client on \*.\* to 'repl'@'192.168.1.205' identified by 'root123456';

\# 刷新授权表信息

mysql> flush privileges;

\# 查看binlog文件的position(偏移)和File(日志文件)的值，从机上需要用到mysql> show master status;

\+----------------------+----------+--------------+------------------+-------------------+| edu-mysql-bin.000001 |      439 |              | mysql            |                   |

\+----------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

这时可以启动节点1(205)的slave服务

3.3 Slave配置\# master\_log\_file和master\_log\_pos：205节点上执行show master status;对应File和position的值

mysql> change master to master\_host='192.168.1.205',master\_user='repl', master\_password='root123456', master\_port=3306, master\_log\_file='edu-mysql-bin.000001', master\_log\_pos=120, master\_connect\_retry=30;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> show slave status\\G;\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*               Slave\_IO\_State:                  Master\_Host: 192.168.1.205                  Master\_User: repl                  Master\_Port: 3306                Connect\_Retry: 30              Master\_Log\_File: edu-mysql-bin.000001          Read\_Master\_Log\_Pos: 120               Relay\_Log\_File: edu-mysql-relay-bin.000001                Relay\_Log\_Pos: 4        Relay\_Master\_Log\_File: edu-mysql-bin.000001             Slave\_IO\_Running: No            Slave\_SQL\_Running: No              Replicate\_Do\_DB:              \#...省略其它配置

3.4、启动Slave

shell> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\\G;\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*               Slave\_IO\_State: Waiting for master to send event                  Master\_Host: 192.168.1.205                  Master\_User: repl                  Master\_Port: 3306                Connect\_Retry: 30              Master\_Log\_File: edu-mysql-bin.000001          Read\_Master\_Log\_Pos: 439               Relay\_Log\_File: edu-mysql-relay-bin.000002                Relay\_Log\_Pos: 287        Relay\_Master\_Log\_File: edu-mysql-bin.000001             Slave\_IO\_Running: Yes            Slave\_SQL\_Running: Yes              Replicate\_Do\_DB:              ...省略其它配置

4、验证\# 登录205创建一个数据库

shell> mysql -u root -p

mysql> create database if not exists mydb default character set utf8 collate utf8\_general\_ci;

mysql> create table user (id int, username varchar(30), password varchar(30));

mysql> insert into user values (1, 'yangxin', '123456');\# 下面是在206节点上的操作\#1、登录206查询所有库，是否包含mydb数据库\#2、切换到mydb库，是否包含user表，并有一条数据\#3、在206的mydb.user表插入一条数据，查看205是否同步过去

mysql> insert into user values (2,'yangxin2','123456')

详细过程如下图所示：

![c2456d7fd672207b3b1007c04dbe3cb2.png][]

本文由职坐标整理发布，欢迎关注职坐标MySQL频道，学习更多数据库知识！

[c2456d7fd672207b3b1007c04dbe3cb2.png]: /images/20221119/dd43f2c8870a40588b4611f757a0f76e.png