MySQL read-only mode setting method and experiment "turn"

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


After practical testing, for MySQL single-instance database and master library, if you need to set to read-only state, you need to do the following and settings:
To set MySQL as a read-only command:
# 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%";


The command to set MySQL from read-only to read-write status:
mysql> unlock tables;
mysql> set global read_only=0;


For salve libraries that need to ensure Master-slave master-slave synchronization, if you want to set the read-only state, the command to execute is:
mysql> set global Read_only=1;


To change the Salve library from a read-only state to a read-write state, the command to execute is:
mysql> set global read_only=0;


For the database read and write state, mainly rely on "read_only" global parameters to set; By default, the database is used for read and write operations, so the READ_ONLY parameter is also a 0 or faluse state, both local users and remote access to the database can read and write operations , set the READ_ONLY parameter to 1 or true if you want to set it to read-only state, but there are two places to be aware of when setting the Read_only=1 state:
1.read_only=1 read-only mode, does not affect the functionality of slave synchronous replication, so after Read_only=1 is set in the MySQL slave library, the slave status is viewed through the show Status\g Salve command. You can see that salve still reads the log on master and applies the log in the slave library to ensure that the master-slave database is synchronized;
2.read_only=1 read-only mode, you can restrict the operation of data modification for ordinary users, but will not restrict the data modification operation of users with super privileges; After setting up read_only=1 in MySQL, normal application users insert, UPDATE, When a DML operation that produces data changes, such as delete, will report that the database is in read-only mode without data changes, but users with super privileges, such as logging in to the database locally or remotely through the root user, or DML operations that can make data changes;

To ensure that all users, including those with super privileges, are not able to read and write, you need to execute the command "flush tables with read lock;" For all table-read locks, so that users with super privileges log on to the database and want to have data changes , you will be prompted to indicate that the table is locked and cannot be modified.

This is done by setting "Read_only=1" and "flush tables with read lock;" Two commands, you can ensure that the database is in read-only mode, there will be no data changes, in the MySQL database migration, limit the Master Master Library can not have any data changes in this way to set.

But at the same time because the command of the table lock is very strict to the database table, if the slave from the library to execute this command, the slave library can read Binlog log from master, but can not apply the log, the slave library can not change data, of course, can not achieve master-slave synchronization, If you use "Unlock tables;" When the global table read lock is lifted, slave will apply the Binlog log read from Master, and continue to ensure that the master-slave database is synchronized consistently.

In order to ensure that the master-slave synchronization can be carried out, in the slave library to ensure that the root of super permissions and other users can only log on locally, no data changes, other remote connected application users are only on demand for select,insert,update,delete and other rights, Guaranteed no super privilege, then only need to set the salve "Read_only=1" mode, you can guarantee the master-slave synchronization, but also to achieve from the library read-only.


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;", the corresponding unlock mode command is: "Unlock tables;".

Of course, after setting the Read_only=1, all the Select query operations can be performed normally.

Turn from:

MySQL read-only mode Setup method and experiment-Yumushui's Column-Blog channel-csdn.net
http://blog.csdn.net/yumushui/article/details/41645469#

MySQL read-only mode setting method and experiment "turn"