mysql读写分离

mysql读写分离

• 原理
  让主数据库（master）处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库（slave）处理SELECT查询操作。

前置准备

主机	说明
192.168.4.51	主数据库
192.168.4.52	从数据库
192.168.4.1	代理服务器
192.168.4.53	客户端

主从数据库搭建

#192.168.4.51
vim /etc/my.cnf
==========================
[mysqld]
server_id=51
log-bin=db51
===================================
systemctl restart mysqld

mysql -uroot -p
	grant replication slave on *.* to look@'%' identified by '123abc...A'
	exit

#192.168.4.52
vim /etc/my.cnf
=============================
[mysqld]
server_id=52
==================================
systemctl restart mysqld

mysql -uroot -p
	change master to 
	master_host='192.168.4.51',
	master_user='look',
	master_password='123abc...A',
	master_log_file='db51.000001',
	master_log_pos='154';
	
	start slave ;
	show slave status;  #查看IO SQL进程是否正常
	
	

#4.51测试
mysql -uroot -p
	create database aa;

#4.52查看
mysql -uroot -p
	show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aa                 | #从库上有aa数据库了
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

配置maxscale代理服务器

#4.1
rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm
vim /etc/maxscale.cnf
============================
[maxscale]
threads=auto            #运行的线程的数量

[server1]            #定义数据库服务器
type=server
address=192.168.4.51        #主服务器ip
port=3306
protocol=MySQLBackend        

[server2]
type=server
address=192.168.4.52        #从服务器IP
port=3306
protocol=MySQLBackend

[MySQL Monitor]                #定义监控的数据库服务器
type=monitor
module=mysqlmon
servers=server1, server2        #监控的数据库列表，不能写ip
user=maxscalemon                    #监控用户
passwd=123abc...A                #密码
monitor_interval=10000        

#[Read-Only Service]        //不定义只读服务
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#passwd=mypwd
#router_options=slave

[Read-Write Service]            #定义读写分离服务
type=service
router=readwritesplit
servers=server1, server2
user=maxscalerouter            #路由用户 用来查询访问代理服务器的用户是否有权限访问真实数据库
passwd=123abc…A                #密码
max_slave_connections=100%


[MaxAdmin Service]        #定义管理服务
type=service
router=cli

#[Read-Only Listener]        //不定义只读服务使用的端口号
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008

[Read-Write Listener]            #定义读写服务使用的端口号
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]        #管理服务使用的端口号
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016     #手动添加，不指定时使用的是默认端口在启动服务以后可以知道默认端口是多少
================================================================================

主从授权用户

#主从同步  所以只要在主库上配置即可
#4.51
mysql -uroot -p
	grant replication slave,replication client on *.* to maxscalemon@'%' identified by '123abc...A';
	grant select on mysql.* to maxscalerouter@'%' identified by '123abc...A';
	
	create database gamedb;#创建测试数据库
	create talbe gamedb.a(id int);#创建表
	grant select,insert on gamedb.* to yaya666@'%' identified by '123abc...A';
	
	
#分别配置代理服务器所需的路由角色权限，监控角色权限的

#4.52查看权限是否成功设置
mysql -uroot -p
	select user,host from mysql.user where user like 'maxscale%'
+----------------+------+
| user           | host |
+----------------+------+
| maxscalemon    | %    |
| maxscalerouter | %    |
+----------------+------+


#代理4.1测试授权
yum -y  install mariadb  #安装提供mysql命令的软件包
mysql -h 192.168.4.51 -umaxscalemon -p123abc…A
mysql -h 192.168.4.52 -umaxscalemon -p123abc…A
mysql -h 192.168.4.51 -umaxscalerouter -p123abc…A
mysql -h 192.168.4.52 -umaxscalerouter -p123abc…A

启动服务器代理服务

maxscale -f /etc/maxscale.cnf   启动服务代理服务
ps -C maxscale  #查看进程
PID TTY          TIME CMD
4004 ?        00:00:02 maxscale

ss -nutlp | grep 4006	#查看读写分离端口
ss -nutlp | grep 4016    #查看管理服务端口

查看服务是否成功

#4.1
maxadmin -uadmin -pmariadb -P4016   #自带用户信息
MaxScale> list servers  #查看服务情况
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.4.51    |  3306 |           0 | Master, Running
server2            | 192.168.4.52    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------



#4.53客户端访问代理服务
mysql -h192.168.4.1 -uyaya666 -p123abc...A
	#成功登陆  可以执行操作了
	show user();	
+----------------------+
| user()               |
+----------------------+
| yaya666@192.168.4.1  |  #登录是在代理服务器4.1登录
+----------------------+

验证读写分离

• 从服务器上插入数据 客户端查询的到，主服务器上没有 证明读分离
• 从服务器stop slave 客户端插入数据 查询主从服务器 只在主上查询到 证明写分离