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

MySQL数据库运维管理基础知识 金官丁 136 6166 8096 jinguanding@hotpu.cn 2013 年 7 月

目录 • 数据库版本选择 • 数据库监控体系 • 数据库备份恢复策略 • 大数据量DML操作的技巧 • DDL变更注意事项和变更技巧 • 在线数据库迁移的技巧

数据库版本选择 • MySQL数据库分支

数据库版本选择 • MySQL数据库版本种类 • 版本号小于 5.0：MySQL3.X MySQL4.X 等 • MySQL5.0.X • MySQL5.1.X+Build-in InnoDB存储引擎 • MySQL5.1.X+Plug-in InnoDB存储引擎 • MySQL5.5.X • MySQL5.6.X • MySQL Cluster 6.2.X 和 MySQL Cluster 6.3.X • MySQL Cluster 7.0.X • MySQL Cluster 7.1.X • MySQL Cluster 7.2.X • MySQL Cluster 7.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、内存、网卡流量、数据增长 • 数据库的日志 • 主机系统的日志

数据库备份恢复策略 • 数据故障恢复指标 • 业务中断小于10秒钟，则在线热备 • 业务中断小于5分钟，则在线延迟恢复热备 • 业务中断小于2小时，则完整备份+二进制日志备份 • 数据库备份恢复规划 • 双主在线热备 • 双主在线热备+延迟恢复备库 • 每天完整备份+每小时二进制日志备份 • 数据库备份恢复软件组合 • 开源工具：mysqldump • 开源工具：xtrabackup 或收费工具：InnoDB hot backup

大数据量DML操作的技巧 • 大数据量INSERT • 大数据量UPDATE • 大数据量DELETE

大数据量DML操作的技巧 • 大数据量INSERT • INSERT INTO … VALUES(),(),()…. • LOAD DATA …. • 写入临时表，再INSERT … SELECT … 或循环判断写入

大数据量DML操作的技巧 • 大数据量UPDATE • SELECT 唯一字段，变更字段 • 查询结果集写入临时表 • 给临时表创建组合索引 • 循环临时表的每条记录 • 依据临时表的唯一字段值，更新目标表的相关字段值

大数据量DML操作的技巧 • 大数据量DELETE • 若是定期且特殊规则删除大批量的数据，则建议采用分区表 • 若是定期且无特殊规则删除大批量的数据，则分区表+UPDATE • 若是上述2种方式无法使用，则可以主备轮流删除+数据服务切换 • 若是上述3种方式无法使用 • 调整参数…，直接执行DELETE批量语句 • 先查询出要删除数据的唯一标识写入临时表，然后游标循环删除

大表DDL变更注意事项和变更技巧 • 变更注意事项 • 表级锁 • InnoDB存储引擎的数据字典锁 • DDL加速技巧

大表DDL变更注意事项和变更技巧 • 变更注意事项 • 增加字段必须要有默认值 • 例如: • ALTER TABLE tmp_test ADD COLUMN gmt_CreateDate TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00’; • 增加的字段不能指定字段顺序，必须默认尾部增加模式 • 例如：增加一个字段到表对象的头部 • ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0 FIRST; • 这是我们强烈反对的行为，可能导致需要更多时间用于数据重整， • 以及对复制同步可能造成数据错乱，应该写成： • ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0;

大表DDL变更注意事项和变更技巧 • 变更注意事项 • 建议表设计都设置为NOT NULL 且默认值 • 非NULL且有默认值的字段属性，有利于提高数据比对检索的性能， • 更加有利于索引结构的优化 • 同一表增加多个字段，不分多条语句，而应只用一条变更语句 • 例如： • ALTER TABLE tmp_test ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0; • ALTER TABLE tmp_test ADD COLUMN gmt_CreateDate TIMESTAMP NOT NULL DEFAULT ‘0000-00-00’; • 应该改写成： • ALTER TABLE tmp_test • ADD COLUMN age TINYINT UNSIGNED NOT NULL DEFAULT 0, • ADD COLUMN gmt_CreateDate TIMESTAMP NOT NULL DEFAULT '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变更技巧 • 大表删除 • 创建被删除表的硬链接 • 执行DROP TABLE 语句 • 业务低谷期删除磁盘上真实的数据文件

DDL变更技巧 • 主备切换变更 • 业务低谷期，备库上创建连接，设置 • SESSION.sql_log_bin=0 • 备库上执行DDL变更命令 • 待备库执行完毕，切换数据服务的主备库关系 • 新备库上创建连接，设置 SESSION.sql_log_bin=0 • 新备库上执行DDL变更命令 • 变更完毕

数据迁移服务 • 数据库实例迁移

数据迁移服务 • 数据库迁移 • 目标数据库服务器安装、配置、初始化 • 目标数据库服务器加载完整备份与重新初始化 • 搭建目标数据库服务器与迁移数据库服务器之间的主从复制关系 • 暂停迁移数据库的业务应用访问 • 修改应用访问连接地址 • 目标数据库服务器提供数据服务 • 断开目标数据库服务器与迁移数据库服务器之间的主从复制关系 • 特殊技术删除迁移数据库服务器上多余的数据库

数据迁移服务 • 异构数据库迁移到MySQL数据库 • 业务模型分析 • 数据容量预估与拆分规则 • 目标业务模型的POC • 数据库结构设计与优化 • 模拟生产环境的性能测试 • 全量数据导出与加载到MySQL数据库 • 实时同步原异构数据库的变更到MySQL数据库 • 数据读服务切换到MySQL数据库 • 数据写服务切换到MySQL数据库 • 数据服务质量检测与优化调整

个人介绍 • 2003年-2006年主攻SQL Server • 2005年-至今主攻MySQL • 2008年-2010年至少帮助过3000名不同技术人员解答疑难问题 • 2010年-2012年培养 “门生”超过30名，现分布各大互联网公司 • 2012年-至今，以独立顾问身份，负责惠普-中国联通U-Cloud • 项目的数据库平台DaaS的架构设计、研发和实施 • 曾加盟阿里巴巴，推动数据库平台转型MySQL，组建MySQL • 团队、规范、标准、流程等，参与研发的项目超过50个 • 开心农场早期员工，日活跃用户数超过5000万，负责技术研发部 • 现任上海热璞网络科技有限公司的CTO兼创始人

上海热璞网络科技有限公司 业务范围：数据库服务数据迁移运维服务整体解决方案软件开发企业培训服务宗旨：解决企业当下的疑难帮助企业规避未来可能发生的故障借助技术和经验为企业降低成本企业文化：诚信是立足之本，口碑是客户认可的标志，技术、服务、信誉是核心竞争力

财务管理渗透于业务活动 信息系统支撑流程化管理谢谢！李福申中国联通集团副总裁 2011年4月27日

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