How to Automated MySQL server Daily Monitoring Report to Gmail

Make Ubuntu (Linux) Server Ready

Run sudo apt-get update && sudo apt-get upgrade, you are first updating the local package index and then upgrading the installed packages to their latest versions.

  sudo apt-get update && sudo apt-get upgrade

Make sure that root password has been set up. To set up the root password, run the following code.

  sudo passwd root

when you run sudo passwd root, it prompts you to enter a new password for the root user. After entering and confirming the new password, the password for the root user is set or changed. Now restart the server and login as root user. After that run sudo apt install net-tools. It will download and install the net-tools package and its associated utilities.After the installation is complete, you can use various networking commands such as ifconfig, route, and others that are included in the net-tools package.

  sudo apt install net-tools;

Now you have to change server time. Make sure Ubuntu server time is selected with your local time. For example: I am changing here server time as NewYork EST time

  sudo timedatectl set-timezone America/New_York;

Give root user SSH permission remotly

To give the root user SSH permission remotely, you typically need to perform a few steps. Keep in mind that granting root access over SSH should be done with caution due to security concerns. It's generally recommended to use a regular user with sudo privileges for remote access.Here are the steps to enable remote SSH access for the root user: Ensure SSH is Installed: Make sure that the SSH server is installed on your system. If not, you can install it with the following command:

  
  sudo apt install openssh-server
  sudo nano /etc/ssh/sshd_config

