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?

up vote 6 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.

  • 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
  • I had to add "SUPER" but did not need "TRIGGERS" even though we have many triggers. MySQL 5.5 – Damon Feb 20 '17 at 2:43

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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