Configure automatic email delivery of data comparison logs

Data Compare for MySQL allows you to set up automatic email delivery of the log file in both cases: when SQL data comparison goes well and when it fails. The task can be completed by means of the command-line functionality.

To set up automatic email notifications, perform the following steps:

  • Step 1: Specify Source and Target in the corresponding .txt files
  • Step 2: Configure the email settings in a .ps1 file
  • Step 3: Create a .bat file to run comparison and email delivery

Step 1: Set Source and Target

1. Open Notepad and enter the source server connection, port, database name, username, and password in a single line separated by commas.

TestUser, source_database_name, source_server_name, Port, Password

where:

  • TestUser and Password are the username and password to the source server you want to connect to.
  • source_database_name is a name of the source database located on the server you connect to.
  • source_server_name is a name of the source server you want to connect to.
  • Port is a port number of the source server.

2. Save the .txt file.

3. Repeat the steps 1 and 2 for the target database.

Step 2: Configure the email settings

Configure the sender and the recipient of the comparison results. For this, create a Windows PowerShell cmdlet file ( .ps1). It is a script that contains a series of lines written in the PowerShell scripting language.

1. In Notepad, type in the following code:

# Define SMTP Server and port
$SmtpServer = "smtp.office365.com"
$SmtpPort = "587"

# Define email data
$EmailFrom = "[email protected]"
$EmailTo = "[email protected]"
$EmailSubject = "Test"
$EmailBody = "This is a test email."
$attachment= "path_to_file"

# Define credentials (Make sure to replace 'myusername' and 'mypassword' with your actual Office365 username and password)
$MailUsername = "[email protected]"
$MailPassword = ConvertTo-SecureString "[password]" -AsPlainText -Force
$Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $MailUsername, $MailPassword

# Send the email
Send-MailMessage -From $EmailFrom -To $EmailTo -Subject $EmailSubject -Body $EmailBody -SmtpServer $SmtpServer -port $SmtpPort  -UseSsl  -Credential $Credentials -Attachment $attachment

#end

In the syntax above:

  • $SmtpServer - specifies the SMTP server of your mail service
  • $EmailFrom - specifies the sender’s email address
  • $EmailTo - specifies the recipient’s email address
  • $EmailSubject - specifies the email subject
  • $EmailBody - specifies any text for the email body, if required
  • $attachment = "path_to_file" - specifies the path to the log file

2. Save the file as .ps1.

Step 3: Create an executable file

Create an executable .bat file. This way, the task can be performed using the command line.

1. In Notepad, type in the code:

Set Compare="path_to_app"
Set Sender= powershell.exe

FOR /F "eol=; tokens=1,2,3,4,5* delims=, " %%e in (Source_Servers_and_DBs.txt) do (
  FOR /F "eol=; tokens=1,2,3,4,5* delims=, " %%j in (Target_Servers_and_DBs.txt) do (
    %Compare% /datacompare /source connection:"User Id=%%e; Database=%%f; Host=%%g; Port=%%h; Password=%%i; Character Set=utf8" /target connection:"User Id=%%j; Database=%%k; Host=%%l; Port=%%m;  Password=%%n; Character Set=utf8" /log:"D\data_compare_mysql_log.log"
  )

  IF %ERRORLEVEL%==0 (
    %Sender% -File D:\PowerShell\send_email_script.ps1
    cd.>Compare_result.log
  )
)

pause

In the syntax above:

  • Set Compare="path_to_app" specifies the location of the Data Compare application on your PC. The default location is “C:\Program Files\Devart\dbForge Compare Bundle for MySQL Standard\dbForge Data Compare for MySQL\datacompare.com”.
  • Source_Servers_and_DBs.txt is the .txt file containing source servers and databases we’ve created in Step 1.
  • Target_Servers_and_DBs.txt is the .txt file containing target servers and databases we’ve created in Step 1.
  • %Compare% /datacompare is the command that initiates the comparison.
  • D:\PowerShell\send_email_script.ps1 is the location and name of the script with the email settings.
  • if %ERRORLEVEL%==0 checks if the exit code (ERRORLEVEL) of the previous datacompare command is equal to 0 (i.e., successful execution).

2. Save the file with the .bat extensions.

3. Run the created file.

Note

You can also automate the execution of the created .bat file with the help of Windows Task Scheduler.

The script is executed, displaying the result of the comparison process and generation of the log file and sends the result to the specified email.

Result