编程宝库 - 技术改变世界

编程宝库

Search

Mysql知识

一键清空(重置)本地MySQL8.0密码脚本

MySQL导出sql脚本文件操作指南

Mysql双主搭建的方法步骤

1. Mysql binlog参数配置

log-bin=mysql-bin

打开二进制日志功能，默认在datadir下

binlog-ignore-db

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog不记录上述表的修改

binlog_format

binlog_format=mixed

值有三个，ROW/STATEMENT/MIXED

expire_logs_days

自动删除binlog的天数，8.0已弃用，由binlog_expire_logs_seconds代替。MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项

binlog_expire_logs_seconds

二进制日志有效期，单位秒

binlog_expire_logs_seconds=259200 自动删除3天前的binlog

binlog_cache_size

binlog_cache_size=1M

默认值32KB。在一个事务中binlog为了记录SQL状态所持有的cache大小，如果经常使用大事务，可以增加此值来获取更大的性能。

max_binlog_size

max_binlog_size=500M

如果二进制日志写入的内容超出给定值，日志就会发生滚动

binlog_do_db

binlog-ignore-db=test

binlog只记录test数据库的修改

log_slave_updates

log_slave_updates=1

从库如果做为其他从库的主库，那么这个参数必须开启，设置为1为开启

因为直接往mysql写入的数据会写入binlog，但是后台IO线程从relaylog日志读取写入的数据在参数关闭的情况下不会写入binlog，如果该库还需要向其他slave同步，这个参数必须开启

2. Mysql binlog查看详细内容

show binary logs

查看binlog日志列表

show binlog events in 'mysql-bin.000003'

查看指定binlog文件

文件格式如下

3. Mysql双主搭建

版本8.0.25

my.cnf配置如下

[mysqld]
port=3308
server_id=123
default_authentication_plugin=mysql_native_password
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
symbolic-links=0
explicit_defaults_for_timestamp=true
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

binlog_cache_size=1M
binlog_format=mixed
binlog_expire_logs_seconds=259200
max_binlog_size=500M
slave_skip_errors=1062
log_slave_updates=1
lower_case_table_names=1
log-bin=mysql-bin
replicate-ignore-db=mysql,information_schema
sync_binlog=0
bind-address=0.0.0.0
skip-name-resolve
skip_ssl

auto_increment_offset=1
auto_increment_increment=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake=true

max_connections=1000
innodb-force-recovery=0

gtid-mode=on   #GTID开启
enforce-gtid-consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
innodb_flush_log_at_trx_commit=0
relay_log_recovery=1

[client]
port=3308
socket=/var/run/mysqld/mysqld.sock
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

两台mysql配置只有server_id不同，如果两台mysql部署在同一服务器，须再设置端口port不同，配置完成后启动两台mysql

1、登录两台mysql，都创建相应用来主从复制的用户

CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'ws-123456';
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'ws-123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'repl'@'%';
flush privileges;

2、登录第一个节点（3308节点）

show master status; -- 查看当前节点binlog日志信息，主要是获取当前binlog日志名和偏移量

执行下边的命令，将当前mysql设置为3309的从节点

change master to
master_host='lxy-mysql-one',
master_port=3309,
master_user='repl',
master_password='ws-123456',
master_log_file='mysql-bin.000004', -- 上边status命令查出来的日志名
master_log_pos=6534; -- 上边status命令查出来的日志偏移量


--如果是GTID模式，不用查偏移量使用下边进行主从关系设置
change master to 
master_host='192.168.149.104',
master_port=3309, 
master_user='repl',
master_password='ws-123456',
MASTER_AUTO_POSITION=1;

启动主从复制

start slave;

检查主从复制状态，如果红圈都为YES则正常

show slave status ;

3、同理登录第二个节点（3309节点），同第2步操作，设置3309为3308的从节点，show slave status正常后即搭建完成

另：移除同步配置如下命令

stop slave; -- 停止主从复制

reset slave all; -- 移除主从设置的所有信息

4. Mysql双主解决数据回环

如何解决数据回环

主从复制模式下，是master写入binlog，slave接收master的binlog，写入relaylog，slave消费relaylog从而达成数据一致，slave只读不写，可以关闭binlog

而双主模式下，双主都可写，也就是都会产生binlog（需要开启binlog和log_slave_updates参数），那么master1插入数据，产生binlog，master2写入relaylog消费后又会写入binlog，推送至master1，如果没有特殊处理，那么这一条sql岂不是开始无限循环？

解决这样无限循环可以有以下几个方式

1、master2写入relaylog消费后不写binlog，那么循环即终止。也就是只有从客户端连接执行的sql写binlog，后台relaylog执行不写binlog。

使用log_slave_updates参数配置即可实现relaylog执行不写binlog，但是这样做如果master1和master2自身有从节点，即集群是双主双从架构，这样会造成master1和对应slave数据不同步

2、master2写入relaylog、写入binlog之后，不推送该条sql到master1。

根据binlog格式可以看出，binlog中记录了该条sql从哪个server来（server_id），这也是为什么双主server_id必须不同的原因，那就只推送server_id是当前节点id的binlog，但是这样也不适合双主双从架构

3、master2写入relaylog、写入binlog之后，推送该条sql到master1，master1端根据某个标识不执行重复sql。

猜测是根据serverid判断是否重复

4.1 双主同步测试一

1）3308节点初始状态

binlog偏移量为253

binlog日志初始为空

同步状态，已读取3309节点最新的binlog偏移量为253，当前节点relaylog偏移量为373

2）3309节点初始状态

binlog偏移量为253

binlog日志初始为空

同步状态，已读取3308节点最新的binlog偏移量为253，当前节点relaylog偏移量为373

3）在3308节点执行插入

4）观察3308节点变化

