How to Execute MYSQL Queries in PowerShell and Export Results to CSV

This post was created to show how to execute MYSQL Database queries using scripts written in PowerShell ISE. In this example, the connection details for the SQL instance are being retrieved from a CSV, and the query is being executed. Once the query is executed, the results of the query are being outputted to a CSV file for consumption by another application. 

Prerequisites

  • TLS 1.2 is installed
  • PowerShell ISE 5.0 or higher is being used
  • Internet Connection is available
  • Account with Local Admin privileges is being used to execute script
  • NuGet Package Library is installed (at least version: 2.8.5.201 – Requires TLS 1.2 to be enabled)
  • Firewall access from Windows Machine to MYSQL Database is open
  • Remote Access allowed on MYSQL Server (Default security when installing MYSQL disables this)
  • Enable MYSQL Native Authentication

Installation Tasks and Steps

Task 1 – Install and Import the MYSQL Module 

This first task will import the MYSQL module into PowerShell. The Module contains certain commands and parameters that need to be executed to complete this script. Follow the below instructions to install the module:

  1. Launch the PowerShell ISE as an Administrator
  2. Execute the following command (The module, named SimplySql, can be searched for and downloaded via the Internet or found here:
    https://www.powershellgallery.com/packages/SimplySql/1.6.2)


Task 2 – Create CSV File with Connection Details (Optional if CSV is not the required input method)

  1. Open a text editor to create the CSV file
  2. Create header columns with the connection details as a header
    • Below is an example:
      1. SQL Server Hostname:
      2. Database:
      3. Port:
      4. User ID:
      5. Password:
  3. Save the file in a directory that can be navigated to as a CSV file

Task 3 – Complete the Script to execute the command

This script will execute the SQL Query and export it to a CSV file for consumption:

Conclusion

To conclude this article, this script is meant to allow an Administrator to execute MYSQL queries against a SQL instance using PowerShell and export the results to a CSV file. This query is getting a list of all the users authenticated to a particular SQL database. This script should work with almost any query that can be executed.

When utilizing this script, please remember to check the same query in SQL Developer first to ensure that the results that return are the expected results. User permissions and database rights still apply when using this script.

Looking for additional help with querying the MYSQL DB server for users using Powershell? ISX is an elite IAM security firm that offers boundless expertise in a range of cybersecurity and business process services, including Powershell. Take your interoperability to the next level, and contact an ISX consultant today.

ISXHow to Execute MYSQL Queries in PowerShell and Export Results to CSV

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *