查看原文
其他

MySQL 必备工具使用的6个锦囊妙计!

superZS 民工哥技术之路 2018-07-31

点击上方“民工哥Linux运维”关注我,

置顶公众号,精彩内容第一时间呈现。

作者:老张

原文:http://blog.51cto.com/sumongodb/1955184

今儿给大家分享一篇,关于MySQL DBA必备工具的使用。可以方便帮助我们管理我们的数据库,让我们的工作更高效。

这款工具是 MySQL 一个重要分支 percona 的,名称叫做 percona-toolkit(一把锋利的瑞士军刀),它呢是一组命令的集合。今儿给大家介绍几个我们在生产环境中最长用到的。

工具包的下载地址:https://www.percona.com/downloads/percona-toolkit/LATEST/

之前有伙伴在后台问我关于机器学习、算法的公众号,这不,注意啦!推荐一个公众号给大家:机器学习算法与自然语言处理(我关注很久的公众号)

一个哈工大nlp在读博士的技术提升之路,为您提供一系列我在学习路上的笔记,经验以及感悟。自然语言处理领域排名第一,机器学习算法领域排名第一,与君成长!

安装过程很简单,先解压:
tar -zxvf percona-toolkit-3.0.3_x86_64.tar.gz
由于是二进制的包,解压完可以直接进到percona-toolkit-3.0.3/bin目录下使用。

锦囊妙计一:

pt-online-schema-change

功能可以在线整理表结构,收集碎片,给大表添加字段和索引。避免出现锁表导致阻塞读写的操作。针对 MySQL 5.7 版本,就可以不需要使用这个命令,直接在线 online DDL 就可以了。

展现过程如下:
由于是测试环境,就不创建一张数据量特大的表,主要让大家理解这个过程。
这是表里面数据的情况和表结构

mysql> select count(*) from su;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.03 sec)
mysql> desc su;
+-------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type             | Null | Key | Default           | Extra                       |
+-------+------------------+------+-----+-------------------+-----------------------------+
| id    | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| c1    | int(11)          | NO   |     | 0                 |                             |
| c2    | int(11)          | NO   |     | 0                 |                             |
| c3    | int(11)          | NO   |     | 0                 |                             |
| c4    | int(11)          | NO   |     | 0                 |                             |
| c5    | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| c6    | varchar(200)     | NO   |     |                   |                             |

在线增加字段的过程:

[root@node3 bin]# ./pt-online-schema-change --user=root --password=root123
--host=localhost  --alter="ADD COLUMN city_id INT" D=test,t=su --execute
No slaves found.  See --recursion-method if host node3 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
 analyze_table, 10, 1
 copy_rows, 10, 0.25
 create_triggers, 10, 1
 drop_triggers, 10, 1
 swap_tables, 10, 1
 update_foreign_keys, 10, 1
Altering `test`.`su`...
Creating new table...
Created new table test._su_new OK.
Altering new table...
Altered `test`.`_su_new` OK.
2017-08-10T14:53:59 Creating triggers...
2017-08-10T14:53:59 Created triggers OK.
2017-08-10T14:53:59 Copying approximately 100163 rows...
2017-08-10T14:54:00 Copied rows OK.
2017-08-10T14:54:00 Analyzing new table...
2017-08-10T14:54:00 Swapping tables...
2017-08-10T14:54:00 Swapped original and new tables OK.
2017-08-10T14:54:00 Dropping old table...
2017-08-10T14:54:00 Dropped old table `test`.`_su_old` OK.
2017-08-10T14:54:00 Dropping triggers...
2017-08-10T14:54:00 Dropped triggers OK.
Successfully altered `test`.`su`.

查看结果新增了一个 city_id 的字段:

