OurMySQL

美团MySQL数据库巡检系统的设计与应用

OurMySQL — Thu, 27 May 2021 08:04:30 +0000

巡检工作是保障系统平稳有效运行必不可少的一个环节，目的是能及时发现系统中存在的隐患。我们生活中也随处可见各种巡检，比如电力巡检、消防检查等，正是这些巡检工作，我们才能在稳定的环境下进行工作、生活。巡检对于数据库或者其他IT系统来说也同样至关重要，特别是在降低风险、提高服务稳定性方面起到了非常关键作用。

本文介绍了美团MySQL数据库巡检系统的框架和巡检内容，希望能够帮助大家了解什么是数据库巡检，美团的巡检系统架构是如何设计的，以及巡检系统是如何保障MySQL服务稳定运行的。

一、背景

为了保障数据库的稳定运行，以下核心功能组件必不可少：

图1 数据库运维保障核心功能组件

其中，数据库巡检作为运维保障体系最重要的环节之一，能够帮助我们发现数据库存在的隐患，提前治理，做到防患于未然。对于大规模集群而言，灵活健壮的自动化巡检能力，至关重要。

任何系统都会经历一个原始的阶段，最早的巡检是由中控机+定时巡检脚本+前端展示构成的。但是，随着时间的推移，老巡检方案逐渐暴露出了一些问题：

巡检定时任务执行依赖中控机，存在单点问题；
巡检结果分散在不同的库表，无法进行统计；
巡检脚本没有统一开发标准，不能保证执行的成功率；
每个巡检项都需要单独写接口取数据，并修改前端用于巡检结果展示，比较繁琐；
巡检发现的隐患需要DBA主动打开前端查看，再进行处理，影响整体隐患的治理速度；
……

所以我们需要一个灵活、稳定的巡检系统来帮助我们解决这些痛点，保障数据库的稳定。

二、设计原则

巡检系统的设计原则，我们从以下三个方面进行考虑：

稳定：巡检作为保证数据库稳定的工具，它自身的稳定性也必须有所保证；高效：以用户为中心，尽量化繁为简，降低用户的使用成本，让新同学也能迅速上手治理和管理隐患；提高新巡检部署效率，随着架构、版本、基础模块等运维环境不断变化，新的巡检需求层出不穷，更快的部署等于更早的保障；可运营：用数据做基础，对巡检隐患进行运营，包括推进隐患治理，查看治理效率、趋势、薄弱点等。

三、系统架构

美团MySQL数据库巡检系统架构图设计如下。接下来，我们按照架构图从下到上的顺序来对巡检系统主要模块进行简单的介绍：

图2 美团MySQL数据库巡检系统架构图

1. 执行层

巡检执行环境：由多台巡检执行机组成，巡检任务脚本会同时部署在所有执行机上。执行机会定时从巡检Git仓库拉取最新的脚本，脚本使用Python Virtualenv + Git进行管理，方便扩充新的执行机。

任务调度：巡检任务使用了美团基础架构部研发的分布式定时任务系统Crane进行调度，解决传统定时任务单点问题。Crane会随机指派某一台执行机执行任务，假如这台执行机出现故障，会指派其他执行机重新执行任务。一般一个巡检任务对应着一个巡检项，巡检任务会针对特定的巡检目标根据一定的规则来判断是否存在隐患。

巡检目标：除了对生产数据库进行巡检以外，还会对高可用组件、中间件等数据库周边产品进行巡检，尽可能覆盖所有会引发数据库故障的风险点。

2. 存储层

巡检数据库：主要用来保存巡检相关数据。为了规范和简化流程，我们将巡检发现的隐患保存到数据库中，提供了通用的入库函数，能够实现以下功能：

自动补齐隐患负责人、隐患发现时间等信息；
入库操作幂等；
支持半结构化的巡检结果入库，不同巡检的隐患结果包括不同的属性，比如巡检A的隐患有“中间件类型”，巡检B有“主库CPU核数”，以上不同结构的数据均可解析入库；
针对表粒度的隐患项，如果分库分表的表出现隐患，会自动合并成一个逻辑表隐患入库。

巡检脚本Git仓库：用来管理巡检脚本。为了方便DBA添加巡检，在系统建设过程中，我们增加了多个公共函数，用来降低开发新巡检的成本，也方便将老的巡检脚本迁移到新的体系中。

3. 应用层

集成到数据库运维平台：作为隐患明细展示、配置巡检展示、管理白名单等功能的入口。为了提高隐患治理效率。我们做了以下设计。

隐患明细展示页面会标注每个隐患出现的天数，便于追踪隐患出现原因。
配置新的巡检展示时必须要同时制定隐患解决方案，确保隐患治理有章可循，避免错误的治理方式导致“错上加错”。

隐患运营后台：这个模块主要目的是推进隐患的治理。

运营报表，帮助管理者从全局角度掌握隐患治理进展，报表包括隐患趋势、存量分布、增量分布、平均治理周期等核心内容，进而由上到下推动隐患治理；报表数据同样是通过crane定时任务计算获得。
隐患治理催办功能，用来督促DBA处理隐患。催办内容中会带有隐患具体内容、出现时长、处理方案等。催办形式包括大象消息、告警，具体选用哪种形式可根据巡检关键程度做相应配置。

外部数据服务：主要是将巡检隐患数据提供给美团内部其他平台或项目使用，让巡检数据发挥更大的价值。

对接先知平台（美团SRE团队开发的主要面向RD用户的风险发现和运营平台），平台接收各服务方上报的隐患数据，以RD视角从组织架构维度展示各服务的风险点，并跟进RD处理进度。巡检系统会把需要RD参与治理的隐患，比如大表、无唯一键表等，借助先知平台统一推送给RD进行治理。
运维周报，主要面向业务线RD负责人和业务线DBA，以静态报告形式展示业务线数据库运行情况以及存在的问题，巡检隐患是报告内容之一。

四、巡检项目

巡检项目根据负责方分为DBA和RD，DBA主要负责处理数据库基础功能组件以及影响服务稳定性的隐患。RD主要负责库表设计缺陷、数据库使用不规范等引起的业务故障或性能问题的隐患。也存在需要他们同时参与治理的巡检项，比如“磁盘可用空间预测”等。目前巡检项目共64个，类目分布情况如下图所示：

图3 巡检项类目分布

集群：主要检查集群拓扑、核心参数等集群层面的隐患；机器：主要检查服务器硬件层面的隐患； Schema/SQL：检查表结构设计、数据库使用、SQL质量等方面的隐患；高可用/备份/中间件/报警：主要检查相关核心功能组件是否存在隐患。

下面，我们通过列举几个巡检任务来对巡检项做简单的说明：

五、成果

美团MySQL巡检系统已稳定运行近一年时间，基于新巡检体系上线的巡检项49个。通过巡检体系持续运行，在团队的共同努力下，我们共治理了8000+核心隐患，近3个月隐患治理周期平均不超过4天，将隐患总数持续保持在极小的量级，有效地保障了数据库的稳定。

图4 隐患运营-团队内各虚拟小组隐患平均治理周期

下面的隐患趋势图，展示了近一年中隐患的个数，数量突然增长是由于新的巡检项上线。从整体趋势上看，隐患存量有非常明显的下降。

图5 隐患运营-隐患总量趋势情况

除了推动内部隐患治理之外，我们还通过对接先知平台，积极推动RD治理隐患数量超过5000个。

图6 对接先知-推动RD治理隐患

为了提升用户体验，我们在提升准确率方面也做了重点的投入，让每一个巡检在上线前都会经过严格的测试和校验。

对比其他先知接入方，DBA上报隐患在总量、转化率、反馈率几个指标上都处于较高水平，可见我们上报的隐患风险也得到了RD的认可。

图7 对接先知-各接入方上报隐患情况

指标说明：

反馈率 = 截止到当前时刻反馈过的风险事件数量/截止到当前时刻产生的风险事件总量 * 100%；
反馈准确率 = 截止到当前时刻反馈准确的风险事件数量/截止到当前时刻反馈过的风险事件总量 * 100%；
转化率 = 截止到当前时刻用户反馈准确且需要处理的风险事件数量 / 截止到当前时刻产生的风险事件总量 * 100%。

六、未来规划

除了继续完善补充巡检项以外，未来巡检系统还会在以下几个方向继续探索迭代：

提高自动化能力，完善CI和审计；
加强运营能力，进一步细化每个隐患的重要程度，辅助决策治理优先级；
隐患自动修复。

作者简介

王琦，基础架构部DBA组成员，2018年加入美团，负责MySQL数据库运维/数据库巡检系统/监控/自动化运维周报/运维数据集市建设等工作。

猜想失败，您看看下面的文章有用吗？

按照重要程度划分数据库级别

OurMySQL — Wed, 26 May 2021 23:07:37 +0000

按照重要程度划分数据库级别
级别	D	C	B	A	S
影响面	小于10人	10-1000人	1000-100000人	100000-1000000人	1000000人以上
业务类型举例	测试/开发系统，小型OA 记账软件等	中型OA ERP 财务软件等	中大型ERP MES HRM ，大型医院HIS	电信CRM 银行BANKING等	大型公共应用如12306等
灾难救援价格	500-5000	5千到三万	3万到10万	10万到50万	50万以上
一般的配套设施	几乎无任何有效备份	少量磁盘上逻辑备份	可能有物理备份+其他备份形式	物理/逻辑备份+DataGuard,OGG等物理/逻辑灾备;存储级别冗余等+多数据中心冗余	都有

猜想失败，您看看下面的文章有用吗？

修复 MySQL 编码问题

OurMySQL — Mon, 17 May 2021 23:26:15 +0000

有个疑似 OCD 患者最近抽风升级了一下 MySQL 数据库，然后发现 blog 里面全都变成了乱码。

那乱码的模式一看就是把 utf8 直接扔进了 latin1 的数据库，一看 SHOW CREATE TABLE mt_entry 发现果然如此。

略有些慌神，看了 MySQL 文档发现用 ALTER TABLE 的 CONVERT TO 硬来有点不太行好，遂想到可以试试看 mysqldump，于是做了：

当作 latin1(不然会再按 utf8 编码一次)：

mysqldump mt_delphij –default-character-set=latin1 -r utf8.dump

把里面的 CHARSET=latin1 替换为 CHARSET=utf8：

sed -e s,CHARSET=latin1,CHARSET=utf8,g < utf8.dump > utf8.dump.edited

删掉其中的 SET NAMES latin1。

然后重新导入：

mysql -uroot -p --default-character-set=utf8 mt_delphij
mysql> SET names utf8;
mysql> SOURCE utf8.dump.edited;

还好没用到 zfs rollback。

猜想失败，您看看下面的文章有用吗？

如何获取 MySQL innodb 的 B+tree 的高度

OurMySQL — Wed, 05 Feb 2020 15:06:48 +0000

前言

MySQL 的 innodb 引擎之所以使用 B+tree 来存储索引，就是想尽量减少数据查询时磁盘 IO 次数。树的高度直接影响了查询的性能。一般树的高度在 3~4 层较为适宜。数据库分表的目的也是为了控制树的高度。那么如何获取树的高度呢？下面使用一个示例来说明如何获取树的高度。

示例数据准备

建表语句如下：

CREATETABLE`user` (
  `id` int(11) NOTNULLAUTO_INCREMENT,
  `name` varchar(100) CHARACTERSETlatin1 DEFAULTNULL,
  `age` int(11) DEFAULTNULL,
  PRIMARYKEY(`id`),
  KEY`name` (`name`),
  KEY`age` (`age`)
) ENGINE=InnoDB DEFAULTCHARSET=utf8

表中插入100万条数据。数据如下：

mysql> select* fromuserlimit 2\G
*************************** 1. row ***************************
  id: 110000
name: ab
 age: 100
*************************** 2. row ***************************
  id: 110001
name: ab
 age: 100
2 rowsinset(0.00 sec)

通过查询相关数据表获取树的高度

以 MySQL5.6 版本为例说明如何获取树的高度。

首先获取 page_no

mysql> SELECTb.name, a.name, index_id, type, a.space, a.PAGE_NO FROMinformation_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHEREa.table_id = b.table_id ANDa.space<> 0 andb.name='test/user';
+-----------+---------+----------+------+-------+---------+
| name      | name    | index_id | type | space| PAGE_NO |
+-----------+---------+----------+------+-------+---------+
| test/user| PRIMARY|       22 |    3 |     6 |       3 |
| test/user| name    |       23 |    0 |     6 |       4 |
| test/user| age     |       24 |    0 |     6 |       5 |
+-----------+---------+----------+------+-------+---------+
3 rowsinset(0.00 sec)

page_no 是索引树中Root页的序列号。其它各项的含义可以参照：
https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html

再读取页的大小

mysql> show global variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row inset(0.00 sec)

最后读取索引树的高度

$ hexdump -s 49216 -n 10 ./user.ibd
000c040 0200 0000 0000 0000 1600
000c04a

可以发现 PAGE_LEVEL 为 0200，表示这棵二级索引树的高度为 3。后面的 1600 是索引的 index_id 值。十六进制的 16 转换为十进制数字是 22。这个 22 正好就是上面主键的 index_id。
上面 hexdump 命令中 49216 是怎么算出来的？公式是 page_no * innodb_page_size + 64。
3*16384+64=49216

我们在用这个方式查看下其他两个索引的高度。

$ hexdump -s 65600 -n 10 ./user.ibd
0010040 0100 0000 0000 0000 1700
001004a
$ hexdump -s 81984 -n 10 ./user.ibd
0014040 0200 0000 0000 0000 1800
001404a

可见，name 索引的高度是 2，age 索引的高度是 3。

根据索引的结构估算

如果你没有数据库服务器的权限。自己也可以根据数据库索引结构进行估算树的高度。
根据 B+Tree 结构，非叶子节点存储的是索引数据，叶子节点存储的是每行的所有数据。
非叶子节点每个索引项的大小是，数据大小+指针大小。假设指针大小为 8 个字节。每页不会被占满，预留1/5的空隙。下面我们估算下 name 和 age 两个索引的高度。

name 索引高度估算

非叶子节点每页存放的索引项数量。每页大小是 16k。name 的值为 ab。占2个字节。每项数据大小是 2+8=10字节。每页能存放的索引项数量是 16384 * 0.8 / 10 = 1310 个。
叶子节点每页存放的索引数量。每页大小是 16k。每项数据大小是 4+2+8=14 个字节。没页能存放的索引数量是 16384 * 0.8 / 14 = 936 个。
两层能存放 1310*936=1226160 个数据记录。可见120万条记录以下，树的高度为2。

age 索引高度估算

非叶子节点每页存放的索引项数量。每页大小是 16k。age 的类型为 int。占4个字节。每项数据大小是 4+8=12字节。每页能存放的索引项数量是 16384 * 0.8 / 12 = 1092 个。
叶子节点每页存放的索引数量。每页大小是 16k。每项数据大小是 4+4+8=16 个字节。没页能存放的索引数量是 16384 * 0.8 / 16 = 819 个。
两层能存放 1092*819=894348 个数据记录。可见90万条记录以下，树的高度为2。100万条为 3 层。

其它工具

还有一个小工具可以查看。InnoDB 表空间可视化工具innodb_ruby
https://www.cnblogs.com/cnzeno/p/6322842.html

猜您喜欢

纳尼，mysqldump导出的数据居然少了40万？

OurMySQL — Sat, 11 Mar 2017 23:45:37 +0000

0、导读

用mysqldump备份数据时，加上 -w 条件选项过滤部分数据，发现导出结果比实际少了40万，什么情况？

本文约1500字，阅读时间约5分钟。

1、问题

我的朋友小文前几天遇到一个怪事，他用mysqldump备份数据时，加上了 -w 选项过滤部分数据，发现导出的数据比实际上少了40万。

要进行备份表DDL见下：

CREATE TABLE `oldbiao` (

`aaaid` int(11) NOT NULL,

`bbbid` int(11) NOT NULL,

`cccid` int(11) NOT NULL,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`dddid` int(11) DEFAULT NULL,

KEY `index01` (`ccccid`),

KEY `index02` (`dddid`,`time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

顺便吐槽下，这个表DDL设计的真是low啊。没主键，允许NULL。

mysqldump备份指令增加的 -w/-where 选项是：

-w “time>‘2016-08-01 00:00:00′”

加上这个参数的作用是：只备份 2016-8-1 之后的所有数据，相当于执行了下面这样的SQL命令：

SELECT SQL_NO_CACHE * FROM t WHERE time>‘2016-08-01 00:00:00′

然后把导出的SQL文件恢复后，再随机抽查下数据看看是否有遗漏的。不查不知道，一查吓一跳，发现 2016-12-12 下午的数据是缺失的。经过仔细核查，发现比原数据大概少了40万条记录。

百思不得其解的小文请我帮忙排查问题。

2、排查

既然是少了一部分数据，那就要先定位到底是丢失了的是哪部分数据。

那么，如何定位呢？

搞数据库的人，应该都知道折半查找法，这是计算机科学里比较基础的概念之一。我们就利用这种方法来快速定位。

经过排查，发现是缺少的数据有个特点，根据时间排序，发现最早的数据是 2016-8-1 8点的，而我们上面设定的条件则是 2016-8-1 0点开始的所有数据，整整差了8个小时。

看到8小时这个特点，我想你应该大概想到什么原因了吧。对，没错，就是因为时区的因素导致的。

经过排查，发现是因为原先写数据时，是以 0时区 时间写入的，但执行mysqldump备份时则使用的是本地 东8区 的时间，所以就有了8小时的差距。

2、解决

知道了问题所在，方法就简单了。有两个方法：

1、修改mysqldump中的where条件时间值，减去8个小时。建议采用该方法。

mysqldump … -w “time>‘2016-07-31 16:00:00′”

2、修改MySQL全局时区，从 0时区改成东8区，并且mysqldump加上 -skip-tz-utc 选项。这种方法需要修改MySQL的全局时区，可能会导致更多的业务问题，因此强烈不建议使用。

mysqldump … -skip-tz-utc -w “time>‘2016-08-01 08:00:00′”

问题暂且按下，我们先来看下时区因素怎么影响查询结果的。

先看下系统本地时间：

[yejr@imsyql]$ date -R

Wed, 21 Dec 2016 14:04:51 +0800

测试表DDL：

CREATE TABLE `t1` (

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

…

`c1` timestamp NULL DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

查看MySQL的时区设置：

图1

然后写入一条数据：

图2

第一次备份，用本地时间条件去过滤：

mysqldump -w “dt >= ‘2016-12-21 14:00:00′”

这种情况下，显然是没有结果的。

图3

第二次备份，用本地时间减去8小时再去过滤：

mysqldump -w “dt >= ‘2016-12-21 06:00:00′”

这种就可以备份出数据了。

图4

此外，我们注意到mysqldump的 -tz-utc 选项，它是和时区设定有关系的：

-tz-utc

SET TIME_ZONE=’+00:00′ at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones.

(Defaults to on; use -skip-tz-utc to disable.)

这个选项的作用，就是以 0时区备份数据，把所有时间都转换成 0时区的数据。比如本来是在东8区(+08:00) 的时间 14:00:00，转换成 0时区后，会变成 06:00:00，原来是西8区(-08:00) 的时间14:00:00，则转换成 22:00:00。这个选项是默认启用的。

在上面第一次备份时没有数据，就是因为MySQL里本身存储的就是 0时区的数据，mysqldump也设定了转换成 0时区，我们传递进去的参数却是东8区的时间，因此没有数据。

3、总结

本来只想简单写一下的，结果里嗦写了好多。

其实我们只需要注意一点，服务器在哪里，就是用哪里的时区，也就是 SYSTEM 时区，在做SQL查询以及mysqldump备份数据时，也使用服务器上的时间，而不使用我们本地时间。

图5

猜您喜欢

今天你检查备份了吗？

OurMySQL — Sat, 11 Mar 2017 23:44:28 +0000

0、导读

《炉石传说》游戏数据库回档事件反思

今天引爆各大技术群的事情就是网易游戏《炉石传说》游戏数据库发生宕机并引发数据丢失事故，最终决定回档并后续补偿玩家损失。详情可见官网公告：http://hs.blizzard.cn/articles/16/8565

我以前也在搜狐畅游（http://www.changyou.com，NASDAQ:CYOU）负责游戏数据库维护，也遇到过因为服务器故障最终导致回档的事故，不过都没像这次炉石搞这么大动作。在这里我并不想借机调侃消费他们或搞营销，只想和大家一起聊聊作为DBA，应该注意哪些事。

我们从公告的内容中，我们看到了几个问题：

公告发布时间是2017.1.18 18点，决定回档到2017.1.14 15:20，中间这段时间难道一直都在尝试恢复数据库，就不能快速做出决策尽快直接回档吗，这是在考验游戏玩家的耐心，很容易引发玩家的“群体事件”；
因为供电意外导致故障，并造成数据库损坏，如果也用MySQL数据库的话，看起来应该是没开启双1设置，并且有可能还在使用老式的锂电池BBU。所以断电后很容易导致阵列卡cache中的数据丢失，数据库也跟着损坏，以前没少才踩这个坑；
连备份数据库也发生故障，有点不可思议，这样就容易让人产生是人为事故的联想了。不过，我多年前也发生过类似的情况，不过那次是因为用mysqldump备份时指定了错误的字符集，并且在做备份恢复测试时没严格测试数据的有效性，致使发生故障时不能正常恢复，结果也悲剧了。作为不了解内情的局外人，只能以官方公告为准，无要无端臆测；

关于服务器可靠性以及数据库备份，有几点建议：

必须定期全备，并且优先推荐物理备份，逻辑备份通常相对更慢。一般至少每天一次全备；
每小时一次增备或差异备份，我以前的做法是开binlog，并且利用last_update_time列特征每小时做一次差异备份。这样我要恢复的话，一般最多只损失不到一个小时的数据；
备份文件务必进行恢复测试，如果有多个备份集，可以采用随机抽取的方式做恢复测试，但一定要保证所有实例的备份最终都会被验证一次；
必须监控服务器硬件健康状况，包括CPU、内存、阵列卡、阵列卡电池等部件，以及服务器温度等。我们曾经有在哈尔滨及西安某机房的服务器，一到夏天就很容易因为温度过高而引发自动重启

猜您喜欢
- mysqldump加-w参数备份
- linux 定期自动备份mysql的shell

MySQL工具推荐 | 基于MySQL binlog的flashback工具

OurMySQL — Sat, 11 Mar 2017 23:43:07 +0000

1、前言

相信您应该遇到过因为误操作破坏数据库的问题，比如忘了带WHERE条件的UPDATE、DELETE操作，然后就需要进行传统方式的全量 & 增量恢复。现在，给您介绍一下MySQL中的flashback玩法，也可以做到像Oracle的flashback那样。

目前MySQL的 flashback(又称闪回)一般是利用binlog完成的，能快速完成恢复且无需停机维护。

第一个实现该功能的是阿里云的 彭立勋，他在MySQL 5.5版本上就已实现，并将其开源及提交给MariaDB，为社区提供了非常优秀的参考模型。

2、闪回原理

本节我们先来介绍一下MySQL binlog flashback的基本工作原理。

MySQL的binlog以event的形式，记录了MySQL中所有的变更情况，利用binlog我们就能够重现所记录的所有操作。

MySQL引入binlog主要有两个用途/目的：一是为了主从复制；二是用于备份恢复后需要重新应用部分binlog，从而达到全备+增备的效果。

MySQL的binlog共有三种可选格式(binlog_format)，其各有优缺点：

statement，基于SQL语句的模式，一般来说生成的binlog尺寸较小，但是某些不确定性SQL语句或函数在复制过程可能导致数据不一致甚至出错；
row，基于数据行的模式，记录的是数据行的完整变化。相对更安全，推荐使用(但通常生成的binlog会比其他两种模式大很多)；
mixed，混合模式，可以根据情况自动选用statement抑或row模式；这个模式下也可能造成主从数据不一直。它属于MySQL 5.1版本时期的过渡方案。因此，如果你现在还使用mixed的话，那你的过渡时间也太久了……

备注：想要使用binlog flashback工具，需要将binlog_format设置为row才行。

3、工具推荐

项目一：mysqlbinlog_flashback

项目作者：赖亿@58到家

github项目地址：https://github.com/58daojia-dba/mysqlbinlog_flashback

也可在github.com上搜索“mysqlbinlog_flashback”

项目介绍：产生在线mysqlbinlog的回滚的sql，现在已经在阿里的rds上，db为utf8字符集的生产环境下使用。其他环境没有在生产环境下使用，请小心。

项目使用反馈：laiyi@daojia.com

项目二：binlog2sql

项目作者：曹单锋

github项目地址：https://github.com/danfengcao/binlog2sql

也可在github.com上搜索“binlog2sql”

项目介绍：从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。

项目反馈：danfengcao.info@gmail.com

应用场景

flashback，数据快速回滚；
主从切换后数据不一致的修复；
从binlog生成标准SQL，再自行二次开发；

5、使用方法

两个软件的使用上都比简单，都是在 https://github.com/noplay/python-mysql-replication 项目基础上进行的二次开发。

两个项目中都有详细的使用说明，感谢两位作者细心的整理，我们这里不再进行赘述，请自行到作者的项目上查看，如果对你帮助，请记得给个 star 哟！

6、flashback总结

社区里做这块的工具比较多，不开源的这里不在讨论，开源产品随着时间的前进，作者有可能忙于其它事情，没来的及更新也会失效过期。所以使用中需要有一定自我修订能力，也希望各位使用者也能加入到开源的大家庭中，共同维护这些项目。

这两款工具开发时侧重点不同，所以使用中也需要注意一下：

mysqlbinlog_flashback 更便重于阿里云 RDS环境的使用。
binlog2sql 便重于通常MySQL的处理。从代码上来看，该项目更简洁一点。

在具体使用中及项目定制中那个更合适，只有使用后，找到适合自已就可以。这也是开源的魅力。如果对这两个软件有兴趣深度交流的，也可以加到QQ群： 529671799 (两位作者已在)来交流吧。

猜想失败，您看看下面的文章有用吗？

关于MySQL线程池资料整理–Percona Server

OurMySQL — Mon, 20 Feb 2017 00:18:18 +0000

MySQL执行statement使用一个连接一个thread的方式,当连接数增加到某一个点,那么性能将会开始下降.
这个特性的作用就是无论有多少连接,thread pool都讲保证性能处于最好水平.
thread pool的原理主要是减少线程,减少上下文交换和锁资源争抢,针对OLTP环境特别有效.

为可以开启thread pool功能,请将thread_handling=pool-of-threads.

percona server实现的thread pool是在server级别,和oracle mysql的不一样(插件形式),另外一个不同点在于,percona的thread pool不尝试最小化并发事物数量.

优先连接调度:
在percona server 5.5.30,thread pool的优先连接调度已经实现,虽然线程池会限制并发执行的queries的数量,但是open的transactions依然可能很多.因为新开启的transaction
会跟在queue后面,高并发的transaction意味着很多当前运行的查询.为了增强性能,引入了thread_pool_high_prio_tickets参数.

这个参数控制进入高优先级队列的策略,对每个新连接分配可以进入高优先级队列的ticket,如果当一个query因为没有可用的线程导致执行延时,如果这个连接满足下面的条件可以
直接进入高优先级的队列.
1、这个连接有已经在执行的transaction
2、这个连接持有的ticket是非0的.
如果满足这两个条件,这个连接将被放到高优先级队列,并且ticket会被缩减.
也就是说,如果不满足上面的条件会被放入普通的queue.ticket的初始值就是thread_pool_high_prio_tickets参数指定的.
每次线程池寻找一个新的连接进行处理,首先检查高优先级队列,如果高优先级队列为空,将会把连接从普通队列放到高优先级队列.

这样做的目标就是减少服务器上正在进行的事务量,好处在于减少事务的运行时间,减少系统资源的等待.

基于某些自动提交或者需要使用低优先级队列的情况,我们提供thread_pool_high_prio_mode参数.

variable thread_pool_idle_timeout
Command Line:Yes
Config File:Yes
Scope:Global
Dynamic:Yes
Variable Type:Numeric
Default Value:60 (seconds)
这个值用来设定线程空闲多久会将退出.

variable thread_pool_high_prio_mode
Command Line:Yes
Config File:Yes
Scope:Global, Session
Dynamic:Yes
Variable Type:String
Default Value:transactions
Allowed Values:transactions, statements, none
控制高优先级队列的模式.
transaction：只有已经开启事务的连接可以进入高优先级队列
statements:所有的statement都可以单独进入高优先级队列,不判断是否开启事务或者ticket的值.
none:禁止进入高优先级队列,主要针对监控之类的线程做设置.如果全局设置none的话,等同于statements.

variable thread_pool_high_prio_tickets
Command Line:Yes
Config File:Yes
Scope:Global, Session
Dynamic:Yes
Variable Type:Numeric
Default Value:4294967295
对新建立的连接分配ticket值,如果当ticket值为0,那么将被禁止进入高优先级队列.

variable thread_pool_max_threads
Command Line:Yes
Config File:Yes
Scope:Global
Dynamic:Yes
Variable Type:Numeric
Default Value:100000
限制pool里面最大的线程数量,如果达到这个限制,将不会创建新的线程.

variable thread_pool_oversubscribe
Command Line:Yes
Config File:Yes
Scope:Global
Dynamic:Yes
Variable Type:Numeric
Default Value:3
该参数的值越高,可以同时运行多个线程,如果低于3的值可能会导致更多的睡觉和唤醒。

variable thread_pool_size
Command Line:Yes
Config File:Yes
Scope:Global
Dynamic:Yes
Variable Type:Numeric
Default Value:Number of processors
设置线程池可以同时使用CPU的线程数.

thread_pool_stall_limit
Command Line:Yes
Config File:Yes
Scope:Global
Dynamic:No
Variable Type:Numeric
Default Value:500 (ms)
执行单个sql的超时时间,如果sql执行时间超过这个值,那么线程池将唤醒或者创建另外的线程开始工作.主要为long-running query设计.

状态值:
variable Threadpool_idle_threads
Command Line:Yes
Variable Type:Numeric
查看线程池中空闲的线程

variable Threadpool_threads
Command Line:Yes
Variable Type:Numeric
查看线程池中有多少线程

调优：
一般来说默认情况已经很好了.所以只需要对下面两个参数就可以了.
thread_handling = pool-of-threads #开启线程池
thread_pool_oversubscribe = 10 #这个值建议在3~20间

http://www.percona.com/doc/percona-server/5.5/performance/threadpool.html

http://www.percona.com/blog/2014/01/29/percona-server-thread-pool-improvements/

http://imysql.com/2014/07/02/percona-thread-pool-benchmark-testing.shtml

猜您喜欢

SQL里是否可以使用JOIN

OurMySQL — Thu, 22 Dec 2016 23:29:54 +0000

很多公司都禁止程序员在 SQL 中使用 JOIN，至于原因则出奇的一致：用 JOIN 慢。不过我从没见过谁来论证为什么用 JOIN 慢，结果这个人云亦云的结论越传越广，让我觉得是时候来讨论一下这个看似正确的结论了。

举个例子：查询最新的十篇帖子和对应的用户信息，用 JOIN 是这样的：

SELECT posts.id, posts.content, users.name, ...
FROM posts
JOIN users on posts.user_id = users.id
ORDER BY posts.created_at DESC
LIMIT 10

如果不使用 JOIN 的话，那么大概会改写成如下两条 SQL：

SELECT id, content, ...
FROM posts
ORDER BY created_at DESC
LIMIT 10

SELECT name, ...
FROM users
WHERE id in (...)

第一次查询得到帖子数据，然后在程序代码里收集好想要的 user_id，第二次查询通过 user_id 得到用户数据，接着在程序代码里把两份数据组合起来。

哪个快？我就不用跑个 bench 了吧，正常人都能看出来是用 JOIN 的快！

JOIN

在我看来，JOIN 的问题不是性能，而是当你执行 posts JOIN users 的时候，实际上相当于做出了一个假设：posts 和 users 两个结婚的表将永远住在同一个 DB 实例上，以后无论贫穷还是富有，疾病还是健康，永不分离。不过实际上，随着项目的发展，很可能会出现 posts 和 users 两个表不得不离婚的情况，结果它们会被划分到不同 DB 实例，一旦出现此类情况，那么当初使用 JOIN 的地方将不得不大量改写。

至于 SQL 里是否可以使用 JOIN，如果相关的表以后有独立部署的可能性，那么就要考虑避免使用 JOIN，否则用 JOIN 也无妨。当然，有人会找出一些使用 JOIN 后效率奇差的例子，不过这样的问题一来可能是索引不佳，二来可能是特殊情况，用不用 JOIN 都会有类似的问题，只要使用的时候留意即可。下次如果大家再听到别人以性能为由反对 JOIN 的使用，那么不妨把本文的链接发给他，因为他多半没有搞清楚真正的原因是什么。

猜您喜欢

MYSQL基础笔记

OurMySQL — Sat, 11 Jun 2016 15:26:58 +0000

update: 2013-08-11, 笔记录了一半不到，还没码完….>< update: 2013-11-23, DONEmysql速查

参考： Mysql必知必会,网络blog, stackoverflow

注；基础部分sql参考《mysql必知必会》, 还不错的一本书，菜鸟入门级，需要的话可以入手

在浏览器中使用查找

寻找一个好的mysql开源gui工具

环境配置

ubuntu安装mysql

sudo apt-get install mysql-server mysql-client
netstat -nltp | grep mysql
配置文件 /etc/mysql/my.conf

基本概念

数据库基础：

InnoDB是一个可靠地事务处理引擎，不支持全文本搜索
MyISAM是一个性能极高的引擎，支持全文本搜索，不支持事务处理

数据库-database

保存有组织的数据的容器（通常是一个文件或一组文件）

表-table

某种特定类型数据的结构化清单

模式-schema

关于数据库和表的布局及特性的信息

列-column

表中的一个字段，所有表都是由一个或多个列组成的

数据类型-datatype

所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据

行-row

表中的一个记录

主键-primary key

一列或一组列，其值能够唯一区分表中的每个行

mysql命令行

进入

输入： mysql
或者   mysql -u ken
       mysql -u ken -p -h myserver -P 9999 【给出用户名，主机名，端口】

获取帮助: mysql --help

命令格式和说明：

1.命令必须；或\g结束，仅Enter不执行明林
2.help 或\h获得帮助
3.quit或exit退出

可以用GUI工具

MySQL Administrator
MySQL Query Browser

use

创建库:

>CREATE DATABASE MYSQLDATA

使用某个库

use db_name

show

查看所有数据库

show databases;

列出库中所有表

use db_name;
show tables;

列出表的所有列信息

show columns from table_name;
or
desc table_name;

显示创建的sql语句

show create database db_name;
show create table table_name;

其他

show status  服务器状态信息
show grants  显示授权用户
show errors/show warnings 显示服务器错误或警告信息

查询

SELECT子句顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

select

检索单个列

>SELECT col FROM tb_name;

多个列

>SELECT col1, col2
 FROM tb_name

检索所有列

>SELECT *
 FROM tb_name;
#除非确认要用到所有列

检索去重

>SELECT DISTINCT col
 FROM tb_name

限制结果数

>SELECT col1
 FROM tb_name
 LIMIT 5;
返回不多于五行

>SELECT col1
 FROM tb_name
 LIMIT 5, 5
 第一个为开始位置，初始为0.第二个为显示个数
等价于LIMIT 5 OFFSET 5

order by

按某个字段排序

>SELECT col1
 FROM tb_name
 ORDER BY col1

按多列排序

>SELECT col1, col2, col3
 FROM tb_name
 ORDER BY col1, col2

指定排序方向（升序降序）

>SELECT col1, col2
 FROM tb_name
 ORDER BY col1 DESC;【默认ASC】
注意：如果想在多个列上排序，必须对每个列使用DESC
注意：ORDER BY必须放在LIMIT之前

where

过滤

>SELECT col1, col2
 FROM tb_name
 WHERE col1 = 2.5;

过滤不匹配

>SELECT col1, col2
 FROM tb_name
 WHERE col1 <> 1000

范围检查

>SELECT col1, col2
 FROM tb_name
 WHERE col1 BETWEEN 5 AND 10

空值检查

>SELECT col1
 FROM tb_name
 WHERE col2 IS NULL
NULL, 无值，它与字段包含0，空字符串或仅仅包含空格不同

多条件，组合and

>SELECT col1
 FROM tb_name
 WHERE col1=100 AND col2 <= 10

多条件, 组合or

>SELECT col1
 FROM tb_name
 WHERE col1=100 OR col2 <= 10

优先级 and 大于 or, 先处理的and,所以应该适当使用括号

select prod_id from products where (prod_price < 2.5 or vend_id = 1000) and prod_price > 1;

指定查询范围, in操作符

>SELECT col1
 FROM tb_name
 WHERE col1 IN (1001,1002)

取反，not操作符

>SELECT col1
 FROM tb_name
 WHERE col1 NOT IN (1001,1002)

操作符

=
<>
!=
<
<=
>
>=
between A and B

like

通配

>SELECT col1
 FROM tb_name
 WHERE col1 LIKE ‘jet%’

%匹配0个或多个字符

单个字符

>SELECT col1
 FROM tb_name
 WHERE col1 LIKE ‘_ ton anvil’

数据过滤regexp

正则搜索

>SELECT col1
FROM tb_name
WHERE col1 REGEXP ‘1000’

REGEXP ‘.000’
REGEXP对列值匹配

进行or匹配

>SELECT col1
 FROM tb_name
 WHERE col1 REGEXP ‘1000|2000’

几个之一

select prod_id from products where prod_name regexp '[1|2]000';

匹配范围

select prod_id from products where prod_name regexp '[1-5]000';

匹配特殊字符，\ 进行转义

必须使用\\为前导。 \\-
>SELECT col1
 FROM tb_name
 WHERE col1 REGEXP ‘\\.’

like和 regexp

like整列匹配
regexp 列值内匹配

concat

拼接字符

>SELECT Concat(name, ‘ ----‘, age)
 FROM tb_name

去除空白

>SELECT Rtrim(name)
 FROM tb_name

Ltrim() Trim()

使用列名

>SELECT Concat(name, ‘---‘, age) AS info
 FROM tb_name

算术计算

>SELECT quantity * item_price AS total_price
 FROM tb_name

支持+ - * /

文本函数

文本处理函数

left()  串左边字符
length() 串长度
locate() 找出串的一个子串
lower() 转为小写
ltrim() 去掉左边空格
right() 返回串右边字符
rtrim() 去掉串右边空格
soundex() 返回字符串soundex值
upper() 大写

>SELECT Upper(name)
FROM tb_name

日期函数

日期和时间处理函数

adddate() 增加一个日期-天或周
addtime() 增加一个时间
curdate() 返回当前日期
curtime() 返回当前时间
date() 返回日期时间的日期部分
datediff() 计算两个日期差
date_add() 高度灵活的日期运算函数
date_format() 返回一个格式化的日期或时间串
day() 返回一个日期的天数部分
dayofweek() 对于一个日期，返回对应的星期几
hour()
minute()
month()
now() 当前日期和时间
second()
time() 当前日期时间的时间部分
year()

>SELECT col1
 FROM tb_name
 WHERE Date(order_date) = ‘2005-09-01’

常用日期和时间函数

Date()返回日期时间的日期部分
Day()返回日期的天数部分

数值函数

数值处理函数

abs()
cos()
exp() 指数
mod()
pi() 返回圆周率
rand() 随机数
sin()
sqrt()
tan()

聚集函数

avg 平均

>SELECT AVG(price) AS avg_price
 FROM tb_name

count 计数

select count(*) from products; #无论Null还是非空，均纳入计数
select count(prod_id) from products; #计数有值记录，忽略NULL值

max 最大

>SELECT MAX(price) AS max_price
 FROM tb_name

min 最小

>SELECT MIN(price) AS min_price
 FROM tb_name

sum 求和

>SELECT SUM(quantity) AS total
 FROM tb_name
#sum函数忽略值为NULL的行

group

>SELECT id, COUNT(*) AS num_prods
 FROM tb_name
 GROUP BY id

注意：

1.group by 可以包含任意数目的列
2.group by 中每个列都必须是检索列或有效的表达式（但不能使聚集函数）
3.除聚集函数外，select语句中的每个列都必须在group by子句中出现
4.如果分组列有Null值，Null将作为一个分组返回
5.group by 子句必须出现在where子句之后, order by 之前

过滤分组

>SELECT cust_id, COUNT(*) AS orders
 FROM orders
 GROUP BY cust_id
 HAVING COUNT(*) > 2

where和having区别

where在分组前过滤，having在分组后过滤

子查询

1.用于过滤

>SELECT cust_id
 FROM orders
 WHERE order_num IN (SELECT order_num
                    FROM orderitems)

2.作为字段

>SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
 FROM customers
 ORDER BY cust_name

联结表

1.创建联结

>SELECT vend_name, prod_name, prod_price
 FROM vendors, products
 WHERE vendors.vend_id = products.vend_id
 ORDER BY vend_name, prod_name;

可进行联结多个表

2.内部联结

>SELECT vend_name,prod_name,prod_price
 FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id

高级联结表

1.自联结

>SELECT prod_id, prod_name
 FROM products
 WHERE vend_id = (SELECT vend_id FROM products
                WHERE prod_id = ‘DTNTR’)

等价于

>SELECT p1.prod_id, p1.prod_name
 FROM products AS p1, products AS p2
 WHERE p1.vend_id = p2.vend_id
        AND p2.prod_id = ‘DTNTR’

2.外部联结

>SELECT customers.cust_id, orders.order_num
 FROM customers LEFT OUTER JOIN orders
      ON customers.cust_id = orders.cust_id

组合查询

1.UNION

>SELECT vend_id, prod_id, prod_price
 FROM products
 WHERE prod_price <=5
 UNION
 SELECT vend_id, prod_id, prod_price
 FROM products
 WHERE vend_id IN (1001,1002)

UNION自动去除重复行
UNION ALL 保留

2.放在UNION后的排序语句

对所有SELECT生效

全文本搜索

MyISAM 支持全文本搜索

InnoDB不支持全文本搜索

1.启用

>CREATE TABLE productnotes(
 note_id int NOT NULL AUT_INCREMENT,
 note_text text NULL,
 FULLTEXT(note_text)

2.进行全文本搜索

>SELECT note_text
 FROM tb_name
 WHERE Match(note_text) Against(‘rabbit’)

3.布尔文本搜索

>SELECT note_text
 FROM productontes
 WHERE Match(note_text) Against(‘heavy’ IN BOOLEAN MODE)

插入数据

1.基本插入

>INSERT INTO customers(cust_name,
                     cust_address)
 VALUES(‘Pep’, ‘100 main street’)

2.插入多行

>INSERT INTO customers(cust_name,
                     cust_address)
 VALUES(‘Pep’, ‘100 main street’),
       (‘Tim’, ‘200 main Street’);

3.插入检索出来的数据

>INSERT INTO customers(cust_name,
                     cust_address)
 SELECT cust_name, custaddress
 FROM custnew;

更新

1.更新行

>UPDATE customers
 SET cust_email = ‘a@fudd.com’
 WHERE cust_id = 10005

2.即使发生错误也继续进行而不是退出

>UPDATE IGNORE customers

删除

1.删除数据

>DELETE FROM customers
 WHERE cust_id = 10006

表操作

1.创建表

>CREATE TABLE customers(
   cust_id int NOT NULL AUTO_INCREMENT,
   cust_name char(50) NOT NULL,
   vend_city char(50) NULL,
   quantity int NOT NULL DEFAULT 1,
   PRIMARY KEY(cust_id)
)ENGINE=InnoDB

2.更新表

加字段

>ALTER TABLE vendors
 ADD vend_phone CHAR(20)

删除某个字段

>ALTER TABLE tb1 DROP COLUMN names;

改变列类型

>ALTER TABLE infos CHANGE list list tinyint NOT NULL DEFAULT '0'

加主键

>ALTER TABLE tb1 ADD primary key(id)

删除一个字段

>ALTER TABLE tb1 DROP field_name

增加自增长主键

alter table customers change id id not null auto_increment primary key;

增加新字段并设置为主键

Alter TABLE tablename ADD new_field_id int(5) default 0 not null auto_increment ADD primary key(new_field_id)

ALTER TABLE example ADD ID INT NOT NULL;
ALTER TABLE example ADD UNIQUE(url)

>ALTER TABLE vendors
 DROP COLUMN vend_phone

alter table syntax: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

3.删除表

>DROP TABLE customers2;

4.清空表数据

>DELETE FROM mytable;

5.重命名表

>RENAME TABLE customers2 TO customers;
 ALTER TABLE 'oldname' RENAME TO 'newname'

视图操作

1.创建视图

>CREATE VIEW productcustomers AS
 SELECT cust_name, cust_contact
 FROM customers, orders, orderitems
 WHERE customers.cust_id = orders.cust_id

2.使用视图

>SELECT cust_name, cust_contact
 FROM productcustomers
 WHERE prod_id = ‘TNT2’

存储过程

1.创建简单存储过程

>CREATE PROCEDURE productpricing()
 BEGIN
SELECT Avg(price) AS priceavg
FROM products;
 END;

调用:CALL productpricing()

2.删除存储过程

>DROP PROCEDURE productpricing

3.使用参数

>CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quality)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;

调用:

>CALL ordertotal(200005, @total;
>SELECT @total;

3.检查存储过程

>SHOW CREATE PROCEDURE ordertotal;

游标

1.创建游标

>CREATE PROCEDURE processorders()
 BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_number FROM orders;

BEGIN ordernumbers;

FETCH ordernumbers INTO o;

CLOSE ordernumbers;
 END;

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;
REPEAT
    FETCH ordernumbers INTO o;
END;

触发器

1.创建触发器

>CREATE TRIGGER newproduct AFTER INSERT ON products
 FOR EACH ROW SELECT ‘Product added’

2.删除触发器

>DROP TRIGGER newproduct;

3.INSERT触发器

>CREATE TRIGGER neworder AFTER INSERT ON orders
 FOR EACH ROW SELECT NEW.order_num

4.DELETE触发器

>CREATE TRIGGER deleteorder BEFORE DELETE ON orders
 FOR EACH ROW
 BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
 END;

5.UPDATE触发器

>CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
 FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

事务

基本概念

ACID
A,原子性，食物是一个原子操作单元，其对数据的修改，要么全执行，要么全不执行
C.一致性，事务开始和完成的时候，数据必须都保持一致状态（所有相关数据规则和内部数据结构）
I.隔离性，保证事务不受外部并发操作影响，即事务处理中间过程状态对外不可见
D.持久性，事务完成后，对数据修改时永久性的，及时出现系统故障也能够保持

1.事务

>START TRANSACTION
 DELETE FROM ordertotals;
 SELECT * FROM ordertotals;

>ROLLBACK
回退

>COMMIT
提交

2.设立保留点

>SAVEPOINT delete1;

>ROLLBACK TO delete1;

导入导出

1.导入

用文本形式插入数据

>LOAD DATA LOCAL INFILE 'd:/mysql.txt' INTO TABLE mytable;

导入.sql

>use database;
>source d:/mysql.sql

从另外一张表往这张表插入

INSERT INTO tab1(f1,f2)
SELECT a.f1, a.f2
FROM a WHERE a.f1='a'

2.备份

导出要用到MySQL的mysqldump工具，基本用法是：

mysqldump [OPTIONS] database [tables]

备份MySQL数据库的命令

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

还原

还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql
mysql -hhostname -ppassword databasename tablename < backuptablefile.sql

还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

将查询结果导入外部文件

SELECT a,b,a+b 
FROM test_table
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

或者

mysql -u you -p -e "SELECT ..." >  file_name

性能研究

1.什么情况下无法使用索引？

实时监控

查看mysql数据库的当前连接数

命令： show processlist;
或者 # mysqladmin -uroot -p密码 processlist

当前状态

命令： show status;
或者 # mysqladmin -uroot -p密码 status

猜想失败，您看看下面的文章有用吗？

数据导入主键冲突问题

OurMySQL — Mon, 06 Jun 2016 23:41:13 +0000

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、问题解决

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

删除主键定义，或者删除主键的自增属性；

修改id列的主键索引为唯一索引或者普通索引；

调整表DDL定义时指定的AUTO_INCREMENT值为0或1；

修改表引擎为InnoDB或MyISAM；

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

4、其他建议

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

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

猜您喜欢

MySQL复制线程长时间Opening tables

OurMySQL — Thu, 05 May 2016 13:07:12 +0000

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（performanc_schema）库的几个表结构都异常了。另外，mysql.innodb_table_stats表也异常了，导致InnoDB表数据收集异常，然后SQL thread也跟着异常了。

3、问题解决建议

造成上面问题主要原因是，没有用正确的方式来部署slave实例。从master上用xtrabackup物理备份到slave，启动实例后，应该再执行 mysql_upgrade 升级相关表结构，确保P_S（performanc_schema）、I_S（information_schema）以及 mysql 等几个系统库表结构都升级到最新版本。

猜想失败，您看看下面的文章有用吗？

MySQL 管理工具集 percona-toolkit

OurMySQL — Sun, 17 Apr 2016 17:46:38 +0000

apt-get install -y percona-toolkit

# 检查重复索引
pt-duplicate-key-checker -host=’sohu.dev.chenpeng.info’ -port=’43307′ -databases=’grape’

# 运行状态监控
pt-mysql-summary -host=’sohu.dev.chenpeng.info’ -port=’43307′ -databases=’grape’

# 在线修改表结构
pt-online-schema-change -host=’sohu.dev.chenpeng.info’ -port=’3306′ -alter “ADD COLUMN c1 INT” D=chenpeng_litchi,t=lc_operate_log -execute -print
pt-online-schema-change -host=’sohu.dev.chenpeng.info’ -port=’3306′ -alter “ENGINE=InnoDB” D=chenpeng_litchi,t=lc_operate_log -execute -print

# 格式化explain结果
mysql -hsohu.dev.chenpeng.info -P43307 grape -e”explain SELECT a.line_id,b.dnf_source FROM gp_report_advertiser_day a INNER JOIN gp_line b ON a.line_id=b.id WHERE a.data_date=’2016-04-10′ LIMIT 100″ | pt-visual-explain

重复索引结果示例：

# ########################################################################
# grape.gp_mars_report_advertiser_hour
# ########################################################################

# line_id is a left-prefix of line_id_data_time
# Key definitions:
# KEY `line_id` (`line_id`) USING BTREE,
# KEY `line_id_data_time` (`line_id`,`data_time`) USING BTREE
# Column types:
# `line_id` bigint(20) unsigned not null comment ‘??id’
# `data_time` datetime not null comment ‘??’
# To remove this duplicate index, execute:
ALTER TABLE `grape`.`gp_mars_report_advertiser_hour` DROP INDEX `line_id`;

# ########################################################################
# grape.gp_operate_log
# ########################################################################

# related_id is a left-prefix of search3
# Key definitions:
# KEY `related_id` (`related_id`),
# KEY `search3` (`related_id`,`opt_datetime`),
# Column types:
# `related_id` int(10) unsigned not null comment ‘?????id’
# `opt_datetime` datetime not null comment ‘??????’
# To remove this duplicate index, execute:
ALTER TABLE `grape`.`gp_operate_log` DROP INDEX `related_id`;

# ########################################################################
# grape.gp_report_advertiser_hour
# ########################################################################

# idx_line_id is a left-prefix of line_id_data_time
# Key definitions:
# KEY `idx_line_id` (`line_id`),
# KEY `line_id_data_time` (`line_id`,`data_time`),
# Column types:
# `line_id` bigint(20) not null
# `data_time` datetime not null
# To remove this duplicate index, execute:
ALTER TABLE `grape`.`gp_report_advertiser_hour` DROP INDEX `idx_line_id`;

# ########################################################################
# Summary of indexes
# ########################################################################

# Size Duplicate Indexes 48
# Total Duplicate Indexes 3
# Total Indexes 268

工具检测到gp_operate_log有重复的索引，提供以下SQL语句删除重复的索引：

ALTER TABLE `grape`.`gp_operate_log` DROP INDEX `related_id`;

explain简化显示示例：

root@ubuntu:/tmp/d3OnxmMr# mysql -hsohu.dev.chenpeng.info -P43307 -ugrapeuser -pfaf74223tr grape -e”explain SELECT a.line_id,b.dnf_source FROM gp_report_advertiser_day a INNER JOIN gp_line b ON a.line_id=b.id WHERE a.data_date=’2016-04-10′ LIMIT 100″ | pt-visual-explain
JOIN
+- Filter with WHERE
| +- Bookmark lookup
| +- Table
| | table b
| | possible_keys PRIMARY
| +- Unique index lookup
| key b->PRIMARY
| possible_keys PRIMARY
| key_len 4
| ref grape.a.line_id
| rows 1
+- Index lookup
key a->data_date
possible_keys line_id,data_date
key_len 3
ref const
rows 10722

猜想失败，您看看下面的文章有用吗？

MySQL锁问题最佳实践

OurMySQL — Tue, 05 Apr 2016 10:30:05 +0000

最近一段时间处理了较多锁的问题，包括锁等待导致业务连接堆积或超时，死锁导致业务失败等，这类问题对业务可能会造成严重的影响，没有处理经验的用户往往无从下手。下面将从整个数据库设计，开发，运维阶段介绍如何避免锁问题的发生，提供一些最佳实践供RDS的用户参考。

一．设计阶段：在数据库设计阶段，引擎选择和索引设计不当可能导致后期业务上线后出现较为严重的锁或者死锁问题。

(1).表引擎选择使用myisam，引发table level lock wait。

从5.5版本开始，MySQL官方就把默认引擎由myisam转为innodb，这两种引擎的主要区别：

由于myisam引擎只支持table lock，在使用myisam引擎表过程中，当数据库中出现执行时间较长的查询后就会堵塞该表上的更新动作，所以经常会碰到线程会话处于表级锁等待（Waiting for table level lock）的情况，严重的情况下会出现由于实例连接数被占满而应用无法正常连接的情况

CREATE TABLE `t_myisam` (

`id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8；

Query |111 | User sleep | select id,sleep(100) from t_myisam |

Query |108 | Waiting for table level lock | update t_myisam set id=2 where id=1|

Query | 3 | Waiting for table level lock | update t_myisam set id=2 where id=1|

从上述的案例中可以看到，t_myisam表为myisam存储引擎，当该表上有执行时间较长的查询语句在执行的时候，该表上其他的更新全被堵塞住了，这个时候应用或者数据库的连接很快耗完，导致应用请求失败。这个时候快速的恢复方法为将线程id：111 kill掉即可（可以执行show processlist查看到当前数据库所有连接状态）。另外myisam存储引擎的表索引在实例异常关闭的情况下会导致索引损坏，这个时候必须要对表进行repair操作，该操作同样会阻塞该表上的所有请求。

(2).表索引设计不当，导致数据库出现死锁。

索引设计是数据库设计非常重要的一环，不仅仅关系到后续业务的性能，如果设计不当还可导致业务上的死锁。下面的一则案例就出现在线上系统，数据库在并发更新的时候出现了死锁，通过排查定位于update更新使用了两个索引导致，死锁信息如下：

*** (1) TRANSACTION:

TRANSACTION 29285454235, ACTIVE 0.001 sec fetching rows

mysql tables in use 3, locked 3

LOCK WAIT 6 lock struct(s), heap size 1184, 4 row lock(s)

MySQL thread id 6641616, OS thread handle 0x2b165c4b1700, query id 28190427937 10.103.180.86 test_ebs Searching rows for update

UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4580605

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 132 page no 37122 n bits 352 index `PRIMARY` of table `testdriver`.`test` trx id 29285454235 lock_mode X locks rec but not gap waiting

Record lock, heap no 179 PHYSICAL RECORD: n_fields 8; compact format; info bits 0

*** (2) TRANSACTION:

TRANSACTION 29285454237, ACTIVE 0.001 sec fetching rows, thread declared inside InnoDB 4980

mysql tables in use 3, locked 3 5 lock struct(s), heap size 1184, 3 row lock(s)

MySQL thread id 6639213, OS thread handle 0x2b1694cc2700, query id 28190427939 10.103.180.113 test_ebs Searching rows for update

UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 132 page no 37122 n bits 352 index `PRIMARY` of table `testdriver`.`test` trx id 29285454237 lock_mode X locks rec but not gap

Record lock, heap no 179 PHYSICAL RECORD: n_fields 8; compact format;

表结构：

CREATE TABLE `test` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,

`test_id` bigint(20) DEFAULT NULL,

`group_id` bigint(20) DEFAULT NULL COMMENT ‘Id，对应test_group.id’,

`gmt_created` datetime DEFAULT NULL COMMENT ‘创建时间’,

`gmt_modified` datetime DEFAULT NULL COMMENT ‘修改时间’,

`is_deleted` tinyint(4) DEFAULT ‘0’ COMMENT ‘删除。’,

PRIMARY KEY (`id`),

KEY `idx_testid` (`test_id`),

KEY `idx_groupid` (`group_id`)

) ENGINE=InnoDB AUTO_INCREMENT=7429111 ;

SQL执行计划：

mysql>explain UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859

所以第一个事务先根据group_id索引，已经锁住primary id，然后再根据test_id索引，锁定primary id；第二个事务先根据test_id索引，已经锁住primary id，然后再根据group_id索引，去锁primary id；所以这样并发更新就可能出现死索引。

MySQL官方也已经确认了此bug：https://bugs.mysql.com/bug.php?id=77209

解决方法有两种：

第一、添加test_id+group_id的组合索引，这样就可以避免掉index merge；

第二、将优化器的index merge优化关闭；

二．开发阶段：事务处理时间过长，导致并发出现锁等待。

并发事务处理在数据库中经常看到的应用场景，在这种场景下，需要避免大事务，长事务，复杂事务导致事务在数据库中的运行时间加长，事务时间变长则导致事务中锁的持有时间变长，影响整体的数据库吞吐量。下面的一则案例中，用户的业务数据库中出现大量的update等待，导致大量的业务超时报错：

问题排查：

1).通过show processlist 确定出现锁等待的线程以及SQL信息:

