备案控制台
探索云世界
开发者社区 数据库 文章 正文

案例：MySQL主从复制与读写分离

2023-06-30 81
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL Serverless，价值2615元额度，1个月
简介： 案例：MySQL主从复制与读写分离

       既然如此，那就让我们开始享受今日这份晚宴吧🥘

       下图中，一台MySQL主服务器带两台MySQL从服务器做了数据复制，前端应用在进行数据库写操作时，对主服务器进行操作，在进行数据库读操作做时，对两台从服务器进行操作，这样大量减轻了主服务器的压力。

 

MySQL主从复制原理

MySQL支持的复制类型：

  1. 基于语句的复制。在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
  2. 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
  3. 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

 

MySQL读写分离原理

🍖MySQL常见的读写分离分为两种：

  • 基于程序代码内部实现

在代码中根据select、insert进行路由分类，这类方法也是目前生产环境应用最广泛的。

  • 基于中间代理层实现

       代理一般位于客户端和服务器之间，代理服务器连接到客户端的请求后判断后转发到后端数据库，有两个代表性程序，MySQL-Proxy、Amoeba。

 

案例环境

       如图配置实验环境，关闭防火墙、selinux、配置IP地址。开始环境为MySQL已安装快照，MySQL搭建详情参考上边的配置。

搭建MySQL主从复制

配置时间同步

  • 主节点服务器建立时间同步环境。
1. [root@mysql ~]# yum -y install ntp              //安装ntp
2. [root@mysql ~]# vim /etc/ntp.conf               //配置ntp，加入下面两行配置
3. server 127.127.1.0
4. fudge 127.127.1.0 stratum 8
5. [root@mysql ~]# systemctl restart ntpd          //启动
6. [root@mysql ~]# systemctl enable ntpd
  • 两台从服务器进行同步
1. [root@1 ~]# yum -y install ntpdate
2. [root@1 ~]# ntpdate 192.168.1.101
3. 28 Jul 08:56:50 ntpdate[4665]: no server suitable for synchronization found

配置MySQL Master

  • 三台服务器启动MySQL服务，设置MySQL服务器的root密码。
1. [root@mysql ~]# systemctl start mysqld
2. [root@mysql ~]# mysqladmin -u root password 'pwd123'

注意：以下配置在主服务器下做。

  • 在/etc/my.cnf中修改或增加下面内容。
1. [root@mysql ~]# vim /etc/my.cnf
2. [mysqld]                                        //在mysqld配置项下添加三行配置
3. 
4. server-id = 11
5. log_bin = master-bin
6. log-slave-updates = true
7. [root@mysql ~]# systemctl restart mysqld        //配置后重启服务
  • 登录MySQL程序，给授权给从服务器。
1. [root@mysql ~]# mysql -uroot -p
2. mysql>  GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.1.%' IDENTIFIED BY '123456';
3. Query OK, 0 rows affected (0.01 sec)
4. 
5. mysql> flush privileges;
6. Query OK, 0 rows affected (0.00 sec)
7. 
8. mysql> show master status;
9. +-------------------+----------+--------------+------------------+-------------------+
10. | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
11. +-------------------+----------+--------------+------------------+-------------------+
12. | master-bin.000001 |      410 |              |                  |                   |
13. +-------------------+----------+--------------+------------------+-------------------+
14. 1 row in set (0.00 sec)

配置从服务器

  • 在/etc/my.cnf中添加三行内容，开启中继日志功能。

🍝注意：两台从服务器都需要配置，并且三台的server_id不能相同。

1. [root@1 ~]# vim /etc/my.cnf 
2. [mysqld]                                    //在mysqld配置项下添加三行配置 
3. 
4. server_id = 22
5. relay-log=relay-log-bin
6. relay-log-index=slave-relay-bin.index
7. [root@1 ~]# systemctl restart mysqld
  • 登录MySQL配置时间同步

master_log_file为主服务器的File参数。

master_log_pos为主服务器的Position参数。

1. [root@1 ~]# mysql -uroot -p
2. ......//省略部分内容
3. mysql> change master to master_host='192.168.1.101',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=410;
4. Query OK, 0 rows affected, 2 warnings (0.03 sec)
  • 启动时间同步并查看slave状态（确认两个yes）。停止服务使用"stop slave;"命令。
