Bug #73481 Restore from SQL-file needs powershell tips
Submitted: 6 Aug 2014 8:40 Modified: 5 Jul 2016 18:36
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6 OS:Microsoft Windows
Assigned to: Philip Olson CPU Architecture:Any

[6 Aug 2014 8:40] Daniël van Eeden
Description:
Page: http://dev.mysql.com/doc/refman/5.6/en/reloading-sql-format-dumps.html

Using this won't work in powershell:
mysql.exe < backup.sql

This should work:
Get-Content backup.sql | mysql.exe

However it will probably fail for larger backups:
http://stackoverflow.com/questions/4788935/powershell-pipe-file-contents-into-application-...

How to repeat:
Try to restore a .sql backup file in powershell.
[6 Aug 2014 8:47] Daniël van Eeden
It might also be a better solution to create a powershell compatible program for processing SQL files. This is because stdin in powershell works differently than cmd.exe/bash/csh.

Like this:
mysqlsource.exe -f backup.sql

Or this:
mysql.exe --source backup.sql

This should already work, but it's not as userfriendly/easy.
mysql.exe -e "source d:\data\backup.sql"
I don't know if this should work, as the \ might need to be changed to / or \\.
[6 Aug 2014 9:24] MySQL Verification Team
Hello Daniël,

Thank you for the report.

Thanks,
Umesh
[6 Aug 2014 9:25] MySQL Verification Team
//
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\ushastry> cd D:\ushastry\mysql-advanced-5.6.19-winx64\bin
PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin> mysql -h x.x.x.x -uushastry -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
..
..

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q
Bye
PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin> mysql -h x.x.x.x -uushastry -p < dump.sql
The '<' operator is reserved for future use.
At line:1 char:39
+ mysql -h x.x.x.x -uushastry -p < <<<<  dump.sql
    + CategoryInfo          : ParserError: (<:OperatorToken) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : RedirectionNotSupported

PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin>

PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin> get-content dump.sql | mysql -h x.x.x.x -uushastry -p
Enter password:
PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin>
[6 Aug 2014 9:31] Peter Laursen
I never was able to make any MySQL command line client work in Powershell at all.

It was nice if it worked as Powershell has scripting options etc. etc. not unlike bash in Linux for instance.

But I think that MySQL/Sun/Oracle never considered Powershell. In my understanding it would also require some kind of wrapper written in a .NET language. IMO it would be more useful to have this than the current utilities for Windows ('MySQL for Excel', 'MySQL notifier' etc.).

-- Peter
-- not a MySQL/Oracle person
[21 Aug 2014 13:19] Daniël van Eeden
A mysqldump created with 'mysqldump.exe -A > backup_powershell.sql'
will have a different file encoding than a dump made with the same command in cmd.exe.

The dump made in cmd.exe can be restrored with 'mysql> source dump.sql', but the one made with powershell can't. The work-around is to use 'Get-Content dump.sql | mysql.exe'

With powershell: UTF16LE
With cmd.exe   : UTF8

Instead of using '> dump.sql' in powershell this can be done to create a UTF8 encoded file:
mysqldump.exe -A | Out-File dump.sql -Encoding UTF8
[21 Jun 2016 0:07] Philip Olson
PowerShell usage goes beyond the scope of the MySQL manual, so this will not
be documented. Thankfully its usage, in several ways, is covered on the
Internet.

Thank said, thank you for the request and sorry it cannot be accommodated.
[21 Jun 2016 8:45] Daniël van Eeden
PowerShell is mentioned in the manual already:
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

"""
Note

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:

shell> mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Section 10.1.5, “Connection Character Sets and Collations”), so the dump file will not load correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format:

shell> mysqldump [options] --result-file=dump.sql
"""

I think this is sufficient for most cases, so I won't reopen this bug.
[21 Jun 2016 15:15] Philip Olson
Thank you for following up on this, soon I'll update the docs accordingly. Reopening this bug in the meantime.
[5 Jul 2016 18:36] Philip Olson
The following note was added:

Note:
For Windows PowerShell users: Because the "<" character is
reserved for future use in PowerShell, an alternative approach
is required, such as using quotes 
  cmd.exe /c "mysql < dump.sql"

Additional suggestions and feedback is welcome, and thank you for reporting this bug (and sorry it took so long to handle).