Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Wednesday, 26 June 2019

After change the user privileges, When Changes Take Effect ?

You can modify the user privileges with two methods ...

Changes Take Effect- Immediately

If you modify the grant tables indirectly using an account-management statement (Examples include GRANT, REVOKE, SET PASSWORD, and RENAME USER), the server notices these changes and loads the grant tables into memory again immediately. 

Changes Take Effect - After issuing FLUSH PRIVILEGES or restart MySQL

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE , the changes have no effect on privilege checking until you either tell the server to reload the tables or restart it.

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

A grant table reload affects privileges for each existing client session as follows:

Table and column privilege changes take effect with the client's next request.

Database privilege changes take effect the next time the client executes a USE db_name statement.

Note:
Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database.

Global privileges and passwords are unaffected for a connected client. These changes take effect only in sessions for subsequent connections.

Reference and Copyright to Reference Manual

Friday, 14 June 2019

MAX_USED_CONNECTIONS PER USER/ACCOUNT

How many connections can be opened concurrently against my MySQL database can be configured and checked with the following command:

[mysqld]
max_connections= 2500
SHOW GLOBAL VARIABLES LIKE 'max_connections';

Variable_name
Value
max_connections
2500

We can check the status of this limit as below..

SHOW STATUS LIKE 'max_used%';

Variable_name
Value
Max_used_connections
587
Max_used_connections_time
2019-06-05 16:07:28

But MySQL instances may be configured with many different applications (= database's/schema's) and thus many different users.It is a bit more complicated to find out which of these users have connected how many times concurrently. We can limit the maximum concurrent connection per user with:

[mysqld]
max_user_connections= 500

SHOW GLOBAL VARIABLES LIKE 'max_user_connections';

Variable_name
Value
max_user_connections
500

OR

Further we can limit one specific user with:

GRANT USAGE ON *.* TO 'alok'@'%' WITH MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 700;

and check with: 

SELECT User, Host, max_connections, max_user_connections FROM mysql.user;

User
Host
max_connections
max_user_connections
alok
172.31.28.36
0
700
alok
%
1000
700
root
localhost
0
0

Now if you want to check Max_used_connections per user wise currently we have no chance to check the same.

