This is the ad hoc tutorial on how to provide exceptional or selective privileges to users, also how to backup/restore selectively in MySQL.
- Link to Google Docs Document
- Watch this Tutorial on Youtube
- Linux scripts in Git Repository
- Windows scripts in Git Repository
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
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
Solution for the third scenario
Related resources: