mysql读写分离

1、作用

充分利用硬件资源，提高数据库服务效率。

因为数据库的“写”（写10000条数据到oracle可能要3分钟）操作是比较耗时的。 但是数据库的“读”（从oracle读10000条数据可能只要5秒钟）。

2、原理

多台数据库服务器，通过主从复制保存数据一致。这些服务器分别提供读写服务，均衡流量。有mysql代理面向客户端，sql写请求给主，读请求给从，也可以作其他读写策略，具体由服务设置。

3、实现mysql读写分离，拓扑图如下：

4、实现mysql读写分离步骤

搭建主从

1）主库192.168.4.10上面操作：

[root@master10~]#vim /etc/my.cnf

[mysqld]

server_id=10//指定服务器ID号

log-bin=master10//启用binlog日志，并指定文件名前缀

2）从库192.168.4.20上面操作

[mysqld]

server_id=20//指定服务器ID号，不要与Master的相同

log-bin=slave20//启动SQL日志，并指定文件名前缀

read_only=1//只读模式

3）主库授权一个用户并查看master的状态

mysql>grant all on*.*to'replicater'@'%'identified by'123456';

mysql>show master status;

4）从库通过CHANGE MASTER语句指定MASTER服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置（参考MASTER上的状态输出）

mysql>change master to master_host='192.168.4.10',

->master_user='replicater',

->master_password='123456',

->master_log_file='master10.000002',

->master_log_pos=738;

mysql>start slave;

mysql>show  slave status\G;

实现mysql读写分离

1）配置数据读写分离服务器192.168.4.100，安装maxscale代理服务(MaxScale是maridb开发的一个MySQL数据中间件)

[root@maxscale mysql]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm  

2）配置maxscale

[root@maxscale mysql]#vim /etc/maxscale.cnf.template

[maxscale]

threads=auto      //运行的线程的数量

[server1]             //定义数据库服务器

type=server

address=192.168.4.10          //数据库服务器的ip

port=3306

protocol=MySQLBackend         //后端数据库

[server2]

type=server

address=192.168.4.20

port=3306

protocol=MySQLBackend

[MySQL Monitor]                //定义监控的数据库服务器

type=monitor

module=mysqlmon

servers=server1,server2          //监控的数据库列表，不能写ip

user=scalemon                       //监视数据库服务器时连接的用户名scalemon

passwd=123456                         //密码123456

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=maxscaled                 //用户名 验证连接代理服务时访问数据库服务器的用户是否存在

passwd=123456//密码

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=4099           //手动添加，不指定时使用的是默认端口在启动服务以后可以知道默认端口是多少

3）根据配置文件的设置，在数据库服务器上添加授权用户（主库执行，从库查看）

mysql> grant replication slave,replication client on *.* to  scalemon@'%' identified by "123456";   ////监控数据库服务器时，连接数据库服务器的用户

mysql>grant select on mysql.*to maxscaled@"%"identified by"123456";   ////验证 访问数据时，连接数据库服务器使用的用户，是否在数据库服务器上存在的，连接用户

4）同时查看授权用户

mysql>select user,host from mysql.user where userin("scalemon","maxscaled");

5）启动服务

[root@maxscale ~]# maxscale -f  /etc/maxscale.cnf

[root@maxscale ~]# netstat  -antup | grep maxscale

6）测试，在本机访问管理端口查看监控状态

[root@maxscale~]# maxadmin-P4099-uadmin-pmariadb

MaxScale>list servers

7）在客户端访问读写分离服务器（没有mysql命令可以安装）

mysql -h读写分离服务ip -P4006 -u用户名 -p密码

[root@slave53~]# mysql  -h  192.168.4.100 -P  4006  -ureplicater   -p123456

 mysql>select  @@hostname;//查看当前主机名

作者：萧鼎迷
链接：https://www.jianshu.com/p/5eda59de2e17