Replace a string during the Mysqldump process on Windows

Question

In a powershell script, I have a mysqldump command which outputs to stdin. The goal is to replace all occurences of a string in that stdin before pushing it into a file, because there is not enough disk space on the machine to hold two separate files (dump is around 30Go).

I have tried this (removed the invoke-expression and mysql args):

mysqldump [...args] | ForEach-Object -Process {$_ -replace 'sourceText','targetText' | Add-Content $dumpDataFile}

Or this:

mysqldump [...args] | Foreach-Object {$_ -replace 'sourceText','targetText'} | Set-Content $dumpDataFile

but it is eating up all the memory on the machine.

I have also tried replacing content in the result file but it always ends up in copying to an another file. I also thought about reading line by line and replacing line by line to a new file, with each X lines removing lines from the original file, but methods I have found to cut lines in files end up eating all memory.

In linux I would have used sed, I know it exists for windows but I do not want to add a dependency to the script.

Here is the command that is run:

$expr = "& 'C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe' --defaults-extra-file=env.cnf --log-error=err.log --no-create-info foo | ForEach-Object -Process {$_ -replace 'foo','bar' | Add-Content dump.sql}"
Invoke-Expression $expr

UPDATE I have found that even piping out to out-null eats up all the memory:

& 'C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe' --defaults-extra-file=env.cnf --log-error=err.log --no-create-info foo | out-null

also the scripts run on an amazon virtual machine which has powershell 4

UPDATE 2 This also eats up all the memory, but it does not when running from cmd:

& 'C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe' --defaults-extra-file=env.cnf --log-error=err.log --no-create-info foo > dump.sql

Do you know how to call the full replace command with cmd? I do not manage to escape the mysqldump executable path

UPDATE 3 Realized that my dump contains huge tables, which results in some of the INSERT line being extremely long (thus the memory usage maybe). I tries without extended inserts but it is too long to import then.

Answer 1

If the disk space is premium, how about compressing the data? If NTFS compression isn't good enough, let's write the output into a GZipStream. It should offer good savings for text data. Thus the file on disk would be considerably smaller.

First off, a compression function (idea from a blog post):

function Compress-Stream {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true, ValueFromPipeline=$true)]
        [AllowEmptyString()]
        [string]$Row
    )
    begin {
        $ms = New-Object System.IO.MemoryStream
        $cs = New-Object System.IO.Compression.GZipStream($ms, [System.IO.Compression.CompressionMode]::Compress)
        $sw = New-Object System.IO.StreamWriter($cs)
        }
    process {
        if(-not [string]::IsNullOrWhiteSpace($row)) {
            $sw.Write($Row + [environment]::NewLine)
        }
    }
    end {
        try {$cs.Close(); $cs.Dispose()} catch{} 
        try {$sw.Close(); $sw.Dispose()} catch{}

        $s = [System.Convert]::ToBase64String($ms.ToArray());
        try {$ms.Close(); $ms.Dispose()} catch {}
        $s
    } 
}

Sample usage is to query DBA Overflow data dump. Tt's much more manageable that SO. On my system the result set is 13 MB uncompressed, 3,5 MB compressed.

# SQL Server, so sqlcmd for illustration.
# Pipe results to compression and pipe compressed data into a file
sqlcmd -E -S .\sqli001 -d dbaoverflow -Q "select id, postid from votes order by id;" `
| compress-stream | Set-Content -Encoding ascii -Path c:\temp\data.b64

This should provide a compressed text file. To process it, use MemoryStream and GZipStream again:

$d = get-content c:\temp\data.b64
$data = [System.Convert]::FromBase64String($d)
$ms = New-Object System.IO.MemoryStream
$ms.Write($data, 0, $data.Length)
$ms.Seek(0,0) | Out-Null
$sr = New-Object System.IO.StreamReader(New-Object System.IO.Compression.GZipStream($ms, [System.IO.Compression.CompressionMode]::Decompress))

# $sr can now read decompressed data. For example,
$sr.ReadLine()
id          postid
$sr.ReadLine()
----------- -----------
$sr.ReadLine()
1           2

Doing replacements and writing the final result into another a file should be easy enough.

Answer 2

In the end I use python to replace the string in the dump file while sending it to mysql. It is fast enough and low on memory.