关于数据库分页查询的话题，网上谈论的很多，但开发人员在使用上还是习惯以往的思路。 比如我们有个电话记录表： CREATE TABLE `t_tel_record` ( `f_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '流水号', `f_qiye_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '企业', `f_callno` varchar(20) DEFAULT NULL COMMENT '主叫号码', `f_calltono` varchar(30) DEFAULT NULL COMMENT '被叫号码', `f_Starttime` datetime NOT NULL COMMENT '开始时间', `f_Endtime` datetime DEFAULT NULL COMMENT '结束时间', `f_Calltime` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '通话时间', `f_user_id` bigint(20) NOT NULL COMMENT '员工用户', `f_path` varchar(200) DEFAULT NULL COMMENT '语音文件路径', `f_crm_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '客户库id', `f_call_type` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '0:未知，1:为呼入类型，2:呼出类型', PRIMARY KEY (`f_id`), KEY `idx_endtime_userid` (`f_Endtime`,`f_user_id`,`f_qiye_id`), KEY `idx_crmid` (`f_crm_id`), KEY `idx_qiye_user_calltime` (`f_qiye_id`,`f_Starttime`), KEY `idx_calltono` (`f_calltono`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 查询第1页的数据： SELECT * FROM t_tel_record WHERE f_qiye_id=xxx ORDER BY f_Starttime DESC LIMIT 0,100 当数据量很大，需要查询第10000页的数据： SELECT * FROM t_tel_record WHERE f_qiye_id=xxx ORDER BY f_Starttime DESC LIMIT 999900,100 -- 或者 OFFSET 999900 LIMIT 100 MySQL的 limit m,n 工作原理就是先读取符合where条件的前面m+n条记录，然后抛弃前m条，返回后面n条，所以m越大，偏移量越大，性能就越差。这也是大部分ORM框架生成的分页sql。...

1. slave_exec_mode 参数作用 slave_exec_mode 可以在主从复制中遇到 duplicate-key 和 no-key-found 错误时，自动覆盖或者略过binlog里面这个row_event，避免报错停止复制。 这个参数原本是解决像 NDB Cluster 多节点写入冲突的情况，也可以在普通主从、双主、环形复制等情况下解决冲突，保持幂等性。幂等性怎么定义，感兴趣的可以阅读The differences between IDEMPOTENT and AUTO-REPAIR mode）。 set global slave_exec_mode=IDEMPOTENT （可以动态修改）使从库运行在 幂等模式，对1062，1032等不同的错误类型，有不同的处理： write_row event 遇到主键冲突或唯一索引冲突，这一行被覆写(delete + insert)。 delete时候不是full value match，仅需要主键或唯一索引找到记录则删除 delete_row event 遇到记录不存在，忽略这一行 update_row event 修改唯一索引导致的冲突，忽略这一行 注意： idempotent 模式都是对有疑问的行进行replace或ignore，不影响其它row。 idempotent 模式要求表上必须要有主键 binlog必须是 FULL RBR 模式 2. slave-skip-errors 这个参数不能在线修改，只能加到配置文件里面或者启动的时候带上--slave-skip-errors=1032,1062。除非你真的理解它skip掉了什么，否则不建议使用。 讲一个我所遇到的坑。在我们的一个分库项目中，需要把一个database里面的数据拆成32份，于是做了个主从，把从库里面不需要的那份删除，但复制过来肯定会报 HA_ERR_KEY_NOT_FOUND 错误，于是这也是所期望的，就设置了--slave-skip-errors=1032。 但接下来就出现 1062:HA_ERR_FOUND_DUPP_KEY 错误！从库只会删数据，不会写入和更新，怎么会出现重复数据？读者不妨试想一下为什么。 这里做个说明： ① insert into t values (1, 'a'), (2, 'b'), (3, 'c'); ② begin; ③ delete from t where id=1; ④ delete from t where id in (1, 2, 3); ⑤ insert into t where (3, 'c'), (4, 'd'), (5, 'e'); ⑥ update t set ....

MySQL Binlog 里面记录了每行数据的变更，开发有时候需要根据这些变更的时间、中间值去查问题，是bug导致的，还是用户操作引发的。然而原始binlog内容不利于检索，有段时间使用阿里RDS企业版DMS数据追踪的功能，也能完成这个工作，甚至生成回滚sql，后由于收费以及容量不够的缘故，放弃不用。 本文所介绍的就是基于外面开源的各类组件，整合起来，达到类似数据追踪的功能 —— binlog 可视化。 功能类似：10分钟搭建MySQL Binlog分析+可视化方案 1. 主要技术 项目地址： https://github.com/seanlook/maxwell-graylog docker 使用容器来实现资源的申请和释放，毕竟这类检索binlog内容的需求不多。 本文基于阿里云的容器服务。 maxwell 从mysql server获取binlog和字段信息，组装成json流。建议先阅读 http://xgknight.com/2018/01/13/maxwell-binlog/ 官网：http://maxwells-daemon.io/ graylog 代替ELK技术栈的日志收集、处理、展示平台。社区版够用，需要自行安装，也可以把 graylog 换成 ELK stack。 官网：https://www.graylog.org/ nxlog nxlog 是用 C 语言写的一个开源日志收集处理软件，它是一个模块化、多线程、高性能的日志管理解决方案，支持多平台。 参考：http://blog.csdn.net/weixin_29477879/article/details/52183746 rabbitmq 一款开源消息队列中间件，采用Erlang语言开发，RabbitMQ是AMQP(Advanced Message Queueing Protocol)的标准实现。建议先阅读 http://xgknight.com/2018/01/06/rabbitmq-introduce/ 。 你也可以把消息队列换成kafka。 2. 使用说明 2.1 举例 查看 some3 库 2018-01-22 13:00:00 ~ 2018-01-22 13:00:00 之间，表 t_ecsome_detail 的binlog变化，graylog根据AMQP协议从rabbitmq取binlog json流 提前创建一个 Swarm容器集群，名字叫 maxwell。 在【编排模板】里选择 maxwell-graylog-rabbitmq，【创建应用】下一步修改编排模板： （只修改 environment 里面的变量值） mysql-binlogsvr: image: registry-vpc.cn-hangzhou.aliyuncs.com/workec/mysql_binlogsvr:1.1.3 volumes: - maxwellgraylog_db_data:/var/lib/mysql environment: DBINSTANCE_ID: rm-bp19t9it7c2998633 START_TIME: '2018-01-22 13:00:00' END_TIME: '2018-01-22 14:00:00' ACCESS_ID: LTAIXKHm0v6ob5P4 ACCESS_SECRET: F7g***************Nll19no MYSQL_ROOT_PASSWORD: strongpassword maxwell-svr: image: registry-vpc....

应用场景：同 http://xgknight.com/2017/09/05/mysql-binlog-subscribe-simple-for-dba/ ，但更灵活： 实时同步部分表到另外一个数据库实例 比如在数据库迁库时，将当天表的数据同步到新库，模拟阿里云dms数据传输的功能，相当于在测试环境演练，减少失误。 另外还可以从新库反向同步增量数据到老库，解决测试环境多项目测试引起数据库冲突的问题。 正式切库时的回滚措施 比如数据库迁移项目，切换期间数据写向新库，但如果切换失败需要回滚到老库，就需要把这段时间新增的数据同步回老库（启动消费程序），这就不需要程序段再考虑复杂的回滚设计。 数据库闪回 关于数据库误操作的闪回方案，见 文章MySQL根据离线binlog快速闪回 。binlog2sql的 -B 选项可以将sql反向组装，生产回滚sql。如果需要完善的闪回功能，要进一步开发，提高易用性。 binlog搜索功能 目前组内一版的binlog搜索功能，是离线任务处理的方式，好处是不会占用太大空间，缺点是处理时间较长。通过实时binlog解析过滤的方式，入ES可以快速搜索。需要进一步开发完善。 结合graylog可以实现阿里云RDS类似的数据追踪功能。见 http://xgknight.com/2018/01/25/maxwell-graylog/ rabbitmq介绍：http://xgknight.com/2018/01/06/rabbitmq-introduce/ maxwell介绍：http://xgknight.com/2018/01/13/maxwell-binlog/ 数据已经生成，要完成 MySQL binlog 增量数据同步，还差一个消费者程序，将rabbitmq里面的消息取出来，在目标库重放： ** https://github.com/seanlook/pydbsync ** 目前这个增量程序重放动作是： binlog里面 insert 和 update 行，都变成 replace into binlog里面 delele ，变成 delete ignore xxx limit 1 alter/create，原封不动 所以如果表上没有主键或者唯一索引，是非常难搞定的，原本的update变成 replace into 多插入一条数据。当然如果把 update 事件改成 update tables set f1=v1,f2=v2 where f1=v1,f2=vv2 limit 1 也没毛病。 使用python3，安装rabbitmq 的python客户端即可：pip install pika config.py 增量程序的配置文件 db_info: 指定要写入的目标db rabbitmq_conn_info: 增量数据的来源，rabbitmq连接信息 rabbitmq_queue_bind: 指定怎么划分队列...

1. 介绍 Maxwell 是java语言编写的能够读取、解析MySQL binlog，将行更新以json格式发送到 Kafka、RabbitMQ、AWS Kinesis、Google Cloud Pub/Sub、文件，有了增量的数据流，可以想象的应用场景实在太多了，如ETL、维护缓存、收集表级别的dml指标、增量到搜索引擎、数据分区迁移、切库binlog回滚方案，等等。 它还提供其它功能： 支持SELECT * FROM table 的方式做全量数据初始化 支持主库发生failover后，自动恢复binlog位置（GTID） 灵活的对数据进行分区，解决数据倾斜的问题。kafka支持 database, table, column等级别的数据分区 它的实现方式是伪装成MySQL Server的从库，接收binlog events，然后根据schemas信息拼装，支持ddl,xid,rows等各种event. maxwell由 zendesk 开源：https://github.com/zendesk/maxwell ，而且维护者相当活跃。 网上已有人对 Alibaba Cannal, Zendesk Maxwell, Yelp mysql_streamer进行对比，见文后参考 实时抓取MySQL的更新数据到Hadoop。 类似功能的还有：http://debezium.io/docs/connectors/mysql/ 安装 使用 maxwell 非常简单，只需要jdk环境 yum install -y java-1.8.0-openjdk-1.8.0.121-1.b13.el6.x86_64 curl -sLo - https://github.com/zendesk/maxwell/releases/download/v1.12.0/maxwell-1.12.0.tar.gz \ | tar zxvf - cd maxwell-1.12.0 # 默认寻找当前目录下的 config.properties 配置文件 要求 mysql server binlog格式是 ROW， row_image 是 FULL。感受一下输出结果 mysql> update test.e set m = 5....

rabbitmq可以用一本书取讲，这里只是介绍一些使用过程中，常用到的基本的知识点。 官方文档覆盖的内容，非常全面：http://www.rabbitmq.com/documentation.html 。 1. 介绍 RabbitMQ，即消息队列系统，它是一款开源消息队列中间件，采用Erlang语言开发，RabbitMQ是AMQP(Advanced Message Queueing Protocol)的标准实现。 AMQP是一个公开发布的异步消息的规范，是提供统一消息服务的应用层标准高级消息队列协议，为面向消息的中间件设计.消息中间件主要用于组件之间的解耦，消息的发送者无需知道消息使用者的存在，反之亦然。 https://www.rabbitmq.com/tutorials/amqp-concepts.html 相对于JMS(Java Message Service)规范来说，JMS使用的是特定语言的APIs，而消息格式可自由定义，而AMQP对消息的格式和传输是有要求的，但实现不会受操作系统、开发语言以及平台等的限制。 JMS和AMQP还有一个较大的区别：JMS有队列(Queues)和主题(Topics)两种消息传递模型，发送到 JMS队列 的消息最多只能被一个Client消费，发送到 JMS主题 的消息可能会被多个Clients消费；AMQP只有队列(Queues)，队列的消息只能被单个接受者消费，发送者并不直接把消息发送到队列中，而是发送到Exchange中，该Exchage会与一个或多个队列绑定，能够实现与JMS队列和主题同样的功能。 另外还有一种 MQTT协议，意为消息队列遥测传输，是IBM开发的一个即时通讯协议。由于其维护一个长连接以轻量级低消耗著称，所以常用于移动端消息推送服务开发。MQTT是基于TCP的应用层协议封装，实现了异步Pub/Sub，在物联网（IoT）应用广泛。 RabbitMQ可通过库、插件的形式，支持JMS和MQTT协议。参考：http://geek.csdn.net/news/detail/71894 1.1 主要概念 Broker 接收和分发消息的应用，RabbitMQ Server就是Message Broker Exchange message到达broker的第一站，根据分发规则，匹配查询表中的routing key，分发消息到queue中去。常用的类型有：direct, topic, fanout。 如果没有队列绑定到exchange上，那么该exchange上的消息都会被丢弃，因为它不存储消息又不知道该怎么处理消息。 Queue 消息队列载体，每个消息都会被投入到一个或多个队列 Binding 在exchange和queue之间建立关系就叫Binding，消费者声明队列的时候一般会指定routing_key，也可以叫binding_key。Binding信息被保存到exchange中的查询表中，用于message的分发依据。 Routing Key 这里区分一下binding和routing: binding是一个将exchange和queue关联起来的动作，routing_key可以理解成队列的一个属性，表示这个队列接受符合该routing_key的消息，routing_key需要在发送消息的时候指定。 Vhost 于多租户和安全因素设计的，把AMQP的基本组件划分到一个虚拟的分组中，类似于网络中的namespace概念。当多个不同的用户使用同一个RabbitMQ server提供的服务时，可以划分出多个vhost，每个用户在自己的vhost创建exchange／queue等 Producer 消息生产者，就是投递消息的程序。只负责把消息发送exchange，附带一些消息属性。 Consumer 消息消费者，就是接受消息的程序。 Channel 如果每一次访问RabbitMQ都建立一个Connection，在消息量大的时候建立TCP Connection的开销将是巨大的，效率也较低。 Channel是在connection内部建立的逻辑连接，如果应用程序支持多线程，通常每个thread创建单独的channel进行通讯，AMQP method包含了channel id帮助客户端和message broker识别channel，所以channel之间是完全隔离的。Channel作为轻量级的Connection极大减少了操作系统建立TCP connection的开销。 1.2 对比 rabbitmq activemq rocketmq kafka zeromq redis celery 待续 2. 安装配置 CentOS 6....

SchemaSync是个能够在mysql数据库之间，比较并生成表结构差异的工具，项目地址 https://github.com/mmatuson/SchemaSync 。 SchemaSync介绍与使用 因为工作中经常需要在各个环境之间同步表结构，特别是生产与测试环境之间，长时间的运行后，总会有不一致的。测试环境的表结构一般是测试验证功能之后没有问题，然后通过工单的形式由DBA在生产环境修改。但生产库的结构，如修改索引，紧急修改字段长度，久而久之就会与测试环境有差异，需要同步到测试环境。 又或者有多套测试环境之间要保持结构同步，又比如同一类db（分库）的情况下，比较schema之间的对象差异。 SchemaSync不仅限于表结构，它可以处理的对象还有：视图、事件、存储过程、函数、触发器、外键，与 mysql-utilities 相当。但 SchemaSync 更适合于实践： 默认不会同步 AUTO_INCREMENT 和 COMMENT`，有选项可以控制 对不存在的对象会生成对应的CREATE，对多余的对象会生成DROP 对生成 alter…column 的sql，是有列顺序的 安装简单，相比mysqldiff，要安装mysql-connector-python和一整套mysql-utilities工具 当然前两点在我自己的 mysqldiff 版本里，已经加入了支持，见 MySQL数据库表结构同步之mysqldiff SchemaSync安装： （使用virtualenv） $ pip install mysql-python pymysql schemaobject schemasync SchemaObject也是同一个作者的，专门用于操作数据库对象的库，于是schemasync只需要获取对象，比较差异，然后调用schemaobect生成sql。（SchemaObject依赖pymysql，SchemaSync依赖MySQLdb，其实可以用同一个） SchemaSync用法： $ schemasync --help Usage: schemasync [options] <source> <target> source/target format: mysql://user:pass@host:port/database A MySQL Schema Synchronization Utility Options: -h, --help show this help message and exit -V, --version show version and exit. -r, --revision increment the migration script version number if a file with the same name already exists....

生产库遇到过好几例本文要讨论的案例，而且比较棘手。简而言之，有类似这样的查询 SELECT * FROM t1 where t1.f2>1 and t2.f2<100 order by t1.id，id是主键，条件里面有个range查询，就会造成优化器是选择主键，还是选择filesort问题，有些特殊情况就会选错索引，比如为了回避内存排序，选择了主键扫描，导致原本走范围过滤再sort 500ms勉强可以结束的查询，5分钟不出结果。 下面具体来这个案例。 1. 背景 阿里云RDS，5.6.16-log。 表 d_ec_someextend.t_tbl_test_time_08: CREATE TABLE `t_tbl_test_time_08` ( `f_some_id` int(11) unsigned DEFAULT '0', `f_qiye_id` int(11) DEFAULT '0', `f_type` tinyint(3) DEFAULT '0' COMMENT '有效联系类型 1: QQ联系，2:拨打电话，3:发送邮件，4:发送短信，5:添加跟进记录，6:拜访客户，7:EC联系，8:更新客户阶段', `f_contact_time` timestamp NULL DEFAULT '1970-01-01 16:00:01', UNIQUE KEY `some_qiye_type` (`f_some_id`,`f_qiye_id`,`f_type`), KEY `f_contact_time` (`f_contact_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 表索引信息： mysql> show table status like "t_tbl_test_time_08"; +-----------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+--------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Block_format | +-----------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+--------------+ | t_tbl_test_time_08 | InnoDB | 10 | Compact | 19264318 | 45 | 882900992 | 0 | 2176843776 | 752877568 | NULL | 2017-10-25 20:27:08 | NULL | NULL | utf8mb4_general_ci | NULL | | | Original | +-----------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+--------------+ 1 row in set mysql> show index from t_tbl_test_time_08; +--------------------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t_tbl_test_time_08 | 0 | some_qiye_type | 1 | f_some_id | A | 19264318 | NULL | NULL | YES | BTREE | | | | t_tbl_test_time_08 | 0 | some_qiye_type | 2 | f_qiye_id | A | 19264318 | NULL | NULL | YES | BTREE | | | | t_tbl_test_time_08 | 0 | some_qiye_type | 3 | f_type | A | 19264318 | NULL | NULL | YES | BTREE | | | | t_tbl_test_time_08 | 1 | f_contact_time | 1 | f_contact_time | A | 9632159 | NULL | NULL | YES | BTREE | | | +--------------------+------------+-----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set 问题查询：...

1. 现象 生产环境数据库在某一刻突然发现大量活跃连接，而且大部分状态是 updating 。问题出现在周六上午，持续了大概三四分钟，得益于我们自己的快照程序，拿到了当时现场的的processlist, 锁等待关系，innodb status 信息：(经过脱敏处理) innodb_status.txt片段： var_mydb_snapshot.html （也可以通过 pt-stalk 收集） 首先在 Lock Waits Info 一节，看到每行的trx_id(事务)的role分为 Blocker(引起阻塞的线程) 与 Blockee（被阻塞者）；最后一列 blocking_trx_id 在role是Blockee时才有值，代表谁阻塞了当前事务。 根据上面的关系，可以得出以下结论： 事务 19705811640 运行了231s，阻塞了19706118937、19706124453、19706124752，而这些事务都在做同一个UPDATE语句 被锁定的记录是 mydb.mytable1表的主键索引值为 5317885 行 事务 19706124752 既被阻塞，也阻塞了别人19706125253 不难发现 19705811640 应该最先运行的事务，且对其它事务产生了链式阻塞，它的thread_id是 9898630，来源IP 但是当你兴冲冲的找到引起阻塞的事务 19705811640 在做什么事情时，发现它没有任何sql的信息，lock info以及processlist里面都是None。那么有哪些情况会导致在会话是活跃的，但sql的内容为空： 执行show processlist的时候，刚好在事务里面两个sql的中间 sql已经执行完成，但长时间没有提交 2. 初步分析 其实这个现象已经遇到过很多次了，第1个原因常发生在 大量单条记录更新 的情况，一个sql在一个事务里循环执行10000次，即使每条都很快，但大部分时间都在网络传输上，（可以改成批量的形式）。在本案例基本上能确定的是第2个原因：事务开启之后，sql也执行了，但中间又做别的事情去了。那么怎样才能知道这个事务是什么内容呢？两个方向去找： 从来源ip上的应用程序的日志里分析 binlog里面分析 应用程序日志里可以看 10:21:00 ~ 10:26:00 之间，mydb.mytable1 表上主键id=5317885 在做什么事情。因为我们上了听云，在听云APM里面也可以清楚的看到这个时间点的哪个方法慢： 响应时间230多秒，从“相关SQL”里面看到操作的记录内容，确定就是它了(根据innodb status快照时间 - ACTIVE 230.874 sec，倒推得到的时间与这里刚好吻合)。从接口名称也清楚的知道是在进行禁用用户的操作，猜想： 禁用用户的逻辑上有先挪到回收站，再删资料、删权限、删关系，清理缓存等等一系列操作，放在事务里保证他们的原子性，似乎是合理的。但为什么执行了将近4分钟还没有提交呢，分析相关的sql效率都很高。 有三种情况： 这个事务执行到一半，它需要操作的数据被别人锁住，等待了这么久 类似事务要操作5000条数据，但是一条一条的操作，然后一起提交（已出现过类似的例子） 事务务执行完成很快，但调用其它接口迟迟没有返回，导致事务没提交。 不会是1和2，因为从一开始的分析看到事务 19705811640 都是在阻塞别人，而不是受害者。那么结合上图中有个有两个操作redis的接口执行时间占比96%，可以下定论了：...