宝塔 mysql ssl 宝塔 mysql集群

转载

mob6454cc7796a7 2023-11-24 07:49:37

文章标签 宝塔 mysql ssl mysql 服务器数据 文章分类 MySQL 数据库

mysql安装

MySQL官方安装手册地址：https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html

或者可以使用宝塔面板、Docket

mysql主从集群
环境介绍

两台centos7服务器

为了便于使用，两个mysql服务需要打开远程登录权限，开启方式需要在本机登录mysql，执行以下语句。

#开启远程登录

use mysql;
 update user set host=’%’ where user=‘root’;
 flush privileges;

一下mysql主节点称为主服务器从节点称为从服务器

配置mysql集群的主服务器

修改mysql的配置文件/etc/my.cnf文件
server-id=47
#开启binlog
log_bin=master-bin
log_bin-index=master-bin.index
skip-name-resolve
 # 设置连接端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysql-files
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
 # 服务端使用的字符集默认为UTF8
character-set-server=utf8
 # 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
 # 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password

配置说明：主要需要修改的是以下几个属性：

server-id：服务节点的唯一标识。需要给集群中的每个服务分配一个单独的ID。

log_bin：打开Binlog日志记录，并指定文件名。

log_bin-index：Binlog日志文件

	重启MySQL服务， service mysqld restart

	然后，我们需要给root用户分配一个replication slave的权限。

#登录主数据库
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';
flush privileges;
#查看主节点同步状态：
show master status;

在实际生产环境中，通常不会直接使用root用户，而会创建一个拥有全部权限的用户来负责主从同步。

mysql集群的从服务器

#主库和从库需要不一致
server-id=48
#打开MySQL中继日志
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
#打开从服务二进制日志
log-bin=mysql-bin
#使得更新的数据写进二进制日志中
log-slave-updates=1
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/mysql-files
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
配置说明：主要需要关注的几个属性：

server-id：服务节点的唯一标识

relay-log：打开从服务的relay-log日志。

log-bin：打开从服务的bin-log日志记录。

然后我们启动mysqls的服务，并设置他的主节点同步状态。

#登录从服务

mysql -u root -p;
#设置同步主节点：
CHANGE MASTER TO
MASTER_HOST='192.168.232.128',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='master-bin.000004',
MASTER_LOG_POS=156
GET_MASTER_PUBLIC_KEY=1;
#开启slave
start slave;
#查看主从同步状态
show slave status;
或者用 show slave status \G; 这样查看比较简洁

CHANGE MASTER TO
 MASTER_HOST=‘192.168.232.128’,
 MASTER_PORT=3306,
 MASTER_USER=‘root’,
 MASTER_PASSWORD=‘root’,
 MASTER_LOG_FILE=‘master-bin.000004’,
 MASTER_LOG_POS=156
 GET_MASTER_PUBLIC_KEY=1; 这是一条sql语句

CHANGE MASTER指令中需要指定的MASTER_LOG_FILE和MASTER_LOG_POS必须与主服务中查到的保持一致。

集群扩展知识

全库同步与部分同步
就是让从服务器只同步那些database跟它下面的数据

主服务器在my.cnf文件中添加

#需要同步的二进制数据库名
binlog-do-db=masterdemo
#只保留7天的二进制日志，以防磁盘被日志占满(可选)
expire-logs-days  = 7
#不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys

这三种参数如多个database就跟binlog-ignore-db这个参数一样写多行即可

从服务器在my.cnf中添加

#如果salve库名称与master库名相同，使用本配置
replicate-do-db = masterdemo 
#如果master库名[mastdemo]与salve库名[mastdemo01]不同，使用以下配置[需要做映射]
replicate-rewrite-db = masterdemo -> masterdemo01
#如果不是要全部同步[默认全部同步]，则指定需要同步的表
replicate-wild-do-table=masterdemo01.t_dict
replicate-wild-do-table=masterdemo01.t_num

多个database写多行跟主服务配置的一个意思

读写分离配置

就是mysql主从集群当给主服务器添加数据时从服务器会自动同步但是从服务区也是可以添加数据的但是从服务器添加的数据无法被主服务器同步所以从服务器一般用来做查询主服务器用来添加这就是所谓的读写分离可以通过 set global read_only=1;这个sql语句设置从服务器为只读这个只读不会影响主从之间的同步只限制了普通用户的写入管理员用户还是可以写的如果想让管理员也不能写super_read_only=0设置这个参数即可但是这样主从之间的同步也会给禁止了

其他集群方式

