mysql - Powershell 调用-MySQL

Question

在从 powershell 获取 MySQL 查询时遇到一些问题，这是我的代码:

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"

问题出现在 $Connection.Open() 命令上，错误如下:

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]

谁能帮帮我？ :)

Answer 1

脚本没有错误，我认为你的服务器无法在端口 3306 上访问。

请确保您没有阻止此端口的防火墙规则。

也许你的 mysql 服务器只监听本地主机的 ip 地址。

假设您在 linux mysql 服务器上，请按照以下步骤操作:

打开这个文件/etc/mysql/my.cnf，将bind-address的值从127.0.0.1修改为你网卡对应的IP地址(例子: 192.168.0.50 或外部 ip(如果您的服务器托管在外部)。

然后重启你的mysql服务器

/etc/init.d/mysql restart

mysql重启后执行这个查询:

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

现在您将能够通过 Powershell 查询您的数据库。