Linux Learning Note: MySQL Read and write separation experiment "Reclaim permissions from Library"

1) Reclaim the user's write permissions from the Vault (Update/delete/insert)

This method must ensure that the corresponding user on the main library does not change, and if it does change, the user's authorization will be synchronized to the library, resulting in the recovery failure.

Experimental topological diagram

Note that there are multiple instances running on master, and this test uses a 3308 port instance.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7A/3D/wKiom1al2Lezavt4AAKsLak8T-U260.jpg "title=" Qq20160125160644.jpg "alt=" Wkiom1al2lezavt4aakslak8t-u260.jpg "/>

Experiment:

1) Create a new user web on Vmtest with Add and delete permissions

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 [email  protected]%                                                                                       |+------------------------ --------------------------------------------------------------------------------------+| 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) Configure Master-Slave synchronization

Reference http://coosh.blog.51cto.com/6334375/1738068 no longer detailed here.

[[email protected] ~]# 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: 0Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                 last_sql_errno: 0                last_sql_error:[[email protected] ~]# mysql  -uroot -p -e  ' Show processlist\g '                    *************************** 1. row       Id: 25   User: system  user   host:      db: nullcommand: connect    Time: 69971  State: Waiting for master to send event    Info: NULL*************************** 2. row ***************************      Id: 26   User: system user   Host:      &nBsp;db: nullcommand: connect   time: 2541  state: has read  all relay log; waiting for the slave I/O thread to  Update it   info: null

The above two show commands represent an established and continuously running synchronization

3) Recover additions and deletions from the library Lab and retain select only

[[email protected] ~]# mysql -uroot -p -e  "Revoke insert,update,delete  on mydb.student from  ' web ' @ ' 192.168.5.% ';show grants for [email  Protected] ' 192.168.5.% '; " +-------------------------------------------------------------------------------------------------------------- +| grants for [email protected]%                                                                                        |+------------------------------------------------------------------------------------------------ --------------+| grant usage on *.* to  ' web ' @ ' 192.168.5.% '  IDENTIFIED  by password  ' *67138d0908e294a380ca501a1f1a48898426b13b '  | |  GRANT SELECT ON  ' mydb '. ' Student '  TO  ' web ' @ ' 192.168.5.% '                                                          |+------------------------ --------------------------------------------------------------------------------------+

4) test the connection master and slave libraries separately on the WebTest server

Inserting a piece of data on the main library vmtest (IP 192.168.5.103)

[Email protected] ~]# mysql-uweb-pweb123-h 192.168.5.103-p 3308-e "insert into mydb.student (Name,score) VALUES (' Webt EST ', 88); "

Query the data you just inserted from the library Lab (IP 192.168.5.41) .

[Email protected] ~]# 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) Try to modify and insert data from the library with a Web user

[[email protected] ~]# 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 ' [[email protected] ~]#  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 ' [[Email protected] ~]# 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 ' 

Because the user can not modify the student table, but can read, the most basic read-write separation is realized because the permission is recycled.

Linux Learning Note: MySQL Read and write separation experiment "Reclaim permissions from Library"