mysql主主同步配置

原创

abc小辉 2022-01-10 10:06:58 ©著作权

文章标签 mysql 服务器自增长数据库 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者abc小辉的原创作品，请联系作者获取转载授权，否则将追究法律责任

主机环境

Centos7.6版本

Master:172.19.36.149

Slave:172.19.36.150

注意下面几点：

1)要保证同步服务期间之间的网络联通。即能相互ping通，能使用对方授权信息连接到对方数据库（防火墙开放3306端口）。

2）关闭selinux。

3）同步前，双方数据库中需要同步的数据要保持一致。这样，同步环境实现后，再次更新的数据就会如期同步了

关闭selinux

1.vim /etc/sysconfig/selinux //编辑selinux配置文件

2.修改SELINUX=disabled //修改值为disabled为关闭

关闭防火墙

Systemctl status firewalld(查看防火墙状态)

Systemctl stop firewalld(关闭防火墙)

mysql主主同步配置_数据库

Active:inactive(dead)：关闭状态

主主复制的缺点和解决方法

在做主主同步前，提醒下需要特别注意的一个问题：

主主复制和主从复制有一些区别，因为多主中都可以对服务器有写权限，所以设计到自增长重复问题，例如：

出现的问题（多主自增长ID重复）

1）首先在A和B两个库上创建test表结构;

2）停掉A，在B上对数据表test(存在自增长属性的ID字段)执行插入操作，返回插入ID为1;

3）然后停掉B，在A上对数据表test(存在自增长属性的ID字段)执行插入操作，返回的插入ID也是1;

4）然后同时启动A,B，就会出现主键ID重复

解决方法：
只要保证两台服务器上的数据库里插入的自增长数据不同就可以了
如：A插入奇数ID，B插入偶数ID，当然如果服务器多的话，还可以自定义算法，只要不同就可以了
在下面例子中，在两台主主服务器上加入参数，以实现奇偶插入！
记住:在做主主同步时需要设置自增长的两个相关配置，如下：
auto_increment_offset 表示自增长字段从那个数开始，取值范围是1 .. 65535。这个就是序号。如果有n台mysql机器，则从第一台开始分为设1，2...n
auto_increment_increment 表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535。如果有n台mysql机器，这个值就设置为n

在主主同步配置时，需要将两台服务器的：
auto_increment_increment 增长量都配置为2
auto_increment_offset 分别配置为1和2。这是序号，第一台从1开始，第二台就是2，以此类推.....
这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。（针对的是有自增长属性的字段）

实现过程

1.在master上的my.cnf配置

validate_password = off #禁用密码策略

mysql主主同步配置_服务器_02

mysql主主同步配置_服务器_03

Cd /etc/my.cnf
Vi /etc/my.cnf
server-id=149 #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin #开启bin-log，并指定文件目录和文件名前缀
binlog-ignore-db=mysql #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1 #确保binlog日志写入后与硬盘同步
binlog_checksum = crc32 #跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed #bin-log日志文件格式，设置为MIXED可以防止主键重复。
auto-increment-increment = 2 #自增长字段每次递增2
auto-increment-offset = 1 #自增长字段从1开始
slave-skip-errors = all #跳过所有的错误错误，继续执行复制操作
validate_password_policy=0 #指定密码策略
重启数据库
Systemctl restart mysqld
连接数据库
Mysql -u root -p 123456

数据同步授权（iptables防火墙开启3306端口，要确保对方机器能使用下面权限连接到本机mysql）

grant replication slave,replication client,select on *.* to slave@'172.19.36.150' identified by 'Parav1ew@123';

mysql>flush privileges;

最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁；锁住后，就不能往表里写数据，但是重启mysql服务后就会自动解锁！

注意该参数设置后，如果自己同步对方数据，同步前一定要记得先解锁！

mysql> flush tables with read lock;

查看服务器master状态(注意File与Position项，从服务器需要这两项参数)

mysql> show master status;

mysql主主同步配置_自增长_05

2.在slave上的my.cnf配置

validate_password = off #禁用密码策略

vi /etc/my.cnf
server-id=150 #数据库唯一ID，主从的标识号绝对不能重复。
log-bin=mysql-bin #开启bin-log，并指定文件目录和文件名前缀
binlog-ignore-db=mysql #不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
sync_binlog = 1 #确保binlog日志写入后与硬盘同步
binlog_checksum = crc32 #跳过现有的采用checksum的事件，mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none
binlog_format = mixed #bin-log日志文件格式，设置为MIXED可以防止主键复。
auto-increment-increment = 2 #自增长字段每次递增2
auto-increment-offset = 1 #自增长字段从1开始
slave-skip-errors = all #跳过所有的错误错误，继续执行复制操作
validate_password_policy=0 #指定密码策略
重启数据库
Systemctl restart mysqld
连接数据库
Mysql -u root -p Parav1ew
grant replication slave,replication client,select on *.* to slave@'172.19.36.149' identified by 'Parav1ew@123';
mysql> flush privileges;

20.最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁；锁住后，就不能往表里写数据，但是重启mysql服务后就会自动解锁！

注意该参数设置后，如果自己同步对方数据，同步前一定要记得先解锁！

mysql> flush tables with read lock;
执行主张同步
先在slave数据库上做同步master的设置。（确保slave上要同步的数据，提前在master上存在。最好双方数据保持一致）
mysql> unlock tables; (原先是因为执行了锁表 flush tables with read lock;)
Stop slave;
change master to master_host='172.19.36.149',master_user='slave',master_password='Parav1ew@123',master_log_file='mysql-bin.000003',master_log_pos=1104436;
Start slave;
show slave status\G;
再在master数据库上做同步slave的设置。（确保slave上要同步的数据，提前在master上存在。最好双方数据保持一致）
mysql> unlock tables;(由于执行了锁表操作查看POS值： flush tables with read lock;)
Stop slave;
change master to master_host='172.19.36.150',master_user='slave',master_password='Parav1ew@123',master_log_file='mysql-bin.000006',master_log_pos=1658050;
Start slave;
show slave status\G;