1. Story 在没有形成自己的数据库管理平台以前，数据库实例一多（包括生产和测试环境），许多表要执行DDL会变得异常繁杂。 说个自己的经历，需要改现网的一个索引来看优化的效果，因为存在风险，不会一次全改，先只改1个库，然后逐步放开。前后验证效果可能花上一两周的时间，除非实现完整的记录了当时的ddl语句和对应的库，否则根本难以记得。这就完全依赖于个人的习惯及能力。 又比如现网出了个问题，开发追查到一个时间点，想确认那个时候有没有对库表进行过更改操作，如果没有记录表结构变更的历史，也就难以提供需要的信息。 记录差异，很早就思考过能不能用git来做。终于花了一天时间来实现，并验证、修改达到预期的效果，还算满意。 github项目地址在文后。 2. Concept 思路很简单，就是利用 mydumper 导出表时会把各表（结构）单独导成一个文件的特性，每天低峰期导出所有对象元数据：表、视图、存储过程、事件、触发器。需要过滤掉 AUTO_INCREMENT 值。 结构内容存放在一个git仓库下，通过shell脚本提交到 gitlab。所有DDL更改由原来依赖于DBA的主动记录，变成被动采集。 测试环境和生产环境表结构总会有些差异，为了兼顾同时收集两个环境的数据，设置了 environment 选项，根据当前所在运行的机器，自动判断采集哪些实例信息。 3. Usage 首先你需要能够存放表结构信息的git仓库，如gitlab，而且建议设置为私有。 安装 git 和 mydumper mydumper 0.9.1 版本需要编译安装，可以参考这里 file-mydumper-install-ubuntu14-04-sh。当然 yum 或 apt-get 安装其他版本也是一样的。 脚本会尝试自动获取 mydumper 命令的路径。 注意配置git权限的时候，最好不允许其它用户手动提交修改仓库内容。 配置db实例地址 settings.ini示例： [environment] production=puppetmaster test=puppettestmaster [production] production_auth=your_defaultuser:yourpassword db_name1=192.168.1.100:3306 db_name2=192.168.1.101:3306 db_name3=name3.dbhost.com:3306 db_name4=192.168.1.100:3306:myuser:mypassword [test] test_auth=user1:password1 db_name1=10.0.100.1:3306 db_name2=10.0.100.1:3307 db_name3=10.0.100.2:3306 db_name4=10.0.100.3:3306:myuser1:mypassword1 上面的配置采集 production和test两个环境的表结构，识别两个环境是根据 hostname 来决定的。这样做的好吃就是这个脚本在两个环境下运行不需要做任何修改。 [production]节的名字就是 [environment]节指定的名字 production=xx dbname1=就是配置各个db，地址+端口的形式。用户名和密码可以继续用 : 跟上 production_auth=表示 production 环境下，如 dbname1没有配置用户名时，默认采用这个用户名和密码。这样设计主要是简化配置。 该数据库用户需要 select,show view,event,trigger,procedure 权限。...

