mysql数据库基于二进制日志的主从复制(异步)，基于msyql-proxy的读写分离

SS_CC_Go

墨衍会员 · AI 创作全网分发码龄9年

关注

原创最新推荐文章于 2026-05-13 16:51:25 发布 526 阅读 AI 写同款· GEO 优化 ›

使用数据库异步复制实现分布式事务 1.背景介绍使用数据库异步复制实现分布式事务作者：禅与计算机程序设计艺术背景介绍当今互联网时代的架构挑战在当今的互联网时代，随着微服务架构的普及和云计算技术的发展，越来越多的应阅读详情

一、异步复制(基于二进制日志)

1、工作原理：

基本上是分为三步：

1.主数据库（Master）将变更信息写入到二进制日志文件中，这里需要注意的是旧版本的MySQL数据库默认是不开启二进制日志的，强烈建议在安装好数据库启动之前一定要先检查一下二进制日志文件是否开启，即使不做主从复制架构也要开启，否则当数据库启动之后再开启二进制日志时需要重新启动数据库。

2.从数据库（Slave）开启一个IO工作线程，通过该IO线程与主数据库建立一个普通客户端连接，主数据库会启动一个二进制日志转储线程（binglog dump thread），从数据库的IO线程通过这个转储线程读取主库上的变更事件，并将变更事件记录到中继日志中（relay_log），如果从数据库的IO线程读取速度追赶上主库的事件变更，在没有得到新变更的通知时，IO线程会进入Sleep状态。

3.从数据库还会启动一个SQL Thread线程，这个线程从中继日志（relay_log）中读取变更事件，并将变更同步到从数据库中。同时，可以通过配置选项，除了将变更存储到数据库中，也可以将变更事件同时存储在从数据库的二进制日志中。

2、实验环境：

server1：172.25.27.101	mater节点
server2：172.25.27.102	slave节点

3、创建master节点
数据库安装：

安装包:
    mysql-community-client-5.7.24-1.el7.x86_64.rpm
    mysql-community-common-5.7.24-1.el7.x86_64.rpm
    mysql-community-libs-5.7.24-1.el7.x86_64.rpm
    mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
    mysql-community-server-5.7.24-1.el7.x86_64.rpm
yum install mysql* -y

vim /etc/my.cnf
    [mysqld]
    server-id=1        主节点
    log-bin=mysql-bin
cat /var/log/mysqld.log | grep password        查看数据库的临时密码
安全初始化：
    mysql_secure_installation
    输入临时密码
    重新设定密码：ZHAOjun@123
    .
    .
    .

登陆数据库：

mysql -uroot -pZHAOjun@123
    CREATE USER 'repl'@'172.25.27.%' IDENTIFIED BY 'ZHAOjun@123';
    GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.25.27.%';
    flush privileges;
    show plugins;
    show master status;    #创建slaves节点会用到
    | File             | Position |
    | mysql-bin.000002 |     1685 |

4、创建slave节点
安装数据库：

安装包：
    mysql-community-client-5.7.24-1.el7.x86_64.rpm
    mysql-community-common-5.7.24-1.el7.x86_64.rpm
    mysql-community-libs-5.7.24-1.el7.x86_64.rpm
    mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
    mysql-community-server-5.7.24-1.el7.x86_64.rpm
yum install mysql* -y

vim /etc/my.cnf
    [mysqld]
    server-id=2        主节点
cat /var/log/mysqld.log | grep password        查看数据库的临时密码
安全初始化：
    mysql_secure_installation
    输入临时密码
    重新设定密码：ZHAOjun@123
    .
    .
    .

登陆数据库：

mysql -uroot -pZHAOjun@123  #用到主节点 show master status; 的参数
    CHANGE MASTER TO
    MASTER_HOST='172.25.27.101',
    MASTER_USER='repl',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=1685;
start slave    启动子节点
show slave status\G; 查看字节状态
    最后两行参数为yes才ok;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.27.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2343
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 978
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