mysql> desc su;
+---------+------------------+------+-----+-------------------+-----------------------------+
| Field   | Type             | Null | Key | Default           | Extra                       |
+---------+------------------+------+-----+-------------------+-----------------------------+
| id      | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| c1      | int(11)          | NO   |     | 0                 |                             |
| c2      | int(11)          | NO   |     | 0                 |                             |
| c3      | int(11)          | NO   |     | 0                 |                             |
| c4      | int(11)          | NO   |     | 0                 |                             |
| c5      | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| c6      | varchar(200)     | NO   |     |                   |                             |
| city_id | int(11)          | YES  |     | NULL              |                             |
+---------+------------------+------+-----+-------------------+-----------------------------+



锦囊妙计二:

pt-query-digest 

功能:现在捕获线上TOP 10 慢 sql 语句。
大家都知道数据库大多数的性能问题是 sql 语句造成的,所以我们要抓住它们这些犯罪分子。及时做相关的优化处理。

展现过程如下:
可以根据时间间隔,来采样慢 sql 语句。since 是可以调整的 sql 语句

[root@node3 bin]# ./pt-query-digest --since=24h /data/mysql/slow.log > 1.log

查看 sql 报告,总结慢语句有哪些,并可以看针对时间的消耗。

如下只是部分报告过程

cat 1.log
# Profile
# Rank Query ID           Response time Calls R/Call  V/M   Item
# ==== ================== ============= ===== ======= ===== ==============
#    1 0x040ADBE3A1EED0A2 16.8901 87.2%     1 16.8901  0.00 CALL insert_su
#    2 0x8E44F4ED46297D4C  1.3013  6.7%     3  0.4338  0.18 INSERT SELECT test._su_new test.su
#    3 0x12E7CAFEA3145EEF  0.7431  3.8%     1  0.7431  0.00 DELETE su
# MISC 0xMISC              0.4434  2.3%     3  0.1478   0.0  <3ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0x040ADBE3A1EED0A2 at byte 19060 ____
# Scores: V/M = 0.00
# Time range: all events occurred at 2017-08-02 12:12:07
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2       1
# Exec time     47     18s     18s     18s     18s     18s       0     18s
# Lock time      0   103us   103us   103us   103us   103us       0   103us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0      21      21      21      21      21       0      21
# String:
# Databases    test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
call insert_su(50000)\G

可以看到报告中,列举出了一些sql语句响应时间占比情况,和sql语句的执行时间情况。方便我们可以很直观的观察哪些语句有问题。(这里只列举了一条sql)

锦囊妙计三:

pt-heartbeat
功能监控主从延迟。监控从库落后主库大概多少时间。
环境介绍:192.168.56.132主库,192.168.56.133从库

操作如下:
在主库上执行:

[root@node3 bin]# ./pt-heartbeat --database test --update
--create-table --daemonize -uroot -proot123

test为我监控同步的库,在该库下创建一张监控表heartbeat,后台进程会时时更新这张表。

在从库上执行监控主从同步延迟时间的语句:
master-server-id是主库的server-id, -h(主库ip)

[root@node4 bin]# ./pt-heartbeat --master-server-id=1323306
--monitor --database test  -uzs -p123456 -h 192.168.56.132
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]
0.00s [  0.00s,  0.00s,  0.00s ]

时间是0s,目前没有延迟的出现。

锦囊妙计四:

pt-table-checksum
功能检查主从复制一致性

原理:在主上执行检查语句去检查 mysql主从复制的一致性,生成 replace 语句,然后通过复制传递到从库,再通过update 更新 master_src 的值。最后通过检测从上 this_src 和 master_src 的
值从而判断复制是否一致。

比较test库的差异情况,在主库上面执行:

[root@node3 bin]# ./pt-table-checksum --no-check-binlog-format --nocheck-replication-filters
--databases=test --replicate=test.checksums --host=192.168.56.132 -uzs -p123456
           TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
08-10T16:01:02      0      0        1       1       0   0.013 test.heartbeat
08-10T16:01:02      0      0        0       1       0   0.015 test.su
08-10T16:01:02      0      0        0       1       0   0.011 test.t

可见diff都为0,证明主从的test库没有差异情况。

比较test库哪些表有差异(需要添加replicate-check-only),在主库上面执行:

