MySQL Selective/Exceptional Permissions and Backup/Restore

By | November 20, 2018

Bismillahi-r-Rahmani-r-Rahim (In the name of Allah, the most Compassionate, the most Merciful)

Assalamu alaikum wa rahmatullahi wa barakatuh (May the peace, mercy, and blessings of Allah be with you)


This is the ad hoc tutorial on how to provide exceptional or selective privileges to users, also how to backup/restore selectively in MySQL.

Below content is complementary to the video tutorial above.

Scenarios

Let’s say we have a database with lots of tables in it. Consider following scenarios:

1. We need to grant read-only access for all the tables except the table tbl2 and the tables beginning with __ prefix;
2. We need to dump the database but ignore all the tables ending with _log. Or we need to dump only tables starting with tbl1 keyword.
3. We need to restore only 2 tables from the dump file created previously and add prefix restored_ to the restored tables.

Unfortunately, there are no natural / built-in ways to accomplish the above tasks. So, I will show how to create automative bash scripts for each task both for linux (.sh) and windows (.bat). All the scripts are provided in Resources section in Github repository.

Solution for the first scenario

users.sh

#!/bin/bash

# Define the database and root authorization details
db_host='localhost'
db_name='adhoctuts'
db_user='root'
db_pass='Adhoctuts2018#'

# Define the query to get the needed tables
table_list=$(mysql -h $db_host -u $db_user -p"$db_pass" -se "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema='$db_name' and table_name not like 'tbl1' AND table_name not like '\_\_%';" $db_name | cut -f1)

# Convert the query result into the array
table_arr=(${table_list//,/ })

# Declare the associative array of the users as username=>password pair
# e.g: declare -A user_list=(["'user1'"]="pass1" ["'user2'"]="pass2")
# In our case there is a single user
declare -A user_list=(["'aht_r'@'localhost'"]="Adhoctuts2018#")
for user in "${!user_list[@]}"
do
    pass=${user_list[$user]}
    # Recreate user
    mysql -h $db_host -u $db_user -p"$db_pass" -se "drop user if exists $user; create user $user identified by '$pass';"

    # Provide SELECT privilege
    mysql -h $db_host -u $db_user -p"$db_pass" -se "revoke all privileges, grant option from $user;" $db_name
    mysql -h $db_host -u $db_user -p"$db_pass" -se "grant usage on $db_name.* TO $user;" $db_name
    for tbl in "${table_arr[@]}"; do
        echo "grant select on $tbl TO $user"
        mysql -h $db_host -u $db_user -p"$db_pass" -se "grant select on $tbl TO $user;" $db_name
    done
done

Note: You need to execute this script every time the new tables are added to the database or after any other structural changes.

Solution for the second scenario

backup.sh

#!/bin/bash

# Define the database and root authorization details
db_host='localhost'
db_name='adhoctuts'
db_user='root'
db_pass='Adhoctuts2018#'

# Define the ignore list
tmp=$(mysql -h $db_host -u $db_user -p"$db_pass" -se "select group_concat(concat(' --ignore-table=',table_schema,'.',table_name)) into @tbl from information_schema.tables where table_schema='$db_name' and table_name like '%_log';select @tbl;" $db_name | cut -f1)
ignore=${tmp//,/""}
mysqldump -h $db_host -u $db_user -p"$db_pass" --skip-add-locks --skip-extended-insert $ignore $db_name > dump_ignore.sql

# Define the table list using the same logic
tmp=$(mysql -h $db_host -u $db_user -p"$db_pass" -se "select group_concat(concat(' ',table_name)) into @tbl from information_schema.tables where table_schema='$db_name' and table_name like 'tbl1%';select @tbl;" $db_name | cut -f1)
tbl_list=${tmp//,/""}
mysqldump -h $db_host -u $db_user -p"$db_pass" --skip-add-locks --skip-extended-insert $db_name $tbl_list > dump_tbl_only.sql

Solution for the third scenario

restore.sh

#!/bin/bash

# Where to restore
db_host='localhost'
db_name='adhoctuts'
db_user='root'
db_pass='Adhoctuts2018#'

dump_file='/root/dump_ignore.sql'

# Associative table list array as source_table=>destination_table pairs
declare -A tbl_list=( ["tbl1"]="restored_tbl1" ["tbl2"]="restored_tbl2")

for tbl in "${!tbl_list[@]}"
do
    echo "Restore $tbl to ${tbl_list[$tbl]}"
    # extract the content between drop table and Table structure for, also replace the table name
    sed -n -e '/DROP TABLE IF EXISTS `'$tbl'`/,/\/*!40000 ALTER TABLE `'$tbl'` ENABLE KEYS \*\/;/p' $dump_file > tbl.sql
    sed -i 's/`'$tbl'`/`'${tbl_list[$tbl]}'`/g' tbl.sql
    mysql -h $db_host -u $db_user -p"$db_pass" $db_name < tbl.sql
    rm -f tbl.sql
done

Related resources: