877-230-8196

MySQL backup rights Print E-mail
User Rating: / 0
PoorBest 
Tuesday, 06 May 2008 22:13

Allowing a mysql backup client to read all data in the database is critical to appropriately backup all data.  Many people will do local dumps on their server and then simply copy those logs remotely.  This is certainly safe, but you still don't want the passwords that provide write-access to all data in all of your databases stored in plain-text.  Creating a user that has read-only access is key.

grant select,show databases,process on *.* to 'my-backup-client'@'ip-address' identified by 'my-password';

This grant statement is also good for a user that needs access to see server process information also (for monitoring and things of the sort).  I've toned down the statement to where it should work on MySQL 4 and 5 as there are a few additional rights on MySQL5 that didn't exist in 4 that I didn't include.  Keep in mind that these rights will not permit the locking of tables, which is really recommended to do a inclusive dump of all data in the databases.  The problem with locks is that writes are suspended during this time, so it could cause some application related issues, which is usually not worth the trade-off.