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

For MySQL single instance database and master library, if you need to set it to read-only state, you need to do the following:

The command to set MySQL to read-only:

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%"

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

To set slave from the library to read-only, the command you need to execute is:

Mysql > set global read_only=1

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

Mysql > set global read_only=0

The read and write status of the Mysql 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 state. At this time, both local users and users accessing 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:

The 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.

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.

Table locking operation

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.

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.