分享一个alpha版mysql awr，其提供的特性类似于oracle awr或statspack+集中式监控。对于原来从事oracle dba或者相关运维的人原来说，这会是个不错的选择。
下载地址：https://gitee.com/zhjh256/mysqlawr/releases
mysql awr提供的监控信息等价于Oracle AWR/Statspack，几乎比现有所有开源、商业mysql监控工具更为实用和强大。
1、解压jdk
tar zxvf jdk-8u191-linux-x64.tar.gz –C /usr/local/
2、配置环境变量
vi /etc/profile
===============================================================================
JAVA_HOME=/usr/local/jdk
JRE_HOME=$JAVA_HOME/jre
PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
CLASSPATH=:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib/dt.jar
export JAVA_HOME JRE_HOME PATH CLASSPATH
export PATH
3、生效环境变量
source /etc/profile
4、检查jdk版本
java –version
5、解压 tomcat
tar -xvf apache-tomcat-8.5.37.tar.gz -C /home/
mv /home/apache-tomcat-8.5.37/ /home/tomcat80
6、修改配置文件
vi /home/tomcat80/conf/server.xml
应用和数据库部署在同一台服务器
1、创建基础目录并修改文件夹的属组
mkdir -p /data/datafile
mkdir –p /data/log
chown -R mysql:mysql /data
2、卸载自带包
sudo yum remove -y mariadb*
3、安装rpm
rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm
4、修改数据库my.cnf参数
# vi /etc/my.cnf
[mysqld]
datadir=/data/datafile
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names=1
character_set_server=utf8
max_connections=2000
log-bin = /data/log/mysql-bin.log
server-id = 1
log-bin-index =/data/log/binlog.index
log_bin_trust_function_creators=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO
5、启动服务
service mysqld restart
6、修改数据库密码
--查看数据库默认密码
# cat /var/log/mysqld.log|grep password
--更改密码
# mysql –u root -p
mysql> set global validate_password_policy=0;
mysql> SET PASSWORD = PASSWORD('fswl@1234');
7、数据库配置
执行war中包含的\\initsql\\perfstat-db-init.sql
CREATE database perf_stat default character set gbk;
use perf_stat;
CREATE table apps (
hostname varchar(64),
port int,
map_port int,
appname varchar(64) primary key,
ver varchar(64),
mysql_username varchar(32),
mysql_password varchar(64),
ssh_username varchar(32),
ssh_password varchar(64),
ssh_port int,
snap_interval int);
CREATE table app_snaps (hostname varchar(64),appname varchar(64),snap_id int,log_time datetime,exec_result_info varchar(256)); -- ±£´7ͬؔ¶¯ɾ³
CREATE index idx_app_snaps_appname on app_snaps(appname);
CREATE table is_global_status(hostname varchar(64),appname varchar(64),log_time datetime,snap_id int,
variable_name varchar(64),
variable_value varchar(64)); -- Ǵ¶¯Ӕ4ȫ
CREATE index idx_global_status_app_snap on is_global_status(appname,snap_id);
CREATE index idx_global_status_variable_name on is_global_status(variable_name,appname,snap_id);
CREATE index idx_global_status_log_time on is_global_status(log_time);
-- mysql½춄cpuʱ¼哔¼°ϯmŚ´浦´¢´洢Ϊ±偿Ļcpu_time, memory_used¡£
CREATE table is_global_variables(hostname varchar(64),appname varchar(64),log_time datetime,snap_id int,
variable_name varchar(64),
variable_value varchar(64)); -- Ǵ¶¯Ӕ4ȫ
CREATE index idx_global_VARIABLES_app_snap on is_global_variables(appname,snap_id);
CREATE index idx_global_VARIABLES_variable_name on is_global_variables(variable_name,appname,snap_id);
CREATE index idx_global_VARIABLES_log_time on is_global_variables(log_time);
CREATE table ps_events_waits_summary_global_by_event_name
(
hostname varchar(64),appname varchar(64),log_time datetime,snap_id int,
`EVENT_NAME` varchar(128) NOT NULL,
`COUNT_STAR` bigint(20) unsigned NOT NULL,
`SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,
`MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,
`AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,
`MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL
);
CREATE index idx_ewsgben_app_snap on ps_events_waits_summary_global_by_event_name(appname,snap_id);
....
CREATE table slow_query_log_digest (
\thostname VARCHAR(64),appname VARCHAR(64),log_time DATETIME,snap_id INT,
\tquery_id VARCHAR(32) not null default ' ',
\ttotal_response_time decimal not null default 0.0,
\tcalls bigint not null default 0,
\tabbr_sql VARCHAR(256) not null default ' ',
\tfull_sql VARCHAR(1024) not null default ' ',
\ttotal_rows_examined bigint not null default 0,
\ttotal_rows_effected bigint not null default 0);
CREATE INDEX idx_slow_query_log_digest_app_snap ON slow_query_log_digest(appname,snap_id);
CREATE INDEX idx_slow_query_log_digest_log_time ON slow_query_log_digest(log_time);
CREATE USER perf_stat@'%' IDENTIFIED BY 'perf_stat123';
GRANT ALL ON *.* TO perf_stat@'%' ;
grant super on *.* to perf_stat@'%';
到这里监控端的环境基本就配置好了，后面再介绍下怎么部署mysql awr及配置被监控端，感兴趣的朋友可以关注下~