Mysql从库read-only配置详解

在做mysql主从同步时我们会碰到一个从库read-only了,下面小编来为各位介绍Mysql从库read-only配置详解,希望文章对各位有用.

为了防止用户对从库进行插入,采用read-only参数.

配置:

[root@Slave-Mysql data]# grep read-only /etc/my.cnf

read-only

试验过程:

主库授权ALL:mysql> grant all on *.* to 'imbyrd'@'localhost' identified by 'admin';

从库测试:

1. [root@Slave-Mysqldata]#/usr/local/mysql/bin/mysql-uimbyrd-p'admin'
2. mysql>usehitest;
3. mysql>insertintotest(id,name)values(14,'fo');
4. QueryOK,1rowaffected(0.14sec)

主库授权select,insert,update,delete

1. mysql>REVOKEallON*.*FROM'imbyrd'@'localhost';
2. mysql>grantselect,insert,update,deleteon*.*to'imbyrd'@'localhost'identifiedby'admin';
3. mysql>showgrantsforimbyrd@'localhost';
4. +----------------------------------------------------------------------------------------------------------------------------------------+
5. |Grantsforimbyrd@localhost|
6. +----------------------------------------------------------------------------------------------------------------------------------------+
7. |GRANTSELECT,INSERT,UPDATE,DELETEON*.*TO'imbyrd'@'localhost'IDENTIFIEDBYPASSWORD'*4ACFE3202A5FF5CF467898FC58AAB1D615029441'|--phpfensi.com
8. +----------------------------------------------------------------------------------------------------------------------------------------+
9. 1rowinset(0.00sec)

从库测试:

1. mysql>usehitest;
2. mysql>insertintotest(id,name)values(16,'dddd');
3. ERROR1290(HY000):TheMySQLserverisrunningwiththe--read-onlyoptionsoitcannotexecutethisstatement

主库配置:

1. mysql>grantallon*.*to'imbyrd'@'localhost'identifiedby'admin';
2. mysql>showgrantsforimbyrd@'localhost'＼G
3. ***************************1.row***************************
4. Grantsforimbyrd@localhost:GRANTALLPRIVILEGESON*.*TO'imbyrd'@'localhost'IDENTIFIEDBYPASSWORD'*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
5. 1rowinset(0.00sec)
6. mysql>REVOKESUPERON*.*FROM'imbyrd'@'localhost';
7. mysql>showgrantsforimbyrd@'localhost'＼G
8. ***************************1.row***************************
9. Grantsforimbyrd@localhost:GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOWDATABASES,CREATETEMPORARYTABLES,LOCKTABLES,EXECUTE,REPLICATIONSLAVE,REPLICATIONCLIENT,CREATEVIEW,SHOWVIEW,CREATEROUTINE,ALTERROUTINE,CREATEUSER,EVENT,TRIGGER,CREATETABLESPACEON*.*TO'imbyrd'@'localhost'IDENTIFIEDBYPASSWORD'*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
10. 1rowinset(0.00sec)

从库测试:

1. [root@Slave-Mysqldata]#/usr/local/mysql/bin/mysql-uimbyrd-p'admin'
2. mysql>usehitest;
3. mysql>insertintotest(id,name)values(23,'fddf');
4. ERROR1290(HY000):TheMySQLserverisrunningwiththe--read-onlyoptionsoitcannotexecutethisstatement

结论:当用户权限中没有SUPER权限(ALL权限是包括SUPER的)时,从库的read-only生效.