Querying MySQL from Powershell
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.
You will, of course, need to place your credentials, database name & host within the script fields.
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
$MySQLAdminUserName = 'username'
$MySQLAdminPassword = 'password'
$MySQLDatabase = 'MySQL-DB'
$MySQLHost = 'MySQL-Host'
$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = $ConnectionString
$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")
Write-Host "ERROR : Unable to run query : $query `n$Error"
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"
Joe Smith email@example.com
Bryan Steele firstname.lastname@example.org
Barry Lyndhurst email@example.com
Carly Hyatt firstname.lastname@example.org
Simon Dyson email@example.com
If you found this blog post useful, and want to refer to it again, why not download it as a PDF?
Using PowerShell for your Office 365 reporting needs is one of the most common options, and scripts like this show its power and integration. However, we know it can be time-consuming and laborious – which is one of the key reasons we’ve developed Nova, our Office 365 management tool, to give you a solution that’s easy-to-use, efficient and scalable.