ProxySQL之连接复用(multiplexing)以及相关问题说明

ProxySQL在连接池(persistent connection poll)的基础上,还有一个连接复用的概念 multiplexing connection,官方的wiki里没有很明确的说明,但在作者的一些 blog post 和 issue 里能找到解答: https://github.com/sysown/proxysql/issues/939#issuecomment-287489317 ...

2017-04-17 · 10 min

ProxySQL之读写分离与分库路由演示

本文演示使用ProxySQL来完成读写分离和后端分库的一个实际配置过程,安装及配置项介绍见前文 ProxySQL之安装及配置详解。 环境 1 2 3 4 5 6 7 8 9 10 11 instance0: 10.0.100.100 (db0,db2,db4,db6) instance1: 10.0.100.101 (db1,db3,db5,db7) instance2: 10.0.100.102 (db2,db6,db10,db14) instance3: 10.0.100.103 (db3,db7,db11,db15) instance0 slave: 192.168.10.4:3316 instance1 slave: 192.168.10.4:3326 instance2 slave: 192.168.10.4:3336 instance3 slave: 192.168.10.4:3346 proxysql node0: 10.0.100.36 现在想达到这样一个目的:客户端应用连接上 proxysql 的ip:port,连接时指定分库db名,执行sql时自动路由到对应的实例、对应的库。考虑下面的部署结构: ...

2017-04-17 · 8 min

ProxySQL之安装及配置详解

ProxySQL是一个高性能的MySQL中间件,拥有强大的规则引擎。具有以下特性: 连接池,而且是 multiplexing 主机和用户的最大连接数限制 自动下线后端DB 延迟超过阀值 ping 延迟超过阀值 网络不通或宕机 强大的规则路由引擎 实现读写分离 查询重写 sql流量镜像 支持prepared statement 支持Query Cache 支持负载均衡,与gelera结合自动failover 集这么多优秀特性于一身,那么缺点呢就是项目不够成熟,好在作者一直在及时更新,并且受到 Percona 官方的支持。 ...

2017-04-10 · 13 min

清明闲扯

这个清明假期,没有做特意的安排,4月1日晚上的时候冒出个想法——去爬七娘山。后来考虑天太热,便作罢了,正好抽一点时间整理些近来的事情。 ** 关于博客 很早在朋友圈看到 多说 要关闭的消息,不久后也收到了邮件。全国第一的评论系统,说倒就倒,到底是没有好的营收模式,看到网上一堆评论说挺惋惜的,但那么多用户评论数据在上面,价值可想而知,不应遭此地步。在2014年刚用 多说 的时候,就感觉到它后台基本上没有人在维护了,自身管理后台都有不少问题,提反馈也没响应。 ...

2017-04-02 · 4 min

一次艰辛的字符集转换历程 ACMUG分享

本文的ppt是3月25日在中国MySQL用户组2017深圳活动上,我所做的一个主题分享,关于实际生产使用mysql过程中与字符集有关的一些坑。 ...

2017-03-27 · 2 min

index merge 引起的死锁分析