插入语句产生了binlog，偏移量更新到了573

产生了binlog

5）观察3309节点同步状态变化

已读取到3308节点binlog日志最新偏移量为573，3309的relaylog偏移量也有了更新

3309节点相应的也产生了binlog，更新了偏移量和日志文件，其中插入语句的server_id是123，是3308节点的id

6）观察3308节点同步状态变化

发现3308节点读到了3309节点最新的binlog偏移量，但是relaylog偏移量没有变动

4.1.1 测试总结

3308插入执行后产生了binlog，3309节点的slave状态显示binlog偏移量更新，即读取到了3308最新的binlog，relay日志偏移量变大，这是正常的主从同步完成，3309写入之后自身binlog偏移量更改，3308节点的slave状态显示binlog偏移量更新，即读取到了3309最新的binlog，但是relay日志偏移量不变。

这表示是在3308节点中IO线程读取到了3309节点这个重复的binlog，但是没有写入relaylog，根据binlog格式判断是根据Server_id进行过滤从而忽略了重复sql。

但是这个测试表现还不能确定是server_id起了主要作用，因为默认下GTID模式开启（gtid-mode=on，8.0.25），在binlog中也有gtid存在，也可能mysql是根据gtid进行的重复sql判断，虽然可能不大，但是还是要严谨看测试二

4.2 双主同步测试二

猜想：3308在接收到来自3309节点的重复binlog之后是根据serverId进行过滤，从而不写入relaylog，达到一个打破数据回环的效果

测试场景：首先关闭GTID模式，避免GTID对测试效果产生影响，其次在3308执行语句之后，假如修改3308节点的server_id，那么是不是就不能打破回环，接着会产生数据循环问题

1）两个节点都设置GTID模式关闭

SET @@GLOBAL.GTID_MODE=OFF 或者 my.cnf文件中设置gtid-mode=off
-- 查看参数确定已经关闭
SELECT @@GTID_MODE

2）将测试一中节点状态重置

stop slave;
reset slave all;
reset master;

3）重新搭建双主，略

show master status;

change master to
master_host='192.168.149.104',
master_port=3309,
master_user='repl',
master_password='ws-123456',
master_log_file='mysql-bin.000001', -- 上边status命令查出来的日志名
master_log_pos=156; -- 上边status命令查出来的日志偏移量

start slave;

-- 检查同步状态，测试插入是否同步等
show slave status;

4）在3308节点上执行如下sql，先停止3308节点从3309节点的同步，然后执行插入sql

stop slave;
insert into approve.table1 (id, name) values (999, '999');

3308节点binlog:

show binlog events in 'mysql-bin.000001';

3309节点binlog:

可以看到这条sql已经写入3308节点binlog，同样也写入了3309节点的relaylog和binlog，但是目前3309到3308的同步是关闭的，3309的binlog还没推送到3308

5）修改3308节点的serverId，开启同步

set global server_id=888;
start slave;
show binlog events in 'mysql-bin.000001';

3308节点binlog:

3309节点binlog:

数据回环的效果已经出现，binlog在疯狂的增加，relaylog也来者不拒，不断地执行这条sql，表里都是重复数据

4.2.1 测试总结

解决数据回环主要就是IO线程在拉取到binlog之后根据server_id进行过滤，如果该binlog的serverId与自己相同，那么不计入relaylog，从而解决回环问题。

那GTID在其中又做了什么呢，开启GTID模式，重新走一遍测试二的流程

4.3 双主同步测试三

1）启动GTID模式

SET @@GLOBAL.GTID_MODE=ON 或者 my.cnf配置gtid-mode=on

2）两个节点重置测试二状态

stop slave;
reset slave all;
delete from approve.table1;
reset master;


-- 还需要把3308的serverId改回原来的id
set global server_id=123;

3）重新搭建双主，略，使用MASTER_AUTO_POSITION=1来使用GTID模式搭建主从

change master to
master_host='192.168.149.104',
master_port=3309,
master_user='repl',
master_password='ws-123456',
MASTER_AUTO_POSITION=1;


start slave;
show slave status;

4）在3308节点上执行如下sql，先停止3308节点从3309节点的同步，然后执行插入sql

stop slave;
insert into approve.table1 (id, name) values (999, '999');

show binlog events in 'mysql-bin.000001';

3308节点状态：

3308节点relaylog日志：

3309节点binlog状态：

5）修改3308节点的serverId，开启同步

set global server_id=888;
start slave;

show binlog events in 'mysql-bin.000001';

发现3308节点的binlog并没有任何变化，相应的3309节点更没有变化

6）再来观察3308节点的slave状态，relaylog日志进行了滚动，之前是00002，变成了00003文件

滚动是因为停止了同步（每次stop slave;start slave; relaylog都会进行滚动）

3308节点的relaylog状态如下，可以看到relaylog没有记录这条sql，但是有一条Rotate记录，也就是说GTID起了作用，使得IO线程跳过了这条sql不记录relaylog，再将relaylog的position更新为了最新的binlog位点

4.3.1 测试总结

在开启了GTID模式的情况下，在写入relaylog时除了根据serverId过滤，还会根据gtid进行过滤，已经执行过的gtid不再记录到relaylog，以此打破回环

关于Mysql双主搭建的方法步骤的文章就介绍至此，更多相关Mysql双主搭建内容请搜索编程宝库以前的文章，希望以后支持编程宝库！

下一节：MySQL导出sql脚本文件操作指南

MySQL教程

前言sql脚本文件在我们做项目时，特别是学习别人的开源项目时经常需要进行导入导出操作，才能在自己的系统上跑起来，这篇文章主要介绍如何导出sql脚本文件，具体操作如下，附带截图详解。 ...

一键清空(重置)本地MySQL8.0密码脚本

MySQL导出sql脚本文件操作指南