Run a SQL Script Against MySQL using Powershell

Question

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.

I don't understand what the Mysql.Data part is (due to my ignorance of advanced Powershell, not because it's unclear) but wouldn't it be more straightforward to use the command-line mySQL client instead? — Pekka
– Pekka, Commented Jan 13, 2011 at 14:23
Pekka I am using Powershell so I can use Task Scheduler to run it each night. — abarr
– abarr, Commented Jan 13, 2011 at 20:16

Keith Hill · Accepted Answer · 2011-01-14 17:07:28Z

4

This line:

$sql = Get-Content C:\db2.sql

Returns an array of strings. When that gets assigned to something expecting a string then PowerShell will concatenate the array of strings into a single string using the contents of the $OFS (output field separator) variable. If this variable isn't set, the default separator is a single space. Try this instead and see if it works:

$sql = Get-Content C:\db2.sql 
...
$OFS = "`r`n"
$cm.CommandText = "$sql"

Or if you're on PowerShell 2.0:

$sql = (Get-Content C:\db2.sql) -join "`r`n"

edited Jan 14, 2011 at 17:07

answered Jan 13, 2011 at 17:17

Keith Hill

204k44 gold badges370 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

abarr Over a year ago

Thanks Keith. I tried the above (I am using PS 1) but get an exception :

abarr Over a year ago

Exception calling "ExecuteReader" with "0" argument(s): "Fatal error encountered during command execution." At C:\mysqlbackup\scripts\mysqlbackup.ps1:86 char:18 + $cm.ExecuteReader <<<< () + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

abarr Over a year ago

Keith, Sorry for the delay, was travelling for work and didn't get a chance to follow up. I have added it above. I added $OFS = "rn" after the Get-Content command. Thanks in advance.

Keith Hill Over a year ago

Looks like there's a SQL script error at line 86, column 18 of mysqlbackup.ps1. Anything odd going on at that location in your script?

Keith Hill Over a year ago

Try this $sql = [io.file]::ReadAllText('c:\db2.sql') to retrieve the script contents instead of Get-Content. At this point, $sql will contain the exact script contents as a single string.

|

Collectives™ on Stack Overflow

Run a SQL Script Against MySQL using Powershell

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related