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?

$sql.CommandText = Get-Content D:\MySQL_Dumps\myFile.sql -Raw — iRon
– iRon, Commented Jul 16, 2018 at 8:59
Please elaborate. Do you get the same error? What is in the D:\MySQL_Dumps\myFile.sql file? Please, add this information into the question (especially if you get a different error now). — iRon
– iRon, Commented Jul 16, 2018 at 10:47
I'm getting exactly the same error; file contents are valid as it works perfectly on any IDE — Behnam2016
– Behnam2016, Commented Jul 16, 2018 at 11:59
It's not possible that replacing "SOURCE D:/MySQL_Dumps/myFile.sql" with the content of the SQL file would give you the same error, unless the file were sourcing itself. Which would be a problem in and out of itself. — Ansgar Wiechers
– Ansgar Wiechers, Commented Jul 16, 2018 at 12:05

mytuny · Accepted Answer · 2019-10-16 15:59:44Z

24

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

answered Oct 16, 2019 at 15:59

mytuny

87510 silver badges15 bronze badges

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

5 Comments

Tashrik Anam Over a year ago

This does the trick @mytuny. But can you explain what is happening after the -e flag or point me to an article please? Have been scratching my head while trying to understand that part.

Murolack Over a year ago

For those who stumble upon this thread and need and explanation. The -e or --execute flags are used to execute an SQL statement. If you already in the MySQL shell or want to execute your statement from a file, you can use the MySQL command source. Execute Manual Source Manual

mytuny Over a year ago

Excellent explanation @Murolack!!

Anse Over a year ago

Thanks for the "source" command, never saw that before. Why does the input pipe not work in PS: ./mysql -u root -p DatabaseName < file.sql ?

Daniel Wu Over a year ago

@Anse I desperately need answer for this too. "<" operator is very useful on classic command prompt, to stream file content into CLI based app such as mysql. I really hate how PowerShell removed this operator.

Diego Grigol · Accepted Answer · 2021-06-26 04:14:35Z

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.

Slava Rozhnev · Accepted Answer · 2021-06-16 10:02:59Z

0

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

This worked perfectly for me.

edited Jun 16, 2021 at 10:02

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

answered Jun 16, 2021 at 9:37

Erez Hazan

1

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related