主从互主集群就是服务器1是服务器2的主服务2也是服务器1的主搭建互主集群只需要按照上面的方式，在主服务上打开一个slave进程，并且指向slave节点的binlog当前文件地址和位置把上面的主从配置在各各机器中都写上互相配上对方的ip

一主多从可以提升mysql读取数据的压力

GTID同步集群

之前的主从同步是根据binlog文件实现同步这个文件记录了我们执行的每一条可以对数据做出修改的sql 当从服务器跟主服务同步数据时主服务器会开启一个线程把binarylog文件发送到从服务器当然从服务也是开启一个线程接收这个文件就是使用io流到了从服务器这边就把这个io中的数据写入到一个relaylog文件中然后开启一个线程把同步过来的sql一个一个的执行 GTID是mqsql5.6引入本质也是使用的binlog实现主从同步但是它是基于事物id来表示同步进度的事物id是一个全局的变量每当开启一个事物就会从0-N依次递增的一个数字当id 然后同步的时候从服务器会告诉主服务器执行了那些事物id 然后主服务器会把未执行的事物id发送给从服务器然后根据事物执行sql然后就同步了

主服务器的配置写到my.cnf文件

gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
server_id=单独设置一个
binlog_format=row

从服务器的配置写到my.cnf文件

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1
server_id=单独设置一个

集群扩容

当现在有一主一从两台机器现在有启动了一台机器当从服务器这样的同步是先在主服务器 mysqldump -u root -p --all-databases > backup.sql 执行这个命令它会把主服务器所有的数据生成一个sql 然后去新启动的这台从服务器中执行mysql -u root -p < backup.sql 然后在给这台机器的my.cnf配置上面写的从服务器的参数然后重新启动

半同步复制

当从服务器跟主服务器同步数据时是开启了一个线程同步这样就是异步的主服务器是不知道有没有同步成功如果数据没有同步成功在主从集群、互主集群就会有数据丢失的隐患

所以就同步数据的时候使用同步来同步数据当主服务器给从服务器发送binlog数据时让客户端进行阻塞当从服务器返回一个ack同步成功的消息才让客户端返回成功但是如果当从服务器宕机了就会永远的阻塞所以在给客户端返回的时候设置一个过期时间这就称为了半同步复制

搭建半同步复制集群

需要使用mysql安装目录下的lib/plugin目录下的semisync_master.so和semisync_slave.so两个文件在主服务器安装semisync_master.so 从服务器安装semisync_slave.so

主服务器

install plugin rpl_semi_sync_master soname 'semisync_master.so';
执行这个的命令即可    前提要登录mysql

从服务器

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';  
执行这个的命令即可    前提要登录mysql

主从架构的数据延迟

在搭建的主从集群中主从直接的复制会有延迟在做了读写分离之后更容易体现出来刚在主服务器插入一条数据从服务器查询确查不出来这就是因为主从复制之间的延迟这个问题根本在于面向的业务是多线程并发写入同步数据时从服务器是单个线程拉取binlog 这就会有效率问题在mysql5.7版本可以支持并发复制通过set global slave_parallel_workers=一个大于0的数;set global slave_parallel_type=LOGICAL_CLOCK; 就可以了

mysql高可用方案

MMM

MMM(Master-Master replication managerfor Mysql，Mysql主主复制管理器) 它会监听所有的主服务器做一个主从互主的集群它会帮我们自动设置哪个主节点复制写哪个主节点复制读当这两个主节点其中一个宕机会根据一个虚拟ip的机制（VIP）如果读的节点宕机了会把读的功能放到写的节点上让这个节点既可以读也可以写虚拟ip就是 mmm给我的mysql的主节点创建了一个虚拟IP 当读的节点宕机了这个虚拟ip就会变成写的节点的IP 就是会变成其它节点的ip 那个读那个写也都是通过虚拟ip决定的

优点
提供了读写VIP的配置，使读写请求都可以达到高可用

缺点

目前MMM社区已经缺少维护，不支持基于GTID的复制
故障简单粗暴，容易丢失事务，建议采用半同步复制方式，减少失败的概率

MHA

Master High Availability Manager and Tools for MySQL（读音马斯特海儿饿外老白老tei卖了着按的吐奥斯fao吗色口中文意思掌握MySQL的高可用性管理器和工具） MHA需要单独部署分为Manager节点和Node节点 Manager节点一般是单独部署的一台机器，而Node节点一般是部署在MySQL机器上 Node节点通过解析MySQL的日志来进行一些操作Manager节点会通过探测集群里的Node节点去判断各个Node所在机器上的MySQL运行是否正常，如果发现某个Master故障了，就直接把他的一个Slave提升为Master，然后让其他Slave都挂到新的Master上去，完全透明。 MHA的VIP转移脚本需要我们自己写

