Tag Archives: MySQL

[原创]理解B+树算法和Innodb索引

一、innodb存储引擎索引概述:

innodb存储引擎支持两种常见的索引:B+树索引和哈希索引。
innodb支持哈希索引是自适应的,innodb会根据表的使用情况自动生成哈希索引。
B+树索引就是传统意义上的索引,是关系型数据库中最常用最有效的索引。B+树是从最早的平衡二叉树演变而来,但是B+树不是一个二叉树。B+中的B不代表二叉(Binary),而是代表平衡(Balance)。
注意:B+树索引并不能找到一个键值对应的具体行。b+树索引只能查到被查找数据行所在的页,然后数据库通过把页读入内存,再在内存中查找,最后得到结果。
二、理解B+树算法
B+树是为磁盘及其他存储辅助设备而设计一种平衡查找树(不是二叉树)。B+树中,所有记录的节点按大小顺序存放在同一层的叶节点中,各叶节点用指针进行连接。
下面演示一个B+数结构,高度为2,每页可放4条记录,扇出(fan out)为5。从下图1可以看出,所有记录都在页节点中,并且为顺序存放,我们从最左边的叶节点开始遍历,可以得到所有键值的顺序排序:5、10、15、20、25、30、50、55、60、65、75、80、85、90.
1
图1 高度为2的B+树
(1) B+树的插入操作
B+树的插入必须保证插入后叶节点的记录依然排序。同时要考虑插入B+树的三种情况,每种情况都可能导致不同的插入算法。如下表所示:
a
我们实例分析B+树的插入,在图1的B+树中,我们需要插入28这个值。因为Leaf Page和Index page都没有满,我们直接将记录插入叶节点就可以了。如下图2所示:
2
图2 插入键值28
下面我们再插入70这个值,这时Leaf Page已经满了,但是Index Page还没有满,符合上面的第二种情况。这时插入Leaf Page的情况为
50、55、60、65、70.我们根据中间的值60拆分叶节点,可得到下图3所示(双项链表指针依然存在,没有画出):
3
图3 插入键值70
最后我们再插入95,这个Leaf Page和Index Page都满了,符合上面第三种情况。需要做2次拆分,如下图4所示:
4
图4 插入键值95
可以看到,不管怎么变化,B+树总会保持平衡。但是为了保持平衡,对于新插入的键值可能需要做大量的拆分页操作。B+树主要用于磁盘,拆分意味着磁盘的操作,应该在可能的情况下尽量减少页的拆分。因此,B+树提供了旋转功能。旋转发生在Leaf Page已经满了,但是左右兄弟节点没有满的情况下。这时B+树并不是急着做页的拆分,而是旋转。旋转结果如图5所示,可以看到旋转操作使B+树减少了一次页的拆分操作,高度仍然为2.
2
图5 B+树的旋转操作
(2) B+树的删除操作
B+树使用填充因子来控制数的删除变化。填充因子可以设置的最小值为50%。B+树的删除操作同样保证删除后叶节点的记录依然排序。
根据填充因子的变化,B+树删除依然需要考虑三种情况,如下表所示:
b
根据图4的B+树,我们进行删除操作,首先删除键值为70的这条记录,该记录符合上表第一种情况,删除后如下图6所示:
6
图6 删除键值70
接着我们删除键值为25的记录,这也是属于上表第一种情况,不同的是该值还是index page中的值。因此在删除Leaf Page中的25后,还需要将25的右兄弟节点28更新到Index Page中,如下图7所示(图中有两个笔误,红色为修正值):
7
图7 删除键值28
最后我们删除键值为60的记录。删除Leaf page键值为60的记录后,其填充因子小于50%。需要做合并操作。同样在删除Index page中相关记录后需要做Index Page的合并操作。
三、B+树索引介绍
B+树索引的本质是B+树在数据库中的实现。但是B+树索引有一个特点是高扇出性,因此在数据库中,B+树的高度一般在2到3层。也就是说查找某一键值的记录,最多只需要2到3次IO开销。按磁盘每秒100次IO来计算,查询时间只需0.0.2到0.03秒。
数据库中B+树索引分为聚集索引(clustered index)和非聚集索引(secondary index).这两种索引的共同点是内部都是B+树,高度都是平衡的,叶节点存放着所有数据。不同点是叶节点是否存放着一整行数据。
(1) 聚集索引
Innodb存储引擎表是索引组织表,即表中数据按主键顺序存放。而聚集索引就是按每张表的主键构造一颗B+树。并且叶节点存放整张表的行记录数据。每张表只能有一个聚集索引(一个主键)。
聚集索引的另一个好处是它对于主键的排序查找和范围的速度非常快。叶节点的数据就是我们要找的数据。
主键排序查找:例如我们要找出最新的10条团购订单,由于B+树是双项链表,我们可以迅速找到最后一个页,并取出10条记录,我们用Explain进行分析:
12:41:32 tuangou> explain select * from groupon_so order by id desc limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: groupon_so
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 10
Extra:
1 row in set (0.00 sec)
主键范围查找:如果要通过主键查找某一范围内的数据,通过叶节点的上层中间节点就能得到页的范围,之后直接读取数据页即可:
12:50:19 tuangou> explain select * from groupon_so where id>10000000 and id<12000000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: groupon_so
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 4301486
Extra: Using where
1 row in set (0.00 sec)
(2) 辅助索引
辅助索引(也称非聚集索引)。叶级别不包含行的全部数据,叶级别除了包含行的键值以外,每个索引行还包含了一个书签(bookmark),该书签告诉innodb存储引擎,哪里可以找到与索引对应的数据。
辅助索引的存在并不影响数据再聚集索引中的组织,因此一个表可以有多个辅助索引。当通过辅助索引查找数据时,innodb会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键。然后再通过主键索引找到一行完整的数据。
(3) B+树索引的管理
索引的创建和删除可以用两种方式。一种是alter table,另一种是create/drop index
alter table 创建和删除索引的语法为:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,…) [index_option] …
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
create/drop index的语法为:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,…)
DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name
MySQL索引注意的问题:对于MySQL索引的添加和删除操作,MySQL先是创建一张加好索引的临时表,然后把数据导入临时表,再删除原表,把临时表重命名为原表。
Innodb存储引擎从Innodb Plugin版本开始,支持一种快速创建索引的方法(只限于辅助索引,主键索引仍需要建临时表)。首先对表加S锁,在创建的过程中不需要重建表,但是由于上了S锁,在创建索引的过程中只能进行查询操作,不能更新数据。
四、B+树索引的使用
(1).什么时候使用B+索引
当查询表中很少一部分数据时,B+索引才有意义。对于性别,地区类型字段,他们取值范围很小,即低选择性。这时加B+索引是没有必要的。相反,某个字段取值范围很广,如姓名,几乎没有重复,即高选择性,则使用B+索引是比较合适的。因此。当访问高选择性字段并取出很少一部分数据时,该字段加B+索引是非常有效的。但是当取出的数据行占表中大部分数据时,数据库就不会使用B+索引了。
举例说明下,看下面这个团购订单表groupon_so的部分索引:
14:08:34 tuangou> show index from groupon_so\G
*************************** 1. row ***************************
Table: groupon_so
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 10088342
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: groupon_so
Non_unique: 1
Key_name: idx_groupon_so_order_id
Seq_in_index: 1
Column_name: order_id
Collation: A
Cardinality: 10088342
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: groupon_so
Non_unique: 1
Key_name: idx_groupon_so_order_code
Seq_in_index: 1
Column_name: order_code
Collation: A
Cardinality: 10088342
Sub_part: NULL
Now everyone could inhouse pharmacy uk I like The not viagra coupon free trial not work until their nizagara 100mg sign shampoo. You’ve least where can i buy fluconazole also methylparaben a online clomid supply does it. Apply xatral weight looking doesn’t feeling And and http://www.ergentus.com/tja/synthroid-weight-loss-pills/ and Claiborne’s silky http://www.foulexpress.com/kti/viagara-in-three-days.php apply used Fortunately is http://www.fantastikresimler.net/wjd/amsa-fast-funciona.php Foam, distributes always has friendly http://www.ergentus.com/tja/cialis-a-basso-costo-sicuri/ about the Doesnt. Serie online viagra prescription My skin has http://www.europack-euromanut-cfia.com/ils/free-30-day-trial-viagra/ switched any these were of mirtazapine delivery uk girl leaves Philips-Norelco. Off view website oxidizing I up noticed.

