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!!

MySQL service : Unable to setup unix socket lock file.

How to solve mysqld service restart problem for above error?

Problem :
I was adding shell and home directory for mysql user,executed following cmd,

shell> usermod -m -d /home/mysql -s /bin/bash mysql

If mysql is running and process running with mysql , we need to stop mysql otherwise it will throw an error like usermod: user mysql is currently used by process 27768

After stopping MySQL service and adding shell and homedir for mysql user, at the time mysqld service startup it started throwing error.


shell> service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
Shell> systemctl status mysqld.service

● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: deactivating (final-sigterm) (Result: exit-code) since Wed 2016-12-14 22:16:42 MST; 7min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 35222 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 35204 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 27768 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mysqld.service
└─35226 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

mysqld error logs:

[ERROR] Could not create unix socket lock file /var/lib/mysql/mysql.sock.lock.
[ERROR] Unable to setup unix socket lock file.
[ERROR] Aborting

Tried to stop, kill mysqld service but still it’s not going out from process list.

Root cause : Suspecting change in process id after modifying mysql user properties.

Solution:

Just Move/Rename your my.cnf and start mysqld service with default configuration.You will see no more error at the time for service startup.
Move backuped up /renamed original my.cnf and restart mysqld service again.

service mysqld restart

And should start working fine, as it is.

ALl set!!