Query a MySQL Database with PowerShell

In a current project I am limited to using MySql instead of my usual Microsoft SQL Server platform and I wanted to build a set of scripts that perform some ETL (Extract, Transform, and Load) work in MySQL. Since I want the ability to make small changes without recompiling an application or a dll, I decided to use PowerShell and Oracle MySql Connector/Net. This simplistic example assumes that Connector/Net is installed and has the dlls loaded in the GAC (Global Assembly Cache).

Below is the sample PowerShell script that executes a basic query against a MySql database. This example database has a single table (Test) and a single column (A), but you can easily manipulate the script to perform all sorts of database administration tasks including creating/dropping tables/indexes, importing data, exporting data, e-mailing data, managing processes/users, etc...

Like ADO.Net, MySql connector/Net has the idea of a connected and disconnected mode. This example assumes that the data manipulation will happen while the client is connected, rather than using DataSets and TableAdapters to read/manipulate the data.

---

# Mike Burr

# April 2013

# Demonstrates how to perform a basic query on MySql using PowerShell

[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data.MySqlClient")

#Create the connection

$connstr = "server=localhost;user=root;database=my_test_db;port=3306;password=some_pwd;"

$conn = New-Object "MySql.Data.MySqlClient.MySqlConnection" $connstr

Try {

   write-host "Connection Opened"

   #Open the connection

   $conn.Open()

   $query = "SELECT * FROM Test WHERE A = @A"

  

   $cmd = New-Object "MySql.Data.MySqlClient.MySqlCommand" -ArgumentList $query,$conn

   $cmd.Parameters.AddWithValue("@A", "test")

  

   $reader = $cmd.ExecuteReader()

  

   while ($reader.Read()) {

      write-host ("A is " + ($reader["A"]))

   }

  

  

} Catch {

  #Perform exception handling logic here

} Finally {

  #Close the connection

  write-host "Connection Closed"

  $conn.Close()

}

-->

---

See Also:
How to Query a Microsoft SQL Server Database with Windows PowerShell