在线分享《如何针对业务做DB优化》(附PPT、录音、视频地址)

知数堂资深MySQL讲师,前新媒传信首席DBA、MySQL中国用户组(ACMUG)主席吴炳锡老师在5月19日晚上20:30做了在线分享《如何针对业务做DB优化》,受到了很多朋友的肯定和支持。也要再次感谢KVM虚拟化实践社区&Ceph中国社区的鼎力支持。

1、分享资料下载

百度云盘链接:http://pan.baidu.com/s/1mhSgnlm 密码:2ycr  ,欢迎转存并再次分享。

如何针对业务做DB优化.001 如何针对业务做DB优化.002 如何针对业务做DB优化.0032、本次分享PPT

这次的PPT我也在slideshare上放了一份。


FAQ系列 | 数据导入主键冲突问题

0、导读

用LOAD DATA导入数据却一直提示主键冲突问题解决案例。

1、问题描述

有位学生遇到数据导入时一直提示1022主键冲突问题,而导入的数据明明完全没有任何冲突,百思不得其解,请我帮忙协查。

下面是关于该问题现象描述:

1、表结构DDL

CREATE TABLE `wcp` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`txcode` char(4) NOT NULL,

`notice_from` enum(‘page’,’server’) ,

`message` varchar(600) NOT NULL ,

`signature` varchar(260) NOT NULL ,

`payment_no` char(30) NOT NULL ,

`notice_time` int(10) unsigned NOT NULL COMMENT ,

`dealt_ok` tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT,

  PRIMARY KEY (`id`)

) ENGINE=ARCHIVE AUTO_INCREMENT=117 DEFAULT CHARSET=utf8;

欲导入的数据有116条,可以非常肯定的是,主键值都是顺序增长的,完全没有冲突,所以感觉非常奇怪。

2、原因分析

细心的同学,从上面我贴的表DDL或许能感觉出什么不对劲的味道(嗯,有点像狗狗嗅觉灵敏的意思,哈哈)

经我这么一提醒,再认真看一下,是不是真的发现了什么?嗯,很棒,答对了(从我女儿爱看的米奇妙妙屋里学到的语气词,嘿),该表的引擎是ARCHIVE,而不是我们常用的InnoDB或MyISAM,会不会和这个有关系呢?

ARCHIVE几乎没怎么被用过,非常冷门,它有什么特点呢。翻翻手册便知:

The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.

The ARCHIVE engine supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE. It does support ORDER BY operations, BLOB columns, and basically all but spatial data types (see Section 11.17.4.1, “MySQL Spatial Data Types”). The ARCHIVE engine uses row-level locking.

The ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error. The ARCHIVE engine also supports the AUTO_INCREMENT table option in CREATE TABLE and ALTER TABLE statements to specify the initial sequence value for a new table or reset the sequence value for an existing table, respectively.

可以看到,ARCHIVE引擎和我们平时用的InnoDB、MyISAM区别还是很大的。它不支持DELETE、REPLACE、UPDATE等操作命令,只支持INSERT、SELECT、LOAD DATA等。ARCHIVE也支持自增列属性,但也必须是普通索引、唯一索引或主键索引(这个和其他引擎基本一致)。

接下来我们尝试解决数据导入主键冲突的问题。

3、问题解决

在这个例子中,想要最终能导入数据的话,有几个方法:

  1. 删除主键定义,或者删除主键的自增属性;
  2. 修改id列的主键索引为唯一索引或者普通索引;
  3. 调整表DDL定义时指定的AUTO_INCREMENT值为0或1;
  4. 修改表引擎为InnoDB或MyISAM;

第3个解决方案也是我自己几次反复测试才发现的,手册中也未提及,比较奇葩,这也是这次的案例令人最为不解的地方。

4、其他建议

我在很多场合强调过,InnoDB引擎已可适用95%以上的业务场景,完全没必要再使用其他引擎了,这次的奇葩案例也是一开始没注意到用ARCHIVE引擎而走了些弯路。

不听老叶言,吃亏在眼前,这话我看在理,嘿嘿~

 

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

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

第二届全球PHP开发者大会分享:程序猿都该知道的MySQL秘籍

这是上周末在第二节全球PHP开发者大会上分享的内容,本来打算周六晚上就发布的,但因为拖延症又发作了,直到现在才发布,嘿~

这次的PPT我也同时放到百度云盘里了,欢迎下载转存。百度云盘链接: http://pan.baidu.com/s/1pKX1qTt 密码: y7ut

程序猿都该知道的MySQL秘籍(发布版) - 20160514.001 程序猿都该知道的MySQL秘籍(发布版) - 20160514.002 程序猿都该知道的MySQL秘籍(发布版) - 20160514.003

我放了一份在slideshare上,看看有多少能翻墙的同学,哈哈。


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