1
#! /usr/bin/perl -w -I ..
3
# MySQL Database Server Tests via check_mysql_healthdb
6
# These are the database permissions required for this test:
7
# GRANT SELECT ON $db.* TO $user@$host INDENTIFIED BY '$password';
8
# GRANT SUPER, REPLICATION CLIENT ON *.* TO $user@$host;
10
# mysql -u$user -p$password -h$host $db
18
plan skip_all => "check_mysql_health not compiled" unless (-x "./check_mysql_health");
22
my $bad_login_output = '/Access denied for user /';
23
my $mysqlserver = getTestParameter(
25
"A MySQL Server with no slaves setup"
27
my $mysql_login_details = getTestParameter(
28
"MYSQL_LOGIN_DETAILS",
29
"Command line parameters to specify login access",
32
my $with_slave = getTestParameter(
33
"NP_MYSQL_WITH_SLAVE",
34
"MySQL server with slaves setup"
36
my $with_slave_login = getTestParameter(
37
"NP_MYSQL_WITH_SLAVE_LOGIN",
38
"Login details for server with slave",
44
$result = NPTest->testCmd("./check_mysql_health -V");
45
cmp_ok( $result->return_code, '==', 0, "expected result");
46
like( $result->output, "/check_mysql_health \\(\\d+\\.\\d+\\)/", "Expected message");
48
$result = NPTest->testCmd("./check_mysql_health --help");
49
cmp_ok( $result->return_code, '==', 0, "expected result");
50
like( $result->output, "/slave-lag/", "Expected message");
51
like( $result->output, "/slave-io-running/", "Expected message");
52
like( $result->output, "/slave-sql-running/", "Expected message");
53
like( $result->output, "/threads-connected/", "Expected message");
54
like( $result->output, "/threadcache-hitrate/", "Expected message");
55
like( $result->output, "/querycache-hitrate/", "Expected message");
56
like( $result->output, "/keycache-hitrate/", "Expected message");
57
like( $result->output, "/bufferpool-hitrate/", "Expected message");
58
like( $result->output, "/tablecache-hitrate/", "Expected message");
59
like( $result->output, "/table-lock-contention/", "Expected message");
60
like( $result->output, "/temp-disk-tables/", "Expected message");
61
like( $result->output, "/connection-time/", "Expected message");
62
like( $result->output, "/slow-queries/", "Expected message");
63
like( $result->output, "/qcache-lowmem-prunes/", "Expected message");
64
like( $result->output, "/bufferpool-wait-free/", "Expected message");
65
like( $result->output, "/log-waits/", "Expected message");
70
$result = NPTest->testCmd("./check_mysql_health -H $mysqlserver -m connection-time -u dummy -pdummy");
71
cmp_ok( $result->return_code, '==', 2, "Login failure");
72
like( $result->output, "/CRITICAL - Cannot connect to database: Error: Access denied/", "Expected login failure message");
74
$result = NPTest->testCmd("./check_mysql_health");
75
cmp_ok( $result->return_code, "==", 3, "No mode defined" );
76
like( $result->output, "/Must specify a mode/", "Correct error message");
78
$result = NPTest->testCmd("./check_mysql_health -m connection-time -w 10 -c 30");
79
cmp_ok( $result->return_code, "==", 0, "Connected" );
80
like( $result->output, "/OK - Connection Time ([0-9\.]+) usecs|connection_time=([0-9\.]+);10;30/", "Correct error message");
82
$result = NPTest->testCmd("./check_mysql_health -m keycache-hitrate -w :10 -c 2");
83
cmp_ok( $result->return_code, "==", 2, "Connected" );
84
like( $result->output, "/CRITICAL - Key Cache Hitrate at ([0-9\.]+)%|keycache_hitrate=([0-9\.]+)%;:10;2/", "Correct error message");
86
$result = NPTest->testCmd("./check_mysql_health -m qcache-hitrate -w :10 -c 2");
87
cmp_ok( $result->return_code, "==", 2, "Connected" );
88
like( $result->output, "/CRITICAL - Query Cache Hitrate at ([0-9\.]+)%|qcache_hitrate=([0-9\.]+)%;:10;2/", "Correct error message");
90
$result = NPTest->testCmd("./check_mysql_health -m qcache-hitrate -w :10 -c 2 -v 2>&1");
91
cmp_ok( $result->return_code, "==", 2, "Connected" );
92
like( $result->output, "/NOTICE: we have results/", "Verbose output");
93
like( $result->output, "/CRITICAL - Query Cache Hitrate at ([0-9\.]+)%|qcache_hitrate=([0-9\.]+)%;:10;2/", "Correct error message");
98
my $slow_queries_last = 0;
101
$result = NPTest->testCmd("./check_mysql_health -m slow-queries -w :10 -c 2");
103
$result = NPTest->testCmd("./check_mysql_health -m slow-queries -w :10 -c 2 -v 2>&1");
104
ok( $result->output =~ /Load variable Slow_queries \(([0-9]+)\) /);
105
$slow_queries_last = $1;
106
ok( $result->output =~ /Result column 1 returns value ([0-9]+) /);
108
$delta = $slow_queries - $slow_queries_last;
109
ok( $result->output =~ /OK - ([0-9]+) slow queries/);
110
cmp_ok($1, "==", $delta);
115
$result = NPTest->testCmd("./check_mysql_health -m slow-queries -w :11 -c :22 -v 2>&1");
116
like( $result->output, "/slow_queries_rate=[0-9\.]+;:11;:22 slow_queries=[0-9]+;:11;:22/", "Correct error message");
118
$result = NPTest->testCmd("./check_mysql_health -m qcache-lowmem-prunes -w :11 -c :22 -v 2>&1");
119
like( $result->output, "/lowmem_prunes_rate=[0-9\.]+;:11;:22 lowmem_prunes=[0-9]+;:11;:22/", "Correct error message");
121
$result = NPTest->testCmd("./check_mysql_health -m bufferpool-wait-free -w :11 -c :22 -v 2>&1");
122
like( $result->output, "/bufferpool_free_waits_rate=[0-9\.]+;:11;:22 bufferpool_free_waits=[0-9]+;:11;:22/", "Correct error message");
124
$result = NPTest->testCmd("./check_mysql_health -m log-waits -w :11 -c :22 -v 2>&1");
125
like( $result->output, "/log_waits_rate=[0-9\.]+;:11;:22 log_waits=[0-9]+;:11;:22/", "Correct error message");
129
skip "Has a slave server", 6 if $with_slave;
131
$result = NPTest->testCmd("./check_mysql_health -m slave-lag");
132
cmp_ok( $result->return_code, "==", 2, "No slave" );
133
like( $result->output, "/CRITICAL - Slave lag NULL|slave_lag=0;10;20/", "Correct error message");
135
$result = NPTest->testCmd("./check_mysql_health -m slave-io-running");
136
cmp_ok( $result->return_code, "==", 2, "No slave" );
137
like( $result->output, "/CRITICAL - Slave io not running|slave_io_running=0/", "Correct error message");
139
$result = NPTest->testCmd("./check_mysql_health -m slave-sql-running");
140
cmp_ok( $result->return_code, "==", 2, "No slave" );
141
like( $result->output, "/CRITICAL - Slave sql not running|slave_io_running=0/", "Correct error message");
146
skip "No mysql server with slaves defined", 5 unless $with_slave;
147
$result = NPTest->testCmd("./check_mysql_health -H $with_slave $with_slave_login");
148
cmp_ok( $result->return_code, '==', 0, "Login okay");
150
$result = NPTest->testCmd("./check_mysql_health -S -H $with_slave $with_slave_login");
151
cmp_ok( $result->return_code, "==", 0, "Slaves okay" );
153
$result = NPTest->testCmd("./check_mysql_health -S -H $with_slave $with_slave_login -w 60");
154
cmp_ok( $result->return_code, '==', 0, 'Slaves are not > 60 seconds behind');
156
$result = NPTest->testCmd("./check_mysql_health -S -H $with_slave $with_slave_login -w 60:");
157
cmp_ok( $result->return_code, '==', 1, 'Alert warning if < 60 seconds behind');
158
like( $result->output, "/^SLOW_SLAVE WARNING:/", "Output okay");