首页 > 数据库 >14-Mysql主从复制

14-Mysql主从复制

时间：2023-12-27 14:55:20浏览次数：43  
标签：主库 主从复制 slave 14 Mysql 线程 mysql 日志 从库

一、mysql复制原理

image

1.1 主从复制原理过程

  • 从库的I/O thread 线程会读取master info 文件 获取主库的 user,password port信息然后还会获取上次获取主库二进制日志的位置 如3640 就是00003这个文件640这个位置，主库收到从库的请求后，会验证用户名密码等的合法性，然后问主库你有没有比上次00003文件640这个位置更加新的二进制日志，然后主库就会查看自己的binglog日志，如果发现比640这个新，比如已经到达00003文件1080这个位置了，主库就会把00003号文件的640这个位置到1080这个位置的binglog日志截断，通过dump(i/o)线程返回给从库的I/O thread线程，到从库之后，它会先存到tcp/ip缓存当中(tcp/ip cached)，然后从库会立即发送一个ack给主库，主库收到ack后就认为这个过程已经完成，就可以去干别的事情了，此时从库会更新mast info的信息，把binglog的位置信息更新到1080，下次就从1080开始往下找，然后再把tcp/ip缓存的日志写入到relaylog当中，最后sql thread线程会读取relay-log.info,获取到上次执行binglog日志的位置信息，比如发现上次以及执行到640这个位置了，sql就会读取relaylog从640的位置开始执行二进制日志，当执行完后，最后更新relay-log.info文件，记录最后执行的位置，最后，relaylog会自动把已经执行过的二进制日志清理掉这样一次复制就完成了

  • 复制中的线程及文件

2.1、主库
Dump（IO） thread：在复制过程中，主库发送二进制日志的线程
2.2、从库
IO thread：向主库请求二进制日志，并且接受二进制日志的线程
SQL thread：执行请求过来的二进制的线程
2.3、主库
binlog文件：主库的二进制日志
2.4、从库
relaylog:中继日志，存储请求过来的二进制日志
master.info:
	1、从库连接主库的重要参数（user,passwd,ip,port）
	2、上次获取过的主库二进制日志的位置
relay-log.info
	存储从库SQL线程已经执行过的relaylog日志位置

二、mysql 主从搭建

2.1 主从复制前提

1、两台以上MySQL实例（可以是多台物理机，也可是mysql实例）
2、主库要开启二进制日志
3、主库要提供复制相关的用户需要用到 replication slave一个比较特殊的权限
4、从库需要将和主库相差的数据进行追加，一般情况下认为备份数据库，恢复到从库上
5、从库应该从恢复后的时间点开始自动从主库获取二进制日志开始自动同步主库数据，我们需要告诉从库，从哪儿开始复制二进制日志进行学习

2.2 主从复制搭建实战

1、环境准备

 两个MySQL实例
 3307：master
 3308：slave

2、开启主库binlog,从库开启relay-log(默认在数据目录下生成)

vim /data/3307/my.cnf
log-bin=/data/3307/mysql-bin
binlog_format=row

3、server-id不同

[root@db02 data]# cat /data/3307/my.cnf |grep server-id
server-id=3307
[root@db02 data]# cat /data/3308/my.cnf |grep server-id
server-id=3308

4、关闭数据库自动域名解析（没个节点实例都加）

skip-name-resolve

5、启动多实例

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &

6、主库创建复制账户连接到主库

mysql -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

7、从库数据追加

（1）不需要追加的情况
主和从同时搭建的新环境，就不需要备份主库数据，恢复到从库了，直接从第一个binlog(mysql-bin.000001)开头位置（120）

（2）如果主库已经工作了很长时间了，我们一般需要备份主库数据，恢复到从库，然后从库从备份的时间点起自动进行复制

mysqldump -S /data/3307/mysql.sock -A -R  --triggers --master-data=2 --single-transaction >/tmp/full.sql
sed -n '22p' /tmp/full.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=325
  • 恢复到从库：
mysql -S /data/3308/mysql.sock 
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql

8、从库开启主从

mysql -S /data/3308/mysql.sock

help change master to

CHANGE MASTER TO
  MASTER_HOST='10.0.0.52',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=325;
  start slave    ---> 实际上开启主从是（开启IO/SOL线程）

9、查看主从状态

show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

io线程和sql线程yes表示主从复制正常

三 、 主从复制常见异常解决思路

主从重要状态信息介绍

show slave status\G
Slave_IO_Running: Yes（io线程状态）
Slave_SQL_Running: Yes（sql线程状态）
Last_IO_Errno: 0（io线程异常状态码）
Last_IO_Error: （io线程异常详细信息）
Last_SQL_Errno: 0（sql线程状态码）
Last_SQL_Error: （sql线程异常详细信息）

3.1 IO线程故障

1、主库连接不上

检查user，password，port，IP，网络，防护墙
stop slave;
reset slave all;(清空配置)
chagen master to
start slave

2、主库二进制文件丢失或损坏

解决方案；
stop slave;
reset slave all;
从新备份恢复
change master to
start slave;

3.2 SQL线程故障