|Query|37|updating|UPDATE test_warning SET … WHERE test_id = ’46a9b’

2).通过innodb的information_schema数据库中的锁等待以及事务试图，查出相关的锁信息：

select r.trx_mysql_thread_id waiting_thread, r.trx_id waiting_trx_id,r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_query blocking_query,b.trx_mysql_thread_id blocking_thread,b.trx_ started,b.trx_wait_started from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id =w.blocking_trx_id inner join

information_schema.innodb_trx r on r.trx_id=w.requesting_trx_id \G

waiting_thread: 318984063

waiting_trx_id: 26432631

waiting_query: UPDATE test_warning SET …….. WHERE test_id = ’46a9b’

blocking_trx_id: 26432630

blocking_query: NULL

blocking_thread: 235202017

trx_started: 2016-03-01 13:54:39

从述的锁等待信息中发现，事务26432631被26432630阻塞了，那么我们就可以从general log中去排查一下事务26432630做了哪些操作。

3).从提前打开数据库的general log中查找到上述被blcok 的update语句在日志中的位置，发现了update被blcok的根本原因：日志中有两条不同的SQL同时并发更新同一条记录，所以后更新的SQL会等待前更新的SQL，如果SQL1所在的事务1直没有提交，那么事务2将会一直等待，这样就出现上述updating的状态

