mysql只读模式的设置方法与实验

布满荆棘的人生 2022-08-14 04:42 17阅读 0赞

在MySQL数据库中，在进行数据迁移和从库只读状态设置时，都会涉及到只读状态和Master-slave的设置和关系。  
  
  
       经过实际测试，对于MySQL单实例数据库和master库，如果需要设置为只读状态，需要进行如下操作和设置：  
      将MySQL设置为只读状态的命令：  
\# mysql -uroot -p  
mysql> show global variables like "%read\_only%";  
mysql> flush tables with read lock;  
mysql> set global read\_only=1;  
mysql> show global variables like "%read\_only%";  
  
  
将MySQL从只读设置为读写状态的命令：  
mysql> unlock tables;  
mysql> set global read\_only=0;  
  
  
      对于需要保证master-slave主从同步的salve库，如果要设置为只读状态，需要执行的命令为：  
mysql> set global read\_only=1;  
  
  
     将salve库从只读状态变为读写状态，需要执行的命令是：  
mysql> set global read\_only=0;  
  
  
     对于数据库读写状态，主要靠 “read\_only”全局参数来设定；默认情况下，数据库是用于读写操作的，所以read\_only参数也是0或faluse状态，这时候不论是本地用户还是远程访问数据库的用户，都可以进行读写操作；如需设置为只读状态，将该read\_only参数设置为1或TRUE状态，但设置 read\_only=1 状态有两个需要注意的地方：  
      1.read\_only=1只读模式，不会影响slave同步复制的功能，所以在MySQL slave库中设定了read\_only=1后，通过 show slave status\\G 命令查看salve状态，可以看到salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致；  
      2.read\_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作；在MySQL中设置read\_only=1后，普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时，都会报出数据库处于只读模式不能发生数据变化的错误，但具有super权限的用户，例如在本地或远程通过root用户登录到数据库，还是可以进行数据变化的DML操作；

为了确保所有用户，包括具有super权限的用户也不能进行读写操作，就需要执行给所有的表加读锁的命令 “flush tables with read lock;”，这样使用具有super权限的用户登录数据库，想要发生数据变化的操作时，也会提示表被锁定不能修改的报错。

这样通过 设置“read\_only=1”和“flush tables with read lock;”两条命令，就可以确保数据库处于只读模式，不会发生任何数据改变，在MySQL进行数据库迁移时，限定master主库不能有任何数据变化，就可以通过这种方式来设定。

但同时由于加表锁的命令对数据库表限定非常严格，如果再slave从库上执行这个命令后，slave库可以从master读取binlog日志，但不能够应用日志，slave库不能发生数据改变，当然也不能够实现主从同步了，这时如果使用 “unlock tables;”解除全局的表读锁，slave就会应用从master读取到的binlog日志，继续保证主从库数据库一致同步。

为了保证主从同步可以一直进行，在slave库上要保证具有super权限的root等用户只能在本地登录，不会发生数据变化，其他远程连接的应用用户只按需分配为select,insert,update,delete等权限，保证没有super权限，则只需要将salve设定“read\_only=1”模式，即可保证主从同步，又可以实现从库只读。  
  
  
       相对的，设定“read\_only=1”只读模式开启的解锁命令为设定“read\_only=0”;设定全局锁“flush tables with read lock;”，对应的解锁模式命令为：“unlock tables;”.

当然设定了read\_only=1后，所有的select查询操作都是可以正常进行的。  
  
  
  
  
**实验一：**设置了read\_only=1后，远程业务用户进行数据库修改会提示ERROR 1290错误：

