What are the minimum persmission required to restore mysql dump

Question

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.

Answer 1

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.

Answer 2

If your question is about unix permissions, the beauty of MySQL is that you don't need any permissions at all. You can be a seriously underprivileged unix user account, but you can run with full access to MySQL databases if you have the MySQL root user's password.

Imagine I'm logged on to a server as account jim, which has essentially no permissions outside of its home directory BUT has access to the .sql file I want to restore. The root account on the machine has password 'toughpassword1', which I don't know. The mysql root account on the machine has password 'toughmysqlpassword'. I do know this password, so I'm set. I can put this into my cron job:

@hourly mysql -uroot -ptoughmysqlpassword the_database_to_restore < /home/jim/the_mysqldump_file.sql

Or if you prefer

@hourly bzcat /home/jim/the_mysqldump_file.sql | mysql -uroot -ptoughmysqlpassword the_database_to_restore

The only unix permissions required are read access to the_mysqldump_file.sql. The database permissions, which you presumably have, can be stored in the cron job.