235202017 Query UPDATE test_warning ….. WHERE test_id = ’46a9b’

318984063 Query UPDATE test_warning ….. task_order_id = ” WHERE test_id = ’46a9b’

所以我们就可以在看一下这个事务的上下文：

thread id=235202017 的SQL上下文：

235202017 Query SET autocommit=0

235202017 (13:54:39) Query UPDATE test_warning SET …. WHERE test_id = ’46a9b’

235202017 Query commit

thread id=318984063 的SQL上下文：

318984063 Query SET autocommit=1

318984063 (13:54:39)Query SELECT …. FROM test_waybill WHERE (test_id IN (’46a9b’))

318984063 Query SELECT……FROM test_waybill WHERE test_id = ’46a9b’

318984063 Query UPDATE test_warning SET ….. WHERE test_id = ’46a9b’

318984063 (13:55:31)Query UPDATE test_waybill_current t ….. WHERE t.test_id IN (’46a9b’)

318984063 Query SET autocommit=0

可以看到事务1 从13:54:39开始，直到13:55:30结束，事务2 中有更新事务1中的同一条记录，所以直到事务1 提交后，事务2才得以执行完毕，有了这样的日志，将此信息发给用户很快就找到了问题，在事务1中由于还存在其他的业务逻辑，导致事务1的提交迟迟没有完成，进而导致了其他业务锁的发生。