Find the line that says PermitRootLogin and change it to PermitRootLogin yes(remove # before PermitRootLogin and write yes) and restart ssh service.

  
  PermitRootLogin yes
  sudo systemctl restart ssh

MySQL Installation

Installing MySQL on a Linux system involves a series of commands. Here are the general steps for Ubuntu.

  
  # Install MySQL server
  sudo apt install mysql-server
  
  # Start MySQL service
  sudo systemctl start mysql
  
  # Access MySQL shell
  sudo mysql
  
  # Set root user password
  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword';
  
  # Exit MySQL shell
  exit
  
  # Log in to MySQL with the new password
  mysql -u root -p
  
  # Exit Again
  exit

Postfix Installation

Postfix is a popular mail transfer agent (MTA) used for routing and delivering email on a Unix or Linux system. Here are some reasons why you might install Postfix.

Install Postfix:

  sudo apt-get install postfix

During the installation, you'll be prompted to choose a mail server configuration. Choose "Internet Site" if you're setting up a simple mail server on the internet. Enter your system mail name when prompted (in your example, you used "anything.com"). Check Postfix configuration:

  cat /etc/postfix/main.cf

This command displays the configuration file for Postfix. You can review the configuration settings in this file. Postfix should now be installed and configured on your system.

Create sasl_passwd directory

Now we need to setup Postfix to relay emails through Gmail's SMTP server using SASL (Simple Authentication and Security Layer) for authentication. The steps you've outlined involve creating a file to store login information.

To create sasl_passwd file:

  sudo vim /etc/postfix/sasl/sasl_passwd

This command opens the sasl_passwd file for editing using the vim text editor.

Add the following line to the file, replacing abc@gmail.com with your Gmail email address and xxxxxxxxxxxxxxxx with your Gmail app password:

  [smtp.gmail.com]:587 abc@gmail.com:xxxxxxxxxxxxxxxx

Save the file: Press Esc to exit insert mode in vim. Type :wq to save the file and exit vim.

We have to create Postfix lookup table. To do so:

  sudo postmap /etc/postfix/sasl/sasl_passwd

This command generates a hashed database file (sasl_passwd.db) from the plaintext sasl_passwd file. To check:

  ls /etc/postfix/sasl/

After running the postmap command, you can check the contents of the /etc/postfix/sasl/ directory. The directory should now contain two files: sasl_passwd (plaintext file with your credentials) and sasl_passwd.db (hashed database file for Postfix lookup).

Now we need to restrict access to the sasl_passwd and sasl_passwd.db files, ensuring that only the root user has read and write permissions. Change ownership to root:

  
  sudo chown root:root /etc/postfix/sasl/sasl_passwd /etc/postfix/sasl/sasl_passwd.db
  sudo chmod 0600 /etc/postfix/sasl/sasl_passwd /etc/postfix/sasl/sasl_passwd.db

This command sets the file permissions for both files to 0600, which means that only the owner (root) has read and write permissions, and no one else has any permissions.

Configuring Postfix to use Gmail's SMTP server

We need to configuring Postfix to use Gmail's SMTP server for relaying emails and enabling SASL authentication.

Edit main.cf configuration file:

  sudo vim /etc/postfix/main.cf

This command opens the main.cf file for editing using the vim text editor.

modify the relayhost parameter:

  relayhost = [smtp.gmail.com]:587

This line sets the relayhost to Gmail's SMTP server on port 587. Add additional lines for SASL authentication:

  
  smtp_sasl_auth_enable = yes
  smtp_sasl_security_options = noanonymous
  smtp_sasl_password_maps = hash:/etc/postfix/sasl/sasl_passwd
  smtp_tls_security_level = encrypt
  smtp_tls_CAfile = /etc/ssl/certs/ca-certificates.crt

These lines enable SASL authentication, specify security options, define the password maps file, set TLS security level, and provide the path to the CA file for TLS. Save the file: Press Esc to exit insert mode in vim. Type :wq to save the file and exit vim. Restart Postfix:

  sudo systemctl restart postfix

This command restarts the Postfix service to apply the new configuration

Configuring msmtp to work with Gmail's SMTP server for sending emails

We need to install msmtp to work with Gmail's SMTP server for sending emails. Install msmtp:

  sudo apt-get install msmtp

This command installs the msmtp package on your system. Now edit the ~/.msmtprc file:

  nano ~/.msmtprc

This command opens the Nano text editor to create or edit the ~/.msmtprc configuration file. Add configuration settings for Gmail:

  
  defaults
  tls on
  tls_starttls on
  tls_trust_file /etc/ssl/certs/ca-certificates.crt
  
  account Your_Account
  host smtp.gmail.com
  port 587
  from abc@gmail.com
  auth on
  user abc@gmail.com
  password xxxxxxxxxxxxxxxx
  logfile ~/.msmtp.log

Replace abc@gmail.com and xxxxxxxxxxxxxxxx with your Gmail email address and app password. Save the file: Press Ctrl + X to exit Nano. Press Y to confirm saving changes. Press Enter to confirm the filename. Give appropriate permissions to ~/.msmtprc file:

  chmod 600 ~/.msmtprc

This command sets the file permissions to make the ~/.msmtprc file readable and writable only by the file owner.

create a Bash script (mysql_health.sh) for generating MySQL monitor emails

Create .sh file name it anything that make sense for example: mysql_health.sh. To create .sh file:

  sudo vim mysql_health.sh

then paste below sample code:

  
  #!/bin/bash

# MySQL credentials
MYSQL_USER="root"
MYSQL_PASSWORD="YourPassword"

# Email configuration
TO_EMAIL="abc@gmail.com"
FROM_EMAIL="abc@gmail.com"
SMTP_SERVER="smtp.gmail.com"
SMTP_PORT="587"
SMTP_USER="abc.com"
SMTP_PASSWORD="xxxxxxxxxxxxxxxxxxxxxxx"

# Get hostname
HOSTNAME=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -N -s -e "SHOW VARIABLES LIKE 'hostname';")

# Get MySQL version
MYSQL_VERSION=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -N -s -e "SELECT CONCAT('MySQL ', VERSION());")

# Get OS version
OS_VERSION=$(lsb_release -d | awk -F"\t" '{print $2}')

# Generate HTML content with MySQL version and OS version information in a table
HTML_CONTENT_VERSION_INFO="MySQL Version OS Version
${MYSQL_VERSION} ${OS_VERSION}"

# Check MySQL connection for database size
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "USE information_schema; SELECT table_schema 'DB Name', ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) 'DB Size in MB' FROM tables GROUP BY table_schema;" || exit 1

# Generate HTML content with MySQL server name and database size information in a table
HTML_CONTENT_DB_SIZE=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -N -s -e "USE information_schema; SELECT table_schema 'DB Name', ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) 'DB Size in MB' FROM tables GROUP BY table_schema;" | awk 'BEGIN {print ""} {print ""} END {print "DB Name DB Size in MB
" $1 " " $2 " MB"}')

# Check MySQL connection for user information
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "SELECT user, host, password_expired, account_locked FROM mysql.user;" || exit 1

