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

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

Solution for the third scenario

restore.sh


Related resources: