FAQ系列 | 复制线程长时间Opening tables

0、导读

在slave上,发现SQL thread长时间处于Opening tables状态

1、问题描述

朋友的数据库,做了主从replication复制。在slave实例上,SQL thread的长时间处于Opening tables状态,复制进程异常。

mysql> show processlist;

+—-+————-+———–+——+———+——-+—————————————-+——————+

| Id | User        | Host      | db   | Command | Time  | State                                  | Info             |

+—-+————-+———–+——+———+——-+—————————————-+——————+

|  1 | system user |           | NULL | Connect |   554 | Queueing master event to the relay log | NULL       |

|  2 | system user |           | NULL | Connect | 59212 | Opening tables                         | NULL             |

整个实例大概20个database,总共300G左右。

master是5.5版本,slave是5.6版本,master上执行xtrabackup全库备份后搭建的slave。

2、原因分析

我的第一反应是table cache是不是太小了,导致open table比较慢,所以才长时间处于这个状态。无论如何,先一层层排查吧。

先看下slave status(部分无用信息我隐掉了):

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Queueing master event to the relay log

Master_Log_File: master-bin.000618

Read_Master_Log_Pos: 614915856

Relay_Log_File: replicate.000008

Relay_Log_Pos: 2384117

Relay_Master_Log_File: master-bin.000617

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 252048331

Relay_Log_Space: 1438994074

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Seconds_Behind_Master: 59240

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID:

Master_Info_File: /home/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Opening tables

           Master_Retry_Count: 86400

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

看不出来有什么异常的。

再看下系统负载情况:

[root@localhost mysql]# vmstat -S m 1

procs ———–memory———- —swap– —–io—- –system– —–cpu—–

 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st

 0  2      1  19591    458 230576    0    0    11    20    0    0  0  0 100  0  0

 1  1      1  19587    458 230579    0    0  2032  2528 1645  584  1  1 93  4  0

 1  1      1  19583    458 230582    0    0  1664  2712 1773  461  1  1 93  4  0

 0  2      1  19578    458 230585    0    0  2080  3376 1810  660  1  1 93  4  0

 2  0      1  19576    458 230587    0    0  2224  1804 1634  594  1  1 94  4  0

 3  1      1  19569    458 230590    0    0  1968  3488 1693  566  1  1 93  4  0

 1  1      1  19567    458 230593    0    0  2016  2632 1775  515  1  1 93  4  0

[root@localhost mysql]# sar -d 1

Linux 2.6.32-431.el6.x86_64 (localhost.localdomain)     03/21/2015      _x86_64_        (24 CPU)

03:21:57 PM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

03:21:58 PM    dev8-0    185.86   5753.54     64.65     31.30      1.20      6.48      5.08     94.44

03:21:58 PM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

03:21:59 PM    dev8-0    197.03   6114.85     95.05     31.52      1.16      5.88      4.48     88.32

03:21:59 PM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

03:22:00 PM    dev8-0    188.89   5882.83     88.89     31.61      1.14      6.03      5.09     96.16

03:22:00 PM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util

03:22:01 PM    dev8-0    166.00   5152.00     80.00     31.52      1.42      8.56      5.58     92.70

也看不出来异常,继续看看mysql的日志吧:

2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’objects_summary_global_by_type’ has the wrong structure

2015-04-21 14:51:04 18667 [ERROR] Incorrect definition of table performance_schema.rwlock_instances: expected column ‘WRITE_LOCKED_BY_THREAD_ID’ at position 2 to have type bigint(20), found type int(11).

2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’setup_actors’ has the wrong structure

2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’setup_objects’ has the wrong structure

2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’table_io_waits_summary_by_index_usage’ has the wrong structure

2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’table_io_waits_summary_by_table’ has the wrong structure

2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’table_lock_waits_summary_by_table’ has the wrong structure

2015-04-21 14:51:04 18667 [ERROR] Column count of mysql.threads is wrong. Expected 14, found 3. Created with MySQL 50524, now running 50623. Please use mysql_upgrade to fix this error.

2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’events_stages_current’ has the wrong structure

2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’events_stages_history’ has the wrong structure

2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’events_stages_history_long’ has the wrong structure

2015-04-21 15:05:17 7f5997fff700 InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.