1. mysql> start slave;                         //启动服务
2. Query OK, 0 rows affected (0.00 sec)
3. 
4. mysql> show slave status\G                  //查看Slave状态
5. *************************** 1. row ***************************
6.                Slave_IO_State: Waiting for master to send event
7.                   Master_Host: 192.168.1.101
8.                   Master_User: myslave
9.                   Master_Port: 3306
10.                 Connect_Retry: 60
11.               Master_Log_File: master-bin.000001
12.           Read_Master_Log_Pos: 410
13.                Relay_Log_File: relay-log-bin.000002
14.                 Relay_Log_Pos: 284
15.         Relay_Master_Log_File: master-bin.000001
16.              Slave_IO_Running: Yes            //此项应为yes
17.             Slave_SQL_Running: Yes            //此项应为yes
18.               Replicate_Do_DB: 
19.           Replicate_Ignore_DB:

       🍤配置从服务器1.102后记得配置从服务器1.103哦

验证主从复制

  • 在主、从服务器上登录MySQL，都查看一下数据库。
1. [root@mysql ~]# mysql -u root -p
2. mysql> show databases;
3. +--------------------+
4. | Database           |
5. +--------------------+
6. | information_schema |
7. | mysql              |
8. | performance_schema |
9. | test               |
10. +--------------------+
11. 4 rows in set (0.01 sec)
  • 在主服务器创建库。
1. mysql> create database test_db;
2. Query OK, 1 row affected (0.00 sec)
  • 两台从服务器查看数据库，显示数据库相同，则主从复制成功。
1. mysql> show databases;
2. +--------------------+
3. | Database           |
4. +--------------------+
5. | information_schema |
6. | mysql              |
7. | performance_schema |
8. | test               |
9. | test_db            |
10. +--------------------+
11. 5 rows in set (0.00 sec)

搭建MySQL读写分离

       Amoeba项目开源框架于2008年发布一款Amoeba for MySQL软件。这个软件致力于MySQL的分布式数据库前端代理层，它主要为应用层访问MySQL的时候充当SQL路由功能，并具有负载均衡、高可用性、SQL过滤、读写分离、可路由相关的到目标服务器、可并发请求多台数据库。

安装Java环境

  • 安装Java环境
