Run a SQL Script Against MySQL using Powershell
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"
abarr
Updated on June 12, 2022Comments
-
abarr almost 2 years
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.
-
abarr over 13 yearsThanks Keith. I tried the above (I am using PS 1) but get an exception :
-
abarr over 13 yearsException 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 13 yearsKeith, 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 = "
r
n" after the Get-Content command. Thanks in advance. -
Keith Hill over 13 yearsLooks like there's a SQL script error at
line 86, column 18
ofmysqlbackup.ps1
. Anything odd going on at that location in your script? -
abarr over 13 yearsKeith, it is the Execute call for the SQL Command. It only happens when I use $OFS. I have checked the script and the changes I made are in the .sql file and when I run it I pipe it to screen and I can see the sql but for some reason it does not reflect the changes (I added a table to the original DB).
-
Keith Hill over 13 yearsTry 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.