以下の内容はhttps://tech.guitarrapc.com/entry/2013/05/02/230551より取得しました。

最終更新日

PowerShell で MySQL に接続するスクリプト集を作ってみた

SQL

PowerShellからMySQLにクエリ飛ばしたりとかやってました。 まとめてfunction作ったり、Trigger作ったりとかしたかったので。

1回作ると意外と楽だったのですが……周りからの変態扱いが酷くなったのでアレです。 とりあえず、ググったりしても国内外問わずPowerShellの実行公開例が意外とないので、参考程度にどうぞ。

あ、AWSのRDSですが……時間がUTC +0:00です。 これは、本記事のクエリで日本時間の現在時刻 (UTC +9:00)を入れられるようになります。自分で入れる場合は、 jst_now関数をたたくイメージです。 自動でかかる個所は、Triggerを張ってInsertやUpdateのタイミングでjst_now関数がかけています。

具体的なテーブルが無くて申し訳ないのですが、簡単な内容なので分かりますよね……ご参考程度にどうぞ。

概要

一連の動作のスクリプト集となります。

  • MySQL Conenctionの生成
  • Insert系のNonQuery
  • Select/Update系のNonQuery
  • MySQL Connectionの切断、破棄
  • 複合させてのFunction作成
  • 複合させてのTrigger

GitHubで公開しています。

PowerShellUtil/PS-MySQLConnect | GitHub

利用手順

Moduleとしてあります。

PowerShell Scriptを実行できる環境であれば、同梱されているinstall.batを管理者として実行すれば.ps1権限も確認/昇格しつつコピーできます。 もしできない場合は、PS-MySQLConnectを以下のパスにおいてください。

"$env:userProfile\documents\WindowsPowerShell\Modules"

うまくModuleにいれれば、Import-Module不要で自動的にModuleを走査して実行されます。

MySQL Connectionの生成

利用するコマンドレット:

New-MySQLConnection -User "user" -Password "password" -HostAddress "IP か Domain名"

MySQLに接続するためのパラメーターを渡すことで、Connectionが作成されます。 別途破棄するまでは、このコネクションを利用してクエリを実行したりします。 そのため、必ずNew-MySQLConnectionの実行結果は、$Connectionなど適当な変数で受け取って下さい。

$connection = New-MySQLConnection -User "user" -Password "password" -HostAddress "IP か Domain名"

接続パラメータは、パラメータ名が一致していればこのようにも渡せます。

$parameters = @{
  User = "user"
  Password = "passowrd"
  Hostaddress="IPAddress or NetBIOS"
}
$connection = New-MySQLConnection @parameters

これを利用すれば、複数ホストへの実行もこのように書けます。

$HostAddress = @(
  "host1"
  "host2"
  "host3"
  "host4"
  "host5"
  "host6"
  "host7"
)

$HostAddress | %{
  $parameters = @{
    User = "user"
    Password = "passowrd"
    HostAddress=$_
  }
  $parameters
} | %{ $connection = New-MySQLConnection @parameters }

Insert系の NonQuery

MySQLへの接続で得た$Connection変数を受けて、Queryを実行できます。 この時のQueryは、 InsertやCreate、Deleteなど結果をDataset(テーブル風)に受け取らないものです。

利用するコマンドレット

Execute-MySQLNonQuery -Query "DELETE / INSERT / CREATE .....;" -Connection $connection

例えば、hogeテーブルからidが5の行を削除するクエリならこのように。

Execute-MySQLNonQuery -Query "DELETE FROM hoge WHERE id = 5;" -Connection $connection

Queryは唯のStringなので、どこかのファイルから読んだり、PowerShellで生成した数字や文字を当て込んだりも自由です。

Select/Update系の NonQuery

同様にMySQLへの接続で得た$Connection変数を受けて、Queryを実行できます。 この時のQueryは、Selectなど、結果をDataset(テーブル風)に受け取るものですが、Update、Change DB、SHOW .... もいけちゃいます。

利用するコマンドレット:

Execute-MySQLReadQuery -Query "SELECT / UPDATE / USE / SHOW / .... " -Connection $connection

例えば、hogeテーブルからidが5の行を取得するなら、

