I’ve created a small module for Powershell to connect and interact with a MySQL Database and Wamp services. You can probably find several other versions around the web, I am just trying to keep things simple and in line with my coding style. As I’ve stated before most of my posts will be focused on system administration tools/tips/tricks, and this is one of my favorite tools!
Here’s the project on GitHub : PowerWamp
Why Powershell and MySQL?
Many times I’m collecting a bunch of data from a system (or multiple systems) and need to save the data as well as share that data with other team members. Text files and CSV’s don’t always cut the mustard. Using this Powershell module I’m able to quickly gather data from many systems and insert that data into a small MySQL database. This is also great for keeping historical data for a script you may run each day, or another set schedule. In my day job I routinely setup quick and dirty PHP pages to display the data that I am retrieving. The WAMP stack is a great simple tool for System administrators and this module is essential for the Powershell integration.
I’m sure some System Administrators probably cringe a bit with the idea of interacting with a database as part of their daily routine. I can admit it’s a bit intimidating at first, it’s a whole new language to learn! Understanding tables, foreign keys, primary keys, updates, selects, Joins, etc. Oh what a learning curve it is! Here is a great resource to get started: w3schools.
I can assure it’s worth your time. I have a few open source projects that I’m working on that I believe will be extremely useful to system administration automation. Hopefully over the next few months I’ll begin to share them with you. I’ve already started working on my next Blog post; ‘Getting started with PowerWamp’, which should hit the web very soon!
Here is a quick peak at using the tool, This data set will not mean much to you. My Getting started blog post will provide you with code you can actually use.
I ran the following commands against a database I had on my machine to show how simple this is:
Import-Module .\powerWamp.psm1 $query = "select * from rts_properties" Invoke-MySQLQuery -Query $query -MySQLUsername root -MySQLPassword " " -MySQLDatabase summitrts -MySQLServer localhost $query = "insert into rts_properties (Name,Val) VALUES ('I love PowerWamp','Download it Today!');" $MyConnectionString = "server=localhost;port=3306;uid=root;pwd=;database=summitrts" $lastInsertId = @(Invoke-MySQLInsert -Query $query -ConnectionString $MyConnectionString) $lastInsertId $query = "select * from rts_properties" Invoke-MySQLQuery -Query $query -ConnectionString $MyConnectionString remove-module Powerwamp
Here is a screenshot. Notice the new row I added in the second collection, and it only a takes few lines of Powershell!
Did you notice I changed my connection method? The second method lends itself nicely to a Powershell program this is running many queries over and over.
Starting/Stopping Wamp Services is simple too!
import-module .\powerWamp.psm1 Invoke-Wamp -action start -Service apache Invoke-Wamp -action start -Service mysql
I’m always interested in feedback or feature requests.