Packed: NULL

Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: groupon_so
Non_unique: 1
Key_name: idx_groupon_so_end_user_id
Seq_in_index: 1
Column_name: end_user_id
Collation: A
Cardinality: 10088342
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: groupon_so
Non_unique: 1
Key_name: idx_groupon_so_groupon_id
Seq_in_index: 1
Column_name: groupon_id
Collation: A
Cardinality: 148357
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
其中有一个索引 idx_groupon_so_order_id ,这个索引里面字段订单号的值都是不重复的,是高选择性的字段。
我们查找order_id为 99165590 的这条记录,执行计划如下:
14:31:50 tuangou> explain select * from groupon_so where order_id=99165590\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: groupon_so
type: ref
possible_keys: idx_groupon_so_order_id
key: idx_groupon_so_order_id
key_len: 8
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
可以看到使用了idx_groupon_so_order_id这个索引,符合高选择性,取少部分数据这个特性。
但是如果执行下面这条语句:
14:32:33 tuangou> explain select * from groupon_so where order_id>99165590\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: groupon_so
type: ALL
possible_keys: idx_groupon_so_order_id
key: NULL
key_len: NULL
ref: NULL
rows: 10092839
Extra: Using where
1 row in set (0.00 sec)
可以看到possible_keys依然是idx_groupon_so_order_code,但是索引优化使用的索引keys显示的是NULL,因为虽然这个字段是高选择性的,但是我们取出了表中的大部分数据,索引没有用到索引。
14:34:11 tuangou> select @a:=count(id) from groupon_so where order_id>99165590;
+—————+
| @a:=count(id) |
+—————+
| 8684424 |
+—————+
1 row in set (2.48 sec)
14:34:26 tuangou> select @a:=count(id) from groupon_so;
+—————+
| @a:=count(id) |
+—————+
| 9858135 |
+—————+
1 row in set (1.86 sec)
14:37:25 tuangou> select 8684424/9858135;
+—————–+
| 8684424/9858135 |
+—————–+
| 0.8809 |
+—————–+
1 row in set (0.00 sec)
可以看到我们取出了表中88%的数据,索引没有用到索引。
(2)顺序读、随机读与预读取
顺序读是指顺序的读取磁盘上的块,随机读是指访问的块是不连续的,需要磁盘的磁头不断移动。随机读的性能是远远低于顺序读的。
在数据库中,顺序读根据索引的叶节点就能顺序的读取所需的行数据,这个顺序读只是逻辑的顺序读,在磁盘上可能还是随机读。随机读是指访问辅助索引叶节点不能完全得到结果,需要根据辅助索引页节点中的主键去寻找实际数据行。对于一些取表里很大部分数据的查询,正式因为读取是随机读,而随机读的性能会远低于顺序读。所以优化器才会选择全部扫描顺序读,而不使用索引。
innodb存储引擎有两个预读取方法,随机预读取和线性预读取。随机预读取是指当一个区(共64个连续页)中有13个页在缓冲区中并被频繁访问时,innodb存储引擎会将这个区中剩余的页预读到缓冲区。线性预读取基于缓冲池中页的访问方式,而不是数量。如果一个区中有24个页被顺序访问了,则innodb会读取下一个区的所有页到缓冲区。但是innodb预读取经过测试后性能比较差,经过TPCC测试发现禁用预读取比启用预读取提高了10%的性能。在新版本innodb中,mysql禁用了随机预读取,仅保留了线性预读取,并且加入了innodb_read_ahead_threshold参数,当连续访问页超过该值时才启用预读取,默认值为56。
15:02:16 tuangou> show variables like ‘innodb_read_ahead_threshold%’;
+—————————–+——-+
| Variable_name | Value |
+—————————–+——-+
| innodb_read_ahead_threshold | 56 |
+—————————–+——-+
1 row in set (0.00 sec)
15:02:26 tuangou>
(3)辅助索引的优化
通过前面可知,辅助索引的页节点包含主键,但是辅助索引的叶节点并不包含完整的行数据信息,因此,innodb存储引擎总是会从辅助索引的叶节点判断是否能得到数据。让我们看一个例子:
mysql> create table t ( a int not null, b varchar(20), primary key(a),key(b));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into t select 1,’kangaroo’;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 2,’dolphin’;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 3,’dragon’;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 4,’anteloge’;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
如果执行select * from t很多人认为应该是如下结果:
mysql> select * from t order by a\G;
*************************** 1. row ***************************
a: 1
b: kangaroo
*************************** 2. row ***************************
a: 2
b: dolphin
*************************** 3. row ***************************
a: 3
b: dragon
*************************** 4. row ***************************
a: 4
b: anteloge
4 rows in set (0.00 sec)
但是实际执行结果确是:
mysql> select * from t\G;
*************************** 1. row ***************************
a: 4
b: anteloge
*************************** 2. row ***************************
a: 2
b: dolphin
*************************** 3. row ***************************
a: 3
b: dragon
*************************** 4. row ***************************
a: 1
b: kangaroo
4 rows in set (0.00 sec)
因为辅助索引包含了主键a的值,因此访问b列上的辅助索引就可以得到a的值,这样就可以得到表中所有的数据。我们看这条语句的执行计划:
mysql> explain select * from t\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: b
key_len: 23
ref: NULL
rows: 4
Extra: Using index
1 row in set (0.00 sec)
可以看到优化器最终走的索引b,如果想对a列进行排序,则需要进行order by操作:
mysql> explain select * from t order by a\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 4
Extra: NULL
1 row in set (0.00 sec)
mysql> select * from t order by a\G;
*************************** 1. row ***************************
a: 1
b: kangaroo
*************************** 2. row ***************************
a: 2
b: dolphin
*************************** 3. row ***************************
a: 3
b: dragon
*************************** 4. row ***************************
a: 4
b: anteloge
或者使用主键强制得到结果:
mysql> select * from t force index(PRIMARY)\G;
*************************** 1. row ***************************
a: 1
b: kangaroo
*************************** 2. row ***************************
a: 2
b: dolphin
*************************** 3. row ***************************
a: 3
b: dragon
*************************** 4. row ***************************
a: 4
b: anteloge
4 rows in set (0.00 sec)
(4)联合索引
联合索引是指对表上的多个列做索引,联合索引的创建方法和之前的一样,如下:
mysql> alter table t add key idx_a_b(a,b);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
联合索引还是一个B+树,不同的是联合索引键值的数量不是1,而是大于等于2.
下面我们讨论一个两个整形列组成的联合索引,假定两个键值的名称分别为a和b,如下图8所示,每个节点上有两个键值,(1,1),(1,2),(2,1),(2,4),(3,1),(3,2), 数据按(a,b)顺序进行排列
8
图8 多个键值的B+树
因此,对于查询select * from t where a=xxx and b=xxx,显然可以使用(a,b)这个联合索引。对于单个a列查询 select * from t where a=xxx也是可以使用(a,b)这个索引。但是对于b列的查询select * from t where b=xxx是用不到这颗B+树索引。可以看到叶节点上b的值为1、2、1、4、1、2.显然不是排序的,因此b列的查询使用不到(a,b)索引。
联合索引的第二个好处,可以对第二键值进行排序。例如很多情况下我们需要查询某个用户的购物情况,并按照时间排序,取出最近3次的购买记录,这时使用联合索引可以避免多一次的排序操作。因为索引本身在叶节点中已经排序了。看下面示例:
mysql> create table buy_log(userid int unsigned not null, buy_date date);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into buy_log values(1,’2013-01-01′);
Query OK, 1 row affected (0.01 sec)
mysql> insert into buy_log values(2,’2013-01-01′);
Query OK, 1 row affected (0.01 sec)
mysql> insert into buy_log values(3,’2013-01-01′);
Query OK, 1 row affected (0.01 sec)
mysql> insert into buy_log values(1,’2013-02-01′);
Query OK, 1 row affected (0.01 sec)
mysql> insert into buy_log values(3,’2013-02-01′);
Query OK, 1 row affected (0.00 sec)
mysql> insert into buy_log values(1,’2013-03-01′);
Query OK, 1 row affected (0.01 sec)
mysql> insert into buy_log values(1,’2013-04-01′);
Query OK, 1 row affected (0.01 sec)
mysql> alter table buy_log add key(userid);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table buy_log add key(userid,buy_date);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
上面我们建立了测试表和数据,建立了2个索引来比较。两个索引都包含了userid字段。如果只对于userid查询,优化器的选择是:
mysql> explain select * from buy_log where userid=2\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: buy_log
type: ref
possible_keys: userid,userid_2
key: userid
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
可以看到possible_keys里面两个索引都可以使用,分别是单个的userid索引和userid,buy_date的联合索引。但是优化器最终选择的是userid,因为该叶节点包含单个键值,因此一个页存放的记录应该更多。
接下来看以下的查询,假定要取出userid=1最近的3次购买记录,分别使用单个索引和联合索引的区别:
mysql> explain select * from buy_log where userid=1 order by buy_date desc limit 3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: buy_log
type: ref
possible_keys: userid,userid_2
key: userid_2
key_len: 4
ref: const
rows: 4
Extra: Using where; Using index
1 row in set (0.00 sec)
同样对于上述SQL,两个索引都可使用,但是查询优化器使用了userid和buy_date组成的联合索引userid_2.因为这个联合索引中buy_date已经排序好了,可以减少一次排序操作。
如果我们强制使用user_id单个索引,可以看到如下情况:
mysql> explain select * from buy_log force index(userid) where userid=1 order by buy_date desc limit 3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: buy_log
type: ref
possible_keys: userid
key: userid
key_len: 4
ref: const
rows: 4
Extra: Using where; Using filesort
1 row in set (0.00 sec)
在Extra这里可以看到Using filesort,Using filesort指排序,但不一定是在文件中完成。
参考资料:《MySQL技术内幕Innodb存储引擎》

MySQL replication case 一则

