I have created backup user with limited privileges which will be used by mysqldump.
mysql> show grants for ‘backup’@‘localhost’;
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for backup@localhost |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, FILE, SHOW DATABASES, LOCK TABLES, EXECUTE, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON . TO ‘backup’@‘localhost’
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
However, I also want do dump mysql user grants and user and I have written simple sql statement to dump grants statements
SQLSTMT=“SELECT CONCAT(‘SHOW GRANTS FOR ‘’’,user,‘’‘@’‘’,host,‘’‘;’)”
SQLSTMT=“${SQLSTMT} FROM mysql.user WHERE user<>‘’”
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed ‘s/$/;/g’ > Grants_usingcommand.sql
When we execute above SQL using backup user, the sql statements which will create users and grants, it does not have password information. Password is replaced by secret e.g IDENTIFIED BY PASSWORD
When I run above SQL using root, hashed password is getting displayed. Question is what extra permission do I need to give to backup user so that it can also provide password information required to create user
I have granted SUPER privilege and password is getting displayed for backup user. I want to give only minimum required privileges and not sure whether SUPER is good idea. Any suggestions.
Thanks a lot.