How to import .sql file into MySQL using PowerShell script?

Question

I'm trying to import a dump file (.sql file containing CREATE/DROP/INSERT commands) into a remote MySQL server from a windows environment. I've got this PowerShell script to do the job for me by using "MySQL shell utility" functionalities installed in my environment

Set-ExecutionPolicy RemoteSigned
Add-Type –Path 'C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.11\Assemblies\v4.5.2\MySql.Data.dll'
$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString='server=192.168.100.100;Encrypt=false;uid=myUser;pwd=myPass;database=myDB;'}
$Connection.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection
$sql.CommandText = "SOURCE D:/MySQL_Dumps/myFile.sql"
$sql.ExecuteNonQuery()
$Reader.Close()
$Connection.Close()

I can connect to the server and execute basic SELECT queries, using ExecuteNonQuery()/ExecuteScalar()/ExecuteReader() methods, but receiving an error for this specific SOURCE command:

Exception calling "ExecuteNonQuery" with "0" argument(s): "You have an error
in your SQL syntax; check the manual  that corresponds to your MySQL server
version for the right syntax to use near 'SOURCE
D:/MySQL_Dumps/myFile.sql' at  line 1" At line:8 char:1
+ $sql.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : MySqlException

I tried several alternatives to enter the path, like using double slashes or double backslashes but encountered the same error. Can anyone help on this?

Answer 1

./mysql -u root -p DatabaseName -e "source C:\path\to\my_sql_file.sql"

Answer 2

I know this thread is a little bit old, but it is the top one when searching about this issue on Google, so I'm going to put my pennies on the pot.

I ran into the same issue in a project, and after using the snippet

$sql.CommandText = Get-Content D:\MySQL_Dumps\myFile.sql -Raw

I got issues with scripts using custom DELIMITER characters (.sql files containing stored procedures, triggers and user defined functions).

After some research, I found that there is the class MySql.Data.MySqlClient.MySqlScript that handles .sql files/scripts execution. According with this thread, from connector version 5.2.8 onward, custom DELIMITER problem does not exist anymore. So, what worked for me was the following snippet:

$Script = New-Object MySql.Data.MySqlClient.MySqlScript($MySqlConn)
$Script.Query = Get-Content $ScriptPath -Raw
$Script.Execute() | Out-Null

I am currently using MySql.Data.dll version 8.0.25.