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多層元信息與查詢的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！