1. [root@localhost ~]# mount /dev/cdrom /media
2. mount: /dev/sr0 写保护，将以只读方式挂载
3. [root@localhost ~]# cp /media/jdk-6u14-linux-x64.bin /root
4. [root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin 
5. [root@localhost ~]# ./jdk-6u14-linux-x64.bin                    //根据提示按回车键即可
6. ......//省略部分内容
7. Do you agree to the above license terms? [yes or no]            //输入yes
8. yes
9. ......//省略部分内容
10. Press Enter to continue.....                                    //输入回车键即可
11. 
12. Done.
13. [root@localhost ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
  • 配置文件后面加入以下内容
1. [root@localhost ~]# vim /etc/profile
2. export  JAVA_HOME=/usr/local/jdk1.6
3. export  CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
4. export  PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
5. export  AMOEBA_HOME=/usr/local/amoeba
6. export  PATH=$PATH:$AMOEBA_HOME/bin
  • 执行并查看版本;java环境已经配置成功。
1. [root@localhost ~]# . /etc/profile
2. [root@localhost ~]# java -version
3. java version "1.6.0_14"
4. Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
5. Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

安装并配置Amoeba

1. [root@localhost ~]# mkdir /usr/local/amoeba
2. [root@localhost ~]# tar zxf /media/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
3. [root@localhost ~]# chmod -R 755 /usr/local/amoeba/
4. [root@localhost ~]# /usr/local/amoeba/bin/amoeba
5. amoeba start|stop                               //显示此内容说明Amoeba安装成功

配置Amoeba读写分离

  • Master、Slave1、Slave2中开放权限给Amoeba访问。注意：三台mysql服务器都要开放权限。
1. mysql> grant all on *.* to test@'192.168.1.%' identified by '123.com';
2. Query OK, 0 rows affected (0.00 sec)
  • 在Amoeba主机上编辑配置文件。
1. [root@localhost ~]# cd /usr/local/amoeba/
2. [root@localhost amoeba]# vim conf/amoeba.xml
3.           <property name="user">amoeba</property>                   //修改><内用户为amoeba
4.           <property name="password">123456</property>               //修改><内密码为123456

🍒同一文件内，以下配置文件中，🍒注意删除注释🍒。

1.                <property name="defaultPool">master</property>          //修改><内为master
2. 
3.                <!--                                                    //此为注释注意需要删除！！！
4.                <property name="writePool">master</property>            //修改><内为master
5.                <property name="readPool">slaves</property>             //修改><内为slaves
6.                 -->                                                     //此为注释注意需要删除！！！
  • 编辑第二个配置文件
1. [root@localhost amoeba]# vim conf/dbServers.xml 
2.                          <property name="user">test</property>              //修改><内用户为test
3. 
4.                         <!--  mysql password                                //此为注释注意需要删除！！！
5.                         <property name="password">123.com</property>        //修改><内密码为123.com
6.                         -->                                                 //此为注释注意需要删除！！！

🍳注意：同一文件内，修改master、name、和主机即可。

1.         <dbServer name="master"  parent="abstractServer">                                   //name修改为master
2.                 <factoryConfig>
3.                         <!-- mysql ip -->
4.                         <property name="ipAddress">192.168.1.101</property>                 //IP地址修改为192.168.1.101
5.                 </factoryConfig>
6.         </dbServer>
7. 
8.         <dbServer name="slave1"  parent="abstractServer">                                   //name修改为slave1（数字1）
9.                 <factoryConfig>
10.                         <!-- mysql ip -->
11.                         <property name="ipAddress">192.168.1.102</property>                 //IP地址修改为192.168.1.102
12.                 </factoryConfig>
13.         </dbServer>
14.        
15.         <dbServer name="slave2"  parent="abstractServer">                           //name修改为slave2（数字2），以下内容复制slave1的6行内容，修改name和IP地址即可
16.                 <factoryConfig>
17.                         <!-- mysql ip -->
18.                         <property name="ipAddress">192.168.1.103</property>                 //IP地址修改为192.168.1.103
19.                 </factoryConfig>
20.         </dbServer>
21. 
22.         <dbServer name="slaves" virtual="true">
23.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
24.                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
25.                         <property name="loadbalance">1</property>
26. 
27.                         <!-- Separated by commas,such as: server1,server2,server1 -->
28.                         <property name="poolNames">slave1,slave2</property>                 //修改><内为slave1,slave2
29.                 </poolConfig>
30.         </dbServer>
  • 启动Amoeba软件

🍲修改配置无误后，启动Amoeba，默认端口为tcp8066。

1. [root@localhost amoeba]# bin/amoeba start&                                          //关闭可以使用stop&命令
2. [root@localhost amoeba]# netstat -antpt | grep java
3. tcp6       0      0 127.0.0.1:18003         :::*                    LISTEN      5406/java           
4. tcp6       0      0 :::8066                 :::*                    LISTEN      5406/java                  
5. tcp6       0      0 192.168.1.110:35868     192.168.1.103:3306      ESTABLISHED 5314/java           
6. tcp6       0      0 192.168.1.110:47544     192.168.1.101:3306      ESTABLISHED 5314/java           
7. tcp6       0      0 192.168.1.110:58650     192.168.1.102:3306      ESTABLISHED 5314/java

验证读写分离

  • 测试主机上安装mysql
1. [root@localhost ~]# yum -y install mysql
2. ......//省略部分内容
3. [root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.1.110 -P8066        //通过代理访问MySQL
4. ......//省略部分内容
5. 
6. MySQL [(none)]>
  • 在Master（1.101）上创建一个表，同步到各个服务器上，然后关掉两台从服务器（1.102/1.103）的Slave功能，再插入区别语句测试。
1. [root@mysql ~]# mysql -uroot -ppwd123
2. ......//省略部分内容
3. mysql> use test_db
4. Database changed
5. mysql> create table AAA (id int(10),name varchar(10));
6. Query OK, 0 rows affected (0.02 sec)

（1）此刻两台从服务器的test_db库中应同步出一个名为AAA的表

1. mysql> show tables;
2. +-------------------+
3. | Tables_in_test_db |
4. +-------------------+
5. | AAA               |
6. +-------------------+
7. 1 row in set (0.00 sec)

（2）两台从服务器（1.102、1.103）关闭slave功能。注意：生产环境中一般不允许关闭！

1. mysql> stop slave;
2. Query OK, 0 rows affected (0.00 sec)

（3）主服务器（1.101）上插入一条数据。

1. mysql> insert into AAA values('101','zhang3');
2. Query OK, 1 row affected (0.00 sec)

（4）两台从服务器上分别插入一条数据。

🍭注意：在1.102主机的配置如下：

1. mysql> use test_db;
2. Database changed
3. mysql> insert into AAA values('102','li4');
4. Query OK, 1 row affected (0.00 sec)

🍭注意：在1.103主机的配置如下：

1. mysql> use test_db;
2. Database changed
3. mysql> insert into AAA values('103','wang5');
4. Query OK, 1 row affected (0.00 sec)

测试机——读操作

（1）在client主机上第一次的查询结果如下。

1. MySQL [(none)]> use test_db
2. Database changed
3. MySQL [test_db]> select * from AAA;
4. +------+-------+
5. | id   | name  |
6. +------+-------+
7. |  103 | wang5 |
8. +------+-------+
9. 1 row in set (0.01 sec)

(2）第二次查询结果如下。

1. MySQL [test_db]> select * from AAA;
2. +------+------+
3. | id   | name |
4. +------+------+
5. |  102 | li4  |
6. +------+------+
7. 1 row in set (0.00 sec)

（3）第三次查询结果如下。

1. MySQL [test_db]> select * from AAA;
2. +------+-------+
3. | id   | name  |
4. +------+-------+
5. |  103 | wang5 |
6. +------+-------+
7. 1 row in set (0.00 sec)

🥞经过三次操作只能查询到从服务器中的内容，由此看来读操作成功。

测试机——写操作

  • 在Client主机（1.111）上插入一条语句。
1. MySQL [test_db]> insert into AAA values('111','zhao6');
2. Query OK, 1 row affected (0.01 sec)

       但是在Client主机上查不到，最终只能在master（1.101）上才能查到这条语句内容，说明写操作在Master服务器上。

  • 在Master（1.101）主机上查看内容。
1. mysql> select * from AAA;
2. +------+--------+
3. | id   | name   |
4. +------+--------+
5. |  101 | zhang3 |
6. |  111 | zhao6  |
7. +------+--------+
8. 2 rows in set (0.00 sec)

       由此证明，已经实现了MySQL的读写分离，目前所有的写操作都全部在Master（1.101）上，用来避免数据的不同步；所有的读操作都分摊给Slave（从服务器），用来分担数据库压力。

  • 如果两台从服务器开启slave功能数据将被同步过去，测试机中操作能分别查到以下内容。
1. MySQL [test_db]> select * from AAA;
2. +------+--------+
3. | id   | name   |
4. +------+--------+
5. |  102 | li4    |
6. |  101 | zhang3 |
7. |  111 | zhao6  |
8. +------+--------+
9. 3 rows in set (0.00 sec)
10. 
11. MySQL [test_db]> select * from AAA;
12. +------+--------+
13. | id   | name   |
14. +------+--------+
15. |  103 | wang5  |
16. |  101 | zhang3 |
17. |  111 | zhao6  |
18. +------+--------+
19. 3 rows in set (0.00 sec)

        🍡首先感谢各位大佬的关注，谢谢各位的指点。各位没点关注的大佬，点点关注，我们相互学习，闭门造车是行不通滴。学海无涯苦作舟，那就这样了，晚宴结束时间已到，各位我们八月见啦。ヾ(￣▽￣)Bye~Bye~


文章标签：
云数据库 RDS MySQL 版
Java
前端开发
SQL
关系型数据库
负载均衡
网络安全
数据安全/隐私保护
MySQL
数据库
分布式数据库
关键词：
云数据库 RDS MySQL 版读写分离
云数据库 RDS MySQL 版案例
云数据库 RDS MySQL 版主从复制
案例云数据库 RDS MySQL 版
云数据库 RDS MySQL 版主从复制读写分离
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
大虾好吃吗
目录
相关文章
穆雄雄.
|
2月前
Mybatis+mysql动态分页查询数据案例——测试类HouseDaoMybatisImplTest)
Mybatis+mysql动态分页查询数据案例——测试类HouseDaoMybatisImplTest)
穆雄雄.
22 1
穆雄雄.
|
2月前
Mybatis+mysql动态分页查询数据案例——条件类（HouseCondition）
Mybatis+mysql动态分页查询数据案例——条件类（HouseCondition）
穆雄雄.
17 1
穆雄雄.
|
2月前
Mybatis+mysql动态分页查询数据案例——房屋信息的实现类(HouseDaoMybatisImpl)
Mybatis+mysql动态分页查询数据案例——房屋信息的实现类(HouseDaoMybatisImpl)
穆雄雄.
23 2
极客李华
|
23天前
|
负载均衡 容灾 关系型数据库
mysql主从复制
mysql主从复制
极客李华
37 1
穆雄雄.
|
2月前
Mybatis+mysql动态分页查询数据案例——工具类(MybatisUtil.java)
Mybatis+mysql动态分页查询数据案例——工具类(MybatisUtil.java)
穆雄雄.
16 1
俄罗斯刺沙蓬
|
1天前
|
设计模式 容灾 关系型数据库
MySQL 主从复制架构
MySQL 主从复制架构
俄罗斯刺沙蓬
6 0
李振良_阿良
|
8天前
|
负载均衡 关系型数据库 MySQL
MySQL-Proxy实现MySQL读写分离提高并发负载
MySQL-Proxy实现MySQL读写分离提高并发负载
李振良_阿良
10 1
爱笑的源码基地
|
13天前
|
Java 关系型数据库 MySQL
一套java+ spring boot与vue+ mysql技术开发的UWB高精度工厂人员定位全套系统源码有应用案例
UWB (ULTRA WIDE BAND, UWB) 技术是一种无线载波通讯技术，它不采用正弦载波，而是利用纳秒级的非正弦波窄脉冲传输数据，因此其所占的频谱范围很宽。一套UWB精确定位系统，最高定位精度可达10cm，具有高精度，高动态，高容量，低功耗的应用。
爱笑的源码基地
33 0
一套java+ spring boot与vue+ mysql技术开发的UWB高精度工厂人员定位全套系统源码有应用案例
程序三两行
|
22天前
|
SQL 关系型数据库 MySQL
mysql主从复制
mysql主从复制
程序三两行
26 0
穆雄雄.
|
2月前
|
Java 数据库连接 mybatis
Mybatis+mysql动态分页查询数据案例——Mybatis的配置文件(mybatis-config.xml)
Mybatis+mysql动态分页查询数据案例——Mybatis的配置文件(mybatis-config.xml)
穆雄雄.
20 1

热门文章

最新文章

  • 1
    MySQL全局库表查询准确定位字段
  • 2
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 3
    基于 java + Springboot + vue +mysql 大学生实习管理系统（含源码）
  • 4
    mysql性能优化-慢查询分析、优化索引和配置
  • 5
    CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解
  • 6
    MySQL 8教程：详解如何更改和重置用户密码
  • 7
    centos7安装mysql-带网盘安装包
  • 8
    MySQL学习手册（第一部分）
  • 9
    高性能 MySQL 第四版（GPT 重译）（三）(1)
  • 10
    mysql用户权限设置
  • 1
    连接和管理RDS
    30
  • 2
    购买和初始化阿里云RDS
    28
  • 3
    rds教程
    27
  • 4
    TiDB与MySQL、PostgreSQL等数据库的比较分析
    125
  • 5
    OBCP实践 - 迁移 MySQL 数据到 OceanBase 集群
    42
  • 6
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    41
  • 7
    TIDB和MySQL的区别
    183
  • 8
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    66
  • 9
    [MySQL]事务原理之redo log,undo log
    46
  • 10
    [MySQL]SQL优化之索引的使用规则
    36

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考