Pipe stream to other process

Question

I would like to download a 20GB dump, replace strings and pipe it to mysql.exe while it still downloads in Powershell. But I'm having issues piping the stream.

If my file was already downloaded, I could stream while replacing strings in the file to StdOut with:

Get-Content 'dump.sql' | %{ $_.replace("production_db", "staging_db") }

Or if I also download the file while streaming and replacing strings to StdOut , I could do this:

$url = 'http://MyServer.ext/dump.sql'
& {
    $myHttpWebRequest = [System.Net.WebRequest]::Create($url)
    $myHttpWebRequest.Headers.Add("Authorization", "Basic " + [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes("MyUsername:MyPassword")))
    try {
        $res = $myHttpWebRequest.GetResponse()
    }
    catch [System.Net.WebException] {
        $res = $_.Exception.Response
    } 
    if ([int] $res.StatusCode -ne 200) {
        'Error: ' + [int]$res.StatusCode + " " + $res.StatusCode
    } else {
        $receiveStream = $res.GetResponseStream()
        $encode = [System.Text.Encoding]::GetEncoding("utf-8")
        $readStream = [System.IO.StreamReader]::new($receiveStream, $encode)
        while (-not $readStream.EndOfStream) {
            $readStream.ReadLine().replace("production_db", "staging_db")
        }
        $res.Close()
        $readStream.Close()
    }
}

But in both cases, I fail to pipe this as a stream to mysql.exe. It seams the whole stream is first loaded into memory, before being passed on to the mysql.exe process, when I append:

 | & 'C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe' -u MyUsername -pMyPassword -h 127.0.0.1

How do I manage to pipe a stream to another process in Powershell?

Answer 1

We know mysql.exe will take standard input because we can run mysql.exe < myfile.sql and it works great. I found this fantastic answer about sending lines of text to the standard input of a process, and it seems to work for me. I'm using the CMD shell to test with since I don't have mysql handy:

# Setup: example commands to feed via stream to process
'echo "hello"
echo "world"
echo "bye"
exit' > 'C:\temp\file.bat'

# create streamreader from file
$readStream = [System.IO.Streamreader]::new('C:\temp\file.bat')
$encode = [System.Text.Encoding]::GetEncoding("utf-8")

For you, just update your replacement and the exe name:

# Create a process for the job
$psi = New-Object System.Diagnostics.ProcessStartInfo;
$psi.FileName = "cmd.exe"; #process file
$psi.UseShellExecute = $false; #start the process from it's own executable file
$psi.RedirectStandardInput = $true; #enable the process to read from standard input
$psi.RedirectStandardOutput = $true #send standard output to object

$p = [System.Diagnostics.Process]::Start($psi);

# Send each line of the file to the process as standard input:
while (-not $readStream.EndOfStream) {
  $p.StandardInput.WriteLine(
    ($readStream.ReadLine().replace("l", "L"))
  )
}

$readStream.Close()

# Make sure you don't accidentally close mysql before it's done processing.
# For example, add WriteLine('exit') after the While{}, then use Wait-Process.
# get the output of the process, should also wait for the process to finish
$p.StandardOutput.ReadToEnd()
$p.Close() 

In my tests it's working, replacing the commands as they're read, and sending them to the process, and shows the output:

Microsoft Windows [Version 10.0.19043.1110]
(c) Microsoft Corporation. All rights reserved.

C:\>echo "heLLo"
"heLLo"

C:\>echo "worLd"
"worLd"

C:\>echo "bye"
"bye"

C:\>exit

I think you should be able to hook into $p.StandardOutput and read it as you go, but when I tried it would cause the process to hang? Maybe just use mysql logging instead.