概述
分享一个alpha版mysql awr,其提供的特性类似于oracle awr或statspack+集中式监控。对于原来从事oracle dba或者相关运维的人原来说,这会是个不错的选择。
下载地址:https://gitee.com/zhjh256/mysqlawr/releases
mysql awr提供的监控信息等价于Oracle AWR/Statspack,几乎比现有所有开源、商业mysql监控工具更为实用和强大。
![88ade257fd7f7f23340373b44cb486a1.png](https://i-blog.csdnimg.cn/blog_migrate/f83ac258a100d453a83ff3b11342d46a.jpeg)
一、部署JDK及tomcat
1、解压jdk
tar zxvf jdk-8u191-linux-x64.tar.gz –C /usr/local/
2、配置环境变量
vi /etc/profile===============================================================================JAVA_HOME=/usr/local/jdkJRE_HOME=$JAVA_HOME/jrePATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/binCLASSPATH=:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib/dt.jarexport JAVA_HOME JRE_HOME PATH CLASSPATHexport PATH
3、生效环境变量
source /etc/profile
4、检查jdk版本
java –version
![1c7d2541bd4031ecc39e21c4246372ca.png](https://i-blog.csdnimg.cn/blog_migrate/6942a403de73275263b0e825e2a70db4.jpeg)
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
![e3f30c7197c58b4ec3f0ebedbaf3c214.png](https://i-blog.csdnimg.cn/blog_migrate/cdbf9233b7ec530c5a2ed7f9593caf90.jpeg)
二、安装mysql数据库
应用和数据库部署在同一台服务器
1、创建基础目录并修改文件夹的属组
mkdir -p /data/datafilemkdir –p /data/logchown -R mysql:mysql /data
![28394be1c36c88afb77ae0a9a94ccfd7.png](https://i-blog.csdnimg.cn/blog_migrate/eb55f0d8f2338cee69069df8f92f6ba6.jpeg)
2、卸载自带包
sudo yum remove -y mariadb*
![315732fb60941d3db019afadeea2ca7d.png](https://i-blog.csdnimg.cn/blog_migrate/cd39b4efb4d85e31a9e6cb4a5271e3d2.jpeg)
3、安装rpm
rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpmrpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpmrpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm
![a900fee8d2b655ab73d64ad9fa1e3488.png](https://i-blog.csdnimg.cn/blog_migrate/6fa920cd6a986fb909ee3ecf912f0234.jpeg)
4、修改数据库my.cnf参数
# vi /etc/my.cnf
[mysqld]datadir=/data/datafilesocket=/var/lib/mysql/mysql.socksymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidlower_case_table_names=1character_set_server=utf8max_connections=2000log-bin = /data/log/mysql-bin.logserver-id = 1log-bin-index =/data/log/binlog.indexlog_bin_trust_function_creators=1sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO
5、启动服务
service mysqld restart
![213ab129d59ca37ba9993dd44fc006f3.png](https://i-blog.csdnimg.cn/blog_migrate/20c63d4f463d7ca46bb6248f1118ba4f.jpeg)
6、修改数据库密码
--查看数据库默认密码# cat /var/log/mysqld.log|grep password--更改密码# mysql –u root -pmysql> set global validate_password_policy=0;mysql> SET PASSWORD = PASSWORD('fswl@1234');
![0990d6ba23391e7c3ec283b5aee32984.png](https://i-blog.csdnimg.cn/blog_migrate/3044d54cc49bfed76ba706adcdfe5475.jpeg)
7、数据库配置
执行war中包含的initsqlperfstat-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 (hostname VARCHAR(64),appname VARCHAR(64),log_time DATETIME,snap_id INT,query_id VARCHAR(32) not null default ' ',total_response_time decimal not null default 0.0,calls bigint not null default 0,abbr_sql VARCHAR(256) not null default ' ',full_sql VARCHAR(1024) not null default ' ',total_rows_examined bigint not null default 0,total_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@'%';
![7ee674eb550d23ab7a870b1e68ebf943.png](https://i-blog.csdnimg.cn/blog_migrate/31b4ebccc32953ea67efdeff4d2aa48d.jpeg)
![2e464b8752ca1dd0b1d027feac45397c.png](https://i-blog.csdnimg.cn/blog_migrate/3396647c7fbd97ba10e82b645248b6fa.jpeg)
![21c1bc35d57ac51ca2cd1430b2450c0f.png](https://i-blog.csdnimg.cn/blog_migrate/a12a63d18ee9dd35d9467f7127477657.jpeg)
![f2dc1d06ff4d59b4fd3c2c28eb59511f.png](https://i-blog.csdnimg.cn/blog_migrate/38fa6720483d094f889eaade91ed9e86.jpeg)
到这里监控端的环境基本就配置好了,后面再介绍下怎么部署mysql awr及配置被监控端,感兴趣的朋友可以关注下~
![c1e98be47d95b9ce51377b74e533cfa2.gif](https://i-blog.csdnimg.cn/blog_migrate/26f9344e5eece7014d807208487e4863.gif)