方法一:通过测试账号ping命令返回的信息判断数据库可以通过网络连接
[root@host-39-108-217-12 scripts]# cat mysql-available-status.sh
#!/bin/bash
MYSQL_PING=`/usr/bin/mysqladmin -uroot -p123456 -P3306 ping`
MYSQL_OK="mysqld is alive"
if [[ "$MYSQL_PING" != "$MYSQL_OK" ]];then
echo "MySQL is not ok !"
sleep 5
systemctl restart mariadb
else
echo "MySQL is ok !"
fi
1.监控数据库是否可以连接
方法:
mysqladmin -umonitor_user -p -h ping
telnet ip db_port
2.监控数据库是否可以读写
方法:
select @@version
3.监控数据库的连接数
方法:
show variables like 'max_connections'
show global status like 'Threads_connected'
Threads_connected / max_connections>0.8 就需要报警
1.监控数据库并发请求数量
show global status like 'Thread_running'
如何监控Innodb的阻塞:
从mysql性能字典表,innodb_lock_waits 是锁信息,innodb_trx是事务信息,有两条记录,需要放到一行中,因此,需要关联2次。
select b.trx_mysql_thread_id as ‘被阻塞线程’,
b.trx_query as ‘被阻塞SQL’,
b.trx_mysql_thread_id as ‘阻塞线程’,
b.trx_query as ‘阻塞SQL’,
(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(b.trx_started)) as ‘阻塞时间’
from
information_schema.innodb_lock_waits a
join information_schema.innodb_trx b
on a.requesting_trx_id=b.trx_id
join information_schema.innodb_trx c
on a.blocking_trx_id=c.trx_id
where (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started))>60;
设置全局锁的超时时间:
set global innodb_lock_wait_timeout=180;
查看线程相互阻塞的方法:
show engine innodb status \G 查看详细事务内容
SELECT b.,a. FROM information_schema.INNODB_TRX a,information_schema.PROCESSLIST b
WHERE a.trx_mysql_thread_id=b.ID AND a.trx_state=’RUNNING’ ;
参考: https://blog.csdn.net/m0_37814112/article/details/80936388 https://blog.csdn.net/weixin_43549578/article/details/88905102 https://cloud.tencent.com/developer/article/1068377 https://blog.csdn.net/bisal/article/details/78372919
六个最常用的 MySQL 数据库监控工:https://blog.csdn.net/alexwei2009/article/details/125323483