mysql 设置数据库只读_MySQL数据库设置为只读模式及测试

最新推荐文章于 2025-01-15 11:52:31 发布

原创最新推荐文章于 2025-01-15 11:52:31 发布 · 702 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql 设置数据库只读

本文展示了如何将MySQL数据库设置为只读模式，通过`show global variables like "%read_only%"`确认状态，然后使用`flush tables with readlock`锁定所有表，确保在维护期间防止写操作。在只读模式下，可以正常查询如data03.t01等表，但更新操作将被阻止，直到执行`unlock tables`解除锁定。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql>mysql>show databases;+--------------------+

| Database |

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

| information_schema |

| bitvc |

| data03 |

| ga |

| jiradb |

| meibi |

| meibi02 |

| mysql |

| performance_schema |

| sbtest |

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

10 rows in set (0.00sec)

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.00sec)

mysql>mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:172.32.1.200Master_User: repl

Master_Port:3307Connect_Retry:60Master_Log_File: mysql-bin.000009Read_Master_Log_Pos:5853Relay_Log_File: huobiDBtest-relay-bin.000002Relay_Log_Pos:6016Relay_Master_Log_File: mysql-bin.000009Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:5853Relay_Log_Space:6195Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_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:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:2003307Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d

Master_Info_File:/data/mysqldata/3308/data/master.info

SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State: Slave hasread all relay log; waiting for the slave I/O thread to updateit

Master_Retry_Count:86400Master_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.00sec)

mysql>mysql> flush tables with readlock;

Query OK,0 rows affected (0.00sec)

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:172.32.1.200Master_User: repl

Master_Port:3307Connect_Retry:60

Master_Log_File: mysql-bin.000009Read_Master_Log_Pos:6531Relay_Log_File: huobiDBtest-relay-bin.000002Relay_Log_Pos:6016Relay_Master_Log_File: mysql-bin.000009Slave_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:0Last_Error:

Skip_Counter:0

Exec_Master_Log_Pos: 5853Relay_Log_Space:6873Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:120Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:2003307Master_UUID: 6f68eea7-76e9-11e4-8f99-00221904cd5d

Master_Info_File:/data/mysqldata/3308/data/master.info

SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State: Waitingfor global readlock

Master_Retry_Count:86400Master_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.00sec)

mysql>mysql>mysql> select * fromdata03.t01;+-----+------+------+

| id1 | a1 | b1 |

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

| 1 | 1 | 1 |

| 2 | 2 | 2 |

| 4 | 4 | 4 |

| 5 | 5 | 5 |

| 6 | 6 | 6 |

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

5 rows in set (0.00sec)

mysql>mysql>unlock tables;

Query OK,0 rows affected (0.00sec)

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:172.32.1.200Master_User: repl

Master_Port:3307Connect_Retry:60

Master_Log_File: mysql-bin.000009Read_Master_Log_Pos:6531Relay_Log_File: huobiDBtest-relay-bin.000002Relay_Log_Pos:6694Relay_Master_Log_File: mysql-bin.000009Slave_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:0Last_Error:

Skip_Counter:0

Exec_Master_Log_Pos: 6531Relay_Log_Space:6873Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No