位置: 编程技术 - 正文

mysql主键的缺少导致备库hang住(mysql主键冲突但数据不存在)

编辑:rootadmin

推荐整理分享mysql主键的缺少导致备库hang住(mysql主键冲突但数据不存在),希望有所帮助,仅作参考,欢迎阅读内容。

文章相关热门搜索词:mysql主键用完了,mysql没有主键,mysql没有主键,mysql主键语句,mysql里主键什么意思,mysql里主键什么意思,mysql没有主键是如何存储数据的,mysql没有主键,内容如对您有帮助,希望把文章链接给更多的朋友!

最近线上频繁的出现slave延时的情况,经排查发现为用户在删除数据的时候,由于表主键的主键的缺少,同时删除条件没有索引,或或者删除的条件过滤性极差,导致slave出现hang住,严重的影响了生产环境的稳定性,也希望通过这篇博客,来加深主键在innodb引擎中的重要性,希望用户在使用RDS,设计自己的表的时候,一定要为表加上主键,主键可以认为是innodb存储引擎的生命,下面我们就来分析一下这个案例(本案例的生产环境的binlog为row模式,对于myisam存储引擎也有同样的问题):(1).现象slave:

slave的Seconds_Behind_Master一直在增加,slave出现hang住。(2).解析当前slave执行到的位置的binlog:

(3)分析:模拟场景:1.表中无主键,全表进行更新:master:表结构:CREATE TABLE `dmpush_message_temp` (`clientid` varchar() DEFAULT NULL,`infoid` bigint() DEFAULT NULL,`endtime` varchar() DEFAULT NULL,`stat` varchar(8) DEFAULT NULL) ENGINE=innodb DEFAULT CHARSET=utf8;

mysql> update dmpush_message_temp set stat=1 ;Query OK, rows affected (1. sec)Rows matched: Changed: Warnings: 0a.binlog中第一个出现的update事务日志:mysqlbinlog -vvv /home/mysql/data/mysql/mysql-bin. >/tmp/test.log

### UPDATE qianyi.dmpush_message_temp ### WHERE ### @1='fb5cc9-0ac2--b2-bdc9e1dd′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### @2= /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3='′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### @4='0′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### SET ### @1='fb5cc9-0ac2--b2-bdc9e1dd′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### @2= /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3='′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### @4='1′ /* VARSTRING() meta= nullable=1 is_null=0 */

b.binlog中最后出现的update事务日志: ### UPDATE qianyi.dmpush_message_temp ### WHERE ### @1='fffff4fc-0b-4ba2--af6d5′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### @2= /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3='′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### @4='0′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### SET ### @1='fffff4fc-0b-4ba2--af6d5′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### @2= /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3='′ /* VARSTRING() meta= nullable=1 is_null=0 */ ### @4='1′ /* VARSTRING() meta= nullable=1 is_null=0 */注意这里由于表中没有主键,所以导致了每一个事务条目的更新都是全表扫描,如果表中很很多的数据,则备库执行该更新的事务条目的时候,就会出现很多的全表扫描更新;

c.计算有多少事务条目:root@xxxxxxxxx # cat /tmp/test.log|grep ‘UPDATE qianyi.dmpush_message_temp' -A |wc -l

mysql> select /;—-为一个update事务条目占用的行数+————-+| / |+————-+| . |+————-+

mysql> use qianyiDatabase changedmysql> select count(*) from dmpush_message_temp;+———-+| count(*) |+———-+| |+———-+可以看到,binlog的条目数和该表的数据量查不多是一致,也就是在全表更新的时候(在row模式下)更新多少行,就有多少事务的事务条目;2.为dmpush_message_temp表添加主键:mysql> alter table dmpush_message_temp add column id int not null auto_increment,add PRIMARY Key(id);Query OK, rows affected (1. sec)Records: Duplicates: 0 Warnings: 0

mysql> update dmpush_message_temp set stat=0 ;Query OK, rows affected (1. sec)Rows matched: Changed: Warnings: 0

解析binlog中的事务条目:root@xxxxxxxxx # mysqlbinlog -vvv /home/mysql/data/mysql/mysql-bin. >/tmp/test1.log