1. 简介 项目地址：https://github.com/seanlook/px-table-checksum 主从环境下数据一致性校验经常会用 pt-table-checksum 工具，它的原理及实施过程之前写过一篇文章：生产环境使用 pt-table-checksum 检查MySQL数据一致性。但是DBA工作中还会有些针对两个表检查是否一致，而这两个表之间并没有主从关系，pt工具是基于binlog把在主库进行的检查动作，在从库重放一遍，此时就不适用了。 总会有这样特殊的需求，比如从阿里云RDS实例迁移到自建mysql实例，它的数据传输服务实现方式是基于表的批量数据提取，加上binlog订阅，但强制row模式会导致pt-table-checksum没有权限把会话临时改成statement。另一种需求是，整库进行字符集转换：库表定义都是utf8，但应用连接使用了默认的 latin1，要将连接字符集和表字符集统一起来，只能以latin1导出数据，再以utf8导入，这种情况数据一致性校验，且不说binlog解析程序不支持statement（如canal），新旧库本身内容不同，pt-table-checksum 算出的校验值也会不一样，失效。 所以才萌生了参考 pt-table-checksum 自己写了一个：px-table-checksum 。 2. 实现方法 整体思路是借鉴pt-table-checksum，从源库批量（即chunk）取出一块数据如1000行，计算CRC32值，同样的语句在目标库运行一遍，结果都存入另一个库，最后检查对应编号的chunk crc值是否一致。知道不一致还不行，得能否快速方便的修复差异，所以继续根据那些不一致的chunk，去目标库和源库找到不一致的行，是缺失，还是多余，还是被修改了，然后生成修复sql，根据指示是否自动修复。 那么问题就在于： 如何确定批次，也就是下一个chunk该怎么取？ 我还没想做到pt-table-checksum那样，可以根据负载动态调整chunk大小，甚至活跃线程数超过阀值就暂停检查，上来工作量就太大了。目前每次计算的chunk的行数是固定的，可以配置1000或2000等。 所以就要用到分页查询，根据（自增或联合）主键、唯一索引，每次limit 1000后升序取最后一条，作为下一批的起始。所以要分析表上的键情况，组合查询条件。目前仅能检查有主键或唯一所以的表。 如何保证源库和目标库，运行的sql一样？ 之前一版是目标库和源库，以多线程各自计算chunk，入库，后来才意识到严重的bug：比如同样是取1000行，如果目标库少数据，那么下一个chunk起始就不一样，比较的结果简直一塌糊涂。 所以必须保证相同编号的chunk，起点必须相同，所以想到用队列，存放在源库跑过的所有校验sql，模拟pt工具在目标库重放。考虑到要多线程同时比较多个表，队列可能吃内存过大，于是使用了redis队列。 直接在数据库中计算crc32，还是取出数据在内存里计算？ 翻了pt-table-checksum的源码，它是在数据库里计算的。但是第一节里说过，如果目标库和源库要使用不同的字符集才能读出正确的数据，只能查询出来之后再比较。所以 px-table-checksum 两种都支持，只需指定一个配置项。 同时检查多个表，源库sql挤在队列，目标库拿出来执行时过了1s，此时源库那条数据又被修改了一次同步到了目标库，会导致计算结果不一致，实则一致，怎么处理 无法处理，是px-table-checksum相比pt-table-checksum最大的缺陷。 但为了尽可能减少此类问题（比如主从延迟也可能会），特意设计了多个redis队列，目标库多个检查线程，即比如同时指定检查8个表，源库检查会有8个线程对应，但可以根据表的写入情况，配置4个redis队列（目前是随机入列），10个目标库检查线程，来减少不准确因素。 但站在我的角度往往来说，不一致的数据会被记录下来，如果不多，人工核对一下；如果较多，就再跑一遍检查，如果两次都有同一条数据不一致，那就有情况了。 3. 限制 如果检查期间源表数据，变化频繁，有可能检查的结果不准确 也就是上面第4点的问题。很明显，这个程序每个检查的事务是分开的，不像pt工具能严格保证每条检查sql的事务顺序。但有不一致的数据再排查一下就ok了。实际在我线上使用过程中，99.9%是准确的。 表上必须有主键或唯一索引 程序会检查，如果没有会退出。 varbinay,blob等二进制字段不支持修复 其实也不是完全不支持，要看怎么用的。开发如果有把字符先转成字节，再存入mysql，这种就不支持修复。是有办法可以处理，那就是从源库查时用 hex()函数，修复sql里面unhex()写回去。 4. 使用说明 该python程序基于2.7开发，2.6、3.x上没有测试。使用前需要安装 MySQLdb和hotqueue： $ sudo pip install MySQL-python hotqueue 要比较的表和选项，使用全配置化，即不通过命令行的方式指定（原谅命令行参数使用方式会额外增加代码量）。 4.1 px-table-checksum.py 主程序，运行python px-table-checksum.py 执行一致性检查，但一定了解下面的配置文件选项。 4.2 settings_checksum.py 配置选项 CHUNK_SIZE: 每次提取的chunk行数 REDIS_INFO: 指定使用redis队列地址 REDIS_QUEUE_CNT: redis队列数量，消费者（目标库）有一一对应的线程守着队列 REDIS_POOL_CNT: 生产者（源库）redis客户端连接池。这个设计是为了缓解GIL带来的问题，把入列端与出列端分开，因为如果表多可能短时间有大量sql入队列，避免hotqueue争用...

