mysql多层元信息与查询的示例分析 - MySQL数据库

小编给大家分享一下mysql多层元信息与查询的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

一、元信息重要性

1.1、什么是mysql元信息

   mysql元信息是指记录mysql的基本信息，例如服务器信息，IP信息，实例信息，集群信息，数据库信息，数据库用户信息，域名信息，域名与实例关系信息，备份任务信息，备份结果信息等

1.2、mysql元信息作用

<1>规范化

为了规范化mysql的基本信息

<2>自动化

可以很方便被shell、python脚本调用，获取信息方便

<3>逻辑化

可以清晰mysql各逻辑情况，例如实例，服务器，集群等

二、元信息架构

1、服务器（my_server）

功能简述：管理服务器信息

2、集群（my_cluster）

功能简述：管理集群信息

3、实例（my_db）

功能简述：管理实例信息

4、数据库（my_database）

功能简述：管理数据库信息

5、用户（my_database_user）

功能简述：管理数据库用户信息

6、域名（my_domain）

功能简述：管理域名信息

7、数据库备份任务（my_backup_task）

功能简述：管理备份任务

8、数据库备份记录（my_backup_info）

功能简述：管理备份记录信息

三、建表SQL详细

3.1、服务器信息表

注：因1台机器有2个IP，所以有ip，ip2

 CREATE TABLE `my_server` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `ip` char(15) NOT NULL COMMENT 'IP地址',

  `ip2` char(15) NOT NULL COMMENT 'ip2',

  `hostname` varchar(50) DEFAULT NULL COMMENT '主机名',

  `valid` varchar(1) NOT NULL COMMENT '是否有效，1有效，0无效',

  `idc` varchar(32) DEFAULT NULL COMMENT '机房信息',

  `create_time` datetime DEFAULT NULL COMMENT '机器添加时间',

  `machine_type` varchar(50) DEFAULT NULL COMMENT '机器套餐',

  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),

  UNIQUE KEY `ux_ip` (`ip`),

  UNIQUE KEY `ux_ip2` (`ip2`),

  UNIQUE KEY `ux_hostname` (`hostname`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mysql机器信息表' 

功能：用于记录服务器信息的表

3.2、IP信息表

CREATE TABLE `my_ip` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `ip` char(15) NOT NULL COMMENT 'IP地址',

  `hostname` varchar(50) DEFAULT NULL COMMENT '主机名',

  `valid` varchar(1) NOT NULL COMMENT '是否有效，1有效，0无效',

  `idc` varchar(32) DEFAULT NULL COMMENT '机房信息',

  `create_time` datetime DEFAULT NULL COMMENT '机器添加时间',

  `machine_type` varchar(50) DEFAULT NULL COMMENT '机器套餐',

  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),

  UNIQUE KEY `ux_ip` (`ip`),

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='IP信息表' 

功能：用于记录mysql相关IP的信息，用于给my_db选择IP

关联关系 ip管理my_server的ip/ip2

3.3、集群表

