Several commonly used mysql monitoring script commands

MySQL is increasingly accepted by more and more enterprises. With the development of enterprises, MySQL storage data is expanding day by day, and there are more and more issues related to MySQL performance analysis, monitoring and warning, and capacity expansion. In this article, we will give you a summary of several mysql monitoring script commands.

[java]

#/bin/sh

#Detect whether mysql server is providing services normally
mysqladmin -u sky -ppwd -h localhost ping

#Get the current status values ​​of mysql
mysqladmin -u sky -ppwd -h localhost status

#Get 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 '"' ENCLOSED BY ',';

#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'


#mysqlcheck tool program can 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 quantity
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 must be above 90% to be considered reasonable.
mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Thread%"'

#mysql Locking status: The locking status includes table locks and row locks. We The total number of locks, the number of times the lock caused other threads to wait, and the lock waiting time information can be obtained through the system status variable
mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "%lock%" '

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

#mysql Tmp table status The status of Tmp Table is mainly Used to monitor whether MySQL uses too many temporary tables and whether any temporary tables are too large and 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 status: 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 status variable directly reflects the Innodb Log Buffer insufficient space Number of times to wait
mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Innodb_log_waits'

The above content is a summary of several mysql monitoring script commands. I hope it will be helpful to everyone Help.

Related recommendations:

mysql script for automatic monitoring and synchronization

Detailed examples of Linux server monitoring

10 recommended articles about server monitoring

The above is the detailed content of Several commonly used mysql monitoring script commands. For more information, please follow other related articles on the PHP Chinese website!