-
Notifications
You must be signed in to change notification settings - Fork 22
/
Copy pathmysql.sh
110 lines (100 loc) · 3.49 KB
/
mysql.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
#!/bin/bash
# Zabbix MySQL trap sender
# UserParameter=mysql[*],/etc/zabbix/mysql.sh $1 $2
# run '/etc/zabbix/mysql.sh uptime debug' to debug trap sending
ZABBIX_CONFIG_FILE=/etc/zabbix/zabbix_agentd.conf
TMPV=/tmp/zabbix_variables.tmp
TMPS=/tmp/zabbix_status.tmp
variables=(
max_connections
query_cache_size
table_open_cache
)
status=(
Com_begin
Com_commit
Com_delete
Com_delete_multi
Com_insert
Com_insert_select
Com_lock_tables
Com_replace
Com_replace_select
Com_rollback
Com_select
Com_update
Connections
Created_tmp_disk_tables
Created_tmp_files
Created_tmp_tables
Innodb_buffer_pool_reads
Innodb_buffer_pool_read_requests
Innodb_buffer_pool_wait_free
Innodb_data_pending_fsyncs
Innodb_data_pending_reads
Innodb_data_pending_writes
Innodb_data_reads
Innodb_data_writes
Innodb_log_writes
Innodb_pages_read
Innodb_row_lock_current_waits
Open_tables
Qcache_free_memory
Qcache_hits
Qcache_inserts
Queries
Slow_queries
Table_locks_immediate
Table_locks_waited
Threads_cached
Threads_connected
Threads_created
Threads_running
)
#send variables (once per hour by default)
if [ "$1" == "max_connections" ]; then
MYSQLV=`echo "SHOW GLOBAL VARIABLES" | /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf`
for var in ${variables[*]}; do
echo "$MYSQLV" | grep "^$var\s" >> $TMPV
done
sed -i "s/^/- mysql.variables./" $TMPV
if [ "$2" == "debug" ]; then
cat $TMPV
/usr/bin/zabbix_sender -c $ZABBIX_CONFIG_FILE -i $TMPV -vv
else
/usr/bin/zabbix_sender -c $ZABBIX_CONFIG_FILE -i $TMPV >/dev/null 2>&1
fi
#return max_connections
echo "$MYSQLV" | grep "^max_connections\s" | awk '{print $2}'
rm $TMPV
#send status (once per 5min by default)
elif [ "$1" == "uptime" ]; then
MYSQLS=`echo "SHOW GLOBAL STATUS" | /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf`
for stat in ${status[*]}; do
echo "$MYSQLS" | grep "^$stat\s" >> $TMPS
done
echo "SHOW SLAVE STATUS\G" | /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf | grep Seconds_Behind_Master | sed -r 's/^ *//' | sed 's/://' | sed 's/NULL/0/' >> $TMPS
sed -i "s/^/- mysql.status./" $TMPS
if [ "$2" == "debug" ]; then
cat $TMPS
/usr/bin/zabbix_sender -c $ZABBIX_CONFIG_FILE -i $TMPS -vv
else
/usr/bin/zabbix_sender -c $ZABBIX_CONFIG_FILE -i $TMPS >/dev/null 2>&1
fi
#return Uptime
echo "$MYSQLS" | grep "^Uptime\s" | awk '{print $2}'
rm $TMPS
#extensions below (could be removed, used by template_app_mysql_transactions)
#number of transactions with rows_locked >X (default 10000)
elif [ "$1" == "trans-locking" ]; then
if [ "$2" == "" ]; then t='10000'; else t="$2"; fi
echo "select count(*) from INFORMATION_SCHEMA.INNODB_TRX where trx_rows_locked>$t" | /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf | tail -n 1
#number of transactions started > X sec ago
elif [ "$1" == "trans-old" ]; then
if [ "$2" == "" ]||[ "$2" == "0" ]; then w=''; else w="where TIME_TO_SEC(TIMEDIFF(NOW(),trx_started))>$2"; fi
echo "select count(*) from INFORMATION_SCHEMA.INNODB_TRX $w" | /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf | tail -n 1
#number of transactions with query running > X sec
elif [ "$1" == "trans-running" ]; then
if [ "$2" == "" ]||[ "$2" == "0" ]; then w=''; else w="AND p.TIME>$2"; fi
echo "select count(*) from INFORMATION_SCHEMA.INNODB_TRX t, INFORMATION_SCHEMA.PROCESSLIST p WHERE t.trx_mysql_thread_id=p.ID AND p.COMMAND='Query' $w" | /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf | tail -n 1
fi