在看线上一个 MySQL innodb status 时,发现有死锁信息,而且出现的频率还不低。于是分析了一下,把过程记录下来。 1. 概要 表结构脱敏处理: 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE t_mytb1 ( f_id int(11) unsigned NOT NULL AUTO_INCREMENT, f_fid int(11) unsigned NOT NULL DEFAULT '0', f_sid int(11) unsigned NOT NULL DEFAULT '0', f_mode varchar(32) NOT NULL DEFAULT '', f_read int(11) unsigned NOT NULL DEFAULT '0', f_xxx1 int(11) unsigned NOT NULL DEFAULT '0', f_xxx2 int(11) unsigned NOT NULL DEFAULT '0', f_wx_zone int(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (f_id), KEY idx_sid (f_sid), KEY idx_fid (f_fid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 死锁信息: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 LATEST DETECTED DEADLOCK ------------------------ 2017-02-28 13:58:29 7f25a3efd700 *** (1) TRANSACTION: TRANSACTION 4907718431, ACTIVE 0.010 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 154 lock struct(s), heap size 30248, 10 row lock(s) LOCK BLOCKING MySQL thread id: 13589250 block 13589247 MySQL thread id 13589247, OS thread handle 0x7f25a17e3700, query id 27061926722 11.xx.52.xx ecweb Searching rows for update UPDATE `d_db1`.`t_mytb1` SET `f_read` = f_read+1 WHERE (f_fid=91243) AND (f_sid=100) AND (f_mode='浏览器') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 13288 page no 375 n bits 352 index `PRIMARY` of table `d_db1`.`t_mytb1` trx id 4907718431 lock_mode X locks rec but not gap waiting Record lock, heap no 245 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 0000a63b; asc ;;; 1: len 6; hex 0001246304a7; asc $c ;; 2: len 7; hex 7f000ac0162428; asc $(;; 3: len 4; hex 00016470; asc dp;; 4: len 4; hex 00000064; asc d;; 5: len 9; hex e6b58fe8a788e599a8; asc ;; 6: len 4; hex 0000244f; asc $O;; 7: len 4; hex 0000007c; asc |;; 8: len 4; hex 00000000; asc ;; 9: len 4; hex 00000000; asc ;; *** (2) TRANSACTION: TRANSACTION 4907718435, ACTIVE 0.007 sec fetching rows mysql tables in use 3, locked 3 154 lock struct(s), heap size 30248, 3 row lock(s) MySQL thread id 13589250, OS thread handle 0x7f25a3efd700, query id 27061926757 11.xx.104.xxx ecweb Searching rows for update UPDATE `d_db1`.`t_mytb1` SET `f_read` = f_read+1 WHERE (f_fid=91248) AND (f_sid=100) AND (f_mode='浏览器') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 13288 page no 375 n bits 352 index `PRIMARY` of table `d_db1`.`t_mytb1` trx id 4907718435 lock_mode X locks rec but not gap Record lock, heap no 245 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 4; hex 0000a63b; asc ;;; -- 42555 1: len 6; hex 0001246304a7; asc $c ;; -- 4905436327 2: len 7; hex 7f000ac0162428; asc $(;; 3: len 4; hex 00016470; asc dp;; -- 91248 4: len 4; hex 00000064; asc d;; -- 100 5: len 9; hex e6b58fe8a788e599a8; asc ;; 6: len 4; hex 0000244f; asc $O;; -- 9295 7: len 4; hex 0000007c; asc |;; -- 124 8: len 4; hex 00000000; asc ;; 9: len 4; hex 00000000; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 13288 page no 202 n bits 1272 index `idx_sid` of table `d_db1`.`t_mytb1` trx id 4907718435 lock_mode X locks rec but not gap waiting Record lock, heap no 705 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 00000064; asc d;; -- 100 1: len 4; hex 0000a633; asc 3;; -- 42547 *** WE ROLL BACK TRANSACTION (2) 乍一看很奇怪,tx1和tx2 两个 UPDATE 各自以 f_fid 为条件更新的记录互不影响才对,即使 91243,91248 两个值有可能出现在同一条数据上(因为f_fid上是二级索引),那顶多也就是个更新锁等待,谁后来谁等待,怎么会出现互相争用对方已持有的锁,被死锁检测机制捕获? 当然,把 update 语句拿到数据库中 EXPLAIN 一下就可以看出端倪。这里不妨先分析一下输出的锁情况: 先看 Tx2 (对应trx id 4907718435) : RECORD LOCKS space id 13288 page no 375 n bits 352 告诉我们是表空间id 13288 (可从 information_schema.INNODB_SYS_DATAFILES 查到对应ibd文件) 即 t_mytb1 表,第 375 号页面的 245 位置的记录被锁,并且是 idx PRIMARY 上的记录锁(注:本实例隔离级别为RC)。 Tx2正持有这把记录锁。 因为是聚集索引,显示了完整记录 1 2 3 4 5 6 0: 主键f_id=42555 1: DB_TRX_ID = 4905436327 2: DB_ROLL_PTR指向undo记录的地址 3: f_fid=91248 4: f_sid=100 ... 然而Tx2还在等待一个记录锁(lock_mode X locks rec but not gap waiting),但这把锁来自二级索引 idx_sid 索引上的记录锁。在 RC 级别下没有GAP lock,行锁除了加在符合条件的二级索引 f_sid=100 上外,还会对主键加record lock。 二级索引值: ...

2017-03-11 · 5 min

MySQL根据离线binlog快速“闪回”

昨天突然有个客户说误操作,自己删除了大量数据,CTO直接将我拉到一个讨论组里,说要帮他们恢复数据。他们自己挖的坑,打算让开发那边根据业务日志去恢复,被告知只记录的删除主键这样的信息,物理删除,无能为力。 上服务器看了下记录的日志,发现好几台上面都有被误删的记录输出。阿里RDS虽然可以克隆一个恢复到删除时间点前的实例,但这散落的几万个id找起来费力,还有就是几个表之间关联的数据也要恢复,觉得麻烦。 想到 MySQL 的闪回方案。以前看过好几篇相关文章,甚至差点自己用python撸一个来解析binlog,反转得到回滚sql,实在没空,这下要急用了。赶紧找了下网上“现成的方案”。 正文开始 MySQL(含阿里RDS)快速闪回可以说是对数据库误操作的后悔药,flashback功能可以将数据库返回到误操作之前。但是即使oracle数据库也只支持短时间内的闪回。 网上现有开源的MySQL闪回实现,原理都是解析binlog,生成反向sql: (必须为row模式) 对于 delete 操作,生成insert (DELETE_ROWS_EVENT) 对于 update 操作,交换binlog里面值的顺序 (UPDATE_ROWS_EVENT) 对于 insert 操作,反向生成delete (WRITE_ROWS_EVENT) 对于多个event,要逆向生成sql 开源实现: https://github.com/58daojia-dba/mysqlbinlog_flashback https://github.com/danfengcao/binlog2sql/ 上面两种实现方式,都是通过 python-mysql-replication 包,模拟出原库的一个从库,然后 show binary logs 来获取binlog,发起同步binlog的请求,再解析EVENT。但是阿里云 RDS 的binlog在同步给从库之后,** 很快就被 purge 掉了 。如果要恢复 ** 昨天 的 ** 部分数据 **,两种方案都是拿不到binlog的。也就是闪回的时间有限。 还有一些比较简单的实现,就是解析 binlog 物理文件,实现回滚,如 binlog-rollback.pl ,试过,但是速度太慢。 为了不影响速度,又想使用比较成熟的闪回方案,我们可以这样做: 借助一个自建的 mysqld 实例,将已purge掉的binlog拷贝到该实例的目录下 在自建实例里,提前创建好需要恢复的表(结构),因为工具需要连接上来从 information_schema.columns 获取元数据信息 拷贝的时候,可以替换掉mysql实例自己的binlog文件名,保持连续 可能要修改 mysql-bin.index,确保文件名还能被mysqld识别到 重启mysql实例,show binary logs 看一下是否在列表里面 接下来就可以使用上面任何一种工具,模拟从库,指定一个binlog文件,开始时间,结束时间,得到回滚SQL 再根据业务逻辑,筛选出需要的sql ...

2017-03-03 · 4 min

关于MySQL自增主键的几点问题(下)

AUTO-INC waiting 锁等待 这是生产环境出现的现象,某日下午5点业务高峰期,我们的 慢查询快照抓取程序 报出大量线程阻塞,但是1分钟以后就好了。于是分析了当时的 processlist 和 innodb status 现场记录,发现有大量的 AUTO-INC waiting: ...

2017-02-17 · 4 min

关于MySQL自增主键的几点问题(上)

前段时间遇到一个InnoDB表自增锁导致的问题,最近刚好有一个同行网友也问到自增锁的疑问,所以抽空系统的总结一下,这两个问题下篇会有阐述。 1. 划分三种插入类型 这里区分一下几种插入数据行的类型,便于后面描述:(纯逻辑上的划分) ...

2017-02-16 · 6 min

监控MySQL你还应该收集表信息

1. Story 也许你经常会被问到,库里某个表最近一年的内每个月的数据量增长情况。当然如果你有按月分表比较好办,挨个 show table status,如果只有一个大表,那估计要在大家都休息的时候,寂寞的夜里去跑sql统计了,因为你只能获取当前的表信息,历史信息追查不到了。 ...

2016-12-04 · 3 min