Mysql从库read-only配置详情

2024-07-24 12:35:24

字体：大中小

来源：转载

供稿：网友

　　在做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';

　　从库测试:

　　[root@Slave-Mysql data]# /usr/local/mysql/bin/mysql -uimbyrd -p'admin'
　　mysql> use hitest;
　　mysql> insert into test(id,name) values(14,'fo');
　　Query OK, 1 row affected (0.14 sec)
　　主库授权select,insert,update,delete

　　mysql> REVOKE all ON *.* FROM 'imbyrd'@'localhost';
　　mysql> grant select,insert,update,delete on *.* to 'imbyrd'@'localhost' identified by 'admin';
　　mysql> show grants for imbyrd@'localhost';
　　+----------------------------------------------------------------------------------------------------------------------------------------+
　　| Grants for imbyrd@localhost |
　　+----------------------------------------------------------------------------------------------------------------------------------------+
　　| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'imbyrd'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' | --phpfensi.com
　　+----------------------------------------------------------------------------------------------------------------------------------------+
　　1 row in set (0.00 sec)
　　从库测试:

　　mysql> use hitest;
　　mysql> insert into test(id,name) values(16,'dddd');
　　ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
　　主库配置:

　　mysql> grant all on *.* to 'imbyrd'@'localhost' identified by 'admin';
　　mysql> show grants for imbyrd@'localhost'＼G
　　*************************** 1. row ***************************
　　Grants for imbyrd@localhost: GRANT ALL PRIVILEGES ON *.* TO 'imbyrd'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
　　1 row in set (0.00 sec)
　　mysql> REVOKE SUPER ON *.* FROM 'imbyrd'@'localhost';
　　mysql> show grants for imbyrd@'localhost'＼G
　　*************************** 1. row ***************************
　　Grants for imbyrd@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'imbyrd'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
　　1 row in set (0.00 sec)
　　从库测试:

　　[root@Slave-Mysql data]# /usr/local/mysql/bin/mysql -uimbyrd -p'admin'
　　mysql> use hitest;
　　mysql> insert into test(id,name) values(23,'fddf');
　　ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
　　结论:当用户权限中没有SUPER权限(ALL权限是包括SUPER的)时,从库的read-only生效.

（编辑：武林网）

上一篇：配置Mysql数据库的主从同步教程详情

下一篇：MYSQL数据库多实例配置流程详解