2015-04-21 15:05:17 7f5997fff700 InnoDB: Error: Fetch of persistent statistics requested for table “db_anonymous_info_10”.”t_friend_info_3″ but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

2015-04-21 15:05:17 7f7beb0cd700 InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.

2015-04-21 15:05:17 7f7beb0cd700 InnoDB: Error: Fetch of persistent statistics requested for table “db_user_trade_1”.”t_trade_15″ but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

可以看到,这个实例中,P_S(performance_schema)库的几个表结构都异常了。另外,mysql.innodb_table_stats表也异常了,导致InnoDB表数据收集异常,然后SQL thread也跟着异常了。

3、问题解决建议

造成上面问题主要原因是,没有用正确的方式来部署slave实例。从master上用xtrabackup物理备份到slave,启动实例后,应该再执行 mysql_upgrade 升级相关表结构,确保P_S(performance_schema)、I_S(information_schema)以及 mysql 等几个系统库表结构都升级到最新版本。

 

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

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

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 获得专属优惠

FAQ系列 | 防范SQL注入风险

0、导读

在MySQL里,如何识别并且避免发生SQL注入风险

1、关于SQL注入

互联网很危险,信息及数据安全很重要,SQL注入是最常见的入侵手段之一,其技术门槛低、成本低、收益大,颇受各层次的黑客们所青睐。

一般来说,SQL注入的手法是利用各种机会将恶意SQL代码添加到程序参数中,并最终被服务器端执行,造成不良后果。

例如,我们访问接口 http://imysql.com/user.php?userid=123 来根据userid获取用户信息,假设程序中是这么处理的:

$sql = “SELECT * FROM user WHERE userid = $_GET[userid]“;

上面这段代码看起来既low有很xx对吧,尤其是在双引号里面还可以直接引用数据类型变量,所以说php是世界上最好的语言一点不为过,哈哈(其实我早期也写过几年php的)。

这时候,如果我们传递进去的参数改成这样:http://imysql.com/user.php?userid=123 or 1=1,这就会导致SQL条件永远成立,所有的数据都会被读取出来。又或者可以传递这样的参数:http://imysql.com/user.php?userid=123 or if(now()=sysdate(),sleep(5),1),这时候不但所有的数据都会被读取到,也会让这个SQL执行完毕后再等待5秒才能返回,黑客可据此来判断这个SQL注入探测是否成功。

在上面这个例子中,其实我们只需要对用户输入的参数进行简单的类型判断和控制,即可快速避免被注入的风险,例如改成下面这样就可以了:

$userid = intval(strim($_GET[‘userid’]));

$sql = “SELECT * FROM user WHERE userid = “ . mysql_real_escape_string($userid);

可见,至少基础的SQL注入并不难防范,只要在各个层面都做足工作就可以。而简单的SQL盲注(就是乱拳打死老师傅的玩法)已经可以采用sqlmap之类的辅助工具来做了,完全不需要人工执行。

2、如何防范

上面提到过sqlmap,它既可以作为SQL盲注的工具,也可以在新项目上线前内部扫一次,提前发现潜在漏洞,及时修补,反过来为我们所用。其他可以检测sql注入漏洞的知名扫描工具有:SQLIer、SQLID、SQL Power Injector、SQLNinja

我们也可以自己通过频繁扫描当前执行的SQL列表,根据一些关键字来判断是否发生了SQL注入或潜在风险,常见的关键字有:

  • SLEEP() — 一般的SQL盲注都会伴随SLEEP()函数出现,而且一般至少SLEEP 5秒以上
  • MID()
  • CHAR()
  • ORD()
  • SYSDATE()
  • SUBSTRING()
  • DATABASES()
  • SCHEMA()
  • USER()
  • VERSION()
  • CURRENT_USER()
  • LOAD_FILE()
  • OUTFILE/DUMPFILE
  • INFORMATION_SCHEMA
  • TABLE_NAME
  • fwrite()/fopen()/file_get_contents() — 这几个是PHP文件操作函数

我们可以以较高频率检查当前的活跃SQL命令,一旦发现上述关键字,可以立即记录下来并触发告警,通知管理员及时人工确认处理,甚至也可以先直接自动杀掉这些SQL查询(可以用 pt-kill 工具来做到这点,也可以自行开发),以防万一,少给黑客留机会。

还有,我们建议把选项 safe-update/sql_safe_updates 设置为 1,防止没有任何 WHERE 条件的误操作更新,将全表数据都写错

3、其他建议

防范SQL注入只是数据安全保护工作很小的一部分,只要做好基本功就可以防住至少80%以上的SQL注入探测。

在app server层,以PHP开发语言为例,除了上面提到的规范用户输入类型外,还可以改成用 sprintf() 函数来格式化构造 SQL 语句,也可以一定程度防范SQL注入。还可以修改 php cgi 程序的运行属主为普通用户,最起码不能使用 root 用户,避免因为代码层不严谨导致被黑客上传可执行 php 程序代码文件。还可以把php中的远程文件调用权限关闭,把选项 allow_url_fopen、allow_url_include 均设置为 off,并限定php可以打开的文件目录,不允许跨区域访问敏感文件。

除了在代码层面做好数据类型判断、用户输入判断外,还可以在web server层加上过滤策略,比如在nginx上启用WAF插件。或者,也可以购买IDC运营商、云主机提供商提供的商业解决方案。对于重视数据安全的企业来说,花点钱保平安更为重要。

4、附录

下面是一些常见SQL注入参考案例:

案例1:SELECT * FROM t WHERE a LIKE ‘%xxx%’ OR (IF(NOW=SYSDATE(), SLEEP(5), 1)) OR b LIKE ‘1=1‘;

案例2:SELECT * FROM t WHERE a > 0 AND b IN(497 AND (SELECT * FROM (SELECT(SLEEP(20)))a));

案例3:SELECT * FROM t WHERE a=1 and b in (1234,(SELECT (CASE WHEN (5=5) THEN SLEEP(5) ELSE 5*(SELECT 5 FROM INFORMATION_SCHEMA.CHARACTER_SETS) END)));

 

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

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

2016.03.31,知数堂在线分享《魅力Python》

知数堂2016年第二次在线分享活动启动,由资深Python专家张老师为我们带来主题分享《魅力Python》,张老师是某大型公司云计算docker方向后台开发负责人,具有5年以上Python开发经验,原360安全卫士自动化部门开发工程师

随着云平台的发展,对于传统运维压力非常大,也将面临淘汰的危机。 在云的背景下,对于运维和DBA也提出了新的要求:具备开发能力,Python是一门万能的胶水语言,在系统运维,数据抓取,中间层调用,大数据方面都有较好的使用案例

本次主要内容有:

1    Python能做什么

1.1   Python 是我们工作中的得力助手

1.2   Python 给我们生活带来的乐趣

1.3   Python 带来的便利

2    掌握Python对我们提升工作能力的帮助

2.1   如何成为Python开发工程师

2.2   作为DBA掌握Python的好处是什么

3    如何学好Python

4    我们的课程能够给你带来什么

4.1   Python快速入门

4.2  快速提升Python能力

4.3  掌握用Python开发后台web服务能力

 

分享时间:2016.03.31 20:30 – 22:00

参加分享请提前加入QQ群 373900864(暗语:PY大法好),YY语音直播频道 86142750。听完分享并立刻报名参加“知数堂Python培训”,即可以获得开班优惠活动,立减300元 报名:http://t.cn/RqzE1xl。课程咨询QQ:982892381、1037447289、619987610、4700963、82565387。

MySQL安全策略

0、导读

MySQL被运用于越来越多的业务中,在关键业务中对数据安全性的要求也更高,如何保证MySQL的数据安全?

MySQL被运用于越来越多的业务中,在关键业务中对数据安全性的要求也更高,如何保证MySQL的数据安全。

数据安全如果只靠MySQL应用层面显然是不够的,是需要在多个层面来保护的,包括网络、系统、逻辑应用层、数据库层等。

下面是我们可借鉴的一些安全策略。

1、网络、系统层面