执行relaylog日志新新的事件
1、删除，修改对象的操作时，没有这个对象
2、创建对象时，对象已存在
3、主键冲突

总结：从库先于主库做写入操作，会导致以上问题出现

处理方法跳过这个错误

stop slave;
set global sql_slave_skip_counter=1; 或者 set global slave_exec_mode='IDEMPOTENT';
start slave;
/etc/my.cnf
slave-skip-errors=1032,1062,1007

说明：但是，以上操作有的时候时候是有风险的，最安全的方法是从新构建新的主从
如何预防？

修改从库为只读库

set global read_only=1;
vim /etc/my.cnf
read_only=1(这个参数只能控制普通用户)

3.3 主从延时过长

show slave status \G
Seconds_Behind_Master:0 -->一分钟以上可能会有问题

说明：默认的主从复制是异步的过程

主从延时解决思路

  • 主库原因
1、主库做修改操作之后，才会记录二进制日志
2、主库的压力特别大（大事务，多事物）
3、从库数量多，导致domp线程繁忙
  • 从库原因：
1、relay-log写入慢
2、sql线程慢（主从硬件差异较大）
  • 解决思路
主库
1、sync_binlog=1(1表示只要主库做了一次commit，二进制日志就会立刻刷新到磁盘，如果等于0要根据系统binlog决定)
2、大事物拆分成小事物，多事物进行分离
3、使用多级主从，分库分表架构
4、将binlog放在ssd或者flash上，高性能存储
从库
1、将relay放到ssd或者flash上
2、尽量选择和主库一样的硬件配置

四、主从复制其他特性

4.1 半同步复制

1、出现原因

保证数据一致性问题，安全考虑
默认情况下MySQL主从备份是通过异步的方式进行备份，当从库向主库请求二进制日志，从库会把请求过来的二进制日志存到tcp/ip缓存当中，返回一个ack告诉主库接收到了二进制日志，试想一下当这个时候从库突然断电了，存到tcp/ip缓存当中的二进制日志就会丢失，而主库还也以为从库正常接收到了二进制日志，从库的数据就会丢失，半同步的实现方法就是当从库接收到二进制日志后必须要存到relaylog落地到磁盘当中从库才会发送ack给主库，这样就保证了数据的一致性
半同步复制是在5.5出现的概念，但5.5的半同步复制性能太差不建议使用，在5.6以后出现group commit 组提交功能，来提升开启版同步复制的性能，5.7 增强半同步复制的新特性：after sync;

2、具体实现

加载插件一个插件即可

主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加载成功:
show plugins;

启动:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;

从:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

查看是否在运行

主:
show status like 'Rpl_semi_sync_master_status';
从:
show status like 'Rpl_semi_sync_slave_status';
-----

补充：

rpl_semi_sync_master_timeout       | 10000
默认情况先,到达10秒钟还没有ack，主从关系自动切换为普通复制
如果是1主多从的半同步复制，只要有一台落地relaylog，返回ack，这次半同步就完成了。

4.2 从库延时

防止数据库的逻辑损坏，假设在主库不小心误删除了一个表，这个事件会同样记录到二进制日志当中发送到从库，这时候从库不会立即执行这个 操作，比如配置了延时3小时，从库会在3小时后才执行删除表的操作，这样就给我们运维人员一些缓冲的时间，延时从库是对sql线程的延时,不会影响到io线程

生产会专门找一个节点，配置成延时节点，尽可能防止逻辑损坏，一般情况下这个节点会被用备份

  • 配置实现

SQL_thread的延

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 60;  -->延时一分钟

mysql>start slave;

mysql> show slave status \G
SQL_Delay: 300

取消延时：

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 0; -->设置为0 即可
mysql> start slave;

4.2 复制过滤

出现原因，有得时候我们不想复制主库的所有数据，只想复制一个库或者一个库某些表

  • 主库方面控制（不建议使用）：

    • 白名单:只记录白名单中列出的库的二进制日志
      binlog-do-db
    • 黑名单：不记录黑名单列出的库的二进制日志
      binlog-ignore-db

  • 从库方面控制：

白名单：只执行白名单中列出的库或者表的中继日志

--replicate-do-db=test （哪个库）
--replicate-do-table=test.t1 （哪个库的哪个表）
--replicate-wild-do-table=test.x* （模糊的匹配）

黑名单：不执行黑名单中列出的库或者表的中继日志

--replicate-ignore-db
--replicate-ignore-table
--replicate-wild-ignore-table

例子

只复制world数据库的数据
在从库配置

vi /etc/my.cnf
replicate-do-db=world
重启从库

查询：show slave status \G
replicate-do-db = world

五、 主从复制新模式-GTID复制

  • GTID是从5.6之后新的复制特性，以前的复制模式是当主库发生了任何方式的变化，会已事件的形式记录到binlog日志当中，每个事件生成一个position号，然后从库去获取这些二进制事件，而GTID是把每个完整的事物单独的生成一个全局唯一的编号，这样就简化了主从复制的配置，实现过程相对也简单写，在事务补偿具有优势
    它的官方定义如下：
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
前半段为UUID，后半段为事物的编号，存放在auto.cnf下

