mysql 部署慢日志收集程序
配置yum 源centos 7 配置yum源
http://mirror.centos.org/centos/7/
http://mirror.centos.org/centos/$releasever/os/$basearch/$releasever$basearch 分别表示操作系统版本和位数，yum能识别这两个变量
http://mirror.centos.org/centos/7/extras/x86_64/ extra yum 源
保证/etc/yum.repos.d/目录没有其他的repo文件，如有可以创建一个bak目录，备份bak目录
cat /etc/yum.repos.d/centos7.repo
centos6 配置yum源
http://mirror.centos.org/centos/6/
cat /etc/yum.repos.d/centos6.repo
安装pt-tools
配置好了yum源，通过yum解决依赖
yum localinstall percona-toolkit-2.2.19-1.noarch.rpm #依赖于mysql-lib,一定要安装和mysql-server/mysql-client匹配的lib
如果yum没配置好，需要手动安装依赖
yum install perl-DBI
rpm -ivh MySQL-shared-5.6.26-1.el6.x86_64.rpm
rpm -ivh MySQL-shared-compat-5.6.26-1.el6.x86_64.rpm
perl的依赖有：
perl-DBD-MySQL
perl-IO-Socket-SSL
perl-Net-LibIDN
perl-Net-SSLeay
perl-TermReadKey
perl-Time-HiRes
rpm -Uvh perl-5.10.1-144.el6.x86_64.rpm perl-libs-5.10.1-144.el6.x86_64.rpmperl-Pod-Escapes-1.04-144.el6.x86_64.rpmperl-Pod-Simple-3.13-144.el6.x86_64.rpm perl-Module-Pluggable-3.90-144.el6.x86_64.rpmperl-version-0.77-144.el6.x86_64.rpm
rpm -ivh percona-toolkit-2.2.19-1.noarch.rpmperl-DBI-1.609-4.el6.x86_64.rpm perl-DBD-MySQL-4.013-3.el6.x86_64.rpmperl-IO-Socket-SSL-1.31-3.el6_8.2.noarch.rpm perl-Time-HiRes-1.9721-144.el6.x86_64.rpm perl-TermReadKey-2.30-13.el6.x86_64.rpmperl-Net-LibIDN-0.12-3.el6.x86_64.rpmperl-Net-SSLeay-1.35-10.el6_8.1.x86_64.rpm
安装Python2.7.5，用自己的Python脚本，安装一次，拷贝，在其他的服务器安装
tar xvf Python-2.7.5.tar.bz2
cd Python-2.7.5
yum install zlib zlib-devel
./configure --prefix=/usr/local/python27
make && make install
安装setuptools，Python2.7 安装源码包依赖于此
unzip setuptools-38.5.2.zip
cd setuptools-38.5.2
/usr/local/python27/bin/python setup.pyinstall
安装 MarkupSafe-1.0，Jinja2-2.7.3依赖于此
tar xvf MarkupSafe-1.0.tar.gz
cd MarkupSafe-1.0
/usr/local/python27/bin/python setup.pyinstall
安装 Jinja2
tar xvf Jinja2-2.7.3.tar.gz
cd ../Jinja2-2.7.3
/usr/local/python27/bin/python setup.pyinstall
安装distribute-0.6.28，MySQL-python-1.2.4b4依赖于此
tar xvf distribute-0.6.28.tar.gz
cd /distribute-0.6.28
/usr/local/python27/bin/python setup.pyinstall
3、安装MySQL-python-1.2.4b4
tar xvf MySQL-python-1.2.4b4.tar.gz
cdMySQL-python-1.2.4b4
yum install gcc
/usr/local/python27/bin/python setup.pyinstall
如果mysql是源码安装，需要为mysql_config创建一个软连接
ln -sv /usr/local/mysql/bin/mysql_config/usr/bin/mysql_config，否则安装MySQL-python-1.2.4b4会报错
创建slowlog数据库和用户
create database slowlog;
set global validate_password_policy='MEDIUM';
GRANT ALL PRIVILEGES ON `slowlog`.* TO 'slowlog'@'127.0.0.1'>
set global validate_password_policy='LOW';
use slowlog;
CREATE TABLE slowlog.`global_query_review_history` (
`ts_min` datetime NOT NULL,
`ts_max` datetime NOT NULL,
`ts_cnt` float DEFAULT NULL,
`Query_time_sum` float DEFAULT NULL,
`Query_time_min` float DEFAULT NULL,
`Query_time_max` float DEFAULT NULL,
`Query_time_pct_95` float DEFAULT NULL,
`Lock_time_sum` float DEFAULT NULL,
`Lock_time_min` float DEFAULT NULL,
`Lock_time_max` float DEFAULT NULL,
`Lock_time_pct_95` float DEFAULT NULL,
`sample` text NOT NULL,
`checksum` bigint(20) unsigned NOT NULL,
`tag` varchar(100) DEFAULT NULL,
`ip` varchar(50) DEFAULT NULL,
PRIMARY KEY (`checksum`,`ts_min`,`ts_max`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
部署脚本
mkdir -p /data/scripts/templates
cp top10slowquery.html /data/scripts/templates
cp s*.py/data/scripts/
测试：./slowquery_report.py
查看是数据库中是否有数据产生
select count(*) from slowlog.query_history;
select count(*) from slowlog.query;
select count(*) from slowlog.global_query_review_history;
测试：./send_slowquery_report.py
查看是否有邮件发出
如果有，可以部署contab
crontab -e
0 1 * * */data/scripts/slowquery_report.py
0 2 * * */data/scripts/send_slowquery_report.py
报错：
UnboundLocalError: local variable 'conn' referenced before assignment
Python获取连接失败，需要在数据中创建用户和密码
页:
[1]