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;Pwd=p@ssword;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;Pwd=p@ssword;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;Pwd=p@ssword;" -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;Pwd=p@ssword;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;Pwd=p@ssword;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.
Tags: function, Microsoft, MySQL, MySQL .NET Connector, MySQL Query, MySQL with Powershell, PowerShell, Powershell and MySQL, run-mysqlquery, Server, Windows Powershell Last modified: August 18, 2018
Hey,
thx for the Script! This is the first one how worked at the firsttime without any errors!
thx
cheers
Chris
Very helpful post.
I’m building script in PS with Active Directory output and INSERT info MySQL Table row by row.
Can you help me with this? I already did the AD output but can’t connect to MySQL DB to make the INSERT.
Hi,
Awesome script and I have this working but I am unable to get the
‘SELECT last_insert_id()’
working so that I can return id of the row I just inserted. I need this so that I can insert this into other tables for references.
When I run an insert command the insert works. But when I try and grab the last inserted row it returns ‘System.Data.DataRow’
./Mysql.ps1 -Query “insert into foo (blah,blah,blah2) values (‘$one’,’$two’,’$three”)”
$id = ./Mysql.ps1 -Query “SELECT last_insert_id()”
Have you any idea how I can get the values of the last id?
change the line: $dataSet.Tables[“data”] | Format-Table
to –> return $dataSet.Tables[“data”]
then the answer set is returned as a system.array object…
Thank you!
Thanks a lot for posting this even though it was many years ago. :)
you’re welcome, happy it still helps people :)
Thank you for your posting, this works great locally. I am just wondering if the script still works for a remote server such as mywebsite.com. I am getting an error when I enter my remote server name instead of ‘localhost ‘ and not sure if this is a firewall issue or something else.