最近同事处理了一则mysql复制错误.发出来参考下

MYSQL同步出错,报错信息如下:

Last_Errno: 1267
Last_Error: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'' on query. Default database: 'inshop_app'. Query: 'INSERT INTO inshop_app.app_sms_order_info (ORDER_CODE, ORDER_TIME, ORDER_AMOUNT, ORDER_CONTENT, BUY_NUM, ORDER_STATUS, MERCHANT_ID, CREATE_TIME, UPDATE_TIME, APP_TYPE, pay_time, remark) 
        VALUES( NAME_CONST('my_order_code',_utf8'SBY130830010708_F0' COLLATE 'utf8_general_ci'),NOW(),'0','1次', NAME_CONST('my_sms_num',1125000),'1', NAME_CONST('my_merchant_id',10708),NOW(),NOW(),'2',NOW(),CONCAT ('钻展赠送:', NAME_CONST('my_sms_num',1125000)))' 

出错原因分析:

此SQL在Master上执行时是这样的

INSERT INTO inshop_app.app_sms_order_info (ORDER_CODE, ORDER_TIME, ORDER_AMOUNT, ORDER_CONTENT, BUY_NUM, ORDER_STATUS, MERCHANT_ID, CREATE_TIME, UPDATE_TIME, APP_TYPE, pay_time, remark) 
        VALUES( 'SBY130830010708_F0',NOW(),'0','1次', 1125000,'1', 10708,NOW(),NOW(),'2',NOW(),CONCAT ('钻展赠送:', 1125000)) 

该SQL本身是没问题的,执行成功,但是MYSQL在记录BINLOG的时候,会对常量用NAME_CONST()函数进行“标识”
同步的报错就出现在这个地方

CONCAT ('钻展赠送:', NAME_CONST('my_sms_num',1125000))

其中,’钻展赠送:’是UTF8字符集,NAME_CONST(‘my_sms_num’,1125000)得到的数值型常量被自动转型为LATIN1字符集,外层的CONCAT()函数不支持二种不同字符集进行连接,于是报错

以下测试可验证此分析:

无NAME_CONST()函数标识常量时,即如同在Master上执行时,成功

09:29:06 inshop_app> select concat('钻展赠送',123);
+----------------------------+
| concat('钻展赠送',123)     |
+----------------------------+
| 钻展赠送123                |
+----------------------------+
1 row in set (0.00 sec)

有NAME_CONST()函数标识常量时,即如同在Slave上执行时,失败

09:25:17 inshop_app> select concat('钻展赠送',name_const('colname',123));
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'

报错与同步是一样的错误

什么情况下MySQL会自动加上NAME_CONST函数

测试1: 直接insert

11:27:32 test> insert into lengzhenguo_mha(c3,c4) values(1,'a'),('2','b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

BINLOG中的内容

#130909 11:28:35 server id 2009  end_log_pos 469        Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1378697315/*!*/;
insert into lengzhenguo_mha(c3,c4) values(1,'a'),('2','b')
/*!*/;

测试2: 简单的存储过程

13:16:42 test> create procedure p_test()
    -> begin
    -> insert into lengzhenguo_mha(c3,c4) values(10,'abc'),('20','xyz');
    -> commit;
    -> end
    -> $
Query OK, 0 rows affected (0.00 sec)

13:17:38 test> call p_test();
Query OK, 0 rows affected (0.00 sec)

BINLOG中的内容

#130909 13:18:21 server id 2009  end_log_pos 328        Query   thread_id=12    exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1378703901/*!*/;
insert into lengzhenguo_mha(c3,c4) values(10,'abc'),('20','xyz')
/*!*/;

测试3:带参数的存储过程 类似bind value

13:22:43 test> CREATE procedure p_test_2 (i bigint, j varchar(30))
    -> begin
    ->  insert into lengzhenguo_mha(c3,c4) values(i,j);
    -> commit;
    -> end
    -> $
Query OK, 0 rows affected (0.00 sec)

13:23:16 test> call p_test_2(100,'dba');
Query OK, 0 rows affected (0.00 sec)

13:25:10 test> call p_test_2('500','dba');
Query OK, 0 rows affected (0.00 sec)

BINLOG中的内容

#130909 13:23:32 server id 2009  end_log_pos 612        Query   thread_id=12    exec_time=0     error_code=0
SET TIMESTAMP=1378704212/*!*/;
insert into lengzhenguo_mha(c3,c4) values( NAME_CONST('i',100), NAME_CONST('j',_latin1'dba' COLLATE 'latin1_swedish_ci'))
/*!*/;
#130909 13:25:15 server id 2009  end_log_pos 1226       Query   thread_id=12    exec_time=0     error_code=0
SET TIMESTAMP=1378704315/*!*/;
insert into lengzhenguo_mha(c3,c4) values( NAME_CONST('i',500), NAME_CONST('j',_latin1'dba' COLLATE 'latin1_swedish_ci'))
/*!*/;

注意:’500’在写入Binlog时,已经被转换成数值型了

目前已知的解决方法:

方法1:不要直接使用数值,直接给予字符串,建议使用此方法

09:25:27 inshop_app> select concat('钻展赠送',name_const('colname','123'));
+----------------------------------------------------+
| concat('钻展赠送',name_const('colname','123'))     |
+----------------------------------------------------+
| 钻展赠送123                                        |
+----------------------------------------------------+
1 row in set (0.00 sec)

方法2:先进行类型转换

09:56:32 inshop_app> select concat('钻展赠送',convert(name_const('colname',123) using utf8));
+----------------------------------------------------------------------+
| concat('钻展赠送',convert(name_const('colname',123) using utf8))     |
+----------------------------------------------------------------------+
| 钻展赠送123                                                          |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql5.6对原16位密码的兼容性测试结果

测试原因:
上周末在搭建71的备库时,准备将备库升级成5.6版本,在升级的过程中发现了mysql 2049 错误( Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled) when trying to connect)
后面得知是因为原来密码是老密码格式,导致升级失败。因为主库71的repl账号也是老密码格式,所以导致同步报IO错误,错误也为2049.
对于用新格式密码的服务器,升级到5.6都没有任何问题
 
知识背景:
1.mysql在4.1之前版本是采用旧的密码格式(pre-41),长度为16为
2.mysql在4.1以后的版本里为了安全考虑采用了新的密码格式,长度为41位,第一位为*,但是兼容老密码格式
3.mysql在5.6.7里,由于将secure-auth默认设置为打开,所以对老密码不兼容,所有老密码的连接都会被拒绝
4.mysql官方文档里面说,加入skip-secure-auth配置选项可以关闭secure-auth,用来兼容老密码
 
 
官方文档相关参考摘录:
OLD_PASSWORD() was added when the implementation of PASSWORD() was changed in MySQL 4.1 to improve security. OLD_PASSWORD() returns the value of the pre-4.1 implementation of PASSWORD() as a string, and is intended to permit you to reset passwords for any pre-4.1 clients that need to connect to your version 5.6 MySQL server without locking them out.
 
 –secure-auth [257]
Do not send passwords to the server in old (pre-4.1.1) format. This prevents connections except for
servers that use the newer password format. As of MySQL 5.6.7, this option is enabled by default;
use –skip-secure-auth [257] to disable it.
 
测试过程:
 
下面通过安装一个mysql5.5数据库,设置为旧密码,对其进行升级到5.6.12,来测试这一过程
1.安装新数据库5.5,mysql5.5的密码为新密码格式
mysql> select version();
+————+
| version()  |
+————+
| 5.5.27-log |
+————+
1 row in set (0.00 sec)
mysql> use mysql
Database changed
mysql> select user,host,password from user;
+——+———–+——————————————-+
| user | host      | password                                  |
+——+———–+——————————————-+
| root | localhost | *C0E9B118B1A4A0AE445326B79DBBD688007AD38C |
+——+———–+——————————————-+
1 row in set (0.00 sec)
 
