数据库 18

MySQL中从库延迟状况排查的一则案例,主从复制面试宝典【数据库】

MySQL中从库延迟状况排查的一则案例,mysql排查

今天给一个客户巡检的情况下发从库没有业务的情况mysqld的cpu的一个core占用100%.查主库慢查询也没有关于写的SQL.
可以说是典的单进程复制把一个cpu占满造成的.知道原因了,就好分析了.
分析一下binlog中写的什么,看看有什么地方可以优化或是加速的.利用工具:pasrebinlog
利用show slave status\G; 查当前同步的到节点,然后对日值进行解析.

git clone https://github.com/wubx/mysql-binlog-statistic.git
cd mysql-binlog-statistic/bin/
parsebinlog /u1/mysql/logs/mysql-bin.000806

...
====================================
Table xx_db.xxtable:
Type DELETE opt: 101246
Type INSERT opt: 103265
================================
...

 

以最大的数排序看, 定位到: xx_db.xxtable,对于一个日值中能删除10几万,写入10几万.是不是这个表写入比较慢了呢.
在从库上查看innodb的相关情况:

MySQL> show engine innodb status\G;
...
---TRANSACTION 1C0C2DFDF, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
3361 lock struct(s), heap size 407992, 477888 row lock(s), undo log entries 42
MySQL thread id 43, OS thread handle 0x7fc1800c4700, query id 1908504 Reading event from the relay log
TABLE LOCK table xx_db.xxtable trx id 1C0C2DFDF lock mode IX
RECORD LOCKS space id 1002 page no 1975 n bits 1120 index `AK_movieid` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6965 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6967 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6973 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6982 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6983 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6987 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6999 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 7000 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS
----------------------------
END OF INNODB MONITOR OUTPUT

...

从Innodb 的monitor output 中也可看到 xx_db.xxtable
这表已经是表级表了,造成并发比较低,而且有大量的: GEN_CLUST_INDEX 而且属于一个事务.  GEN_CLUST_INDEX表示没有主建,内部产生一个主建,对于内部产生的主建很很容易造成page拆分的操作.

问题到这里基本上可以得到解决问题的方法了:
给xx_db.xxtable 添加一个主建即可.这里后是给xx_db.xxtable
添加了一个无业务意义的id int 自增主建.这样立马可以看到mysqld占用的cpu单核降到了3%左右, 同时后续同步一切正常,观查一天没出现同步延迟的问题.

今天给一个客户巡检的情况下发从库没有业务的情况mysqld的cpu的一个core占用100%.查主库慢查…

MySQL> show engine innodb status\G;
...
---TRANSACTION 1C0C2DFDF, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
3361 lock struct(s), heap size 407992, 477888 row lock(s), undo log entries 42
MySQL thread id 43, OS thread handle 0x7fc1800c4700, query id 1908504 Reading event from the relay log
TABLE LOCK table xx_db.xxtable trx id 1C0C2DFDF lock mode IX
RECORD LOCKS space id 1002 page no 1975 n bits 1120 index `AK_movieid` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6965 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6967 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6973 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6982 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6983 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6987 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6999 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 7000 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS
----------------------------
END OF INNODB MONITOR OUTPUT

...

重启mysql服务

以最大的数排序看, 定位到: xx_db.xxtable,对于一个日值中能删除10几万,写入10几万.是不是这个表写入比较慢了呢.
在从库上查看innodb的相关情况:

1.
负载均衡,由于复制的时间差,不能保证同步读,而且写仍然单点,没法多点写,我对这个理解就是半吊子的读写均衡。

git clone https://github.com/wubx/mysql-binlog-statistic.git
cd mysql-binlog-statistic/bin/
parsebinlog /u1/mysql/logs/mysql-bin.000806

...
====================================
Table xx_db.xxtable:
Type DELETE opt: 101246
Type INSERT opt: 103265
================================
...

数据库 1数据库 2

从Innodb 的monitor output 中也可看到 xx_db.xxtable
这表已经是表级表了,造成并发比较低,而且有大量的: GEN_CLUST_INDEX 而且属于一个事务.  GEN_CLUST_INDEX表示没有主建,内部产生一个主建,对于内部产生的主建很很容易造成page拆分的操作.

在主服务器创建一个数据库

问题到这里基本上可以得到解决问题的方法了:
给xx_db.xxtable 添加一个主建即可.这里后是给xx_db.xxtable
添加了一个无业务意义的id int 自增主建.这样立马可以看到mysqld占用的cpu单核降到了3%左右, 同时后续同步一切正常,观查一天没出现同步延迟的问题.

  1. 容灾,基本都是有损容灾,因为数据不同步,谁用谁知道,半吊子的容灾。

您可能感兴趣的文章:

  • 利用Python绘制MySQL数据图实现数据可视化
  • MySql
    insert插入操作的3个小技巧分享
  • Linux
    shell实现每天定时备份mysql数据库

一、什么是主从复制:

 

数据库 3

