MySQL数据库准入规范

当前版本

  • 版本: V0.6
  • MySQL数据库准入规范由DBA起草,经rd\op\tc评审通过;

1. MySQL 准入的背景和目的

公司在2007年开始成立专门的DBA团队,负责MySQL和Oracle数据库的运维,并逐步从RD/OP手中接管回归业务数据库。在接手过程中,由于之前RD/OP对数据库设计和使用的不合理、不规范,造成数据库性能不高,安全性差,更谈不上易扩展易维护了,从而导致DBA运维成本明显提高。

2011年底OP部门出台了《OP准入规范》,并且2012年DBA将重点平台化,自动化,规范化,所以DBA组决定制定一部有关MySQL数据库使用/运维准入的文档。本文档面向RD、OP以及DBA,里面涉及数据库的设计、中间层dbproxy的设计、网络硬件的选择,SQL的编写,前端程序的应用等有关MySQL数据库的相关准入条目。

本文档目的在于,各个产品线数据库集群都符合相关的准入条目,从而使MySQL数据库的使用规范化,标准化,简单化,安全化,规避不合理的使用数据库造成业务性能下降和安全漏洞。同时使RD和OP在程序开发和上线过程中有据可依,这样DBA的日常运维工作也能更轻松,简单。

本文档中的2.1和2.2章节的准入标准同样适用于DDBS,其他的可根据具体情况DDBS自行定义。

本文档适用于OLTP业务的MySQL数据库和DBProxy,不适用于OLAP业务;另外对于SequentDB、MongoDB、Infobright、REDIS、NDB等存储技术不做维护和准入规范。

另注,在实际操作中,业务与本文档产生冲突时需要由RD和DBA共同确定具体的准入项取值或者适用与否,并由相关的项目经理审批后方可上线;文档中的措辞分为必须、禁止、推荐和不推荐四种,分为对应不同的审批级别和评审原则。

另注,本文档施行后,只针对新增数据库集群适用,现有系统可以不做改动。对于有争议或者产生问题的准入项,由DBA和RD共同商榷后做出相应的调整。本文档每半年进行重新审核调整。

2. MySQL 数据库准入项

2.1 数据库设计

2.1.1 数据库设计

数据库的名称必须控制在32个字符以内

数据库的名称推荐以产品线在noah运维树中的名称或缩写开头,后跟子系统名称或缩写,以“_”分隔,且只能使用字母、数字和下划线,一律采用小写格式,例如iknow产品线的qb子系统数据库的名称是“iknow_qb”

一般分库名称命名格式是“库通配名_编号”,编号从“0”开始递增,比如“iknow_qb_0”

以时间进行分库的名称格式是“库通配名_时间”,时间是库中数据的时间四-六位数字缩写,比如iknow_qb在2012年5月份的库为“iknow_qb_1205”

在单个mysqld实例中,必须首先考虑分表策略,如需要再考虑分库策略

创建数据库时必须显式指定字符集,并且字符集只能是utf8或者gbk

创建数据库SQL举例:

create database iknow_qb default character set utf8;

create database iknow_qb_0000 default character set gbk;

2.1.2 表结构设计

表和列的名称必须控制在32个字符以内

表和列的名称不能使用系统保留字和特殊字符,只能使用字母、数字和下划线,一律采用小写格式并以字母开头

表名称推荐以子系统名称或缩写开头,后跟功能名称或缩写,以“_”分隔,如表名“qb_tblreply”

一般分表名称命名格式是“表通配名_编号”,编号从“0”开始递增,比如“qb_tblreply_0”

以时间进行分表的名称格式是“表通配名_时间”,时间是表中数据的时间四-六位数字缩写,比如qb_tblreply在2012年5月份的表名为“qb_tblreply_1205”

列名称使用具体字段代表的功能名称或者缩写,若多个词需用“_”分隔,如列名“qid”

创建表时必须显式指定字符集,并且字符集必须与数据库字符集相同,utf8或者gbk

创建表时必须显式指定表存储引擎类型,可以是InnoDB、MyISAM、Memory、SequentDB或者InfoBright

当需要使用除InnoDB/MyISAM/Memory以外的存储引擎时,必须通过DBA审核才能在生产环境中使用

Memory存储引擎不能与其他存储引擎的表共用同一个MySQL数据库实例

不推荐使用enum,set,blob,text等类型

表中的自增列(auto_increment属性)推荐使用bigint类型

业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型

业务中IP地址字段推荐使用int类型

业务活跃的大表中必须有行数据的创建时间字段create_time和最后更新时间字段update_time

表中所有字段必须都是NOT NULL属性,业务可以根据需要定义DEFAULT值

2.1.3 索引设计

InnoDB类型表必须要有主键列,且主键必须是单列字段,使用有序递增的整数值做主键

表的主键列值禁止被更新,可以进行删除操作

主键的名称以“pk_”开头,唯一键以“uk_”开头,普通索引以“idx_”开头,一律使用小写格式,以表名/字段的名称或缩写作为后缀,如有表“qb_tblreply”,那么它的主键是“pk_qb_tblreply”,唯一键是“uk_tblreply_qid”,cid列上的普通索引是“idx_tblreply_cid”

InnoDB和MyISAM存储引擎表,索引类型必须为BTREE;MEMORY表可以根据需要选择HASH或者BTREE类型索引;InfoBright和SequentDB可以根据需要进行选择

单个索引中每个索引记录的长度不能超过64KB

单个表上的索引个数不能超过7个

2.1.4 分区表

分区表的分区字段(partition-key)必须有索引,或者是组合索引的首列

单个分区表中的分区(包括子分区)个数不能超过1024

上线前RD或者DBA必须指定分区表的创建、清理策略

2.1.5 中间表和备份表

中间表用于保留中间结果集,名称以“tmp_”开头,中间是源表名称或缩写,以创建日期为后缀,以“_”分隔,如中间表“tmp _qb_tblreply_20120320”

备份表用于备份或抓取源表快照,名称以“bak_”开头,中间是源表名称或缩写,以创建日期为后缀,以“_”分隔,如备份表“bak _qb_tblreply_20120320”

中间表和备份表仅由DBA在运维时使用并定期删除,禁止前端应用程序创建并访问

2.1.6 分库分表

采用分库策略的,库的数量不能超过1024

采用分表策略的,表的数量不能超过4096

分库分表的字段上必须有索引,或者是组合索引的首列

2.1.7 数据库账号

线上程序账号必须以读写权限进行区分,读写账号以“_w”后缀,只读账号以“_r”后缀

线上程序账号与数据库db的关系只能是1对1,或者多个模块功能对应1个db

线上程序账号以产品线名称为前缀,中间是db名称或缩写,以读写标志为后缀,各个部分以“_”分隔,例如可对iknow_qb库进行读写的账号是“iknow_qb_w”

线下程序账号必须以“off”前缀,中间是db名称或缩写,以读写标志为后缀,各个部分以“_”分隔,例如可对iknow_qb库进行只读的线下程序账号是“off_iknow_qb_r”

临时程序账号必须以“tmp”前缀,中间是db名称或缩写,以读写标志为后缀,各个部分以“_”分隔,例如iknow_qb库的临时只读账号是“tmp_iknow_qb_r”

个人账号以申请人的邮箱前缀为账号名称,且个人账号只能有只读权限,如分配给张三(zhangsan01@mars.com)的个人账号是“zhangsan01”

当多个模块程序访问同一个数据库时,账号名称中间以“库名_模块名”为标识,其他参考如上说明

数据库账号的授权需要具体到授权服务器的IP,禁止使用机器名和%(handoff机器除外)

数据库中监控账号名称为“monitor”,主从同步账号名称为“mysqlsync”

新建数据库账号、授权或修改账号密码时必须使用grant语句

收回用户授权时必须使用revoke语句

清理用户时须先使用set sql_log_bin=0,再执行drop user语句

禁止使用insert、delete或update语句进行用户权限的设置,清理和修改操作

禁止使用old_password进行加密,数据库参数old_passwords必须置为OFF

同一数据库集群中提供线上服务的主从数据库上的授权必须保持一致

注:这里的同一集群是指提供同一产品线服务的主从数据库

访问数据库的前端机器下线后,RD或者OP应通知DBA进行权限回收

2.1.8 字符集

数据库和表的字符集必须一致,且所有表的字符集必须一致,只能是utf8或者gbk

主、从数据库的字符集必须一致

前端程序字符集或者环境变量中的字符集,与数据库、表的字符集必须一致

前端程序的编译参数中禁止使用latin、gb2312、big5等,只能选用utf8、gbk或者binary

2.1.9 数据库连接

前端程序连接数据库或者DBProxy,必须要有连接超时(最大1秒)、空闲超时(最大30秒)和失败重连机制,且失败重连必须有间隔时间

前端应用程序配置或连接多个数据库或者DBProxy IP,用于连接失败重连和程序端轮循访问

