percona-toolkit是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和
麻烦的mysql和系统任务,这些任务包括:

  • ü 检查master和slave数据的一致性
  • ü 有效地对记录进行归档
  • ü 查找重复的索引
  • ü 对服务器信息进行汇总
  • ü 分析来自日志和tcpdump的查询
  • ü 当系统出问题的时候收集重要的系统信息
    percona-toolkit源自Maatkit和Aspersa工具,这两个工具是管理mysql的最有名的工具,现在Maatkit工具已经不维护了,请大家还是使用percona-toolkit吧!这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类,作为一个优秀的DBA,里面有的工具非常有用,如果能掌握并加以灵活应用,将能极大的提高工作效率。

安装mysql

如果没有安装,请看传送门

如果安装了,服务器启动有问题,请检查配置文件,查看日志。最好的解决方法就是log-error指定的错误日志中记录的信息,或者使用mysqld status命令来查看。

问题解决后,启动的时候可以选择mysqld_safe &,也可以使用mysqld --user=root来启动。最好的方式还是mysqld.server start,但是不一定能成功,错误日志中没有信息,找不出原因。

安装DBD::MySQL

DBD:MySQL是Perl5的MySQL数据库驱动。首先需要安装Perl,macOSX中默认安装了perl,使用命令perl -v可以查看perl的版本,如果没有perl命令,说明可能没有安装perl,需要自己安装perl。

如果安装了perl,接下来安装DBD::MySQL。

1
perl -MCPAN -e "install DBD::mysql"

如果之前没有对CPAN做过配置处理,安装时,会在用户名录下创建一个.cpan的文件下,生成一个配置文件.cpan/CPAN/MyConfig.pm。关于cpan的具体不谈,我们要做的是安装pt。

安装时,会询问:What approach do you want?让你选择local::lib,选项有sudo和manual。这里我们选择sudo即可。
安装时,其一会做test,其二需要把生成的libmysqlclient.dylib放到mysql安装的路径下的lib下,所以需要先安装mysql。

做测试连接时,默认是从/tmp/mysql.sock中与mysql建立socket连接的,但是mac默认的socket路径是/var/lib/mysql/mysql.sock,所以,解决方案就是在/tmp/下建立软连接,或者把在/etc/my.cnf中修改socket的路径为/tmp/mysql.sock

安装DBD::MySQL的时候,建议mysql是正常运行的,这样安装测试时,创建测试账号,执行授权等操作时就可以看出问题。但也可以不启动,最多就是skip过测试。

最后看到:

1
2
3
4
5
6
7
8
9
10
11
12
13
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /Library/Perl/5.18/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bs
Installing /Library/Perl/5.18/darwin-thread-multi-2level/auto/DBD/mysql/mysql.bundle
Installing /Library/Perl/5.18/darwin-thread-multi-2level/Bundle/DBD/mysql.pm
Installing /Library/Perl/5.18/darwin-thread-multi-2level/DBD/mysql.pm
Installing /Library/Perl/5.18/darwin-thread-multi-2level/DBD/mysql/GetInfo.pm
Installing /Library/Perl/5.18/darwin-thread-multi-2level/DBD/mysql/INSTALL.pod
Installing /usr/local/share/man/man3/Bundle::DBD::mysql.3pm
Installing /usr/local/share/man/man3/DBD::mysql.3pm
Installing /usr/local/share/man/man3/DBD::mysql::INSTALL.3pm
Appending installation info to /Library/Perl/Updates/5.18.2/darwin-thread-multi-2level/perllocal.pod
MICHIELB/DBD-mysql-4.041.tar.gz
sudo /usr/bin/make install -- OK

这时候,DBD::MySQL就安装成功了。

安装percona-toolkit

下载percona-toolkit:

  • 1 直接在percona-toolkit官网下载源码

  • 2 使用wget下载:

    1
    wget https://www.percona.com/downloads/percona-toolkit/3.0.1/source/tarball/percona-toolkit-3.0.1.tar.gz

    截止写本文时,最新版本是3.0.1,后续如果版本更新了,大家到官网查看,versionpercona-toolkit-3.0.1,*software*:Source Code.选择Source Code即可看到tar.gz文件。点击可以直接下载,复制链接可以使用wget下载。

