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? :)

Solution

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.