Not the answer you need?
Register and ask your own question!

backup user used for mysqldump

sanjay92sanjay92 ContributorCurrent User Role Beginner
I have created backup user with limited privileges which will be used by mysqldump.

mysql> show grants for 'backup'@'localhost';
+
+
| Grants for [email protected] |
+
+
| 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 <secret>

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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.