Below is a simple script which backup Mysql users & grants into separate sql file. So,if you are taking particular database backup then this script might be useful for backup of all users & their corresponding privileges.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
#!/bin/bash dbhost=localhost dbuser=root dbpassword=Redhat@123 dbport=3306 user_path=/home/grant/create_user.sql user_path1=/home/grant/users_read.txt user_path2=/home/grant/all_grants.sql ####generating create user statment########### `mysql -B -N -h$dbhost -u$dbuser -p$dbpassword -P$dbport -e "SELECT CONCAT('create user',' ','\'', user,'\'@\'', host, '\'',' ','IDENTIFIED WITH',' ', plugin,' ','AS',' ','\'',authentication_string,'\'',';') FROM user WHERE user != 'root' AND user != ''" mysql > $user_path` if [ $? -eq 0 ]; then ########generating user & host file which will be used by next command################# `mysql -B -N -h$dbhost -u$dbuser -p$dbpassword -P$dbport -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'root' AND user != ''" mysql > $user_path1` if [ $? -eq 0 ]; then ##########Reading user & host file and generating final grant & privileges file################# `while read line; do mysql -B -N -h$dbhost -u$dbuser -p$dbpassword -P$dbport -e "SHOW GRANTS FOR $line"; done < $user_path1 > $user_path2` ####################finally,using sed command for proper formatting################# `sed -i 's/$/;/' $user_path2` else echo "Unable to generate second file" fi else echo "Unable to generate first file" fi |
