pt-query-digest的一个问题


昨天有同学问,用pt-query-digest分析后,发现排在最前面都是ADMIN开头的语句,真正的语句却没有真实反映在报表中

$ sudo pt-query-digest --type genlog /tmp/general.log  --order-by Query_time:cnt --limit 3
 
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== =============
#    1 0x440061F2EB7A7639  0.0000  0.0% 53390 0.0000  0.00 ADMIN CLOSE
#    2 0x99AA0165670CE848  0.0000  0.0% 53389 0.0000  0.00 ADMIN PREPARE
#    3 0x1228F184663C8627  0.0000  0.0% 53386 0.0000  0.00 ADMIN EXECUTE
# MISC 0xMISC              0.0000  0.0%  5042 0.0000   0.0 <42 ITEMS>

首先要知道这些语句是因为JAVA使用preparestatment引起,打开general.log发现类似语句:

11637701 Prepare select * from t  
 11637701 Execute select * from t
 11637701 Close stmt

pt-query-digest没有很好处理prepare及execute后面语句,导致真正的sql没有展示出来。
想到把Execute改成Query,把其余的ADMIN过滤掉,那就是一份正确的报告了。

$sed -i 's/Execute/Query/g' /tmp/general.log 
$ sudo pt-query-digest --type genlog /tmp/general.log --filter '$event->{fingerprint} !~ m/^admin/i' --order-by Query_time:cnt --limit 3
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x2DAF846CC781D91A  0.0000  0.0% 14855 0.0000  0.00 SELECT t?
#    2 0x7164FF6E647C5863  0.0000  0.0% 11604 0.0000  0.00 SELECT t2?
#    3 0x6576B004C40147B5  0.0000  0.0%  4582 0.0000  0.00 SELECT t3

可以看到真正的sql出来

细心同学会发现ADMIN CLOSE、ADMIN PREPARE、ADMIN EXECUTE的值几乎都是一样,理论上应该是相同。

mysql分区表和普通表关联查询异常


发现线上有数据库load异常,查看show processlist,同一条sql都是在copy tmp,以前执行几十个ms的,现在基本都是几十s,怀疑执行计划有问题
1
可以看到a表(按月分表的分区表)进行全表扫描,a表记录在上千万,b表记录就几千。看一下a、b表的索引情况
1
当初为了快速解决问题,把join sql拆成两条sql,问题解决。今天想追查一下问题,发现一样的sql,执行计划居然正常了
1

目前没有找到根本原因,先记录一下,莫非和统计信息收集有关或者是bug?

类似bug:Bug #69013

recover table from full backup(二)


1、目标要为 XtraDB
2、 innodb_file_per_table=on
3、目标库innodb_expand_import(innodb_import_table_from_xtrabackup较新版本)开启

背景:

root@luoxuan >desc export_test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
root@luoxuan >insert into export_test values(1);
Query OK, 1 row affected (0.00 sec)

instance 1:
一、原db备份

$ sudo ./innobackupex --defaults-file=/u01/mysql/my.cnf --user=root /u01/bak2

二、prepare

$ sudo ./xtrabackup --prepare --export --target-dir=/u01/bak2/2013-03-27_11-33-39

三、在instance 2上创建和原表一样的表结构,并discard掉

