Sort out common mysql monitoring scripts

Sort out common mysql monitoring scripts

01 #! /Bin/sh02 # filename: mysql_monitor.sh03 # site: www. jbxue. com04 # Check whether mysql server provides services normally 05 mysqladmin-u sky-ppwd-h localhost ping0607 # obtain the current mysql Status values 08 mysqladmin-u sky-ppwd-h localhost status0910 # obtain the current connection information of the Database 11 mysqladmin-u sky-ppwd-h localhost processlist1213 # obtain the number of connections of the current database 14 mysql-u root-p123456-BNe "select host, count (host) from processlist group by host; "information_schema1516 # display mysql uptime17my SQL-e "SHOW STATUS LIKE '% uptime %'" | awk '/ptime/{calc = $ NF/3600; print $(NF-1 ), calc "Hour"} '2014 # view database size 20 mysql-u root-p123456-e 'select table_schema, round (sum (data_length + index_length)/1819, 4) from information_schema.tables group by table_schema; '1970 # view the column information of a table 23 mysql-u <user> -- password = <password>-e "show columns from <table>" <database> | awk' {print $1} '| tr "\ n "", "| sed's/, $ // G'2425 # Run mysql script 26 mysql-u user-name-p password <script. sql2728 # mysql dump data export 29 mysqldump-uroot-T/tmp/mysqldump test test_outfile -- fields-enclosed-by = \ "-- fields-terminated-by =, 3031 # mysql Data Import 32 mysqlimport -- user = name -- password = pwd test -- fields-enclosed-by = \ "-- fields-terminated-by =, /tmp/test_outfile.txt33LOAD data infile '/tmp/test_outfile.txt' into table test_outfile FIELDS TERMINATED B Y' "'enabledby ','; 343536 # mysql process monitoring 37 ps-ef | grep "mysqld_safe" | grep-v "grep" 38 ps-ef | grep "mysqld" | grep-v "mysqld_safe" | grep- v "grep" 3940 # view the status of the current database 41 mysql-u root-p123456-e 'show status' 4243 # check can be performed by the mysqlcheck tool program ), repair, analyze and optimize MySQL Server Table 44 mysqlcheck-u root-p123456 -- all-databases4546 # mysql qps query QPS = Questions (or Queries) /Seconds47mysql-u Root-p123456-e 'show /*! 50000 GLOBAL */status like "Questions" '48mysql-u root-p123456-e' SHOW /*! 50000 GLOBAL */status like "Queries" '49 # mysql Key Buffer hit rate bytes = (1-Key_reads/Key_read_requests) * 100% key_buffer_write_hits = (1-Key_writes/Key_write_requests) * 100% 50 mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Key %" 5152 # mysql Innodb Buffer hit rate innodb_buffer_read_hits = (1-Innodb_buffer_pool_reads/bytes) * 100% 53 mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Innodb_buffer_pool_read %" '5455 # mysql Query Cache hit rate Query_cache_hits = (Qcache_hits/(Qcache_hits + Qcache_inserts )) * 100% 56 mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Qcache %" 5758 # mysql Table Cache STATUS 59 mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Open %" '6061 # mysql Thread Cache hit rate Thread_cache_hits = (1-Threads_created/Connections) * 100% normally, the Thread Cache hit rate must be greater than 90%. 62 mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Thread %" '63 # www. jbxue. com6465 # mysql lock status: the lock status includes table locks and row locks. We can use the system status variable to obtain the total number of locks and the number of waits of other threads caused by locks, and lock wait time information 66 mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "% lock %" '6768 # mysql replication latency: Run 69 mysql-u root-p123456-e 'show slave status' 7071 # mysql Tmp on the SLAVE Node table Condition Tmp Table condition is mainly used to monitor whether there are too many temporary tables in MySQL, whether a temporary table is too large and has to be swapped out from memory to the disk file 72 mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Created_tmp %" 7374 # mysql Binlog Cache Usage: Binlog Cache is used to store Binlog messages that have not been written to the disk. 75 mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Binlog_cache %" '7677 # mysql nnodb_log_waits volume: the Innodb_log_waits status variable directly reflects the number of waits caused by insufficient Innodb Log Buffer space 78 mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Innodb_log_waits'