三．维护阶段：DDL操作被大查询block。

当应用上线进入维护阶段，则开始会有较多的数据库变更操作，比如：添加字段，添加索引等操作，这一类操作导致的锁故障也是非常频繁的，下面将会介绍一则案例，一个DDL操作被查询block，导致数据库连接堆积：

Query |6 | User sleep | select id ,sleep(50) from t

Query |4 | Waiting for table metadata lock | alter table t add column gmt_create datetime

Query |2 | Waiting for table metadata lock | select * from t where id=1

Query |1 | Waiting for table metadata lock | select * from t where id=2

Query |1 | Waiting for table metadata lock | update t set id =2 where id=1

Metadata lock wait 的含义：为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ，来保护表的元数据信息。因此在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在 Metadata lock wait。

导致 Metadata lock wait 等待的常见因素包括：活动事务，当前有对表的长时间查询，显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，表上有失败的查询事务等。

上述案例中，查询，更新和DDL操作的线程状态都为Waiting for table metadata lock，对表t的操作全部被阻塞，前端源源不断的请求到达数据库，这个时候数据库的连接很容易被打满，那我们来分析一下为什么有这这些锁等待：

alter 操作的锁等待：由于在表t上做了一个添加字段的操作，该操作会在结束前对表获取一个metadata lock，但是该表上已经有一个查询一直未结束，导致metadata lock一直获取不到，所以添加字段操作只能等待查询结束，这就解释了alter操作为什么状态为Waiting for table metadata lock。
查询和更新的锁等待：由于前面进行的alter操作已经在T表上试图获取metadata lock，所以后续对表T的的查询和更新操作在获取metadata lock的时候会被alter操作所阻塞，进而导致这些线程状态为Waiting for table metadata lock。

