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: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.6 | OS: | Windows |
Assigned to: | Philip Olson | CPU Architecture: | Any |
[6 Aug 2014 8:40]
Daniël van Eeden
[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).