MySQL script automation and security

After MySQL installation, If you don’t have any enterprise level / any GUI interface for monitoring, backup then one of the option is, write your own scripts to automate these tasks.
In this Blog post, we are going to see few monitoring and backup scripts with covering common security issues.

Credential security

Following is a simple script, which will monitor MySQL service. In case MySQL service [mysqld] is down, then it will send email alert.

#!/bin/sh

# Connection details
MYSQL_USER="monitor"
MYSQL_PASS="lemon_pwd@123" ----> [# Plain text password, Security thread]
MYSQL_HOST="localhost"
SERVER_HOST=$( hostname )
EMAIL=user1@xyz.com,user2@xyz.com

# MySQL status
mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} ping 2>/dev/null 1>/dev/null
if [ $? -ne 0 ]; then
echo "MySQL database is down " | mail -s " MySQL not running on $SERVER_HOST" "$EMAIL"
fi
# END!!

The Problem with above script is, it has mysql user credentials info in plain text. We should store mysql user credentials to somewhere safe in encrypted file.

mysql_config_editor and Credential security

The mysql_config_editor utility enables you to store authentication credentials in an encrypted login path file named .mylogin.cnf.

1. Create a user for monitoring with required privileges.

CREATE USER IF NOT EXISTS 'monitor'@'localhost' IDENTIFIED BY 'strong_pwd'; 

GRANT SELECT, RELOAD, PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';

2. Store user credentials info into .mylogin.cnf (This conf file will get created under current OS user home directory)

shell> mysql_config_editor set --login-path=monitor_client --host=localhost --user=monitor --password                                                                                     

Enter password:<enter_mysql_monitor_user_password>

3. Verify the file contains

shell> mysql_config_editor print --login-path=monitor_client
[monitor_client]
user = monitor
password = *****
host = localhost

4. Implementation and use.

MySQL access with credentials:

 
shell> mysql -u monitor -h localhost -p  
  

MySQL access with mysql_config_editor login_path:

shell> mysql --login-path=minitor_client

Monitoring scripts

Shell script for MySQL service and replication monitoring.


#!/bin/bash

SERVER_HOST=$( hostname )
EMAIL=user1@xyz.com,user2@xyz.com

# MySQL service monitoring
mysqladmin --login-path=monitor_client ping 2>/dev/null 1>/dev/null
if [ $? -ne 0 ]; then
echo "MySQL database is down " | mail -s " MySQL not running on $SERVER_HOST" "$EMAIL"
fi

# Replication monitoring 

for MYSQL_HOST in localhost
do
  MSG1=`/usr/bin/mysql --login-path=monitor_client -e "show slave status\G;" | grep 'Slave_IO_Running:'`
  OUTPUT=(${MSG1//:/ })
  STATUS1=`echo ${OUTPUT[1]}`
  MSG2=`/usr/bin/mysql --login-path=monitor_client -e "show slave status\G;" | grep 'Slave_SQL_Running:'`
  OUTPUT=(${MSG1//:/ })
  STATUS2=`echo ${OUTPUT[1]}`
  if [ "$STATUS1" == "Yes" ] && [ "$STATUS2" == "Yes" ];
  then
    echo "$MYSQL_HOST = $STATUS1 - $STATUS2"
  else
    echo "$SERVER_HOST replication not working"
    mail -s "Replication DOWN - $SERVER_HOST" "$EMAIL" <<EOF
Please check $SERVER_HOST database replication.
EOF
  fi
done
# End!!

Backup scripts

1. Create a user for backup with required privileges.

CREATE USER IF NOT EXISTS 'backup'@'localhost' IDENTIFIED BY 'strong_pwd'; 

GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES,SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

2. Store backup user credentials info into .mylogin.cnf (This conf file will get created under current OS user home directory)

shell> mysql_config_editor set --login-path=backup_client --host=localhost --user=backup --password                                                                                          

Enter password:<enter_mysql_backup_user_password>

3. Backup script


#!/bin/bash

NOW="$(date +"%d-%m-%Y")"
BKP_DIR="/backups/full_backups/$NOW/"
SERVER_HOST=$( hostname )

mkdir -p "$BKP_DIR"
touch "$BKP_DIR/backup.log"

echo "Dumping MySQL databases into separate SQL command files into dir=$BKP_DIR" >> $BKP_DIR/backup.log

db_count=0

for d in $(mysql -NBA --login-path=backup_client -e 'show databases')
do
   if [[ "$d" != information_schema ]] && [[ "$d" != mysql ]] && [[ "$d" !=  performance_schema ]] && [[ "$d" !=  sys ]]; then
    (( db_count++ ))
   echo "DUMPING DATABASE: $d " >> $BKP_DIR/backup.log
mysqldump --login-path=backup_client --single-transaction  $d | gzip > $BKP_DIR/$d.sql.gz
echo "Dumping --triggers --routines --events for databases $d into dir=$BKP_DIR" >> $BKP_DIR/backup.log
mysqldump --login-path=backup_client --triggers --routines --events --no-create-info --no-data --no-create-db --skip-opt $d | gzip > $BKP_DIR/$d-routines.sql.gz
  fi
done

echo "$db_count databases dumped into dir=$BKP_DIR" >> $BKP_DIR/backup.log

find /apps/backups/full_backups/ -type d -ctime +6 -exec rm -rf {} \;

echo "OLDER than 6 days BACKUPS deleted successfully" >> $BKP_DIR/backup.log

# End!!

Automate these scripts runs and All set!!

Advertisements

2 thoughts on “MySQL script automation and security

  1. Pingback: Basics of MySQL Administration and best practices | Lalit's Blog
  2. Pingback: Basics-of-mysql-administration-and-best-practices | CHINMAYA DAS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s