(test01@172.32.1.200) [data03]> show tables;
 +------------------+
 | Tables_in_data03 |
 +------------------+
 | t01 |
 | t02 |
 | user |
 +------------------+
 3 rows in set (0.00 sec)
 
 (test01@172.32.1.200) [data03]> 
 (test01@172.32.1.200) [data03]> 
 (test01@172.32.1.200) [data03]> show global variables like "%read_only%";
 +------------------+-------+
 | Variable_name | Value |
 +------------------+-------+
 | innodb_read_only | OFF |
 | read_only | ON |
 | tx_read_only | OFF |
 +------------------+-------+
 3 rows in set (0.00 sec)
 
 (test01@172.32.1.200) [data03]> 
 (test01@172.32.1.200) [data03]> 
 (test01@172.32.1.200) [data03]> delete from t01 where id1=3;
 ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
 (test01@172.32.1.200) [data03]> update t01 set id1=id1+30 where id1=3;
 ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
 (test01@172.32.1.200) [data03]> insert into t01(id1,a1,b1) values(9,9,9);
 ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
 (test01@172.32.1.200) [data03]> 
 (test01@172.32.1.200) [data03]> select * from t01;
 +-----+------+------+
 | id1 | a1 | b1 |
 +-----+------+------+
 | 1 | 1 | 1 |
 | 2 | 2 | 2 |
 | 4 | 4 | 4 |
 | 5 | 5 | 5 |
 | 6 | 6 | 6 |
 +-----+------+------+
 5 rows in set (0.00 sec)
 
 (test01@172.32.1.200) [data03]>

**实验二：**设定了全局读写后，具有super权限的用户进行数据修改后，也会提示错误ERROR 1223：

mysql> use data03;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 
 Database changed
 mysql> show tables;
 +------------------+
 | Tables_in_data03 |
 +------------------+
 | t01 |
 | t02 |
 | user |
 +------------------+
 3 rows in set (0.00 sec)
 
 mysql> select * from t01;
 +-----+------+------+
 | id1 | a1 | b1 |
 +-----+------+------+
 | 1 | 1 | 1 |
 | 2 | 2 | 2 |
 | 4 | 4 | 4 |
 | 5 | 5 | 5 |
 | 6 | 6 | 6 |
 +-----+------+------+
 5 rows in set (0.00 sec)
 
 mysql> 
 mysql> show global variables like "%read_only%";
 +------------------+-------+
 | Variable_name | Value |
 +------------------+-------+
 | innodb_read_only | OFF |
 | read_only | ON |
 | tx_read_only | OFF |
 +------------------+-------+
 3 rows in set (0.00 sec)
 
 mysql> 
 mysql> 
 mysql> insert into t01(id1,a1,b1) values(8,8,8); 
 Query OK, 1 row affected (0.00 sec)
 
 mysql> update t01 set a1=a1+10 where id1=2;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed: 1 Warnings: 0
 
 mysql> delete from t01 where id1=4;
 Query OK, 1 row affected (0.00 sec)
 
 mysql> select * from t01;
 +-----+------+------+
 | id1 | a1 | b1 |
 +-----+------+------+
 | 1 | 1 | 1 |
 | 2 | 12 | 2 |
 | 5 | 5 | 5 |
 | 6 | 6 | 6 |
 | 8 | 8 | 8 |
 +-----+------+------+
 5 rows in set (0.00 sec)
 
 mysql> 
 mysql> flush tables with read lock;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> 
 mysql> insert into t01(id1,a1,b1) values(9,9,9);
 ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
 mysql> 
 mysql> update t01 set a1=a1+10 where id1=5;
 ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
 mysql> 
 mysql> delete from t01 where id1=5;
 ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
 mysql> 
 mysql>

**实验三：**MySQL从库设定read\_only=1后主从同步正常，设定表读锁后，不能同步，解除读锁后，主从同步恢复。