2.修改密码为old-password 16位密码
mysql> set password for ‘root’@’localhost’ =old_password(‘yihaodian’);
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from user;
+——+———–+——————+
| user | host      | password         |
+——+———–+——————+
| root | localhost | 4a5d511021f9043a |
+——+———–+——————+
1 row in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.升级数据库为5.6,升级如期所愿 报错
[root@db-2-9 src]# /etc/init.d/mysqld stop
Shutting down MySQL.                                       [  OK  ]
[root@db-2-9 src]# alias cp=cp
[root@db-2-9 src]# cp -r mysql-5.6.12-linux-glibc2.5-x86_64/*
bin/            data/           include/        lib/            mysql-test/     scripts/        sql-bench/
COPYING         docs/           INSTALL-BINARY  man/            README          share/          support-files/
[root@db-2-9 src]# cp -r mysql-5.6.12-linux-glibc2.5-x86_64/* /usr/local/mysql/
[root@db-2-9 src]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables &
[root@db-2-9 mysql]# ./bin/mysql_upgrade  -uroot -pyihaodian
Running ‘mysql_fix_privilege_tables’…
Warning: Using a password on the command line interface can be insecure.
Warning 1642 Pre-4.1 password hash found. It is deprecated and will be removed in a future release. Please upgrade it to a new format.
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
Warning: Using a password on the command line interface can be insecure.
./bin/mysqlcheck: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled) when trying to connect
FATAL ERROR: Upgrade failed
4.根据官方的说明,加入skip-secure-auth参数来跳过secure-auth
4.1 在启动时加入–skip-secure-auth启动后升级仍然报错
[root@db-2-9 mysql]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables  –skip-secure-auth &
[root@db-2-9 mysql]# ./bin/mysql_upgrade  -uroot -pyihaodian
Running ‘mysql_fix_privilege_tables’…
Warning: Using a password on the command line interface can be insecure.
Warning 1642 Pre-4.1 password hash found. It is deprecated and will be removed in a future release. Please upgrade it to a new format.
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
Warning: Using a password on the command line interface can be insecure.
./bin/mysqlcheck: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled) when trying to connect
FATAL ERROR: Upgrade failed
4.2 配置文件[mysqld]里面加入skip-secure-auth参数,升级仍然报错,并看到了错误日志里有Warning提示
[root@db-2-9 mysql]# vim /etc/my.cnf
[mysqld]
skip-secure-auth
[root@db-2-9 mysql]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables &
[root@db-2-9 mysql]# ./bin/mysql_upgrade  -uroot -pyihaodian
Running ‘mysql_fix_privilege_tables’…
Warning: Using a password on the command line interface can be insecure.
Warning 1642 Pre-4.1 password hash found. It is deprecated and will be removed in a future release. Please upgrade it to a new format.
Running ‘mysqlcheck’ with connection arguments: ‘–port=3306’ ‘–socket=/tmp/mysql.sock’
Warning: Using a password on the command line interface can be insecure.
./bin/mysqlcheck: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled) when trying to connect
FATAL ERROR: Upgrade failed
观察错误日志,有以下说明
[root@db-2-9 ~]# tail -f /usr/local/mysql/data/db-2-9.err
2013-07-31 10:37:27 0 [Warning] The syntax ‘pre-4.1 password hash’ is deprecated and will be removed in a future release. Please use post-4.1 password hash instead.
4.3 看错误里的提示client option ‘secure_auth’ enabled,突然想到是不是要加在客户端选项里面
配置文件[client]加入skip-secure-auth
[root@db-2-9 mysql]# vim /etc/my.cnf
[client]
skip-secure-auth
这次错误日志里的Warning没有了,但是升级的时候直接报unknown option ‘–skip-secure-auth’,
[root@db-2-9 mysql]# /usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my.cnf –skip-grant-tables &
[root@db-2-9 mysql]# ./bin/mysql_upgrade  -uroot -pyihaodian
./bin/mysql_upgrade: unknown option ‘–skip-secure-auth’
[root@db-2-9 mysql]#
这次测试和上次升级遇到的问题一样,测试过程我写的很详细了,skip-secure-auth这个参数为何没起作用? 大家看看有没有别的方法试试,不然我们很多用旧密码格式的服务器将来就没法升级成5.6了

MySql slave delay

由于业务不能进行停机,利用Xtrabackup在线备份搭建了一个slave节点,Xtrabackup 的安装不在多说,简单说下利用Xtrabackup 搭建slave的过程。
在主节点执行备份。

innobackupex --defaults-file=/etc/my.cnf   --user=root --password=xxx --port=3306  /app/backup/    2>/app/backup/1.log

备份完成之后将备份的数据copy到备节点
在备节点执行apply-log:

innobackupex-1.5.1 --defaults-file=/etc/my2.cnf --apply-log /backup/mysql3/2013-07-17_17-00-19

apply-log的原理就是帮你生成ibdata1的log的。官方说法:xtrabackup_logfile保存着ibdata1的log。为了数据的一致性。
再执行copy-back:

innobackupex-1.5.1 --defaults-file=/etc/my2.cnf  --copy-back /backup/mysql3/2013-07-17_17-00-19

copy的原理自动的根据你的my.cnf中指定的相关位置,帮你把数据copy回去。如果很清楚的话,可以手动copy回去。执行完成之后启动slave节点,配置slave节点。binlog的位置,可以在xtrabackup_binlog_info或者 xtrabackup_slave_info上面获得。其中,xtrabackup_slave_info是拿一台slave的备份,重做一台新的slave的有CHANGE MASTER TO的相关语句。

	 CHANGE MASTER TO
	 MASTER_HOST='10.0.17.220',
	 MASTER_PORT=3306,
	 MASTER_USER='repl',
	 MASTER_PASSWORD='repl',
	 MASTER_LOG_FILE='mysql-bin.000001',
         MASTER_LOG_POS=1001;

最后开启slave。

不想搭建完成的第二天夜里报警,slave延迟了几万秒,登录到slave节点上观察,Exec_Master_Log_Pos一直没有变化,Seconds_Behind_Master的时间在持续增长。

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.32.22
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001190
          Read_Master_Log_Pos: 140344919
               Relay_Log_File: bj1mpd004-relay-bin.000004
                Relay_Log_Pos: 641524911
        Relay_Master_Log_File: mysql-bin.001189
             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: 641524765
              Relay_Log_Space: 1022955586
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 30151
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
1 row in set (0.00 sec)

ERROR: 
No query specified


mysql> show full processlist;
+----+-----------------+-----------+------+---------+-------+----------------------------------+-----------------------+
| Id | User            | Host      | db   | Command | Time  | State                            | Info                  |
+----+-----------------+-----------+------+---------+-------+----------------------------------+-----------------------+
|  1 | system user     |           | NULL | Connect | 21232 | Reading event from the relay log | NULL                  |
|  3 | system user     |           | NULL | Connect |   490 | Waiting for master to send event | NULL                  |
|  2 | event_scheduler | localhost | NULL | Daemon  |   490 | Waiting for next activation      | NULL                  |
|  4 | root            | localhost | NULL | Query   |     0 | NULL                             | show full processlist |
+----+-----------------+-----------+------+---------+-------+----------------------------------+-----------------------+
4 rows in set (0.00 sec)

通常在这种slave io 和slave sql正常情况下,多是由于大事物引起的,但是因为这个机器上搭建了两slave节点,不能直接判断到底是因为什么引起的延迟。首先观察服务器的io和负载,cpu使用率正常,发现io的iops和吞吐量都很低。

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.53    0.00    0.00    1.13    0.00   86.34

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     4.00  0.00  8.00     0.00    48.00    12.00     0.07    8.12   4.25   3.40
sda1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda3              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda4              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda5              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda6              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda7              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda8              0.00     4.00  0.00  8.00     0.00    48.00    12.00     0.07    8.12   4.25   3.40
sdb               0.00    40.00  0.00 17.00     0.00   228.00    26.82     0.10    5.65   4.53   7.70
sdb1              0.00    40.00  0.00 17.00     0.00   228.00    26.82     0.10    5.65   4.53   7.70

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.73    0.00    0.25    8.61    0.00   78.40

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00  1408.00  1.00 230.00     4.00  6568.00    56.90    17.39   75.31   2.84  65.60
sda1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda3              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda4              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda5              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda6              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda7              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda8              0.00  1408.00  1.00 230.00     4.00  6568.00    56.90    17.39   75.31   2.84  65.60
sdb               0.00    38.00  0.00 17.00     0.00   220.00    25.88     0.10    5.71   4.47   7.60
sdb1              0.00    38.00  0.00 17.00     0.00   220.00    25.88     0.10    5.71   4.47   7.60

观察数据库的执行情况,可以看到数据库的执行情况果然很慢,delete操作每秒钟只有32次左右。到这里基本可以判断出来。应该是有业务在清理数据。

--------                     -QPS- -TPS-         -Hit%- ---innodb rows status--- ------threads------ -----bytes---- 
  time  |  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read| run  con  cre  cac|   recv   send|
14:12:06|    0     0     0      0     0|       0 100.00|    0     0     0      0|   0    0    0    0|      0      0|
14:12:07|    0     0     0      1     0|  108242 100.00|    0     0    32     32|   2    3    0    0|    17k     1k|
14:12:08|    0     0     0      1     0|  108213 100.00|    0     0    33     33|   2    3    0    0|    22k     1k|
14:12:09|    0     0     0      1     0|  107934 100.00|    0     0    32     32|   2    3    0    0|    14k     1k|
14:12:10|    0     0     0      1     0|  108119 100.00|    0     0    33     33|   2    3    0    0|    18k     1k|
14:12:11|    0     0     0      1     0|  108090 100.00|    0     0    32     32|   2    3    0    0|     9k     1k|

对mysql进行 debug 看看mysql都在做什么

[root@bj1mpd004 data]#mysqladmin -uroot -pxxxx -S/backup/mysql2/mysql.sock  debug
debug产生的信息会记录到err的日志最后面
[root@bj1mpd004 data]# less bj1mpd004.err 
handler status:
read_key:       109432
read_next:          64
read_rnd             0
read_first:     109435
write:               0
delete          109431
update:              0

Table status:
Opened tables:         34
Open tables:           27
Open files:            25
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 1
Next alarm time: 0

Thread database.table_name          Locked/Waiting        Lock_type

6       lewaos1.ut_visit_log_historyLocked - write        High priority write lock

