MySQL 读写分离

Linux System Environment

[root@master ~]# cat /etc/redhat-release                    #==》系统版本
CentOS Linux release 7.5.1804 (Core)
[root@master ~]# uname –r                                   #==》系统内核
3.10.0-862.el7.x86_64
[root@master ~]# uname -m                                   #==》系统位数
x86_64
[root@master ~]# echo $LANG                                 #==》系统字符集
en_US.UTF-8
[root@master ~]# mysql –version                             #==》MySQL版本
mysql  Ver 14.14 Distrib 5.7.27, for linux-glibc2.12 (x86_64) using  EditLine wrapper

MySQL读写分离简介

MySQL读写分离是指两台服务器，已经配置好了MySQL主从同步（一主一从），为了有效利用从库的资源，所有访问数据库读功能指定访问MySQL从库，所有对数据内容更新的指定到MySQL主库执行，这样降低能将MySQL主库的压力。

MySQL主从复制读写分离授权方案
1、授权方案一

主库：创建一个xiaoming用户并授权所有权
mysql>grant all privileges on test.* to ‘xiaoming’@’localhost’ identified by ‘123456’;
从库：从库同步主库，把xiaoming用户inser/delete/update/drop权限回收
mysql>grant all privileges on test.* to ‘xiaoming’@’localhost’ identified by ‘123456’;
mysql>revoke drop,insert,delete,update on test.* from 'xiaoming'@'localhost';
mysql> mysql>show grants for 'xiaoming'@'localhost';

2、授权方案二

主库：创建一个xiaoming_w用户并授权所有权
mysql>grant all privileges on test.* to ‘xiaoming_w’@’localhost’ identified by ‘123456’;
从库：创建一个xiaoming_r用户只授权select权限
mysql>grant all privileges on test.* to ‘xiaoming’@’localhost’ identified by ‘123456’;
mysql>revoke INSERT,DELETE,UPDATE,CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER on test.* from 'xiaoming_r'@'localhost';
mysql>show grants for 'xiaoming_r'@'localhost';

3、授权方案三

mysql库不同步，在MySQL配置文件/etc/my.cnf添加以下参数
binlog-ignore-db=mysql #==》忽略授权表mysql库
binlog-ignore-db=mysql.session
binlog-ignore-db=mysql.sys
主库：创建一个xiaoming_w用户并授权所有权
mysql>grant all privileges on test.* to ‘xiaoming_w’@’localhost’ identified by ‘123456’;
从库：创建一个xiaoming_r用户只授权select权限
mysql>grant all privileges on test.* to ‘xiaoming’@’localhost’ identified by ‘123456’;
mysql>revoke INSERT,DELETE,UPDATE,CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER on test.* from 'xiaoming_r'@'localhost';
mysql>show grants for 'xiaoming_r'@'localhost';

4、授权方案四

通过read-only参数防止数据库写从库的方法
除了上面再从库仅做SELECT的授权方法外，还可以在从库服务器配置文件my.cnf配置文件中添加read-only参数确保从库只读，当然授权用户和 read-only参数二者同时操作效果更佳，这也是生产环境中使用的方案。
Read-only参数可以让从库服务器只允许来自从库器线程或具有SUPER权限或ALL PRIVILEGES权限的用户对数据库更新的操作，其它普通用户是无法更新。
--read-only