This feature request was opened at MySQL bug (#77888) report https://bugs.mysql.com/bug.php?id=77888

Implementing Max_used_connections per user:-


I will show you how to implement a custom table for storing max_used_connections &max_used_tim user wise.
Create Table max_used_connections in database mysql

USE mysql;
CREATE TABLE `max_used_connections` (
  `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `CONNECTION` bigint(20) NOT NULL,
  `MAX_USED_CONNECTIONS` bigint(20) NOT NULL,
  `MAX_USED_TIME` varchar(50)DEFAULT NULL,
  UNIQUE KEY `USER` (`USER`,`HOST`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8;


Now create an event to update the table “max_used_connections” and store the required status.

CREATE DEFINER=root@localhost EVENT mysql.max_used_connections
ON SCHEDULE EVERY 5 SECOND
DO
INSERT INTO mysql.max_used_connections
SELECT user, host, current_connections,current_connections as CONNECTION ,'' MAX_USED_TIME FROM performance_schema.accounts 
WHERE user IS NOT NULL AND host IS NOT NULL ON DUPLICATE KEY
UPDATE max_used_connections = IF(current_connections > max_used_connections, current_connections, max_used_connections),
CONNECTION = current_connections ,
MAX_USED_TIME = IF(max_used_connections > current_connections, MAX_USED_TIME,now());

NOTE: Event Scheduler must be Enable to achieve this feature.
Now you can check the current status of max_used_connections per user as below.



Friday, 24 May 2019

MySQL Incremental Backup Script

How to take incremental backup in MySQL ?

::#Point-in-Time Backup and Recovery in MySQL

Using large production databases must required an Incremental backup to ensure Safe and Reliable database system. In this article, I'm writing a shell script for automatic MySQL FULL&Incremental Backup which can be schedule by cron job for automation of their result.
This Script is written in very simple way and logic which you can easily understand.

Features....

  • Automation of Daily Full Backup using Percona XtraBackup
  • No production loss during the backup 
  • Creating a mysql-binlog check-points in every backup so that you can easily use the same for point-in-time recovery or setup new replication server.
  • Create Backup Directory if not exist.
  • No repetition of same day backup.
  • Creating a Log File for whole process.
  • Sending Email Alerts for any failure/success backup  
  • Easy-to-Understand 

Limitation...

  • You must have to installed Percona XtraBackup 2.4
  • Must have to configured Mail smtp function (mailx) for sending email alerts
  • Occupy the disk storage if data size is very large

#!/bin/bash
#########################################
# By-Alok Kumar Singh@www.getmsql.info###
# Features-                           ###
# FULL , Incremental, Restore ,Daily/ ###
# Weekly Backup Rotation, Mail Alert  ###
# on Successful/Failure               ###
######################################### 


SECRET="--user=MySQLusername --password=MySQLpassword"

## Parameters

BACKUP_DIR_ROOT=/backup/xtrabackup
BACKUP_DIR_Daily=$BACKUP_DIR_ROOT/Daily
DATA_DIR=/var/lib/mysql
HOSTIP="192.168.1.1"
SERVERNAME="Production-Slave-DB"
TAR_COMPRESS="1"  ## 0 - for disable

emails="info.getmysql@gmail.com" ## Add Multiple emails with space

usage() { 
echo "usage: $(basename $0) [option]" 
echo "option=full: Perform Full Backup"
echo "option=incremental: Perform Incremental Backup"
echo "option=restore: Start to Restore! Be Careful!! "
echo "option=help: show this help"
}

fullbackup() {
local TMPFILE=/tmp/XtraBackup$$.tmp
if [ ! -d $BACKUP_DIR_ROOT ]
then
mkdir $BACKUP_DIR_ROOT
fi
if [ ! -d $BACKUP_DIR_Daily ]
then
mkdir $BACKUP_DIR_Daily
fi

rm -rf $BACKUP_DIR_Daily/*

echo "$(date +%d%m%y" ""%T.%3N") :: Cleanup the backup folder is done! Starting backup" >> $BACKUP_DIR_Daily/xtrabackup_full.log

#xtrabackup --backup $SECRET -u root -p --history --compress --slave-info --compress-threads=4 --target-dir=$BACKUP_DIR_Daily/FULL

xtrabackup --backup $SECRET --history  --target-dir=$BACKUP_DIR_Daily/FULL > $TMPFILE 2>&1
#xtrabackup --backup --user=root --password=Mysqlroot@123 --history --parallel=8 --compress --compress-threads=8 --target-dir=/tmp/FULL /tmp/log 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_FULL failed" >> $BACKUP_DIR_Daily/xtrabackup_full.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_full.log
#echo -e $ERRMSG | mail -r "root@alert.com" -s "XtraBackup failed" $emails
mail -r "root@alert.com" -s "ERROR:XtraBackup_FULL failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
rm -rf $BACKUP_DIR_Daily/FULL
exit 1
fi
rm -rf $TMPFILE
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_FULL Done!" >> $BACKUP_DIR_Daily/xtrabackup_full.log
echo -e "XtraBackup_FULL Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_FULL Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 
}

incremental_backup(){
local  TMPFILE=/tmp/XtraBackup_inc$$.tmp
if [ ! -d $BACKUP_DIR_Daily/FULL ]
then
echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:Unable to find the FULL Backup. aborting.....!" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
exit -1
fi

if [ ! -f $BACKUP_DIR_Daily/last_incremental_number ]; then
NUMBER=1
else
NUMBER=$(($(cat $BACKUP_DIR_Daily/last_incremental_number) + 1))
fi
echo "$(date +%d%m%y" ""%T.%3N") :: Starting Incremental backup inc$NUMBER" >> $BACKUP_DIR_Daily/xtrabackup_inc.log

if [ $NUMBER -eq 1 ]
then
xtrabackup --backup $SECRET --history  --incremental --target-dir=$BACKUP_DIR_Daily/inc$NUMBER --incremental-basedir=$BACKUP_DIR_Daily/FULL > $TMPFILE 2>&1
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_inc$NUMBER failed" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_inc$NUMBER failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
rm -rf $BACKUP_DIR_Daily/inc$NUMBER
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_inc$NUMBER Done!" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo -e "XtraBackup_inc$NUMBER Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_inc$NUMBER Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE

else
xtrabackup --backup $SECRET --history --incremental --target-dir=$BACKUP_DIR_Daily/inc$NUMBER --incremental-basedir=$BACKUP_DIR_Daily/inc$(($NUMBER - 1)) > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_inc$NUMBER failed" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_inc$NUMBER failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
rm -rf $BACKUP_DIR_Daily/inc$NUMBER
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_inc$NUMBER Done!" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo -e "XtraBackup_inc$NUMBER Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_inc$NUMBER Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE

fi

echo $NUMBER > $BACKUP_DIR_Daily/last_incremental_number

}

restore()
{
local  TMPFILE=/tmp/XtraBackup_restore$$.tmp
timestamp=$(date +%Y%m%d_%H%M%S)
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing the FULL backup" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
# xtrabackup $SECRET --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR_Daily/FULL 
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing Done !!!" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Prepareing FULL Backup ..." >> $BACKUP_DIR_Daily/xtrabackup_restore.log

xtrabackup $SECRET --prepare  --apply-log-only --target-dir=$BACKUP_DIR_Daily/FULL > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_FULL_Prepare failed" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_FULL_Prepare failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_FULL_Preparation Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo -e "XtraBackup_FULL_Preparation Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_FULL_Preparation Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE        
if [ ! -f $BACKUP_DIR_Daily/last_prepare_number ]; then
P=1
else
P=$(cat $BACKUP_DIR_Daily/last_prepare_number)
fi
#P=1
while [ -d $BACKUP_DIR_Daily/inc$P ] && [ -d $BACKUP_DIR_Daily/inc$(($P+1)) ]
do
  # echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing incremental:$P" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
  # xtrabackup $SECRET --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR_Daily/inc$P 
  # echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing incremental:$P Done !!!" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
  echo "$(date +%d%m%y" ""%T.%3N") :: Prepareing incremental backup inc$P" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
  xtrabackup $SECRET --prepare --apply-log-only --target-dir=$BACKUP_DIR_Daily/FULL --incremental-dir=$BACKUP_DIR_Daily/inc$P > $TMPFILE 2>&1
  
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_inc$P Prepare failed" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_inc$P Prepare failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_inc$P Preparation Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo -e "XtraBackup_inc$P preparation Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_inc$P Preparation Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE
P=$(($P+1))
echo $P > $BACKUP_DIR_Daily/last_prepare_number
done

if [ -d $BACKUP_DIR_Daily/inc$P ]
then
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing the last incremental:$P" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
# xtrabackup $SECRET --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR_Daily/inc$P 
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing the last incremental:$P Done !!!" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Prepareing last incremental backup inc$P" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
xtrabackup $SECRET --prepare --target-dir=$BACKUP_DIR_Daily/FULL --incremental-dir=$BACKUP_DIR_Daily/inc$P > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_last_inc$P Prepare failed" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_last_inc$P Prepare failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_last_inc$P Preparation Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo -e "XtraBackup_last_inc$P Preparation Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_last_inc$P Preparation Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE

echo $P > $BACKUP_DIR_Daily/last_prepare_number 

fi
if [ ! -d $BACKUP_DIR_ROOT/Weekly ]
then
mkdir $BACKUP_DIR_ROOT/Weekly
fi
destdir="$BACKUP_DIR_ROOT/Weekly/FULLBACKUP_till_$timestamp"
mv $BACKUP_DIR_Daily $destdir

#### Compress........

if [ "$TAR_COMPRESS" == "1" ]; then
echo "$(date +%d%m%y" ""%T.%3N") :: $destdir compression Enabled!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: $destdir compression Starting!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
#cd $BACKUP_DIR/$NOWDAY && sudo tar --create --gzip --file=$db.sql.tgz $db.sql --remove-files 
tar --create --gzip --file=$destdir.tgz $destdir --remove-files 
echo "$(date +%d%m%y" ""%T.%3N") :: $destdir compression Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
fi

}

if [ $# -eq 0 ]
then
usage
exit 1
fi

case $1 in
"full")
fullbackup
;;
"incremental")
incremental_backup
;;
"restore")
restore
;;
"help")
usage

;;
*) echo "invalid option";;
esac
    

##########################################################
### Copyright@ Alok Kumar Singh with www.getmysql.info ###
##########################################################
How to use the Script ?

  • Save the script as xtrabackup.sh (Script must have 777 permission)
  • Define the below parameters according to your environment...
         SECRET="--user=MySQLusername --password=MySQLpassword"
         BACKUP_DIR_ROOT=/backup/xtrabackup
         BACKUP_DIR_Daily=$BACKUP_DIR_ROOT/Daily
         DATA_DIR=/var/lib/mysql
         HOSTIP="192.168.1.1"
         SERVERNAME="Production-Slave-DB"
         TAR_COMPRESS="1"  ## 0 - for disable
  • For taking full backup run -: ./xtrabackup.sh full
  • For taking incremental backup run -:  ./xtrabackup.sh incremental
  • For taking restore backup run -: ./xtrabackup.sh restore
  • Restore command is very critical . It will prepare FULL and all incremental in FULL.

How to configure the cron job for automation of this script ?

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

##This CRON will take Daily FULL backup at 12:01 AM and then incremental on every 2 Hour . Before taking the full backup it will remove all the previous backups. You can change the remove policy according to your disk space.
## Herein I'm not automate restore command . I will perform this task manually once needed. You can do as per your policy.

01 00 * * * root cd /xtra-bkp-incr && ./xtrabackup.sh full    
01 */2 * * * root cd /xtra-bkp-incr && ./xtrabackup.sh incremental

For any Query on this script you can contact us or Submit you Query Here!

Please write you valuable comments and feedback in the comment box. Your valuable feedback is highly appreciated .

Please Follow us to update and notify for such type of articles and scripts. 


Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...