MySQL主从复制与读写分离原理及用法详解

本文实例讲述了MySQL主从复制与读写分离原理及用法。分享给大家供大家参考，具体如下： 主从复制 概念 影响MySQL-A数据库的操作，在数据库执行后，都会写入本地的日志系统A中。 假

本文实例讲述了MySQL主从复制与读写分离原理及用法。分享给大家供大家参考，具体如下：

主从复制

概念

影响MySQL-A数据库的操作，在数据库执行后，都会写入本地的日志系统A中。 假设，实时的将变化了的日志系统中的数据库事件操作，在MYSQL-A的3306端口，通过网络发给MYSQL-B。 MYSQL-B收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库中完成。 那么，MYSQL-A的变化，MYSQL-B也会变化，这样就是所谓的MYSQL的复制，即MYSQL replication。 在上面的模型中，MYSQL-A就是主服务器，即master，MYSQL-B就是从服务器，即slave。 日志系统A，其实它是MYSQL的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，即bin log。【注意MYSQL会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全】 日志系统B，并不是二进制日志，由于它是从MYSQL-A的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为中继日志，即relay log。 可以发现，通过上面的机制，可以保证MYSQL-A和MYSQL-B的数据库数据一致，但是时间上肯定有延迟，即MYSQL-B的数据是滞后的。 【即便不考虑什么网络的因素，MYSQL-A的数据库操作是可以并发的执行的，但是MYSQL-B只能从relay log中读一条，执行下。因此MYSQL-A的写操作很频繁，MYSQL-B很可能跟不上。】

解决问题

数据如何不被丢失 备份 读写分离 数据库负载均衡 高可用

环境搭建

1.准备环境 两台windows操作系统 ip分别为: 172.27.185.1(主)、172.27.185.2(从) 2.连接到主服务(172.27.185.1)服务器上，给从节点分配账号权限。 GRANT REPLICATION SLAVE ON . TO ‘root'@‘172.27.185.2' IDENTIFIED BY ‘root'; 3.在主服务my.ini文件新增

server-id=200 log-bin=mysql-bin relay-log=relay-bin relay-log-index=relay-bin-index

重启mysql服务 4.在从服务my.ini文件新增

server-id = 210 replicate-do-db =itmayiedu #需要同步数据库

重启mysql服务 5.从服务同步主数据库

stop slave; change master to master_host='172.27.185.1',master_user='root',master_password='root'; start slave; show slave status;

注意事项 ①一定要在同一个局域网中 ②使用360WiFi创建局域网 ③最好把防火墙全部关闭掉

什么是读写分离

在数据库集群架构中，让主库负责处理事务性查询，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。当然，主数据库另外一个功能就是负责将事务性查询导致的数据变更同步到从库中，也就是写操作。

读写分离的好处

1）分摊服务器压力，提高机器的系统处理效率 读写分离适用于读远比写的场景，如果有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能并不高，而主从只负责各自的写和读，极大程度的缓解X锁和S锁争用； 假如我们有1主3从，不考虑上述1中提到的从库单方面设置，假设现在1分钟内有10条写入，150条读取。那么，1主3从相当于共计40条写入，而读取总数没变，因此平均下来每台服务器承担了10条写入和50条读取（主库不承担读取操作）。因此，虽然写入没变，但是读取大大分摊了，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了，说白了就是拿机器和带宽换性能； 2）增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务

主从复制原理

依赖于二进制日志，binary-log. 二进制日志中记录引起数据库发生改变的语句 Insert 、delete、update、create table

Scale-up与Scale-out区别

Scale Out是指Application可以在水平方向上扩展。一般对数据中心的应用而言，Scale out指的是当添加更多的机器时，应用仍然可以很好的利用这些机器的资源来提升自己的效率从而达到很好的扩展性。 Scale Up是指Application可以在垂直方向上扩展。一般对单台机器而言，Scale Up值得是当某个计算节点（机器）添加更多的CPU Cores，存储设备，使用更大的内存时，应用可以很充分的利用这些资源来提升自己的效率从而达到很好的扩展性。

MyCat

什么是 Mycat

是一个开源的分布式数据库系统，但是因为数据库一般都有自己的数据库引擎，而Mycat并没有属于自己的独有数据库引擎，所有严格意义上说并不能算是一个完整的数据库系统，只能说是一个在应用和数据库之间起桥梁作用的中间件。 在Mycat中间件出现之前，MySQL主从复制集群，如果要实现读写分离，一般是在程序段实现，这样就带来了一个问题，即数据段和程序的耦合度太高，如果数据库的地址发生了改变，那么我的程序也要进行相应的修改，如果数据库不小心挂掉了，则同时也意味着程序的不可用，而对于很多应用来说，并不能接受；

引入Mycat中间件能很好地对程序和数据库进行解耦，这样，程序只需关注数据库中间件的地址，而无需知晓底层数据库是如何提供服务的，大量的通用数据聚合、事务、数据源切换等工作都由中间件来处理； Mycat中间件的原理是对数据进行分片处理，从原有的一个库，被切分为多个分片数据库，所有的分片数据库集群构成完成的数据库存储，有点类似磁盘阵列中的RAID0.

