动漫做的游戏 迅雷下载网站,discuz 科技网站模板,wordpress 时间,wp网站搬家教程文章目录 一、mysql日志错误日志查询日志二进制日志慢查询日志redo log和undo log 二、mysql集群主从复制原理介绍配置命令 读写分离原理介绍配置命令 三、mysql分库分表垂直拆分水平拆分 一、mysql日志 MySQL日志 是记录 MySQL 数据库系统运行过程中不同事件和操作的信息的文件… 文章目录 一、mysql日志错误日志查询日志二进制日志慢查询日志redo log和undo log 二、mysql集群主从复制原理介绍配置命令 读写分离原理介绍配置命令 三、mysql分库分表垂直拆分水平拆分 一、mysql日志 MySQL日志 是记录 MySQL 数据库系统运行过程中不同事件和操作的信息的文件。这些日志对于故障排除、性能调优、备份恢复以及复制等方面都非常重要。
查看mysql中与日志相关的系统变量的配置信息
show variables like log_%;错误日志 错误日志 错误日志是 MySQL 中最重要的日志之一它记录了当 mysqld 启动和停止时以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时可以首先查看此日志。 mysqld 使用错误日志名 host_name.err(host_name 为主机名) 并默认在参数 DATADIR(数据目录)指定的目录中写入日志文件。 查询日志 查询日志 查询日志记录了客户端的所有语句。由于上线项目sql特别多开启查询日志IO太多导致MySQL效率低只有在调试时才开启比如通过查看sql发现热点数据进行缓存。
mysql show global variables like %genera%;二进制日志 二进制日志 二进制日志(BINLOG) 记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言) 语句但是不包括数据查询语句。语句以“事件”的形式保存它描述了数据的更改过程。 此日志对于灾难时的数据恢复起着极其重要的作用。 二进制日志的两个重要的应用场景主从复制、数据恢复。
查看二进制日志
show binary logs;慢查询日志 慢查询日志 MySQL可以设置慢查询日志当SQL执行的时间超过我们设定的时间那么这些SQL就会被记录在慢查询日志当中然后我们通过查看日志用explain分析这些SQL的执行计划来判定为什么效率低下是没有使用到索引还是索引本身创建的有问题或者是索引使用到了但是由于表的数据量太大花费的时间就是很长那么此时我们可以把表分成n个小表比如订单表按年份分成多个小表等。 对于日志的配置我们可以直接在 /etc/my.cnf 下配置即可。 redo log和undo log redo log和undo log redo log
redo log重做日志用于记录事务操作的变化确保事务的持久性。
redo log 是在事务开始后就开始记录不管事务是否提交都会记录下来在异常发生时如数据持久化过程中掉电InnoDB会使用redo log恢复到掉电前的时刻, 保证数据的完整性。
innodb_log_buffer_size 默认是16M就是redo log缓冲区的大小它随着事务开始就开始写redolog如果事务比较大为了避免事务执行过程中花费过多磁盘IO可以设置比较大的redo log缓存节省磁盘IO。
InnoDB修改操作数据不是直接修改磁盘上的数据实际只是修改Buffer Pool中的数据。InnoDB总是先把Buffer Pool中的数据改变记录到redo log中用来进行崩溃后的数据恢复。 优先记录redo log然后再慢慢的将Buffer Pool中的脏数据刷新到磁盘上。
innodb_log_group_home_dir指定的目录下的两个文件ib_logfile0和ib_logfile1该文件被称作重做日志。
buffer pool 缓存池的作用加速读和加速写直接操作data page写redo log修改就算完成有专门的线程去做把bufferpool中的dirty page写入磁盘。 undo log 回滚日志保存了事务发生之前的数据的一个版本用于事务执行时的回滚操作同时也是实现多版本并发控制MVCC下读操作的关键技术。详情见【MySQL】事务管理 二、mysql集群
在实际生产环境中如果对mysql数据库的读和写都在一台数据库服务器中操作无论是在安全性、高可用性还是高并发等各个方面都是不能满足实际需求的一般要通过 主从复制 的方式来同步数据再通过 读写分离 来提升数据库的并发负载能力。
数据备份 - 热备份容灾高可用读写分离支持更大的并发
主从复制
原理介绍
主库提供对外的增删改查服务写入二进制日志(Binary log)从库通过特定的线程将Binary log 日志中的内容同步到从库中。 主从复制的流程 两个日志binlog二进制日志relay log日志和三个线程master[主库]的一个线程和slave[从库]的二个线程
主库的更新操作写入binlog二进制日志中。master服务器创建一个binlog转储线程将二进制日志内容发送到从服务器。slave机器执行START SLAVE命令会在从服务器创建一个IO线程接收master的binary log复制到其中继日志 Relay log。 首先slave开始一个工作线程I/O线程I/O线程在master上打开一个普通的连接然后开始binlog dump processbinlog dump process从master的二进制日志中读取事件如果已经跟上master它会睡眠并等待master产生新的事件I/O线程将这些事件写入中继日志。sql slave threadsql从线程处理该过程的最后一步sql线程从中继日志中读取事件并重放其中的事件而更新slave机器的数据使其与master的数据一致。只要该线程与I/O线程保持一致中继日志通常会位于os缓存中所以中继日志的开销很小。 配置命令 配置命令 这里我们以Linux下的mysql作为主库Windows下的mysql作为从库来进行演示。
mastercentos7 192.168.29.128 slavewin10192.168.3.7
保证master和salve之间网络互通。并且保证3306端口是开放的。
master配置
开启二进制日志然后重启mysqld服务 创建一个用于主从库通信用的账号
mysql CREATE USER mslave192.168.29.1 IDENTIFIED BY Cjl1314520..;
mysql GRANT REPLICATION SLAVE ON *.* to mslave192.168.29.1;
mysql FLUSH PRIVILEGES;获取binlog的日志文件名和position
mysql show master status;slave配置 配置全局唯一的server-id涉及修改配置文件需要重启mysql57服务 使用master创建的账户读取binlog同步数据stop slavestart slave mysql CHANGE MASTER TO MASTER_HOST192.168.29.128,
MASTER_PORT3306,
MASTER_USERmslave,
MASTER_PASSWORDCjl1314520..,
MASTER_LOG_FILEmysql-bin.000002,
MASTER_LOG_POS765;START SLAVE 通过show slave status命令查看主从复制状态。show processlist查看master和salve相关线程的运行状态。 下面我们来看一下现象 下面我们来看一下在搭建主从复制环境中可能会遇到的一些常见问题以及解决方案 读写分离
原理介绍
MySQL读写分离 是一种常见的数据库架构设计模式主要是为了提高系统的性能和可用性。它通过将读操作和写操作分离到不同的MySQL实例上来实现。 在实际的生产环境中如果对数据库的读和写都在同一个数据库服务器中操作无论是安全性高可用还是并发等各个方面都不能完全满足实际需求的因此一般来说都是通过主从复制的方式来同步数据再通过读写分离来提供数据的高并发负载能力这样的方案来进行部署。
简单来说读写分离就是只在主服务器上写只在从服务器上读基本的原理是让主数据库处理事务性查询而从数据库处理select查询数据库复制被用来把事务性查询导致的改变更新同步到集群中的从数据库。
目前较为常见的MySQL读写分离方式有
程序代码内部实现引入中间代理层 MySQL_proxyMycat
实际的开发过程中我们不能将代码和主从环境强行绑定因为和环境强相关了我们写代码得时候必须得知道哪个机器是负责写操作的主库哪个机器是负责读操作的从库由代码来选择。而这时如果有某个机器挂掉了代码也不会知道还是按照原来的方式转发请求通信就会出现问题所以把读写分离用代码实现肯定不合适。所以在实际的解决方案中读写分离需要依赖数据库的中间件。
客户端实际上区分不出来连的是MyCat还是MySQL因为通信都是遵守的是MySQL通信协议之前怎么和MySQL通信现在就怎么和MyCat通信所以不用进行区分。
在MyCat上配置读写分离我们在客户端上的代码不用做任何变更代码上不需要区分哪个请求是读哪个请求是写代码直接访问的是MyCat由MyCat解析请求根据SQL的读写性质转发到负责相应操作的服务器实现读写分离。
在Mycat上需要配置主服务器和从服务器的信息有3种情况一主一从、一主多从、多主多从。
一主一从
多主多从
可以看到图中MyCat服务器挂了两套环境如果其中1套的主库宕机了它对应的从库也就不能用了此时MyCat会自动切到另一套环境因为M1和M2之间也是配置成互为主从的所以M2可以同步M1的数据提供与M1环境完全相同的服务所以它的 高可用容灾 能力是非常不错的。 MyCat服务端口和管理端口 MySQL的服务端口是3306MyCat服务端口是8066这个端口也是可以改的也就是MySQL Client连接的是8066端口登录8066端口看到的界面就和登录MySQL Server的3306端口一样。MyCat还有一个管理端口9066登录这个管理端口可以查看MyCat正在工作的所有状态以及和后端服务器的连接以及连接数据源的状态等。 配置命令 环境准备 mastercentos7、NAT模式、固定IP 192.168.29.128 slavewin10、路由器局域网、DHCP协议192.168.3.7 JDK1.7版本以上java -version检查jdk环境 MySQL的root账户有远程访问权限 查看主从复制状态 查看JDK版本
MyCat的运行需要java环境需要JDK1.7版本以上执行 java -version 检查JDK环境。 查看root远程连接权限 安装MyCat
用rz命令将MyCat安装包传输到Linux上。 解压mycat安装包 这里为了使用方便我们直接在 /usr/bin 下建立软链接连接用户目录下的mycat和我们解压路径下的mycat。
ln -s /MySQL/MyCat/mycat/bin/mycat /usr/bin/mycat配置文件 server.xml
server.xml 配置登录mycat账号信息。 不需要和MySQL的账号密码一样因为我们的MySQL Client直接访问的是MyCat再由MyCat登录MySQL Server。TESTDB是逻辑库是一个不存在的库最终这个库映射到后端的MySQL上实际上它会真实地映射到MySQL库表当中所以我们把它叫做逻辑库。这个逻辑库看起来好像在MyCat一台机器上实际上经过分库分表操作可能分配在不同的机器上我们只需要操作这个逻辑库就可以其他的不用关心。多个逻辑库的话用逗号分隔开。
schema.xml
schema.xml配置逻辑库与数据源、读写分离分库分表等。
逻辑库和逻辑表MySQL Client都是操作的MyCat上的逻辑库schema和逻辑表数据节点这个库或者表的内容放在哪个节点dataNode上这个节点对应具体的物理机器dataHost以下三个地方需要相同其中逻辑库、数据节点以及数据库主机名称都可以随便取 启动服务 启动mycat 查看服务是否正常
这里我们需要注意的是如果配置上有问题启动服务是看不出来的应该查看 mycat/logs/warpper.log记录了mycat启动过程中的错误。 mycat 9066端口和8066端口 9066管理端口 show help 可以显示mycat的管理端支持的命令 show database查看逻辑表 show datanode;查看逻辑节点和真实库之间的映射关系 show datasource;查看数据源 8066数据端口
OpenCloundDB表示我们看到的是一个云状数据库云后面是如何提供的库表的服务能力我们是不知道的。mycat就是云DB把后端所有的细节给客户端隐藏了客户端只需要去处理代理服务器上的DB就可以了。可以看作一个反向代理服务。 查看数据库 这个逻辑库TESTDB对应的就是真实库testDB。 验证读写分离 验证读操作在slave
将Windows上和Linux上的查询日志都打开。 验证读写操作
操作8066端口逻辑库下面的user表 在Linux下的master服务器查看general_log并没有看见查询日志。 在Windows下的slave服务器种查看 general_log看到了mycat发送的查询user表的SQL。 验证写操作在slave
登录mycat8066数据端口给user表insert一条数据。 在Windows下的slave服务器中查看general_log没有发现insert数据的SQL。 在Linux下的master服务器查看general_log看到了insert数据的SQL。 这样我们就验证了我们读写分离是没有问题的所有的写操作都是在master上进行的读操作都是在slave上进行的这就是读写分离比起单个主机既做写也做读操作肯定能提升它的性能。 验证容灾功能 我们在 mycat/conf/schema.xml 中配置的是多主多从因此M1挂了读写操作都会全部转发到M2上在我们当前的环境中如果Linux上的MySQL Server 挂了所有的读写操作都会转发给Windows上的MySQL Server。
关闭Linux上的mysqld服务相当于关闭了master。 这里我们登录mycat 8806端口对user表分别进行读写操作。 查看我们多主多从中备用系统的general_log即Windows下的MySQL Server 的 general_log。 这里可以看到由于master挂了读写操作都被转发到了备用的Windows 上的 MySQL Server证明容灾没有问题。 三、mysql分库分表
刚开始多数项目用单机数据库就够了随着服务器流量越来越大面对的请求也越来越多我们做了数据库读写分离 使用多个从库副本Slave负责读使用主库Master负责写master和slave通过主从复制实现数据同步更新保持数据一致。slave 从库可以水平扩展所以更多的读请求不成问题。
但是当用户量级上升写请求越来越多怎么保证数据库的负载足够增加一个Master是不能解决问题的 因为数据要保存一致性写操作需要2个master之间同步相当于是重复了而且架构设计更加复杂。这时需要用到 分库分表sharding对写操作进行切分。 库表问题 单库太大 单库处理能力有限、所在服务器上的磁盘空间不足、遇到IO瓶颈需要把单库切分成更多更小的库
单表太大CURD效率都很低、数据量太大导致索引膨胀、查询超时需要把单表切分成多个数据集更小的表 拆分策略 单个库太大先考虑是表多还是数据多。
如果因为表多而造成数据过多则使用垂直拆分即根据业务拆分成不同的库如果因为单张表的数据量太大则使用水平拆分即把表的数据按照某种规则拆分成多张表
分库分表的原则应该是先考虑垂直拆分再考虑水平拆分。
垂直拆分 垂直分表 也就是“大表拆小表”基于列字段进行。一般是表中的字段较多将不常用的 数据较大长度较长比如text类型字段的拆分到“扩展表“。一般是针对几百列的这种大表也避免查询时数据量太大造成的“跨页”问题。 垂直分库 垂直分库针对的是一个系统中的不同业务进行拆分。
比如用户User一个库商品Product一个库订单Order一个库 切分后要放在多个服务器上而不 是一个服务器上。想象一下一个购物网站对外提供服务会有用户商品订单等的CRUD。没拆分之前 全部都是落到单一的库上的这会让数据库的单库处理能力成为瓶颈。按垂直分库后如果还是放在一个数据库服务器上 随着用户量增大这会让单个数据库的处理能力成为瓶颈还有单个服务器的磁盘空间内存tps等非常吃紧。 所以我们要拆分到多个服务器上这样上面的问题都解决了以后也不会面对单机资源问题。
数据库业务层面的拆分和服务的“治理”“降级”机制类似也能对不同业务的数据分别的进行管理 维护监控扩展等。 数据库往往最容易成为应用系统的瓶颈而数据库本身属于“有状态”的相对于Web和应用服务器来讲是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限在高并发场景下垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。 水平拆分 水平分表 针对数据量巨大的单张表比如订单表按照某种规则RANGE,HASH取模等切分到多张表里面去。 但是这些表还是在同一个库中所以库级别的数据库操作还是有IO瓶颈不建议采用。 水平分库分表 将单张表的数据切分到多个服务器上去每个服务器具有相应的库与表只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力突破IO、连接数、硬件资源等的瓶颈。
下面我们以水平拆分的方式来模拟一下 修改配置文件 创建数据库和表 登录mycat服务器 分库分表演示
