Powershell Invoke-MySQL

Question

Having some problems getting MySQL queries working from powershell, here is my code:

function Invoke-MySQL {
Param(
  [Parameter(
  Mandatory = $true,
  ParameterSetName = '',
  ValueFromPipeline = $true)]
  [string]$Query
  )

$MySQLAdminUserName = 'USER'
$MySQLAdminPassword = 'PASSWORD'
$MySQLDatabase = 'DATABASE'
$MySQLHost = 'MYSQLSERVER.mydomain.local'
$ConnectionString = "server=" + $MySQLHost + "; port=3306; uid=" + $MySQLAdminUserName + "; pwd=" + $MySQLAdminPassword + "; database="+$MySQLDatabase

Try {
  [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
  $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
  $Connection.ConnectionString = $ConnectionString
  $Connection.Open()

  $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
  $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
  $DataSet = New-Object System.Data.DataSet
  $RecordCount = $dataAdapter.Fill($dataSet, "data")
  $DataSet.Tables[0]
  }

Catch {
  throw "ERROR : Unable to run query : $query `n$Error[0]"
 }

Finally {
  $Connection.Close()
  }
 }

Invoke-MySQL -Query "select * from ImaginaryTable"

The problem occurs on the $Connection.Open() command with the following error:

ERROR : Unable to run query : select * from ImaginaryTable
Exception calling "Open" with "0" argument(s): "Unable to connect to any of the specified MySQL h
osts."[0]

Anyone that can help me? :)

Are you able to connect to that server using workbench on the same machine, it could be a firewall blocking port 3306? — David Martin
– David Martin, Commented May 19, 2014 at 9:41
You can look at this script module for some ideas. Made my work life a breeze. github.com/skukx/PSMySQL — skukx
– skukx, Commented May 12, 2016 at 23:23
This module looks pretty legit too: github.com/adbertram/MySQL/blob/master/MySQL.psm1 — Kellen Stuart
– Kellen Stuart, Commented Oct 25, 2016 at 19:21

Adeel ASIF · Accepted Answer · 2014-05-19 10:23:18Z

2

There is no error in the script, I think your server is not accesible on port 3306.

Please make sure that you don't have firewall rules blocking this port.

Maybe your mysql server is only listening from localhost ip address.

Assuming you're on linux mysql server follow the steps below :

Open this file /etc/mysql/my.cnf, and change bind-address value from 127.0.0.1 to the corresponding IP address of your network card (example : 192.168.0.50 or external ip if your server is hosted outside).

Then restart your mysql server

/etc/init.d/mysql restart

After mysql restart execute this query :

GRANT ALL PRIVILEGES ON *.* TO 'USER'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Now you'll be able to query your Database via Powershell.

edited May 19, 2014 at 10:23

answered May 19, 2014 at 10:17

Adeel ASIF

3,57410 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell Invoke-MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related