1. 简介 取名mypumpkin，是python封装的一个让mysqldump以多线程的方式导出库表，再以mysql命令多线程导入新库，用于成倍加快导出，特别是导入的速度。这一切只需要在 mysqldump 或 mysql 命令前面加上 mypumpkin.py 即可，所以称作魔法。 项目地址：https://github.com/seanlook/mypumpkin 该程序源于需要对现网单库几百G的数据进行转移到新库，并对中间进行一些特殊操作（如字符集转换），无法容忍mysqldump导入速度。有人可能会提到为什么不用 mydumper，其实也尝试过它但还是放弃了，原因有： 不能设置字符集 mydumper强制使用 binary 方式来连接库以达到不关心备份恢复时的字符集问题，然而我的场景下需要特意以不同的字符集导出、再导入。写这个程序的时候正好在公众号看到网易有推送的一篇文章 (解密网易MySQL实例迁移高效完成背后的黑科技)，提到他们对mydumper的改进已支持字符集设置，可是在0.9.1版本的patch里还是没找到。 没有像 mysqldump 那样灵活控制过滤选项（导哪些表、忽略哪些表） 因为数据量之巨大，而且将近70%是不变更的历史表数据，这些表是可以提前导出转换的；又有少量单表大于50G的，最好是分库导出转换。mydumper 不具备 mysqldump 这样的灵活性 对忽略导出gtid信息、触发器等其它支持 阿里云rds 5.6 导出必须要设置 set-gtid-purged=OFF 另外有人还可能提到 mysqlpump —— 它才是我认为mysqldump应该具有的模样，语法兼容，基于表的并发导出。但是只有 mysql服务端 5.7.9 以上才支持，这就是现实和理想的距离。。。 2. 实现方法 首先说明，mysqldump的导出速度并不慢，经测试能达到50M/s的速度，10G数据花费3分钟的样子，可以看到瓶颈在于网络和磁盘IO，再怎样的导出工具也快不了多少，但是导入却花了60分钟，磁盘和网络大概只用到了20%，瓶颈在目标库写入速度（而一般顺序写入达不到IOPS限制），所以mypumpkin就诞生了 —— 兼顾myloader的导入速度和mysqldump导出的灵活性。 用python构造1个队列，将需要导出的所有表一次放到队列中，同时启动N个python线程，各自从这个Queue里取出表名，subprocess调用操作系统的mysqldump命令，导出数据到以 dbname.tablename.sql 命名的文件中。load in 与 dump out 类似，根据指定的库名或表名，从dump_dir目录找到所有sql文件，压进队列，N个线程同时调用mysql构造新的命令，模拟 < 操作。 参数解析从原来自己解析，到改用argparse模块，几乎做了一次重构。 对于没有指定--tables的情况，程序会主动去库里查询一下所有表名，然后过滤进队列。 load in目标库，选项做到与dump out一样丰富，可以指定导入哪些db、哪些表、忽略哪些表。 其中的重点是做到与原mysqldump兼容，因为需要对与表有关的选项（-B, -A, --tables, --ignore=），进行分析并组合成新的执行命令，考虑的异常情况非常多。 3. 限制 重要：导出的数据不保证库级别的一致性 对历史不变表，是不影响的 具体到一个表能保证一致性，这是mysqldump本身采用哪些选项决定的 不同表导出动作在不同的mysqldump命令中，无法保证事务。 在我的案例场景下，是有开发同学辅助使用一套binlog解析程序，等完成后重放所有变更，来保证最终一致性。 另，许多情况下我们导数据，并不需要完整的或者一致的数据，只是用于离线分析或临时导出，重点是快速拿数据给到开发。 不寻常选项识别 程序已经尽力做到与mysqldump命令兼容，只需要加上 mypumpkin....

1. utf8 与 utf8mb4 异同 先看 官方手册 https://dev.mysql.com/doc/refman/5.6/en/charset-unicode-utf8mb4.html 的说明： The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. The utf8mb4 character set uses a maximum of four bytes per character supports supplementary characters: - For a BMP character, utf8 and utf8mb4 have identical storage characteristics: same code values, same encoding, same length. - For a supplementary character, utf8 cannot store the character at all, whereas utf8mb4 requires four bytes to store it....

最近一个与qq有关的服务迁到腾讯云上，相应的数据库也要从原阿里云RDS迁移到腾讯云CDB上，经过一番摸索，不带任何政治色彩的说，CDB跟RDS相比弱的不止一条街。比如看个错误日志还要提工单，数据库访问没有白名单，数据传输服务竞不支持源库的开启GTID，自带的后台管理是phpMyAdmin，要临时看查询日志也要提工单，当然这些都是可以容忍通过其它方法解决的，但是如果使用上带来了mysql数据库本身的影响，就用的不太爽了。 最近2个月一直在弄与字符集相关的工作，却还是在cdb踩到一个大坑。情况是这样的，我们旧的RDS上的数据库表定义都是utf8，但由于历史原因，开发一直使用 latin1 去连接的。现在要把这样的一个db迁移到CDB，腾讯云的数据传输服务出了点问题，于是想了办法用阿里云的DTS反向迁。现象是： 用Navicat客户端latin1连接，旧数据显示都ok 但程序端看到历史数据全是乱码，新数据正常 而且新数据通过navicat去看用 utf8 连接才正常 在mysql命令行下手动 set names latin1 读取旧数据ok，但新数据乱码 这明显是新写入的时候就是以 utf8 连接的，读取的时候新旧数据也以 utf8 连接。但应用端已明确设置使用 latin1 连接来读写。为了验证是否CDB的问题，在相同环境下自建了个mysql实例，一切都ok。 腾讯云工程师先是怀疑迁移有问题，后来说可能是character_set_server设置的问题，我站在2个月来处理字符集的经验看了虽然不太可能，还是配合截了几个图，在工单、电话了里撕了几个来回： 因为跟腾讯有合作关系，上头就直接联系到了腾讯云的人，这才找到问题根源：都是--skip-character-set-client-handshake惹的祸。 --character-set-client-handshake Do not ignore character set information sent by the client. To ignore client information and use the default server character set, use --skip-character-set-client-handshake; this makes MySQL behave like MySQL 4.0 一看到这个选项就恍然大悟了，官方文档FAQ里有专门介绍：A.11.11（个人感觉最后一段贴的结果有问题），大意是说为了兼容 mysql 4.0 的习惯，mysqld启动时加上 --skip-character-set-client-handshake 来忽略客户端字符集的设置，强制使用服务端character-set-server的设置。 但这个选项默认是没有开启的，当你在web控制台修改了实例字符集时，CDB自作自作主张修改了这个参数并重启 character_set_client_handshake = 0 。而这个参数在 show variables 看不到的，隐藏的比较深。正好我建实例的时候选择了utf8，然后修改为utf8mb4，但应用端要求latin1，便中枪了。 主要是以前没听过这个参数，后来发现老叶也有篇文章讲到它 MySQL字符集的一个坑，其实是很小的东西，结果排查验证问题前后花了2天。。。...

