MySQL数据库运维管理基础知识

MySQL数据库运维管理基础知识 2013年7月 金官inguanding 数据库版本选择数据库监控体系数据库备份恢复策略大数据量DML操作的技巧DDL变更注意事项和变更技巧在线数据库迁移的技巧 目录 数据库版本选择 MySQL数据库分支 数据库版本选择 MySQL数据库版本种类 版本号小于5 0 MySQL3 XMySQL4 X等MySQL5 0 XMySQL5 1 X Build inInnoDB存储引擎MySQL5 1 X Plug inInnoDB存储引擎MySQL5 5 XMySQL5 6 XMySQLCluster6 2 X和MySQLCluster6 3 XMySQLCluster7 0 XMySQLCluster7 1 XMySQLCluster7 2 XMySQLCluster7 3 X 数据库版本选择 挑选版本的技巧 大版本发布 至少要大于或等于18个版本号最新版本号倒退1 2个版本号假设选择版本号为MySQL5 5 31看版本号MySQL5 5 31修复的BUG数和新增功能看版本号MySQL5 5 32修复的BUG数看版本号MySQL5 5 30修复的BUG数 数据库版本选择 版本定型的过程 内部的功能测试 以用到的MySQL功能为测试基准针对用到的MySQL功能做基准的压力测试内部开发环境试用内部产品的功能和性能测试环境试用稳定性要求不高且压力较小的生产环境试用稳定性要求高且压力中等的生产环境试用生产环境正式试用 数据库版本选择数据库监控体系数据库备份恢复策略大数据量DML操作的技巧大表DDL变更注意事项和变更技巧在线数据库迁移的技巧 目录 数据库监控体系 数据库服务是否正常数据库的性能数据库的逻辑IO和物理IO数据库的业务量数据库的数据与二进制日志的增长量主机的CPU IO 内存 网卡流量 数据增长数据库的日志主机系统的日志 数据库版本选择数据库监控体系数据库备份恢复策略大数据量DML操作的技巧大表DDL变更注意事项和变更技巧在线数据库迁移的技巧 目录 数据库备份恢复策略 数据故障恢复指标业务中断少于10秒钟 则在线热备业务中断少于5分钟 则在线延迟恢复热备业务中断小于2小时 则完整备份 二进制日志备份数据库备份恢复规划双主在线热备双主在线热备 延迟恢复备库每天完整备份 每小时二进制日志备份数据库备份恢复软件组合开源工具 mysqldump开源工具 xtrabackup或收费工具 InnoDBhotbackup 数据库版本选择数据库监控体系数据库备份恢复策略大数据量DML操作的技巧大表DDL变更注意事项和变更技巧在线数据库迁移的技巧 目录 大数据量DML操作的技巧 大数据量INSERT大数据量UPDATE大数据量DELETE 大数据量DML操作的技巧 大数据量INSERT INSERTINTO VALUES LOADDATA 写入临时表 再INSERT SELECT 大数据量DML操作的技巧 大数据量UPDATE SELECT唯一字段 变更字段查询结果集写入临时表给临时表创建组合索引循环临时表的每条记录依据临时表的唯一字段值 更新目标表的相关字段值 大数据量DML操作的技巧 大数据量DELETE 若是定期且特殊规则删除大批量的数据 则建议采用分区表若是定期且无特殊规则删除大批量的数据 则分区表 UPDATE若是上述2种方式无法使用 则可以主备轮流删除 数据服务切换若是上述3种方式无法使用调整参数 直接执行DELETE批量语句先查询出要删除数据的唯一标识写入临时表 然后游标循环删除 数据库版本选择数据库监控体系数据库备份恢复策略大数据量DML操作的技巧大表DDL变更注意事项和变更技巧在线数据库迁移的技巧 目录 大表DDL变更注意事项和变更技巧 变更注意事项表级别锁InnoDB存储引擎的数据字典锁DDL加速技巧 大表DDL变更注意事项和变更技巧 变更注意事项 增加字段必须要有默认值例如 ALTERTABLEtmp testADDCOLUMNgmt CreateDateTIMESTAMPNOTNULLDEFAULT 0000 00 0000 00 00 增加的字段不能指定字段顺序 必须默认尾部增加模式例如 增加一个字段到表对象的头部ALTERTABLEtmp testADDCOLUMNageTINYINTUNSIGNEDNOTNULLDEFAULT0FIRST 这是我们强烈反对的行为 可能导致需要更多时间用于数据重整 以及对复制可能造成数据错乱 应该写成 ALTERTABLEtmp testADDCOLUMNageTINYINTUNSIGNEDNOTNULLDEFAULT0 大表DDL变更注意事项和变更技巧 变更注意事项 建议表设计都设置为NOTNULL且默认值非NULL且有默认值的字段属性 有利于提高数据比对检索的性能 更加有利于索引结构的优化同一表增加多个字段 不分多条语句 而应只用一条变更语句例如 ALTERTABLEtmp testADDCOLUMNageTINYINTUNSIGNEDNOTNULLDEFAULT0 ALTERTABLEtmp testADDCOLUMNgmt CreateDateTIMESTAMPNOTNULLDEFAULT 0000 00 00 应该改写成 ALTERTABLEtmp testADDCOLUMNageTINYINTUNSIGNEDNOTNULLDEFAULT0 ADDCOLUMNgmt CreateDateTIMESTAMPNOTNULLDEFAULT 0000 00 00 大表DDL变更注意事项和变更技巧 表级别锁InnoDB存储引擎的数据字典锁 DDL变更注意事项 DDL加速技巧 DDL操作的过程 申请表对象加锁 只允许读不允许更新 复制一份变更对象表的结构代码 原数据结构基础之上修改 新建一组临时文件 MyISAM引擎和InnoDB引擎的文件有所不同 分别为 frm MYD MYI frm ibd 变更对象表中的数据写入到临时表中 速度读写完毕的情况下 完全锁定被变更的表 并且删除被变更表 对临时文件执行RENAME操作 刷新数据字典信息 释放锁 DDL变更注意事项 DDL加速技巧 调整的SESSION变量 建议增加SESSION级别的sort buffer size值 以加速DDL过程需要做的排序操作 建议增加SESSION级别的read buffer size值 增加顺序读的速度 建议增加SESSION级别的tmp table size和max heap table size值 增加内存缓存数据的能力 若是存在InnoDB引擎转MyISAM的操作 建议在线增加key buffer size的值 增加索引数据的缓存能力 DDL变更技巧 大表删除 创建被删除表的软链接执行DROPTABLE语句业务低谷期删除磁盘上真实的数据文件 DDL变更技巧 主备切换变更 业务低谷期 备库上创建连接 设置SESSION sql log bin 0备库上执行DDL变更命令待备库执行完毕 切换数据服务的主备库关系新备库上创建连接 设置SESSION sql log bin 0新备库上执行DDL变更命令变更完毕 数据库版本选择数据库监控体系数据库备份恢复策略大数据量DML操作的技巧大表DDL变更注意事项和变更技巧在线数据库迁移的技巧 目录 数据迁移服务 数据库实例迁移 数据迁移服务 数据库迁移 目标数据库服务器安装 配置 初始化目标数据库服务器加载完整备份与重新初始化搭建目标数据库服务器与迁移数据库服务器之间的主从复制关系暂停迁移数据库的业务应用访问修改应用访问连接地址目标数据库服务器提供数据服务断开目标数据库服务器与迁移数据库服务器之间的主从复制关系特殊技术删除迁移数据库服务器上多余的数据库 数据迁移服务 异构数据库迁移到MySQL数据库 业务模型分析数据容量预估与拆分规则目标业务模型的POC数据库结构设计与优化模拟生产环境的性能测试全量数据导出与加载到MySQL数据库实时同步原异构数据库的变更到MySQL数据库数据读服务切换到MySQL数据库数据写服务切换到MySQL数据库数据服务质量检测与优化调整 个人介绍 2003年 2006年主攻SQLServer2005年 至今主攻MySQL2008年 2010年至少帮助过3000名不同技术人员解答疑难问题2010年 2012年培养 门生 超过30名 现分布各大互联网公司2012年 至今 以独立顾问身份 负责惠普 中国联通U Cloud项目的数据库平台DaaS的架构设计 研发和实施曾加盟阿里巴巴 推动数据库平台转型MySQL 组建MySQL团队 规范 标准 流程等 参与研发的项目超过50个开心农场早期员工 日活跃用户数超过5000万 负责技术研发部现任上海热璞