MySQL主从复制与读写分离

最新推荐文章于 2024-03-28 19:43:24 发布
最新推荐文章于 2024-03-28 19:43:24 发布
阅读量150 收藏
点赞数 5
分类专栏： 数据库管理与高可用 文章标签： 1024程序员节 mysql 数据库 linux
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48190875/article/details/109261078
版权
本文介绍了在企业环境中，通过MySQL主从复制和读写分离来提升数据库的安全性、可用性和并发性能。详细步骤包括：主从复制的配置，如在主服务器上设置日志，在从服务器上同步；读写分离的实现，如使用Amoeba作为中间代理层。同时，文中还提及了可能遇到的配置错误及解决方案，例如Amoeba启动失败的XML配置问题。
摘要由CSDN通过智能技术生成

MySQL主从复制与读写分离

案例概述

  • 在企业应用中，成熟的业务通常数据量都比较大
  • 单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求
  • 配置多台主从数据库服务器以实现读写分离

案例前置知识点

MySQL主从复制原理

  • MySQL的复制类型
    • 基于语句的复制
    • 基于行的复制
    • 混合类型的复制
  • MySQL主从复制的工作过程

MySQL读写分离原理

  • 只在主服务器上写，只在从服务器上读
  • 主数据库处理事务性查询，从数据库处理SELECT查询
  • 数据库复制用于将事务性查询的变更同步到集群中的从数据库
  • 读写分离方案
    • 基于程序代码内部实现
    • 基于中间代理层实现
      • MySQL-Proxy
      • Amoeba

案例环境

在这里插入图片描述

案例实施（主从复制）

主服务器（master）

关闭防火墙和核心防护

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0

修改用户名

[root@localhost ~]# hostnamectl set-hostname master

同步阿里云服务器

[root@master ~]# ntpdate ntp1.aliyun.com
[root@master ~]#  date -R

安装MySQL数据库（5.7）

mysql主服务器配置

[root@master ~]# vi /etc/my.cnf
server-id = 11
log-bin = master-bin   ##主服务器日志文件
log-slave-updates   ##从服务器更新二进制日志

[root@master ~]# systemctl restart mysqld

[root@master ~]# ls /usr/local/mysql/data/   #检查是否产生日志文件


[root@master ~]# mysql -uroot -p

mysql> grant replication slave on *.* To 'myslave'@'20.0.0.%' identified by '123456';
mysql> select host,user from mysql.user;  #创建成功20.0.0.%
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| 20.0.0.%  | myslave       |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
mysql> flush privileges;

mysql> show master status;    #主从复制是依赖日志和定位599
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      599 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

从服务器（slave1）

关闭防火墙和核心防护

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0

修改用户名

[root@localhost ~]# hostnamectl set-hostname slave1

同步阿里云服务器

[root@slave1 ~]# ntpdate ntp1.aliyun.com
[root@slave1 ~]#  date -R

安装MySQL数据库（5.7）

[root@slave1 ~]# vi /etc/my.cnf
server-id = 22
relay-log = relay-log-bin    #从主服务器上同步日志文件记录到本地
relay-log-index = slave-relay-bin.index   #定义relay-log的位置和名称

[root@slave1 ~]# systemctl restart mysqld

[root@slave1 ~]# mysql -uroot -p

mysql> change master to master_host='20.0.0.26',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=599;

mysql> start slave;   #开启
mysql> show slave status\G   #查看主从同步的状态 “\G”:将查询到的横向表格纵向输出，方便阅读
...
             Slave_IO_Running: Yes   		#IO和SQL是否是yes模式
            Slave_SQL_Running: Yes
...

从服务器（slave2）

关闭防火墙和核心防护

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0

修改用户名

[root@localhost ~]# hostnamectl set-hostname slave2

同步阿里云服务器

[root@slave2 ~]# ntpdate ntp1.aliyun.com
[root@slave2 ~]#  date -R

安装MySQL数据库（5.7）

[root@slave2 ~]# vi /etc/my.cnf
server-id = 33
relay-log = relay-log-bin    #从主服务器上同步日志文件记录到本地
relay-log-index = slave-relay-bin.index   #定义relay-log的位置和名称

[root@slave2 ~]# systemctl restart mysqld

[root@slave2 ~]# mysql -uroot -p
mysql> change master to master_host='20.0.0.26',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=599;

mysql> start slave;   #开启
mysql> show slave status\G   #查看主从同步的状态 “\G”:将查询到的横向表格纵向输出，方便阅读
...
             Slave_IO_Running: Yes   #IO和SQL是否是yes模式
            Slave_SQL_Running: Yes
