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 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
If you found this blog post useful, and want to refer to it again, why not download it as a PDF?