程序端日志必须记录连接数据库的标准MySQL错误号以及所连接的数据库信息(比如IP和PORT,数据库用户名),用于DBA排查错误

对于有连接池的前端程序,必须根据业务需要配置初始、最小、最大连接数,超时时间(最大1秒)以及连接回收机制(最大3600秒)

程序端使用的连接数据库的so库包以及客户端数据库版本,必须与线上数据库服务器的版本兼容

2.1.10 其他

不推荐使用外键,临时表,视图,自定义函数,存储过程以及触发器

上线前RD或者DBA必须指定表数据保留和清理方案,并编写程序实现

SSD硬盘上,单表数据行数不能超过5000万或者存储空间不得大于30GB

SAS硬盘上,单表数据行数不能超过2000万或者存储空间不得大于15GB

上线前DBA必须根据3年内的业务访问量和数据增长量,给出库、表的扩展方案

在应用程序设计阶段,RD必须考虑并规避数据库SLA文档中允许的主从同步延迟对前端业务的影响

2.2 SQL编写

2.2.1 SELECT语句

SELECT语句必须指定具体字段名称,禁止写成“*”

SELECT语句禁止使用UNION,推荐使用UNION ALL,并且UNION子句个数限制在5个以内

2.2.2 DML语句

INSERT语句必须指定具体的字段名称,不要写成INSERT VALUES(……)形式

SQL语句在程序中传入的参数值类型必须与字段在数据库中的类型相同

2.2.3 多表联合查询

多表连接查询推荐使用别名,且SELECT列表中要用别名引用字段,数据库.表格式,如“select a.cid from iknow_qb. tblreply a where …”

生产系统中,单个查询中不推荐将3张表以上(包括3张表)做连接

生产系统中,强烈不推荐使用外关联,包括左外关联,右外关联和全外关联

在多表连接的查询中,驱动表须要选择结果集较小的表

禁止写成多层子查询嵌套的SQL语句,推荐改写成表顺序连接的格式

禁止在INSERT|UPDATE|DELETE|REPLACE语句中进行多表连接操作

2.2.4 事务

事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在2000,以及WHERE子句中IN列表的传参个数控制在2000

批量操作数据时,需要控制事务处理间隔时间,进行必要的sleep,具体值由DBA给出,并且程序必须有中断处理能力

对于有auto_increment属性字段的表的插入操作,并发需要控制在200以内

SQL级别/事务级别/主从数据库中的表存储引擎类型要一致,存储引擎混合使用会导致主从数据不一致或主从同步中断(但涉及InfoBright表的情况除外)

对于同步延迟不敏感的只读查询,必须放到从库上执行;对于同步延迟敏感的只读查询,可以放到主库上执行

前端程序中禁止使用set语句,包括set names、set sql_mode和set isolation_level等

2.2.5 表扫描方式

SELECT|UPDATE|DELETE|REPLACE要有WHERE子句,且WHERE子句的条件必需使用索引查找

生产数据库中强烈不推荐大表上发生全表扫描,但对于100行以下的静态表可以全表扫描

业务中大表全表扫描和全表导出(dump)推荐放在备份库或者线下读库中进行

WHERE 子句中禁止只使用全模糊的LIKE条件进行查找,必须有其他查询条件

WHERE子句中的索引列或组合索引前导列上不能使用函数

2.2.6 排序和分组

有distinct、order by和group by子句的查询,中间结果集限制10000行以内

对于大结果集(中间结果集超过10000行)的排序、分组放到程序端实现

2.2.7 数据导出导入

load data和mysqldump导出后需拆成多个小文件,再逐个执行数据库导入,每个小文件控制在1GB以内

load data|mysqldump以及其他导入导出操作必须显式指定字符集

2.2.8 cache的使用

业务上对某个字段频繁的重复写,推荐使用写缓存机制,如计数器的写入

2.2.9 其他

单个SQL语句的大小限制在5MB以内

生产数据库中SQL语句的中间结果集和最终结果集必须限制在1MB以内

生产数据库中SQL语句禁止使用提示,如force index,ignore index,straight_join,sql_no_cache等

禁止使用全文检索功能

禁止使用事件(EVENT)功能

程序中不要使用或操作mysql库和test库,禁止创建test或以test开头的库

禁止在mysql中使用用户自定义变量

线上数据库中不要进行业务的实时统计或者汇总等计算操作,可导出后利用其它工具或者在线下备份库中完成

2.3 MySQL 服务器及实例

2.3.1 硬件及网络

2.3.1.1 OS内核

操作系统版本选择RHEL 4U3或者更高版本

Linux内核选择2.6.9_5-9-0-0、2.6.32_1-4-0-0或者更高版本

2.3.1.2 磁盘相关

只能使用raid10或raid5类型,且存放数据或日志分区文件类型必须为EXT3

磁盘分区小于8T的数据块大小只能设置为4K,分区大于等于8T的数据块大小至少设置为8K

2.3.1.3 机房及网络

单个数据库集群服务器至少部署于两个IDC机房,且单机房必须满足SLA中有关产品线流量的规定

同一数据库集群中,同一IDC机房的所有服务器禁止在同一C网段

对于SLA为A级别的数据库服务,必须提供一个主库备机以防主库故障时可以进行及时有效的切换

主库备机使用的注意事项:

1)主库通过dbproxy访问情况下,主备库必须位于不同C网段

2)主库通过直连方式访问的情况下,主备库必须使用同一C网段,并提供虚IP供前端访问

3)数据库备份服务器必须与主库位于不同IDC机房

2.3.2 数据库实例

2.3.2.1 MySQL 版本

MySQL版本只能选择官方社区版本,且只能为5.1.45、5.1.63这两个版本,严禁打补丁(SequentDB、Infobright和OLAP等存储引擎除外)

同一集群数据库版本必须一致,否则可能带来主从数据不一致以及同步中断风险

注:这里的同一集群是指提供同一产品线服务的主从数据库

2.3.2.2 MySQL 目录

MySQL安装目录、子目录结构和命名等请参考并遵守《公司MySQL数据库管理规范》

2.3.2.3 MySQL 参数

主库上禁止设置binlog-do-db和binlog-ignore-db参数

从库上禁止使用replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table以及replicate-rewrite-db参数,使用replicate-wild-do-table或replicate-wild-ignore-table进行代替,

从库上必须设置replicate-wild-ignore-table过滤mysql和test库

超时时间按照如下设置,

参数 强制设置标准
interactive_timeout、wait_timeout 300秒
slave_net_timeout 60秒

其它各超时时间参数设置请DBA结合业务需求合理指定

文件句柄参数open_files_limit设置为10240或更大,且必须同时更改操作系统支持的最大打开文件句柄数为10240或更大,需与OS设置的句柄数相同(flashDB或者SEQDB除外)

除了EMS系统采用row同步方式外,其它所有产品数据库必须使用statement方式

transaction_isolation必须设置为REPEATABLE-READ,并且严禁在客户端或者应用程序中设置会话级事务隔离级别

MySQL的参数设置请参考并遵守《公司MySQL数据库管理规范》

2.3.2.4 MySQL 架构

同一数据库集群至少为1主1从架构,且至少要求部署于2个IDC机房中,单机房必须满足SLA中有关产品线流量的规定

同一集群下主从级联层数不能超过3层,不同集群不能存在同步的上下游关系,必须通过业务逻辑实现

同一集群下必须满足数据库账号授权和密码一致性,必须创建集群下所有机器的同步账号

注:这里的同一集群是指提供同一产品线服务的主从数据库

MySQL数据库的磁盘使用量不应超过85%

2.3.2.5 MySQL 日志

MySQL 全日志,错误日志,慢日志,以及binlog日志的保留期限和切分方案请参考并遵守《公司MySQL数据库管理规范》

2.3.2.6 MySQL 备份

数据库备份方案、备份策略、备份目录等请参考并遵守《公司MySQL数据库管理规范》以及签订的数据库SLA文档

2.4 中间层DBProxy

2.4.1 服务器配置和命名

DBProxy安装目录、配置和命名请参考并遵守《公司DBProxy配置规范》

DBProxy服务器的磁盘只可选用raid5或raid10类型,且存放数据和日志的分区文件系统类型必须为EXT3

DBProxy服务器磁盘分区数据块大小设置为4K

同一集群的DBProxy版本必须保持一致

同一集群的DBProxy配置文件必须在同一IDC相同,跨机房可以不相同

注:这里的同一集群是指提供同一产品线服务的主从数据库

DBProxy服务与MySQL数据库服务必须分服务器部署,严禁同机部署

DBProxy的配置文件中禁止将一个从库设置多次来解决负载不均衡问题

同一服务器上DBProxy总共占用的磁盘空间不超过硬盘总量的85%

同一服务器上DBProxy总共占用的CPU使用率不超过50%

同一服务器上DBProxy总共占用的内存不能超过20G

