MySQL主从复制读写分离授权方案及实践

当配置好MySQL主从复制后，由于数据复制是单向的，所有对数据库的更新操作都必须在主服务器上进行，只有在主库上更新，才能避免用户对主服务器上数据库内容的更新与对从服务器上数据库内容的更新一致，而不会发生冲突。

MySQL复制环境用户授权方案

生产授权方案1

方案1、2对比推荐使用方案1，生产环境中推荐使用忽略授权表方式授权

数据库	用户名	密码	IP地址	端口	权限
主库	web	passwd	192.168.0.12	3306	selectinsertdeleteupdate
从库	web	passwd	192.168.0.13	3306	select

说明：
从库的用户名和密码：主库的web用户同步到从库，然后通过revoke权限回收将insert,delete,update权限回收。

主库用户授权示例：

grant select,insert,update,delete on dbName.* to 'userName'@'localhost' identified by 'password';

提示：特殊的业务可能权限会比较多，如果业务安全性要求不高，也可以给与all privileges权限。

从库用户授权示例：

grant select,insert,update,delete on dbName.* to 'userName'@'localhost' identified by 'password';

revoke insert,update,delete on on dbName.* from 'userName'@'localhost';
当然也可以不回收权限，通过配置文件在[mysqld]区域设置read-only或者在mysql启动时指定read-only参数。修改完记得重启服务
另外：read-only参数可以让slave服务器只允许来自slave服务器线程或者具有super权限（即对用户授权时不能指定有super或all privileges权限）的用户更新。
但可以确保slave服务器不接受来自普通用户的更新，slave服务器启动选项增加--read-only也是同样的功能。

---

生产授权方案2

数据库	用户名	密码	IP地址	端口	权限
主库	web_w	passwd	192.168.0.12	3306	selectinsertdeleteupdate
从库	web_r	passwd	192.168.0.13	3306	select

说明：
在主库授权web_w账号、从库授权web_r账号对应权限权限；
对于开发而言，使用多套用户名密码不专业。

通过忽略授权表的方式防止数据写入从库的方法

在生产环境中，一般会采取忽略授权表方式同步，然后对从服务器(slave)上的用户仅授权select读权限。不同步mysql库，这样的话我们就保证了主库和从库相同的用户可以授权不同的权限。在主从库分别进行如下授权：

数据库	用户名	密码	IP地址	端口	权限
主库	web	passwd	192.168.0.12	3306	selectinsertdeleteupdate
从库	web	passwd	192.168.0.13	3306	select

忽略mysql库和information_schema库的主从同步参数

缺陷：从库切换主库时，连接用户权限问题。可以保留一个从库专门准备接替主。

[mysqld]
binlog-do-db=dbName # 要同步的某个库
replicate-ignore-db=mysql
binlog-ignore-db=mysql  #　不同步的库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

另外：忽略记录binlog日志的参数binlog-ignore-db一般用于系统的库和表。(修改完my.cnf后重启服务)

replication中忽略binlog数据量

master端参数：

--binlog-do-db 二进制日志记录的数据库(多个数据库用,分割)
--binlog-ignore-db 二进制日志忽略的数据库(多个数据库用,分割)

slave端参数：

--replication-do-db 设定需要复制的数据库(多个数据库用,分割)
--replication-ignore-db 设定忽略复制的数据库(多个数据库用,分割)
--replocation-do-table 设定需要复制的表(多个表用,分割)
--replication-ignore-table 设定忽略复制的表(多个表用,分割)
--replication-wild-do-table和--replocation-do-table功能一样，但是可以加通配符。
--replication-wild-ignore-table和--replication-ignore-table功能一样，但是可以加通配符。