5.1 配置实现

1、重要参数

gtid-mode=on （是否开启GTID）
enforce-gtid-consistency=true （强制GTID的一致性）
log-slave-updates=1  (slave更新是否写入日志)

2、规划

主库： 10.0.0.51/24
从库1: 10.0.0.52/24
从库2：10.0.0.53/24

3、写入配置文件

主库：
加入以下配置信息

db01:10.0.0.51/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=51
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave1：
db02:10.0.0.52/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

slave2：
db02:10.0.0.53/24

vim /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=53
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/mysql.sock

3、台节点分别初始化数据：

/application/mysql/scripts/mysql_install_db --user=mysql  --basedir=/application/mysql --datadir=/application/mysql/data/

分别启动三个节点mysql：

/etc/init.d/mysqld start

测试启动情况：

mysql -e "show variables like 'server_id'"

4、 配置主从

在主库中创建复制用户51

grant replication slave  on *.* to repl@'10.0.0.%' identified by '123';

在从库中配置master to信息并启动

change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;

start slave;

标签：主库,主从复制,slave,14,Mysql,线程,mysql,日志,从库
From： https://www.cnblogs.com/ejjw/p/17930568.html

相关文章

  • 04-Mysql多实例
    多实例就是多套线程和多各进程和多个预分配的内存结构配置思路启动多个mysqld进程规划多套数据规划多个端口规划多套日志路径配置例子1、创建多套目录mkdir-p/data/330{7,8,9}2、准备多套配置文件vi/data/3307/my.cnf[mysqld]basedir=/application/mysqldatadi......
  • 05-Mysql 用户管理
    一、MySQL用户管理用户定义：user主机范围使用某个用户从哪个（些）地址访问我的数据库用户的功能：1、用来登录mysql数据库2、用来管理数据库对象（库、表）权限功能：针对不同用户设置对不同对象管理能力selectupdatedeleteinsertcreatedrop。。。权限范围：......
  • 01-Mysql介绍及安装
    关系型数据库的特点二维表典型产品Oracle传统企业，MySQL是互联网企业数据存取是通过SQL最大特点，数据安全性方面强（ACID）•NoSQL：非关系型数据库（NotonlySQL）不是否定关系型数据库，做关系型数据库的的补充想做老大，先学会做老二•NoSQL特性总览–不是否定......
  • 在windows下安装mysql 8.1
    1、下载并解压官网下载mysql8,https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-winx64.zip解压到D:\mysql，以下称为根目录2、编写配置文件在根目录下新建my.ini文件，配置以下内容[mysqld]#设置3306端口port=3306#设置mysql的安装目录，一定要与上面的安装路......
  • 02-Mysql体系结构
    一、MySQL服务器连接模型2、应用程序如何连接到mysql2.1tcp/ip的方式mysql-uroot-poldboy123-h10.0.0.2002.2套接字的方式mysql-uroot-poldboy123-S/tmp/mysql.sock二、MySQL服务器构成——实例连接层sql层处理流程解析器（执行计划）--优化器（选择比......
  • 03-MySQL基本管理
    一、数据库连接管理mysql-uroot-poldboy123#隐藏条件-S默认socket方式mysql-uroot-poldboy123-h10.0.0.52-P3308#tcp/ip的方式mysql-uroot-poldboy123-S/application/mysql/tmp/mysql.sock#socket方式mysql-uroot-poldboy123-e"showvariableslike......
  • 14通道自动灵敏度校准低功耗电容触摸传感器芯片Si314
    刷卡解锁、一步开门、远程监测、遇到风险自动宣布警报、智能联动等人们关于门锁各种看似遥不可及的梦想，因为智能锁的呈现一一变成实际。由于智能门锁的不断进化，人们关于智能家居也有了更多梦想和期待。将触摸屏引入智能门锁交互，让用户在智能锁的体会上更安全、更便利、更个性化。......
  • 2.3T NPU强势登场！NXP i.MX 8M Plus开启工业新篇章，14纳米！
            更多产品详情以及购买咨询可添加如下客服人员微信 （即刻添加，马上咨询） 更多i.MX8MPlus产品资料可长按二维码识别下载如需选购，请登录创龙科技天猫旗舰店:tronlong.tmall.com!欢迎加入i.MX8MPlus技术交流群：1064661665......
  • 14.并列句-考点分析-长难句分析
    长难句分析——在分析长难句的时候只要见到有并列连词的出现通常会有省略;分析长难句第二步找连词，翻译把省略补齐在翻译。但是当连词连接2个单词时当做每看见。eg.Iwasbeatenandyou（werebeaten是省略的部分）.如何查找省略的内容呢？——一句话只要有省略，就一定省略在连......
  • Mysql根据字段值的长度查找过滤，排序等
    Mysql根据字段值的长度查找过滤，排序等http://www.shanhubei.com/archives/5882.html1.Mysql根据字段的指定长度搜索过滤SELECT*FROMuserWHEREis_deleted=0ANDlength(name)>52.添加普通索引ALTERTABLE'table_name'ADDINDEXindex_name('column')3.在表中某一列......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99