Mysql – What are the minimum persmission required to restore thesql dump

MySQLmysqldump

I'm trying to create a cronjob which will be populating the data on the server from a local machine. What are the best permission which are required to restore mysql Dump

I want to restore the dump on the web server I want to know what are the least privileges that A user on the Web server should have so that It can restore the database on the web server.

1) what server is the original database on?

MySQL Local Machine.

2) What mysqldump command do you run as what user to get the sql file?

mysqldump -u user -p pass dbname > db.sql "Here i don't have permissions issue."

3) What server are you restoring the dump file to? Or is it the same machine?

Its a Webserver machine resides in the cloud. And having MySQL server here as well. There is no issue of the local and web server MySQL version.

4) If not, how are you transferring the sql dump (i.e. which user account?)
mysql -hweb.server.ip -u restore_user -phalal db < db.sql

5) What command are you hoping to run on the second server to restore the database?

I am executing the command mentioned in the 4th question on the local machine. which restores the dump remotely.

Now I am having issue of the restore_user permissions I don't want to give the maximum permission to the restore_user only the necessary permissions should be there as the data is much sensitive.

Best Answer

For normal database restore operations, the MySQL database user (which you pass as an argument into the mysqldump command) will needs privileges related to:

  1. STRUCTURE (Schema)
  2. DATA manipulation

For common STRUCTURE operations the user typically will need:

  • DROP
  • CREATE
  • ALTER
  • TRIGGER

DATA privileges typically should be

SELECT, INSERT, UPDATE and DELETE.

Importantly database structure administration privelege GRANT can be revoked. By starting with this limited set and then adding privileges on an as needed basis you are improving the containment policies.

Related Question