一、主从复制读写分离授权方案
当配置好MySQL主从复制后，所有对数据库内容的更新必须在主服务器上进行。那么为什么所有的更新都要在主服务器上进行呢？因为数据复制是单向的，只有在主库上更新，才能避免用户对主服务器上数据库内容的更新与对从服务器上数据库内容的更新一致，避免发生冲突。那么又该如何保证用户在主服务器上进行更新呢？下面有三种方案可供选择：
- 主库用户授权
- 从库用户授权
- 主从库用户授权
（一）主库用户授权
主从库信息：
| 数据库类别 | 用户 | 密码 | 端口 | IP | 权限 |
| 主库 | admin | 123456 | 3306 | 192.168.0.111 | select、insert、delete、update |
| 从库 | admin | 123456 | 3307 | 192.168.0.111 | select |
权限赋予的方式：
- 主库赋予权限、从库回收
主库赋予select、insert、delete、update权限：
GRANT SELECT,INSERT,UPDATE,DELETE ON 'crm'.* TO 'admin'@'192.168.0.%' IDENTIFIED BY '123456'
从库同步主库的admin用户，然后回收insert、delete、update权限。或者
- 主库赋予权限、从库不回收
这种方式就是从库不回收权限，但是因为是同步主库的用户信息和权限，所以通过设置read-only参数保证从库只读。
（二）从库用户授权
主从库信息：
| 数据库类别 | 用户 | 密码 | 端口 | IP | 权限 |
| 主库 | admin_w | w123456 | 3306 | 192.168.0.111 | select、insert、delete、update |
| 从库 | admin_r | r123456 | 3307 | 192.168.0.111 | select |
主库赋予select、insert、delete、update权限：
GRANT SELECT,INSERT,UPDATE,DELETE ON 'crm'.* TO 'admin_w'@'192.168.0.%' IDENTIFIED BY 'w123456'
从库同步主库的所有数据，当然此时还有admin_w用户，那么如何保证从库只读呢？
从库创建admin_r用户，并且将其设置为只读：
GRANT SELECT ON 'crm'.* TO 'admin_r'@'192.168.0.%' IDENTIFIED BY 'r123456'
但是这样会有一个问题就是从库还存在admin_w账户，它拥有写的权限，所有在从库中如果保证所有的账户都是只有只读，那么就是设置read-only参数来解决同步来的账户写的风险。
（三）主从库用户授权（推荐）
主从库信息：
| 数据库类别 | 用户 | 密码 | 端口 | IP | 权限 |
| 主库 | admin | 123456 | 3306 | 192.168.0.111 | select、insert、delete、update |
| 从库 | admin | 123456 | 3307 | 192.168.0.111 | select |
该种方式是在同步时不同步主库的mysql库，然后分别在自己的数据库中创建各自的用户，然后分别授权。
主库赋予select、insert、delete、update权限：
GRANT SELECT,INSERT,UPDATE,DELETE ON 'crm'.* TO 'admin'@'192.168.0.%' IDENTIFIED BY '123456'
从库赋予select权限：
GRANT SELECT ON 'crm'.* TO 'admin'@'192.168.0.%' IDENTIFIED BY '123456'
但是也会有一个问题当主从进行切换时，会产生权限的问题。所以你可以一主多从，使用一个从库专门接替主库（创建一个和主库一样的账户）。
二、主从复制读写分离实战
此处实战采用的是第三种方式，使用忽略授权表的方式，不同步mysql库。
1、主库配置文件
# vim /data/3306/my.cnf ... [mysqld] ...
replicate-ignore-db = mysql binlog-ignore-db = mysql binlog-ignore-db = performance_schema binlog-ignore-db = information_schema ...
在主库配置文件中加入上面四个参数即可，同步时忽略上面的库。
2、主从库操作
# 启动主库 [root@hadoop-slave1 ~]# vim /data/3306/mysql [root@hadoop-slave1 ~]# /data/3306/mysql start MySQL is running... # 启动从库 [root@hadoop-slave1 ~]# rm -rf /data/3307/mysql.sock [root@hadoop-slave1 ~]# rm -rf /data/3307/*.pid [root@hadoop-slave1 ~]# /data/3307/mysql start Starting MySQL... # 连接主库 [root@hadoop-slave1 ~]# mysql -uroot -p -S /data/3306/mysql.sock Enter password: # 查看主库用户信息 mysql> select host,user from mysql.user; +---------------+------+ | host | user | +---------------+------+ | 127.0.0.1 | root | | 192.168.0.% | rep | | ::1 | root | | hadoop-slave1 | | | hadoop-slave1 | root | | localhost | | | localhost | root | +---------------+------+ 7 rows in set (0.01 sec) # 主库中创建测试用户 mysql> create user admin@localhost identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) # 连接从库查看是否同步过来了 root@hadoop-slave1 ~]# mysql -uroot -p -S /data/3307/mysql.sock Enter password: mysql> select user,host from mysql.user; +-------+---------------+ | user | host | +-------+---------------+ | root | 127.0.0.1 | | rep | 192.168.0.% | | root | ::1 | | | hadoop-slave1 | | root | hadoop-slave1 | | | localhost | | root | localhost | +-------+---------------+ 8 rows in set (0.00 sec)
上面的操作就是主库设置忽略授权表，创建用户后，从库并没有将用户同步过来的过程。
3、从库只读不写测试
上面做到的就是没有同步主库的用户，但是从库只读不写是在从库中创建一个admin用户然后赋予读的权限：
-- 从库创建admin用户 mysql>create user admin@localhost identified by '123456'; -- 从库admin用户授权 mysql> grant select on *.* to 'admin'@localhost identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) -- 退出从库，使用admin登录，创建数据库失败，无写入权限 [root@hadoop-slave1 ~]# mysql -uadmin -p -S /data/3307/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 ... mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | reptest | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> create database crm; ERROR 1044 (42000): Access denied for user 'admin'@'localhost' to database 'crm' mysql>
另外一种控制从库只读的方式就是从库设置read-only参数：
- 从库配置文件设置read-only参数
# vim /data/3307/my.cnf ... [mysqld] ... read-only ...
- 创建用户并授权
创建一个新用户，并且授权，注意的是这里你可以将其授权拥有插入的权限。
- 重启从库
- 使用新用户登录
- 测试插入数据仍旧失败
特别注意的是read-only参数设置好后，重启服务；不能使用拥有super或者all privileges权限的用户登录，否则read-only参数失效。