User Privileges for MySQL administration and automation

To automate MySQL administration like using auto backup script, the user privileges play an important role on how MySQL server to be control with enough security measures to prevent mis-using such user role.

Assuming "mysqldump" and "mysqladmin" are going to be used in such automation, the following privileges should be clearly identified and applied to a particular user role for successful MySQL administration:

• SUPER
• SELECT
• SHOW DATABASES
• SHOW VIEW
• LOCK TABLES
• RELOAD
• SHUTDOWN
• PROCESS
• REPLICATION SLAVE
• REPLICATION CLIENT

Example of assigning privileges:

>c:\xampp\mysql\bin\mysql –u –p –-port=3306
Enter password: ************

mysql>CREATE USER ‘replica’@’localhost’ IDENTIFIED BY ‘password’;
mysql>GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’localhost’;

For details of particular privilege, please refer to the manual page:
http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html