网站MySQL数据库优化方案主从架构及读写分离.docx
- 文档编号:26252387
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:30
- 大小:261.88KB
网站MySQL数据库优化方案主从架构及读写分离.docx
《网站MySQL数据库优化方案主从架构及读写分离.docx》由会员分享,可在线阅读,更多相关《网站MySQL数据库优化方案主从架构及读写分离.docx(30页珍藏版)》请在冰豆网上搜索。
网站MySQL数据库优化方案主从架构及读写分离
网站MySQL数据库优化方案
网络运维信息管理中心
(2020年8月)
1总则
数据库为网站提供数据的结构化存储,是网站系统的重要组成部分,但随着业务逻辑的复杂度的增加,数据库需要不断的优化,单一的数据库已无法满足现在要求。
1.1优化目标
针对网站的MySQL数据库部署架构进行优化,其优化的目的是为了防止数据库出现单点故障问题,提高数据库的处理能力,提高数据库的可靠性,为保证网站业务正常办理。
1.2优化工作思路
1、对现有数据库现状分析包括现有数据库配置合理性分析、现有数据库部署情况两部分工作内容;
2、梳理现有网站的功能模块,目的是通过梳理网站的各功能模块对数据读取时效性,分析其是否可以实现读写;
3、以数据库主从架构及数据库读写分离方式,对网站的MySQL数据库提出数据库部署架构优化的方案,数据库主从架构的多数据库模式,解决数据库单点存在的问题,当主数据库出现宕机时,可以将从数据库代替主数据库恢复业务系统正常运行,而且避免数据的丢失,提高数据库高可靠性和高可用性;通过部分查询统计功能,实现数据库读写分离,以便对数据库负载进行分流,缓解主数据库的读取压力。
2数据库现状
2.1当前数据库部署架构图
当前网站的数据库采用单台MySQL数据库提供数据库服务,当前部署架构图如下:
2.2现有数据库主要配置梳理
项目
参数名称
当前设定值
参数说明
临时表参数配置
tmp_table_size
512M
规定了内部内存临时表的最大值,每个线程都要分配。
(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值)
max_heap_table_size
512M
独立的内存表所允许的最大容量。
此选项为了防止意外创建一个超大的内存表导致用尽所有的内存资源。
连接数配置
max_connect_errors
200000
对于同一主机,如果有超出该参数值个数的中断错误连接,则该主机将被禁止连接。
如需对该主机进行解禁,执行:
FLUSHHOST
max_connections
20000
MySQL服务所允许的同时会话数的上限,其中一个连接将被SUPER权限保留作为管理员登录。
即便已经达到了连接数的上限。
表的文件描述符配置
open-files-limit
65535
控制着mysqld进程能使用的最大文件描述(FD)符数量。
是mysql中的一个全局变量且不可动态修改。
table_definition_cache
65536
表定义的缓存。
从其它资源中分离出来了,例如表描述符。
打开的表依然是每个线程,每个表用的,但是表定义是全局的,可以被所有连接有效地共享。
慢查询日志配置
slow-query-log
ON
开启慢查询日志。
slow_query_log_file
/data/soa/mysql/slow_query.log
慢查询日志存放路径。
long_query_time
1
超过1秒的查询,记录到慢查询日志,默认值1。
错误日志配置
log-error
/data/soa/mysql/mysqlcs.err
错误日志保存的文件路径。
查询缓存参数配置排序参数配置
query_cache_size
0
查询的缓存大小设置。
table_open_cache
65535
所有SQL线程可以打开表缓存的数量,缓存ibd/MYI/MYD文件。
read_buffer_size
4M
是MySQL读入缓冲区大小。
对表进行顺序扫描的请求将分配一个读入缓冲区,MySQL会为它分配一段内存缓冲区。
read_buffer_size变量控制这一缓冲区的大小。
read_rnd_buffer_size
16M
MySQL的随机读缓冲区大小。
当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。
进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索。
sort_buffer_size
32M
MySQL执行排序使用的缓冲大小。
如果想要增加ORDERBY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。
join_buffer_size
32M
联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。
quick
Mysqldump导出配置,参数不缓冲查询,直接输出。
2.3数据库部署情况梳理
2.3.1数据库安装部署情况梳理
数据安装目录
数据库数据目录
配置文件目录
/data/soa/mysql
/data/soa/mysql/data
/data/soa/mysql/etc/f
2.3.2现有应用连接数据库情况梳理
连接数据库的应用系统有会员管理、权限管理、订单模块、商品管理、促销管理、广告管理、报表统计、文章管理、评论管理、系统设置、数据库管理、短信管理、推荐管理、邮件群发管理等。
2.3.3数据库服务启动、停止方式梳理
1、启动命令
(1)普通启动:
/data/soa/mysql/bin/mysqld
(2)centos6以前版本:
servicestartmysqld
(3)centos7+:
systemctlmysqldstart
2、重启命令
先停止:
killallmysqld
后启动:
/data/soa/mysql/bin/mysqld
centos6以前版本:
servicerestartmysqld
centos7+:
systemctlmysqldrestart
3、停止命令
killallmysqld或killall-9mysqld
centos6以前版本:
servicestopmysqld
centos7+:
systemctlmysqldstop
2.4数据库主机配置及运行情况
2.4.1数据库主机硬件配置及操作系统
服务器名称
操作系统
cpu核数
内存
硬盘空间
MySQL数据库服务器
CentOS 7.2 64bit
8
16G
1T
2.4.2数据库主机资源使用情况
服务器名称
cpu使用率
内存使用率
硬盘空间使用率
MySQL数据库服务器
<50%
<80%
<60%
2.5现有使用数据库的功能模块
2.5.1当前技术开发架构
网站采用springboot2的开发框架,springboot2是基于Java语言的极速web开发框架,其核心设计目标是开发迅速、代码量少、学习简单、功能强大、轻量级、易扩展、Restful。
springboot2同时支持多数据源,并且可以针对这多个数据源配置独立的缓存、事务级别等。
2.5.2现有功能模块梳理
功能模块
功能模块说明
读写分离分析
商品管理
商品列表、添加新商品、商品分类、用户评论、商品回收站、商品批量上传、商品批量导出、商品自动上下架。
使用主数据库和从数据库
促销管理
红包类型、团购活动、专题活动、拼团活动、优惠活动、批发管理。
使用主数据库和从数据库
订单管理
订单列表、订单查询、合并订单、订单打印、缺货登记、添加订单、发货单列表、退货单列表。
使用主数据库和从数据库
广告管理
广告列表、广告位置。
使用主数据库和从数据库
报表统计
流量分析、客户统计、订单统计、销售明细、搜索引擎、销售排行、访问购买率。
使用从数据库
文章管理
文章分类、文章列表、文章自动发布、在线调查。
使用主数据库和从数据库
会员管理
会员列表、添加会员、会员等级、会员留言、充值和提现申请、资金管理。
使用主数据库
权限管理
管理员列表、管理员日志、角色管理、办事处列表、供货商列表。
使用主数据库
系统设置
商店设置、会员注册项设置、支付方式、配送方式、邮件服务器设置、地区列表。
使用主数据库
模块管理
模块选择、设置模块、库项目管理、语言项编辑、模块设置备份、邮件模板
使用主数据库
3数据库优化方案设计
3.1数据库配置优化
参数名称
当前设定值
参数说明
优化目的
long_query_time
1
记录到慢查询日志是SQL语句执行时间长
修改,目的对响应较慢SQL语句进行记录,便于分析
innodb_flush_log_at_trx_commit
2
事务提交类型,分别为:
0:
logbuffer将每秒一次地写入logfile中,并且logfile的flush(刷到磁盘)操作同时进行。
该模式下在事务提交的时候,不会主动触发写入磁盘的操作。
1:
每次事务提交时MySQL都会把logbuffer的数据写入logfile,并且flush(刷到磁盘)中去,该模式为系统默认。
2:
每次事务提交时MySQL都会把logbuffer的数据写入logfile,但是flush(刷到磁盘)操作并不会同时进行。
该模式下,MySQL会每秒执行一次flush(刷到磁盘)操作。
修改,该模式速度较快,也比0安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失。
max_allowed_packet
2048M
MySQL导出、入数据包大小限制。
新增,目的防止数据表数据越来越大导致备份或恢复失败。
3.2数据库主从架构设计
3.2.1网站当前数据库部署架构
1、当前数据库部署架构图
2、当前数据库部署架构存在问题及优化思路
通过当前数据库部署架构图可知,网站的数据库采用单台数据库。
当数据库出现故障时,整个网站系统无法正常使用,影响系统正常运行,即网站的数据库存在单点故障;而且数据库服务器资源无法满足增长的读写请求。
针对网站的数据库架构进行优化,优化思路是增加数据库数量,实现数据库主从架构,其优化的目的是为了防止数据库出现单点故障问题,提高数据库的处理能力,提高数据库的可靠性,为保证网站业务正常办理。
3.2.2数据库主从架构优势
1、什么是数据库主从架构
MySQL数据库主从架构,是通过主从复制方式,让一个数据库与其他的数据库同步,保持两个或多个数据库的数据一致。
2、数据库主从架构与单一数据库比较
项目
单一数据库
数据库主从架构
安装MySQL服务数量
1台
2台
应用连接数据库是否需要修改
不修改
不修改
开启主从同步
无
有
两台及两台以上数据库数据一致性的定期校验
无
有
系统运行监控服务器数量
1台
2台
主数据出现故障切换数据库
无
有
主数据出现故障后,能否快速保证系统正常运行?
不能快速
可以快速切换到备份数据库
主数据出现故障后,系统能否启用部分功能或全部?
不能
能
3、数据库主从架构优点
(1)数据分布:
MySQL复制通常不会对带宽造成很大的压力,并且可以随意的停止或开始复制,并在不同的地理位置来分布数据备份,例如不同的数据中心。
即使在不稳定的网络环境下,远程复制也是可以工作。
但如果为了保持很低的复制延迟,最好有一个稳定的、低延迟的连接。
(2)负载均衡:
通过MySQL复制可以将读操作分不到多个服务器上,实现对读密集型应用的优化,并且实现很方面,通过简单的代码修改就能实现基本的负载均衡。
对于小规模的应用,可以简单对机器,名做硬编码或使用DNS轮询。
当然也可以使用更复杂的方法,例如网络负载均衡这一类的标准负载均衡解决方案。
能够很好的将负载均衡分配到不同的MySQL服务器上。
(3)备份:
对于备份来说,复制是一项很有意义的技术补充,但复制既不是备份也不能取代备份。
(4)高可用性和故障切换:
复制能够补助应用程序避免MySQL单点失败,一个包含复制的设计良好的故障切换系统能显著缩短当机时间。
(5)MySQL升级测试:
这种做法比较普遍,使用一个更高版本的mysql作为备库,保证在升级全部实例前,查询能够在备库按照预期执行。
4、优化后的网站数据库部署架构
(1)数据库主从架构图
2、数据库主从架构说明
将主数据库与从数据库配置为主从同步模式,主数据库负责写入状态,从数据库将同步到主数据库刚写入的数据,保证从数据库与主数据库的数据一致。
优点是实时灾备,当主库出现问题,可以快速切换到从库提供服务;同时该架构容易扩展,比如实现读写分离,提高系统读写数据的性能,缓解数据库的压力,增加了数据库的处理能力。
3.2.3数据库主从架构实现原理
1、数据库主从实现架构图
2、数据库主从实现架构说明
(1)数据库主从实现整体有三个步骤
第一步:
主数据库将改变记录到二进制日志(binarylog)中(这些记录叫做二进制日志事件,binarylogevents);
第二步:
从数据库将主数据库的binarylogevents拷贝到它的中继日志(relaylog);
第三步:
从数据库重做中继日志中的事件,将改变反映它自己的数据。
(2)数据库主从架构具体实现过程
第一部分就是主数据库记录二进制日志。
在每个事务更新数据完成之前,主数据库在二日志记录这些改变。
MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。
在事件写入二进制日志完成后,主数据库通知存储引擎提交事务。
下一步就是从数据库将主数据库的binarylog拷贝到它自己的中继日志。
首先,从数据库开始一个工作线程——I/O线程。
I/O线程在主数据库上打开一个普通的连接,然后开始binlogdumpprocess。
Binlogdumpprocess从主数据库的二进制日志中读取事件,如果主数据库没有新的binarylog记录,它会睡眠并等待主数据库产生新的事件。
I/O线程将这些事件写入中继日志。
从数据库SQL线程(SQLthread)处理该过程的最后一步。
SQL线程从中继日志读取事件,并重放其中的事件而更新从数据库的数据,使其与主数据库中的数据一致。
只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。
此外,在主数据库中也有一个工作线程:
和其它MySQL的连接一样,从数据库在主数据库中打开一个连接也会使得主数据库开始一个线程。
复制过程有一个很重要的限制——复制在从数据库上是串行化的,也就是说主数据库上的并行更新操作不能在从数据库上并行操作。
3.2.4数据库主从架构搭建的硬件要求
1、数据库主机数量
主数据库和从数据库部署建议在不同的主机上,这样实现一主一从的架构,需要两台数据库主机,目的是主数据库出现故障时,切换到从数据库继续提高数据库服务。
2、CPU要求
CPU尽量选择64位CPU,这样才可以安装64位操作系统,有了64位操作系统才能利用好更大的内存,便于后期服务器硬件扩展。
3、内存要求
内存IO高于SSD的IO,SSD的IO高于磁盘IO,足够的内存满足读请求与写请求,在选择内存时,要虑数据库并发量、数据库热数据的大小等。
4、硬盘要求
数据库的数据硬盘最好与操作系统的系统硬盘分开,目的是提高数据库I/O性能和保证数据安全,原因是系统盘使用频率(I/O)较高,而且操作系统最容易出现问题,在修复或重新安装操作系统时,容易将系统盘数据丢失。
3.2.5主数据库搭建流程
1、修改MySQL数据库配置文件f,配置一台数据库为主数据库,在MySQL数据库配置文件添加MySQL服务器id标识和开启二进制日志(binarylog)功能,配置如下:
[mysqld]
log-bin=mysql-bin
server-id=1
参数说明如下:
参数名称
设定值
参数说明
server_id
1
binlog配置需要给MySQL服务器设置id用来标识MySQL服务器,每个MySQL服务器都有不一样的id。
log_bin
mysql-bin
启用binlog功能,并制定路径名称。
2、修改完配置文件后,重启MySQL数据库服务。
3、主数据库上创建复制账号,通过SQL语句创建,SQL语句如下:
mysql>GRANTREPLICATIONSLAVEON*.*to'replication'@'%'identifiedby'123456';
设定值
说明
replication
复制账户名。
123456
复制账号使用的密码。
*.*
复制账户分配权限,表示该账号对主数据库状态有查看权限,并可以从主服务器读取二进制日志。
4、查询主数据库状态,查看主数据库状态SQL语句为:
mysql>showmasterstatus;
比如如下结果如下:
记录主数据库的两个关键数值File和Position,两个字段说明如下:
字段名
说明
File
binlog的当前日志文件名。
Position
当前日志文件偏移量。
执行完此步骤后不要再操作主数据库,防止主服务器状态值变化。
5、通过MySQL的mysqldump命令进行对需要做主从同步的库进行备份,命令如下:
mysqldump-uroot-p--lock-tables-R-q--databases--extended-insert=true--default-character-set=utf8数据库名>数据库名.sql
mysqldump参数说明:
参数
说明
-u
指定数据库用户名。
-p
设置数据库用户名的密码,该示例未指定密码,是执行命令后提示用户输入密码。
--lock-tables
在dump表之前锁表。
-R
导出存储过程和自定义函数
-q
不缓冲查询,直接导出到标准输出。
--databases
输入后的所有参数都作为数据库名。
--extended-insert
表示长INSERT,多row在一起批量INSERT。
--default-character-set
指定默认字符集。
6、将mysqldump备份出来的备份文件“数据库名.sql”复制到从数据库服务器上。
3.2.6从数据库搭建流程
1、修改MySQL从数据库配置文件f,增加server-id参数,配置参数如下:
[mysqld]
server-id=2
参数说明如下:
参数名称
设定值
参数说明
server_id
1
server-id的值必须是唯一的,不能和主数据库的配置相同,如果有多个从数据库服务器,每个从数据库服务器必须有自己唯一的server-id值。
2、从数据库配置修改完后,使用--skip-slave-start选项启动从数据库,这样不会立即启动从数据库服务上的复制进程,方便我们对从数据库的服务进行进一步的配置,操作命令:
mysql>mysqld_safe--defaults-file=/etc/f--skip-slave-start
注:
此示例的f路径只做参考,具体实施时按实际路径填写。
3、对从数据库做相应设置,指定复制使用的用户、密码,主数据库服务器的IP、端口,以及开始执行复制的日志文件和位置等,执行SQL语句如下:
mysql>CHANGEMASTERTOMASTER_HOST='192.168.xxx.xxx',MASTER_PORT=3306,MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=308;
SQL语句参数说明:
参数名
说明
MASTER_HOST
主数据库IP地址,192.168.xxx.xxx只是示例,具体实施时按实际情况填写。
MASTER_PORT
主数据库端口。
MASTER_USER
主数据库配置主从复制账号。
MASTER_LOG_FILE
主数据库备份前记录的binlog的日志文件名。
MASTER_LOG_POS
主数据库备份前记录的binlog的日志文件的偏移量。
4、启动从数据库复制功能,执行SQL语句如下:
mysql>startslave;
5、检查从服务器复制功能状态,执行SQL语句如下:
mysql>showslavestatus\G
**********************1.row*********************
Slave_IO_State:
Waitingformastertosendevent
Master_Host:
192.168.xxx.xxx//主服务器地址
Master_User:
'replication'//授权帐户名,尽量避免使用root
Master_Port:
3306//数据库端口,部分版本没有此行
Connect_Retry:
60
Master_Log_File:
mysql-bin.000004
Read_Master_Log_Pos:
600//#同步读取二进制日志的位置,大于等于Exec_Master_Log_Pos
Relay_Log_File:
relay-bin.000003
Relay_Log_Pos:
251
Relay_Master_Log_File:
mysql-bin.000004
Slave_IO_Running:
Yes//此状态必须YES
Slave_SQL_Running:
Yes//此状态必须YES
注:
Slave_IO及Slave_SQL进程必须正常运行,Slave_IO_Running和Slave_SQL_Running显示为“YES”状态,如果有错误,Last_IO_Error或Last_SQL_Error显示相对应的错误信息。
3.2.7主从同步测试流程
1、主数据库创建一张新表。
2、新表插入一条新的数据。
3、在从数据库查询新表是否存在,新添加的记录是否同步。
4、检查没问题,数据库主从搭建完成。
3.2.8数据库主从配置参数说明
1、主数据库配置说明
[mysqld]
#bin-log
#主服务器配置
#binlog配置需要给MySQL服务器设置id用来标识MySQL服务器,每个MySQL服务器都有不一样的id。
一般情况下可使用ip地址后一位
server_id=1
#开启binlog,MySQL主服务器都需要开启
log-bin=mysql-bin
#binlog存放到文件里面,index里面存放着binlog的文件名
log-bin-index=mysql-bin.index
#配置要生成binlog日志的库
binlog-do-db=databas_name
#配置不需要生成binlog日志的库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
2、从数据库配置说明
[mysqld]
#bin-log
#从服务器配置
#binlog配置需要给MySQL服务器设置id用来标识MySQL服
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 网站 MySQL 数据库 优化 方案 主从 架构 读写 分离