Execute-MySQLReadQuery -Query "SELECT * FROM hoge where id = 5;" -Connection $connection

亜種 : 対象ホストの全SCHEMAの取得

頻繁に使うので、専用のコマンドレットを作ってあります。

Get-MySQLDatabases -Query "hogehoge"  -Connection $connection

Queryを入れてもいいですが、入れなければ、自動的にホストの全SCHEMAとテーブルを取得するSQLが組まれています。 "select TABLE_SCHEMA as SchemaName,TABLE_NAME as TableName from information_schema.TABLES;"

なお、 -SCHEMAは未実装です。

亜種 : 対象ホストの全カラム取得

頻繁に使うので、専用のコマンドレットを作ってあります。

Get-MySQLColumns -Query "hogehoge"  -Connection $connection

Queryを入れてもいいですが、入れなければ、自動的にホストの全SCHEMAとTABLE名とCOLUMNを取得するSQLが組まれています。

SELECT
  column_name AS COLUMNNAME,
  data_type AS DATATYPE,
  is_nullable AS IsNullable,
  column_default AS COLUMNDEFAULT,
  table_name AS TABLENAME,
  Table_schema AS SCHEMANAME,
  COLUMN_COMMENT
FROM
  INFORMATION_SCHEMA.COLUMNS

なお、 -Schemaと-Tableは未実装です。

MySQL Connectionの切断破棄

利用するコマンドレット:

Disconnect-MySQLConnection -connection $connection

MySQLに接続するためのパラメーターを渡すことでコネクションが切断(Close())、破棄(Dispose())されます。 そのため、必ず破棄したい$Connectionなど適当な変数を渡して下さい。

複合させてのFunction作成

ここまでくれば、後はPowerShellでいかようにでもなります。 function生成のサンプルを作っています。Function作成時の注意点は、DELIMITER指定が不要になるという事です。

利用するコマンドレットはこちらです。

Set-MySQLFunctionJstNow  -User "user" -Password "password" -HostAddress "IP か Domain名"

コマンド全文です。内部で、接続、実行、破棄しています。 実行はべたうちなので、渡すのはConnectionにかかわるパラメータだけです。

今回の場合は、AWSがUTC0:00なため、日本時刻にするためには+9:00します。現在時刻から自動的に算出した値を返す関数を作っています。

実行内容は直接触る必要があって非常にアレですが、参考ということで。

#Requires -Version 2.0

function Set-MySQLFunctionJstNow{

  Param(
    [Parameter(
    Mandatory = $false,
    ParameterSetName = '',
    ValueFromPipeLinebyPropertyName = '',
    ValueFromPipeline = $true)]
    [string]
    $User,

    [Parameter(
    Mandatory = $false,
    ParameterSetName = '',
    ValueFromPipeLinebyPropertyName = '',
    ValueFromPipeline = $true)]
    [string]
    $Password,

    [Parameter(
    Mandatory = $false,
    ParameterSetName = '',
    ValueFromPipeLinebyPropertyName = '',
    ValueFromPipeline = $true)]
    [string]
    $HostAddress
  )

  begin
  {
    Write-Verbose "Loading $PSCommandPath ."
  }
  process
  {
    Write-Verbose "Creating Paramters for connection."
    $parameters = @{
    user = $User
    Password = $Password
    hostaddress = $HostAddress
    }

    Write-Verbose "Opening connection"
    $connection = New-MySQLConnection @parameters

    Write-Verbose "Load query to get Schema_Name from infomration_schema, and run query."
    $query = "SELECT TABLE_SCHEMA AS SCHEMANAME,TABLE_NAME AS TABLENAME, ENGINE, TABLE_COMMENT FROM INFORMATION_SCHEMA.`TABLES` WHERE TABLE_NAME LIKE '%_MASTERS';"
    $result = Get-MySQLDatabases -Query $query -Connection $connection

    Write-Verbose "Sort Schema and get unique."
    $schemas = $result.SchemaName | sort -Unique

    Write-Verbose "Define Function query for jst_now"
    $queryFunction = "
DROP FUNCTION IF EXISTS jst_now;
CREATE FUNCTION jst_now()
  RETURNS datetime
  DETERMINISTIC
  NO SQL
  SQL SECURITY Definer
  COMMENT 'get jst time, ust+9:00'
BEGIN
  return CONVERT_TZ(CURRENT_TIMESTAMP(),'+00:00','+09:00');
END"

    Write-Verbose "Start foreach schemas."
    foreach ($schema in $Schemas){

      Write-Host "Change Database to $schema " -ForegroundColor Green
      Invoke-MySQLReadQuery -Query "use $schema ;" -Connection $connection

      Write-Host "Executing create function query to $Schema" -ForegroundColor Green
      Invoke-MySQLNonQuery -Query $queryFunction -Connection $connection

      Write-Host "Executing show function status query." -ForegroundColor Green
      Invoke-MySQLReadQuery -Query "show function status;" -Connection $connection

      }
  }

  end
  {
    Disconnect-MySQLConnection -connection $connection
  }

}

実行はたったこれだけ。

$HostAddress = @(
  "host1"
  "host2"
  "host3"
  "host4"
  "host5"
  "host6"
  "host7"
)

$HostAddress `
  | %{
    $parameters = @{
    User = "user"
    Password = "passowrd"
    HostAddress=$_
    }
    $parameters} `
  | %{ Set-MySQLFunctionJstNow @parameters }

複合させてのTrigger

同様にTrigger生成のサンプルを作っています。 参考程度にどうぞ。

TriggerもDELIMITER指定が不要になるので注意です。

利用するコマンドレットは、こちらです。

Set-MySQLTriggerCreatedModified -User "user" -Password "password" -HostAddress "IP か Domain名"

コマンド全文です。内部で、接続、実行、破棄しています。 実行はべたうちなので、渡すのはConnectionにかかわるパラメータだけです。

今回の場合は、AWSがUTC 0:00なため、日本時刻にするためには +9:00します。 これを、処理負担の低いMaster系だけDBのTriggerでやっちゃえっていう事です。

実行内容は直接触る必要があって非常にアレですが、参考ということで。 後、余計なforeachが廻っているのは気付いていますが、後で直しますってことで。

#Requires -Version 2.0

function Set-MySQLTriggerCreatedModified{

  Param(
    [Parameter(
    Mandatory = $false,
    ParameterSetName = '',
    ValueFromPipeLinebyPropertyName = '',
    ValueFromPipeline = $true)]
    [string]
    $User,

    [Parameter(
    Mandatory = $false,
    ParameterSetName = '',
    ValueFromPipeLinebyPropertyName = '',
    ValueFromPipeline = $true)]
    [string]
    $Password,

    [Parameter(
    Mandatory = $false,
    ParameterSetName = '',
    ValueFromPipeLinebyPropertyName = '',
    ValueFromPipeline = $true)]
    [string]
    $HostAddress
  )

  begin
  {
    Write-Verbose "Loading $PSCommandPath ."
  }
  process
  {
    Write-Verbose "Creating Paramters for connection."
    $parameters = @{
    user = $User
    Password = $Password
    hostaddress = $HostAddress
    }

    Write-Verbose "Opening connection"
    $connection = New-MySQLConnection @parameters

    Write-Verbose "Load query to get Schema_Name from infomration_schema, and run query."
    $Query = "SELECT column_name AS COLUMNNAME, data_type AS DATATYPE, is_nullable AS IsNullable, column_default AS COLUMNDEFAULT, table_name AS TABLENAME, Table_schema AS SCHEMANAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%_MASTERS'"
    $result = Get-MySQLColumns -Query $Query -Connection $connection | where datatype -eq "datetime"

    Write-Verbose "Sort Schema and get unique."
    $Schemas = $result | sort SchemaName -Unique

    Write-Verbose "Start foreach schemas."
    foreach ($schema in $Schemas){

      Write-Verbose "Change Database to $schema "
      Invoke-MySQLReadQuery -Query "use $($Schema.SchemaName) ;" -Connection $connection

      Write-Verbose "Load query to get Table_Name from infomration_schema, and run query."
      $Query = "SELECT column_name AS COLUMNNAME, data_type AS DATATYPE, is_nullable AS IsNullable, column_default AS COLUMNDEFAULT, table_name AS TABLENAME, Table_schema AS SCHEMANAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%_MASTERS'"
      $result = Get-MySQLColumns -Query $Query -Connection $connection  | where datatype -eq "datetime" | where {($_.columnName -like "crea*") -or ($_.columnName -like "mod*")}

      Write-Verbose "Sort Table and get unique."
      $Tables = $result | where { $_.schemaname -eq $schema.SchemaName} | sort TableName -Unique

      Write-Verbose "Start foreach tables."
      foreach ($table in $Tables)
      {

        Write-Verbose "Load query to get COLUMN_Name from infomration_schema, and run query."
        $Query = "SELECT column_name AS COLUMNNAME, data_type AS DATATYPE, is_nullable AS IsNullable, column_default AS COLUMNDEFAULT, table_name AS TABLENAME, Table_schema AS SCHEMANAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%_MASTERS'"
        $result = Get-MySQLColumns -Query $Query -Connection $connection  | where datatype -eq "datetime"

        Write-Verbose "Obtain only current Table Name Columns."
        $Columns = $result | where { $_.schemaname -eq $schema.SchemaName} | where { $_.TableName -eq $table.TableName}

        Write-Verbose "where cruese for target column."
        $created = $Columns | where {$_.columnName -like "crea*"}
        $modified = $Columns | where {$_.columnName -like "mod*"}

        Write-Verbose "Define Tigger query for Insert and Update"
        $TriggerNameInsert = $table.TableName + "_insert_time"
        $TriggerNameUpdate = $table.TableName + "_update_time"

        $queryInsertTrigger = "
DROP TRIGGER IF EXISTS $TriggerNameInsert;
CREATE TRIGGER $TriggerNameInsert BEFORE INSERT ON $($table.TableName)
FOR EACH ROW BEGIN
  SET NEW.$($created.ColumnName) = jst_now();
  SET NEW.$($modified.ColumnName) = jst_now();
END;"

        $queryUpdateTrigger = "
DROP TRIGGER IF EXISTS $TriggerNameUpdate;
CREATE TRIGGER $TriggerNameUpdate BEFORE UPDATE ON $($table.TableName)
FOR EACH ROW BEGIN
  SET NEW.$($modified.ColumnName) = jst_now();
END;"

        Write-Host "Executing create Insert Trigger query for $($Schema.SchemaName).$($table.TableName).$($created.ColumnName) / $($modified.ColumnName)"  -ForegroundColor Green
        Invoke-MySQLNonQuery -Query $queryInsertTrigger -Connection $connection

        Write-Host "Executing create Update Trigger query for $($Schema.SchemaName).$($table.TableName).$($created.ColumnName) / $($modified.ColumnName)" -ForegroundColor Green
        Invoke-MySQLNonQuery -Query $queryUpdateTrigger -Connection $connection

      }

    }
  }
  end
  {
    Disconnect-MySQLConnection -connection $connection
  }

}

実行はたったこれだけ。

$HostAddress = @(
  "host1"
  "host2"
  "host3"
  "host4"
  "host5"
  "host6"
  "host7"
)


$HostAddress | %{
    $parameters = @{
      User = "user"
      Password = "passowrd"
      HostAddress=$_
    }
    $parameters} | %{ Remove-MySQLTriggerCreatedModified @parameters }

まとめ

正直PowerShellでやるならC# 書いた方がどう考えてもいいような気が……まぁ、-Connection渡しておいて、 jsonや好きな形でひょいひょい加工できるのが楽なのはあります。 ただ、実装しちゃえばC# の方がメリット高いわけで。

アプリ書く程度じゃないんだけどど、サクッと、ちょいっとででんと廻したい！ というかるーい感じで利用されると共存できるかと。

追記

Bad Exampleに入っているのは、コネクションと実行などを分けていない初期のです。 こういう悪い例はやっちゃだめです。



以上の内容はhttps://tech.guitarrapc.com/entry/2013/05/02/230551より取得しました。
このページはhttp://font.textar.tv/のウェブフォントを使用してます
不具合報告/要望等はこちらへお願いします。
モバイルやる夫Viewer Ver0.14