1,os version
redhat 6.5
2,startup mysql 5.7
[root@mysqlupgrade ~]# ps -ef|grep mysql
root 1292 1 0 01:36 ? 00:00:00 bin/sh usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 1492 1292 0 01:36 ? 00:00:01 usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root 2336 1614 0 02:18 pts/0 00:00:00 grep mysql
3,login in mysql 5.7.16
[root@mysqlupgrade ~]# mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> quit
Bye
[root@mysqlupgrade ~]#
4,pt-mysql-summary工具的用法
[root@mysqlupgrade ~]# pt-mysql-summary --help
Usage: pt-mysql-summary [OPTIONS]
For more information, 'man pt-mysql-summary' or 'perldoc usr/bin/pt-mysql-summary'.
Command line options:
--all-databases mysqldump and summarize all databases.
--ask-pass Prompt for a password when connecting to MySQL.
--config Read this comma-separated list of config files.
--databases mysqldump and summarize this comma-separated list of
databases.
--defaults-file Only read mysql options from the given file.
--help Print help and exit.
--host Host to connect to.
--password Password to use when connecting.
--port Port number to use for connection.
--read-samples Create a report from the files found in this directory.
--save-samples Save the data files used to generate the summary in this
directory.
--sleep Seconds to sleep when gathering status counters.
--socket Socket file to use for connection.
--user User for login if not current user.
--version Print tool's version and exit.
Options and values after processing arguments:
--all-databases FALSE
--ask-pass FALSE
--config (No value)
--databases (No value)
--defaults-file (No value)
--help TRUE
--host (No value)
--password (No value)
--port (No value)
--read-samples (No value)
--save-samples (No value)
--sleep 10
--socket (No value)
--user (No value)
--version FALSE
[root@mysqlupgrade ~]#
5，pt-mysql-summary的用法
[root@mysqlupgrade ~]# pt-mysql-summary --user root --password system
---告诉密码不要用明文，不安全（时间好像是西方时间，不舒服）
mysql: [Warning] Using a password on the command line interface can be insecure.
# Percona Toolkit MySQL Summary Report #######################
System time | 2018-05-04 18:29:30 UTC (local TZ: CST +0800)
---MYSQL实例的数据文件目录
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
mysql_upgrade_dir 0 0 /var/lib/mysql/mysql.sock
--MYSQL的执行文件
# MySQL Executable ###########################################
Path to executable | /usr/sbin/mysqld
Has symbols | Yes
---MYSQL备库检查
# Slave Hosts ################################################
No slaves found
--MYSQL端口
（检查MYSQL数据库的配置运行信息）
# Report On Port 3306 ########################################
User | root@localhost
Time | 2018-05-05 02:29:30 (CST)
Hostname | mysqlupgrade
Version | 5.7.16 MySQL Community Server (GPL)
Built On | Linux x86_64
Started | 2018-05-05 02:23 (up 0+00:06:08)
Databases | 5
Datadir | /mysql_upgrade_dir/
Processes | 1 connected, 1 running
Replication | Is not a slave, has 0 slaves connected
Pidfile | /var/run/mysqld/mysqld.pid (exists)
--MYSQL Processlist信息
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Query 1 1 0 0
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
root 1 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
localhost 1 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
NULL 1 1 0 0
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
starting 1 1 0 0
---如下显示MYSQL状态变量值
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 11 secs
Aborted_connects 700
Bytes_received 400000 4 450
Bytes_sent 6000000 80 2250
Com_select 1250 2
Com_show_status 450
Com_show_variables 225
Connections 2250 1
Created_tmp_files 1500
Created_tmp_tables 700 5
Flush_commands 225
Handler_commit 1250
Handler_external_lock 50000
Handler_read_first 2000
Handler_read_key 1500
Handler_read_next 225
Handler_read_rnd_next 400000 4 70
Handler_write 200000 2 35
Innodb_buffer_pool_bytes_data 1250000000 15000 3000
Innodb_buffer_pool_pages_flushed 8000
Innodb_buffer_pool_read_requests 600000 6 25
Innodb_buffer_pool_reads 70000
Innodb_buffer_pool_write_requests 80000 20
Innodb_data_fsyncs 1750
Innodb_data_read 1000000000 12500
Innodb_data_reads 70000
Innodb_data_writes 12500
Innodb_data_written 150000000 1750 6000
Innodb_dblwr_pages_written 450
Innodb_dblwr_writes 225
Innodb_log_writes 450
Innodb_os_log_fsyncs 900
Innodb_os_log_written 250000 2
Innodb_pages_created 8000
Innodb_pages_read 70000
Innodb_pages_written 8000
Innodb_rows_read 2000 4
Innodb_num_open_files 5000
Innodb_available_undo_logs 30000
Key_read_requests 1500
Key_reads 700
Open_table_definitions 25000
Opened_files 30000
Opened_table_definitions 25000
Opened_tables 25000
Qcache_not_cached 1250 2
Queries 3000 4
Questions 3000 4
Select_scan 1500
Table_locks_immediate 22500
Table_open_cache_hits 700
Table_open_cache_misses 25000
Threads_created 225
Uptime 90000 1 1
--MYSQL的TABLE CACHE配置
# Table cache ################################################
Size | 2000
Usage | 5%
---主要的PERCONA服务器配置信息（由于我们是MYSQL SERVER，所以显示为不支持）
# Key Percona Server features ################################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Disabled
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
---检查PERCONA XTRADB CLUSTER集群配置
# Percona XtraDB Cluster #####################################
--检查插件
# Plugins ####################################################
InnoDB compression | ACTIVE
---查询缓冲
# Query cache ################################################
query_cache_type | OFF
Size | 1.0M
Usage | 1%
HitToInsertRatio | 0%
--MYSQL的数据库
# Schema #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
SSL | No
Explicit LOCK TABLES | No
Delayed Insert | No
XA Transactions | No
NDB Cluster | No
Prepared Statements | No
Prepared statement count | 0
--INNODB的配置，比如缓冲池配置组件
# InnoDB #####################################################
Version | 5.7.16
Buffer Pool Size | 128.0M
Buffer Pool Fill | 3%
Buffer Pool Dirty | 0%
File Per Table | ON
Page Size | 16k
Log File Size | 2 * 48.0M = 96.0M
Log Buffer Size | 16M
Flush Method |
Flush Log At Commit | 1
XA Support | ON
Checksums | ON
Doublewrite | ON
R/W I/O Threads | 4 4
I/O Capacity | 200
Thread Concurrency | 0
Concurrency Tickets | 5000
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
Adaptive Flushing | ON
Adaptive Checkpoint |
Checkpoint Age | 9
InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
History List Len | 53
Read Views | 0
Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 1xnot started
--MYISAM存储引擎
# MyISAM #####################################################
Key Cache | 8.0M
Pct Used | 20%
Unflushed | 0%
--安全配置
# Security ###################################################
Users | 3 users, 0 anon, 0 w/o pw, 0 old pw
Old Passwords | 0
---加密配置
# Encryption #################################################
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
No keyring plugins found
--二进制日志记录
# Binary Logging #############################################
# Noteworthy Variables #######################################
Auto-Inc Incr/Offset | 1/1
default_storage_engine | InnoDB
flush_time | 0
init_connect |
init_file |
sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
join_buffer_size | 256k
sort_buffer_size | 256k
read_buffer_size | 128k
read_rnd_buffer_size | 256k
bulk_insert_buffer | 0.00
max_heap_table_size | 16M
tmp_table_size | 16M
max_allowed_packet | 4M
thread_stack | 256k
log |
log_error | /var/log/mysqld.log
log_warnings | 2
log_slow_queries |
log_queries_not_using_indexes | OFF
log_slave_updates | OFF
--MYSQL的配置文件
# Configuration File #########################################
Config File | /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
---mysql内存管理库类型，采用了jemalloc
# Memory management library ##################################
jemalloc is not enabled in MySQL config for process with ID 2529
# The End ####################################################
[root@mysqlupgrade ~]#
6,pt-mysql-summary只显示过滤某个状态变量的值
[root@mysqlupgrade ~]# pt-mysql-summary --user root --password system --sleep 5|grep -i connection
mysql: [Warning] Using a password on the command line interface can be insecure.
Connections 12500 3
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
7，编写shell持续显示
root@mysqlupgrade ~]# mkdir -p /shell_dir
[root@mysqlupgrade ~]#
[root@mysqlupgrade ~]# cd /shell_dir/
[root@mysqlupgrade shell_dir]#
[root@mysqlupgrade shell_dir]# more pt_tool.sh
#!/bin/bash
asleep=10
user=root
password=root
probe_file=probe.log
output_image=/tmp/probe.png
while [ 1 ] ;
do
pt-mysql-summary --user $user --password $password | grep -i "com_insert\|com_delete\|com_update\|com_select\|com_commit\|com_rollback"
done;
8,执行shell持久执行pt-mysql-summary
[root@mysqlupgrade shell_dir]# chmod u+x pt_tool.sh
[root@mysqlupgrade shell_dir]#
[root@mysqlupgrade shell_dir]# ./pt_tool.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
Connections 15000 3
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Connections 15000 3
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Connections
[root@mysqlupgrade ~]# ps -ef|grep 25682|grep -v grep
root 770 25682 1 02:59 pts/0 00:00:00 /bin/sh /usr/bin/pt-mysql-summary --user root --password system --sleep 5
root 25682 1614 0 02:53 pts/0 00:00:00 /bin/bash ./pt_tool.sh
[root@mysqlupgrade ~]# kill -9 25682
[root@mysqlupgrade ~]# ps -ef|grep mysql
root 6481 32516 0 03:00 pts/1 00:00:00 grep mysql
[root@mysqlupgrade shell_dir]# mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
2018_05_05_03_00_22 Cannot connect to MySQL. Check that MySQL is running and that the options after -- are correct.
[root@mysqlupgrade shell_dir]#
