List of mysql common monitoring Script Commands

List of mysql common monitoring Script Commands
[Java] #/bin/sh
 
# Check whether mysql server provides services normally
Mysqladmin-u sky-ppwd-h localhost ping
 
# Obtain the Current Status values of mysql
Mysqladmin-u sky-ppwd-h localhost status
 
# Obtain the current connection information of the database
Mysqladmin-u sky-ppwd-h localhost processlist
 
 
# Mysql dump data export
Mysqldump-uroot-T/tmp/mysqldump test test_outfile -- fields-enclosed-by = \ "-- fields-terminated-by =,
 
# Mysql Data Import
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 '"'enabled ',';
 
# Mysql process monitoring
Ps-ef | grep "mysqld_safe" | grep-v "grep"
Ps-ef | grep "mysqld" | grep-v "mysqld_safe" | grep-v "grep"
 
 
# View the status of the current database
Mysql-u root-p123456-e 'show status'
 
 
# The mysqlcheck tool can be used to check, repair, analyze, and 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 rate Query_cache_hits = (Qcache_hits/(Qcache_hits + Qcache_inserts) * 100%
Mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Qcache % "'
 
# Mysql Table Cache status
Mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Open % "'
 
# Mysql Thread Cache hit rate Thread_cache_hits = (1-Threads_created/Connections) * 100% normally, the Thread Cache hit rate is more than 90%.
Mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Thread % "'
 
# Mysql lock status: the lock status includes table lock and row lock. We can use the system status variable to obtain the total number of locks, the number of waits of other threads caused by locks, and the lock wait time information.
Mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "% lock % "'
 
# Mysql replication latency is executed on the slave Node
Mysql-u root-p123456-e 'show SLAVE status'
 
# Mysql Tmp table condition the Tmp Table condition is mainly used to monitor whether there are too many temporary tables in MySQL and whether there are temporary tables that have to be swapped out from 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 messages that have not been written to the disk.
Mysql-u root-p123456-e 'show /*! 50000 GLOBAL */status like "Binlog_cache % "'
 
# Mysql nnodb_log_waits quantity: The Innodb_log_waits status 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'