MySQL Deployment slow log collector

Configuring the Yum source CentOS 7 configuration Yum Source

http://mirror.centos.org/centos/7/

http://mirror.centos.org/centos/$releasever/os/$basearch/$releasever $basearch indicate the operating system version and the number of bits, Yum recognizes both variables

Http://mirror.centos.org/centos/7/extras/x86_64/extra Yum Source

Ensure that the/etc/yum.repos.d/directory has no other repo files, and if you can create a Bak directory, back up the Bak directory
Cat/etc/yum.repos.d/centos7.repo

CENTOS6 Configuring the Yum Source

http://mirror.centos.org/centos/6/
Cat/etc/yum.repos.d/centos6.repo

Installing Pt-tools

The Yum source is configured, and the Yum solution relies on
yum localinstall percona-toolkit-2.2.19-1.noarch.rpm#依赖于mysql-lib, be sure to install and mysql-server/mysql-client matching lib

If the Yum is not configured properly, you need to manually install dependencies
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

The dependencies of Perl are:

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.rpm perl-pod-escapes-1.04-144.el6.x86_64. RPM perl-pod-simple-3.13-144.el6.x86_64.rpm perl-module-pluggable-3.90-144.el6.x86_64.rpm perl-version-0.77-144.el6.x86_64.rpm
RPM-IVH percona-toolkit-2.2.19-1.noarch.rpm perl-dbi-1.609-4.el6.x86_64.rpm perl-dbd-mysql-4.013-3.el6.x86_64.rpm perl-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.rpm perl-net-libidn-0.12-3.el6.x86_64.rpm perl-Net-SSLeay-1.35-10.el6_8.1. x86_64.rpm

Install Python2.7.5, use your own Python scripts, install once, copy, install on other servers

tar xvf Python-2.7.5.tar.bz2 cd Python-2.7.5yum install zlib zlib-devel./configure --prefix=/usr/local/python27make && make install

Installing the setuptools,python2.7 installation source package relies on this

unzip setuptools-38.5.2.zip cd setuptools-38.5.2/usr/local/python27/bin/python setup.py  install

Installation markupsafe-1.0,jinja2-2.7.3 depends on this

tar xvf MarkupSafe-1.0.tar.gz cd MarkupSafe-1.0/usr/local/python27/bin/python setup.py  install

Installing JINJA2

tar xvf Jinja2-2.7.3.tar.gz cd ../Jinja2-2.7.3/usr/local/python27/bin/python setup.py  install

Installation DISTRIBUTE-0.6.28,MYSQL-PYTHON-1.2.4B4 depends on this

tar xvf distribute-0.6.28.tar.gz cd /distribute-0.6.28/usr/local/python27/bin/python setup.py  install

3, Installation Mysql-python-1.2.4b4

tar xvf MySQL-python-1.2.4b4.tar.gzcd  MySQL-python-1.2.4b4yum install gcc/usr/local/python27/bin/python setup.py  install

If MySQL is a source installation, you need to create a soft connection for mysql_config
Ln-sv/usr/local/mysql/bin/mysql_config/usr/bin/mysql_config, otherwise the installation MYSQL-PYTHON-1.2.4B4 will error

Creating Slowlog databases and Users

create database slowlog;set global validate_password_policy=‘MEDIUM‘;GRANT ALL PRIVILEGES ON `slowlog`.* TO ‘slowlog‘@‘127.0.0.1‘ identified by ‘xxxxx‘;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;

Deployment scripts

mkdir -p /data/scripts/templatescp top10slowquery.html /data/scripts/templatescp s*.py  /data/scripts/

Test:./slowquery_report.py
See if there is data generated in the database

select count(*) from slowlog.query_history;select count(*) from slowlog.query;select count(*) from slowlog.global_query_review_history;

Test:./send_slowquery_report.py
See if a message is sent

If yes, you can deploy Contab

crontab -e0 1 * * *  /data/scripts/slowquery_report.py0 2 * * *  /data/scripts/send_slowquery_report.py

Error:

unboundlocalerror:local variable ' conn ' referenced before assignment [duplicate]
Python failed to get connection, need to create user and password in data

MySQL Deployment slow log collector