# MySQL主从复制读写分离授权方案及实践 当配置好MySQL主从复制后，由于数据复制是单向的，**所有对数据库的更新操作都必须在主服务器上进行**，只有在主库上更新，才能避免用户对主服务器上数据库内容的更新与对从服务器上数据库内容的更新一致，而不会发生冲突。 ## MySQL复制环境用户授权方案 ### 生产授权方案1 *方案1、2对比推荐使用方案1，生产环境中推荐使用忽略授权表方式授权* | 数据库 |用户名 | 密码 | IP地址 | 端口 | 权限 | | -- | -- | -- | -- | -- | -- | | 主库 | web | passwd | 192.168.0.12 | 3306 | `select` `insert` `delete` `update` | | 从库 | 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 | `select` `insert` `delete` `update` | | 从库 | web_r | passwd | 192.168.0.13 | 3306 | `select` | > **说明**： > 在主库授权web_w账号、从库授权web_r账号对应权限权限； > 对于开发而言，使用多套用户名密码不专业。 ### 通过忽略授权表的方式防止数据写入从库的方法 在生产环境中，一般会**采取忽略授权表方式同步**，然后对从服务器(`slave`)上的用户仅授权`select`读权限。不同步`mysql`库，这样的话我们就保证了主库和从库相同的用户可以授权不同的权限。在主从库分别进行如下授权： | 数据库 |用户名 | 密码 | IP地址 | 端口 | 权限 | | -- | -- | -- | -- | -- | -- | | 主库 | web | passwd | 192.168.0.12 | 3306 | `select` `insert` `delete` `update` | | 从库 | 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`功能一样，但是可以加通配符。