小编给大家分享一下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多层元信息与查询的示例分析”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!