查看linux mysql 收回权限,Linux学习笔记：MySQL读写分离实验【从库回收权限】

1)在从库上回收用户的写权限(UPDATE/DELETE/INSERT)

这种方法必须保证主库上对应的用户不会发生改动；如果真的发生了改动，该用户的授权又会同步到从库上去，导致回收失效。

实验拓扑图

请留意，Master上由于运行多实例，而本次测试用的是3308端口的实例。

实验：

1)在vmtest上新建用户web，具备增删改权限mysql> grant select,update,insert,delete on mydb.student to 'web'@'192.168.5.%' identified by 'web123';

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'web'@'192.168.5.%';

+--------------------------------------------------------------------------------------------------------------+

| Grants for web@192.168.5.%                                                                                   |

+--------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'web'@'192.168.5.%' IDENTIFIED BY PASSWORD '*67138D0908E294A380CA501A1F1A48898426B13B' |

| GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.`student` TO 'web'@'192.168.5.%'                              |

+--------------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

2)配置主从同步[root@lab ~]# mysql -uroot -p -e 'show slave status\G'

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.5.103

Master_User: rep

Master_Port: 3308

Connect_Retry: 60

Master_Log_File: mysql-bin.000102

Read_Master_Log_Pos: 277

Relay_Log_File: mysqld-relay-bin.000039

Relay_Log_Pos: 422

Relay_Master_Log_File: mysql-bin.000102

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 277

Relay_Log_Space: 724

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

[root@lab ~]# mysql -uroot -p -e 'show processlist\G'

*************************** 1. row ***************************

Id: 25

User: system user

Host:

db: NULL

Command: Connect

Time: 69971

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 26

User: system user

Host:

db: NULL

Command: Connect

Time: 2541

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

以上两个show命令代表已经建立并持续运行同步

3)在从库Lab上回收增删改权限，只保留SELECT[root@lab ~]# mysql -uroot -p -e "revoke insert,update,delete on mydb.student from 'web'@'192.168.5.%';show grants for web@'192.168.5.%';"

+--------------------------------------------------------------------------------------------------------------+

| Grants for web@192.168.5.%                                                                                   |

+--------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'web'@'192.168.5.%' IDENTIFIED BY PASSWORD '*67138D0908E294A380CA501A1F1A48898426B13B' |

| GRANT SELECT ON `mydb`.`student` TO 'web'@'192.168.5.%'                                                      |

+--------------------------------------------------------------------------------------------------------------+

4)在WebTest服务器上分别测试连接主从库

在主库vmtest(IP 192.168.5.103)上插入一条数据[root@WebTest ~]# mysql -uweb -pweb123 -h 192.168.5.103 -P 3308 -e "insert into mydb.student(name,score) values('webtest',88);"

在从库Lab(IP 192.168.5.41)上查询刚才插入的数据。[root@WebTest ~]# mysql -uweb -pweb123 -h 192.168.5.41 -e "select * from mydb.student where name='webtest';"

+----+---------+-------+------+------+------+

| id | name    | score | sex  | age  | qq   |

+----+---------+-------+------+------+------+

|  8 | webtest |    88 | NULL | NULL | NULL |

+----+---------+-------+------+------+------+

5)用web用户尝试在从库上修改和插入数据[root@WebTest ~]# mysql -uweb -pweb123 -h 192.168.5.41 -e "update mydb.student set score=98 where name='webtest';"

ERROR 1142 (42000) at line 1: UPDATE command denied to user 'web'@'192.168.5.141' for table 'student'

[root@WebTest ~]# mysql -uweb -pweb123 -h 192.168.5.41 -e "insert into mydb.student(name,score) values('hacker',100);"

ERROR 1142 (42000) at line 1: INSERT command denied to user 'web'@'192.168.5.141' for table 'student'

[root@WebTest ~]# mysql -uweb -pweb123 -h 192.168.5.41 -e "delete from mydb.student where name='webtest';"

ERROR 1142 (42000) at line 1: DELETE command denied to user 'web'@'192.168.5.141' for table 'student'

由于增删改权限被回收，web用户不能修改student表，但能读，那么就可以实现最基本的读写分离。