Mail Alert for Mysql Group Replication using Power shell

Step 1: Create a power shell script and save it with .ps1 extension. Here you can find a sample script

You can try with anyone of the scripts as mentioned below.

Script: 1

$From ="somesh.aprs@gmail.com"
$To1="somesh.aprs@gmail.com"

$Subject="Mail Alert for Mysql Group Replication"
$SMTPServer="https://mail.google.com"
$SMTPPort="587"


$ip='192.168.0.7'
$user = 'Read'
$pass = 'P@ssw0rd@123'
$conpass='-p'+$pass
$mysql  = 'C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe'
$params = '-h',$ip, '-P3306', '-u', $user, $conpass

$QueryResult1 =& $mysql @params -e "select MEMBER_HOST,MEMBER_PORT Port,MEMBER_STATE Status,'`n' from performance_schema.replication_group_members"

Send-MailMessage -From $From -to $To1  -Subject $Subject -Body ($QueryResult1 | Out-String) -SmtpServer $SMTPServer 


Script2:

$ip='192.168.0.7'

$user = 'Read'

$pass = 'password'

$conpass='-p'+$pass

$mysql  = 'C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe'

$params = '-h',$ip,  '-P6446',  '-u', $user, $conpass

$QueryResult1 =& $mysql @params -e "select MEMBER_HOST,MEMBER_PORT Port,MEMBER_STATE Status,'`n' from performance_schema.replication_group_members"

 

#from mail id and password

$Username = "somesh.aprs@gmail.com";

$Password = "gthvdfldwxthcqrz";

$path = "D:\replication.txt";

function Send-ToEmail([string]$email, [string]$attachmentpath){

    $message = new-object Net.Mail.MailMessage;

    $message.From = "somesh.aprs@gmail.com";

    $message.To.Add($email);

    $message.Subject = "Mail Alert for Mysql Group Replication";

    $message.Body = ($QueryResult1 | Out-String);

    $attachment = New-Object Net.Mail.Attachment($attachmentpath);

    $message.Attachments.Add($attachment);

    $smtp = new-object Net.Mail.SmtpClient("mail.google.com", "587");

    $smtp.EnableSSL = $true;

    $smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);

    $smtp.send($message);

    write-host "Mail Sent" ; 

    $attachment.Dispose();

 }

Send-ToEmail  -email "somesh.aprs@gmail.com" -attachmentpath $path;

Step 2: Test the Script 

                  - open windows PowerShell command prompt
                  - Run below command
                          & "E:/mailalert.ps1"
             Note: here I have saved the file name as a mailalert.ps1. Please use your file path location with the filename.

Result: you will receive the mail.

Step 3: Open Task scheduler
Step 4: Create a new Task
             - Give Task name and description
             - Trigger, check on Daily and Repeat every hour
             - Action, select Start program, type Powershell in script field and file location in                     arguments
             - Save it.
Note: Above Task Schedule created for every hour.