Thursday, October 23, 2008

Powershell Check MySQL Status

The script below will check the specified server using the credentials provided and report back to you all of the Status Variables. You will have to change the line "$myconnection.ConnectionString" to what ever your settings are.  On a side note drop the <> from the lines.  Also you will need the MySQL .Net Connector for this to work.


::Start Code::
[void][system.reflection.Assembly]::LoadFrom("C:\Program Files\MySQL\MySQL Connector Net 5.2.3\Binaries\.NET 2.0\MySQL.Data.dll")

$myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$myconnection.ConnectionString = "server=;user id=;password=;pooling=false"
$myconnection.Open()

$mycommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$mycommand.Connection = $myconnection
$mycommand.CommandText = "SHOW STATUS;"
$myreader = $mycommand.ExecuteReader()
while($myreader.Read()){write-host $myreader.getstring(0)"|"$myreader.getstring(1)}
$mycommand.Connection.close()
::End Code::

If you are only interested in a two or three of those variables (say threads_running and threads_connected) you can use the code below.

[void][system.reflection.Assembly]::LoadFrom("C:\Program Files\MySQL\MySQL Connector Net 5.2.3\Binaries\.NET 2.0\MySQL.Data.dll")

$myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$myconnection.ConnectionString = "server=IP OR HOSTNAME;user id=YOUR_USER;password=YOUR_PASSWORD;pooling=false"
$myconnection.Open()

$mycommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$mycommand.Connection = $myconnection
$mycommand.CommandText = "SHOW STATUS LIKE 'threads_connected';"
$myreader = $mycommand.ExecuteReader()

while($myreader.Read()){write-host $myreader.getstring(0)" : "$myreader.getstring(1)}
$mycommand.Connection.close()
$mycommand.connection.open()
$mycommand.CommandText = "SHOW STATUS LIKE 'threads_running';"
$myreader = $mycommand.executereader()
while($myreader.read()){write-host $myreader.getstring(0)" : "$myreader.getstring(1)}
$mycommand.Connection.close()

No comments: