tech.guitarrapc.cóm

Technical updates

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

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

SQL
PowerShellから MySQLにクエリ飛ばしたりとかやってました。 纏めて function作ったり、Trigger作ったりとかしたかったので。 1回作ると意外と楽だったのですが……周りからの変態扱いが酷くなったのでアレです。 とりあえず、ググったりしても国内外問わず纏まった形でのPowerShellでの実行公開例が意外とないのでよろしければどーぞ。 あ、AWS のRDSですが……時間がUST +0:00なのです。 これは、本記事のクエリで日本時間の現在時刻 (UST +9:00)を入れれるようになります。自分で入れる場合は、 jst_now 関数をたたくイメージです。 自動でかかる個所は、Triggerを張って InsertやUpdateのタイミングでjst_now 関数がかけています。 具体的なテーブルが無くて申し訳ないのですが、簡単な内容なので分かりますよね……ご参考程度にどうぞ。

GitHub公開

GitHubで公開しています。
GitHub -PowerShellUtil / PS-MySQLConnect /

概要

一連の動作を行うためのスクリプト集となります。
  1. MySQL Conenctionの生成
  2. Insert系の NonQuery
  3. Select/Update系の NonQuery
  4. MySQL Connectionの切断、破棄
  5. 複合させてのFunction作成
  6. 複合させてのTrigger

利用手順

Moduleとしてあります。 PowerShell Scriptを実行できる環境であれば、同梱されているinstall.batを管理者として実行すれば.ps1権限も確認/昇格しつつコピーできます。 もしできない場合は、PS-MySQLConnectを以下のパスにおいてください。 
“$env:userProfile\documents\WindowsPowerShell\Modules”
installerを使うと、こんな感じですね。 [office src="https://skydrive.live.com/embed?cid=D0D99BE0D6F89C8B&resid=D0D99BE0D6F89C8B%211400&authkey=ABcEfqfQLkGkx1Q" width="319" height="247"] うまくModuleに入れば、PowerShell ISE 3.0などでは、このようにImport-Module不要で自動的にModuleを走査して実行できるようにしてくれます。 [office src="https://skydrive.live.com/embed?cid=D0D99BE0D6F89C8B&resid=D0D99BE0D6F89C8B%211401&authkey=AGSbm9I8hK3kfz4" width="319" height="257"]

MySQL Connectionの生成

利用するCmdlet: 
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(テーブル風)に受け取らないものです。 利用するCmdlet: 
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 .... もいけちゃいます。 利用するCmdlet: 
Execute-MySQLReadQuery -Query "SELECT / UPDATE / USE / SHOW / .... " -Connection $connection
例えば、hogeテーブルから id が 5の行を取得するなら、 
Execute-MySQLReadQuery -Query "SELECT * FROM hoge where id = 5;" -Connection $connection

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

頻繁に使うので、専用のCmdletを作ってあります。 
Get-MySQLDatabases -Query "hogehoge"  -Connection $connection
Query を入れてもいいですが、入れなければ、自動的にホストの全SCHEMAとテーブルを取得するSQLが組まれています。 "select TABLE_SCHEMA as SchemaName,TABLE_NAME as TableName from information_schema.`TABLES`;" なお、 -SCHEMAは未実装です。

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

頻繁に使うので、専用のCmdletを作ってあります。 
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の切断、破棄

利用するCmdlet: 
Disconnect-MySQLConnection -connection $connection
MySQLに接続するためのパラメーターを渡すことで、Connectionが切断(Close())、破棄(Dispose())されます。 そのため、必ず破棄したい$Connectionなど適当な変数を渡して下さい。

複合させてのFunction作成

ここまでくれば、後はPowerShellでいかようにでもなります。 function生成のサンプルを作っています。 参考程度にどうぞ。 Function作成時の注意点は、DELIMITER指定が不要になるという事です。 利用するCmdletは、こちらです。 
Set-MySQLFunctionJstNow  -User "user" -Password "password" -HostAddress "IP か Domain名"
コマンド全文です。 内部で、接続、実行、破棄を行っています。 実行はべたうちなので、渡すのはConnectionにかかわるパラメータだけです。 今回の場合は、AWSが、UST 0: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指定が不要になるので注意です。 利用するCmdletは、こちらです。 
Set-MySQLTriggerCreatedModified -User "user" -Password "password" -HostAddress "IP か Domain名"
コマンド全文です。 内部で、接続、実行、破棄を行っています。 実行はべたうちなので、渡すのはConnectionにかかわるパラメータだけです。 今回の場合は、AWSがUST 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# の方がメリットが高いわけで。 アプリ書く程度じゃないんだけどど、サクッと、ちょいっと ででんと廻したい！という かるーい感じで利用されると共存できるかと。 では！

P.S.

Bad Exampleに入っているのは、コネクションと実行などを分けていない初期のです。 こういう悪い例はやっちゃだめなのでする…。 何しろ、接続もSQLも飽きれるほど酷い…… PowerShellで whereってるしねぇ……できるけど、DataTypeも返ってくるのは実際と違う方ですのでまともに使えません。 正に黒歴史です。