在这个层面可以做很多的事情,我们可以把这些安全要求作为新系统安装时的标准要求,放到自动化装机方案中。

  • 把运行MySQL的服务器放在内网中,不要启用公网;
  • 迫不得已启用公网的话,修改sshd端口到10000以上;
  • 设置防火墙策略,只允许信任的服务器连接sshd和MySQL端口;
  • 修改idrac/imm密码,设置GRUB密码;
  • 设置密码安全策略,比如要求 PASS_MIN_LEN 不低于8位,其实最好是直接用一个复杂密码做MD5之后再作为正式密码,32位长度的安全程度够高吧;
  • 将操作日志记入syslog并且发送到远程log server上,坚决不能只存储在本地;
  • 除了必须的账号,其他的都设为无登入权限;
  • 尽量把运行MySQL的服务器独立出来,不要和web server、app server放一起。必须放一起的话,也要设置好权限分离,不允许web server、app server进程的属主有直接访问MySQL datadir的权限;
  • 禁用web server层的autoindex配置;
  • 可能的话,采用https代替http;
  • 关键应用保持更新,避免老版本的漏洞风险;
  • 设置nginx、php等应用服务的安全策略,禁用危险函数等;
  • 可以考虑购买运营商提供的一些安全防护、扫描器等产品;
  • 坚决杜绝二逼行为,把关键配置文件上传到公共网络上(如把公司项目代码放在github上作为个人项目,内含内网账号密码信息)。

2、逻辑应用层

在这个层面,等多的是依赖运营及开发人员的安全意识,很多本可以避免的低级安全漏洞完全可以在这个层面处理掉,比如下面提到的XSS、CSRF、SQL注入等漏洞。

  • 尽量不要在公网上使用开源的cms、blog、论坛等系统,除非做过代码安全审计,或者事先做好安全策略。这类系统一般都是黑客重点研究对象,很容易被搞;
  • 在web server层,可以用一些安全模块,比如nginx的WAF模块;
  • 在app server层,可以做好代码安全审计、安全扫描,防止XSS攻击、CSRF攻击、SQL注入、文件上传攻击、绕过cookie检测等安全漏洞;
  • 应用程序中涉及账号密码的地方例如JDBC连接串配置,尽量把明文密码采用加密方式存储,再利用内部私有的解密工具进行反解密后再使用。或者可以让应用程序先用中间账号连接proxy层,再由proxy连接MySQL,避免应用层直连MySQL;
  • 应用层启用关键日志记录,例如交易日志,方便后续对账什么的。

3、MySQL数据库层

前面几层如果都做的不够安全的话,在这层也几乎是岌岌可危了。但我们依然可以做些事情的。

  • 启用 safe-update 选项,避免没有 WHERE 条件的全表数据被修改;
  • 将 binlog 的保存周期加长,便于后续的审计、审查;
  • 应用账号只赋予SELECT、UPDATE、INSERT权限,取消DELETE权限。把需要DELETE权限的逻辑改成用UPDATE实现,避免被物理删除;
  • 需要真正删除时,交由DBA先备份后再物理删除;
  • 可以采用Percona的SQL审计插件,据说还有macfee的插件;
  • 还可以采用触发器来做一些辅助功能,比如防止黑客恶意篡改数据。

4、后记

数据安全可以做的事情很多,本文也只是罗列了一些比较简单可快速实施的方案。每个企业应有自己的安全策略规范,每一位参与者都应该心怀敬畏,努力遵守这些必要的规范,不使信息安全成为空谈。

真正的数据安全,是靠所有人的意识安全作为支撑的,没有这个意识靠机制、制度、工具都是不靠谱。

 

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

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

知数堂在线分享:MySQL响应慢排查技巧

知数堂2016年首次在线分享活动启动,由资深MySQL专家、MySQL中国用户组(ACMUG)主席吴炳锡给大家带来《MySQL响应慢排查技巧》,吴炳锡老师具有多年MySQL及系统架构设计及培训教学经验,擅长MySQL大规模运维管理优化、高可用方案、多IDC架构设计,以及企业应用数据库设计等。

本次主要内容有:

1、如何定位性能瓶颈;

1.1、如何分析CPU是否有瓶颈;

1.2、如何分析I/O是否有瓶颈;

1.3、如何分析内存是否有瓶颈;

1.4、其他瓶颈原因分析;

2、如何分析数据库状态;

2.1、MySQL关键状态分析;

2.2、InnoDB关键状态分析;

2.3、其他关键状态分析;

3、如何进行针对性优化;

3.1、慢日志收集分析;

