Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Which are the minimum privileges required to get a backup of a MySQL database schema? I am interested only on the schema, not on the data.

Are SELECT privileges required?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

According to the MySQL Documentation

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

Thus, to run

mysqldump --no-data --routines --triggers ...

the privileges required are

You will also need the SELECT privilege on mysql.proc in order to dump Stored Procedures.

share|improve this answer
    
RolandoMySQLDBA Thank you ! –  pmav99 Dec 25 '13 at 19:06
1  
+1 nice explainment some extra information.. The source off Stored Procedures are also in INFORMATION_SCHEMA.ROUTINES within the column ROUTINE_BODY (read dev.mysql.com/doc/refman/5.6/en/routines-table.html)... you dont need SELECT privilege for this table maybe SUPER privilege if you want to backup Stored Procedures what are not off the same user. (NOTE watch out when GRANTING SUPER privilege) –  Raymond Nijland Dec 26 '13 at 12:57
    
@RaymondNijland Makes sense. It is easier to dump Stored Procedures straight from INFORMATION_SCHEMA.ROUTINES in memory than to reread it from mysql.proc. In light of your comment, the same would have to apply to grants. Even though grants are not accessible from the INFORMATION_SCHEMA, grants are in memory and can be dumped straight from memory. For a specific user, the INFORMATION_SCHEMA would only hold whatever grants allowed it to load. –  RolandoMySQLDBA Dec 26 '13 at 15:08
1  
@RolandoMySQLDBA i try to make sense.. its very true grants are cached on connection/thread level for fast database/table changing. and GRANTS are accessible from the INFORMATION_SCHEMA as off MySQL version 5.0 in these VIEWS -> USER_PRIVILEGES (global grants), SCHEMA_PRIVILEGES (database grants), TABLE_PRIVILEGES (table grants) and COLUMN_PRIVILEGES (table column grants) –  Raymond Nijland Dec 26 '13 at 15:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.