### UPDATE qianyi.dmpush_message_temp### WHERE### @1='fb5cc9-0ac2--b2-bdc9e1dd′ /* VARSTRING() meta= nullable=1 is_null=0 */### @2= /* LONGINT meta=0 nullable=1 is_null=0 */### @3='′ /* VARSTRING() meta= nullable=1 is_null=0 */### @4='1′ /* VARSTRING() meta= nullable=1 is_null=0 */### @5=1 /* INT meta=0 nullable=0 is_null=0 */### UPDATE qianyi.dmpush_message_temp### WHERE### @1='fb5bdc4f-da8a-4f-aa5e-d7c8ac3′ /* VARSTRING() meta= nullable=1 is_null=0 */### @2= /* LONGINT meta=0 nullable=1 is_null=0 */### @3='′ /* VARSTRING() meta= nullable=1 is_null=0 */### @4='1′ /* VARSTRING() meta= nullable=1 is_null=0 */### @5=2 /* INT meta=0 nullable=0 is_null=0 */可以看到这里的事务条目中由于已经有了主键,也就是@5(第一个事务条目更新和第二个事务条目更新的@5是递增的,即主键),这样事务日志就会根据主键来更新,备库执行则不会卡住;

解决:问题的原因已经找到了,由于表中没有主键,ROW模式下,每删一条数据都会做全表扫,也就是说一条delete,如果删了条,会做次全表扫。。。。所以slave一直卡住;1.slave:停止slave;mysql> stop slave;Ctrl-C — sending “KILL QUERY ” to server …Ctrl-C — query aborted.Ctrl-C — sending “KILL ” to server …Ctrl-C — query aborted.Ctrl-C — exit!Aborted2.这个时候,发现slave已经卡住,无法进行任何操作,这个时候只有强行kill掉mysql进程root@xxxxxxxx.com # ps -ef|grep root 1 0 Oct &#; :: /bin/sh /usr/bin/mysqld_safe ?defaults-file=/etc/my.cnfmysql 0 Oct &#; :: /usr/sbin/mysqld ?defaults-file=/etc/my.cnf ?basedir=/ ?datadir=/home/mysql/data/dbs ?user=mysql ?log-error=/home/mysql/data/mysql/master-error.log ?open-files-limit= ?pid-file=/home/mysql/data/dbs/xxxxxxxx.com.pid ?socket=/home/mysql/data/tmp/mysql.sock ?port=

kill -9

mysql主键的缺少导致备库hang住(mysql主键冲突但数据不存在)

由于我们的slave复制是在mysqld启动的时候自动启动,所以这里我们需要将其关闭:vi /etc/my.cnf中加入:skip-slave-start,在用mysqld_safe启动;

2.由于主库的binlog已经传入备库,这个时候,slave执行没有主键更新的事务日志就会hang住,这个时候可以采取一种巧妙的方法来避过,就是将备库中的这张表进行数据清空,slave在执行realy log的时候,就会报错误,我们写一个脚本进行skip掉这些错误,当备库追赶上主库后,我们在把主库的表通过mysqldump,或者insert select 还原到备库,这样就可以让slave正常的运行起来,然后在通知客户进行主键的改造;a。slave上执行以下命令:slave:清空备库上有问题的表set sql_log_bin=off;truncate table qianyi.dmpush_message_temp;start slave;

跳过该表上的错误:sh /tmp/skip.sh dmpush_message_temp

b.等备库追上主库后,执行以下命令:master:

lock tables qianyi.dmpush_message_temp read;

create table a2 like qianyi.dmpush_message_temp ;

lock tables a2 write, qianyi.dmpush_message_temp read;

insert into a2 select * from qianyi.dmpush_message_temp ;

slave:

set sql_log_bin=off;

drop table qianyi.dmpush_message_temp;

create table qianyi.dmpush_message_temp like a2;

insert into qianyi.dmpush_message_temp select * from a2;

c.最后让应用改造,添加上主键:mysql> alter table dmpush_message_temp add column id int not null auto_increment,add PRIMARY Key(id);

3.当slave卡住的时候,可以通过解析binlog来看看,slave到底卡住在那里,是那个事务,下面是一个简单的方法,来看当前salve打开的表:mysql> show open tables;+———-+———————+——?+————-+| Database | Table | In_use | Name_locked |+———-+———————+——?+————-+| qianyi | dmpush_message_temp | 1 | 0 || qianyi | test | 0 | 0 || qianyi | anson | 0 | 0 || mysql | db | 0 | 0 || mysql | slow_log | 0 | 0 || mysql | event | 0 | 0 |+———-+———————+——?+————-+可以看到这里dmpush_message_temp一直处于打开状态,这里就可以直接定位问题的根源了;

总结:主键对于innodb来说,是非常重要的,每张表的设计的时候,都应该把主键默认的加上,不管你需不需要他,而且主键的设计最好选择自增型的主键,这里也可以略提一下自增主键的好处:a.自增型主键以利于插入性能的提高;b.自增型主键设计(int,bigint)可以降低二级索引的空间,提升二级索引的内存命中率;c.自增型的主键可以减小page的碎片,提升空间和内存的使用。

mysql同步问题之Slave延迟很大优化方法 一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发。简单说,在master上是并发模式(以InnoDB引擎为主)完成事务提

MySQL-tpch 测试工具简要手册 tpch是TPC(TransactionProcessingPerformanceCouncil)组织提供的工具包。用于进行OLAP测试,以评估商业分析中决策支持系统(DSS)的性能。它包含了一整套面向商业

MySQL字符编码设置方法 通过showvariableslike'character_set%%';查看编码修改mysql的编码方式可以有以下几个:1.通过配置文件修改my.ini(windows下)或/etc/my.cnf(linux下)分别添加如下内容[mysq

标签: mysql主键冲突但数据不存在

本文链接地址:https://www.jiuchutong.com/biancheng/347535.html 转载请保留说明!

上一篇:mysql优化的重要参数 key_buffer_size table_cache(mysql优化sql有哪些)

下一篇:mysql同步问题之Slave延迟很大优化方法

  • 怎样在电子税务局查询已开发票
  • 分期收款企业所得税调整
  • 零申报公司注销麻烦吗
  • 公司为员工报销的医药费
  • 开普票要个人身份证号吗
  • 2020劳务退税怎么退
  • 减税降费对企业的影响案例
  • 购进出口商品的会计分录
  • 接受非货币性资产捐赠以其什么确认捐赠额
  • 开具的发票是否为印花税的应税凭证?
  • 公司变卖后员工怎么办
  • 核销的方式
  • 购买的车位应如果要进行税务处理需注意什么?
  • 个体户增值税怎么算
  • 营改增后房地产企业增值税如何核算
  • 建筑业简易征收的适用范围
  • 小微企业可以抵税吗
  • 非同一控制下企业合并商誉的计算
  • 过桥过闸 增值税
  • 金蝶k3外购入库单套打设置
  • 合伙企业利润分配个人所得税
  • windows7网络无法连接
  • 在XP中,为什么"网络连接"图标消失?
  • PHP:stream_get_line()的用法_Stream函数
  • 二手车交易税咋算
  • 债务重组利得计入投资收益还是营业外收入
  • css禁用button按钮
  • 土地增值税的收费标准
  • 如何编写会计记账凭证
  • wordpress自定义函数
  • 收到保险公司退款
  • 没进项可以开专票吗
  • 一岁宝宝喝红糖姜水
  • 四轮电车电机价格
  • 时间格式转换成小时数
  • 租赁房屋开具发票商品名称
  • 小规模纳税人在什么情况下会成为一般纳税人
  • vue连接webapi
  • sql server 2008r
  • db2入门视频教程
  • 外部审计查什么
  • 社保公司承担部分怎么做账
  • 自然人独资公司可以变更法人吗
  • 会计和税法折旧年限不相同
  • 初次购买金税盘怎么做账
  • 个税起征点调整至10000
  • 广告费和业务宣传费
  • 公司地址的变更需要哪些资料和手续
  • 应交增值税进项税额月底怎么处理
  • 积分换购怎么做账
  • 银行回单nxt
  • 个人往对公账户上存款怎么存
  • 年末本年利润没有结转影响报税吗
  • 为取得交易性金融资产支付的手续费
  • 记账凭证的分类及每类的具体含义是什么
  • MySQL删除重复数据只保留一条
  • linux下安装mysql简单的方法
  • XP系统无法安装软件
  • ubuntu20.4配置ip
  • windows 安装源
  • centos8拨号上网
  • windows7装进u盘
  • 如何把旧mac上的所有内容迁移到新mac上
  • Linux中cp和scp命令的使用方法
  • windows怎么更改网络状态
  • centos 安装chia
  • LayoutInflater.inflate方法解析
  • js插件写法
  • 基于arcgis的python编程
  • scrapy—redis
  • jQuery实现的指纹扫描效果实例(附演示与demo源码下载)
  • javascript零基础入门
  • android自定义ui
  • 基于javascript的毕业设计选题
  • javascript面向对象精要pdf
  • 北京交通政务
  • 车辆购置税减免政策上汽大通 大家mifa6
  • 北京朝阳税务局办税大厅
  • 税控盘怎么看收入
  • 地方税务局几点下班
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设