# Generate HTML content with MySQL user information in a table
HTML_CONTENT_USER_INFO=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -N -s -e "SELECT user, host, password_expired, account_locked FROM mysql.user;" | awk 'BEGIN {print ""} {print ""} END {print "User Host Password Expired Account Locked
" $1 " " $2 " " $3 " " $4 ""}')

# Get the size of /var/lib/mysql/ in megabytes
MYSQL_DIR_SIZE=$(sudo du -sm /var/lib/mysql/ | awk '{print $1}')

# Calculate free space
TOTAL_SPACE=$(df -BM /var/lib/mysql | awk 'NR==2 {print $4}' | sed 's/M//')
FREE_SPACE=$((TOTAL_SPACE - MYSQL_DIR_SIZE))

# Determine color based on free space percentage
if [ $FREE_SPACE -lt $((TOTAL_SPACE * 20 / 100)) ]; then
  FREE_SPACE_COLOR="red"
else
  FREE_SPACE_COLOR="green"
fi

# Generate HTML content with table
TABLE_HTML="MySQL Directory Size Information

  
    TOTAL SPACE (MB)
    MYSQL DIRECTORY SIZE (MB)
    FREE SPACE (MB)
  
  
    ${TOTAL_SPACE}
    ${MYSQL_DIR_SIZE}
    ${FREE_SPACE}
  
"

# Compose the email subject
EMAIL_SUBJECT="${HOSTNAME} MySQL Server Report"

# Compose the email
echo "Subject: $EMAIL_SUBJECT" > email_body
echo "Content-Type: text/html" >> email_body
echo "" >> email_body
echo "" >> email_body
echo "MySQL Version and OS Version Information" >> email_body
echo "$HTML_CONTENT_VERSION_INFO" >> email_body
echo "
" >> email_body
echo "MySQL Database Information" >> email_body
echo "$HTML_CONTENT_DB_SIZE" >> email_body
echo "
" >> email_body
echo "MySQL User Login Information" >> email_body
echo "$HTML_CONTENT_USER_INFO" >> email_body
echo "
" >> email_body
echo "$TABLE_HTML" >> email_body
echo "" >> email_body

# Send the email
command -v msmtp >/dev/null 2>&1 || { echo >&2 "msmtp not found. Please install it."; exit 1; }
cat email_body | msmtp -a Your_Account -t $TO_EMAIL --from=$FROM_EMAIL || exit 1

# Clean up temporary files
rm email_body

exit 0

MySQL Version	OS Version
${MYSQL_VERSION}	${OS_VERSION}

DB Name	DB Size in MB
" $1 "	" $2 " MB

User	Host	Password Expired	Account Locked
" $1 "	" $2 "	" $3 "	" $4 "

TOTAL SPACE (MB)	MYSQL DIRECTORY SIZE (MB)	FREE SPACE (MB)
${TOTAL_SPACE}	${MYSQL_DIR_SIZE}	${FREE_SPACE}

Replace your account credential here and save the file (press Esc the :wq ). Now you have to make this script executable. To do that:

  sudo chmod +x ./mysql_health.sh

And finally run that script by writing:

  ./mysql_health.sh

You will get email after 1/2 minutes. Thank you.

cron job setup

  crontab -e 
  11 14 * * * /bin/sh /root/mysql_health.sh

How to Automated MySQL server Daily Monitoring Report to Gmail

Make Ubuntu (Linux) Server Ready

Give root user SSH permission remotly

MySQL Installation

Postfix Installation

Create sasl_passwd directory

Configuring Postfix to use Gmail's SMTP server

Configuring msmtp to work with Gmail's SMTP server for sending emails

create a Bash script (mysql_health.sh) for generating MySQL monitor emails

MySQL Directory Size Information

MySQL Version and OS Version Information

MySQL Database Information

MySQL User Login Information

Post a Comment

FIX: Remote Desktop Connection: An authentication error has occurred. The function requested is not supported

Contact form

How to Automated MySQL server Daily Monitoring Report to Gmail

Make Ubuntu (Linux) Server Ready

Give root user SSH permission remotly

MySQL Installation

Postfix Installation

Create sasl_passwd directory

Configuring Postfix to use Gmail's SMTP server

Configuring msmtp to work with Gmail's SMTP server for sending emails

create a Bash script (mysql_health.sh) for generating MySQL monitor emails

MySQL Directory Size Information

MySQL Version and OS Version Information

MySQL Database Information

MySQL User Login Information

You may like these posts

Post a Comment

Contact form