Chat with us, powered by LiveChat

Blog

Back

Querying MySQL from Powershell

24 Dec 2012 by Dan Rose

Want to save this blog for later? Download it now.

This quick and simple script will allow you to query MySQL from within Powershell. Ideal if you working on MySQL and wish to quickly check results, personally I find it quicker to run this inside the shell rather than firing up the Query Browser.

I would assume at this point you already have the MySQL.Net connector installed, but in case you haven’t, then you will need to do this as a first step. For information and download use this link.

What I’ve done here is incorporate the entire piece of code into 1 script, that way I can call this from any Powershell command window. I’ve also included a single input parameter called $Query. This is where you will be piping in your query string.

Read more: Office 365 scripting workshop

You will, of course, need to place your credentials, database name & host within the script fields.
Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$Query
)
$MySQLAdminUserName = 'username'
$MySQLAdminPassword = 'password'
$MySQLDatabase = 'MySQL-DB'
$MySQLHost = 'MySQL-Host'
$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase
Try {
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]
}
Catch {
Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
}
Finally {
$Connection.Close()
}

Save the above code as MySQL.ps1 and then from the command line, simply type in the query you want.

 
.\MySQL.ps1 -Query "select users, loginnames from customers"
From this, you should get the output you want in your command window.
PS C:\Powershell> .\MySQL.ps1 -Query "select users, loginnames from customers"
Users LoginNames
-------- -------------------
Joe Smith joe.smith@mydomain.com
Bryan Steele bryan.steele@mydomain.com
Barry Lyndhurst barry.lyndhurst@mydomain.com
Carly Hyatt carly.hyatt@mydomain.com
Simon Dyson simon.dyson@mydomain.com
PS C:\Powershell\InTest>

If you found this blog post useful, and want to refer to it again, why not download it as a PDF?