Powershell: Run MySQL Querys with Powershell

Powershell Header
In our Hosting enviroment we are now using MS SQL Servers for our back-end. But there are still some MySQL Server left which store some data. Now since we do a lot of automation with Windows Powersehll I wrote a function to run MySQL querys with Powershell.

First you have to install the MySQL .NET Connector or just copy the MySql.Data.dll from the MySQL.

You can now load this:

With DLL File:

 [void][system.reflection.Assembly]::LoadFrom("C:\myscript\MySQL.Data.dll")

or if you have installed the MySQL .NET Connector:

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

Now basiclly this function does everything for you:

Function Run-MySQLQuery {
<# .SYNOPSIS run-MySQLQuery .DESCRIPTION By default, this script will: - Will open a MySQL Connection - Will Send a Command to a MySQL Server - Will close the MySQL Connection This function uses the MySQL .NET Connector or MySQL.Data.dll file .PARAMETER ConnectionString Adds the MySQL Connection String for the specific MySQL Server .PARAMETER Query The MySQL Query which should be send to the MySQL Server .EXAMPLE C:\PS> run-MySQLQuery -ConnectionString "Server=localhost;Uid=root;[email protected];database=project;" -Query "SELECT * FROM firsttest"
 
Description
-----------
This command run the MySQL Query "SELECT * FROM firsttest"
to the MySQL Server "localhost" with the Credentials User: Root and password: p@ssword and selects the database project
 
.EXAMPLE
C:\PS> run-MySQLQuery -ConnectionString "Server=localhost;Uid=root;[email protected];database=project;" -Query "UPDATE firsttest SET firstname='Thomas' WHERE Firstname like 'PAUL'"
 
Description
-----------
This command run the MySQL Query "UPDATE project.firsttest SET firstname='Thomas' WHERE Firstname like 'PAUL'"
to the MySQL Server "localhost" with the Credentials User: Root and password: p@ssword
 
.EXAMPLE
C:\PS> run-MySQLQuery -ConnectionString "Server=localhost;Uid=root;[email protected];" -Query "UPDATE project.firsttest SET firstname='Thomas' WHERE Firstname like 'PAUL'"
 
Description
-----------
This command run the MySQL Query "UPDATE project.firsttest SET firstname='Thomas' WHERE Firstname like 'PAUL'"
to the MySQL Server "localhost" with the Credentials User: Root and password: p@ssword and selects the database project
 
#>
Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$query,
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$connectionString
)
Begin {
Write-Verbose "Starting Begin Section"
}
Process {
Write-Verbose "Starting Process Section"
try {
# load MySQL driver and create connection
Write-Verbose "Create Database Connection"
# You could also could use a direct Link to the DLL File
# $mySQLDataDLL = "C:\scripts\mysql\MySQL.Data.dll"
# [void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL)
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $ConnectionString
Write-Verbose "Open Database Connection"
$connection.Open()
 
# Run MySQL Querys
Write-Verbose "Run MySQL Querys"
$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["data"] | Format-Table
}
catch {
Write-Host "Could not run MySQL Query" $Error[0]
}
Finally {
Write-Verbose "Close Connection"
$connection.Close()
}
}
End {
Write-Verbose "Starting End Section"
}
}

You can use this Powershell function like this:

run-MySQLQuery -ConnectionString "Server=localhost;Uid=root;[email protected];database=project;" -Query "SELECT * FROM firsttest"

If you wanna run more than one command you could do something like this:

$connString = "Server=localhost;Uid=root;[email protected];database=project;"
 
[array]$sQLDings += "SELECT * from project.firsttest"
[array]$sQLDings += "UPDATE project.firsttest SET firstname='Thomas' WHERE Firstname like 'Paul'"
[array]$sQLDings += "SELECT * from project.firsttest"
 
foreach($sQLquery in $SQLDings) {
run-MySQLQuery -connectionString $connString -query $sQLquery
}

If you need more information about Powershell and MySQL Mark Alexander Bain wrote a blog post about this.

And if you need a local MySQL Server on your machine to test this, you could use Microsoft Platform Installer to install MySQL and other cool Webtools.