解决办法则是将线程6 kill 掉即可。

锁问题是非常常见的问题，需要我们在数据库开发，设计，管理的各个阶段都需要注意，防范未然，做到心中有数。

设计开发阶段

表设计要避免使用myisam存储引擎，改用innodb引擎；
为SQL创建合适的索引，避免多个单列索引执行出错；
避免大事务，长事务，复杂事务导致事务在数据库中的运行时间加长；

管理运维阶段：

在业务低峰期执行上述操作，比如创建删除索引；
在结构变更前，观察数据库中是否存在长时间运行的SQL，未提交的事务；
结构变更期间，监控数据库的线程状态是否存在lock wait。

猜您喜欢

MySQL 加锁处理分析

MySQL防范SQL注入风险

OurMySQL — Tue, 05 Apr 2016 10:17:08 +0000

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)))；

猜您喜欢

解决 SQL 注入的另类方法

MySQL relay_log_purge=0 时的风险

OurMySQL — Sat, 02 Apr 2016 13:49:07 +0000

有时候，我们希望将 MySQL 的 relay log 多保留一段时间，比如用于高可用切换后的数据补齐，于是就会设置 relay_log_purge=0，禁止 SQL 线程在执行完一个 relay log 后自动将其删除。但是在官方文档关于这个设置有这么一句话：

