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?


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.

  • 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) 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. 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) Dec 26 '13 at 15:24
  • 1
    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
  • RELOAD is also needed, if you use --master-data option. Oct 18 '20 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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