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

MySQL数据库运维管理基础知识
2013 年 7 月
金官丁
136 6166 8096
******@
数据库版本选择
数据库监控体系
数据库备份恢复策略
大数据量DML操作的技巧
巧
大表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变更技巧
主备切换变更
业务低谷期，备库上创建连接，设置
=0
备库上执行DDL变更命令
待备库执行完毕，切换数据服务的主备库关系
新备库上创建连接，设置 =0
新备库上执行DDL变更命令
变更完毕
数据库版本选择
数据库监控体系
数据库备份恢复策略
大数据量DML操作的技巧
大表DDL变更注意事项和变更技巧
在线数据库迁移的技巧
目录
数据迁移服务
数据库实例迁移
数据迁移服务
数据库迁移
目标数据库服务器安装、配置、初始化
目标数据库服务器加载完整备份与重新初始化
搭建目标数据库服务器与迁移数据库服务器之间的主从复制关系
暂停迁移数据库的业务应用访问
修改应用访问连接地址
目