3.2、锁等待或死锁分析;

3.3、其他情况分析;

3.4、案例总结。

 

分享时间:2016.03.15 20:30 – 22:00(和央视315晚会同时)

 

参加分享请提前加入QQ群 373900864(暗语:不约而同),YY语音直播频道 86142750。听完分享并立刻报名参加“知数堂MySQL DBA培训”,即可获得7期报名特惠价6800元(预计4月中下旬起将涨价),点此链接直达 http://t.cn/RU6UR4q (其他宝贝不参与此活动)。课程咨询QQ:982892381、1037447289、4700963、82565387。

 

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

目前第七期即将开班,学员已超250多人,多位优秀学员学成后先后入职淘宝、腾讯、去哪儿、37玩、平安金融等众多互联网知名公司,在获得更好的职业发展机遇同时薪资也得到了大幅提升。

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

扫描下面二维码加入QQ群

zst-mysql-Qqun

扫描下面几个二维码咨询课程详细信息

zst-mysql-yangyang

zst-mysql-zhangdh

zst-mysql-yejr

zst-mysql-wubx

FAQ系列 | 如何避免ibdata1文件大小暴涨

0、导读

遇到InnoDB的共享表空间文件ibdata1文件大小暴增时,应该如何处理?

1、问题背景

用MySQL/InnoDB的童鞋可能也会有过烦恼,不知道为什么原因,ibdata1文件莫名其妙的增大,不知道该如何让它缩回去,就跟30岁之后男人的肚腩一样,汗啊,可喜可贺的是我的肚腩还没长出来,hoho~

正式开始之前,我们要先知道ibdata1文件是干什么用的。

ibdata1文件是InnoDB存储引擎的共享表空间文件,该文件中主要存储着下面这些数据:

  • data dictionary
  • double write buffer
  • insert buffer/change buffer
  • rollback segments
  • undo space
  • Foreign key constraint system tables

另外,当选项 innodb_file_per_table = 0 时,在ibdata1文件中还需要存储 InnoDB 表数据&索引。ibdata1文件从5.6.7版本开始,默认大小是12MB,而在这之前默认大小是10MB,其相关选项是 innodb_data_file_path,比如我一般是这么设置的:

innodb_data_file_path = ibdata1:1G:autoextend

当然了,无论是否启用了 innodb_file_per_table = 1,ibdata1文件都必须存在,因为它必须存储上述 InnoDB 引擎所依赖&必须的数据,尤其是上面加粗标识的 rollback segmentsundo space,它俩是引起 ibdata1 文件大小增加的最大原因,我们下面会详细说。

2、原因分析

我们知道,InnoDB是支持MVCC的,它和ORACLE类似,采用 undo log、redo log来实现MVCC特性的。在事务中对一行数据进行修改时,InnoDB 会把这行数据的旧版本数据存储一份在undo log中,如果这时候有另一个事务又要修改这行数据,就又会把该事物最新可见的数据版本存储一份在undo log中,以此类推,如果该数据当前有N个事务要对其进行修改,就需要存储N份历史版本(和ORACLE略有不同的是,InnoDB的undo log不完全是物理block,主要是逻辑日志,这个可以查看 InnoDB 源码或其他相关资料)。这些 undo log 需要等待该事务结束后,并再次根据事务隔离级别所决定的对其他事务而言的可见性进行判断,确认是否可以将这些 undo log 删除掉,这个工作称为 purge(purge 工作不仅仅是删除过期不用的 undo log,还有其他,以后有机会再说)。

那么问题来了,如果当前有个事务中需要读取到大量数据的历史版本,而该事务因为某些原因无法今早提交或回滚,而该事务发起之后又有大量事务需要对这些数据进行修改,这些新事务产生的 undo log 就一直无法被删除掉,形成了堆积,这就是导致 ibdata1 文件大小增大最主要的原因之一。这种情况最经典的场景就是大量数据备份,因此我们建议把备份工作放在专用的 slave server 上,不要放在 master server 上。

另一种情况是,InnoDB的 purge 工作因为本次 file i/o 性能是在太差或其他的原因,一直无法及时把可以删除的 undo log 进行purge 从而形成堆积,这是导致 ibdata1 文件大小增大另一个最主要的原因。这种场景发生在服务器硬件配置比较弱,没有及时跟上业务发展而升级的情况。

