CODEWITHSUNDEEP
mysql
Rahul Singh
Rahul Singh

Reputation: 41

Mysql user privilege for restore from mysqldump

What are the minimum privilege required for a mysql db user to use the mysqldump file and restore.

  1. Cannot use root db user in my case.
  2. Have taken full backup of all schemas in a dump file using mysqldump utility.
  3. Need to know minimum required privileges to be given to a db user(other than root db user) so that it can be used to do restore from mysqldump file.

Upvotes: 0

Views: 7582

Answers (3)

Jakov Sosic
Jakov Sosic

Reputation: 360

I've started with SUPER, INSERT, & ALTER and tried repeatedly adding new ones until the restore finished successfully.

This is what I've ended up with:

  • SUPER
  • ALTER
  • INSERT
  • CREATE
  • DROP
  • LOCK TABLES
  • REFERENCES
  • SELECT

If you have routines and triggers then you'll need these two additionally:

  • CREATE ROUTINE
  • TRIGGER

Hope this helps.

Upvotes: 3

Michael - sqlbot
Michael - sqlbot

Reputation: 179442

It is not possible to restore a complete, unmodified dump file to a MySQL Server without the SUPER privilege.

The "root" user is not a magical user. It just happens to be a user that is created by default and has GRANT ALL PRIVILEGES ON *.* ... WITH GRANT OPTION. Another user can be given the same privileges.

Restoring a database essentially means obliterating everything on a server and replacing it with something else, including all the user accounts so SUPER is required.

More limited privileges can be used if certain modifications to the dump file are made, such as removing all DEFINER statements, and modifying the way the mysql schema is handled, but those modifications are an advanced topic with system-specific considerations.

Upvotes: 3

lulyon
lulyon

Reputation: 7265

From the Mysql official site:

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.

--single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

In conclusion, privileges are:

  • select (required)
  • lock tables (required)
  • show views and trigger (optional)

Upvotes: -1

Related Questions