今天我们就来讲讲mysql高可用。
CentOS7 64位安装MySql
1. 先检查系统是否装有mysql
rpm -qa | grep mysql
2. 下载mysql的repo源
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
报错: -bash: wget: 未找到命令
安装插件 yum -y install wget
3. 安装mysql-community-release-el7-5.noarch.rpm包
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
4. 安装MySQL
sudo yum install mysql-server
5. 重置MySQL密码
mysql -u root
报错:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
原因:原因是/var/lib/mysql的访问权限问题。
chown root /var/lib/mysql/
6. 重启MySQL服务
service mysqld restart
7. 接着登陆设置密码
mysql -u root
use mysql;
update user set password=password('123456') where user='root';
exit;
接着继续重启MySQL服务
service mysqld restart
8. 接着设置Root账户远程连接密码
mysql -u root -p
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";
重启服务器 service mysqld restart
9. 使用外网工具连接MySQL
关闭防火墙
systemctl stop firewalld.service
MySQL主从复制配置
主从复制原理
MySQL的主从复制是MySQL本身自带的一个功能,不需要额外的第三方软件就可以实现,其复制功能并不是copy文件来实现的,而是借助binlog日志文件里面的SQL命令实现的主从复制,可以理解为我再Master端执行了一条SQL命令,那么在Salve端同样会执行一遍,从而达到主从复制的效果。
从库生成两个线程,一个I/O线程,一个SQL线程;
i/o线程去请求主库 的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中;
主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog;
SQL 线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致;
主服务器节点
vi /etc/my.cnf 新增以下内容
server_id=177 ###服务器id
log-bin=mysql-bin ###开启日志文件
重启mysql服务 service mysqld restart
验证是否已经配置成功
show variables like '%server_id%';
能够查询对应配置文件中的server_id 说明已经配置成功
show master status;
能够看到同步的文件,和行数 说明已经配置成功。
从服务器节点
克隆服务器
vi /etc/my.cnf
server_id=178 ###从服务器server_id
log-bin=mysql-bin ###日志文件同步方式
binlog_do_db=test ###同步数据库
重启mysql服务 service mysqld restart
验证是否已经配置成功
show variables like '%server_id%';
能够查询对应配置文件中的server_id 说明已经配置成功
从服务器同步主服务器配置
change master to master_host='192.168.212.200',master_user='root',master_password='root',
master_log_file='mysql-bin.000002',master_log_pos=216;
开始同步
start slave
检查从服务器复制功能状态
SHOW SLAVE STATUS
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决办法
因为服务器克隆的时候交UUID产生了重复 ,解决办法
cat /etc/my.cnf
cd /var/lib/mysql
rm -rf auto.cnf
重启服务器即可
service mysqld restart
MyCat实现读写分离
什么是MyCat
MyCAT是一款由阿里Cobar演变而来的用于支持数据库,读写分离、分表分库的分布式中间件。MyCAT支持Oracle、MSSQL、MYSQL、PG、DB2关系型数据库,同时也支持MongoDB等非关系型数据库。
MyCAT原理MyCAT主要是通过对SQL的拦截,然后经过一定规则的分片解析、路由分析、读写分离分析、缓存分析等,然后将SQL发给后端真实的数据块,并将返回的结果做适当处理返回给客户端。
官方网站:http://www.mycat.io/
Linux环境安装MyCat实现读写分离
1、上传安装Mycat-server-1.6.5-release-20180122220033-linux.tar
2、解压安装包tar –zxvf
3、配置schema.xml 和server.xml
4、客户端连接端口号: 8066
配置文件介绍:
文件
说明: server.xml Mycat的配置文件,设置账号、参数等
schema.xml Mycat对应的物理数据库和数据库表的配置
rule.xmlMycat分片(分库分表)规则
Linux环境安装MyCat实现读写分离
1、进入bin目录
启动MyCat ./mycat start
停止MyCat ./mycat stop
2、查看/usr/local/mycat/logs wrapper.log日志 如果是为successfully 则启动成功
关闭防火墙:systemctl stop firewalld.service
只可读的账号 user user 端口号8066
可读可写的账号 root 123456 端口号8066
SpringBoot项目整合动态数据源(读写分离)
1.配置多个数据源,根据业务需求访问不同的数据,指定对应的策略:增加,删除,修改操作访问对应数据,查询访问对应数据,不同数据库做好的数据一致性的处理。由于此方法相对易懂,简单,不做过多介绍。
2. 动态切换数据源,根据配置的文件,业务动态切换访问的数据库:此方案通过Spring的AOP,AspactJ来实现动态织入,通过编程继承实现Spring中的AbstractRoutingDataSource,来实现数据库访问的动态切换,不仅可以方便扩展,不影响现有程序,而且对于此功能的增删也比较容易。
3. 通过mycat来实现读写分离:使用mycat提供的读写分离功能,mycat连接多个数据库,数据源只需要连接mycat,对于开发人员而言他还是连接了一个数据库(实际是mysql的mycat中间件),而且也不需要根据不同 业务来选择不同的库,这样就不会有多余的代码产生。
动态数据源核心配置
在Spring 2.0.1中引入了AbstractRoutingDataSource, 该类充当了DataSource的路由中介, 能有在运行时, 根据某种key值来动态切换到真正的DataSource上。
1.项目中需要集成多个数据源分别为读和写的数据源,绑定不同的key。
2.采用AOP技术进行拦截业务逻辑层方法,判断方法的前缀是否需要写或者读的操作
3.如果方法的前缀是写的操作的时候,直接切换为写的数据源,反之切换为读的数据源
也可以自己定义注解进行封装
动态数据源与多数数据源区别
多数据源---以分包形式
动态数据在jvm进行不断地进行切换
数据库集群如何考虑数据库自增唯一性
在数据库集群环境下,默认自增方式存在问题,因为都是从1开始自增,可能会存在重复,应该设置每台节点自增步长不同。
查询自增的步长
SHOW VARIABLES LIKE 'auto_inc%'
修改自增的步长
SET @@auto_increment_increment=10;
修改起始值
SET @@auto_increment_offset=5;
假设有两台mysql数据库服务器
节点①自增 1 3 5 7 9 11 ….
节点②自增 2 4 6 8 10 12 ….
注意:在最开始设置好了每台节点自增方式步长后,确定好了mysql集群数量后,无法扩展新的mysql,不然生成步长的规则可能会发生变化。
数据库分表分库策略
数据库分表分库原则遵循 垂直拆分与水平拆分
垂直拆分就是根据不同的业务,分为不同的数据库,比如会员数据库、订单数据库、支付数据库等,垂直拆分在大型电商系统中用的非常常见。
优点:
拆分后业务清晰,拆分规则明确,系统之间整合或扩展容易。
缺点:
部分业务表无法join,只能通过接口方式解决,提高了系统复杂度存在分布式事务问题。
垂直拆分:把不同的表拆到不同的数据库中,而水平拆分是把同一个表拆到不同的数据库中。
相对于垂直拆分,水平拆分不是将表的数据做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中 的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中,主要有分表,分库两种模式
该方式提高了系统的稳定性跟负载能力,但是跨库join性能较差。
使用MyCat实现水平分片策略
MyCat支持10种分片策略
1、求模算法
2、分片枚举
3、范围约定
4、日期指定
5、固定分片hash算法
6、通配取模
7、ASCII码求模通配
8、编程指定
9、字符串拆分hash解析
详细:http://www.mycat.io/document/mycat-definitive-guide.pdf
使用MyCat实现水平分片策略
分片枚举这种规则适用于特定的场景,比如有些业务需要按照省份或区县来做保存,而全国的省份区县固定的,这类业务使用这一规则。配置如下
1.案例步骤:
创建数据库userdb_1 、 userdb_2、userdb_3
2.修改partition-hash-int.txt 规则
wuhan=0
shanghai=1
suzhou=2
根据地区进行分库 湖北数据库、江苏数据库 山东数据库
使用MyCat分表分库原理分析
1.Mycat中的路由结果是通过分片字段和分片方法来确定的,如果查询条件中有 id 字段的情况还好,查询将会落到某个具体的分片
2.如果查询没有分片的字段,会向所有的db都会查询一遍,让后封装结果级给客户端。
修改/usr/local/mycat/conf/log4j2.xml日志级别为debug
Sharding-Jdbc
Sharding-Jdbc在3.0后改名为Shardingsphere它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar(计划中)这3款相互独立的产品组成。他们均提供标准化的数据分片、分布式事务和数据库治理功能,可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。
Sharding-Sphere定位为关系型数据库中间件,旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力,而并非实现一个全新的关系型数据库。它通过关注不变,进而抓住事物本质。关系型数据库当今依然占有巨大市场,是各个公司核心业务的基石,未来也难于撼动,我们目前阶段更加关注在原有基础上的增量,而非颠覆。
应用场景:
数据库读写分离
数据库分表分库
相关资料:
Sharding-Jdbc官方网址: http://shardingsphere.io/index_zh.html
改名新闻: https://www.oschina.net/news/95889/sharding-jdbc-change-to-sphere
Sharding-Jdbc与MyCat区别
MyCat是一个基于第三方应用中间件数据库代理框架,客户端所有的jdbc请求都必须要先交给MyCat,再有MyCat转发到具体的真实服务器中。
Sharding-Jdbc是一个Jar形式,在本地应用层重写Jdbc原生的方法,实现数据库分片形式。
MyCat属于服务器端数据库中间件,而Sharding-Jdbc是一个本地数据库中间件框架。
从设计理念上看确实有一定的相似性。主要流程都是SQL 解析 -> SQL 路由 -> SQL 改写 -> SQL 执行 -> 结果归并。但架构设计上是不同的。Mycat 是基于 Proxy,它复写了 MySQL 协议,将 Mycat Server 伪装成一个 MySQL 数据库,而 Sharding-JDBC 是基于 JDBC 的扩展,是以 jar 包的形式提供轻量级服务的。
Sharding-Jdbc实现读写分离
Sharding-Jdbc实现读写分离原理,非常容易。只需要在项目中集成主和从的数据源,Sharding-Jdbc自动根据DML和DQL 语句类型连接主或者从数据源。
注意: Sharding-Jdbc只是实现连接主或者从数据源,不会实现主从复制功能,需要自己配置数据库自带主从复制方式。
查看MasterSlaveDataSource即可查看该类getDataSource方法获取当前数据源名称
DML:数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE 子句组成的查询块: SELECT FROM WHERE
DQL:数据操纵语言DML主要有三种形式: 1) 插入:INSERT 2) 更新:UPDATE 3) 删除:DELETE
Sharding-Jdbc分表分库
LogicTable
数据分片的逻辑表,对于水平拆分的数据库(表),同一类表的总称。
订单信息表拆分为2张表,分别是t_order_0、t_order_1,他们的逻辑表名为t_order。
ActualTable
在分片的数据库中真实存在的物理表。即上个示例中的t_order_0、t_order_1。
DataNode
数据分片的最小单元。由数据源名称和数据表组成,例:test_msg0.t_order_0。配置时默认各个分片数据库的表结构均相同,直接配置逻辑表和真实表对应关系即可。
ShardingColumn
分片字段。用于将数据库(表)水平拆分的关键字段。SQL中如果无分片字段,将执行全路由,性能较差。Sharding-JDBC支持多分片字段。
ShardingAlgorithm
分片算法。Sharding-JDBC通过分片算法将数据分片,支持通过等号、BETWEEN和IN分片。分片算法目前需要业务方开发者自行实现,可实现的灵活度非常高。未来Sharding-JDBC也将会实现常用分片算法,如range,hash和tag等。
SpringBoot整合Sharding-Jdbc分为两种方式
一种为原生配置方式,自己需要实现接口。
1.分库算法类需要实现SingleKeyDatabaseShardingAlgorithm接口
2.分表算法类需要实现SingleKeyTableShardingAlgorithm接口
第二种通过配置文件形式配置。
案例比如:t_order 拆分程t_order_0 t_order _1
Sharding-Jdbc日志分析与原理图
Sharding-JDBC中的路由结果是通过分片字段和分片方法来确定的,如果查询条件中有 id 字段的情况还好,查询将会落到某个具体的分片
如果查询没有分片的字段,会向所有的db或者是表都会查询一遍,让后封装结果级给客户端。
Sharding-Jdbc和MyCat查询原理大致相同
深入mysql原理
数据结构Hash算法
哈希表(Hash table,也叫散列表),是根据关键码值(Key value)而直接进行访问的数据结构。也就是说,它通过把关键码值映射到表中一个位置来访问记录,以加快查找的速度。这个映射函数叫做散列函数,存放记录的数组叫做散列表。
优点:查找可以直接根据key访问
缺点: 不能进行范围查找
index=Hash(key)
数据结构平衡二叉树算法
平衡二叉查找树,又称 AVL树。 它除了具备二叉查找树的基本特征之外,还具有一个非常重要的特点:它 的左子树和右子树都是平衡二叉树,且左子树和右子树的深度之差的绝对值(平衡因子 ) 不超过1。 也就是说AVL树每个节点的平衡因子只可能是-1、0和1(左子树高度减去右子树高度)。
优点:平衡二叉树算法基本与二叉树查询相同,效率比较高
缺点:插入操作需要旋转,支持范围查询
数据结构B树
维基百科对B树的定义为“在计算机科学中,B树(B-tree)是一种树状数据结构,它能够存储数据、对其进行排序并允许以O(log n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。B树,概括来说是一个节点可以拥有多于2个子节点的二叉查找树。与自平衡二叉查找树不同,B-树为系统最优化大块数据的读和写操作。B-tree算法减少定位记录时所经历的中间过程,从而加快存取速度。普遍运用在数据库和文件系统。”
因为B树节点元素比平衡二叉树要多,所以B树数据结构相比平衡二叉树数据结构实现减少磁盘IO的操作。
数据结构B+树
B+树相比B树,新增叶子节点与非叶子节点关系,叶子节点中包含了key和value,非叶子节点中只是包含了key,不包含value。
所有相邻的叶子节点包含非叶子节点,使用链表进行结合,有一定顺序排序,从而范围查询效率非常高。
MySQLb+树能够存放多少字节数据
假设在B+树一个节点为1页
如果从磁盘读取超过1页大小,根据局部性原理与磁盘预读 会读出2页大小如果从磁盘读取小于1页大小,根据局部性原理与磁盘预读 会读出1页大小
根据以上规则,如果读取整哈是页的倍数,这样就可以不用浪费,所以B+树的每一个节点是页的倍数是最佳的。
在MySQL中我们的InnoDB页的大小默认是16k,当然也可以通过参数设置:
show variables like 'innodb_page_size';
16384/1024=16kb;
MySQLb+树能够存放多少字节数据
假设一行为1kb,那么一页可以读取16行数据,一个叶子节点可以存放16条数据
那么非叶子节点存放多少条数据?非叶子节点存放索引值(bigint 8b)和指针(6b)
那么一页16*1024/(8+6)=1170指针
B+树 高度为2 1170*16=18720 条数据
B+树 高度为3 1170*1170*16=21902400 条数据
所以在InnoDB中B+树高度一般为1-3层,它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO,所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。
磁盘IO性质
CPU←内存←磁盘(I/O)
1次:从磁盘读取4到内存中,判断10>4 ,取右指针
2次:从磁盘读到8到内存中,判断10>8,取右指针
3次:从磁盘读到9到内存中,判断10>9,取右指针
4次:从磁盘读到10到内存中,判断10=10,定位到数据
索引文件如何查看
默认数据与索引文件位置: /var/lib/mysql
MyISAM引擎的文件:
.myd 即 my data,表数据文件
.myi 即my index,索引文件
.log 日志文件。
InnoDB引擎的文件:
采用表空间(tablespace)来管理数据,存储表数据和索引,
InnoDB数据库文件(即InnoDB文件集,ib-file set):
ibdata1、ibdata2等:系统表空间文件,存储InnoDB系统信息和用户数据库表数据和索引,所有表共用。
.ibd文件:单表表空间文件,每个表使用一个表空间文件(file per table),存放用户数据库表数据和索引。
数据结构模拟工具
官网首页:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
Avl数据结构https://www.cs.usfca.edu/~galles/visualization/AVLtree.html
MyISAM和InnoDB对B-Tree索引不同的实现方式
主键索引: MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的
这里设表一共有三列,假设我们以Col1为主键,图myisam1是一个MyISAM表的主索引(Primary key)示意。可以看出
MySQL数据库优化方案
Mysql的优化,大体可以分为三部分:索引的优化,sql慢查询的优化,表的优化
MySQL数据库优化方案
开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。
先捕获低效SQL→慢查询优化方案→慢查询优化原则
索引为什么会失效?注意那些事项?
1.索引无法存储null值
2.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
3.对于多列索引,不是使用的第一部分,则不会使用索引
4.like查询以%开头
5.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
6.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
联合索引为什么需要遵循左前缀原则?
如果在一张表中,存在联合索引的话,在根据条件查询的时候必须要加上第一个索引条件。
EXPLAIN select * from user_details WHERE id=1 and user_name=‘yushengjun1’;---索引生效
EXPLAIN select * from user_details WHERE user_name=‘yushengjun1’; 索引是不生效的
因为索引底层采用B+树叶子节点顺序排列,必须通过左前缀索引才能定位到具体的节点范围。
(1,yushengjun1 1,yushengjun2),(2,yushengjun1 2,yushengjun2),(3,yushengjun1 3,yushengjun2)
表分库为什么能够提高数据库查询效率?
分表分库为什么提高查询的效率?因为会将一张表的数据拆分成多个n张表进行存放,让后在使用第三方中间件(MyCat或者Sharding-JDBC)并行同时查询,让后在交给第三方中间进行组合返回给客户端。
谢谢大家的支持和关注,喜欢记得点赞转发。