Sorting out commonly used mysql monitoring Script Commands

Copy codeThe Code is as follows:
#/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

# Obtain the number of connections to the current database
Mysql-u root-p123456-BNe "select host, count (host) from processlist group by host;" information_schema

# Display mysql uptime
Mysql-e "show status like '% uptime %'" | awk '/ptime/{calc = $ NF/3600; print $ (NF-1), calc "Hour "}'

# View the database size
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 ;'

# Viewing the column information of a table
Mysql-u <user> -- password = <password>-e "show columns from <table>" <database> | awk '{print $1}' | tr "\ n" ", "| sed's/, $ // G'

# Execute the mysql script
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 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'

 

The common shell scripts sorted out are placed in the https://github.com/zhwj184/shell-work.

The common shell scripts sorted out are placed in the https://github.com/zhwj184/shell-work.

The common shell scripts sorted out are placed in the https://github.com/zhwj184/shell-work.