Homegrown PowerShell tools for MySQL

In my previous article I created a function that uses the MySQL connector for .Net to run MySQL queries that return as an object inside either a script or a shell. Now I’ll show you how to use that function as a base for a few other tools.

Lets start with a simple function to fetch the version of MySQL:

function get-mysqlVersion {
param ($server)
 $query = "select @@hostname as hostname, @@version as version;"
 $result = Start-mysqlQuery -Server $server -Query $query
 $result
}

Running against a few servers I can see which MySQL versions I have.

PS C:\> cat .\myservers.txt | %{ get-mysqlVersion $_ } | ft -a

hostname                version
--------                -------
mysqlserver04.local.com 5.6.23-enterprise-commercial-advanced-log
mysqlserver02.local.com 5.7.17-enterprise-commercial-advanced-log
mysqlserver03.local.com 5.7.17-enterprise-commercial-advanced-log
mysqlserver01.local.com 5.7.17-enterprise-commercial-advanced-log

Now I have a handy way to identify servers due for upgrade or patching. Let’s take another look at the $Query from my last article where we looked up users:

 
$query = "select user,host,password,@@hostname as hostname from mysql.user;"

And wrap that in a simple function to make a tool, just like the Version function:

function get-mysqlUsers {
 param ($server)
  $query = "select user,host,password,@@hostname as hostname from mysql.user;"
 Start-mysqlQuery $query $server | select User, Host, Password, Hostname
}
PS C:\> get-mysqlUsers mysqlserver04| ft -a

user    host      password                                  hostname
----    ----      --------                                  --------
root    localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com
myuid   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com
svcuser %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com

Now all this works great until we hit a server version 5.7 or newer:

PS C:\> get-mysqlUsers mysqlserver01 1| ft -a
ERROR : Unable to run query : mysqlserver01 select user,host,password,@@hostname as hostname from mysql.user;
PS C:\>

Well at least we know the try{} catch{} is working right, unfortunately it suppresses the real error message. To see what really happened we need to look at $error[0]

PS C:\> $error[0]
Exception calling "Fill" with "2" argument(s): "Unknown column 'password' in 'field list'"
At C:\myscripts\mysqlcrap.ps1:137 char:3
+         $RecordCount = $dataAdapter.Fill($dataSet, "dataSet")
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : MySqlException

Since the ‘password’ field doesn’t exist in versions 5.7 or later we have an issue. The same data does exist, though, in a new field called ‘authentication_string’. So what now? We could build a separate function to run only against newer versions… We could also check the MySQL version in the function and submit a query based on that:

function get-mysqlUsers {
param ($server)
 $ver = get-mysqlVersion $server
 if ($ver.version -match '5.7') {
  $query = "select user,host,authentication_string as password,@@hostname as hostname from mysql.user;"
 }
 else {
  $query = "select user,host,password,@@hostname as hostname from mysql.user;"
 }
 Start-mysqlQuery $query $server | select User, Host, Password, Hostname
}
PS C:\> get-mysqlUsers mysqlserver01 | ft -a

user      host      password                                  hostname
----      ----      --------                                  --------
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
mysql.sys localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com

Awesome! Now it works against a 5.7 server, but most importantly, I can run it against all my MySQL versions and get output all in the same table:

PS C:\> cat .\myservers.txt | %{ get-mysqlUsers $_ } | ft -a

user      host      password                                  hostname
----      ----      --------                                  --------
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
mysql.sys localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver01.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver02.local.com
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver02.local.com
mysql.sys localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver02.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver02.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
mysql.sys localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver03.local.com
root      localhost *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com
myuid     %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com
svcuser   %         *123456789ABCDE123456789ABCDE123456789ABC mysqlserver04.local.com

Using the Start-mysqlQuery and myCStr functions as a base, I was able to add two more commands to my MySQL toolbox for fetching version and user information. I hope you find this useful. If you do or if you have any problems or suggestions then let me know in a comment!

Advertisements
This entry was posted in powershell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s