PowerShell How-To

Querying MySQL Databases with PowerShell

Save some time by automatically querying up data.

• By Adam Bertram
• 03/02/2016

MySQL is a common database amongst many organizations. As such, when building an automation script that needs to query data from somewhere, you might run into the need to query a MySQL database. By using PowerShell and by taking a shortcut and using an existing MySQL module, you can make this happen.

To query a MySQL database, I'm going to assume you already have a Windows MySQL server and a database with at least one table in it you'd like to retrieve. I'm also going to assume that you don't manage this server from the server itself but rather from client software like MySQL Workbench, which allows you to connect to your MySQL instance remotely.

Installing the Prerequisite Software
Unfortunately, PowerShell doesn't come equipped out of the box with support for MySQL. We have to download and install some software one time just like you did with the MySQL Workbench.

The software required to interact with MySQL via PowerShell is called Connector/Net. This is an ADO driver that PowerShell can hook into to manage MySQL databases. If you're working on a machine that already has the fully-featured MySQL Workbench software you won't have to install this. The install is straightforward allowing you to just accept the defaults.

Once the ADO driver has been installed, you'll then need to download some PowerShell code that's already been created to do this. You will be using a PowerShell module called MySQL. This module consists of various commands that you can use to manage MySQL databases. In this example, we're only going to be working with a couple of the functions which you will see momentarily.

MySQL PowerShell Module
Without leaving PowerShell, you can download this PowerShell module using the Invoke-WebRequest cmdlet. In this example, I will be downloading a file called master.zip from a Github repository to a file called MySQL.zip located on the root of C:\.

Invoke-WebRequest  -Uri https://github.com/adbertram/MySQL/archive/master.zip -OutFile  'C:\MySQL.zip'

Once the MySQL module is downloaded, you'll then need unzip it into a folder where PowerShell can auto-load it. I will be using the C:\Program Files (x86)\WindowsPowerShell\Modules folder.

$modulesFolder =  'C:\Program Files\WindowsPowerShell\Modules'

  Expand-Archive -Path  C:\MySql.zip -DestinationPath $modulesFolder

Because Github attaches the branch name to the download, we'll need to remove the "-master" from the end of the folder.

Rename-Item -Path  "$modulesFolder\MySql-master" -NewName MySQL

Next, we need a way to pass a username and password to the MySQL server. A secure way to do this is to use PowerShell's Credential object which stores both the username and password in an encrypted state. I will use the Get-Credential cmdlet to assign a credential to the $dbCred variable.

$dbCred =  Get-Credential

Next, I'll need to make a connection to the MySQL server using the username and password we specified earlier. Here I'm specifying a MySQL server named MYSQLSERVER connecting to a database called SynergyLogistics.

Connect-MySqlServer  -Credential $dbcred -ComputerName 'MYSQLSERVER' -Database SynergyLogistics

Once the connection is made, it's always a good idea to run a simple SELECT query to ensure we can pull information from the database. I'll be using a table called Users inside my database so I'll run a simple SELECT query against it using the Invoke-MySqlQuery function.  Invoke-MySqlQuery is a cmdlet that comes with the MySQL connector you downloaded earlier. This should return all of the rows inside of my table.

Invoke-MySqlQuery  -Query 'SELECT * FROM Users'

You'll see above that I'm showing two rows returned from my Users table.

You can now query MySQL database tables. However, by using the MySQL module downloaded and installed from above you can also do a few other things as well. To see all of the other commands you now have available, you can use the Get-Command function with the Module parameter to list all of the commands available inside of the MySQL module.

You should now have the ability to perform much of the common functionality necessary to not only query MySQL databases from PowerShell but also to create databases, tables and users as well.