root@luoxuan >CREATE TABLE export_test (
    ->   a int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
 
root@luoxuan > ALTER TABLE luoxuan.export_test DISCARD TABLESPACE;

四、把prepare后的文件夹中找到export_test.ibd、export_test.exp并cp instance 2的数据目标
五、在instance 2 上import

root@(none) >ALTER TABLE luoxuan.export_test IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
 
root@(none) >select * from luoxuan.export_test;
+------+
| a    |
+------+
|    1 |
+------+

六、得到备份时binlog位置

$ more xtrabackup_binlog_info 
mysql-bin.002520        6244

同时在instance 1写入一条记录insert into luoxuan.export_test values(2)

七、解析binlog,当然可以有stop-position。自己开发脚本对binlog中只抽取export_test相关的DML

sudo mysqlbinlog --no-defaults -v -v  /u01/mysql/log/mysql-bin.002604 --start-position=6244 > /tmp/recover.sql

八、在instance 2上使用解析后的binlog,可以恢复到

root@luoxuan 04:16:30>select * from luoxuan.export_test;
+------+
| a    |
+------+
|    2 |
|    1 |
+------+

九、instance 1上的数据

root@luoxuan 04:17:10>select * from luoxuan.export_test;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+

recover table from full backup(一)


前提:
1、innodb_fast_shutdown = 0
2、You must not drop, truncate or alter the schema of the table after the backup has been taken.
3、The variable innodb_file_per_table must be enabled.

背景:

root@luoxuan 05:14:25>select * from lijie;
+------+
| b    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
 
root@luoxuan 05:14:29>select * from test_recover;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+

一、获得一致性备份:

session 1: sudo ./innobackupex --defaults-file=/u01/mysql/my.cnf --user=root /u01/bak2 --slave-info --export 2> /tmp/xtrabackup.log

session 2:备份过程进行数据写入

root@luoxuan 05:17:23>insert into lijie values(3);
Query OK, 1 row affected (0.00 sec)
 
root@luoxuan 05:17:27>insert into test_recover values(4);     
Query OK, 1 row affected (0.00 sec)

session 1:

sudo ./innobackupex --defaults-file=/u01/mysql/my.cnf --apply-log --export /u01/bak2/2013-03-26_17-16-55

session 2:获得一致性过程中写入数据

root@luoxuan 05:17:36>insert into lijie values(4);
Query OK, 1 row affected (0.61 sec)
 
root@luoxuan 05:20:43>insert into test_recover values(5);
Query OK, 1 row affected (0.00 sec)

二、删除需单独恢复表的数据

root@luoxuan 05:20:57>select * from lijie;
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)
 
root@luoxuan 05:21:40>select * from test_recover;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)
 
root@luoxuan 05:21:45>delete from test_recover where a = 1;
Query OK, 1 row affected (0.00 sec)

三、
discard需要恢复表

mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE test_recover DISCARD TABLESPACE;

四、把apply-log后的数据文件cp回/u01/mysql/data/库名/

$ sudo cp ./luoxuan/test_recover.ibd /u01/mysql/data/luoxuan

记得修改权限

五、import需要恢复的表

mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE test_recover IMPORT TABLESPACE;
mysql> set FOREIGN_KEY_CHECKS=1;
 
root@luoxuan 05:25:24>select * from test_recover;(正确)
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)
 
root@luoxuan 05:25:45>select * from lijie;  记录应该(1,2,3,4,5)
+------+
| b    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

六、拿到备份时的binlog file及position

$ more xtrabackup_binlog_info 
mysql-bin.002603        12634

七、把binlog解析后发现,postion位置也不对

http://www.mysqlperformanceblog.com/2012/01/25/how-to-recover-a-single-innodb-table-from-a-full-backup/
这篇文章只针对在恢复过程没有写入情况下,恢复出来数据正确,能单独恢复一个表

mysqlserverclone&mysqluserclone&mysqlrpladmin


A、mysqlserverclone对已经存在instance clone一个

mysqlserverinfo 展示mysql server参数情况

$ ./mysqlserverinfo --server=root@localhost:3306 --format=vertical -d
# Source on localhost: ... connected.
*************************       1. row *************************
         server: localhost:3306
        version: 5.5.18-log
        datadir: /u01/mysql/data/
        basedir: /u01/mysql
     plugin_dir: /u01/mysql/lib/plugin
    config_file: /u01/mysql/my.cnf
     binary_log: mysql-bin.002592
 binary_log_pos: 360024544
      relay_log: None
  relay_log_pos: None
1 rows.
 
Defaults for server localhost:3306
#...done.

B、mysqluserclone 创建一个和已有帐户相同的帐户,在percona版本中跑不了

$ ./mysqluserclone --source=root@localhost --destination=root@localhost luoxuan@localhost aa:aa@localhost
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
ERROR: Base user does not exist!

C、mysqlrpladmin工具主要功能类似mysqlfailover,这边不再展开

D、mysqluc集中控制台