FAQ系列 | 列类型被自动修改导致复制失败

0、导读

在复制环境中,有个表的列类型总是被修改,导致复制进程报错停止

1、问题描述

问题发生在朋友的数据库上,做了主从复制,其中某表有一列类型是INT,但是该表上的INSERT事件在BINLOG中却总被记录为MEDIUMINT类型,导致这个事件在SLAVE上执行失败。

相关现场信息见下:

MySQL版本:官方5.5.版本。

表DDL定义:

CREATE TABLE `t` (

`userid` int(10) unsigned NOT NULL DEFAULT 0,

这个表上的INSERT事件在BINLOG中的记录:

### INSERT INTO `imysql`.`t`

### SET

###   @1=207 /* MEDIUMINT meta=0 nullable=0 is_null=0 */

我们看到BINLOG中,这个列类型显示为MEDIUMINT,这个事件在SLAVE上就会报告下面的错误,导致SLAVE无法继续复制:

Column 0 of table ‘imysql.t’ cannot be converted from type ‘mediumint’ to type ‘int(10) unsigned

又是一个看起来很奇葩的案例。

2、原因分析

经过沟通排查,了解到他们的业务模式有点特殊,是从一个旧的空表中复制表结构生成每天日志表,然后再将当天的日志写入该表。也就是大概做法是:

1、创建每天日志表

CREATE TABLE t SELECT t_orig;

2、写入日志

INSERT INTO t SELECT * FROM t_orig;

其实问题就出在每天创建新表的过程中,源表结构像是这样的:

CREATE TABLE `t_orig` (

`userid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

从源表复制到新表之后,又执行了ALTER TABLE,把 userid 列类型从 MEDIUMINT 改为 INT,创建存储过程等其他工作。

生成新表后,再写入生成的日志。但是呢,写入日志却又是采用INSERT…SELECT的用法。一般情况下当然没问题,但这个例子中,源表、目标表的 userid 列类型恰好不一样(源是MEDIUMINT,目标是INT),结果导致在 binglog 中记录event时,将 userid 列类型强制转换为 MEDIUMINT 了。这个 INSERT 在 MASTER 端可以正常执行完毕,但却引发了 SLAVE 检测到二者数据类型不一致,写入失败,复制异常中断。

3、问题建议

遇到这种案例也真的是醉了,从源表每天克隆一个新表做法没问题,采用INSERT…SELECT也没问题,但为啥要源表和新表使用不同数据类型呢,直接把源表的也改成INT不就行了吗,只能说某些人懒得不像样了。

4、类似案例

FAQ系列 | 列类型被自动修改导致复制失败

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

 

附视频及ppt:在线分享《从案例说InnoDB的基本优化》总结

2016.4.20 

KVM虚拟化实践社区&知数堂

联合在线分享《从案例说InnoDB的基本优化》圆满结束

本文进行总结并且发布视频&PPT资源

分享主题

《从案例说InnoDB的基本优化》

嘉宾介绍

叶金荣,资深MySQL专家,ORACLE MySQL ACE

国内最早的MySQL推广者,从事MySQL相关工作10余年,擅长MySQL性能优化。

个人网站 http://imysql.com 始建于2006年,是国内第一个MySQL专业技术站点

个人公众号:MySQL中文网(imysql_wx)

主题介绍

从一个MySQL的ibdata1文件size急剧暴增的案例说起,具体深入解读原因,并由此展开来讲讲如何对InnoDB引擎进行优化。

Agenda

a.  MySQL的ibdata1文件size暴增案例解析

b.  MySQL InnoDB引擎特点介绍

c.  MySQL InnoDB引擎优化建议

问题交流

Q1:长时间执行的事务 因为增长 强行杀掉 会不会出现回滚需要更长时间?

答:发生大事务回滚时,如果把mysqld进程强行kill,在MySQL 5.5以前的版本确实会比较慢;而从5.5及以后的版本中,做就很快了,大概也就最多1-2分钟。

如果不是强行把mysqld进程kill,只是把大事务所在线程kill导致事务回滚,那么就需要较久的时间了,因此强烈建议几点:

a. 不要在一个事务中修改大量数据;

b. 不要直接做Online DDL,有需要的话,可以用pt-osc工具,避免DDL过程中发生异常导致回滚;

c. 需要插入/更新/删除大量数据时,可以分成多批次来执行,每次及时提交事务;

Q2:innodb_max_dirty_pages_pct 默认值为75%,叶老师的建议是改成25-50%,为何相差如此之大

答:一般而言,25%~50%是比较合适的,这个选项设置太高的话容易因为buffer pool中的脏页太多,导致刷新时瞬间I/O负载较高而影响整体TPS的稳定性,出现较大波动。

Q3:autocommit=0时,如果 一个SELECT 事物 start 后  不commit/rollback,直接close会不会增加 undo 空间?

答:一个连接直接close时,会是的该连接中的事务直接被rollback,相当于事务结束了,不会导致增加undo。

Q4:purge THREAD 可以开启多个,可以提高UNDO LOG删除吗,开启多个PURGE THREAD根据什么因素

答:增加purge thread数量一般情况下显然可以提高undo log的purge效率,除非当前I/O已经达到瓶颈。通常是因为undo log的purge速度比较慢,有较大积压,而且当前的I/O负载还没成为瓶颈时,就可以适当增加purge thread。

Q5:不同版本迁移需要注意什么吗32–64

答:不要一下子跨大版本升级通常都没事,比如从5.1 => 5.5,从5.5 => 5.6,尽量不要直接从5.1 => 5.6。

跨版本的种升级最好是认真看一遍官方手册,确认两个版本之间的一些不兼容变化,确认是否会影响升级过程,比如5.6相比5.5而言datetime字段类型就发生了实质性变化,需要特别注意。

而比如说从5.6.20 => 5.6.27,则一般可以放心直接升级。

Q6:请问生产环境在线修改调整innodb参数配置,会有什么影响没有?有哪些注意事项没有?

答:首先,有些选项可以被在线修改,有些则不可以,要看具体是哪个,相应产生的影响也要看改了哪个参数。

比如,我们可以在线修改innodb_flush_log_at_trx_commit选项,将其值从0改为1,这就有可能导致tps性能急剧下降,但却可以进一步保证数据的可靠性。

总之,要具体问题具体分析,一两句话说不清楚。

Q7:看过叶大师的博客,叶大师以前也用过zabbix, 请问大师可否对ZBX的数据库优化提提建议?谢谢。数据量大了的情况,删个host都比较慢,有lock wait 现象。

答:建议先采用表分区的方式把数据打散一下,这样根据时间删除旧数据就相对更快一些了。其次,删除host时,可以考虑采用自定义删除方式,大概做法是依次扫描各个表进行删除,且每次删除时都用LIMIT限定记录数,不要瞬间删除大量数据。最后呢,可以采用tokudb引擎来压缩数据,从我们的使用经验来看,还是挺不错的。

Q8:如何不停机在线升级

答:可以采用主从切换的方式进行升级。

Q9:有些什么主要因素影响binlog大小?

答:每秒产生的事件数直接影响binlog的大小。另外,在个别情况下,binlog format的不同,也可能会影响binlog的大小。比如,一个更新数十万行记录的UPDATE语句在STATEMENT格式下只需记录一次,而在ROW格式下,则需要记录数十万次,这就不仅仅相差几倍了。

Q10:请问培训费用是?

答:我们的MySQL DBA培训分为实战和优化两个班级,单报分别是3500元、3800元,双班一起报则只需7000元。Python班级是4000元。针对KVM社区的同学我们有特别的优惠,详见下方介绍 。

视频、录音、PPT下载

本次活动的视频、录音、PPT已在百度云盘上提供下载,链接: http://pan.baidu.com/s/1gfLL2rL ,提取密码: 8fcd ,敬请下载转存。

关于知数堂

“知数堂MySQL DBA培训”是由资深MySQL专家叶金荣、吴炳锡联合推出的MySQL DBA专业优质培训课程,这也是目前业内最有良心、最有品质的MySQL DBA培训课程。

目前第七期火爆开班中,学员已超300多人位,多位优秀学员学成后先后入职(或在职)腾讯、淘宝、京东、乐视、去哪儿、滴滴、猎豹、58、微博、金山云、聚美、苏宁、恩墨、沃趣、爱可生、37玩、人人贷、美的、新东方、平安金融等众多知名公司,在获得更好的职业发展机遇同时薪资也得到了大幅提升。

课程总耗时约90学时,每周一至周四晚上20:30 – 22:30上课,前后历时约2.5个月,由叶、吴两位大师倾心亲授,毫无保留,该课程特别适合运维工程师、开发工程师、架构师、ORACLE DBA、SQL Server DBA来参加,愿我们的课程能为您的职业发展道路助力腾飞。

为答谢KVM社区对知数堂培训的支持,我们推出KVM社区成员报名优惠码:KVMZSTYH(有效期截止 2016.4.30),凡是最终报名参加知数堂各课程均可利用该优惠码获得直降200元特权,欢迎大家加入QQ群:529671799、373900864垂询,或加QQ:982892381 、 1037447289 、 619987610 、 82565387 、 4700963等,或者直接访问知数堂官方淘宝店 http://zhishuedu.com

相关活动:在线分享《从案例说InnoDB的基本优化》

我们的培训大纲:知数堂MySQL DBA培训大纲

最后,再一次感谢肖力以及KVM社区的大力支持!

FAQ系列 | 写新数据时某列值总是被自动修改

0、导读

往表里写入新数据时,却一直报告主键冲突,某列值一直被重置为一个固定值,疑似被黑,啥情况?

1、问题描述

某朋友的线上数据库,怀疑被侵入了。具体表象是:INSERT的时候,某列值总被自动改成一个固定值。

他们先自查了 TRIGGER 和 EVENT,都是空的,确定不是因为这两种原因引起,实在想不出是哪里被动了手脚。

问题的现象:

MariaDB [information_schema]> use bbs9;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [bbs9]> INSERT INTO cdb_mythreads_latest (uid,username,tid,fid,subject,special,dateline) VALUES (‘1239009‘,’yayv’,’13482713‘,’815′,’bbs5 …………….’,’0′,’1459569279′);

ERROR 1062 (23000): Duplicate entry ‘1239009-8388607’ for key ‘PRIMARY’

可以看到,tid列的值被从 13482713(原始值) 自动替换成了 8388607(篡改值)。

更让人奇怪的是,这条SQL在mysql client端手动执行手,也会报告同样的错误。究竟是什么黑客这么牛逼呢,百思不得其解~~~

2、原因分析

单从现象来看,好像还真是被黑了的意思。

but,但是,可是,你如果足够细心,就会发现端倪。

为什么这么说呢,因为 8388607 这个数值是不是看起来挺眼熟的?嗯,没错,你才对了,这个值是 MEDIUMINT 类型的最大值,而 MEDIUMINT UNSIGNED 的最大值是 16777215

当然了,你再认真看一眼表的名字是什么:cdb_mythreads_latest,我又要呵呵了,你懂得的。

3、其他建议

既然原因已经清楚了,那么解决起来也就简单了,只需要把tid列类型改成INT UNSIGNED,甚至BIGINT UNSIGNED都行。

MEDIUMINT和INT两种类型,也只是差了1个字节,何必呢。与其在这个地方节约1个字节,还不如在别的CHAR/VARCHAR/TEXT列调整下,其优化效果要好的多得多。

4、相关案例

FAQ系列 | 添加自增列失败

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