Adding a new mysql user

To give the user 'read only' access to the entire database.

 

a) on local host only

 

grant select on *.* to 'abinitio'@'localhost' identified by 'password'

 

b) acccess from remote server

 

grant select on databasename.* to 'pxxttest'@'<use IP address>' identified by 'pxxxt'

 

 

grant select on pxxxl.* to ' abinitio'@'10.251.13.23' identified by 'ixxxo';

 

 

 

c) from all remote servers

 

grant select on databasename.* to 'pattest'@'%' identified by 'pxxxt'

 

..this doesn't allow local host access though

 

d) to modify a user to allow all access

 

grant select, insert, update, delete on databasename.* to 'pattest'@'%' identified by 'bloguser' ;

 

 

 

EG Creating new bloguser

 

grant select on blogs.* to 'bloguser'@'%' identified by 'bloguser' ;

 

grant select, insert, update, delete on blogs.* to 'bloguser'@'%' ;

 

 

 

Important Note

 

In the mysql Slave Databases the mysql database is excluded from replication using the configuration Replicate_Ignore_DB: sc3_test_database,mysql in the my.cnf of the slave databases.

Hence it is necessary to create any users on the Slave Database that are created on the corresponding Master Databases. The same principle applies while deleting users.

Also the same principle applies to password change for existing users on these Master and Slave Databases.