Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • 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 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 31, 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
Fixing High Memory Usage by Metafile on Windows Server 2008 R2
next post
Configuring Kerberos Authentication on IIS Website

Related Reading

Installing Language Pack in Windows 10/11 with PowerShell

September 15, 2023

Configure Email Forwarding for Mailbox on Exchange Server/Microsoft...

September 14, 2023

How to View and Change BIOS (UEFI) Settings...

September 13, 2023

How to Create UEFI Bootable USB Drive to...

September 11, 2023

Managing Windows Firewall Rules with PowerShell

August 31, 2023

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

  • How to Use Ansible to Manage Windows Machines

    September 25, 2023
  • Installing Language Pack in Windows 10/11 with PowerShell

    September 15, 2023
  • Configure Email Forwarding for Mailbox on Exchange Server/Microsoft 365

    September 14, 2023
  • How to View and Change BIOS (UEFI) Settings with PowerShell

    September 13, 2023
  • How to Create UEFI Bootable USB Drive to Install Windows

    September 11, 2023
  • Redirect HTTP to HTTPS in IIS (Windows Server)

    September 7, 2023
  • Add an Additional Domain Controller to an Existing AD Domain

    September 6, 2023
  • How to Install an SSL Certificate on IIS (Windows Server)

    September 5, 2023
  • Managing Windows Firewall Rules with PowerShell

    August 31, 2023
  • Fixing ‘The Network Path Was Not Found’ 0x80070035 Error Code on Windows

    August 30, 2023

Follow us

  • Facebook
  • Twitter
  • Telegram
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