比较少见的一种是在早期运行在32位系统的MySQL版本中存在bug,当发现待 purge 的 undo log 总量超过某个值时,purge 线程直接放弃抵抗,再也不进行 purge 了,这个问题在我们早期使用32位MySQL 5.0版本时遇到的比较多,我们曾经遇到这个文件涨到100多G的情况。后来我们费了很大功夫把这些实例都迁移到64位系统下,终于解决了这个问题。

最后一个是,选项 innodb_data_file_path 值一开始就没调整或者设置很小,这就必不可免导致 ibdata1 文件增大了。Percona官方提供的 my.cnf 参考文件中也一直没把这个值加大,让我百思不得其解,难道是为了像那个经常被我吐槽的xx那样,故意留个暗门,好方便后续帮客户进行优化吗?(我心理太阴暗了,不好不好~~)

稍微总结下,导致ibdata1文件大小暴涨的原因有下面几个:

  • 有大量并发事务,产生大量的undo log;
  • 有旧事务长时间未提交,产生大量旧undo log;
  • file i/o性能差,purge进度慢;
  • 初始化设置太小不够用;
  • 32-bit系统下有bug。

稍微题外话补充下,另一个热门数据库 PostgreSQL 的做法是把各个历史版本的数据 和 原数据表空间 存储在一起,所以不存在本案例的问题,也因此 PostgreSQL 的事务回滚会非常快,并且还需要定期做 vaccum 工作(具体可参见PostgreSQL的MVCC实现机制,我可能说的不是完全正确哈)

3、解决方法建议

看到上面的这些问题原因描述,有些同学可能觉得这个好办啊,对 ibdata1 文件大小进行收缩,回收表空间不就结了吗。悲剧的是,截止目前,InnoDB 还没有办法对 ibdata1 文件表空间进行回收/收缩,一旦 ibdata1 文件的肚子被搞大了,只能把数据先备份后恢复再次重新初始化实例才能恢复原先的大小,或者把依次把各个独立表空间文件备份恢复到一个新实例中,除此外,没什么更好的办法了

当然了,这个问题也并不是不能防范,根据上面提到的原因,相应的建议对策是:

  • 升级到5.6及以上(64-bit),采用独立undo表空间,5.6版本开始就支持独立的undo表空间了,再也不用担心会把 ibdata1 文件搞大;
  • 初始化设置时,把 ibdata1 文件至少设置为1GB以上;
  • 增加purge线程数,比如设置 innodb_purge_threads = 8;
  • 提高file i/o能力,该上SSD的赶紧上;
  • 事务及时提交,不要积压;
  • 默认打开autocommit = 1,避免忘了某个事务长时间未提交;
  • 检查开发框架,确认是否设置了 autocommit=0,记得在事务结束后都有显式提交或回滚。

 

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

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

FAQ系列 | 添加自增列失败

0、导读

想往一个表里添加一个自增列做主键,居然失败报告无法读取,这是怎么回事?

1、问题描述

有位朋友在升级discuz论坛数据库时遇到问题了,想给一个表添加自增列做主键,结果发生下面的报错:

mysql> ALTER TABLE pre_common_credit_log ADD `logid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (logid) ;

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

2、原因分析

从报错信息来看,第一反应是:数据表损坏了。不过,再仔细想想,肯定不是啊,否则报错也不是这个了,而是像下面这样的了:

1030 Got error -1 from storage engine

再仔细一想,更大的可能性是:该表里的总数据量,超过了mediumint最大值所致。

让朋友把自增列数据类型改成int或者bigint,果真就好了。

3、其他建议

建议使用discuz的同学们可以考虑这么做:

  • 如果启用了抢楼功能,那么就把post相关的表继续保留使用MyISAM引擎。或者自己动手改造代码,把抢楼功能中的楼梯值用redis来存储;
  • 如果没有启用抢楼功能,那么就可以放心的把所有表引擎改成InnoDB了。

顺便,再次吐槽一下discuz的某些功能设计,比如把session表用HEAP引擎,其他表默认引擎还是MyISAM。不过,这并不影响discuz成为国内最优秀的论坛解决方案(没办法,没更好的了,占了先机)。

 

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

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