mysql 主从同步架构（mysql搭建主从同步实现读写分离）

一、MySQL数据读写分离1.1概念，现在小编就来说说关于mysql 主从同步架构?下面内容希望能帮助到你，我们来一起看看吧!

mysql 主从同步架构

一、MySQL数据读写分离

1.1概念

把客户端访问数据库服务时的查询请求和写请求提交给不同的数据库服务器处理，同时保证写的数据能展现在读的数据库上，即负责写的数据库为主库，读库为从库

1.2实现读写分离的方法

1）人工分离，让程序员执行查询操作时登陆数据库填ip就填从库，修改时填主库；

2）安装第三方软件做代理，提供调度服务，根据访问数据的不同分配给不同的服务器，用户连接时直接连接代理。

1.3读写分离优缺点

1）优点：减轻单台服务器的并发访问压力，同时提高硬件的利用率（分功能工作，各施其职）

2）缺点：只有一台代理，也会有单点故障，当并发高时他也有压力

3）解决方法：搭建高可用集群

1.4工作原理疏理

1） 多台 MySQL 服务器

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

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

2）由 MySQL 代理面向客户端

– 收到 SQL 写请求时,交给服务器 A 处理

– 收到 SQL 读请求时,交给服务器 B 处理

– 具体区分策略由服务设置

二、构建步骤：

2.1基本构建思路

1）搭建mysql主从复制（master作为写，slave作为读）

2）添加一台mysql代理服务器（使用maxscale软件提供此功能）

3）客户端通过代理主及访问mysql数据库，并进行读写测试

2.2 关于mysql代理软件maxscale

1）由mysql的兄弟公司mariadb开发的

2）主配置文件为/etc/maxscale.cnf

3）下载地址：https://downloads.mariadb.com/files/MaxScale

2.3 搭建mysql读写分离

1）环境准备

master服务器

192.168.4.15/24

作为写数据库

slave服务器

192.168.4.25/24

作为读服务器

mysql代理服务器

192.168.4.57/24

提供代理服务，分配请求给数据库服务器

linux客户机

192.168.4.58/24

访问代理服务器测试

ps：实验中我设置了本机登陆数据库系统的root用户密码为123456；

2）搭建mysql主从复制

a主库服务器：

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

[mysqld]

server_id=57

log-bin

binlog-format="mixed"

[root@host57 ~]# systemctl restart mysqld

[root@host57 ~]# mysql -uroot -p123456

mysql> grant replication slave on *.* to repluser@'192.168.4.58' identified by '123456';

mysql> show master status\G;

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

File: host57-bin.000001

Position: 452

b从库服务器：

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

[mysqld]

server_id=58

[root@host58 ~]# systemctl restart mysqld

[root@host58 08.dba-1]# mysql -h192.168.4.57 -urepluser -p123456

//测试能否用授权用户登陆主服务器,登陆成功后退出

[root@host58 ~]# mysql -uroot -p123456

mysql> show slave status;

Empty set (0.00 sec)

mysql> change master to

-> master_host="192.168.4.57",

-> master_user="repluser",

-> master_password="123456",

-> master_log_file="host57-bin.000001",

-> master_log_pos=452;

mysql> start slave;

mysql> show slave status\G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3）部署mysql代理服务器

a 安装软件包

[root@host15 08.dba-1]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

b 修改配置文件

[maxscale]

threads=auto

//定义服务启用的线程数量，让服务根据数据库数量自行选择

[server1]

type=server

address=192.168.4.57

port=3306

protocol=MySQLBackend

//定义数据库服务器

[server2]

type=server

address=192.168.4.58

port=3306

protocol=MySQLBackend

//定义数据库服务器，配置文件只给出了server1的模板，多余的后端服务器配置需要手动添加

[MySQL Monitor]

type=monitor

module=mysqlmon

servers=server1,server2

user=scalemon

passwd=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=maxscale

passwd=123456

max_slave_connections=100%

//定义管理服务

#[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=4007

//定义管理服务使用的端口，端口号需要手动添加上去，默认没写，选用大于1024的端口号，防止与其他服务冲突；

4）在主，从数据库服务器创建授权用户（实际只需在主库上授权，因为会同步过去）

mysql> grant replication slave,replication client on *.* to scalemon@'%' identified by '123456';

//创建监控用户

mysql> grant select on mysql.* to maxscale@'%' identified by '123456';

//授权路由用户

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

5）启动代理服务，查看监听的端口

[root@host15 ~]# maxscale --config=/etc/maxscale.cnf

或[root@host15 ～]# maxscale -f /etc/maxscale.cnf

//-f ：文件

[root@host15 ~]# netstat -ntulp | grep maxscale

tcp6 0 0 :::4006 :::* LISTEN 1928/maxscale

tcp6 0 0 :::4007 :::* LISTEN 1928/maxscale

6）客户端访问测试

连接mysql代理服务器

[root@host25 ~]# mysql -h192.168.4.15 -P4006 -uclient -p123456;

查看所在的数据库并创建数据库，表以及插入记录

MySQL [(none)]> select @@hostname;

MySQL [(none)]> create database haha;

MySQL [(none)]> create table haha.t1(id int(2));

MySQL [haha]> insert into haha.t1 values(1);

切换到从库上查看客户端写入的数据

mysql> select * from haha.t1;

7）在代理服务器上访问管理管端口查看监控状态

[root@host15 ~]# maxadmin -uadmin -pmariadb -P4007

MaxScale> list servers

//登陆管理服务的账号默认是admin，密码为mariadb

8）其他相关指令

停止服务：kill -9 pid号

9）拓展介绍

a 定义监控服务器时设置的用户scalemon,叫监控用户，可自定义用户名，用于监视数据库，监视时用此用户登陆数据库，因此需要数据库授权该用户具有repluser slave,repluser client权限；

b 定义读写分离时设置的用户maxscale，叫路由用户，可自定义用户名，用于查询客户端连接数据库的用户是否在监控数据库里的授权库里存在，存在则客户端输入该用户时才连接成功，因此只需要给路由用户授对mysql库的查询权限即可；

c 读写分离指的是select insert update delete,不包括建库，删库等其他操作，实际应用中用户也没有这些权限

,