主从复制及读写分离解决方案

原创

已于 2023-11-02 16:36:25 修改 · 2.6k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java #数据库 #mycat

于 2021-02-18 14:18:20 首次发布

2.2 SpringBoot读写分离代码实现

3、MyCat实现读写分离

3.1 MyCat主从复制读写分离中的应用

3.2 Mycat应用场景

3.3 Mycat不适合的应用场景

1 、为什么需要读写分离

1.1 读写分离的背景

数据量很大时候，我们的数据库面临着很大的压力，这时候我们需要从架构方面来解决这一问题，在一个网站中读的操作很多，写的操作很少，这时候我们需要配置读写分离，把读操作和写操作分离出来，最大程度的利用好数据库服务器。读写分离的实现就是在执行SQL的时候，根据判断读操作还是写操作映射到不同的数据库上。

1.2 读写分离的原理

读写分离的实现原理如下图所示：

watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1eGlhbmcxOTg1MTExNA==,size_16,color_FFFFFF,t_70

主节点必须启用二进制日志，记录任何修改了数据库数据的事件。
从节点开启一个线程（I/O Thread)把自己扮演成 MySQL的客户端，通过 MySQL协议，请求主节点的二进制日志文件中的事件
主节点启动一个线程（DumpThread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个）。
从节点启动另外一个线程（SQL Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。

1.3 复制中线程的作用

从节点：

I/O Thread: 从 Master 节点请求二进制日志事件，并保存于中继日志中。
SQL Thread: 从Relay log 中读取日志事件并在本地完成重放。

主节点：

Dump Thread:为每个 Slave 的 I/O Thread 启动一个 dump 线程，用于向从节点发送二进制事件。

思考：从节点需要建立二进制日志文件吗？看情况，如果从节点需要作为其他节点的主节点时，是需要开启二进制日志文件的。这种情况叫做级联复制。如果只是作为从节点，则不需要创建二进制文件

1.4 MySQL复制特点

异步复制：主节点中一个用户请求一个写操作时，主节点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成，再响应用户。主节点只需要把写入操作在本地完成，就响应用户。但是，从节点中的数据有可能会落后主节点，为什么?因为网络通讯需要时间！

2、主从复制实现

2.1 主从节点配置过程

主节点：

1）、启用二进制日志。

2）、为当前节点设置一个全局唯一的server_id。

3）、创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

1）、启动中继日志。

2）、为当前节点设置一个全局唯一的server_id。

3）、使用有复制权限的用户账号连接至主节点，并启动复制线程。

准备两台服务器安装Mysql

192.168.223.128（主）

192.168.223.129（从）

rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm #下载
yum -y install mysql-community-server #rpm安装

#加入开机启动
systemctl enable mysqld
#启动mysql服务进程
systemctl start mysqld
#初始化,执行命令,重置密码 
mysql_secure_installation
#会依次出现以下问题。
    Set root password? [Y/n]
    是否设置root用户的密码 (y后【设置登录密码】)
    Remove anonymous users? [Y/n]
    是否删除匿名用户 (y)
    Disallow root login remotely? [Y/n]
    是否禁止root远程登录 (n)
    Remove test database and access to it? [Y/n]
    是否删除test数据库(y)
    Reload privilege tables now? [Y/n]
    是否重新加载授权信息 (y)

# 先进入mysql
mysql -u root -p
# 授权(root用户)远程连接权限(不建议)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '远程登录密码' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# 使用单独的远程登录用户(推荐)
GRANT ALL PRIVILEGES ON *.* TO '新用户名'@'%' IDENTIFIED BY '远程登录密码' WITH GRANT OPTION;
FLUSH PRIVILEGES;

配置mysql主从：

#------------------------------------------#主库设置-------------------------------------------# 
#主库修改配置 vim /etc/my.cnf，具体位置可能不一样，通过whereis my.cnf查看，有可能在usr/my.cnf下
 
#主库配置添加如下
server-id = 1
log-bin = master-bin
log-bin-index = master-bin.index 
 
#3:主库 从库重启
service mysql restart
 
#4:主库设置
mysql -uroot -proot #进入主库
#查看主库状态
show MASTER status;
#创建主从连接账户repl
CREATE USER repl;
#提供给从库账号repl密码123456 跟主库连接，有slave的权限        
GRANT replication slave ON *.* TO 'repl'@'192.168.223.129' identified by '123456';
 
#设置用户权限 主库可以增删改查(当然你高兴直接用root也行)
#创建主库操作账户work
CREATE USER work;
GRANT SELECT,UPDATE,DELETE, INSERT  ON *.* TO 'work'@'%' identified by '123456' WITH GRANT OPTION;
 
#刷新生效
FLUSH PRIVILEGES;
#查询repl权限
show grants for 'repl';

#------------------------------------------#5:从库设置-------------------------------------------# 
#从库修改配置 vim /etc/my.cnf，配置添加如下
server-id = 2
relay-log-index = relay-log.index
relay