Project Puchufy

Connecting to MySQL

This post will provide you guidance on how to run MySQL queries in Powershell.

Save this script as a PS1. In this example, we will save it as db_connect.ps1 to c:\.

Param(
  [Parameter(
  Mandatory = $true,
  ParameterSetName = '',
  ValueFromPipeline = $true)]
  [string]$Query
  )

$MySQLAdminUserName = 'USERNAME'
$MySQLAdminPassword = 'PASSWORD'
$MySQLDatabase = 'DBNAME'
$MySQLHost = 'HOST_IPADDRESS'
$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()
  }

NOTE: Make sure that the following variables are set in accordance to the database you are connecting to –
$MySQLAdminUserName = ‘USERNAME’
$MySQLAdminPassword = ‘PASSWORD’
$MySQLDatabase = ‘DBNAME’
$MySQLHost = ‘HOST_IPADDRESS’

And then, to execute MySQL queries using Powershell:

$my_query = 'Select * from users where type="END_USER"'
c:\db_connect.ps1 -Query $my_query

The variable $my_query contains the MySQL query you wanted to execute. You can replace it with what query you want to execute.

Below is the sample query and result: