1.Procedure Powershell Backup Script Mysql

1.Procedure Powershell Backup Script MySQL

1.Create credential file

To avoid using credentials in your script, MySQL has the option to create an encrypted login path file to store your credentials named .mylogin.cnf. Default the file is stored in %APPDATA%\MySQL.

The default path is not accessible by the scheduler or other account. To change the default path you will need to create a new system variable MYSQL_TEST_LOGIN_FILE “Your Path”

Example:

Please note: Reboot the system to make this variable available!

To create the encrypted file use the following command on the command line

mysql_config_editor set --login-path=”NAME” --host=localhost --user=root --password

You will be prompted for the password.

More information can be found on the following links:

2.Create event-log source.

To make sure the script can write the events to the log, you will need to create the corresponding source.

Open PowerShell and run this command:

New-EventLog –LogName Application –Source “Your Application”, example MySQL

In case the event is not displaying correctly, you can change the following registry entry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\MySQL:

Data: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\EventLogMessages.dll

3.Edit script variables.

$error.clear()

$server="YOUR SERVER"

$backupFolder="BACKUP Location"

$dbName="Database name"

$MySQLDumpPath='C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump.exe'

$limit= (Get-Date).AddDays(-15) # file retention period.

#---Format timestamp for the filename

$timestamp=Get-Date-formatyyyyMMdd-HHmmss

#Write-Host $timestamp

#write-host "Backing up database: " $dbName

#---Set backup filename

$filename=$timestamp+"_"+$dbName+".sql"

$fullPath=$backupFolder+""+$filename

#---Invoke backup Command.

#cmd /c " `"$MySQLDumpPath`" --login-path=Nantis --all-databases > $fullPath"

Invoke-Command{ param($1,$2,$3) cmd.exe/c`"$1`"--login-path=Nantis$2|Out-File$3-Encodingutf8} -ArgumentList"$MySQLDumpPath","$dbname","$fullPath"

# Write eventlog on error

if ($LASTEXITCODE-eq'0') {

write-eventlog-LogNameApplication-SourceMySQL-eventID0007-EntryTypeInformation-Message"Backup $dbNamesuccesfull"

}

if($LASTEXITCODE-eq'2') {

write-eventlog-LogNameApplication-SourceMySQL-eventID0008-EntryTypeError-Message"Backup $dbName failed"

}

#---Delete Files

Get-ChildItem-Path$backupFolder-Force|Where-Object{ !$_.PSIsContainer-and$_.CreationTime-lt$limit } |Remove-Item–Force

4.Schedule script in task scheduler.

Schedule the script in Task Scheduler using the following options. Schedule the script before midnight, to make sure that the dump is included in the daily backup!

Arguments: -Noninteractive -NoProfile -command "&{PATH_TO_SCRIPT}”