导航：首页 > 数据库 >

MySQL 主从的一些基础知识

发表于：2025-01-20 作者：千家信息网编辑

千家信息网最后更新 2025年01月20日，本篇文章给大家分享的是有关MySQL 主从的一些基础知识，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。MySQL复制原理1. 主库修改时

千家信息网最后更新 2025年01月20日MySQL 主从的一些基础知识

本篇文章给大家分享的是有关MySQL 主从的一些基础知识，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

MySQL复制原理

1. 主库修改时会把数据变更作为event记录在binlog中,主库上的sync_binlog参数控制binlog日志刷新到磁盘.

2. 主库推送binlog 中的事件到从库的中继日志relay log，之后从库根据中继日志relay log重做数据变更操作。通过逻辑复制达到主从同步。

MySQL通过3个现成来完成主从数据库之间的复制:

1.binlog dump 跑在主库上，负责读取数据库事件并发送给I/O线程

2.I/O线程跑在从库上，当从库start slave时创建I/O线程链接到master 将主库的事件写入到relaylog

3.SQL线程跑在从库上，负责读取relay log并更新从库。

4.Master.inf and relay-log.info 用来保存复制的进度。

三种复制方法, MIX,STATEMENT,ROW

1. statement

基于语句的复制,可能会因为存储过程或触发器,时间函数导致主备不一致，数据量小。

2. row

行级数据复制,缺点是数据量较大，但是不会因为触发器等外在条件影响数据

3. Mix

默认采用statement模式如果语句中包含更新时间，用户相关的函数等

Mysqlbinlog -vv logfilename -base64-output=DECODE-ROWS -start-pos=number 才能解析row模式的内容。

双主复制架构：

由master1 作为读写库，master2 作为只读库。

同时双向作为对方的slave。

当维护时

1. 先停掉master1上的slave。免得当master2 做维护时影响到master1.

2. 停掉master2 上的slave。开始维护操作，比如创建索引。

3. 打开master2上的slave 让master2 与master1 同步。

4. 将读写操作转移到master2 ，确保master1 上无写入操作

5. 将master1 的slave打开，让其与salve2 同步完成。

搭建mysql复制：

1. 建立一个用于replication的账号：

Create replac slave on *.* TO 'repl'@'192.168.7.200' identified by '1234test';

2. 修改my.cnf 开启binlog 并且设置server-id的值
log-bin = xxxxx.log
server-id=1

3. 锁住所有的表，lock tables with read lock

4. 检查当前日志偏移量 show master status

5. 备份或者通过直接shutdown数据库拷贝数据文件的方式到slave库。

6. Unlock tables;

7. 修改slave的my.cnf server-id=2

8. 使用延迟复制开启salve ./bin/mysql_safe -skip-slave-start&

9. 对数据库服务做响应设置
mysql>change master to master_host='',master_user='',master_password='',master_logfile='',master_log_pos='';

10. 从库上输入:start slave;

11. show process\G检查slave进程

12. show variables like '%sync_binlog%' 0的表示mysql不控制binlog的刷新，由文件系统控制。

13. 最后注意的几点就是MHA架构可以通过自动抽取缺失的log进行catchup。也可以通过mysqlbinlog工具来抽取之前主库为宕机时未抽取的log 传送到备库上去。

半同步复制:

在mysql 5.5之前，由于主备库之间都处于异步复制的状态，如果主库在commit了之后而崩坏，此时binlog尚未传到slave的库上。则会丢失数

而5.5之后mysql推出了半同步复制状态。在默认状态下mysql需要保证有任意一台备库的relaylog 接收到的更改之后才会确认commit已经成功。如果中途断网的话则会再自动切换回半同步复制模式。

安装:
1.首先需要确认mysql是否可以使用该功能,select @@have_dynamic_loading =>YES

2到$MYSQL_HOME/lib/plugin/下找到主库/从库插件semisync_master/slave.so

3.主备库上安装该插件 install plugin rpl_semi_sync_master/salve soname 'semisync_master/slave.so'

4.select * from mysql.plugin

5.在主备库上设置全局参数打开半同步set global rpl_semi_sync_master_enabled=1;

Set global rpl_semi_sync_master_timeout=3000;

从库上
set global rpl_semi_sync_slave=1;

6如果已经启动了异步复制需要停止一下io_thread. Stop slave io_thread; start slave io_thread;

Show status like '%semi_sync%'

需要注意的几个结果有:

rpl_semi_syc_master_status ON 代表打开;

rpl_semi_syc_master_yes/no_tx 表示同步或非同步的transaction次数

rpl_semi_syc_master_timeout 标示等待多久会判断slave超时。

如果从库断开后重新连接上了，会自动转回半同步状态。

Mysql replication 一些有用的参数：

1． Log-slave-updates 从库是否需要记binlog 这个对于从库传从库是比较有帮助的

2． Master-connect-retry 在从库和主库失去连接时的重试间隔默认60秒

3． ./bin/Mysqld_safe -read-only 让以避免超级用户以外的人修改从库的数据

日常维护命令:

1. 查看从库状态 show slave status.比较关心的2状态一个是SLAVE_IO_Running和Slave_SQL_Running.

2. 有时候主备库差距越来越大，可以在空闲的时候使用flush tables with read lock.
show master status 查看binlog的position。接下来使用select master_pos_wait('logiflename','log_pos'); 这个语句会阻塞直到从库同步到该点。然后再解锁表。

3. 从库出错：

a) 如果需要跳过某些导致replication失败的语句可以使用set global SQL_SLAVE_SKIP_COUNTER=N. N=1|2. 当主库没有自增长时使用1 自增长用2.

b) Log event entry exceeded max_allowed_packet 一般是因为有长字符串或blob无法传输。此时需要增加主库上该参数的大小。

c) 多主复制时自增长变量冲突的话在双主上需要设置auto_increment_increment=2,auto_increment_offset=1;(1,3,5,7….) auto_increment_increment=2,auto_increment_offset=0;(2,4,6,8….)