MGR

MGR：MySQL Group Replication。是MySQL官方在5.7.17版本正式推出的一种组复制机制。主要是解决传统异步复制和半同步复制的数据一致性问题。

由若干个节点共同组成一个复制组，一个事务提交后，必须经过超过半数节点的决议并通过后，才可以提交。引入组复制，主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。MGR依靠分布式一致性协议(Paxos协议的一个变体)，实现了分布式下数据的最终一致性，提供了真正的数据高可用方案(方案落地后是否可靠还有待商榷)。

支持多主模式，但官方推荐单主模式：

多主模式下，客户端可以随机向MySQL节点写入数据
单主模式下，MGR集群会选出primary节点负责写请求，primary节点与其它节点都可以进行读请求处理.

分库分表的方式

分库就是判断这个数据存到哪个数据库中分表就是判断这个数据分到哪个表中专业术语统称为分片

垂直分片又称为纵向分片按着业务来划分数据库比如订单就会有订单的数据库用户就有用户的数据库这样的划分

水平分片就是把同一个业务的数据放到多张表或者不同的数据库中比如通过这个数据的id如果id为字符串就根据哈希值对表取模然后放到对应的表中就是把数据分散存储

常用的分片策略有：

取余\取模：优点均匀存放数据，缺点扩容非常麻烦

按照范围分片：比较好扩容，数据分布不够均匀

按照时间分片：比较容易将热点数据区分出来。

按照枚举值分片：例如按地区分片

按照目标字段前缀指定进行分区：自定义业务规则分片

水平分片从理论上突破了单机数据量处理的瓶颈，并且扩展相对自由，是分库分表的标准解决方案。

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案

分库分表的缺点
虽然数据分片解决了性能、可用性以及单点备份恢复等问题，但是分布式的架构在获得收益的同时，也引入了非常多新的问题。

事务一致性问题
原本单机数据库有很好的事务机制能够帮我们保证数据一致性。但是分库分表后，由于数据分布在不同库甚至不同服务器，不可避免会带来分布式事务问题。

跨节点关联查询问题
在没有分库时，我们可以进行很容易的进行跨表的关联查询。但是在分库后，表被分散到了不同的数据库，就无法进行关联查询了。

这时就需要将关联查询拆分成多次查询，然后将获得的结果进行拼装。

跨节点分页、排序函数
跨节点多库进行查询时，limit分页、order by排序等问题，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序。

这时非常容易出现内存崩溃的问题。

主键避重问题
在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。

公共表处理
实际的应用场景中，参数表、数据字典表等都是数据量较小，变动少，而且属于高频联合查询的依赖表。这一类表一般就需要在每个数据库中都保存一份，并且所有对公共表的操作都要分发到所有的分库去执行。

运维工作量
面对散乱的分库分表之后的数据，应用开发工程师和数据库管理员对数据库的操作都变得非常繁重。对于每一次数据读写操作，他们都需要知道要往哪个具体的数据库的分表去操作，这也是其中重要的挑战之一。

什么时候分库分表

在阿里巴巴公布的开发手册中，建议MySQL单表记录如果达到500W这个级别，或者单表容量达到2GB，一般就建议进行分库分表。而考虑到分库分表需要对数据进行再平衡，所以如果要使用分库分表，就要在系统设计之初就详细考虑好分库分表的方案，这里要分两种情况。

一般对于用户数据这一类后期增长比较缓慢的数据，一般可以按照三年左右的业务量来预估使用人数，按照标准预设好分库分表的方案。

而对于业务数据这一类增长快速且稳定的数据，一般则需要按照预估量的两倍左右预设分库分表方案。并且由于分库分表的后期扩容是非常麻烦的，所以在进行分库分表时，尽量根据情况，多分一些表。最好是计算一下数据增量，永远不用增加更多的表。

另外，在设计分库分表方案时，要尽量兼顾业务场景和数据分布。在支持业务场景的前提下，尽量保证数据能够分得更均匀。

最后，一旦用到了分库分表，就会表现为对数据查询业务的灵活性有一定的影响，例如如果按userId进行分片，那按age来进行查询，就必然会增加很多麻烦。如果再要进行排序、分页、聚合等操作，很容易就扛不住了。这时候，都要尽量在分库分表的同时，再补充设计一个降级方案，例如将数据转存一份到ES，ES可以实现更灵活的大数据聚合查询。

场景的分库分表组件

shardingsphere 官网地址：https://shardingsphere.apache.org/document/current/cn/overview/



   mycat 官网地址： http://www.mycat.org.cn/
 



   DBLE 官网地址：https://opensource.actionsky.com/

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。