Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / PowerShell / Run MySQL Queries from PowerShell

August 22, 2016 PowerShell

Run MySQL Queries from PowerShell

One of the useful PowerShell features is the opportunity to connect to databases on remote servers, including MySQL ones. Thus, you can address MySQL tables to access data directly from PowerShell console. In this article, we’ll deal with the examples of connection to MySQL DB from PowerShell script and some commands to read/write the data in database tables. To connect to a MySQL server, we need a special connector – MySQL .NET Connector, which can be downloaded from the official MySQL website.

By the time this article has been written, the latest available connector version was Connector/Net 6.9.9.

Note. It is not required to install the full version of MySQL .NET Connector, it’s enough to copy MySql.Data.dll to your computer.

Download mysql-connector-net-6.9.9.msi and install MySQL .NET Connector in the minimal configuration.

Setup MySQL .NET connector

In advance, create a database to work with on your MySQL server. All operations on the database server are performed from MySQL CLI command prompt, but you can use a graphic tool phpmyadmin or any other suitable utility.

Create aduser database:
mysql> CREATE DATABASE aduser;

On your MySQL server, create a separate user with the privilege to connect to aduser database remotely. Grant this user the privilege to connect to the database remotely from the IP address 10.1.1.195:

mysql>GRANT ALL PRIVILEGES ON aduser.* TO posh@'10.1.1.195' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

mysql create db ant grant privileges from powershell

Select the created database:

mysql> USE aduser;

And create the simplest table consisting from 3 columns: ID, AD username and e-mail address.

mysql> CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100), email VARCHAR(100), PRIMARY KEY (ID));

Go back to the server, from which we’ll connect to the MySQL database. Suppose, we want all names and e-mail addresses of the AD users to be shown in the table. You can get this information using Get-ADUser cmdlet.

The following PowerShell script allows to connect to the database and write the list of users and their e-mails obtained from AD.

Set-ExecutionPolicy RemoteSigned
#connect the library MySql.Data.dll
Add-Type –Path ‘C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.9\Assemblies\v4.5\MySql.Data.dll'
# database connection string, server — server name, uid - mysql user name, pwd- password, database — name of the database on the server
$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString='server=10.1.1.13;uid=posh;pwd=P@ssw0rd;database=aduser'}
$Connection.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection
#prepare a list of users from specific OU containing names and e-mail addresses
Import-Module activedirectory
$UserList=Get-ADUser -SearchBase ‘OU=Users,OU=Paris,DC=adatum,DC=com’ -filter * -properties name, EmailAddress
ForEach($user in $UserList)
{
$uname=$user.Name;
$uemail=$user.EmailAddress;
#write the information about each use to the database table
$sql.CommandText = "INSERT INTO users (Name,Email) VALUES ('$uname','$uemail')"
$sql.ExecuteNonQuery()
}
$Reader.Close()
$Connection.Close()

Powershell Write data to mysql db

The following script is used to read the data previously entered to the database and to display them in PowerShell console. We have displayed the fields containing the names and e-mail addresses of the users:

Set-ExecutionPolicy RemoteSigned
Add-Type –Path ‘C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.9\Assemblies\v4.5\MySql.Data.dll'
$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString='server=10.1.1.13;uid=posh;pwd=P@ssw0rd;database=aduser'}
$Connection.Open()
$MYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$MYSQLDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
$MYSQLDataSet = New-Object System.Data.DataSet
$MYSQLCommand.Connection=$Connection
$MYSQLCommand.CommandText='SELECT * from  users'
$MYSQLDataAdapter.SelectCommand=$MYSQLCommand
$NumberOfDataSets=$MYSQLDataAdapter.Fill($MYSQLDataSet, "data")
foreach($DataSet in $MYSQLDataSet.tables[0])
{
write-host "User:" $DataSet.name  "Email:" $DataSet.email
}
$Connection.Close()

posh: select query from mysql

In the next articles, we’ll consider the case of using MySQL database to collect and store information from Windows event logs ( Tracking Files Deletion using Audit Policy and MSSQL Database).

1 comment
0
Facebook Twitter Google + Pinterest
previous post
Restore Missing CD/DVD Drive in Windows 10
next post
Configuring Kerberos Authentication on IIS Website

Related Reading

Using Previous Command History in PowerShell Console

January 31, 2023

How to Install the PowerShell Active Directory Module...

January 31, 2023

Finding Duplicate E-mail (SMTP) Addresses in Exchange

January 27, 2023

How to Disable or Uninstall Internet Explorer (IE)...

January 26, 2023

How to Delete Old User Profiles in Windows?

January 25, 2023

1 comment

Paulo January 15, 2019 - 10:09 pm

Hi, I am learning my way around powershell and kind of new to trying to create my own script. I was kind of working my way around with your script and attempted write my own but I can’t seem to figure it out. I am basically trying to use powershell to query MySQL and check if Last_IO_Error, is Slave_IO_Running, Slave_SQL_Running and Seconds_Behind_Master=0 Would you happen to have any suggestions? Thanks Paul

Reply

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • Configure User’s Folder Redirection with Group Policy

    February 3, 2023
  • Using Previous Command History in PowerShell Console

    January 31, 2023
  • How to Install the PowerShell Active Directory Module and Manage AD?

    January 31, 2023
  • Finding Duplicate E-mail (SMTP) Addresses in Exchange

    January 27, 2023
  • How to Delete Old User Profiles in Windows?

    January 25, 2023
  • How to Install Free VMware Hypervisor (ESXi)?

    January 24, 2023
  • How to Enable TLS 1.2 on Windows?

    January 18, 2023
  • Allow or Prevent Non-Admin Users from Reboot/Shutdown Windows

    January 17, 2023
  • Fix: Can’t Extend Volume in Windows

    January 12, 2023
  • Wi-Fi (Internet) Disconnects After Sleep or Hibernation on Windows 10/11

    January 11, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • PowerShell Remoting via WinRM for Non-Admin Users
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top