- 数据库是不是都是innodb引擎表 -> 备份方式,热备or冷备
- 数据量大小 -> 逻辑备份or物理备份,全量or增量
- 数据库本地磁盘空间十分充足 -> 备份到本地or远程
- 需要多块恢复 -> 备份频率 小时or天
- 硬件故障(如磁盘损坏)
- 人为删除(如误删除数据、被黑)
- 业务回滚(如游戏bug需要回档)
- 正常需求(如部署镜像库、查看历史某时刻数据)
- 完整的数据库操作日志(binlog)
- 最新一次备份 + binlog恢复到故障时间点(适用于各种数据丢失场景)
- 挖掘最后一次备份到故障点之间的binlog获取相关SQL语句,构造反转SQL语句并应用到数据库(只是用于记录丢失,且binlog必须是row格式)
- 将数据库恢复到指定时间点
需求:将被删除的数据还原
恢复前提:完整的数据库操作日志(binlog)
# 首先需要找到binlog里的信息
# 找出sql语句,然后写出反转sql语句
前提:备份 + 备份以来完整binlog
- 恢复是已经非常苦逼的差事,尽量避免做。我们要做数据卫士而不是救火队员。(线上应该严格把控权限,数据变更操作应事先测试,操作时做好备份)
- 有效备份(+binlog)是重中之重,对数据库定期备份是必须的
- 备份是一切数据恢复的基础
- 安装方式,包安装?二进制包安装?源码安装?
- 路径配置,参数配置(尽量模板化、标准化)
- 一个实例多个库 or 多个实例单个库?
- 初始化实例,编辑配置文件并启动
- 下载MySQL源码安装包
- Cmake配置MySQL编译选项,可以定制需要安装的功能
- 初始化实例,编辑配置文件并启动
- 关闭MySQL5.5实例,修改部分参数,使用MySQL5.6软件启动
- 编辑多个配置文件,初始化多个实例
MySQL线上安装小结
- 根据需求选择合适的版本以及分支,建议使用或升级到较高版本5.5或5.6
- 如果需要定制MySQL功能的话,可以考虑编译安装,否则的话建议使用二进制包安装,比较省事
- 根据机器配置选择部署多个MySQL实例还是单个实例,机器配置非常好的话,建议部署多实例
MySQL主从复制用途
- 实时灾备,用于故障切换
- 读写分离,提供查询服务
MySQL主从复制部署
MySQL复制存在的问题
- 主机宕机后,数据可能丢失
- 从库只有一个sql thread,主库写压力大,复制很可能延时
- 5.5集成到MySQL,以插件形式存在,需要单独安装
- 确保事务提交后binlog至少传输到一个从库
- 不保证从库应用完这个事务的binlog
- 性能有一定的降低,响应时间更长
- 网络异常或从库宕机,卡住主库,直到超时或从库恢复
配置MySQL半同步复制
配置MySQL并行复制
- 库级别并行应用binlog,同一个数据库更改还是串行的(5.7版并行复制基于事务组)
- MySQL主从复制是MySQL高可用性、高性能(负载均衡)的基础
- 简单、灵活,部署方式多样,可以根据不同业务场景部署不同复制结构
- MySQL主从复制目前也存在一些问题,可以根据需要部署复制增强功能来解决问题
- 复制过程中应该时刻监控复制状态,复制出错或延时可能给系统造成影响
- MySQL复制是MySQL数据库工程师必知必会的一项基本技能
- 导数据、数据修改、表结构变更
- 数据库选型部署、设计、监控、备份、优化等
- 数据最终形式(csv、sql文本 还是直接导入某库中)
- 导出为csv格式需要file权限,而且只能数据库本地导
- 避免对业务造成影响,尽量在镜像库做
- 开事务做,修改完检查好了再提交
- 避免一次 修改大量数据,可以分批修改
- 可以避免负载过高,可以限速
- 只给符合需求的最低权限
- 避免给应用账号super权限
问题处理(数据库慢?)
- 日常工作比较简单,但是任何一个操作都可能影响线上服务
- 结合不同环境,不同要求选择最合适的方法处理
- 日常工作应该求稳不求快,保障线上稳定是DBA的最大责任
- 不同服务器之间的配置、性能不一样
- 不同业务场景对数据的需求不一样
- MySQL的默认参数只是个参考值,并不适合所有的应用场合
优化之前我们需要知道什么
- 数据写入cache既返回,数据异步的从cache刷入存储介质
- 数据同时写入cache和存储介质才返回写入成功
- 生产环境里一般不太会用裸设备,通常会使用RAID卡对一块盘或多块盘做RAID
- RAID卡会预留一块内存,来保证数据高效存储与读取
RAID如何保证数据安全
- BBU保证在WB策略下,即使服务器发生掉电或者宕机,也能够将缓存数据写入到磁盘,从而保证数据的安全
MySQL有哪些注意事项
- 实时监控数据库服务器的负载情况
- 实时监控MySQL内部状态值
- 为什么要调整MySQL的参数
- MySQL是通用数据库,但业务是多变的,默认参数无法满足所有业务需求
- MySQL内部一些参数是在MySQL一些很老的版本时候做的,可能之前是做限流和保护用的,但随着机器性能的提高,这些保护类的参数可能会成为性能瓶颈
- 合理利用索引对MySQL查询性能至关重要
- 适当的调整参数也能提升查询性能
- InnoDB存储引擎自己维护一块内存区域完成新老数据的替换
- 内存越大越能缓存更多的数据
- innoDB内部并发控制参数,设置为0代表不做控制
- 如果并发请求较多,参数设置较小,后进来的请求将会排队
- 表结构设计上使用自增字段作为表的主键
- 只对合适的字段加索引,索引太多影响写入性能
- 监控服务器磁盘IO情况,如果写延迟较大则需要扩容
- 选择正确的MySQL版本,合理设置参数
哪些参数有助于提高写入性能
主要影响MySQL写性能的两个参数
- 控制InnoDB事务的刷新方式,一共有三个值:0,1,2
- N=0 - 每隔一秒,把事务日志缓存区的数据写到日志文件中,以及把日志文件的数据刷新到磁盘上(高效,但不安全)
- N=1 - 每个事务提交时候,把事务日志从缓存区写到日志文件中,并且刷新日志文件的数据到磁盘上,优先使用此模式保障数据安全性(低效,非常安全)
- N=2 - 每事务提交的时候,把事务日志数据从缓存区写到日志文件中;每隔一秒,但不一定刷新到磁盘上,而是取决于操作系统的调度(高效,但不安全)
- 控制每次写入Binlog,是否都需要进行一次持久化
- 事务要和Binlog保证一致性
- 换算到数据库每秒只能执行50~60个事务
- MariaDB提出改进,即使这两个参数都是1也能做到合并效果,性能得到了大幅提高。
- 官方吸收了MariaDB的思想,并在此基础上进行了改进,性能再次得到了提高
- 官方在MySQL5.6版本之后才做了这个优化
- Redo log用在数据库崩溃会的故障恢复
- 如果写入频繁导致Redo log里对应的最老的数据脏页还没有刷新到磁盘,此时数据库将卡住,强制刷新脏页到磁盘
- MySQL默认配置两个文件才10M,非常容易写满,生产环境中应适当调整大小。
- InnoDB每次刷多少个脏页,决定InnoDB存储引擎的吞吐能力。
- 在SSD等高性能存储介质下,应该提高该参数以提高数据库的性能。
- 顺序读写 VS 随机读写
- 随机请求性能远小于顺序请求
尽可能多的随机请求合并为顺序请求才是提高数据库性能的关键
- Insert Buffer只对二级索引且非唯一索引有效
- 服务器配置要合理(内核版本、磁盘调度策略、RAID卡缓存)
- 完善的监控系统,提前发现问题
- 数据库版本要跟上,不要太新,也不要太老
- 查询优化:索引优化为主,参数优化为辅
- 写入优化:业务优化为主,参数优化为辅