生产方案1:
主库: 10.0.0.1 3306 用户:web 密码:pass (select,uptade,insert,delete)
从库:10.0.0.2 3306 用户:web 密码:pass (select,uptade,insert,delete)
设置read-only参数确保从库只读,对超级用户无效
[mysqld]
read-only = on
mysql> create database hahakkkk;
ERROR 1290 (HY000): The MySQL server is running with the –read-only option so it cannot execute this statement
生产方案2:
主库: 10.0.0.1 3306 用户:web_w 密码:pass (select,uptade,insert,delete)
从库:10.0.0.2 3306 用户:web_r 密码:pass (select)
风险:web_w连接从库!设置read-only参数确保从库只读,对超级用户无效
[mysqld]
read-only = on
mysql> create database hahakkkk;
ERROR 1290 (HY000): The MySQL server is running with the –read-only option so it cannot execute this statement
生产方案3:
不同步mysql库:主从库分别进行如下授权
主库: 10.0.0.1 3306 用户:web 密码:pass (select,uptade,insert,delete)
从库:10.0.0.2 3306 用户:web 密码:pass (select)
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
缺陷:从库切换主库时候,连接用户权限问题。保留一个从库专门准备接替主库