Mycat安装

创建表结构

CREATE DATABASE IF NOT EXISTS weibo_simple;

-- Table structure for t_users 用户表

DROP TABLE IF EXISTS t_users; CREATE TABLE t_users ( user_id varchar(64) NOT NULL COMMENT '注册用户ID', user_email varchar(64) NOT NULL COMMENT '注册用户邮箱', user_password varchar(64) NOT NULL COMMENT '注册用户密码', user_nikename varchar(64) NOT NULL COMMENT '注册用户昵称', user_creatime datetime NOT NULL COMMENT '注册时间', user_status tinyint(1) NOT NULL COMMENT '验证状态 1：已验证 0：未验证', user_deleteflag tinyint(1) NOT NULL COMMENT '删除标记 1：已删除 0：未删除', PRIMARY KEY (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Table structure for t_message微博表

DROP TABLE IF EXISTS t_message; CREATE TABLE t_message ( messages_id varchar(64) NOT NULL COMMENT '微博ID', user_id varchar(64) NOT NULL COMMENT '发表用户', messages_info varchar(255) DEFAULT NULL COMMENT '微博内容', messages_time datetime DEFAULT NULL COMMENT '发布时间', messages_commentnum int(12) DEFAULT NULL COMMENT '评论次数', message_deleteflag tinyint(1) NOT NULL COMMENT '删除标记 1：已删除 0：未删除', message_viewnum int(12) DEFAULT NULL COMMENT '被浏览量', PRIMARY KEY (messages_id), KEY user_id (user_id), CONSTRAINT t_message_ibfk_1 FOREIGN KEY (user_id) REFERENCES t_users (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置server.xml

<!-- 添加user -->

mycat mycat

<!-- 添加user -->

mycat_red mycat true

配置schema.xml

http://org.opencloudb/">;

<table name="t_message" type="global" primaryKey="messages_id" dataNode="dn1" />

select user()

配置rule.xml文件

http://org.opencloudb/">; user_id func1 autopartition-long.txt

为了更好地定位错误，修改log4j.xml 双击startup_nowrap.bat开始启动

常见问题 SHOW MASTER STATUS 如果为，则在my.ini文件中添加一行log-bin=mysql-bin

给账号分配权限grant all privileges on . to ‘root'@‘172.27.185.1' identified by ‘root';

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

相关推荐

MySQL Hash Join前世今生

GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。 GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。 作者：nw MySQL Hash Join前世今生 因工作需要，对MySQL Hash Join的内部实现做了一些探索和实践，对这个由8.0.18开始引入的连接算法有了一定的了解，写文总结与各位大佬分享，欢迎大家指教。因篇幅较长，这份总结分成若干部分，

数据运维 2023-08-15 捡田螺的小男孩

MySQL一致性实现方法

MySQL一致性实现方法 1 事务 事务是MySQL中实现一致性的重要机制。事务是一组操作，它们被视为单个操作单元。如果所有操作都成功，事务就被提交，否则就被回滚。在MySQL中，事务通常用于保护数据库中的数据免受并发访问的影响。 2 锁 锁是MySQL中实现一致性的另一种机制。锁在MySQL中用于控制对数据库中数据的访问。当一个用户正在访问某个数据时，锁将阻止其他用户对该数据的访问。这确保了数据

数据运维 2023-11-23 捡田螺的小男孩

详解MySQL中的死锁情况以及对死锁的处理方法

当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁。死锁发生在事务试图以不同的顺序锁定资源。以StockPrice表上的两个事务为例： 事务1 START TRANSACTION;UP 当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁。死锁发生在事务试图以不同的顺序锁定资源。以StockPrice表上的两个事务为例： 事务1 START TRANSACTI

数据运维 2023-04-25 捡田螺的小男孩

mysql和DM区别

反引号与双引号 kingbase不支持mysql中的 反引号 与其相对应的是 双引号 mysql中的语法：namekingbase中的语法：“name” 正则表达式 kingbase 正则表达式 与mysql中 的语法不通 ： mysql 中的语法：name regexp ‘^b.’ kingbase中的语法：regexp_like(“name”,’^b.’,‘i’) 字符串包裹 king

数据运维 2024-04-26 捡田螺的小男孩

cmd 访问oracle

在日常开发过程中，我们经常需要使用Oracle数据库来进行数据的存储和查询。而在使用Oracle的时候，我们可以通过cmd命令行来进行数据库的访问和操作。在本文中，我们将详细讲解如何在cmd中访问Oracle数据库，以及常用的操作方法和技巧。 首先，我们需要在cmd中安装Oracle客户端，并且配置好环境变量。在安装完成之后，我们可以通过以下的命令来连接到Oracle数据库： sqlplus us

数据运维 2023-08-06 捡田螺的小男孩