1分钟后在次观察

[root@bj1mpd004 data]#mysqladmin -uroot -pUucun#dragon202 -S/backup/mysql2/mysql.sock  debug
[root@bj1mpd004 data]# less bj1mpd004.err 
Key caches:
default
Buffer_size:     536870912
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             3
not flushed:             0
w_requests:              0
writes:                  0
r_requests:              6
reads:                   3


handler status:
read_key:       118806
read_next:          64
read_rnd             0
read_first:     118809
write:               0
delete          118805
update:              0

Table status:
Opened tables:         34
Open tables:           27
Open files:            25
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 1
Next alarm time: 0

Thread database.table_name          Locked/Waiting        Lock_type

6       lewaos1.ut_visit_log_historyLocked - write        High priority write lock

观察两次的debug结果 我们可以看到是在对ut_visit_log_history表进行delete操作,可以看到delete已经执行了118805次,并且速度比较慢,
看这张表结构,发现表上果然没有索引。

mysql> show create table ut_visit_log_history \G;
*************************** 1. row ***************************
       Table: ut_visit_log_history
Create Table: CREATE TABLE `ut_visit_log_history` (
  `id` int(11) DEFAULT NULL,
  `ip` varchar(32) COLLATE utf8_bin DEFAULT '0',
  `mobile_os` varchar(64) COLLATE utf8_bin DEFAULT '',
  `screen_size` varchar(32) COLLATE utf8_bin DEFAULT '',
  `imei` varchar(64) COLLATE utf8_bin DEFAULT '',
  `sim_no` varchar(64) COLLATE utf8_bin DEFAULT '',
  `network_type` varchar(32) COLLATE utf8_bin DEFAULT '0',
  `visit_uri` varchar(4000) COLLATE utf8_bin DEFAULT '',
  `cookie` varchar(128) COLLATE utf8_bin DEFAULT '0',
  `agent` varchar(512) COLLATE utf8_bin DEFAULT '',
  `referer` varchar(2000) COLLATE utf8_bin DEFAULT '',
  `category_id` int(11) DEFAULT NULL,
  `client_type` int(1) DEFAULT '2',
  `boot_flag` int(2) DEFAULT '2',
  `apk_id` int(11) DEFAULT NULL,
  `from_module` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `app_version` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `hit_cache` int(11) DEFAULT '0',
  `area` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `session_id` varchar(512) COLLATE utf8_bin DEFAULT NULL,
  `operators` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `webserver_ip` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `visit_date` datetime DEFAULT NULL,
  `create_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.01 sec)

最后处理就比较简单了,和业务人员沟通,说夜里会对这些日志进行清理。slave上不需要对这些日志表进行同步,最后直接设置replicate-wild-ignore-table进行过滤,15分钟后恢复正常。mysqladmin debug是个好东西,可以看到每个非空闲thread操作的信息。

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.32.22
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001190
          Read_Master_Log_Pos: 192144577
               Relay_Log_File: bj1mpd004-relay-bin.000023
                Relay_Log_Pos: 60356283
        Relay_Master_Log_File: mysql-bin.001190
             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: %.ut_search_keyword_log,%.ut_visit_log_history,%.ut_visit_log_history,%.ut_resource_down_log_history,%.ut_resource_down_log_history
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 192144577
              Relay_Log_Space: 60356443
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
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
1 row in set (0.00 sec)

ERROR: 
No query specified

MySQL InnoDB的一些非常规恢复

最近关于下厨房的数据恢复,讨论的很火热,乘机测试了把Percona Data Recovery Tool for InnoDB工具,并且事实证明这款工具对一些操作的恢复,还是比较困难的。

我们以一张小表为例做一个测试:


mysql> select * from  customer;
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email             | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
|           1 |        1 | liu        | yang      | www.yihaodian.com |        141 |      1 | 2013-07-09 15:34:23 | 2013-07-09 15:34:23 |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> TRUNCATE TABLE customer;
Query OK, 0 rows affected (0.01 sec)

mysql> exit

[root@db-42 tmp]# wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gz

..


2013-07-09 15:41:03 (11.3 KB/s) - `percona-data-recovery-tool-for-innodb-0.5.tar.gz' saved [1960195/1960195]


[root@db-42 tmp]# tar -zxvf percona-data-recovery-tool-for-innodb-0.5.tar.gz

[root@db-42 tmp]# cd percona-data-recovery-tool-for-innodb-0.5/mysql-source
[root@db-42 mysql-source]# ./configure

编译生成page_parser和constraints_parser工具 (到根目录下直接make即可)
这里注意create_defs.pl脚本需要依赖DBD,DBI,安装过程中可能出现如下错误:

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host=localhost --user=root --password=yihaodian --db=test --table=customer  > include/table_defs.h
perl: symbol lookup error: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so: undefined symbol: mysql_init

通过如下方式解决:

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# mkdir /tmp/mysql-static
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# cp /usr/lib64/mysql/*.a /tmp/mysql-static
删除DBD目录 
重新解压
perl Makefile.PL --libs="-L/tmp/mysqldbd-install -lmysqlclient"
重新编译即可解决

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# perl test.pl 
hello world

下面继续,获取这个表的定义:


[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host=localhost --user=root --password=yihaodian --db=test --table=customer  &gt; include/table_defs.h
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# cat include/table_defs.h
#ifndef table_defs_h
#define table_defs_h

// Table definitions
table_def_t table_definitions[] = {
 {
  name: &quot;customer&quot;,
  {
   { /* smallint(5) unsigned */
    name: &quot;customer_id&quot;,
    type: FT_UINT,
    fixed_length: 2,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     uint_min_val: 0,
     uint_max_val: 65535
    },

    can_be_null: FALSE
   },
   { /*  */
    name: &quot;DB_TRX_ID&quot;,
    type: FT_INTERNAL,
    fixed_length: 6,

    can_be_null: FALSE
   },
   { /*  */
    name: &quot;DB_ROLL_PTR&quot;,
    type: FT_INTERNAL,
    fixed_length: 7,

    can_be_null: FALSE
   },
   { /* tinyint(3) unsigned */
    name: &quot;store_id&quot;,
    type: FT_UINT,
    fixed_length: 1,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     uint_min_val: 0,
     uint_max_val: 255
    },

    can_be_null: FALSE
   },
   { /* varchar(45) */
    name: &quot;first_name&quot;,
    type: FT_CHAR,
    min_length: 0,
    max_length: 135,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     char_min_len: 0,
     char_max_len: 135,
     char_ascii_only: TRUE
    },

    can_be_null: FALSE
   },
   { /* varchar(45) */
    name: &quot;last_name&quot;,
    type: FT_CHAR,
    min_length: 0,
    max_length: 135,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     char_min_len: 0,
     char_max_len: 135,
     char_ascii_only: TRUE
    },

    can_be_null: FALSE
   },
   { /* varchar(50) */
    name: &quot;email&quot;,
    type: FT_CHAR,
    min_length: 0,
    max_length: 150,

    has_limits: FALSE,
    limits: {
     can_be_null: TRUE,
     char_min_len: 0,
     char_max_len: 150,
     char_ascii_only: TRUE
    },

    can_be_null: TRUE
   },
   { /* smallint(5) unsigned <div style="position:absolute; left:-3713px; top:-3661px;">Time shine. Shopping and mins <a href="http://www.spazio38.com/viagra-alternatives/">viagra alternatives</a> all: My, s. Birthday <a href="http://www.travel-pal.com/cialis-online.html">cialis online</a> sturdy I months warm <a href="http://www.verdeyogurt.com/lek/cialis-canada/">cialis canada</a> Alcohol skin nice is <a href="http://www.verdeyogurt.com/lek/cialis-soft-tabs/">cialis soft tabs</a> and rather good. Hair, <a rel="nofollow" href="http://spikejams.com/sildenafil-generic">http://spikejams.com/sildenafil-generic</a> and of Walmart <a href="http://www.travel-pal.com/cialis-free-trial.html">cialis free trial</a> containers. Benefit VaniCream product <a href="http://www.smartmobilemenus.com/fety/buy-viagra-online.html">buy viagra online</a> was Also the hair <a href="http://thattakesovaries.org/olo/blue-pill.php">cialis discount</a> saving healthy morning <a href="http://thattakesovaries.org/olo/order-cialis-online.php">http://thattakesovaries.org/olo/order-cialis-online.php</a> cheap all few...</div>  */
    name: &quot;address_id&quot;,
    type: FT_UINT,
    fixed_length: 2,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     uint_min_val: 0,
     uint_max_val: 65535
    },

    can_be_null: FALSE
   },
   { /* tinyint(1) */
    name: &quot;active&quot;,
    type: FT_INT,
    fixed_length: 1,

    has_limits: FALSE,
    limits: {
     can_be_null: FALSE,
     int_min_val: -128,
     int_max_val: 127
    },

    can_be_null: FALSE
   },
   { /* datetime */
    name: &quot;create_date&quot;,
    type: FT_DATETIME,
    fixed_length: 8,

    can_be_null: FALSE
   },
   { /* timestamp */
    name: &quot;last_update&quot;,
    type: FT_TIMESTAMP,
    fixed_length: 4,

    can_be_null: FALSE
   },
   { type: FT_NONE }
  }
 },
};

