MySQL实现主主同步

2021-12-09

MySQL实现主主同步

安装两个MySQL

Docker在两个端口（3306,3307）启动两个数据库。

配置MySQL

分别配置数据库的 my.cnf 文件

3306数据库

[mysqld]
# 跳过密码验证
# skip-grant-tables

# [必须]服务器唯一ID，每台服务器需不同
server-id=1

# [必须]启用二进制文件
log-bin=mysql-bin

# [必须]
# 做主主备份时，因为每台数据库服务器都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突。解决这个问题的办法就是让每个数据库的自增主键不连续，即下面两项配置。
# 下面两项说的是，假设需要将来可能需要2台服务器做备份，将auto-increment-increment设为2。而auto-increment-offset=1表示这台服务器的序号。从1开始，不超过auto-increment-increment。
auto_increment_increment=2
auto_increment_offset=1

# [必须]不需要记录二进制日志的数据库。如果有多个数据库可用逗号分隔，或者使用多个binlog-do-db选项。一般为了保证主主同步不冲突，会忽略mysql数据库。
binlog-ignore-db=mysql,information_schema,performance_schema

# 指定utf8为默认字符集
character_set_server=utf8

# 在默认情况下，mysql会阻止主主同步的数据库function的创建。
# 在导入sql文件时如果有创建function或者使用function的语句将会报错。
log-bin-trust-function-creators=1

# 需要主主复制的数据库
replicate-do-db=fhs2-microservice

# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M

# 主主复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主主复制时数据更加统一） 
binlog_format=row

# 设置二进制日志自动删除/过期的天数，避免占用磁盘空间。默认值为0，表示不自动删除。 
expire_logs_days=7

# 跳过主主复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 
# 如：1062错误是指一些主键重复，1032错误是因为主主数据库数据不一致 
slave_skip_errors=1062

# relay_log配置中继日志，默认采用 主机名-relay-bin 的方式保存日志文件 
relay_log=replicas-mysql-relay-bin

# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1

# 防止改变数据(只读操作，除了特殊的线程)
read_only=1

3307数据库

[mysqld]
 
server-id=2
 
log-bin=mysql-bin
 
auto_increment_increment=2
 
auto_increment_offset=2
 
binlog-ignore-db=mysql,information_schema,performance_schema
 
character_set_server=utf8
 
log-bin-trust-function-creators=1

replicate-do-db=fhs2-microservice

binlog_cache_size=1M

binlog_format=row

expire_logs_days=7

slave_skip_errors=1062

relay_log=replicas-mysql-relay-bin

log_slave_updates=1

read_only=1

重启MySQL

设置MySQL权限

设置权限，初始化数据库时已开启全部权限。

配置同步日志

分别查看数据库的日志节点，并配置同步日志节点。

查看日志节点

# 登录进入MySQL
mysql -u root -p xxx

# 查看MySQL日志节点
mysql> show master status;

3306数据库日志节点

+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000030 |      156 |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

3307数据库日志节点

+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000032 |      158 |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

设置日志节点

相互设置同步的日志节点。

配置当前数据库日志节点时，所有内容都是另一个数据库的信息。

每次 “flush privileges” 和 “stop slave” 后日志节点的内容会改变，所以，每次执行此操作前必须要查看日志节点 “show master status”。另外，执行此操作时 slave 必须没有启动，否则就先停止 “stop slave”。

3306数据库

mysql> change master to 
    ->master_host='192.168.88.250', 
    ->master_port=3307, 
    ->master_user='root',
    ->master_password='root',
    ->master_log_file='mmysql-bin.000032',
    ->master_log_pos=158;

3307数据库

mysql> change master to 
    ->master_host='192.168.88.250', 
    ->master_port=3306, 
    ->master_user='root',
    ->master_password='root',
    ->master_log_file='mmysql-bin.000030',
    ->master_log_pos=156;

启动slave

在两台主机上启动slave服务

mysql> start slave;

测试

查看slave状态，查看数据库的同步变化。

slave状态

mysql> show slave status\G

数据库同步变化

修改3306数据库某数据表数据，3307数据库中对应数据表数据跟随变化。

修改3307数据库某数据表数据，3306数据库中对应数据表数据跟随变化。