MySQL Monitor common shell scripts

#/bin/sh




#检测mysql server is serving as expected
Mysqladmin-u sky-ppwd-h localhost Ping




#获取mysql当前的几个状态值
Mysqladmin-u sky-ppwd-h localhost status




#获取数据库当前的连接信息
Mysqladmin-u sky-ppwd-h localhost processlist




#获取当前数据库的连接数
Mysql-u root-p123456-bne "Select Host,count (Host) from the Processlist group by host;" Information_schema




#显示mysql的uptime
Mysql-e "SHOW STATUS like '%uptime% '" |awk '/ptime/{calc = $NF/3600;print $ (NF-1), calc "Hour"} '




#查看数据库的大小
Mysql-u root-p123456-e ' Select Table_schema,round (sum (data_length+index_length)/1024/1024,4) from Information_ Schema.tables GROUP by Table_schema; '




#查看某个表的列信息
Mysql-u <user>--password=<password>-E "SHOW COLUMNS from <table>" <database> | awk ' {print '} ' | TR "\ n" "," | Sed ' s/,$//g '




#执行mysql脚本
Mysql-u user-name-p Password < script.sql




#mysql Dump Data export
Mysqldump-uroot-t/tmp/mysqldump test Test_outfile--fields-enclosed-by=\ "--fields-terminated-by=,




#mysql数据导入
Mysqlimport--user=name--password=pwd Test--fields-enclosed-by=\ "--fields-terminated-by=,/tmp/test_outfile.txt
LOAD DATA INFILE '/tmp/test_outfile.txt ' into TABLE test_outfile fields TERMINATED by ' "' enclosed by ', ';




#mysql进程监控
Ps-ef | grep "Mysqld_safe" | Grep-v "grep"
Ps-ef | grep "Mysqld" | Grep-v "Mysqld_safe" | Grep-v "grep"








#查看当前数据库的状态
Mysql-u root-p123456-e ' Show status '








#mysqlcheck Tool program can check (check), fix (repair), analyze (analyze) and optimize (optimize) tables in MySQL Server
Mysqlcheck-u root-p123456--all-databases




#mysql QPS Query QPS = Questions (or Queries)/Seconds
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' Questions '
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' Queries '




#mysql Key Buffer hit Rate key_buffer_read_hits = (1-key_reads/key_read_requests) * 100% key_buffer_write_hits= (1-key_ writes/key_write_requests) * 100%
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' key% '




#mysql Innodb Buffer hit rate innodb_buffer_read_hits= (1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100 %
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' innodb_buffer_pool_read% '




#mysql Query Cache hit query_cache_hits= (Qcache_hits/(qcache_hits + qcache_inserts)) * 100%
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' qcache% '




#mysql Table Cache State Amount
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' open% '




#mysql Thread Cache Hit Thread_cache_hits = (1-threads_created/connections) * 100% Normally, the thread cache hit rate is more than 90%. More reasonable.
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' thread% '




#mysql Lock State: Lock State includes table lock and row lock, we can get the total number of locks through the system state variables, the number of times the lock causes other threads to wait, and the lock wait time information
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like '%lock% '




#mysql replication delay is performed on the slave node
Mysql-u root-p123456-e ' SHOW SLAVE STATUS '




#mysql TMP table Condition TMP table is primarily used to monitor whether MySQL uses temporary tables too much, if temporary tables are too large to be swapped out of memory to disk files
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' created_tmp% '




#mysql Binlog Cache Usage: Binlog cache is used to store Binlog information that has not yet been written to the disk.
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' binlog_cache% '




#mysql nnodb_log_waits Volume: innodb_log_waits state variable directly reflects the number of waits caused by insufficient Innodb log Buffer space
Mysql-u root-p123456-e ' SHOW/*!50000 GLOBAL * * STATUS like ' innodb_log_waits '

MySQL Monitor common shell scripts