MySQL Dump with PowerShell

Question

$mysqlpath = "C:\Program Files\MySQL\MySQL Server 5.6\bin"
$backuppath = "C:\Users\Tiffany\Downloads"
$username = "user"
$password = "123123"
$database = "db"
$errorLog = "error_dump.log"

$date = Get-Date
$timestamp = "" + $date.day + $date.month + $date.year + "_" + $date.hour + $date.minute

$backupfile = $backuppath + $database + "_" + $timestamp +".sql"

CD $mysqlpath
.\mysqldump.exe --user=$username --password=$password --log-error=$errorLog --result-file=$backupfile --databases $database

CD $backuppath
$oldbackups = gci *.sql*

for($i=0; $i -lt $oldbackups.count; $i++){
    if ($oldbackups[$i].CreationTime -lt $date.AddMonths(-1)){
        $oldbackups[$i] | Remove-Item -Confirm:$false
    }
}

However, I keep getting the following:

mysqldump.exe : Warning: Using a password on the command line interface can be insecure.
At C:\Users\Tiffany\Desktop\mysqldump.ps1:14 char:16
+ .\mysqldump.exe <<<<  --user=$username --password=$password --log-error=$errorLog --result-file=$backupfile --databases $database
    + CategoryInfo          : NotSpecified: (Warning: Using ...an be insecure.:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

Do I need to set a flag to allow this commandline?

score 0 · Accepted Answer · edited May 23 '17 at 12:07

I would sidestep that question and use a my.cnf to store credentials instead of storing such data in the powershell script, see How to perform a mysqldump without a password prompt?:

[mysqldump]
user=mysqluser
password=secret

http://dev.mysql.com/doc/refman/5.5/en/option-files.html http://dev.mysql.com/doc/refman/5.5/en/password-security-user.html

Handing over the credentials via command line options will make them show up in the process listing (depending a bit on who looks onto the process list, but still).

JamesHoux · Answer 2 · 2023-06-29T17:26:48.657

If you sidestep this warning, you need to realize you are NOT fixing the problem.

The warning is telling you that your use of the password is insecure. But this is fact is something you have to accept when working with automated scripts. If you want to automate something that needs creds, you're going to have to store the creds somewhere. Its unavoidable, but there are some steps you can take to still maintain reasonable security. You can avoid the warning, but you won't avoid the security problem.

Akira's recommendation isn't really any better. Moving the password into a config file on disk only results in the password being persisted somewhere else. Its just as easily stolen or compromised in that place.

If you are following sound wisdom about password security, what you should really be doing is creating a MySQL user that ONLY has permission to perform a MySQL dump. Then use that user/password combination with your script. This way, if anyone manages to steal the password, the worst they can do is dump the database. They will be blocked from injecting subtle (or not so subtle) changes to the database, which is good because database changes could be used to do significantly larger volumes of damage.

Some general rules to follow:

Try to avoid storing passwords anywhere in plain text form. Encrypt them or store in an encrypted vault where possible.
Always assume passwords are likely to be compromised or stolen.
Always follow principal of least privilege. Whatever user you are using should have the absolute minimum permissions required to complete the task and nothing more.

Caveats

Now having said everything above, you should also be aware of the following. If you are performing the automated process on the same host where MySQL Server is hosted, then you need to consider whether your efforts to protect your password are even fruitful. You need to think about what else a hacker would already gain access to if they've gotten to your stored password. For instance, if this MySQL server is used to back a website (WordPress for instance) and the MySQL Server lives on the same machine as the website files and a password for the website is stored in the files, then any compromise to the machine means 100% compromise. The hacker can get the website files and get full access to the database. Thus, trying to protect your special export password is completely pointless in the described scenario.

Realize that any password persistence brings risks. That's why the warning exists. And trying to sidestep the warning may give you an "illusion" of security. Embrace the warning and understand the implications.

MySQL Dump with PowerShell

2 Answers2