################测试################
在master节点创建数据库
创建表，插入数据
而后在slave节点查看数据库，查看表
如果能够看到内容则ok

二、读写分离

什么是读写分离？

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
为什么要实现读写分离？

因为数据库的“写”（写10000条数据到oracle可能要3分钟）操作是比较耗时的。
但是数据库的“读”（从oracle读10000条数据可能只要5秒钟）。
所以读写分离，解决的是，数据库的写入，影响了查询的效率。
什么时候要读写分离？
数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库主从同步。可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。memcache 或是表折分，或是搜索引擎。都是解决方法。
1、实验原理

2、实验环境:

server1,server2已经实现主从分离

server1：172.25.27.101	master节点
server2：172.25.27.102	slave节点
server3：172.25.27.103	mysql-proxy节点

3、server3配置

tar -zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
mv mysql-proxy /usr/local
cd /usr/local/mysql-proxy
mkdir conf
mkdir log
ls21
        bin  include  lib  libexec  licenses  share   conf   log

配置文件参数
    ./bin/mysql-proxy --help-proxy
    ./bin/mysql-proxy --help
find . -name *.lua        查找.lua脚本

写配置文件：
cd conf
vim mysql-proxy.conf
    proxy-address=0.0.0.0:3306
    proxy-backend-addresses=172.25.27.101:3306                指定master后端，可读可写数据
    proxy-read-only-backend-addresses=172.25.27.102:3306      指定slave后端，读取数据
    proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua    指定读写分离lua脚本，需要在此脚本中修改参数
    pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid                       指定pid文件
    log-file=/usr/local/mysql-proxy/log/mysql-proxy.log                        指定日志文件
    plugins=proxy        添加proxy模块
    log-level=debug    设置日志类型为debug
    keepalive=true      奔溃时尝试重启
    daemon=true         以守护进程方式运行

修改读写分离脚本.lua文件
vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw.splitting.lua
    -- connection pool
    if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
            min_idle_connections = 1,    远程客户端连接最少为1个
            max_idle_connections = 2,   最大为2个，大于两个之后会进行读写分离

            is_debug = false
        }
    end

启动程序：
/usr/local/mysql-proxy/conf/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf    修改权限否则会报错

/usr/local/mysql-proxy/conf/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

4、测试

server1:
    grant select,update,insert on *.* to kehu@'%' identified by 'ZHAOjun@123';
    创建一个远程登陆的用户
localhost:
     mysql -h 172.25.27.103 -ukuhu -pZHAOjun@123                          远程连接大于等于三次才能触发读写分离(在rw_splitting.lua脚本中指定)
    

server3:
1）、查看以下文件是否生成
     /usr/local/mysql-proxy/log/mysql-proxy.pid
     /usr/local/mysql-proxy/log/mysql-proxy.log
2）、查看.log，可以看到日志中已经显示连接到server1,server2数据库
    2019-07-29 14:50:09: (message) chassis-unix-daemon.c:136: [angel] we try to keep     PID=2377 alive
    2019-07-29 14:50:09: (debug) chassis-unix-daemon.c:157: waiting for 2377
    2019-07-29 14:50:09: (debug) chassis-unix-daemon.c:121: we are the child: 2377
    2019-07-29 14:50:09: (critical) plugin proxy 0.8.5 started
    2019-07-29 14:50:09: (debug) max open file-descriptors = 1024
    2019-07-29 14:50:09: (message) proxy listening on port 0.0.0.0:3306
    2019-07-29 14:50:09: (message) added read/write backend: 172.25.27.101:3306
    2019-07-29 14:50:09: (message) added read-only backend: 172.25.27.102:3306
    2019-07-29 14:52:53: (debug) [network-mysqld.c:1134]: error on a connection (fd: -1 event: 0). closing client connection

3）、在server3安装lsof
    lsof -i：3306    查看数据库的连接情况


localhost:

     mysql -h 172.25.27.103 -ukuhu -pZHAOjun@123 

     创建表，插入数据

master:

    查看是否有插入数据

收录于