Run a SQL Script Against MySQL using Powershell

Posted by abarr on Stack Overflow See other posts from Stack Overflow or by abarr
Published on 2011-01-13T13:41:52Z Indexed on 2011/01/13 13:53 UTC
Read the original article Hit count: 252

Filed under:
|

I have a Powershell script that backs up my MySQL DB's each night using mysqldump. This all works fine but I would like to extend the script to update a reporting db (db1) from the backup of the prod db (db2). I have written the following test script but it does not work. I have a feeling the problem is the reading of the sql file to the CommandText but I am not sure how to debug.

[system.reflection.assembly]::LoadWithPartialName("MySql.Data")    
$mysql_server = "localhost"
$mysql_user = "root"
$mysql_password = "password"
write-host "Create coonection to db1"
# Connect to MySQL database 'db1'

$cn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$cn.ConnectionString = "SERVER=$mysql_server;DATABASE=db1;UID=$mysql_user;PWD=$mysql_password"
$cn.Open()
write-host "Running backup script against db1"
# Run Update Script MySQL 
$cm = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand
$sql = Get-Content C:\db2.sql
$cm.Connection = $cn
$cm.CommandText = $sql
$cm.ExecuteReader()
write-host "Closing Connection"
$cn.Close()

Any assistance would be appreciated. Thanks.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about powershell