Remove user from master but allow it read only access on slave

A user "crystal" existed on Master-1 and Slave-2. To prevent load on the master, limit crystal users access to slave only.

Make sure you know the password for the user you are removing, as you will need to recreate it on the slave

 

Log on to mysql on master as root

 

Use mysql;

delete from user where user='crystal';

flush privileges;

 

Log off root account, and try and connect as user to make sure account has been deleted successfully

 

mysql -ucrystal -p

Enter password:

ERROR 1045 (28000): Access denied for user 'crystal'@'localhost' (using password: YES)

 

Deleting the user will replicate to the slave, so need to recreate the user on the slave.

 

Confirm that the account has been deleted from the slave

 

mysql -ucrystal -p

Enter password:

ERROR 1045 (28000): Access denied for user 'crystal'@'localhost' (using password: YES)

 

Recreate the account on the slave, as root

 

            Use mysql;

            grant select on *.* to 'crystal'@'%' identified by '<password>';

            flush privileges;

            select * from user;

Log off root account, try to connect using new account and check access

           

            mysql -ucrystal –p

            use CALLDRIVER;

            select * from Calls limit 1;

OK.