Disabling purging of relay logs when using the --relay-log-recovery option risks data consistency and is therefore not crash-safe.

究竟是什么样的风险呢？查找了一番后，基本上明白了原因。

首先，为了让从库是 crash safe 的，必须设置 relay_log_recovery=1，这个选项的作用是，在 MySQL 崩溃或人工重启后，由于 IO 线程无法保证记录的从主库读取的 binlog 位置的正确性，因此，就不管 master_info 中记录的位置，而是根据 relay_log_info 中记录的已执行的 binlog 位置从主库下载，并让 SQL 线程也从这个位置开始执行。MySQL 启动时，相当于执行了 flush logs ，会新开一个 relay log 文件，新的 relay log 会记录在新的文件中。如果默认情况 relay_log_purge=1 时，SQL 线程就会自动将之前的 relay log 全部删除。而当 relay_log_purge=0 时，旧的 relay log 则会被保留。虽然这并不会影响从库复制本身，但还是会有地雷：

由于崩溃或停止 MySQL 时，SQL 线程可能没有执行完全部的 relay log，最后一个 relay log 中的一部分数据会被重新下载到新的文件中。也就是说，这部分数据重复了两次。
如果 SQL 跟得很紧，则可能在 IO 线程写入 relay log ，但还没有将同步到磁盘时，就已经读取执行了。这时，就会造成新的文件和旧的文件中少了一段数据。

如果我们读取 relay log 来获取数据，必须注意这一点，否则就会造成数据不一致。而保留 relay log 的目的也在于此。因此，在处理 relay log 时必须格外小心，通过其中 binlog 头信息来确保正确性。

关于如何配置 crash safe 的复制本身的配置，可以参照：
http://blog.itpub.net/22664653/viewspace-1752588/
http://www.innomysql.net/article/34.html

参考资料：
http://blog.booking.com/better_crash_safe_replication_for_mysql.html
https://bugs.mysql.com/bug.php?id=73038
http://bugs.mysql.com/bug.php?id=74324

猜您喜欢

MySQL 二进制日志格式基础(一)

MySQL安全策略

OurMySQL — Tue, 22 Mar 2016 23:03:21 +0000

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 5.6 新增的两个密码安全策略体验

MYSQL分页limit速度太慢优化方法

OurMySQL — Mon, 21 Mar 2016 23:24:00 +0000

在mysql中limit可以实现快速分页，但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了，否则可能卡死你的服务器哦。

当一个表数据有几百万的数据的时候成了问题！

如 * from table limit 0,10 这个没有问题当 limit 200000,10 的时候数据读取就很慢，可以按照一下方法解决

第一页会很快

PERCONA PERFORMANCE CONFERENCE 2009上，来自雅虎的几位工程师带来了一篇”EfficientPagination Using MySQL”的报告

limit10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里。

LIMIT 451350 , 30 扫描了45万多行，怪不得慢的都堵死了。

但是

limit 30 这样的语句仅仅扫描30行。

那么如果我们之前记录了最大ID，就可以在这里做文章

举个例子

日常分页SQL语句

select id,name,content from users order by id asc limit 100000,20

扫描100020行

如果记录了上次的最大ID

select id,name,content from users where id>100073 order by id asc limit 20

扫描20行。

总数据有500万左右

以下例子当时候 select * from wl_tagindex where byname=’f’ order by id limit 300000,10 执行时间是 3.21s

优化后：

select * from (

select id from wl_tagindex

where byname=’f’ order by id limit 300000,10

) a

left join wl_tagindex b on a.id=b.id

执行时间为 0.11s 速度明显提升

这里需要说明的是我这里用到的字段是 byname ,id 需要把这两个字段做复合索引，否则的话效果提升不明显

总结

当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。

如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。

如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。

SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);

如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0，这个主键最好是int类型并且auto_increment

SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;

这条语句，大意如下:

SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10;

如果limit的offset值过大，用户也会翻页疲劳，你可以设置一个offset最大的，超过了可以另行处理，一般连续翻页过大，用户体验很差，则应该提供更优的用户体验给用户。

limit 分页优化方法

1.子查询优化法

先找出第一条数据，然后大于等于这条数据的id就是要获取的数据

缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性

实验下

mysql> set profi=1;

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from Member;

+———-+

| count(*) |

+———-+

| 169566 |

+———-+

1 row in set (0.00 sec)

mysql> pager grep !~-

PAGER set to ‘grep !~-‘

mysql> select * from Member limit 10, 100;

100 rows in set (0.00 sec)

mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100;

100 rows in set (0.00 sec)

mysql> select * from Member limit 1000, 100;

100 rows in set (0.01 sec)

mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100;

100 rows in set (0.00 sec)

mysql> select * from Member limit 100000, 100;

100 rows in set (0.10 sec)

mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;

100 rows in set (0.02 sec)

mysql> nopager

PAGER set to stdout

mysql> show profilesG

*************************** 1. row ***************************

Query_ID: 1

Duration: 0.00003300

Query: select count(*) from Member

*************************** 2. row ***************************

Query_ID: 2

Duration: 0.00167000

Query: select * from Member limit 10, 100

*************************** 3. row ***************************

Query_ID: 3

Duration: 0.00112400

Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100

*************************** 4. row ***************************

Query_ID: 4

Duration: 0.00263200

Query: select * from Member limit 1000, 100

*************************** 5. row ***************************

Query_ID: 5

Duration: 0.00134000

Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100

*************************** 6. row ***************************

Query_ID: 6

Duration: 0.09956700

Query: select * from Member limit 100000, 100

*************************** 7. row ***************************

Query_ID: 7

Duration: 0.02447700

Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

从结果中可以得知，当偏移1000以上使用子查询法可以有效的提高性能。

2.倒排表优化法

倒排表法类似建立索引，用一张表来维护页数，然后通过高效的连接得到数据

缺点：只适合数据数固定的情况，数据不能删除，维护页表困难

3.反向查找优化法

当偏移超过一半记录数的时候，先用排序，这样偏移就反转了

缺点：order by优化比较麻烦，要增加索引，索引影响数据的修改效率，并且要知道总记录数

，偏移大于数据的一半

引用

limit偏移算法：

正向查找： (当前页 – 1) * 页长度

反向查找：总记录 – 当前页 * 页长度

做下实验，看看性能如何

总记录数：1,628,775

每页记录数： 40

总页数：1,628,775 / 40 = 40720

中间页数：40720 / 2 = 20360

第21000页

正向查找SQL:

Sql代码

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40

时间：1.8696 秒

反向查找sql:

Sql代码

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40

时间：1.8336 秒

第30000页

正向查找SQL:

Sql代码

1.SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40

时间：2.6493 秒

反向查找sql:

Sql代码

1.SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

时间：1.0035 秒

注意，反向查找的结果是是降序desc的，并且InputDate是记录的插入时间，也可以用主键联合索引，但是不方便。

4.limit限制优化法

把limit偏移量限制低于某个数。。超过这个数等于没数据，我记得alibaba的dba说过他们是这样做的

5.只查索引法

猜您喜欢

获取 MySQL 崩溃时的 core file

OurMySQL — Mon, 21 Mar 2016 13:56:23 +0000

对于一般进程，要让进程崩溃时能生成 core file 用于调试，只需要设置 rlimit 的 core file size > 0 即可。比如，用在 ulimit -c unlimited 时启动程序。

对 MySQL 来说，由于 core file 中会包含表空间的数据，所以默认情况下为了安全，mysqld 捕获了 SEGV 等信号，崩溃时并不会生成 core file，需要在 my.cnf 或启动参数中加上 core-file。

但是即使做到了以上两点，在 mysqld crash 时还是可能无法 core dump。还有一些系统参数会影响 core dump。以下脚本可供参考：

echo 2 >/proc/sys/fs/suid_dumpable
chmod 0777 /var/crash
echo /var/crash/core> /proc/sys/kernel/core_pattern
echo 1 >/proc/sys/kernel/core_uses_pid

由于 mysql 通常会以 suid 方式启动，所以需要打开 suid_dumpable 。对于 core_pattern，最好指定一个保证可写的绝对路径。

之后，就可以用 kill -SEGV 让 mysqld 崩溃，测试一下能不能正常产生 core file 了。

猜想失败，您看看下面的文章有用吗？

MySQL如何将两个表名对调

OurMySQL — Sun, 20 Mar 2016 22:16:02 +0000

问题:

有位同学问我，在类似pt-osc场景下，需要将两个表名对调，怎么才能确保万无一失呢？

分析:

估计其他同学就笑了，表名对掉还不简单吗，相互RENAME一下嘛。

但是，我们想要的是同时完成表名对调，如果是先后的对掉，可能会导致有些数据写入失败，那怎么办？

其实也不难，从MySQL手册里就能找到方法，那就是：同时锁定2个表，不允许写入，然后对调表名。

我们通常只锁一个表，那么同时锁两个表应该怎么做呢，可以用下面的方法：

LOCK TABLES t1 WRITE, t2 WRITE;
ALTER TABLE t1 RENAME TO t3;
ALTER TABLE t2 RENAME TO t1;
ALTER TABLE t3 RENAME TO t2;
UNLOCK TABLES;

看到了吧，其实很简单，两个表同时加表级写锁，然后用ALTER语法改名就可以了。

废话挺多的，谢谢各位客官耐心看完：)