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）配置主从同步
参考http://coosh.blog.51cto.com/6334375/1738068 这里不再详细写了
[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表，但能读，那么就可以实现最基本的读写分离。
