Restrict MySQL from server to read-only status (CentOS 6.6)

Restrict MySQL from server to read-only status (CentOS 6.6)

System: CentOS 6.6

MySQL version: 5.1.73

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.

To set MySQL as a read-only command:

Method One: The MySQL service needs to be restarted after the modification.

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/8C/28/wKiom1hjg6KA_ZZ8AACW2jGsZlQ685.png-wh_500x0-wm_3 -wmp_4-s_1816782486.png "title=" 1.png "alt=" Wkiom1hjg6ka_zz8aacw2jgszlq685.png-wh_50 "/>

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M01/8C/24/wKioL1hjg67y2L5fAABiR6Ti2lo296.png-wh_500x0-wm_3 -wmp_4-s_1124113221.png "title=" 2.png "alt=" Wkiol1hjg67y2l5faabir6ti2lo296.png-wh_50 "/>

Method Two:

# 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;

You can also modify the configuration file:

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M00/8C/24/wKioL1hjg8mSAhjxAACPVGHxa4U458.png-wh_500x0-wm_3 -wmp_4-s_92433709.png "title=" 3.png "alt=" Wkiol1hjg8msahjxaacpvghxa4u458.png-wh_50 "/>

Create an Access user and password on the primary server (192.168.26.210): ( note here that our master-slave synchronization is configured to complete )

Use command: Grant all on baidu.* to ' testuser ' @ ' 192.168.%.% ' identified by ' testpasss ';

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8C/28/wKiom1hjg_mRJqhpAACHJyvn630659.png-wh_500x0-wm_3 -wmp_4-s_2979031103.png "title=" 4.png "alt=" Wkiom1hjg_mrjqhpaachjyvn630659.png-wh_50 "/>

View whether user information is synchronized from the server (192.168.26.211).

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8C/24/wKioL1hjhAWTrtsvAACMq-WUc_E771.png-wh_500x0-wm_3 -wmp_4-s_4193308980.png "title=" 5.png "alt=" Wkiol1hjhawtrtsvaacmq-wuc_e771.png-wh_50 "/>

The user information has been synchronized.

Use the new Create user password to log in to the 192.168.26.211 from the server to test the operation.

Mysql-utestuser-p-h192.168.26.211

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8C/28/wKiom1hjhCbh0TcmAACpkaITZFU632.png-wh_500x0-wm_3 -wmp_4-s_2120973610.png "title=" 6.png "alt=" Wkiom1hjhcbh0tcmaacpkaitzfu632.png-wh_50 "/>

Only query-related operations can be performed and write operations cannot be performed.

then proceed set global read_only=0;

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8C/28/wKiom1hjhLziXP59AACpXY2i1XY159.png-wh_500x0-wm_3 -wmp_4-s_1957627775.png "title=" 7.png "alt=" Wkiom1hjhlzixp59aacpxy2i1xy159.png-wh_50 "/>

The data was successfully inserted from the server.

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8C/24/wKioL1hjhM-AOJyZAADTz0fCAwU610.png-wh_500x0-wm_3 -wmp_4-s_3775339493.png "title=" 8.png "alt=" Wkiol1hjhm-aojyzaadtz0fcawu610.png-wh_50 "/>

Note This setting is only valid for normal user actions: Invalid user for special permissions such as root and sync account.

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/8C/28/wKiom1hjhOjyMpG0AACUP8mESnA201.png-wh_500x0-wm_3 -wmp_4-s_3641237051.png "title=" 9.png "alt=" Wkiom1hjhojympg0aacup8mesna201.png-wh_50 "/>

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 0 or faluse state, this time, whether local users or remote access to the database users, Can be read-write, set the READ_ONLY parameter to 1 or true if you want to set it to read-only state, but there are two things to note about setting the Read_only=1 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, viewing slave status through show Status\g Salve command, you can see salve The log on Master is still read and the log is applied 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;

This article is from the "on the Road" blog, please be sure to keep this source http://jdonghong.blog.51cto.com/3473478/1886994

Restrict MySQL from server to read-only status (CentOS 6.6)