同一服务器上DBProxy总共占用的网卡流量不能超过70M

2.4.2 机房和网段

单个数据库集群的DBProxy服务器至少部署于两个IDC,且单机房必须满足SLA中有关产品线流量的规定

每台机器开启两个或多个vrrpd进程,分成两组或者多组,不同分组根据权重设置默认连接不同的主机

虚ip发生漂移的时间间隔设置为5秒

处于同一分组的两台或多台机器的虚ip权重设置必须不同

2.4.3 DBProxy日志

DBProxy各类日志的切分和保留策略请参考并遵守《公司DBProxy配置规范》

2.4.4 访问账号和权限

账号的db_username和username名称必须保持一致,有关db_username的命名请参见本文档2.1.7章节

线上账号与线下查询账号访问的DBProxy要分开部署,严禁线上服务与线下服务共用同一个DBProxy服务

3. 上线及监控

3.1 上线时间窗口

应选择业务低峰期的时间窗口进行数据库上线,具体时间视产品线而定

3.2 上线单内容

上线单中,RD/OP必须提供连接数据库的IP、端口和产品线的数据库名称,若有需dba注意之处则在备注中进行声明

RD提交的上线单中SQL语句必须简单明确,无二义性,确保可执行并通过测试

RD提交的上线单中不能使用perl、php、python、shell等不是标准化sql语句,更不能使用语言描述代替sql语句

上线单中必须有详细明确且可以执行的回滚方案,若无回滚方案,则需要说明原因

3.3 MySQL 和DBProxy监控

数据库集群在noah平台中至少要有如下监控策略:DB存活监控、DB同步延迟监控,DB同步状态监控,DB性能监控,OS基础监控,SSD使用监控,FTP存活监控,DBProxy存活监控,DBProxy虚IP监控

具体有关MySQL和DBProxy的监控策略部署和命名规范,请参考并遵守《公司DBProxy配置规范》

3.4 产品线SLA

在产品线或者子系统上线前,各个产品线必须与DBA签订数据库的SLA,DBA必须严格按照SLA的服务级别维护数据库并制定KPI

3.5 其他

新增数据库需RD填写数据库资源申请表格,并发送到相关邮件组,经DBA评估审核通过后方可上线

4. MySQL 风险点及BUG

4.1 同步中断

在主库上执行drop user 或者授权操作时,导致的从库上报错,停止恢复主库的binlog

主、从数据库的mysql不同版本(例如主库5.0,备库4.0)导致在备库上某些恢复无法进行,因而报错,停止恢复主库binlog

主库IO压力大,没有及时向从库发送binlog或者响应从库的连接请求,从库重试一定次数(master-connect-retry)/超时(slave-net-timeout)后,与主库连接断开

由于mysql的某些bug导致的中断,这种情况可能的原因不确定

主库上中断了某个操作(比如load data),可能会导致主从不一致,从库中断同步

主、从数据库出现不同的错误号,出现这种情景的原因不确定

主、从数据库中对应表的存储引擎不一致

主、从数据库中对应表的字符集不一致

在主库上操作了从库上没有的表或者行数据,导致从库报错,中断同步

主库中操作临时表,导致从库同步中断

执行insert into t1 select …语句导致从库同步中断

同一个session或者同一个事务中InnoDB和MyISAM表混合执行DML操作

drop table、rename table这类DDL与DML并发执行导致从库同步中断

主库向从库传输的binlog包损坏,导致从库恢复到某个sql时无法进行,同步中断

对于某些允许前端业务写操作的从库(不推荐),从主库同步过来的SQL或者存储过程等与从库本身的事务产生阻塞等待资源释放(如表,行锁),超过等待时间后,从库与主库的同步中断

对于存储过程和触发器等,在主库上执行正常,从库执行报错,导致从库中断

4.2 主从数据不一致

主、从数据库中对应表的存储引擎不一致

主、从数据库中对应表的字符集不一致

主、从数据库的MySQL版本不一致

主库上执行有关ON DUPLICATE KEY UPDATE语句

同一个session或者同一个事务中InnoDB和MyISAM表混合DML操作

4.3 数据库crash

高负载下频繁执行show innodb status、show processlist或开启innodb_monitor,bug请参考:http://bugs.mysql.com/bug.php?id=38883

单条SQL包含超过255个union联合查询,bug请参考: http://bugs.mysql.com/bug.php?id=33362

欢迎关注下方“非著名资深码农“公众号进行交流~

发表评论

邮箱地址不会被公开。