运维的那点事

mysql读写分离

2019-05-18  本文已影响61人  无字天书

第一节基础

--读写分离的原理

多台mysql服务器

分别提供读写服务,均衡流量

通过主从复制保持数据一致性

由mysql代理面向客户端

收到sql写请求时,交给服务器a处理

收到sql读请求时,交给服务器b处理

具体区分策略由服务设置

读写分离:数据的读,写分离

优点

1.增加冗余

2.增加了机器的处理能力

3.对于读操作为主的应用,使用读写分离是最好的场景,因为可以确保写的服务器压力更小,而读又可以接受点时间上的延迟。

缺点

  单点故障

  容易形成瓶颈

拓扑图:

第二节实战

--构建思路

部署mysql一主一从 同步结构

  已经搭建好mysql主从复制

基于上一个实验的结果

其中slave为只读

添加一台mysql代理服务器(读写分离服务器)

部署/启用maxscale

client测试

1)mysql中间件

Mysqlcat

Maxscale

Mysql-proxy

--配置

1,mysql一主一从同步结构

1)配置主服务

[root@mysql-51 ~]# vim /etc/my.cnf

[mysqld]

log_bin=master51

server_id=51   #mysql主从里,id必须唯一,范围:1~255

binlog_format="mixed"

[root@mysql-51 ~]# systemctl restart mysqld   #重启刷新文件配置

mysql> show master status\G; #查看主库master状态,查看当前使用的binlog日志文件

*************************** 1. row ***************************

             File: master51.000001

         Position: 441

     Binlog_Do_DB:

 Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

mysql> grant replication slave on *.* to repluser@'%' identified by '123456'; #授权给从服务器,单个数据库授权无效,必须设置*.*

2)配置从服务器

mysql> show slave status;   #查看从库信息

mysql> show master status;       #查看binlog日志,查看主库信息

[root@mysql-52 ~]# vim /etc/my.cnf

[mysqld]

server_id=52     #从库id,要唯一

[root@mysql-52 ~]# systemctl restart mysqld

mysql> change master to

    -> master_host='192.168.4.51',

    -> master_user='repluser',

    -> master_password='123456',

-> master_log_file='master51.000001',  #与主库binlog日志名相同

-> master_log_pos=441;    #偏移量,与主库相同

提示:任意参数指定不对,io线程读取不了信息

mysql> start slave;    #启动slave(io,sql线程)

mysql> show slave stattus\G;

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.4.51

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master51.000001

Read_Master_Log_Pos: 441

               Relay_Log_File: mysql-52-relay-bin.000002

                Relay_Log_Pos: 319

        Relay_Master_Log_File: master51.000001

Slave_IO_Running: Yes             #看到yes就成功了

Slave_SQL_Running: Yes   #ok了

          ........

提示:sql线程在执行中继日志的sql命令时,一旦出错,sql线程立即停止

方案:停掉slave,在从库新建相关表,重启slave

2,mysql代理服务器配置

使用软件-->maxscale

配置文件:/etc/maxscale.cnf

[root@myweb-57 jluo]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

[root@myweb-57 jluo]# vim /etc/maxscale.cnf

[maxscale]             #进程名

threads=auto   #线程数量,默认1

[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]   #监视mysql服务

type=monitor

module=mysqlmon

servers=server1, server2

user=scalemon      #maxscale登录mysql服务用户名,用于监视mysql 是否running,                        

passwd=123456    #主从结构是否正常,谁是主库,谁是slave

monitor_interval=10000    #每隔10s检查一次

#detect_stale_master=true       #当所有的slave都不可用时,select查询请求会转发到master。

#[Read-Only Service]    不需要指明只读

#type=service

#router=readconnroute

#servers=server1

#user=scalemon

#passwd=mypwd

#router_options=slave

[Read-Write Service]   #定义读写分离服务

type=service

router=readwritesplit

servers=server1, server2

user=pliadmin    #maxscale连接mysql服务检查客户端

passwd=123456   #使用的用户名和密码用的用户名,密码

max_slave_connections=100%  #所有的slave提供select查询服务

#max_slave_replication_lag = 5      # slave超时5秒,就把请求转发到其他slave

[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   #与上面的[Read-Write Service]

protocol=MySQLClient

port=4006           #maxscale服务监听的端口号

[MaxAdmin Listener]

type=listener

service=MaxAdmin Service  #与上面的[MaxAdmin Service]一样

protocol=maxscaled

socket=default

port=4026                    #自定义端口号,不写使用默认端口

#user=maxscale          #用户名默认admin

#passwd=Mhu87p2D  #密码默认mariadb

51,52添加授权

51,52主机都要添加权限

mysql> grant select,insert on db1.* to rose@'%' identified by '123456'; #授予客户端相应访问权限

mysql> grant replication slave,replication client on *.* to scalemon@'%'

-> identified by '123456';           #授权给maxscale

mysql> grant select on mysql.* to pliadmin@'%' identified by '123456'; #授权给maxscal

提示:授权地址必须写’%’,否则会自动添加上ipv6地址,就会出错

replication slave:常用于建立复制时所需要用到的用户权限,也就是slave server必须被master server授权具有该权限的用户,才能通过该用户复制master server 的binlog日志sql语句。

并且"SHOW SLAVE HOSTS"这条命令和REPLICATION SLAVE权限有关,否则执行时会报错:

  ERROR 1227 (42000): Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation

replication client:不可用于建立复制,有该权限时,只是多了可以使用如"SHOW SLAVE STATUS"、"SHOW MASTER STATUS"等命令。

在5.6.6版本以后,也可以使用"SHOW BINARY LOGS"

52主机

mysql> select user,host from mysql.user;

+-----------+-----------+

| user      | host      |

+-----------+-----------+

| pliadmin  | %         |

| scalemon  | %         |

57主机

[root@myweb-57 jluo]# maxscale -f /etc/maxscale.cnf    #开启服务

[root@myweb-57 jluo]# ss -tunlp | grep maxscale

tcp    LISTEN     0      128      :::4026                 :::*                   users:(("maxscale",pid=4498,fd=12))

tcp    LISTEN     0      128      :::4006                 :::*                   users:(("maxscale",pid=4498,fd=11))

[root@myweb-57 jluo]# killall -9 maxscale  #停止服务

提示:注意57主机上不能运行mysql数据库服务

3)客户端验证

在maxscale本机连接管理端口

Maxadmin  -uadmin  -pmariadb  -P端口

访问maxscale代理

Mysql  -h服务器地址 -P端口 -u用户名 -p密码

在57本机访问管理服务查看监控信息

[root@myweb-57 jluo]# maxadmin -uadmin -pmariadb -P4026

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

第三节测试

在客户端主机连接57对数据进行访问,测试能否实现读写分离

测试一:

51主机

mysql> insert into db1.t1 values(11);

Client主机

[root@mysql-50 ~]# mysql -h192.168.4.57 -P4006 -urose -p123456

mysql> select * from db1.t1;

+------+

| id   |

+------+

|   11 |

测试二:

52主机

mysql> insert into db1.t1 values(52);  #主机51没有这条数据

Client主机

mysql> select * from db1.t1;

+------+

| id   |

+------+

|   11 |

|   52 |

测试三:

Client主机:

mysql> insert into db1.t1 values(50);

mysql> select * from db1.t1;

+------+

| id   |

+------+

|   11 |

|   52 |

|   50 |

综上三个测试可知,实现了读写分离


看官福利:


结束语:

更多精彩内容持续更新中,关注我,有你更精彩。

上一篇 下一篇

猜你喜欢

热点阅读