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

MySQL数据库运维管理基础知识数据库版本选择数据库监控体系数据库备份恢复策略大数据量DML操作的技巧DDL变更注意事项和变更技巧在线数据库迁移的技巧目录数据库版本选择MySQL数据库分支数据库版本选择MySQL数据库版本种类版本号小于5.0：MySQL3.XMySQL4.X等MySQL5.0.XMySQL5.1.X+Build-inInnoDB存储引擎MySQL5.1.X+Plug-inInnoDB存储引擎MySQL5.5.XMySQL5.6.XMySQLCluster6.2.X和MySQLCluster6.3.XMySQLCluster7.0.XMySQLCluster7.1.XMySQLCluster7.2.XMySQLCluster7.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年主攻SQLServer2005年-至今主攻MySQL2008年-2010年至少帮助过3000名不同技术人员解答疑难问题2010年-2012年培养“门生”超过30名，现分布各大互联网公司2012年-至今，以独立顾问身份，负责惠普-中国联通U-Cloud项目的数据库平台DaaS的架构设计、研发和实施曾加盟阿里巴巴，推动数据库平台转型MySQL，组建MySQL团队、规范、标准、流程等，参与研发的项目超过50个开心农场早期员工，日活跃用户数超过5000万，负责技术研发部现任上海热璞网络科技有限公司的CTO兼创始人业务范围：数据库服务数据迁移运维服务整体解决方案软件开发企业培训服务宗旨：解决企业当下的疑难帮助企业规避未来可能发生的故障借助技术和经验为企业降低成本企业文化：诚信是立足之本，口碑是客户认可的标志，技术、服务、信誉是核心竞争力上海热璞网络科技有限公司