功能：用于记录一个集群的相关信息

 CREATE TABLE `my_cluster` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `cluster_name` varchar(256) NOT NULL COMMENT '集群名',

  `cluster_port` int(11) DEFAULT NULL COMMENT '集群端口',

  `business_info` varchar(256) NOT NULL COMMENT '数据库归属业务',

  `cluster_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0:online,1：develop,2:test',

  `add_user_id` int(11) DEFAULT NULL COMMENT '建立者，负责此集群的dba',

  `add_time` datetime DEFAULT NULL COMMENT '添加时间',

  `modify_time` datetime DEFAULT NULL COMMENT '修改时间',

  `valid` varchar(1) NOT NULL DEFAULT '1' COMMENT '集群是否有效、下线，1有效，0无效、下线',

  `data_init_size` int(11) NOT NULL DEFAULT '0' COMMENT '集群初始数据量',

  `data_increase` varchar(100) NOT NULL DEFAULT '0' COMMENT '集群增长情况',

  `version` varchar(20) NOT NULL DEFAULT '5.5.27' COMMENT 'mysql版本，5.5.27,5.6.21,5.7.15',

  `mha_seton` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:set off,1:set on',

  `backup_flag` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否备份，1备份，0不备份',

   cluster_desc varchar(500) not null default '' COMMENT '描述',

  PRIMARY KEY (`id`),

UNIQUE KEY `ux_clustername` (`cluster_name`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='mysql集群信息表'

3.4、实例表

功能：记录mysql的集群实例的相关信息

CREATE TABLE `my_db` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `cluster_id` int(11) NOT NULL COMMENT '集群号',

  `host` char(15) NOT NULL COMMENT '实例IP',

  `port` varchar(8) NOT NULL COMMENT '实例端口号',

  `add_user_id` int(11) DEFAULT NULL COMMENT '添加的用户',

  `add_time` datetime DEFAULT NULL COMMENT '添加的时间',

  `modify_time` datetime DEFAULT NULL COMMENT '修改的时间',

  `valid` tinyint(1) NOT NULL DEFAULT '1' COMMENT '实例是否有效，1有效，0无效',

  `service` varchar(32) NOT NULL COMMENT '实例读写情况，Write&Read，Read,Bakup，Out-of-service',

  `role` varchar(80) NOT NULL COMMENT 'db role',

  `xtrabackup_flag` tinyint(4) NOT NULL DEFAULT '0',

  `candidate_master` tinyint(4) DEFAULT '0' COMMENT '是否优先可切为master，1优先，0不优先',

  `no_master` tinyint(4) DEFAULT '0',

  `mha_write_into_conf` tinyint(4) DEFAULT '1' COMMENT 'mha_write_into_conf,1write;0,not write',

  `binlog_dir` varchar(100) DEFAULT NULL COMMENT 'binlog_dir',

  `innodb_buffer` varchar(30) NOT NULL DEFAULT '1G' COMMENT 'innodb_buffer_pool set',

  `db_version` varchar(10) NOT NULL DEFAULT '' COMMENT 'db_version,5.5.27,5.7.15',

  `init_db` varchar(60) DEFAULT NULL COMMENT '初始化db',

  `job_status` varchar(100) NOT NULL COMMENT '实例状态',  

  db_desc varchar(500) not null default ''  COMMENT '描述',

  PRIMARY KEY (`id`),

  UNIQUE KEY `ux_hostportservicename` (`host`,`port`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='mysql实例信息表' 

关联关系：

cluster_id与集群表my_cluster的集群号id对应

host与机器表my_ip的IP对应

3.5、数据库表

功能：记录数据库的信息（schema） 

 CREATE TABLE `my_database` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `cluster_id` int(11) NOT NULL COMMENT '集群id号',

  `db_name` varchar(220) NOT NULL COMMENT 'db name',

  `service_name` varchar(100) NOT NULL COMMENT 'service name',

  `db_owners` varchar(128) NOT NULL DEFAULT '' COMMENT 'RD负责人',

  `add_user_id` int(11) DEFAULT NULL COMMENT '添加库的dba',

  `valid` varchar(1) NOT NULL DEFAULT '1' COMMENT '库是否有效，1有效，0无效',

  `add_time` timestamp NOT NULL DEFAULT '2017-01-01 00:00:00' COMMENT '创建时间',

  `modify_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '修改时间',

  `db_department` varchar(32) NOT NULL DEFAULT '' COMMENT '业务部门',

   db_business varchar(32) NOT NULL DEFAULT '' COMMENT '所属组',

  `job_status` varchar(100) NOT NULL COMMENT '数据库状态',  

  database_desc varchar(500) not null default '' COMMENT '描述',  

