共计 8603 个字符，预计需要花费 22 分钟才能阅读完成。
丸趣 TV 小编给大家分享一下 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 多层元信息与查询的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
