MySQL线上大表如何无害删除??

在我们日常的DB维护中,经常会遇到大表删除问题,大表如果在数据层进行drop操作,将会对磁盘造成很大的IO冲击,进而导致线上业务有问题

这里我们采用针对数据文件建立硬链,欺骗mysql,进行删表操作,然后,再在系统层进行删除

1.新建临时表

mysql> create table event_message_tmp like event_message;

2.新旧表切换

mysql> rename table event_message to event_message_old,event_message_tmp to event_message;

3.系统层面建立硬链接

cd /data/mysql/data/***

# ln event_message_old.ibd event_message_old.ibd.hdlk

# ll event_message_old*

4.DB层面,删除老表

mysql> drop table event_message_old;

注意 表太大,直接rm 必然会对数据库服务器的IO性能造成压力

脚本如下:

#!/bin/bash

TRUNCATE=/usr/bin/truncate

for i in `seq 218 -5 5`;

do

sleep 1

echo “$TRUNCATE -s ${i}G /data/mysql/data/mysql56_data3306/abc/event_message.ibd.hdlk”

$TRUNCATE -s ${i}G /data/mysql/data/mysql56_data3306/abc/event_message.ibd.hdlk

done

FLUSH TABLE WITH READ LOCK详解

FLUSH TABLES WITH READ LOCK简称(FTWRL),该命令主要用于备份工具获取一致性备份(数据与binlog位点匹配)。由于FTWRL总共需要持有两把全局的MDL锁,并且还需要关闭所有表对象,因此这个命令的杀伤性很大,执行命令时容易导致库hang住。如果是主库,则业务无法正常访问;如果是备库,则会导致SQL线程卡住,主备延迟。本文将详细介绍FTWRL到底做了什么操作,每个操作的对库的影响,以及操作背后的原因。

FTWRL做了什么操作?

FTWRL主要包括3个步骤:

1.上全局读锁(lock_global_read_lock)
2.清理表缓存(close_cached_tables)
3.上全局COMMIT锁(make_global_read_lock_block_commit)

FTWRL每个操作的影响

上全局读锁会导致所有更新操作都会被堵塞;关闭表过程中,如果有大查询导致关闭表等待,那么所有访问这个表的查询和更新都需要等待;上全局COMMIT锁时,会堵塞活跃事务提交。由于FTWRL主要被备份工具使用,后面会详细解释每个步骤的作用,以及存在的必要性。FTWRL中的第1和第3步都是通过MDL锁实现,关于MDL的实现,我之前总结了MDL锁的文章,这里主要介绍清理表缓存的流程。

清理表缓存

每个表在内存中都有一个table_cache,不同表的cache对象通过hash链表维护。
访问cache对象通过LOCK_open互斥量保护,每个会话打开的表时,引用计数share->ref_count++,
关闭表时,都会去对引用计数share->ref_count–。
若发现是share对象的最后一个引用(share->ref_count==0),并且share有old_version,
则将table_def_cache从hash链表中摘除,调用free_table_share进行处理。关键函数close table流程如下:

1.关闭所有未使用的表对象
2.更新全局字典的版本号
3.对于在使用的表对象,逐一检查,若表还在使用中,调用MDL_wait::timed_wait进行等待
4.将等待对象关联到table_cache对象中
5.继续遍历使用的表对象
6.直到所有表都不再使用,则关闭成功。

清理表缓存函数调用

mysql_execute_command->reload_acl_and_cache->close_cached_tables
->TABLE_SHARE::wait_for_old_version->MDL_wait::timed_wait->
inline_mysql_cond_timedwait

会话操作表流程

1.打开表操作,若发现还有old_version,则进行等待
2.share->ref_count++
3.操作完毕,检查share->ref_count–是否为0
4.若为0,并且检查发现有新版本号,则认为cache对象需要重载
5.将cache对象摘除,调用MDL_wait::set_status唤醒所有等待的线程。

关闭表对象函数调用

dispatch_command->mysql_parse->mysql_execute_command->
close_thread_tables->close_open_tables->close_thread_table->
intern_close_table->closefrm->release_table_share->my_hash_delete->
table_def_free_entry->free_table_share

关闭表导致业务库堵住的典型场景

假设有3个会话,会话A执行大查询,访问t表;然后一个备份会话B正处于关闭表阶段,需要关闭表t;随后会话C也请求访问t表。三个会话按照这个顺序执行,我们会发现备份会话B和会话C访问t表的线程都处于“waiting for table flush”状态。这就是关闭表引起的,这个问题很严重,因为此时普通的select查询也被堵住了。下面简单解释下原因:

1.会话A打开表t,执行中……
2.备份会话B需要清理表t的cache,更新版本号(refresh_version++)
3.会话B发现表t存在旧版本(version != refresh_version),表示还有会话正在访问表t,
等待,加入share对象的等待队列
4.后续会话C同样发现存在旧版本(version != refresh_version),
等待,加入share对象的等待队列
……
5. 大查询执行完毕,调用free_table_share,唤醒所有等待线程。

free_table_share //逐一唤醒所有等待的线程。
{
while ((ticket= it++))
ticket->get_ctx()->m_wait.set_status(MDL_wait::GRANTED);
}

第4步与第5步之间,所有的访问该表的会话都处于“waiting for table flush”状态,唯有大查询结束后,等待状态才能解除。

主备切换场景

在生产环境中,为了容灾一般mysql服务都由主备库组成,当主库出现问题时,可以切换到备库运行,保证服务的高可用。在这个过程中有一点很重要,避免双写。因为导致切换的场景有很多,可能是因为主库压力过大hang住了,也有可能是主库触发mysql bug重启了等。当我们将备库写开启时,如果老主库活着,一定要先将其设置为read_only状态。“set global read_only=1”这个命令实际上也和FTWRL类似,也需要上两把MDL,只是不需要清理表缓存而已。如果老主库上还有大的更新事务,将导致set global read_only hang住,设置失败。因此切换程序在设计时,要考虑这一点。

关键函数:fix_read_only

1.lock_global_read_lock(),避免新的更新事务,阻止更新操作
2.make_global_read_lock_block_commit,避免活跃的事务提交

FTWRL与备份

Mysql的备份方式,主要包括两类,逻辑备份和物理备份,逻辑备份的典型代表是mysqldump,物理备份的典型代表是extrabackup。根据备份是否需要停止服务,可以将备份分为冷备和热备。冷备要求服务器关闭,这个在生产环境中基本不现实,而且也与FTWRL无关,这里主要讨论热备。Mysql的架构支持插件式存储引擎,通常我们以是否支持事务划分,典型的代表就是myisam和innodb,这两个存储引擎分别是早期和现在mysql表的默认存储引擎。我们的讨论也主要围绕这两种引擎展开。对于innodb存储引擎而言,在使用mysqldump获取一致性备份时,我们经常会使用两个参数,–single-transaction和–master-data,前者保证innodb表的数据一致性,后者保证获取与数据备份匹配的一致性位点,主要用于搭建复制。现在使用mysql主备集群基本是标配,所以也是必需的。对于myisam,就需要通过–lock-all-tables参数和–master-data来达到同样的目的。我们在来回顾下FTWRL的3个步骤:

1. 上全局读锁
2. 清理表缓存
3. 上全局COMMIT锁

第一步的作用是堵塞更新,备份时,我们期望获取此时数据库的一致状态,不希望有更多的更新操作进来。对于innodb引擎而言,其自身的MVCC机制,可以保证读到老版本数据,因此第一步对它使多余的。第二步,清理表缓存,这个操作对于myisam有意义,关闭myisam表时,会强制要求表的缓存落盘,这对于物理备份myisam表是有意义的,因为物理备份是直接拷贝物理文件。对于innodb表,则无需这样,因为innodb有自己的redolog,只要记录当时LSN,然后备份LSN以后的redolog即可。第三步,主要是保证能获取一致性的binlog位点,这点对于myisam和innodb作用是一样的。

所以总的来说,FTWRL对于innodb引擎而言,最重要的是获取一致性位点,前面两个步骤是可有可无的,因此如果业务表全部是innodb表,这把大锁从原理上来讲是可以拆的,而且percona公司也确实做了这样的事情,具体大家可以参考blog链接。此外,官方版本的5.5和5.6对于mysqldump做了一个优化,主要改动是,5.5备份一个表,锁一个表,备份下一个表时,再上锁一个表,已经备份完的表锁不释放,这样持续进行,直到备份完成才统一释放锁。5.6则是备份完一个表,就释放一个锁,实现主要是通过innodb的保存点机制。相关的bug可以参考链接:http://bugs.mysql.com/bug.php?id=71017

MySQL查询条件中字符串包含空格的问题

问题
最近在联调某个业务时发现使用的签名总是验证不过,在MySQL中查询了该业务的私钥配置和业务侧的配置是一样的,问题就出在SQL查询这里,最后将配置导出到本地发现私钥后面多了一个空格,将空格删除然后签名计算OK。问题是:为什么在DB查询条件中的字符串没有包含空格也可以查到实际包含空格的这条记录呢?

原因
如果字段是char或varchar类型,那么在字符串比较的时候MySQL使用PADSPACE校对规则,会忽略字段末尾的空格字符,若想做到精确匹配可以使用下面几种方法:
方法1:使用like语句;
方法2:使用binary类型,例如,select binary ‘a’ = ‘a ‘
方法3:再添加一个length()条件,例如,select col from table where col = ‘a ‘ and LENGTH(col) = LENGTH(‘a ‘)

官方手册说明(5.0版本):
http://dev.mysql.com/doc/refman/5.0/en/char.html
11.1.6.1. The CHAR and VARCHAR Types
All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant.

转载地址:https://blog.csdn.net/delphiwcdj/article/details/16983993

insert into a select * from b 锁问题分析

很长一段时间,都认为”insert into a select * from b” 会导致b表被锁住。在最近一次组内同事的质疑中,发现错了.b表会不会被锁,其实和隔离级别有一定关系,下面我们具体分析一下

环境说明:
1.隔离级别:REPEATABLE-READ
2.MySQL版本:5.6.29
3.Binlog格式:ROW
4.建表SQL:

CREATE TABLE `os_diskio_history_bak` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`ip` varchar(50) NOT NULL,
`tags` varchar(100) DEFAULT NULL,
`fdisk` varchar(50) NOT NULL DEFAULT '0',
`disk_io_reads` bigint(18) NOT NULL DEFAULT '0',
`disk_io_writes` bigint(18) NOT NULL DEFAULT '0',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`YmdHi` bigint(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_ymdhi` (`YmdHi`) USING BTREE,
KEY `idx_ip_ymdhi` (`ip`,`YmdHi`),
KEY `idx_io_reads` (`disk_io_reads`),
KEY `idx_io_writes` (`disk_io_writes`)
) ENGINE=InnoDB AUTO_INCREMENT=484556 DEFAULT CHARSET=utf8

测试用例:
1.直接插入

终端1:
mysql> insert into os_diskio_history_bak select * from os_diskio_history;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 2
929 lock struct(s), heap size 112168, 207989 row lock(s), undo log entries 207063
mysql> show engine innodb status;
mysql tables in use 2, locked 2
1636 lock struct(s), heap size 194088, 363913 row lock(s), undo log entries 362280
mysql> show engine innodb status;
mysql tables in use 2, locked 2
1984 lock struct(s), heap size 226856, 440646 row lock(s), undo log entries 438666

从上面,我们看到 row lock 和 lock struct 在不停的增长,被锁的行数在不断增加

2.按主键降序插入

终端1:mysql> insert into os_diskio_history_bak select * from os_diskio_history order by id desc;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 2
502 lock struct(s), heap size 63016, 109585 row lock(s), undo log entries 109583
mysql> show engine innodb status;
mysql tables in use 2, locked 2
630 lock struct(s), heap size 79400, 137782 row lock(s), undo log entries 137781
mysql> show engine innodb status;
mysql tables in use 2, locked 2
920 lock struct(s), heap size 112168, 201581 row lock(s), undo log entries 201580
mysql> show engine innodb status;
mysql tables in use 2, locked 2
1240 lock struct(s), heap size 144936, 271567 row lock(s), undo log entries 271566

从上面,我们看到 row lock 和 lock struct 在不停的增长,被锁的行数在不断增加,其实同测试案例1
结论:
通过主键排序或则不加排序字段的导入操作”insert into a select * from b”,是会锁b表,但他的锁是逐步地锁定已经扫描过的记录。
默认:
主键升序的select :从第一行开始扫描到最后,即第一行开始锁直到最后。
主键倒序select:从最后一行开始扫描到最前,即最后一行开始锁直到第一行。

3.B 表使用非主键字段排序

终端1:mysql> insert into os_diskio_history_bak select * from os_diskio_history order by YmdHi desc;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 2
2190 lock struct(s), heap size 259624, 486689 row lock(s)
mysql> show engine innodb status;
mysql tables in use 2, locked 2
2191 lock struct(s), heap size 259624, 486689 row lock(s)
mysql> show engine innodb status;
mysql tables in use 2, locked 2
2192 lock struct(s), heap size 259624, 486689 row lock(s), undo log entries 44409

从上面,我们看到 row lock 和 lock struct 值一直没有变,他的锁是一开始就会锁定整张表,不能进行任何操作,直到锁释放了

结论:insert into a select * from b 的导入操作是会锁定原表,但是锁是有2种情况:“逐步锁”,“全锁”

现在我们调整隔离级别为:RC

终端1:
mysql> set tx_isolation='READ-COMMITTED';
mysql> insert into os_diskio_history_bak select * from os_diskio_history order by YmdHi desc;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 1
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 41394
mysql> show engine innodb status;
mysql tables in use 2, locked 1
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 95860

从上面,我们看到 row lock 为 0

总结:
1.在RR模式下:在按主键进行排序或不排序时,采用的是逐步锁定行
2.在RR模式下:在按非主键进行排序时,采用的是表锁
3.在RC模式下:无论按主键还是非主键排序,B表都不加锁

DBPRoxy 监控用户(backend-monitor-pwds)未配置,引起后端DB日志出现登陆报错

最近,在新上线的一套DBProxy+MySQL项目过程中,发现后端MySQL DB出现登陆报错:

[Warning] Access denied for user ‘apps’@’192.168.1.146’ (using password: NO)

以上报错为测试环境,模拟产生的报错信息,实际生产环境是 Access denied for user ‘root’@’proxy IP’ (using password: NO)

查看了其它DBProxy后端的DB,没有此类报错。

对比问题DBPrroxy和正常DBProxy的配置文件,版本等信息,发现都是一样,无差异。

这样看来,好紧张,居然有root用户尝试远程登陆,这是多危险,被攻击了????当时的第一反应(其实要说明一点,root用户其实设置了只能本地登录)。

登陆问题DBProxy服务器,查询进程,top,抓包,看proxy日志,系统日志,都未发现异常,但是,后端所有DB的报错还在持续。

果断,在测试环境复现问题,复现环境如下:

MySQL Version:MySQL 5.7.22

DBPrroxy Version:5.0.99-agent-admin

MySQL 重要参数配置:log_warnings = 2 (设定是否将警告信息记录进错误日志。默认设定为1,表示启用;可以将其设置为0以禁用;而其值为大于1的数值时表示将新发起连接时产生的“失败的连接”和“拒绝访问”类的错误信息也记录进错误日志)

后端DB报错截图如下:

4

在测试环境复现,我们发现如下几个问题:

1.如果用错误的账号来连接DBProxy时,DBProxy的日志会打印出报错信息(这里的192.168.1.146 为DBProxy的IP地址),但我们生产环境上DBProxy没有任何相关报错日志

测试命令如下:

[apps@unify-mysql mysql]$ mysql -uapp -p -h192.168.1.146 -P8888
Enter password:
ERROR 1045 (28000): Access denied for user ‘app’@’192.168.1.146’ (using password: YES)

DBProxy报错截图如下:

2

2.从DB后端错误日志,我们发现一些问题:

生产环境是root@proxy ip 登陆报错,root账号,后端DB是存在这个账号的,这个也就是我们第一反应被攻击的缘由

测试环境是apps@proxy ip登陆报错,apps账号,后端DB不存在这个账号,这是怎么回事呢?大家猜猜!!!文章结尾,我揭晓原因~~~

3.我们尝试从DBProxy,剔除一台从库,看看还会不会报错,剔除后,发现日志干净了,一点报错也没有,到这里,我们心里就踏实了,不是被攻击了,是DBProxy自己去连后端DB的!!

4.于是我们仔细检查DB日志,发现登陆存在一定的规律性,每隔4秒,出现一次报错,截图如下:

1

同时,我们看了DBProxy的相关参数,发现有一项参数,正好设置的是4秒,同时,我咨询了官方的海涛同学,证实了我们刚才的猜想,答复是这样的:

MySQL协议里,如果没有显示的指明用户名的话,会使用root连接。DBProxy里,如果没有配置monitor账号,DBProxy在监控后端DB的时候,会使用NULL用户名去连接DB,MySQL端会认为是root账号连接的。默认proxy的monitor周期检测是4s一次,但是每次检测会遍历每个DB,所以并非完全4s精准~

于是,我们设置monitor账号,命令如下:

set backend-monitor-pwds=dbproxy_user:dbproxy_user;

此时参数的截图如下:

3

此时,我们再看后端DB的日志,发现错误消失了,问题到此,算是找到原因,并解决了

总结:

1.为什么线上环境报错日志是root连,而测试环境报错日志是用apps连??

如果贵司已经进行目录标准化后,我相信您的部署,肯定不是用官方的脚本,而是按照自己的规则定义,比如目录属主,安装路径,启动用户等等,这里就是因为线上用root用户启动,目录属主为root。而测试环境是我自定义安装的,属主为apps用户,启动用户也是apps用户

2.线上DBProxy文件,对比一致,为什么只有这一套环境有报错??

由于DB配置文件不标准化,部署因人而异,log_warnings设置成0了,这个因公司而异,很多公司,这个参数的确设置为0

3.最后,要说明的,任何自动化的前提,都需要标准化,这里不仅目录标准化,还有配置标准化,部署标准化,操作标准化等等。任重而道远~~~

DBProxy Warning: Syntax Forbidden Set option:^A 分析及应对方案

      在我们的日常运维中,随着公司业务的持续发展,DB的单机性能逐渐满足不了要求,大家首先想到的可能是拆表,拆库,扩容,归档,提升硬件配置等等,但实际业务场景中,往往优先出现瓶颈的是读的性能,这也就是,我下面要说的数据库读写分离。
       目前,每个公司的读写分离策略都不太一样,有的采用的是开源的数据库中间件,对应用开发来说,是透明的;有的公司采用的是代码层时间读写分离,需要对代码有一定的倾入性;也有采用自研其他方案。
       现阶段,由于我们绝大多数应用是PHP应用,业务持续发展,代码层实现读写分离,过于繁琐,持续时间长,研发经历有限,最终,经过调研,先后选型了Atlas/DBProxy,目前,公司读写分离中间件,90%都是使用DBProxy。在使用过程中,难免会碰到各种问题,但相对来说,DBProxy的稳定性不错,官方答复问题的速度也是非常迅速,是一个非常棒的开源团队,这里特别需要感谢一下,DBProxy官方团队的海涛同学,多次协助分析问题。
       DBProxy的下载,部署等环节,这里忽略,请移步官方文档,文档写的非常明确详实。
       DBProxy github地址:https://github.com/Meituan-Dianping/DBProxy
       我们使用DBProxy过程中,也并非一帆风顺,也遇到一些问题,比如,业务代码里有一些,Proxy不支持的语法问题,如Proxy Warning – Syntax Forbidden Set option:^A
———————————————————————————————————
下面,我们重点进行分析这个警告的来源及处理方案:
1.报错产生的环境:
         DBProxy Version:5.0.99-agent-admin
         MySQL Version:5.6.25 / 5.5.5-10.2.14-MariaDB-log
         PHP Version:5.6.11
         WARNING:2018-06-25 15:16:24.281125: (critical)proxy-plugin.c:2125(proxy_read_query) event_thread(1) C:192.168.1.146:52396 S:192.168.1.146:8888(thread_id:0) Usr:dbproxy_user Db:dbproxy Proxy Warning – Syntax Forbidden Set option:^A
2.遇到这个问题,首先,我们第一时间打开sql log,因为从日志看,没法知道具体是什么set操作,proxy不支持。
        mysql> set sql-log=on;
        mysql> set sql-log-slow-ms=0;
        mysql> set long-query-time=0;
3.查看SQL日志,这个目录一般在安装路径下的log/sql目录下,部分日志如下:(这里我们利用测试代码,重现错误,后面提供php测试代码)
       2018-06-25 15:16:24.281250: C:192.168.1.146:52396 C_db:dbproxy C_usr:dbproxy_user [Slow Query] 0.164(ms) Set option ^A
       2018-06-25 15:16:24.281876: C_begin:2018-06-25 15:16:24.281327 C:192.168.1.146:52396 C_db:dbproxy C_usr:dbproxy_user S:192.168.1.146:3307(thread_id:1041192) S_db:dbproxy S_usr:dbproxy_user inj(type:4
 bytes:0 rows:0) 0.438(ms) OK Query:SET character_set_connection=utf8, character_set_results=utf8, character_set_client=binary
       2018-06-25 15:16:24.281977: C:192.168.1.146:52396 C_db:dbproxy C_usr:dbproxy_user [Slow Query] 0.648(ms) Query SET character_set_connection=utf8, character_set_results=utf8, character_set_client=bina
ry
       2018-06-25 15:16:24.282334: C_begin:2018-06-25 15:16:24.282083 C:192.168.1.146:52396 C_db:dbproxy C_usr:dbproxy_user S:192.168.1.146:3307(thread_id:1041192) S_db:dbproxy S_usr:dbproxy_user inj(type:4
bytes:0 rows:0) 0.204(ms) OK Query:SET sql_mode=”
       从上面的日志看,最初认为是SET character_set_connection=utf8, character_set_results=utf8, character_set_client=binary,SET sql_mode=” 这些操作的不支持造成,但是,反复测试发现,这些不是根源,而且,从日志观察看,提示OK Query,说明,这个操作,Proxy是支持的,并且是成功,如果不支持的话,Proxy日志应该报ERRQuery.
       所以,从日志看,就是一个Set option ^A,让研发按关键字搜索代码,研发表示懵逼,代码里没有这个操作,搜索其他set操作,如SET character_set_connection=utf8 ,代码倒是有,于是,排查问题,陷入僵局,代码里没有,那么是哪里的问题,但是,直觉告诉你,这肯定和代码有关系,于是,有了下面的抓包分析问题。
       测试代码:测试代码1
4.在DB层或proxy层抓包分析:
抓包:tcpdump -i any tcp and port 3306 -s 500 -w proxy.pcap
分析工具:wireshark
定位截图:
QQ图片20180628104223
       从抓包分析,我们看到,做了multi statements off 操作造成,但是代码里的确没有这个操作,此时,我们判断,可能是PHP自身的函数造成的。
        搜了很多multi statement相关文档后,怀疑是使用了multi_query()这个函数,我们用multi_query()进行查询,的确可以复现问题,感觉快要看到光明了。呵呵….,再次让研发帮忙搜索代码,研发又一次懵逼,代码里没有用到这个函数,擦,瞬间掉到冰窟窿里了,这是哪里出了问题。
       使用mysqli_quey()函数测试代码:测试代码2
       于是,重新模拟代码测试发现(见测试代码1),模拟的代码里只有mysql_connect()和mysql_query()这两个关键函数,会不会是这两个函数的问题呢???
       于是,我们进行了替换,修改为:mysqli_query(),mysql_connect 修改为 mysqli_connect() 问题解决
       见测试代码:测试代码3
       有关mysql_connect()/mysql_query(),PHP官方是这样说的:
       mysql_connect — 打开一个到 MySQL 服务器的连接
       Warning
       本扩展自 PHP 5.5.0 起已废弃,并在自 PHP 7.0.0 开始被移除。应使用 MySQLi 或 PDO_MySQL 扩展来替换之
       这也就是,为什么php7的应用代码,没有这个问题。其实,虽然php5开始废弃,但是并没有被移除,所以,老的函数依然可以使用。这也就是我这边是php5.6的代码,为什么有这个问题
5.最终处理方案:
       mysql_connect()/mysql_query() 替换为 mysqli_connect()/mysqli_query()
6.测试代码如下:
       每段代码的开头和结尾分别加上<?php 和 ?>,进行测试

1.测试代码1:
$con = mysql_connect("192.168.1.146:8888","dbproxy_user","dbproxy_user");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbproxy", $con);
$sql_1 = "SET character_set_connection=utf8, character_set_results=utf8, character_set_client=binary";
mysql_query($sql_1,$con);
$sql_2 = "SET sql_mode=''";
mysql_query($sql_2,$con);
$sql_3 = "select * from shirt limit 1;";
mysql_query($sql_3,$con);
mysql_close($con);

2.测试代码2:
$mysqli = new mysqli("192.168.1.146", "dbproxy_user", "dbproxy_user", "",'8888');
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$mysqli->query("SET NAMES 'UTF8'");
$mysqli->query("SET sql_mode=''");
$mysqli->query("SET character_set_connection=utf8, character_set_results=utf8, character_set_client=binary");
$sql = "INSERT INTO test(id) VALUES (1);";
$sql.= "select id from test;";
if (!$mysqli->query($sql)) {
echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->multi_query($sql)) {
echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

3.测试代码3:
$con = mysqli_connect("192.168.1.146","dbproxy_user","dbproxy_user","dbproxy","8888");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
$sql_1 = "SET character_set_connection=utf8, character_set_results=utf8, character_set_client=binary";
$sql_2 = "SET sql_mode=''";
$sql_3 = "select * from shirt limit 1;";
mysqli_query($con,$sql_1);
mysqli_query($con,$sql_2);
mysqli_query($con,$sql_3);
mysqli_close($con);

MySQL优化器:index merge介绍

在MySQL官方手册上,关于index merge的介绍非常非常少。甚至还有不少误导的地方,这次把5.1版本关于此类优化处理的代码细看了一遍,以案例的方式介绍了各种实用index merge访问类型的SQL。后续的还会继续介绍index merge实现的主要数据结构,以及成本评估。

1. 什么是index merge

MySQL优化器如果发现可以使用多个索引查找后的交集/并集定位数据,那么MySQL优化器就会尝试index merge这类访问方式。index merge主要分为两大类,多个索引交集访问(intersections),多个索引并集访问,当然这两类还可以组合出更为复杂的方式,例如多个交集后做并集。

1.1 index merge的限制:range优先

MySQL在5.6.7之前,使用index merge有一个重要的前提条件:没有range可以使用。这个限制降低了MySQL index merge可以使用的场景。理想状态是同时评估成本后然后做出选择。因为这个限制,就有了下面这个已知的bad case(参考):

SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

优化器可以选择使用goodkey1和goodkey2做index merge,也可以使用badkey做range。因为上面的原则,无论goodkey1和goodkey2的选择度如何,MySQL都只会考虑range,而不会使用index merge的访问方式。这是一个悲剧…(5.6.7版本针对此有修复)

2. 关于index merge的一些案例

关于什么是交集/并集在手册中有详细介绍,这里不赘述。这里通过几个案例来看看,哪些情况使用交集,哪些情况使用并集,哪些情况使用更复杂的组合。

示例中使用的表结构和数据参考本文4.2节。

2.1 k1_p1 = 2 or k2_p1 = 4

这是最典型,也是最简单的场景了:

SELECT * FROM tmp_index_merge where key1_part1 = 2 or key2_part1 = 4

explain SELECT * FROM tmp_index_merge where key1_part1 = 2 or key2_part1 = 4\G …… table: tmp_index_merge type: index_merge key: ind1,ind2 key_len: 4,4 Extra: Using sort_union(ind1,ind2); Using where

2.2 (k1_p1=2 and k1_p2=7) or k2_p1=4\G

这个案例稍微复杂一丁点,第一个索引使用了两个字段:

explain SELECT * FROM tmp_index_merge where (key1_part1 = 2 and key1_part2 = 7) or key2_part1 = 4\G …… table: tmp_index_merge type: index_merge key: ind1,ind2 key_len: 8,4 Extra: Using sort_union(ind1,ind2); Using where

2.3 (k1_p1=2 or k1_p1=7) or k2_p1=4\G

这个案例也能够使用index merge。内部的实现比它表面上看起来要复杂,这里简单解释一下:MySQL在递归处理这个WHERE条件时,先处理前一部分(key1_part1 = 2 or key1_part1 = 7)。对于同一个索引的同一个字段进行or操作,MySQL会将其合并成一颗SEL_ARG树(具体参考),两个条件通过SEL_ARG的Next/prev指针连接。MySQL的range访问方式可以通过遍历这棵树(也可以参考前面这篇文章)。接着优化器再处理or的另一个分支(key2_part1 = 4)发现可以使用第二个索引,于是将index merge加入可能的执行计划列表(后续评估成本,再决定是否实用该访问方式)。

explain SELECT * FROM tmp_index_merge where (key1_part1 = 2 or key1_part1 = 7) or key2_part1 = 4\G …… table: tmp_index_merge type: index_merge key: ind1,ind2 key_len: 4,4 Extra: Using sort_union(ind1,ind2); Using where

2.4 (k1_p1=2 or k1_p2=7) or k2_p1=4\G

这种情况是无法直接使用任何索引的。不解释。

explain SELECT * FROM tmp_index_merge where (key1_part1 = 2 or key1_part2 = 7) or key2_part1 = 4\G …… table: tmp_index_merge type: ALL possible_keys: ind1,ind2 key: NULL Extra: Using where

2.5 k1_p1=1 or (k1_p1=2 and k1_p2=4 and k2_p1=3)

对于这样的条件,MySQL会发现可以使用range访问方式。而根据前面的”range优先”原则,MySQL不再考虑index merge(这里k1_p1=1和k2_p1=3是可以通过index merge访问方式实现的)。MySQL在考虑使用key1访问的时候,看到的条件是:k1_p1=1 or (k1_p1=2 and k1_p2=4)。这里OR两边的条件可以构造成一颗独立的SEL_ARG。(本文后面小结“更多关于range优先原则”有更多详细介绍)

所以,MySQL会直接使用range,而不再考虑index merge。(怎样的条件无法够着成一颗SEL_ARG树,参考,对于两颗SEL_ARG通过or合并的时候,还有一些更复杂的事情,这里暂时不做介绍)

explain SELECT * FROM tmp_sel_tree where key1_part1=1 or (key1_part1=2 and key1_part2=4 and key2_part1=3)\G table: tmp_sel_tree type: range key: ind1 key_len: 8 Extra: Using where

如果前面这几个案例看明白了,那可以继续了,下面会有一些更复杂的案例:

2.6 嵌套的案例1

这个案例看起来很复杂,但其本质跟最前面提到的”已知的bad case”相同,是一个可以使用index merge,但是被range优先掉的案例。

SELECT * FROM tmp_sel_tree where ( key1_part1 = 1 or (key1_part2 = 2 and key2_part1 = 3) ) and ( key3_part1 = 5 )

2.7 嵌套的案例2

这个案例跟上面稍有不同,是一个三个索引的index merge,这里MySQL将考虑使用index merge。但是一般来说,这类index merge成本本身较大,容易超过全表的成本:

SELECT * FROM tmp_sel_tree where ( key1_part1 = 1 or (key1_part2 = 2 and key2_part1 = 3) ) or ( key3_part1 = 5 )

如果成本评估后,发现index merge成本小于全表,则会使用:

table: tmp_sel_tree type: index_merge key: ind1,ind2,ind3 Extra: Using sort_union(ind1,ind2,ind3); Using where

3. 更多关于range优先原则

可以使用range的情况

在5.6.7之前的MySQL版本,只要可以使用Range访问方式,那就不会再使用index merge。因为可以使用range访问的WHERE条件是非常多的,除了我们常见的(k1_p1=const and k2_p2>const),如果参考Range优化相关的数据结构,还会看到更多的WHERE条件可以使用range。

这里拿出其中一个较为复杂的可以使用range访问的WHERE条件,做一个简单分析。

WHERE ( key1_part1 = 3 and key1_part2 > 5 and key2_part1 = 7 ) or ( key1_part1 > 2 )

对于索引key2来说,这个条件可以简化为如下,可以使用index merge的访问方式:

(TRUE AND TRUE AND key2_part1 = 7) OR ( key1_part1 < 2 )

对于索引key1来说,条件简化为:

(key1_part1 = 3 and key1_part2 > 5 and TRUE) or (key1_part1 > 2)

对于索引key1,这是一个可以使用range访问方式的条件。根据前文Range优化相关的数据结构可以构造成一颗SEL_ARG结构,如下:

$ $ SEL_ARG[2,∞) $ $ |^ $ $ next|| $ $ ||prev $ $ v| $ $ SEL_ARG[3,3] ==$====> SEL_ARG[5,∞] $ $ $

range访问会依次SEL_ARG,遍历访问。因为有range访问方式,所以这类条件不会再考虑index merge。

但如果WHERE是如下样子(OR后面条件是key1_part2而不是key1_part1):

WHERE ( key1_part1 = 3 and key1_part2 > 5 and key2_part1 = 7 ) or ( key1_part2 > 2 )

OR后面的key1_part2是无法与前面的key1条件合并成一颗SEL_ARG树,所以也就无法使用range访问。因为or后面条件无法独立使用索引访问,所以也同样无法做index merge访问。

4. 其他

4.1 type in MySQL Explain

在MySQL手册中把Explain中type列称为:”EXPLAIN Join Types”。这给很多人产生了误解,这里的Type实际是指在整个JOIN中这个单表的访问方式。例如:

id: 1 select_type: SIMPLE table: tmp_sel_tree type: index_merge possible_keys: ind1,ind2,ind3 key: ind1,ind2,ind3 key_len: 4,4,4

常见的单表访问方式有:const/ref/range/index/all

MySQL的优化器主要有两个自由度,一个是确定每个单表的访问方式。另一个就是访问顺序。博客中常说的使用”range优化” “index merge优化”也是指MySQL单表访问方式选择了”range”或者”index merge”。

4.2 示例中的表结构和数据

CREATE TABLE `tmp_index_merge` ( `id` int(11) NOT NULL, `key1_part1` int(11) NOT NULL, `key1_part2` int(11) NOT NULL, `key2_part1` int(11) NOT NULL, `key2_part2` int(11) NOT NULL, `key2_part3` int(11) NOT NULL, `key3_part1` int(11) NOT NULL DEFAULT ‘4’, KEY `ind1` (`key1_part1`,`key1_part2`), KEY `ind2` (`key2_part1`,`key2_part2`,`key2_part3`), KEY `ind3` (`key3_part1`) ) ENGINE=InnoDB

MySQL自带的性能压力测试工具mysqlslap

mysqlslap是从MySQL的5.1.4版开始就开始官方提供的压力测试工具。

通过模拟多个并发客户端并发访问MySQL来执行压力测试,同时提供了较详细的SQL执行数据性能报告,并且能很好的对比多个存储引擎(MyISAM,InnoDB等)在相同环境下的相同并发压力下的性能差别。

mysqlslap 官方介绍:http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html

常用参数 [options] 详解

–host=host_name, -h host_name 连接到的MySQL服务器的主机名(或IP地址),默认为本机localhost

–user=user_name, -u user_name 连接MySQL服务时用的用户名

–password[=password], -p[password] 连接MySQL服务时用的密码

–create-schema 代表自定义的测试库名称,测试的schema,MySQL中schema也就是database。
(没指定使用哪个数据库时,可能会遇到错误mysqlslap: Error when connecting to server: 1049 Unknown database ‘mysqlslap’)

–query=name,-q 使用自定义脚本执行测试(可以是SQL字符串或脚本),例如可以调用自定义的一个存储过程或者sql语句来执行测试。

–create 创建表所需的SQL(可以是SQL字符串或脚本)

–concurrency=N, -c N 表示并发量,也就是模拟多少个客户端同时执行query。可指定多个值,以逗号或者–delimiter参数指定的值做为分隔符。例如:–concurrency=100,200,500(分别执行100、200、500个并发)。

–iterations=N, -i N 测试执行的迭代次数,代表要在不同的并发环境中,各自运行测试多少次;多次运行以便让结果更加准确。

–number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)

–engine=engine_name, -e engine_name 代表要测试的引擎,可以有多个,用分隔符隔开。例如:–engines=myisam,innodb,memory。

–auto-generate-sql, -a 自动生成测试表和数据,表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。

–auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括:read (scan tables), write (insert into tables), key (read primary keys), update (update primary keys), or mixed (half inserts, half scanning selects). 默认值是:mixed.

–auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列,从5.1.18版本开始支持。

–number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列,默认1

–number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列,默认1

–commint=N 多少条DML后提交一次。

–compress, -C 如果服务器和客户端支持都压缩,则压缩信息传递。

–only-print 只打印测试语句而不实际执行。

–detach=N 执行N条语句后断开重连。

–debug-info, -T 打印内存和CPU的相关信息。

测试范例:

mysqlslap -uroot -p –socket /tmp/mysql3306.sock –concurrency=1 –iterations=1 –create-schema=’test’ –query=’SELECT id,unionid,current_num,total_num FROM invite_join WHERE unionid=”Cmo” AND active_id=”3″ AND is_deleted =0 ORDER BY id DESC LIMIT 1;’ –number-of-queries=1000000

利用xtrabackup和binlog恢复单表误删数据思路

昨晚,有个朋友说它的DB有张表,被研发误删了一部分数据,咨询我一些恢复的思路:

环境:全备、增备、日志、主从、binlog-format=mixed

由于binlog格式的限制,没法从binlog里面反向解析误删数据,因此,就有了下面的思路,有什么遗漏的或者不到位的地方,欢迎大家指正!!

1.必须有xtrabackup备份

这里包括全备和增备

2.在全备和增备上都执行–apply-log –redo-only操作

执行完此操作后,备份的数据目录里会包含以下几种数据类型文件:

*.frm / *.ibd / *.exp / *.cfg

3.将原来有数据丢失的表重命名为新表

alter table old_table rename old_table_bak

4.创建一个与原表结构一致的新表new_table

5.将新表丢弃表空间

alter table new_table discard tablespace;

6.拷贝备份目录中的 *.ibd/*.cfg/*.exp到mysql的数据目录

mysql> system cp *.{ibd,exp,cfg} /db/mysql5.6/data/test/

7.导入表空间:

alter table new_table import tablespace

注:此时你执行查询时,是可以正常查询,但数据肯定不是最新的

8.由于数据不是最新的,所以需要用到binlog,但是binlog针对单表需要使用脚本过滤,太麻烦,下面介绍一个简单办法

这是使用偷懒的方法,重新授予一个用户,仅对此表有select,insert,delete,update的权限

GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`t1` TO ‘test_user’@’localhost’ identified by ‘123456’;

9.执行binlog恢复,具体根据需要进行处理,这里简单说明

mysqlbinlog –no-defaults -vv –base64-output=decode-rows -d test mysql-bin.000001 | mysql -S /tmp/mysqld.sock -utest_user-p123456 -f

注:

这里的 -f 参数:恢复时,忽略后面的错误,强制导入

自增表死锁问题分析及处理

今天,研发提交了一个死锁信息,涉及到自增表的死锁,测试,压测产生的死锁信息,并发500,信息如下:
1234

从上面死锁信息来看,lock mode AUTO-INC waiting,应该是表的自增列的问题,初步了解,这个死锁和 innodb_autoinc_lock_mode 的值有一定的关系,但也不因全归咎于mysql的问题。

从5.6的用户手册中查找到AUTO-INC的相关信息:

InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable and repeatable order for a given sequence of INSERT statements

InnoDB在为自增列产生值的时候,使用一种叫做AUTO_INC的表级锁来做控制。这种锁是作用于语句的而不是事务(即语句执行完了锁就会被释放)。使用这种锁是为了确保自增列的值的可预见性和可重复性。可预见性是说当一条insert语句作用于多行时,这些行的自增列基于第一行来说是可预见的;可重复执行是指基于语句的复制在slave重放时自增列的值与master的一致。

innodb_autoinc_lock_mode:

默认值:1,可取值为:0,1,2

在 mysql5.1.22之前,没有这个选项,默认都是0,在并发数大于208以上可能出现很多死锁

下面解释一下innodb_autoinc_lock_mode 几种默认值的含义:

0:traditonal (每次都会产生表锁)

1:consecutive (默认,可预判行数时使用新方式,不可时使用表锁,对于simple insert会获得批量的锁,保证连续插入)

Simple inserts:

直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。(INSERT, INSERT … VALUES(),VALUES())

Bulk inserts:

因为不能确定插入的数量,因此使用和以前的模式相同的表级锁定。(INSERT … SELECT, REPLACE … SELECT, LOAD DATA)

其中 insert ….. select ….. ,是特殊的select加X锁的情况,原因是为保证数据的一致性(M-S环境)

Mixed-mode inserts:

直接分析语句,获得最坏情况下需要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。

(INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);INSERT … ON DUPLICATE KEY UPDATE)

2:interleaved (不会锁表,来一个处理一个,并发最高)

这种模式是来一个分配一个,而不会锁表,只会锁住分配id的过程,和innodb_autoinc_lock_mode = 1的区别在于,不会预分配多个,这种方式并发性最高。

从上面来看,一般 innodb_autoinc_lock_mode = 1 默认值,基本上满足需要。

虽说 innodb_autoinc_lock_mode = 2 不安全,但是在 binlog_format=ROW,transaction-isolation=READ-COMMITTED , innodb_autoinc_lock_mode = 2 是非常安全的。

至于,针对高并发的表,主键列的设置建议如下:

1.采用DB的自增属性,此时,需要调整DB参数,尽可能提高并发:
binlog_format=ROW,transaction-isolation=READ-COMMITTED , innodb_autoinc_lock_mode = 2

2.采用程序生成全局自增ID,利用redis、memcache集合生成 ,这也是推荐的方式