今天给一个客户巡检的情况下发从库没有业务的情况mysqld的cpu的一个core占用100%.查主库慢查询也没有关于写的SQL.
可以说是典的单进程复制把一个cpu占满造成的.知道原因了,就好分析了.
分析一下binlog中写的什么,看看有什么地方可以优化或是加速的.利用工具:pasrebinlog
利用show slave status\G; 查当前同步的到节点,然后对日值进行解析.

不妨积极思考留下评论,这才是最后的干货。顺便求个关注。本人公众号也是这个名字【java进阶架构师】

可能只是提供一种成本较低的数据备份方案加不完美的容灾和负载均衡吧,这种方案注定是一种过渡方案,个人认为必须更新了。当然,在不是体量巨大的情况下,还是不失为一个优化的解决办法。

数据库 4

2、架构的扩展。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。

二、主从复制的作用(好处,或者说为什么要做主从)重点!:

五、面试题干货分析(如果问到数据库主从问题,必问以下问题):

2、主从的原理是?

内容较多,可先收藏,目录如下:

新建密码为masterbackup的masterbackup 用户,并赋予replication slave 权限

relay-log-index = slave-relay-bin.index

数据库 5

5.具体需要三个线程来操作:

从库I/O线程。当START
SLAVE语句在从库开始执行之后,从库创建一个I/O线程,该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件,其中包括relay
log文件。

三、从主从服务器测试结果

步骤五:还会创建一个SQL线程,从relay
log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db

主从复制如图:

五、必问面试题干货分析

三、主从复制的原理

二、Slave从服务器配置上的配置(103.251.237.45)

2.登陆主服务器mysql 创建从服务器用到的账户和权限;

一、Master主服务器上的配置(103.251.237.42)

暂时不清楚这是做什么的。加入这两条。

1.log_bin 启动MySQL二进制日志

三、主从复制的原理(重中之重,面试必问):

binlog输出线程。每当有从库连接到主库的时候,主库都会创建一个线程然后发送binlog内容到从库。

登陆mysql,停止同步命令

数据库 6

可以看到用户masterbackup 已经添加

还不懂?没关系,这图也一样:

在[mysqld]中

记录 mysql-bin.000007 以及 276,编写以下命令待用;

查看Slave信息;如图两句都为yes,则状态正常

数据库 7

数据库 8

change master to
master_host=’103.251.237.42′,master_port=3306,master_user=’masterbackup’,master_password=’masterbackup’,master_log_file=’mysql-bin.000007′,master_log_pos=276;

3.查看主数据库的状态

数据库 9

二、主从复制的作用

4、做主从后主服务器挂了怎么办?

1.数据库有个bin-log二进制文件,记录了所有sql语句。

relay-log = slave-relay-bin

3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。

可以知道,对于每一个主从复制的连接,都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程,每一个从库都有它自己的I/O线程和SQL线程。

可以查到,主从服务器配置完成。当然,还有主主复制,如果有感兴趣的朋友可以留言。

4.下面的主从配置就是围绕这个原理配置

数据库 10

数据库 11

数据库 12

数据库 13

server-id = 1 中 1
是可以自己定义的,但是需要保持它的唯一性,是服务器的唯一标识

一、什么是主从复制

在从库里,当复制开始的时候,从库就会创建两个线程进行处理:

在从服务器上查看刚才创建的数据库

3、读写分离,使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢,导致锁表,影响前台服务。如果前台使用master,报表使用slave,那么报表sql将不会造成前台锁,保证了前台速度。

在[mysqld]中注释掉 bind-address = 127.0.0.1 不然mysql无法远程

1.编辑my.cnf(命令查找文件位置:find / -name my.cnf)

从库的SQL线程。从库创建一个SQL线程,这个线程读取从库I/O线程写到relay
log的更新事件并执行。

@之后IP可访问主服务器,这里值定从服务器IP

四、三步轻松构建主从:

主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库;主数据库一般是准实时的业务数据库。

1、做数据的热备,作为后备数据库,主数据库服务器故障后,可切换到从数据库继续工作,避免数据丢失。

2.binlog_do_db 指定记录二进制日志的数据库

注释掉 binlog_do_db 和 binlog_ignore_db ,则表示备份全部数据库

做完这些后,重启下数据库

3、从数据库的读的延迟问题了解吗?如何解决?

原理图

步骤三:此时主库创建一个binlog dump thread,把binlog的内容发送到从库

数据库 14

步骤一:主库db的更新事件(update、insert、delete)被写到binlog

其实主从复制也存在一些问题:

数据库 15

步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay
log

2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。

四、三步轻松构建主从

1.编辑my.cnf (命令查找文件位置:find / -name my.cnf)

1、主从的好处是?

步骤二:从库发起连接,连接到主库

执行用上面准备的命令; 登录Slave从服务器,连接Master主服务器:

重新启动数据同步;

数据库 16

数据库 17

数据库 18

需要知道的是,面试过程中原理不会让你讲那么久,一般的,只要把1234点讲出来,然后说简略说下三个线程,这就满分了!

3.binlog_ignore_db 指定不记录二进制日志的数据库。