Python完成的一个小程序，初衷用于杀掉 MySQL 上的异常线程，如慢查询、处于Sleep状态的，但上线运行以后，以另一种模式运行来实时发现现网的慢查询特别有用，挖掘了许多潜在问题。 项目地址：https://github.com/seanlook/myquerykill 在使用阿里云RDS的过程中，数据库出现异常，需要快速恢复。网上有许多类似的kill脚本，都是通过 mysqladmin 实现的。然而 Ali-RDS 环境有以下限制： 不提供 SUPER 权限的用户，也就是用户只能 kill 自己的线程 当连接数暴增时，外部用户无法登陆，包括控制台 为了解决上面两大问题，该 python 脚本通过在db实例上，使用多线程的方式，为每个用户保留一个连接，并实时读取指令配置文件 mysqk.ini，发现有 kill 需求时，利用对应用户已有连接找到 information_schema.processlist 中符合条件的线程，并 kill 。 说明：该脚本在9月份做过一次重写，7月份的版本（分支 old_0.5.0）是每实例每用户，对应一个线程，db实例一多线程数也太多，看得始终不太优雅，于是改成了一个db实例一个线程，维护同时维护多个用户的会话。同时新版也加入了更多的功能，如按时间窗口检查，包含或排除特定连接，邮件通知，配置项覆盖。 1. 特性 始终通过 mysql ping 维持一个长连接，并有断开自动重来机制，解决没有连接可用的尴尬 每个db实例有自己的线程，避免需要单独登陆个别用户去kill的繁复操作。 如果你具有 SUPER 权限，也可以简化配置做到兼容 能够分开应对需要杀死线程的场景： 长时间运行超过 N 秒的 Sleep 状态的事务 （一般不建议，但有时候kill它，可以快速释放连接给管理员使用） 排除一些线程不能kill，如 Binlog dump。可配置 包含特定关键字的线程要kill 出现符合条件的线程时，会对当时的processlist, engine status，lock_wait 做一个快照，并邮件发出。妈妈再也不愁没有事故现场了。 有试运行dry_run模式，即执行所有的检查过程但不真正kill 这便是开头所讲的，实时关注生产环境慢查询，而不是等出现问题被动去看slow log，严重的情况连接数可能已经爆了 支持只在时间窗口内运行，考虑到晚上一些长任务不检查 密码加密 2. 快速上手 需要pip安装MySQL-python和pycrypto两个库，只在python 2.7上有测试。 在 settings.py 里面设置连接的用户名和密码信息。这里假设同一批db的要check的认证信息是一样的，指定的用户既用于登录认证，也用于告知脚本哪些用户需要被检查。 密码要通过 prpcryptec.py 加密，加密的密钥需写入脚本本身的 KEY_DB_AUTH变量。（担心泄露的话，把mysqk.py编译成 pyc 来跑）...

经常会被问到 InnoDB隔离级别中 READ-COMMITED和REPEATABLE-READ 的区别，今天就整理了一下，不再从“脏读”、“幻读”这样的名词解释一样去回答了。 1. 行锁 InnoDB行锁实际锁的是索引记录，为了防止死锁的产生以及维护所需要的隔离级别，在执行sql语句的全过程中，innodb必须对所需要修改的行每条索引记录上锁。如此一来，如果你执行的 UPDATE 没有很好的索引，那么会导致锁定许多行： update employees set store_id = 0 where store_id = 1; ---TRANSACTION 1EAB04, ACTIVE 7 sec 633 lock struct(s), <strong>heap size 96696</strong>, 218786 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query id 47 localhost root show engine innodb status 上面的 employees 表 store_id 列没有索引。注意 UPDATE 已经执行完成（没有提交），但依然有 218786 个行锁没有释放，还有一个undo记录。这意味着只有一行被更改，但却持有了额外的锁。堆大小（heap size）代表了分配给锁使用的内存数量。 在 REPEATABLE-READ 级别，事务持有的 每个锁 在整个事务期间一直被持有。 在 READ-COMMITED 级别，事务里面特定语句结束之后，不匹配该sql语句扫描条件的锁，会被释放。...

