使用Powershell对MySQL运行SQL脚本

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.

我有一个Powershell脚本，每晚使用mysqldump备份MySQL DB。这一切都很好，但是我想扩展脚本，从prod db (db2)的备份中更新报告db (db1)。我已经编写了下面的测试脚本，但是它不起作用。我有一种感觉，问题是将sql文件读入命令文本，但我不知道如何调试。

[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.

如有任何帮助，我们将不胜感激。谢谢。

1 个解决方案

#1

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:

返回字符串数组。当它被分配给某个期待字符串的对象时，PowerShell将使用$OFS(输出字段分隔符)变量的内容将字符串数组连接到单个字符串中。如果未设置此变量，则默认分隔符为单个空间。试试这个，看看是否有效:

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

Or if you're on PowerShell 2.0:

或者如果你使用PowerShell 2.0:

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

#1