Accessing MySQL with PowerShell programmatically


Being able to access MySQL data from a PowerShell session or script can be useful in many ways. Using the MySQL .Net Connector it is possible to create a set of functions to query and manage your databases. The connector can be downloaded and installed from the MySQL website.

Once downloaded and installed, you should find MySql.Data.dll somewhere like:
C:\Program Files (x86)\MySQL\Connector.NET 6.9\Assemblies\v4.5\MySql.Data.dll

Make the MySql.Data.MySqlClient namespace available to your shell or script with a loadfrom command:

 

 

[void][system.reflection.Assembly]::LoadFrom(

'C:\Program Files (x86)\MySQL\Connector.NET 6.9\Assemblies\v4.5\MySql.Data.dll'

)

To create a client instance you’ll need at least a user id, a server name and a password. You can specify some other stuff, also, like a TCP port or database name. Create a connection string like $myCStr below:

 

$pwd = "mypass"
$Server = "myServer"
$Database = "myDatabase"
# without a database name
$myCStr = "Server=$server; uid=$uid; pwd=$pwd; "
# with a database name and using $env:USERNAME variable
$myCStr = "Server=$server;uid=$env:USERNAME;pwd=$pwd;database=$Database; "

Make up some query and store it in a variable:

$Query = "select user, host from mysql.user;"

Once all that is squared away, create a new connection object:

$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = $myCStr
$Connection.Open()

Create the appropriate object to connect and query your database server:

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

Create a bucket to receive the query output:

$DataSet = New-Object System.Data.DataSet

Execute the query and catch the query info (rows affected, etc.) with the $RecordCount variable and the actual data in the $DataSet variable.

$RecordCount = $DataAdapter.Fill($DataSet, "dataSet")

Close the connection:

$Connection.Close()

Display the data:

PS C:\> $DataSet.Tables[0] | ft -a

user          host
----          ----
devuser       devhost
testuser      testhost
myuid         %
root          localhost

Pretty simple, huh? If you plan on doing this a lot you may want to lock this process down. I use a couple of functions that streamline the query process. The first function (filter)myCStr I use to create the connection string:

filter myCStr {
param (
[Parameter(Position = 0, Mandatory = $false, ValueFromPipeline = $true)]
[System.String]$Server = "myFavServer",
[Parameter(Position = 1, Mandatory = $false, ValueFromPipeline = $true)]
[System.String]$Database,
[Parameter(Position = 2, Mandatory = $true, ValueFromPipeline = $true)]
[System.String]$uid,
[System.String]$pwd = (Get-Credential $uid).GetNetworkCredential().password
)
if ($Database) {
$myCStr = "Server=$Server;uid=$uid;pwd=$pwd;database=$Database;"
}
else {
$myCStr = "Server=$Server;uid=$uid;pwd=$pwd;"
}
if ($Server -eq "myFavServer") {
$myCStr = "Server=$Server;port=23306;uid=$uid;pwd=$pwd;database=$Database;"
}
if ($Server -eq "my2ndFavServer" -and $Database -eq 'myFavDb') {
$myCStr = "Server=$Server;port=33306;uid=$uid;pwd=$pwd;database=$Database;"
}
$myCStr
}

I’ll write more about this function later. I use the ‘filter’ statement to differentiate a functions ‘functionality’ internally, although functionally ‘function’ and ‘filter’ are the same. After configuring the filter to suit your environment, you can call it from your shell or script. This makes fetching a connection string for a given connection much more efficient.

PS C:\> myCStr my2ndFavServer myFavDb myuid
Server=my2ndFavServer;port=33306;uid=myuid;pwd=somepw7;database=myFavDb;
PS C:\>

If you don’t specify a user id (uid) then myCStr will prompt you. You can set a static password in the function instead of relying on ‘get-credentials’ in the params, if you are comfortable with that. Or use your own userid/password tools.

Now a tool to run a MySQL query. The Start-mysqlQuery function puts it altogether for me. Start-mysqlQuery will call myCStr  and fetch a preconfigured connection string for you.

function Start-mysqlQuery {
[CmdletBinding()]
param (
[Parameter(Position = 0, Mandatory = $true,ValueFromPipeline = $true)]
[System.String]$Server,
[Parameter(Position = 1)]
[ValidateNotNull()]
[System.String]$Query,
[Parameter(Position = 2)]
[System.String]$Database
)
Try {
[void][system.reflection.Assembly]::LoadFrom(
'C:\Program Files (x86)\MySQL\Connector.NET 6.9\Assemblies\v4.5\MySql.Data.dll'
)
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = myCStr $Server $Database
# $Connection.ConnectionString
$Connection.Open()
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
if ($Query -like 'call%') {
$query
$Command.CommandType = [System.Data.CommandType]::StoredProcedure
}
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $DataAdapter.Fill($DataSet, "dataSet")
$Connection.Close()
$DataSet.Tables[0]
}
Catch {
Write-Host "ERROR : Unable to run query : $Server $query" # $Error[0] | fl *
}
Finally {
}
}

Now you can query your MySQL servers from the command line or use the code in your own scripts. Start-mysqlQuery returns a data object and viewed or used using other PowerShell operations:

PS C:\> start-mysqlQuery -Server mysqlserver01 -Query $query | ft -a

cmdlet myCStr at command pipeline position 1
Supply values for the following parameters:
uid: myuid

user           host
----           ----
myuid          %
mysql.sys      localhost
root           localhost

Notice that myCStr is harassing me about a user id and password. That’s fine if I’m only going to query one or two servers and not use the code within a scheduled task. You can configure myCStr with a password by altering the $uid and $pwd params with a default value:

[System.String]$uid = "myuid",
[System.String]$pwd = "mypassword"

While not the most secure example, there are many ways to make it secure. Using the command I can do a quick user inventory on a list of MySQL database hosts:

PS C:\> $Query = "select @@hostname as hostname, user, host from mysql.user;"
PS C:\> cat .\myservers.txt | %{ start-mysqlQuery -Server $_ -Query $query } | ft -a

hostname                user           host
--------                ----           ----
mysqlserver04.local.com myuid          %
mysqlserver04.local.com mysql.sys      localhost
mysqlserver04.local.com root           localhost
mysqlserver01.local.com myuid          %
mysqlserver01.local.com mysql.sys      localhost
mysqlserver01.local.com root           localhost
mysqlserver02.local.com myuid          %
mysqlserver02.local.com mysql.sys      localhost
mysqlserver02.local.com root           localhost
mysqlserver03.local.com myuid          %
mysqlserver03.local.com mysql.sys      localhost
mysqlserver03.local.com root           localhost

From here the possibilities are unlimited. By sorting or grouping the output of this query I could make short work of finding standard users that are missing, or non-standard users that are present. Comment below and let me know what you think!

Advertisements
This entry was posted in powershell, Uncategorized and tagged , , , , . Bookmark the permalink.

2 Responses to Accessing MySQL with PowerShell programmatically

  1. Pingback: Homegrown PowerShell tools for MySQL | brendan62269

  2. Sashimi says:

    Good job dad!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s