mysql> 
 mysql> show databases;
 +--------------------+
 | Database |
 +--------------------+
 | information_schema |
 | bitvc |
 | data03 |
 | ga |
 | jiradb |
 | meibi |
 | meibi02 |
 | mysql |
 | performance_schema |
 | sbtest |
 +--------------------+
 10 rows in set (0.00 sec)
 
 mysql> 
 mysql> 
 mysql> 
 mysql> show global variables like "%read_only%";
 +------------------+-------+
 | Variable_name | Value |
 +------------------+-------+
 | innodb_read_only | OFF |
 | read_only | ON |
 | tx_read_only | OFF |
 +------------------+-------+
 3 rows in set (0.00 sec)
 
 mysql> 
 mysql> show slave status\G
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.32.1.200
 Master_User: repl
 Master_Port: 3307
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000009
 Read_Master_Log_Pos: 5853
 Relay_Log_File: huobiDBtest-relay-bin.000002
 Relay_Log_Pos: 6016
 Relay_Master_Log_File: mysql-bin.000009
 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: 5853
 Relay_Log_Space: 6195
 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: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 2003307
 Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d
 Master_Info_File: /data/mysqldata/3308/data/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Master_Retry_Count: 86400
 Master_Bind: 
 Last_IO_Error_Timestamp: 
 Last_SQL_Error_Timestamp: 
 Master_SSL_Crl: 
 Master_SSL_Crlpath: 
 Retrieved_Gtid_Set: 
 Executed_Gtid_Set: 
 Auto_Position: 0
 1 row in set (0.00 sec)
 
 mysql> 
 mysql> flush tables with read lock;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> show slave status\G
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.32.1.200
 Master_User: repl
 Master_Port: 3307
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000009
 Read_Master_Log_Pos: 6531
 Relay_Log_File: huobiDBtest-relay-bin.000002
 Relay_Log_Pos: 6016
 Relay_Master_Log_File: mysql-bin.000009
 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: 5853
 Relay_Log_Space: 6873
 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: 120
 Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error: 
 Last_SQL_Errno: 0
 Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 2003307
 Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d
 Master_Info_File: /data/mysqldata/3308/data/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Waiting for global read lock
 Master_Retry_Count: 86400
 Master_Bind: 
 Last_IO_Error_Timestamp: 
 Last_SQL_Error_Timestamp: 
 Master_SSL_Crl: 
 Master_SSL_Crlpath: 
 Retrieved_Gtid_Set: 
 Executed_Gtid_Set: 
 Auto_Position: 0
 1 row in set (0.00 sec)
 
 mysql> 
 mysql> 
 mysql> select * from data03.t01;
 +-----+------+------+
 | id1 | a1 | b1 |
 +-----+------+------+
 | 1 | 1 | 1 |
 | 2 | 2 | 2 |
 | 4 | 4 | 4 |
 | 5 | 5 | 5 |
 | 6 | 6 | 6 |
 +-----+------+------+
 5 rows in set (0.00 sec)
 
 mysql> 
 mysql> unlock tables;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> show slave status\G
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.32.1.200
 Master_User: repl
 Master_Port: 3307
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000009
 Read_Master_Log_Pos: 6531
 Relay_Log_File: huobiDBtest-relay-bin.000002
 Relay_Log_Pos: 6694
 Relay_Master_Log_File: mysql-bin.000009
 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: 6531
 Relay_Log_Space: 6873
 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: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 2003307
 Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d
 Master_Info_File: /data/mysqldata/3308/data/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Master_Retry_Count: 86400
 Master_Bind: 
 Last_IO_Error_Timestamp: 
 Last_SQL_Error_Timestamp: 
 Master_SSL_Crl: 
 Master_SSL_Crlpath: 
 Retrieved_Gtid_Set: 
 Executed_Gtid_Set: 
 Auto_Position: 0
 1 row in set (0.00 sec)
 
 mysql> select * from data03.t01;
 +-----+------+------+
 | id1 | a1 | b1 |
 +-----+------+------+
 | 1 | 1 | 1 |
 | 2 | 12 | 2 |
 | 5 | 5 | 5 |
 | 6 | 6 | 6 |
 | 8 | 8 | 8 |
 +-----+------+------+
 5 rows in set (0.00 sec)