mac中建议安装软件路径放在/usr/local/Cellar/下面,比如percona-toolkit可以使用这样的路径:/usr/local/Cellar/percona-toolkit/3.0.1/`,不信你自己看看,mac使用brew安装的软件一定是放在Cellar下面,然后软件名,下一层则是版本号。

所以,建议下载好percona-toolkit后,解压(或者解压后移动)到/usr/local/Cellar/percona-toolkit/3.0.1/下面。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 解压
tar zxvf percona-toolkit-3.0.1.tar.gz
# 移动
mv percona-toolkit-3.0.1/ /usr/local/Cellar/percona-toolkit/3.0.1/
# 进入到安装目录
cd /usr/local/Cellar/percona-toolkit/3.0.1/
# perl编译Makefile.PL文件,生成配置Makefile、MYMETA.yml和MYMETA.json文件,下面是输出信息
# Writing Makefile for percona-toolkit
# Writing MYMETA.yml and MYMETA.json
perl ./Makefile.PL

# make ,执行Makefile文件
make

# make test
make test

# make install
sudo make install

执行make install时需要sudo的权限。执行之后,会看到安装信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
Installing /Library/Perl/5.18/Advisor.pm
Installing /Library/Perl/5.18/AdvisorRules.pm
Installing /Library/Perl/5.18/BinaryLogParser.pm
Installing /Library/Perl/5.18/ChangeHandler.pm
Installing /Library/Perl/5.18/CleanupTask.pm
Installing /Library/Perl/5.18/CompareQueryTimes.pm
Installing /Library/Perl/5.18/CompareResults.pm
Installing /Library/Perl/5.18/CompareWarnings.pm
Installing /Library/Perl/5.18/CopyRowsInsertSelect.pm
Installing /Library/Perl/5.18/Cxn.pm
Installing /Library/Perl/5.18/Daemon.pm
Installing /Library/Perl/5.18/Diskstats.pm
Installing /Library/Perl/5.18/DiskstatsGroupByAll.pm
Installing /Library/Perl/5.18/DiskstatsGroupByDisk.pm
Installing /Library/Perl/5.18/DiskstatsGroupBySample.pm
Installing /Library/Perl/5.18/DiskstatsMenu.pm
Installing /Library/Perl/5.18/DSNParser.pm
Installing /Library/Perl/5.18/DuplicateKeyFinder.pm
Installing /Library/Perl/5.18/EventAggregator.pm
Installing /Library/Perl/5.18/EventExecutor.pm
Installing /Library/Perl/5.18/EventTimeline.pm
Installing /Library/Perl/5.18/ExecutionThrottler.pm
Installing /Library/Perl/5.18/ExplainAnalyzer.pm
Installing /Library/Perl/5.18/FakeSth.pm
Installing /Library/Perl/5.18/FileIterator.pm
Installing /Library/Perl/5.18/FlowControlWaiter.pm
Installing /Library/Perl/5.18/GeneralLogParser.pm
Installing /Library/Perl/5.18/IndexLength.pm
Installing /Library/Perl/5.18/IndexUsage.pm
Installing /Library/Perl/5.18/JSONReportFormatter.pm
Installing /Library/Perl/5.18/KeySize.pm
Installing /Library/Perl/5.18/Lmo.pm
Installing /Library/Perl/5.18/MasterSlave.pm
Installing /Library/Perl/5.18/MockSth.pm
Installing /Library/Perl/5.18/MockSync.pm
Installing /Library/Perl/5.18/MockSyncStream.pm
Installing /Library/Perl/5.18/MySQLConfig.pm
Installing /Library/Perl/5.18/MySQLConfigComparer.pm
Installing /Library/Perl/5.18/MySQLProtocolParser.pm
Installing /Library/Perl/5.18/MySQLStatusWaiter.pm
Installing /Library/Perl/5.18/NibbleIterator.pm
Installing /Library/Perl/5.18/OobNibbleIterator.pm
Installing /Library/Perl/5.18/OptionParser.pm
Installing /Library/Perl/5.18/Outfile.pm
Installing /Library/Perl/5.18/PerconaTest.pm
Installing /Library/Perl/5.18/Pipeline.pm
Installing /Library/Perl/5.18/PodParser.pm
Installing /Library/Perl/5.18/Processlist.pm
Installing /Library/Perl/5.18/Progress.pm
Installing /Library/Perl/5.18/ProtocolParser.pm
Installing /Library/Perl/5.18/QueryHistory.pm
Installing /Library/Perl/5.18/QueryIterator.pm
Installing /Library/Perl/5.18/QueryParser.pm
Installing /Library/Perl/5.18/QueryReportFormatter.pm
Installing /Library/Perl/5.18/QueryReview.pm
Installing /Library/Perl/5.18/QueryRewriter.pm
Installing /Library/Perl/5.18/Quoter.pm
Installing /Library/Perl/5.18/RawLogParser.pm
Installing /Library/Perl/5.18/ReadKeyMini.pm
Installing /Library/Perl/5.18/ReplicaLagWaiter.pm
Installing /Library/Perl/5.18/ReportFormatter.pm
Installing /Library/Perl/5.18/ResultIterator.pm
Installing /Library/Perl/5.18/ResultWriter.pm
Installing /Library/Perl/5.18/Retry.pm
Installing /Library/Perl/5.18/RowChecksum.pm
Installing /Library/Perl/5.18/RowDiff.pm
Installing /Library/Perl/5.18/Runtime.pm
Installing /Library/Perl/5.18/Safeguards.pm
Installing /Library/Perl/5.18/Sandbox.pm
Installing /Library/Perl/5.18/Schema.pm
Installing /Library/Perl/5.18/SchemaIterator.pm
Installing /Library/Perl/5.18/SimpleTCPDumpParser.pm
Installing /Library/Perl/5.18/SlowLogParser.pm
Installing /Library/Perl/5.18/SlowLogWriter.pm
Installing /Library/Perl/5.18/SqlModes.pm
Installing /Library/Perl/5.18/SQLParser.pm
Installing /Library/Perl/5.18/TableChecksum.pm
Installing /Library/Perl/5.18/TableChunker.pm
Installing /Library/Perl/5.18/TableNibbler.pm
Installing /Library/Perl/5.18/TableParser.pm
Installing /Library/Perl/5.18/TableSyncChunk.pm
Installing /Library/Perl/5.18/TableSyncer.pm
Installing /Library/Perl/5.18/TableSyncGroupBy.pm
Installing /Library/Perl/5.18/TableSyncNibble.pm
Installing /Library/Perl/5.18/TableSyncStream.pm
Installing /Library/Perl/5.18/TableUsage.pm
Installing /Library/Perl/5.18/TcpdumpParser.pm
Installing /Library/Perl/5.18/TCPRequestAggregator.pm
Installing /Library/Perl/5.18/TextResultSetParser.pm
Installing /Library/Perl/5.18/Transformers.pm
Installing /Library/Perl/5.18/UpgradeResults.pm
Installing /Library/Perl/5.18/VariableAdvisorRules.pm
Installing /Library/Perl/5.18/VersionCheck.pm
Installing /Library/Perl/5.18/VersionCompare.pm
Installing /Library/Perl/5.18/VersionParser.pm
Installing /Library/Perl/5.18/WeightedAvgRate.pm
Installing /Library/Perl/5.18/bash/alt_cmds.sh
Installing /Library/Perl/5.18/bash/collect.sh
Installing /Library/Perl/5.18/bash/collect_mysql_info.sh
Installing /Library/Perl/5.18/bash/collect_system_info.sh
Installing /Library/Perl/5.18/bash/daemon.sh
Installing /Library/Perl/5.18/bash/log_warn_die.sh
Installing /Library/Perl/5.18/bash/mysql_options.sh
Installing /Library/Perl/5.18/bash/parse_options.sh
Installing /Library/Perl/5.18/bash/report_formatting.sh
Installing /Library/Perl/5.18/bash/report_mysql_info.sh
Installing /Library/Perl/5.18/bash/report_system_info.sh
Installing /Library/Perl/5.18/bash/safeguards.sh
Installing /Library/Perl/5.18/bash/subshell.sh
Installing /Library/Perl/5.18/bash/summary_common.sh
Installing /Library/Perl/5.18/bash/tmpdir.sh
Installing /Library/Perl/5.18/HTTP/Micro.pm
Installing /Library/Perl/5.18/Lmo/Meta.pm
Installing /Library/Perl/5.18/Lmo/Object.pm
Installing /Library/Perl/5.18/Lmo/Role.pm
Installing /Library/Perl/5.18/Lmo/Types.pm
Installing /Library/Perl/5.18/Lmo/Utils.pm
Installing /Library/Perl/5.18/Percona/Test.pm
Installing /Library/Perl/5.18/Percona/Toolkit.pm
Installing /Library/Perl/5.18/Percona/XtraDB/Cluster.pm
Installing /usr/local/share/man/man1/percona-toolkit.1p
Installing /usr/local/share/man/man1/pt-align.1p
Installing /usr/local/share/man/man1/pt-archiver.1p
Installing /usr/local/share/man/man1/pt-config-diff.1p
Installing /usr/local/share/man/man1/pt-deadlock-logger.1p
Installing /usr/local/share/man/man1/pt-diskstats.1p
Installing /usr/local/share/man/man1/pt-duplicate-key-checker.1p
Installing /usr/local/share/man/man1/pt-fifo-split.1p
Installing /usr/local/share/man/man1/pt-find.1p
Installing /usr/local/share/man/man1/pt-fingerprint.1p
Installing /usr/local/share/man/man1/pt-fk-error-logger.1p
Installing /usr/local/share/man/man1/pt-heartbeat.1p
Installing /usr/local/share/man/man1/pt-index-usage.1p
Installing /usr/local/share/man/man1/pt-ioprofile.1p
Installing /usr/local/share/man/man1/pt-kill.1p
Installing /usr/local/share/man/man1/pt-mext.1p
Installing /usr/local/share/man/man1/pt-mysql-summary.1p
Installing /usr/local/share/man/man1/pt-online-schema-change.1p
Installing /usr/local/share/man/man1/pt-pmp.1p
Installing /usr/local/share/man/man1/pt-query-digest.1p
Installing /usr/local/share/man/man1/pt-show-grants.1p
Installing /usr/local/share/man/man1/pt-sift.1p
Installing /usr/local/share/man/man1/pt-slave-delay.1p
Installing /usr/local/share/man/man1/pt-slave-find.1p
Installing /usr/local/share/man/man1/pt-slave-restart.1p
Installing /usr/local/share/man/man1/pt-stalk.1p
Installing /usr/local/share/man/man1/pt-summary.1p
Installing /usr/local/share/man/man1/pt-table-checksum.1p
Installing /usr/local/share/man/man1/pt-table-sync.1p
Installing /usr/local/share/man/man1/pt-table-usage.1p
Installing /usr/local/share/man/man1/pt-upgrade.1p
Installing /usr/local/share/man/man1/pt-variable-advisor.1p
Installing /usr/local/share/man/man1/pt-visual-explain.1p
Installing /usr/local/bin/pt-align
Installing /usr/local/bin/pt-archiver
Installing /usr/local/bin/pt-config-diff
Installing /usr/local/bin/pt-deadlock-logger
Installing /usr/local/bin/pt-diskstats
Installing /usr/local/bin/pt-duplicate-key-checker
Installing /usr/local/bin/pt-fifo-split
Installing /usr/local/bin/pt-find
Installing /usr/local/bin/pt-fingerprint
Installing /usr/local/bin/pt-fk-error-logger
Installing /usr/local/bin/pt-heartbeat
Installing /usr/local/bin/pt-index-usage
Installing /usr/local/bin/pt-ioprofile
Installing /usr/local/bin/pt-kill
Installing /usr/local/bin/pt-mext
Installing /usr/local/bin/pt-mysql-summary
Installing /usr/local/bin/pt-online-schema-change
Installing /usr/local/bin/pt-pmp
Installing /usr/local/bin/pt-query-digest
Installing /usr/local/bin/pt-show-grants
Installing /usr/local/bin/pt-sift
Installing /usr/local/bin/pt-slave-delay
Installing /usr/local/bin/pt-slave-find
Installing /usr/local/bin/pt-slave-restart
Installing /usr/local/bin/pt-stalk
Installing /usr/local/bin/pt-summary
Installing /usr/local/bin/pt-table-checksum
Installing /usr/local/bin/pt-table-sync
Installing /usr/local/bin/pt-table-usage
Installing /usr/local/bin/pt-upgrade
Installing /usr/local/bin/pt-variable-advisor
Installing /usr/local/bin/pt-visual-explain
Appending installation info to /Library/Perl/Updates/5.18.2/darwin-thread-multi-2level/perllocal.pod

安装Term::ReadKey

基本上所有Linux系统的安装文档中都在强调Term::ReadKey,在mac中,似乎不安装这一部分也不会有影响。

需要安装Term::ReadKey 包,否则会报perl(Term::ReadKey)>=2.10 is needed by percona-toolkit-2.1.1-1.noarch错误

如果真的出现如大家所遇到的问题,那么安装就一下也无妨。
在命令窗口输入cpan命令回车,出现cpan的控制台。

1
install Term::ReadKey

这样安装即可,然后再安装percona-toolkit。

命令说明

传送门