Shulou(Shulou.com)06/01 Report--

In MySQL databases, the settings and relationships of read-only status and Master-slave are involved in both data migration and read-only state settings from the library.

After actual testing, for MySQL single instance database and master library, if you need to set it to read-only state, you need to do the following operations and settings:

The command that sets MySQL to read-only in general single instance case:

# mysql-uroot-pmysql > 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%"

The command to set MySQL from read-only to read-write:

Mysql > unlock tables;mysql > set global read_only=0

For salve libraries that need to ensure master-slave synchronization of master-slave, if you want to set it to read-only state, the command you need to execute is:

Mysql > set global read_only=1; turn on the read-only switch

To change the salve library from read-only to read-write, the commands you need to execute are:

Mysql > set global read_only=0; turn on the read / write switch

The read and write status of the database is mainly set by the "read_only" global parameter.

By default, the database is used for read and write operations, so the read_only parameter is also 0 or faluse, so both local users and users who access the database remotely can read and write.

To set the read_only to read-only, set the read_only=1 parameter to 1 or TRUE, but there are two things to note about setting the state:

1.read_only=1 read-only mode does not affect the function of slave synchronous replication, so after setting read_only=1 in the MySQL slave library and checking the salve status through the show slave status\ G command, you can see that salve will still read the logs on the master and apply the logs in the slave library to ensure the synchronization of the master and slave databases.

2.read_only=1 read-only mode, which limits the data modification operations of ordinary users, but does not limit the data modification operations of users with super permissions. After setting read_only=1 in MySQL, ordinary application users will report the error that the database is in read-only mode and cannot change data when they perform DML operations such as insert, update, delete and other data changes, but users with super permissions, such as logging in to the database locally or remotely through root users, can still perform DML operations of data changes.

In order to ensure that all users, including those with super authority, cannot read and write, it is necessary to execute the command "flush tables with read lock;" to add a read lock to all tables, so that users with super authority can log in to the database, and when they want to change the data, they will also prompt the table to be locked and cannot be modified.

In this way, by setting the commands "read_only=1" and "flush tables with read lock;", you can ensure that the database is in read-only mode without any data changes. When MySQL migrates the database, it can be set in this way that the master master database cannot have any data changes.

But at the same time, because the command with table lock is very strict to the database table, if slave executes this command from the library, the slave library can read the binlog log from master, but the log cannot be applied, the slave library cannot change the data, and of course, the master-slave synchronization cannot be realized, so if you use "unlock tables" "release the global table read lock, and slave will apply the binlog logs read from master, and continue to ensure consistent synchronization between master and slave database.

In order to ensure that the master-slave synchronization can be carried out all the time, on the slave library, it is necessary to ensure that root and other users with super permissions can only log in locally and will not change the data. Other remote connected application users are only assigned to select,insert,update,delete and other permissions as needed to ensure that there is no super permission, then you only need to set the salve to "read_only=1" mode to ensure master-slave synchronization and read-only slave library.

In contrast, set the unlock command for "read_only=1" read-only mode to set "read_only=0"; set the global lock "flush tables with read lock;", and the corresponding unlock mode command is: "unlock tables;".

Of course, after setting read_only=1, all select query operations can be carried out normally.

Experiment 1: when read_only=1 is set, remote business users who modify the database will prompt ERROR 1290 error:

Copy (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 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 | 5 | 5 | 5 | 6 | 6 | 6 | 6 | +-- -- + 5 rows in set (0.00 sec) (test01@172.32.1.200) [data03] >

Experiment 2: after global read and write is set, users with super permission will also prompt error ERROR 1223 after data modification:

View plain copymysql > 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 | 5 | 5 | 5 | 6 | 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 > insert into T01 (id1) A1 values b1) 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 | 2 | 2 | 5 | 5 | 6 | 6 | 6 | 8 | 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; 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 >

Experiment 3: master-slave synchronization is normal after setting read_only=1 in MySQL slave library, but cannot be synchronized after setting table read lock, and master-slave synchronization is restored after the read lock is released.

Copy

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 01 row in set (0.00 sec) mysql > select * from data03.t01 +-+ | id1 | A1 | b1 | +-+ | 1 | 1 | 1 | 2 | 2 | 2 | 5 | 5 | 6 | 6 | 6 | 8 | 8 | 8 | 8 | +-- -+ 5 rows in set (0.00 sec)

# #

.FLUSH TABLES WITH READ LOCK

This command is a global read lock, and all tables in all libraries are locked read-only after the command is executed. It is generally used in database online backup, when the write operation of the database will be blocked and the read operation will proceed smoothly.

The unlocked statement is also unlock tables.

2.LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

This command is a table-level lock, which can be customized to lock a table. For example, lock tables test read; does not affect the write operation of other tables.

The unlock statement is also unlock tables.

Key points:

Both statements need to pay attention to one feature when executing, that is, implicitly submitted statements. Unlock tables is implicitly executed when exiting the MySQL terminal. That is, if you want the table lock to take effect, you must keep talking all the time.

Read lock and wirte lock of P.S. MYSQL

Read-lock: allows other concurrent read requests, but blocks write requests, that is, you can read at the same time, but no writes are allowed. Also known as shared lock

Write-lock: other concurrent read and write requests are not allowed and are exclusive. Also known as exclusive lock

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.