Querying MySQL from Powershell
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 firstname.lastname@example.org
Bryan Steele email@example.com
Barry Lyndhurst firstname.lastname@example.org
Carly Hyatt email@example.com
Simon Dyson firstname.lastname@example.org
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.