If you are working with MySQL on Windows you probably know about the MySQL Connector/Net . If you don’t, please read further. MySQL Connector/Net has made working with MySQL Server or MariaDB Server on windows a breeze. Initially it was commonly used as an interface between .Net Applications and MySQL. With the advent of Powershell as the defacto scripting language for Windows administrators, the MySQL Connector/Net has become a nice tool to use for automation. In order to use the MySQL Connector/Net with powershell, you will need to download and install version 6.6.6 from the link provided above. You may ask why we are using an older version, the answer is simple, read below.
################################################################ # # MysqlBackup.ps1 # Author: Matthew Marable # Updated: Jan 19, 2012 # # Description: # This script will query a specified MySQL database and then create .sql backup files # of all located databases. # ################################################################ # Core settings - you will need to set these $mysql_server = "10.16.1.249" $mysql_user = "[db_user]" $mysql_password = "[db_pass]" $backupstorefolder = """D:\LinuxBackups\MySQL\""" # Extended Settings - you may not need to set these $pathtomysqldump = "C:\Admin Tools\mysqldump.exe" $latestbackupfolder = """D:\LinuxBackups\MySQL\""" #-------------------------------------------------------- # Determine Today's Date Day (monday, tuesday etc) $gd = get-date $dayofweek = [string] $gd.DayOfWeek # Connect to MySQL database 'information_schema' [system.reflection.assembly]::LoadWithPartialName("MySql.Data") $cn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection $cn.ConnectionString = "SERVER=$mysql_server;DATABASE=information_schema;UID=$mysql_user;PWD=$mysql_password" $cn.Open() # Query MySQL $cm = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand $sql = "SELECT DISTINCT CONVERT(SCHEMA_NAME USING UTF8) AS dbName, CONVERT(NOW() USING UTF8) AS dtStamp FROM SCHEMATA WHERE schema_name NOT IN ('information_schema','performance_schema') ORDER BY dbName ASC" $cm.Connection = $cn $cm.CommandText = $sql $dr = $cm.ExecuteReader() # Loop through MySQL Records while ($dr.Read()) { # Start By Writing MSG to screen $dbname = [string]$dr.GetString(0) write-host "Backing up database: " $dr.GetString(0) # Set backup filename and check if exists, if so delete existing $backupfilename = $dayofweek.tolower() + "_" + $dr.GetString(0) + ".sql" $backuppathandfile = $backupstorefolder + "" + $backupfilename If (test-path($backuppathandfile)) { write-host "Backup file '" $backuppathandfile "' already exists. Existing file will be deleted" Remove-Item $backuppathandfile } # Invoke backup Command. /c forces the system to wait to do the backup C:\Windows\System32\cmd.exe /c " `"$pathtomysqldump`" --routines -h $mysql_server -u $mysql_user -p$mysql_password $dbname > $backuppathandfile " If (test-path($backuppathandfile)) { write-host "Backup created. Presence of backup file verified" } # Handle LatestBackup functionality If (test-path($backuppathandfile)) { $latestbackupfilenameandpath = $latestbackupfolder + "latest_" + $dbname + ".sql" &cmd /c "copy /y `"$backuppathandfile`" `"$latestbackupfilenameandpath`" " write-host "Backup file copied to latestbackup folder" } # Write Space write-host " " } $cn.Close() & 'C:\Admin Tools\EmailAdmin.ps1' "tech@youremail.com" "MySQL Backup Alert: Job Success (Server: DB01)" "Completed Successfully." # END OF SCRIPT
Hi,
This works perfectly for mysql 5.5 and below. But when run in MySQL 5.6 it throws a warning as below:
C:\Windows\System32\cmd.exe : Warning: Using a password on the command line interface can be insecure.
It work be of great help if the script can be updated to to suppress this warning.
Take a look at this link, it may get you going.