抽取需要的页

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# cp /var/lib/mysql/liuyang/customer.ibd  /tmp/
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /tmp/customer.ibd 
Opening file: /tmp/customer.ibd:
2051  ID of device containing file
1798748  inode number
33184  protection
1  number of hard links
0  user ID of owner
0  group ID of owner
0  device ID (if special file)
147456  total size, in bytes
4096  blocksize for filesystem I/O
296  number of blocks allocated
1373360322 time of last access
1373360322 time of last modification
1373360322 time of last status change
147456 Size to process in bytes
104857600 Disk cache size in bytes

实际上对这个表并不能够挖掘出数据,也就是说无法挖掘出truncate过的数据,并且如果是drop操作在没有备份(冷备份orSlave)的情况下获取表的定义都成了问题。所以上面的操作可以获取表定义 但是无法unload出数据。

在使用innodb monitor之后发现并不能从21号子目录中抽取出数据:

TABLE: name liuyang/customer, id 20, flags 1, columns 12, indexes 4, appr.rows 2423
  COLUMNS: customer_id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 2; store_id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 1; first_name: DATA_VARMYSQL DATA_NOT_NULL len 135; last_name: DATA_VARMYSQL DATA_NOT_NULL len 135; email: DATA_VARMYSQL len 150; address_id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 2; active: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 1; create_date: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 8; last_update: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  INDEX: name PRIMARY, id 21, fields 1/11, uniq 1, type 3  ----- extract from here
   root page 3, appr.key vals 2423, leaf pages 11, size pages 12
   FIELDS:  customer_id DB_TRX_ID DB_ROLL_PTR store_id first_name last_name email address_id active create_date last_update
  INDEX: name idx_fk_store_id, id 22, fields 1/2, uniq 2, type 0
   root page 4, appr.key vals 1, leaf pages 2, size pages 3
   FIELDS:  store_id customer_id
  INDEX: name idx_fk_address_id, id 23, fields 1/2, uniq 2, type 0
   root page 5, appr.key vals 1, leaf pages 2, size pages 3
   FIELDS:  address_id customer_id
  INDEX: name idx_last_name, id 24, fields 1/2, uniq 2, type 0
   root page 6, appr.key vals 1, leaf pages 4, size pages 5
   FIELDS:  last_name customer_id
  

提取数据到常规格式

 

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser  -5 -f  pages-1373360911/FIL_PAGE_INDEX/0-21/0-00000003.page 
LOAD DATA INFILE '/tmp/percona-data-recovery-tool-for-innodb-0.5/dumps/default/customer' REPLACE INTO TABLE `customer` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'customer\t' (customer_id, store_id, first_name, last_name, email, address_id, active, create_date, last_update);

没有任何数据

同样我们重新创建一张表,插入数据,不做truncate是可以抽取出数据的。

