Powershell. Запросы к MySQL
08 Sep 2017
local_offer
powershell
Функция выполнения MySQL запроса
function Invoke-MySQLQuery {
Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$query,
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$connectionString
)
try {
# load MySQL driver and create connection
Write-Verbose "Create Database Connection"
# You could also could use a direct Link to the DLL File
$mySQLDataDLL = "C:\ProgramData\icinga2\Scripts\icinga2\MySQL.Data.dll"
[void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL)
#[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$connection.ConnectionString = $ConnectionString
Write-Verbose "Open Database Connection"
$connection.Open()
# Run MySQL Querys
Write-Verbose "Run MySQL Querys"
$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["data"] # | Format-Table
}
catch {
Write-Host "Could not run MySQL Query" $Error[0]
}
Finally {
Write-Verbose "Close Connection"
$connection.Close()
}
} #Конец функции Invoke-MySQLQuery
Пример работы с фунцией:
$connString = "Server=192.168.0.209;Uid=dbuser;Pwd=password;database=inventory;charset=utf8"
$ComputerUUID = "00000000-0000-0000-0000-000000000000-00000000-0000-0000-0000-000000000000"
$sQLquery = "SELECT Name, ComputerTargetId FROM tbComputerTarget WHERE ComputerTargetId='$ComputerUUID'"
$rQLquery = Invoke-MySQLQuery -connectionString $connString -query $sQLquery
Проблема. В версии MySQL 5.7 возникала ошибка
PS C:\Scripts> .\sync_osticket_db.ps1 "pc030.domain.local"
Could not run MySQL Query Исключение при вызове "Open" с "0" аргументами: "SELECT command denied to user 'dbuser'@'host' for table 'session_variables'"
Решение. Дать пользователю, которым выполняется запрос, права на следующие таблицы
GRANT SELECT ON performance_schema.session_variables TO dbuser; GRANT SELECT ON performance_schema.session_status TO dbuser;