...

验证主从复制效果

登录20.0.0.26（master）

[root@localhost ~]# mysql -uroot -p
mysql> create database school;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

登录20.0.0.25（slave1）

[root@localhost ~]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

登录20.0.0.29（slave2）

[root@localhost ~]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

上述说明新建的school数据库同步成功

案例实施（读写分离）

服务器（amoeba）

关闭防火墙和核心防护

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0

修改用户名

[root@localhost ~]# hostnamectl set-hostname amoeba
  1. 导入jdk-6u14-linux-x64.bin文件
  2. 授予执行权限：chmod +x jdk-6u14-linux-x64.bin
  3. 执行二进制文件：./jdk-6u14-linux-x64.bin
    （一路按回车就行、yes、回车）
[root@amoeba ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6   #移到/usr/local/jdk1.6目录下

[root@amoeba ~]# vi /etc/profile

export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

[root@amoeba ~]# source /etc/profile    #执行
[root@amoeba ~]# mkdir /usr/local/amoeba

[root@amoeba ~]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/

[root@amoeba ~]# chmod -R 755 /usr/local/amoeba  #授予权限

检验amoeba是否能执行

[root@amoeba ~]# ls /usr/local/amoeba/bin/
amoeba      amoeba.classworlds  benchmark.bat
amoeba.bat  benchmark           benchmark.classworlds
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba
显示amoeba start|stop说明安装成功

在三台MySQL上添加权限开发给amoeba访问

mysql> grant all on *.* to test@'20.0.0.%' identified by '123.com'; 			#20.0.0网段授予权
mysql> flush privileges;			#刷新

回到amoeba服务器

[root@amoeba ~]# cd /usr/local/amoeba/conf/
[root@amoeba conf]# vi amoeba.xml

----30行----（账号）
<property name="user">amoeba</property>

----32行----（密码）
<property name="password">123456</property>

----115行----（只是定义一个名称）
<property name="defaultPool">master</property>

----117行去掉注释----（只是定义一个名称）
<property name="writePool">master</property>（写）
<property name="readPool">slaves</property>（读取）
<property name="needParse">true</property>

[root@amoeba conf]# vi dbServers.xml
----23行----(注意！！mysql5.7，默认没有test数据库所以需要修改为mysql数据库) - （mysql5.5、5.6直接忽略，因为有test数据库）
<property name="schema">mysql</property>

----26-29行----
<property name="user">test</property>
<!--  mysql password -->
<property name="password">123.com</property>

----45-50行主服务器----
<dbServer name="master"  parent="abstractServer">  #主服务器主机名
	 <factoryConfig>
		 <!-- mysql ip -->
		 <property name="ipAddress"20.0.0.26></property>  #主服务器地址
 	</factoryConfig>
</dbServer>

----52-57行从服务器(1)----
<dbServer name="slave1"  parent="abstractServer">
        <factoryConfig>
                <!-- mysql ip -->
                <property name="ipAddress">20.0.0.25</property>
         </factoryConfig>
</dbServer>

----69-64行从服务器(2)----(源文件没有,复制服务器1的)
<dbServer name="slave2"  parent="abstractServer">
        <factoryConfig>
                <!-- mysql ip -->
                <property name="ipAddress">20.0.0.29</property>
         </factoryConfig>
</dbServer>

----65-73行--与amoeba.xml相匹配
<dbServer name="slaves" virtual="true">
        <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
                <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
                 <property name="loadbalance">1</property>
                 <!-- Separated by commas,such as: server1,server2,server1 -->
                 <property name="poolNames">slave1,slave2</property>
         </poolConfig>
</dbServer>

[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start&   开启amoeba
jobs  查看SQL 中JOB 的运行状态
[12]   Running                 /usr/local/amoeba/bin/amoeba start &   成功

测试客户端

[root@amoeba conf]# yum install -y mariadb*
[root@amoeba conf]# systemctl start mariadb.service 
[root@amoeba conf]# mysql_secure_installation    #自定义部署

Set root password? [Y/n]y    设置密码
Remove anonymous users? [Y/n]n   删除匿名用户 ... skipping跳过
Disallow root login remotely? [Y/n]n   禁用远程登录
Remove test database and access to it? [Y/n]n  删除测试数据库
Reload privilege tables now? [Y/n] y   重新加载privilege数据库

[root@amoeba conf]# mysql -uamoeba -p -h 127.0.0.1 -P8066   123456
MySQL [school]> show tables;   #查看之前的表
MySQL [school]> create table test (id int(4) not null primary key,name varchar(10));  #创建一个新的表
MySQL [school]> insert into test values ('1','T1');
MySQL [school]> select * from test;   #查看不了数据，因为从服务器关闭了
Empty set (0.04 sec)

master (20.0.0.26)

[root@master ~]# mysql -uroot -p
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| test             |
+------------------+

mysql> select * from test;   #主服务器可以看到
+----+------+
| id | name |
+----+------+
|  1 | T1   |
+----+------+

MySQL [school]> select * from test;  #读的slave2服务器
+----+------+
| id | name |
+----+------+
|  3 | T3   |
+----+------+
MySQL [school]> select * from test;  #读的slave1服务器
+----+------+
| id | name |
+----+------+
|  2 | T2   |
+----+------+
在slave1、slave2服务器中轮询读取数据。

slave1(20.0.0.25)

[root@slave1 ~]# mysql -uroot -p
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| test             |
+------------------+

mysql> stop slave;

mysql> select * from test;
Empty set (0.00 sec)

mysql> insert into test values ('2','T2');
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  2 | T2   |
+----+------+

slave2(20.0.0.29)

[root@slave2 ~]# mysql -uroot -p
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| test             |
+------------------+

mysql> stop slave;
mysql> insert into test values ('3','T3');
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  3 | T3   |
+----+------+

可能遇到的问题：

/usr/local/amoeba/bin/amoeba start&开启失败

org.xml.sax.SAXParseException: Element type "property" must be followed by either attribute specifications, ">" or "/>".

解决：
property附加语句不规范，缺少">" or “/>”，需要自己在修改过的配置文件(dbServers.xml和amoeba.xml)中找咯!!

Mysql主从复制与读写分离
weixin_48861962的博客
11-01 978
一、MySQL主从复制与读写分离 1.1 主从分离和读写分离的相关概述 1）主从复制与读写分离、 在实质际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实院需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。 2）为什么要读写分离 因为数据库的"写"（
mysql主从复制与读写分离
weixin_73188318的博客
07-01 841
MHA（全称Master High Availability或MySQL High Availability）是一个用于提高MySQL数据库高可用性的解决方案。它通过在主数据库和备份数据库之间进行故障切换，确保在主数据库出现故障时，系统能够自动切换到备份数据库，从而保证系统的持续运行。
MySQL 主从复制与读写分离
octoben的博客
06-16 1630
1.1 什么是读写分离? 读写分离，基本的原理是让主数据库处理事务性增、改、删操作( INSERT、UPDATE、DELETE) ，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库,,,...
MySQL主从复制和读写分离
2301_81272194的博客
03-28 2356
关于MySQL主从复制和读写分离相关内容
深入浅出MySQL主从复制与读写分离原理及其实践
hy199707的博客
03-28 996
在大型互联网应用中，数据库作为核心组件，其稳定性和性能直接影响着整个系统的健壮性。MySQL作为广泛应用的关系型数据库管理系统，提供了强大的主从复制与读写分离功能，帮助企业搭建高性能、高可用的数据库架构。本文将详细介绍MySQL主从复制与读写分离的原理，并探讨其在实际应用中的实践。
MySQL主从复制与读写分离配置详解
最新发布
10-31
内容概要：本文详细介绍了如何在两台MySQL服务器上配置主从复制和读写分离，以实现数据同步更新和降低数据库压力。具体步骤包括安装MySQL、配置主服务器和从服务器、导入数据库以及配置MySQL配置文件。通过具体的...
MySQL主从复制与读写分离.docx
04-13
MySQL 主从复制与读写分离 MySQL 主从复制是指将一个 MySQL 服务器的数据实时同步到另一个 MySQL 服务器中，以实现数据的高可用性和读写分离。下面是 MySQL 主从复制与读写分离的详细知识点： MySQL 主从复制 ...
MySQL存储过程详细演示示例（in、out、inout）
weixin_48190875的博客
10-21 1758
存储过程简述存储过程存储过程的优缺点优点缺点存储过程的创建和调用MySQL 存储过程中的关键语法案例存储过程的参数1、 in 输入参数2、out输出参数3、inout输入参数报错 简述存储过程 存储过程就是数据库中的函数，增强数据库的安全性 MySQL 5.0 版本开始支持存储过程。 存储过程的优缺点 优点 存储过程执行一次后，生成的二进制代码就驻留在缓冲区 存储过程是SQL语句加上控制语句的集合 存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调用语句，从而可以降低网络负载 存
MySQL进阶查询（null值、正则表达式和各种运算符）案例兼注解
weixin_48190875的博客
10-21 716
MySQL进阶查询NULL值正则表达式各种运算符算术运算符比较运算符逻辑运算符位运算符 NULL值 表示缺失的值 与数字0或者空白（spaces）是不同的 使用IS NULL或IS NOT NULL进行判断 NULL值和空值的区别 空值长度为0，不占空间；null值的长度为null，占用空间 IS NULL无法判断空值 空值使用“=”或者“<>”来处理 count（）计算时，null会忽略，空值会加入计算 验证实验 mysql> alter table info add colu
MySQL数据库的备份与恢复
weixin_48190875的博客
10-19 621
MySQL备份与恢复数据备份的重要性数据库备份的分类从物理与逻辑的角度，备份可分为物理备份物理备份方法逻辑备份从数据库的备份策略角度，备份可分为完全备份 数据备份的重要性 在生产环境中，数据的安全性至关重要 任何数据的丢失都可能产生严重的后果 造成数据丢失的原因 程序错误 人为操作错误 运算错误 磁盘故障 灾难（如火灾、地震）和盗窃 数据库备份的分类 从物理与逻辑的角度，备份可分为 物理备份 对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。 物理备份方法 冷备份（脱机备份）：是在关闭
MySQL数据库索引、事务和存储引擎（三）
weixin_48190875的博客
10-19 536
MySQL存储引擎存储引擎概念MyISAM的介绍MyISAM适用的生产场景举例：InnoDB特点InnoDB适用生产场景分析MyISAM与InnoDB的区别：企业选择存储引擎依据修改存储引擎 存储引擎概念 MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。 存储引擎时MySQL将数据存储在文件系统中的存储方式或者存储格式 MySQL常用的存储引擎 MyISAM（5.5之前使
MySQL数据库索引、事务和存储引擎（二）
weixin_48190875的博客
10-19 461
MySQL索引事务的概念事务的ACID特点原子性 （Atomicity）一致性 （Consistency）隔离性 （Consistency）持久性 （Durability）事务控制语句事务的控制方法案例 事务的概念 是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么执行，要么都不执行。 是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。 适用于多用户同时操作的数据库系统的场景，如银行、保险公司
MySQL进阶查询（内连接、左连接和右连接）案例兼注解
weixin_48190875的博客
10-21 329
连接分类内连接左连接右连接 内连接 两张或多张表中同时符合某种条件的数据记录组合 from子句中使用inner join关键字连接多张表，并使用on设置连接条件 是系统默认的表连接方式，可以省略inner关键字 多表支持连续使用inner join，建议不超过三个表 语法结构 select 字段1，字段2… from 表1 inner join 表2 on 表1.字段1=表2.字段1； 表1：info mysql> select * from info; +----+---------+----
数据库的基本概念
weixin_48190875的博客
10-19 316
数据库的基本概念数据库管理系统（DBMS）数据库的发展主流数据库：非关系数据库介绍：MySQL数据库介绍：MySQL产品阵营 数据库管理系统（DBMS） 是实现对数据库资源有效组织、管理和存取的系统软件 用户可以通过DBMS或应用程序操作数据库 数据请求 低层指令 ————> ————> 应用程序 DBMS DB <———— <———— 数据 数据
事务提交案例：银行转账
weixin_48190875的博客
10-19 255
银行转账条件示例 条件 条件约束：转账金额<=余额 姓名 余额 操作后剩余 张三 $1000.00 $800.00 李四 $0.00 $200.00 关键语句： update info set money=money-200 where name=‘张三’ update info set money=money+200 where name=‘李四’ 示例 mysql> create database bank; ##创建一个银行数据库 mysql> use b
MySQL进阶查询（排序、分组、限制条目、别名、通配符和子查询）案例兼注解
weixin_48190875的博客
10-21 248
MySQL进阶查询排序按关键字排序按单个字段排序按多个字段排序分组对结果进行分组GROUP BY分组限制条目别名设置别名用法（一）设置别名用法（二）通配符子查询 排序 按关键字排序 使用 order by 语句来实现排序 排序可针对一个或多个字段 ASC：升序，默认排序方法 DESC：降序 ORDER BY 的语法结构 语法结构： select 字段1，字段2… from 表名 order by 字段1，字段2… ASC|DESC; 按单个字段排序 升序语法： select * from 表名 orde
MySQL主从复制与读写分离实战配置
MySQL主从复制与读写分离是一种常见的数据库集群架构，用于提高数据库性能和可用性。在本文中，作者分享了在Linux环境下实现这一功能的具体步骤。首先，环境准备阶段非常重要，包括设置MySQL主服务器（IP: 192.168....
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值