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

本文实例讲述了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 -->
  <user name="mycat">
  <property name="password">mycat</property>
  <property name="schemas">mycat</property>
  </user>
	
	<!-- 添加user -->
  <user name="mycat_red">
  <property name="password">mycat_red</property>
  <property name="schemas">mycat</property>
	<property name="readonly">true</property>
  </user>

配置schema.xml

<?xml version="1.0"?>
<!doctype mycat:schema system "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
  <!-- 与server.xml中user的schemas名一致 -->
  <schema name="mycat" checksqlschema="true" sqlmaxlimit="100">
    <table name="t_users" primarykey="user_id" datanode="dn1" rule="rule1"/>
   
    <table name="t_message" type="global" primarykey="messages_id" datanode="dn1" />
  </schema>
<datanode name="dn1" datahost="jdbchost" database="weibo_simple


" />
  
  <datahost name="jdbchost" maxcon="1000" mincon="10" balance="1"
        writetype="0" dbtype="mysql" dbdriver="native" switchtype="1"
        slavethreshold="100">
     <heartbeat>select user()</heartbeat> 
    <writehost host="hostmaster" url="172.27.185.1:3306" user="root" password="root">
    </writehost>
    <writehost host="hostslave" url="172.27.185.2:3306" user="root" password="root"/>
  </datahost>
  
</mycat:schema>

配置rule.xml文件

<?xml version="1.0" encoding="utf-8"?>
<!-- - - licensed under the apache license, version 2.0 (the "license"); 
	- you may not use this file except in compliance with the license. - you 
	may obtain a copy of the license at - - http://www.apache.org/licenses/license-2.0 
	- - unless required by applicable law or agreed to in writing, software - 
	distributed under the license is distributed on an "as is" basis, - without 
	warranties or conditions of any kind, either express or implied. - see the 
	license for the specific language governing permissions and - limitations 
	under the license. -->
<!doctype mycat:rule system "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
	 <tablerule name="rule1">
    <rule>
      <columns>user_id</columns>
      <algorithm>func1</algorithm>
    </rule>
  </tablerule>
  <function name="func1" class="org.opencloudb.route.function.autopartitionbylong">
 	<property name="mapfile">autopartition-long.txt</property>
  </function>
</mycat:rule>

为了更好地定位错误，修改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';