PRIMARY KEY (`id`),

  UNIQUE KEY `ux_clusteriddb` (`db_name`,`cluster_id`),

  KEY `ix_cluster_id` (`cluster_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='数据库schema信息表' 

关联关系：

cluster_id与集群表my_cluster的集群号id对应

3.6、实例与域名关系表

功能：记录数据库实例与域名关系表

CREATE TABLE `my_db_domain` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `db_id` int(11) NOT NULL COMMENT 'my_db表的实例号',

  `domain_id` int(11) NOT NULL COMMENT '域名id号',

  PRIMARY KEY (`id`),

  UNIQUE KEY `ux_db_id` (`db_id`,`domain_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='数据库实例与域名id关系表' 

关联关系：

domain_id与my_domain的实例id号对应

db_id与实例表my_db的实例id对应

3.7、域名信息表

功能：记录域名信息

CREATE TABLE `my_domain` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `cluster_id` int(11) NOT NULL DEFAULT '0' COMMENT '域名所属集群id',

  `domain_flag` int(11) NOT NULL COMMENT '域名读写标识，1，只读，0，读写',

  `domain_name` varchar(64) NOT NULL COMMENT '域名',

  PRIMARY KEY (`id`),

  UNIQUE KEY `ux_domain_name` (`domain_name`),

  KEY `ix_clusterid` (`cluster_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='域名信息表'

关联关系：

cluster_id为集群表my_cluster的id号，需要选择

domain_flag 为了区分域名读写的属性，也可以在域名起名的环节制定规则，来区分读写域名，例如  domainname_w 为写域名，domainname_r为读域名

3.8、用户表

CREATE TABLE `my_database_user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `username` varchar(30) NOT NULL  COMMENT '用户名',

  `userhost` varchar(60) NOT NULL  COMMENT '权限，可来源的IP',

  `userpwd` varchar(30) NOT NULL COMMENT '密码',

  `privs` varchar(2000) NOT NULL  COMMENT '权限',

  `add_user_id` int(11) NOT NULL  COMMENT '添加的DBA',

  `add_time` datetime(6) NOT NULL  COMMENT '添加时间' ,

  `valid` int(11) NOT NULL COMMENT '是否有效，1有效，0无效',

  `modify_time` datetime(6) NOT NULL COMMENT '修改时间' ,

  `database_id` int(11) NOT NULL COMMENT '数据库schema的id',

  PRIMARY KEY (`id`),

  UNIQUE KEY `un_database_user_host` (`database_id`,`username`,`userhost`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8  COMMENT='用户信息表' 

关联关系：

database_id与数据库表my_database 的id号对应

3.9、备份任务表

 CREATE TABLE `my_backup_task` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `db_names` varchar(20) DEFAULT NULL  COMMENT '包含库名',

  `backup_type` int(11) NOT NULL default 0  COMMENT '备份类型(0,'HOTBAK'),(1,'DUMP')',

  `backup_weektime` varchar(50) NOT NULL COMMENT '备份日期',

  `backup_crontime` varchar(30) NOT NULL COMMENT '备份crontab时间',

  `backup_dir` varchar(150) NOT NULL COMMENT '备份地址',

  `expire_counts` int(11) NOT NULL COMMENT '备份保留个数',

  `isvalid` int(11) NOT NULL default 1 COMMENT '是否有效，1有效0无效，默认1有效',

  `modify_time` datetime(6) DEFAULT NULL COMMENT '修改时间‘,

  `last_start_time` datetime(6) DEFAULT NULL COMMENT '最近备份开始时间',

  `last_end_time` datetime(6) DEFAULT NULL COMMENT '最近备份结束时间',

  `last_size` double DEFAULT NULL COMMENT '上次备份文件大小(M)',

  `cluster_id` int(11) NOT NULL COMMENT '集群号',

  `db_id` int(11) NOT NULL COMMENT '实例号',

  `cost_time` int(11) NOT NULL COMMENT '备份耗时(分)',

  `last_status` int(11) NOT NULL  default 1 COMMENT '最近一次备份结果，0failed，1succeed，2succeed with warning',

  PRIMARY KEY (`id`),

  UNIQUE KEY `un_clusterid_backup` (`cluster_id`,`backup_type`),

) ENGINE=InnoDB AUTO_INCREMENT=4633 DEFAULT CHARSET=utf8

关联关系：

cluster_id与集群表my_cluster的id号对应

db_id与实例表my_db的id对应

3.10、备份信息记录表

CREATE TABLE `my_backup_info` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `db_names` varchar(20) DEFAULT NULL COMMENT '包含库',

  `backup_type` int(11) NOT NULL default 0 COMMENT '备份方式,0xtra,1dump',

  `backup_dir` varchar(150) DEFAULT NULL COMMENT '备份最终结果',

  `start_time` datetime(6) DEFAULT NULL COMMENT '备份开始时间',

  `end_time` datetime(6) DEFAULT NULL COMMENT '备份结束时间',

  `size` double DEFAULT NULL COMMENT '份文件大小(M)',

  `status` int(11) NOT NULL COMMENT '备份结果,0,failed,1succeed,2succeed with warning ',

  `message` varchar(256) DEFAULT NULL COMMENT '备份详细信息',

  `cluster_id` int(11) NOT NULL COMMENT '集群号',

  `db_id` int(11) NOT NULL COMMENT '实例号',

  `cost_time` int(11) NOT NULL COMMENT '备份耗时(分)',

  PRIMARY KEY (`id`),

  KEY `ix_cluster_id` (`cluster_id`),

  KEY `ix_db_id` (`db_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

关联关系：

cluster_id与集群表的id号对应

db_id与实例表my_db的id对应

四、元信息最佳使用实践

4.1、qmysql工具

4.1.1、qmysql工具功能

代码：python开发，用于方便DBA查询集群、实例的相关信息，方便使用

原理：即利用多表元信息关联查询，查询出相关的信息

优点：

查询集群拓扑情况，快速展示

快速登录实例

快速登录机器

4.1.2、qmysql功能 汇总

4.1.3、qmysql功能–按集群号查询集群拓扑

4.1.4、qmysql功能–按集群名查询集群拓扑

4.1.5、qmysql功能–按库名查询集群拓扑

4.1.6、qmysql功能–按服务名查询集群拓扑

4.1.7、登录实例

mysql.实例号，即可登录指定实例

4.1.8、远程登录机器

ssh.实例号

即可远程登录机器

以上是“mysql多层元信息与查询的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注云行业资讯频道！