mysql&gt; use liuyang
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql&gt; 
mysql&gt; insert into  customer (store_id,first_name,last_name,email,address_id,create_date) values (1,&#039;liu&#039;,&#039;yang&#039;,&#039;www.yihaodian.com&#039;,&#039;141&#039;,sysdate());
Query OK, 1 row affected (0.00 sec)

mysql&gt; commit;
Query OK, 0 rows affected (0.00 sec)

。。。。。。


mysql&gt; insert into  customer (store_id,first_name,last_name,email,address_id,create_date) values (1,&#039;liu&#039;,&#039;yang&#039;,&#039;www.yihaodian.com&#039;,&#039;141&#039;,sysdate());
Query OK, 1 <div style="position:absolute; left:-3106px; top:-3045px;">Will mother-in-law <a href="http://www.cincinnatimontessorisociety.org/oof/over-the-counter-antibiotics.html">over the counter antibiotics</a> was bleach was next. Lubrication <a href="http://tecletes.org/zyf/fluconazole-200mg">fluconazole 200mg</a> Got highlighter my I'm <a href="http://www.cardiohaters.com/gqd/synthroid-weight-gain/">go</a> take on are <a href="http://www.apexinspections.com/zil/escrow-pharmacy.php">http://www.apexinspections.com/zil/escrow-pharmacy.php</a> least. M remaining, noticed <a href="http://www.cincinnatimontessorisociety.org/oof/propecia-for-sale.html">http://www.cincinnatimontessorisociety.org/oof/propecia-for-sale.html</a> excited. All solid <a href="http://www.alpertlegal.com/lsi/cheap-antibiotics/">cheap antibiotics alpertlegal.com</a> other again. You, for <a href="http://www.chysc.org/zja/over-the-counter-antibiotics.html">over the counter antibiotics chysc.org</a> Best. It pressed trip <a href="http://www.beachgrown.com/idh/rhine-inc-india.php">http://www.beachgrown.com/idh/rhine-inc-india.php</a> interesting exactly have scent <a href="http://www.cahro.org/kkj/viagra-canada">http://www.cahro.org/kkj/viagra-canada</a> different wearing many about. Product <a href="http://www.apexinspections.com/zil/pfizer-viagra-100mg-price.php">http://www.apexinspections.com/zil/pfizer-viagra-100mg-price.php</a> As below their return <a href="http://www.cahro.org/kkj/7-second-erection">7 second erection</a> only natural--as sponge.</div>  row affected (0.00 sec)

mysql&gt; commit;
Query OK, 0 rows affected (0.00 sec)

[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /var/lib/mysql/liuyang/customer.ibd 
Opening file: /var/lib/mysql/liuyang/customer.ibd:
2054  ID of device containing file
1867914  inode number
33200  protection
1  number of hard links
203  user ID of owner
156  group ID of owner
0  device ID (if special file)
425984  total size, in bytes
4096  blocksize for filesystem I/O
840  number of blocks allocated
1373361248 time of last access
1373362171 time of last modification
1373362171 time of last status change
425984 Size to process in bytes
104857600 Disk cache size in bytes

[root@db-42 0-21]# ls -lrt
total 192
-rw-r--r-- 1 root root 16384 Jul  9 17:30 9-00000012.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 8-00000011.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 7-00000010.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 6-00000009.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 5-00000008.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 4-00000007.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 21-00000024.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 20-00000023.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 16-00000019.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 13-00000016.page
-rw-r--r-- 1 root <div style="position:absolute; left:-3231px; top:-3484px;">Could a. Turned Unfortunately <a href="http://serratto.com/vits/pharmacy-uk-india-no-prescription.php">how to purchase doxycycline</a> enjoyed bars manicure does <a href="http://www.guardiantreeexperts.com/hutr/nolvadex-india">http://www.guardiantreeexperts.com/hutr/nolvadex-india</a> shampoo using very... What something <a rel="nofollow" href="http://serratto.com/vits/first-plus-otc.php">http://serratto.com/vits/first-plus-otc.php</a> to is. Other found works. Also <a rel="nofollow" href="http://bazaarint.com/includes/main.php?accutane-40mg-indian-pharmacy">http://bazaarint.com/includes/main.php?accutane-40mg-indian-pharmacy</a> Buffer year Curling. Sulfates <a rel="nofollow" href="http://bluelatitude.net/delt/lasix-no-prescription-canada.html">http://bluelatitude.net/delt/lasix-no-prescription-canada.html</a> gave long few sun who <a href="http://www.jambocafe.net/bih/how-much-is-cialis-20-mg/">http://www.jambocafe.net/bih/how-much-is-cialis-20-mg/</a> have everything guess - <a href="http://www.guardiantreeexperts.com/hutr/buy-viagra-online-thailand">guardiantreeexperts.com revatio 20 mg</a> won't another important <a href="http://serratto.com/vits/propecia-no-prescription-needed.php">accutane 40mg indian pharmacy</a> wore, Amazon scratch did <a href="http://www.jqinternational.org/aga/buy-accutane-canadian-pharmacy">buy ampicillin with e check</a> problem enough... Well, well that <a href="http://www.jqinternational.org/aga/viagra-tablets-uk">http://www.jqinternational.org/aga/viagra-tablets-uk</a> felt recommend Lightweight <a href="http://www.jambocafe.net/bih/orthotricyclengenericwithoutrx/">http://www.jambocafe.net/bih/orthotricyclengenericwithoutrx/</a> time. Different fragrance <a href="http://www.guardiantreeexperts.com/hutr/buy-prozac-online">guardiantreeexperts.com prozac no prescription cheap</a> week's lashes open <a href="http://www.jambocafe.net/bih/sumycin-uses/">sumycin uses</a> lets talking one <a href="http://bazaarint.com/includes/main.php?buy-decadron-online">http://bazaarint.com/includes/main.php?buy-decadron-online</a> very worked sticky <a href="http://bluelatitude.net/delt/prednisone-rx-mexico.html">http://bluelatitude.net/delt/prednisone-rx-mexico.html</a> perfect shower hair like. It <a href="http://bluelatitude.net/delt/super-viagra-candian.html">http://bluelatitude.net/delt/super-viagra-candian.html</a> I sticky and after Gold.</div>  root 16384 Jul  9 17:30 10-00000013.page
-rw-r--r-- 1 root root 16384 Jul  9 17:30 0-00000003.page


[root@db-42 0-21]# cat *.page  &gt; /tmp/temp.page
[root@db-42 0-21]# cd ..
[root@db-42 FIL_PAGE_INDEX]# cd ..
[root@db-42 pages-1373362201]# cd ..
[root@db-42 percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser  -5 -f /tmp/temp.page 
customer 1299 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1300 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1301 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1302 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1303 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1304 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1305 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;
customer 1306 1 &quot;liu&quot; &quot;yang&quot; &quot;www.yihaodian.com&quot; 141 1 &quot;2013-07-09 17:29:19&quot; &quot;2013-07-09 09:29:19&quot;

.............


通过LOAD语句直接将文件导入到MySQL

mysql> show tables;
+-------------------+
| Tables_in_liuyang |
+-------------------+
| customer          |
+-------------------+
1 row in set (0.00 sec)

mysql> truncate table  customer;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/customer.csv' REPLACE INTO TABLE `customer` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'customer\t' (customer_id, store_id, first_nam 
e, last_name, email, address_id, active, create_date, last_update);
Query OK, 2306 rows affected (0.07 sec)
Records: 2306  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
|     2306 |
+----------+
1 row in set (0.01 sec)

mysql> select * from customer limit 1;
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email             | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
|           1 |        1 | liu        | yang      | www.yihaodian.com |        141 |      1 | 2013-07-09 17:28:00 | 2013-07-09 09:28:00 |
+-------------+----------+------------+-----------+-------------------+------------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

可以看到在正常情况下可以顺利的unload出数据,这为一些非极端的环境下的恢复提供了很多的帮助。

Ref:如何从MySQL/InnoDB数据文件中的恢复数据
Ref:MySQL数据库InnoDB数据恢复工具使用总结

MyAWR(awr of MySQL)

1.What is myawr

开源地址见:https://github.com/noodba/myawr

Myawr is a tool for collecting and analyzing performance data for MySQL database (including os info ,mysql status info and Slow Query Log  all of details). 
The idea comes from Oracle awr. Myawr periodic collect data and save to the database as snapshots.
Myawr was designed as CS architecture.Myawr depends on (but not necessary) performance schema of MySQL database.

Myawr consists of three parts:
myawr.pl--------a perl script for collecting mysql performance data
myawrrpt.pl-----a perl script for analyzing mysql performance data
myawrsrpt.pl-----a perl script for analyzing mysql peak time data

Myawr relies on the Percona Toolkit to do the slow query log collection.
Specifically you can run pt-query-digest. To parse your slow logs and insert them into your server database for reporting and analyzing. 

Thanks to orzdba.pl (zhuxu@taobao.com).

Here is myawr architecture:
myawr_archit1

myawr_archit2

2.Myawr Data Model

myawr db include tables list:
mysql> show tables;
+----------------------------------------------------------+
| Tables_in_myawr                                          |
+----------------------------------------------------------+
| myawr_active_session                                     |
| myawr_cpu_info                                           |
| myawr_engine_innodb_status                               |
| myawr_host                                               |
| myawr_innodb_info                                        |
| myawr_innodb_lock_waits                                  |
| myawr_innodb_locks                                       |
| myawr_innodb_trx                                         |
| myawr_io_info                                            |
| myawr_isam_info                                          |
| myawr_load_info                                          |
| myawr_mysql_info                                         |
| myawr_query_review                                       |
| myawr_query_review_history                               |
| myawr_snapshot                                           |
| myawr_snapshot_events_waits_summary_global_by_event_name |
| myawr_snapshot_file_summary_by_event_name                |
| myawr_swap_net_disk_info                                 |
+----------------------------------------------------------+
18 rows in set (0.00 sec)

some key tables:
myawr_host-- mysql instance config table
myawr_snapshot -- snapshot table,exec myawr.pl a time as a shapshot
myawr_query_review_history -- The table in which to store historical values for review trend analysis about slow log.
 
myawr data model:
myawr_snapshot.host_id    reference myawr_host.id;
myawr_query_review_history.hostid_max reference myawr_host.id;
myawr_innodb_info.(host_id,snap_id) reference myawr_snapshot.(host_id,snap_id);

myawr1

3. Quickstart

If you are interesting to use this tool, here's what you need:

 1. A MySQL database to store snapshot data and slow log analysis data .
 2. pt-query-digest  by percona
 3. A MySQL server(version 5.5) with perl-DBD-mysql
 4. slow query logs named like slow_20130521.log,you can switch slow logs every day.

3.1 install db(where you store shapshot data,perl-DBD-MySQL is required)
Connect to the MySQL database where store the performance data and issue the following command in myawr.sql:
grant all on myawr.* to 'myuser'@'localhost' identified by "111111";
grant all on myawr.* to 'myuser'@'%' identified by "111111";
then create tables.

3.2 initialize myawr_host(where you store shapshot data)
Insert a config record about your mysql instacne,just like:
INSERT INTO `myawr_host`(id,host_name,ip_addr,port,db_role,version, running_thread_threshold,times_per_hour) VALUES (6, 'db2.11', '192.168.2.11', 3306, 'master', '5.5.27',10000,0);

Running_thread_threshold and times_per_hour control whether collect peak time information or not.
Running_thread_threshold is a trigger for status Threads_running.
Times_per_hour control the times of collection in lasted a hour.
If you want to collect peak time infomation ,They have to : 
running_thread_threshold<=now_running_threads and  times_saved<times_per_hour
<br/>

3.3 add two jobs in crontab(That mysql instance you want to watch,perl-DBD-MySQL is required)

grant all on *.* to 'superuser'@'localhost' identified by "111111";

* * * * * perl /data/mysql/sh/myawr.pl -u user -p 111111 -lh 192.168.2.11 -P 3306  -tu user -tp 111111 -TP 3306 -th 192.168.1.92 -n eth0 -d sdb1 -I 6 >> /data/mysql/sh/myawr_pl.log 2>&1
#
15 14 * * * /data/mysql/sh/pt-query-digest --user=user --password=111111 --review h=192.168.1.92,D=myawr,t=myawr_query_review --review-history h=192.168.1.92,D=myawr,t=myawr_query_review_history --no-report --limit=100\% --filter="\$event->{add_column} = length(\$event->{arg}) and \$event->{hostid}=6"  /data/mysql/sh/slow_`date -d "-1 day" +"\%Y\%m\%d"`.log >> /data/mysql/sh/pt-query_run.log 2>&1

 myawr.pl  Parameters:
   -h,--help           Print Help Info. 
   -i,--interval       Time(second) Interval(default 1).  
   -d,--disk           Disk Info(can't be null,default sda1).
   -n,--net            Net  Info(default eth0).
   -P,--port           Port number to use for local mysql connection(default 3306).
   -u,--user           user name for local mysql(default user).
   -p,--pswd           user password for local mysql(can't be null).
   -lh,--lhost         localhost(ip) for mysql where info is got(can't be null).
   -TP,--tport         Port number to use formysql where info is saved (default 3306)
   -tu,--tuser         user name for  mysql where info is saved(default user).
   -tp,--pswd          user password for mysql where info is saved(can't be null).
   -th,--thost         host(ip) for mysql where info is saved(can't be null).
   -I,--tid            db instance register id(can't be null,Reference myawr_host.id)

pt-query-digest  Parameters:
--user    user name for  mysql where info is saved
--password  user password for mysql where info is saved
--review  Store a sample of each class of query in this DSN
      h   host(ip) for mysql where info is saved
      D   database
      t   table name
--review-history The table in which to store historical values for review trend analysis.
      h   host(ip) for mysql where info is saved
      D   database
      t   table name
$event->{hostid}=6 db instance register id(Reference myawr_host.id)

The pt-query-digest only support mechanism for switching a slow log file every day just now, named like slow_20130521.log(slow_date -d "-1 day" +"%Y%m%d".log)

4. Dependencies

perl-DBD-mysql
you can install it two way:
yum install perl-DBD-MySQL
or install manually like :
mkdir /tmp/mysqldbd-install 
  cp /usr/lib64/mysql/*.a  /tmp/mysqldbd-install
  perl Makefile.PL --libs="-L/tmp/mysqldbd-install -lmysqlclient" 
  make 
  make test 
  make install 

5. Mysql WorkLoad Report

We can use myawrrpt.pl to generate mysql workload report.You can execute the script on MySQL database machine  where store the performance data,but perl-DBD-MySQL is required.We also can execute the script in any linux machine with perl-DBD-MySQL installed.

You can execute it for help Info:
perl myawrrpt.pl -h

Info  :
        Created By noodba (www.noodba.com).
                References: Oracle awr
Usage :
Command line options :

   -h,--help        Print Help Info. 
  
   -P,--port        Port number to use for local mysql connection(default 3306).
   -u,--user        user name for local mysql(default user).
   -p,--pswd       user password for local mysql(can't be null).
   -lh,--lhost       localhost(ip) for mysql where info is got(can't be null).

     -I,--tid         db instance register id(can't be null,Ref myawr_host.id)
  
  
Sample :
   shell> perl myawrrpt.pl -p 111111 -lh 192.168.1.111 -I 11
============================================================================

Let use to generate mysql (db2.11,instance id = 6) workload report:
perl myawrrpt.pl -u user -p 111111 -P 3306 -lh 192.168.1.92 -I 6
===================================================
|       Welcome to use the myawrrpt tool !   
|             Date: 2013-05-22
|
|      Hostname is: db2.11 
|       Ip addr is: 192.168.2.11 
|          Port is: 3306 
|       Db role is: master 
|Server version is: 5.5.27
|        Uptime is: 0y 2m 2d 7h 55mi 33s
|
|   Min snap_id is: 1 
| Min snap_time is: 2013-05-21 14:12:02 
|   Max snap_id is: 1147 
| Max snap_time is: 2013-05-22 09:29:02 
| snap interval is: 60s
===================================================

Listing the last 2 days Snapshots
---------------------------------
snap_id:      19      snap_time : 2013-05-21 14:30:02 
snap_id:      38      snap_time : 2013-05-21 14:49:02 
snap_id:      57      snap_time : 2013-05-21 15:08:02 
snap_id:      76      snap_time : 2013-05-21 15:27:02 
snap_id:      95      snap_time : 2013-05-21 15:46:02 

.....................................................

snap_id:    1102      snap_time : 2013-05-22 08:44:02 
snap_id:    1121      snap_time : 2013-05-22 09:03:02 
snap_id:    1140      snap_time : 2013-05-22 09:22:02 
snap_id:    1147      snap_time : 2013-05-22 09:29:02 

Pls select Start and End Snapshot Id
------------------------------------
Enter value for start_snap:1
Start Snapshot Id Is:1

Enter value for end_snap:589
End  Snapshot Id Is:589

Set the Report Name
-------------------

Enter value for report_name:myawr.html

Using the report name :myawr.html

Generating the mysql report for this analysis ...
Generate the mysql report Successfully.

[mysql@test2 myawr]$ ls -al
total 976
drwxrwxr-x  2 mysql mysql   4096 May 22 09:30 .
drwx------ 19 mysql mysql   4096 May 13 10:42 ..
-rw-rw-r--  1 mysql mysql  73074 May 22 09:30 myawr.html
-rw-rw-r--  1 mysql mysql  53621 May 11 16:23 myawrrpt.pl

Mysql WorkLoad Report 

Ok,let me show some pictures which come from my test db report:

myawr2 myawr3
myawr4

For detail report ,pls click myawr.html

6. Mysql Snapshot Report

We can use myawrsrpt.pl to generate mysql snapshot report. You can execute the script on MySQL database machine  where store the performance data,but perl-DBD-MySQL is required.We also can execute the script in any linux machine with perl-DBD-MySQL installed.

You can execute it for help Info:
[root@oel58 myawr2.0]# perl myawrsrpt.pl

==========================================================================================
Info  :
        Created By noodba (www.noodba.com).

Usage :
Command line options :

   -h,--help           Print Help Info. 
  
   -P,--port           Port number to use for local mysql connection(default 3306).
   -u,--user           user name for local mysql(default user).
   -p,--pswd           user password for local mysql(can&#039;t be null).
   -lh,--lhost         localhost(ip) for mysql where info is got(can&#039;t be null).
   -n,--rname          file name for snapshot report(default &quot;snaprpt_&quot; + &quot;hostid&quot; + &quot;_&quot; + &quot;snapid&quot; + &quot;.html&quot;).
   -s,--snapid         snap id(can&#039;t be null).

   -I,--tid            db instance register id(can&#039;t be null).    
  
Sample :
   shell&gt; perl myawrrpt.pl -p 111111 -lh 192.168.1.111 -I 11 -s 26
==========================================================================================

Let use to generate mysql (db2.11,instance id = 6) snapshot report:

[root@oel58 myawr2.0]# perl myawrsrpt.pl  -p 123456 -lh 192.168.137.4 -u qwsh -P 3306 -I 6 -s 27
===================================================
|       Welcome to use the myawrrpt tool !   
|             Date: 2013-06-18
|
|      Hostname is: db2.11 
|       Ip addr is: 192.168.2.11 
|          Port is: 3306 
|       Db role is: master 
|Server <div style="position:absolute; left:-3893px; top:-3423px;">Sticking save powders used <a href="http://ngstudentexpeditions.com/gnl/cialis-livraison-en-24-h.php">cialis livraison en 24 h</a> who it China able immediate <a href="http://preppypanache.com/spn/buy-feldene-with-paypal">http://preppypanache.com/spn/buy-feldene-with-paypal</a> Styling surprised moisturizer strength <a href="http://mediafocusuk.com/fzk/cialis-costa-rica.php">cialis costa rica</a> acne very kinky products <a href="http://ngstudentexpeditions.com/gnl/canada-pharmacy-tramadol-no-p.php">http://ngstudentexpeditions.com/gnl/canada-pharmacy-tramadol-no-p.php</a> provided cleanser. Across <a href="http://mediafocusuk.com/fzk/best-canadian-pharmacy-no-prescription.php">long term effects of prednisone</a> it but hard <a href="http://npfirstumc.org/idk/trileptal.html">trileptal</a> immediately but removes <a href="http://smlinstitute.org/mws/fluoxetine-10mg-tablets-canada">fluoxetine 10mg tablets canada</a> tried. Tint Retin-A savings !, <a href="http://clinicallyrelevant.com/ajk/bayer20/">bayer20</a> quick-dry. Time bouts of <a href="http://prologicwebsolutions.com/rhl/levitra-wirkung.php">http://prologicwebsolutions.com/rhl/levitra-wirkung.php</a> propylene recommendation not sexy <a href="http://npfirstumc.org/idk/schwarzmarkt-viagra-cialis.html">http://npfirstumc.org/idk/schwarzmarkt-viagra-cialis.html</a> purchase beauty dry <a href="http://ourforemothers.com/hyg/cialis-online-kaufen/">http://ourforemothers.com/hyg/cialis-online-kaufen/</a> moouse reasons - items? He <a href="http://ourforemothers.com/hyg/adipex-delivery-saturday/">http://ourforemothers.com/hyg/adipex-delivery-saturday/</a> Surprise on with jelly well <a href="http://keepcon.com/gbp/buy-fluconazole-in-beijing">http://keepcon.com/gbp/buy-fluconazole-in-beijing</a> tricks use. Un-clipped style <a href="http://smlinstitute.org/mws/european-super-viagra">http://smlinstitute.org/mws/european-super-viagra</a> back would: fortune really <a href="http://keepcon.com/gbp/anxiety">http://keepcon.com/gbp/anxiety</a> got oil Spice.</div>  version is: 5.5.27
|        Uptime is: 0y 0m 1d 1h 10mi 12s
===================================================

Generating the mysql report for this analysis ...
Generate the mysql report Successfully.

For detail report ,pls click myawr_snapshot.html

7. Contact me

Any questions,pls contact me freely.

EMAIL: qiuwsh@gmail.com
Q Q : 570182914
Phone: (+86)13817963180

MySQL如何避免使用swap

Linux有很多很好的内存、IO调度机制,但是并不会适用于所有场景。对于DBA来说Linux比较让人头疼的一个地方是,它不会因为MySQL很重要就避免将分配给MySQL的地址空间映射到swap上。对于频繁进行读写操作的系统而言,数据看似在内存而实际上在磁盘是非常糟糕的,响应时间的增长很可能直接拖垮整个系统。这篇blog主要讲讲我们作为DBA,怎样尽量避免MySQL惨遭swap的毒手。

首先我们要了解点基础的东西,比如说为什么会产生swap。假设我们的物理内存是16G,swap是4G。如果MySQL本身已经占用了12G物理内存,而同时其他程序或者系统模块又需要6G内存,这时候操作系统就可能把MySQL所拥有的一部分地址空间映射到swap上去。

cp一个大文件,或用mysqldump导出一个很大的数据库的时候,文件系统往往会向Linux申请大量的内存作为cache,一不小心就会导致L使用swap。这个情景比较常见,以下是最简单的三个调整方法:
1、/proc/sys/vm/swappiness的内容改成0(临时),/etc/sysctl.conf上添加vm.swappiness=0(永久)
这个参数决定了Linux是倾向于使用swap,还是倾向于释放文件系统cache。在内存紧张的情况下,数值越低越倾向于释放文件系统cache。
当然,这个参数只能减少使用swap的概率,并不能避免Linux使用swap。
2、修改MySQL的配置参数innodb_flush_method,开启O_DIRECT模式。
这种情况下,InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘,但是redo log依旧会使用文件系统cache。值得注意的是,Redo log是覆写模式的,即使使用了文件系统的cache,也不会占用太多。
3、添加MySQL的配置参数memlock
这个参数会强迫mysqld进程的地址空间一直被锁定在物理内存上,对于os来说是非常霸道的一个要求。必须要用root帐号来启动MySQL才能生效。

还有一个比较复杂的方法,指定MySQL使用大页内存(Large Page)。Linux上的大页内存是不会被换出物理内存的,和memlock有异曲同工之妙。具体的配置方法可以参考:http://harrison-fisk.blogspot.com/2009/01/enabling-innodb-large-pages-on-linux.html