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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[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.
Answer:
This line:
|
1 2 |
$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:|
1 2 3 4 5 |
$sql = Get-Content C:\db2.sql ... $OFS = "`r`n" $cm.CommandText = "$sql" |
Or if you’re on PowerShell 2.0:
|
1 2 |
$sql = (Get-Content C:\db2.sql) -join "`r`n" |