这段时间在公司内部准备了一个分享，主题是关于 MySQL事务与锁，准备过程内容很多，也深入弄清楚了一些以前比较迷糊的地方，加上后面的讨论也就一个半小时。 主要涉及的是乐观锁与悲观锁，InnoDB多版本并发控制的实现，以及隔离级别与各种情况加锁分析，因为涉及的主要还是开发人员，所以不是很深奥。也算花了不少心血，分享一下。 slideshare: http://www.slideshare.net/ssuser5a0bc0/my-sql-seanlook {% pdf http://github.com/seanlook/sean-notes-comment/raw/main/static/mysql-ppt-trx_isolation-lock-seanlook.pdf 900 512 %} 原文连接地址：http://xgknight.com/2016/08/30/mysql-ppt-trx_isolation-lock/

端午在家无聊，又不想学习。于是在Youtube随便逛，看到一个很不错的分享，来自 Percona Database Performance。下面是演示稿： slideshare: http://www.slideshare.net/ssuser5a0bc0/webinar-2013-advancedquerytuning {% pdf https://www.slideshare.net/slideshow/embed_code/key/3HLJJcJmM9KLGT %} Youtube: https://www.youtube.com/watch?v=TPFibi2G_oo 能 条件 的可以看看。 Percona webinars上有许多类似的分享，传送门： https://www.percona.com/resources/webinars ，不少是他们CEO Peter Zaitsev 亲自上马的。 原文连接地址：http://xgknight.com/2016/06/11/mysql-advanced-query-tuning-percona/

如果正在看这篇文章，相信你已经知道自己的需求了。 在 mysql 5.5 版本以前，修改表结构如添加索引、修改列，需要锁表，期间不能写入，对于大表这简直是灾难。从5.5特别是5.6里，情况有了好转，支持Online DDL，相关介绍见 这篇文章，而我在实际alter table过程中还是会引起 data meta lock 问题。pt-online-schema-change是Percona-toolkit一员，通过改进原生ddl的方式，达到不锁表在线修改表结构。 1. pt-osc工作过程 创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构) 在新表执行alter table 语句（速度应该很快） 在原表中创建触发器3个触发器分别对应insert,update,delete操作 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表 Rename 原表到old表中，在把临时表Rename为原表 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理 默认最后将旧原表删除 2. 常用选项说明 只介绍部分常用的选项 --host=xxx --user=xxx --password=xxx 连接实例信息，缩写-h xxx -u xxx -p xxx，密码可以使用参数--ask-pass 手动输入。 --alter 结构变更语句，不需要 ALTER TABLE关键字。与原始ddl一样可以指定多个更改，用逗号分隔。 绝大部分情况下表上需要有主键或唯一索引，因为工具在运行当中为了保证新表也是最新的，需要旧表上创建 DELETE和UPDATE 触发器，同步到新表的时候有主键会更快。个别情况是，当alter操作就是在c1列上建立主键时，DELETE触发器将基于c1列。 子句不支持 rename 去给表重命名。 alter命令原表就不支持给索引重命名，需要先drop再add，在pt-osc也一样。(mysql 5.7 支持 RENAME INDEX old_index_name TO new_index_name) 但给字段重命名，千万不要drop-add，整列数据会丢失，使用change col1 col1_new type constraint（保持类型和约束一致，否则相当于修改 column type，不能online） 子句如果是add column并且定义了not null，那么必须指定default值，否则会失败。 如果要删除外键（名 fk_foo），使用工具的时候外键名要加下划线，比如--alter "DROP FOREIGN KEY _fk_foo"...