[root@node3 bin]# ./pt-table-checksum --no-check-binlog-format
--nocheck-replication-filters --databases=test --replicate=test.checksums  
--replicate-check-only  --host=192.168.56.132 -uzs -p123456
Differences on node4
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
test.t 1 1 1

可见test库下面t这张表主从数据不一致。

锦囊妙计五:

pt-slave-restart
功能:监控主从错误,并尝试重启MySQL主从
注意事项:跳过错误这个命令,解决从库多数据的现象(错误代码1062)。如果从库少数据,还跳过错误,就不能从根儿上解决主从同步的问题了(错误代码1032),就需要先找到缺少的数据是什么了,如果缺少的特别多,建议重新搭建主从环境。

从库出现1062的错误:

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table test.t;
Duplicate entry '1' for key 'PRIMARY',
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
the event's master log mysql-bin.000006, end_log_pos 757482

需要在从库上面执行:

[root@node4 bin]# ./pt-slave-restart -uroot -proot123 --error-numbers=1062
2017-08-10T16:28:12 p=...,u=root node4-relay-bin.000002      751437 1062

跳过错误之后,检查主从结果:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

同步状态又恢复一致了。

锦囊妙计六:

pt-ioprofile
功能:方便定位IO问题,可通过IO吞吐量来定位。

[root@node3 bin]# ./pt-ioprofile
Thu Aug 10 16:33:47 CST 2017
Tracing process ID 3907
    total       read     pwrite      write      fsync filename
13.949355   0.839006   0.000000   0.286556  12.823793 /data/mysql/mysql-bin.000006
 7.454844   0.000000   2.913702   0.000000   4.541142 /data/mysql/ib_logfile0
 0.000193   0.000000   0.000000   0.000193   0.000000 /data/mysql/slow.log

read:从文件中读出数据。要读取的文件用文件描述符标识,数据读入一个事先定义好的缓冲区。
write:把缓冲区的数据写入文件中。
pread:由于lseek和read调用之间,内核可能会临时挂起进程,所以对同步问题造成了问题,调用pread相当于顺序调用了lseek和read,这两个操作相当于一个捆绑的原子操作。
pwrite:由于lseek和write调用之间,内核可能会临时挂起进程,所以对同步问题造成了问题,
调用pwrite相当于顺序调用了lseek 和write,这两个操作相当于一个捆绑的原子操作。
fsync:确保文件所有已修改的内容已经正确同步到硬盘上,该调用会阻塞等待直到设备报告IO完成。
filename:与磁盘交互的文件名称

通过这个报告我们可以看到,哪个文件占用IO的时间比较多,跟磁盘交互最为繁忙,便于锁定IO问题。

因为这个工具集命令很多,今儿先给大家介绍这些比较常用的,其他的一些大家感兴趣可以私下去研究下。
官方地址:https://www.percona.com/doc/percona-toolkit/LATEST/index.html

推荐阅读

[强烈推荐] 精心整理|公众号文章目录大全

民工哥的十年故事续集:杭漂十年,今撤霸都!

吐血推荐的10条建议!让你的薪资上升一个level

Redis 备份、容灾及高可用实战

这20个Docker Command,有几个是你会的?

科普扫盲HTTP Status Code详解,从此排错无忧!

千万级规模高性能、高并发的网络架构经验分享

MySQL 分库分表方案,总结的非常好!

HTTPS科普扫盲,看过的人都恍然大悟!

·end·

—写文不易,你的转发就是对我最大的支持—

我们一起愉快的玩耍吧

超过30000+人正在关注

专注于Linux系统运维、运维架构、各类高可用架构技术,热衷于分享开源技术,热爱开源、拥抱开源,我是80后民工哥,微信公众号:民工哥Linux运维,我为自己带盐,谁说80后不能活出精彩人生!!!

关注公众号点击菜单“微信群组” 入群与小伙伴一起交流吧!

长按关注公众号

热爱开源、拥抱开源

    您可能也对以下帖子感兴趣

    文章有问题?点此查看未经处理的缓存