共計 8603 個字符，預計需要花費 22 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 mysql 多層元信息與查詢的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
一、元信息重要性 1.1、什么是 mysql 元信息
mysql 元信息是指記錄 mysql 的基本信息，例如服務器信息，IP 信息，實例信息，集群信息，數(shù)據(jù)庫信息，數(shù)據(jù)庫用戶信息，域名信息，域名與實例關系信息，備份任務信息，備份結果信息等
1.2、mysql 元信息作用
1 規(guī)范化
為了規(guī)范化 mysql 的基本信息
2 自動化
可以很方便被 shell、python 腳本調(diào)用，獲取信息方便
3 邏輯化
可以清晰 mysql 各邏輯情況，例如實例，服務器，集群等
二、元信息架構
1、服務器（my_server）
功能簡述：管理服務器信息
2、集群（my_cluster）
功能簡述：管理集群信息
3、實例（my_db）
功能簡述：管理實例信息
4、數(shù)據(jù)庫（my_database）
功能簡述：管理數(shù)據(jù)庫信息
5、用戶（my_database_user）
功能簡述：管理數(shù)據(jù)庫用戶信息
6、域名（my_domain）
功能簡述：管理域名信息
7、數(shù)據(jù)庫備份任務（my_backup_task）
功能簡述：管理備份任務
8、數(shù)據(jù)庫備份記錄（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
關聯(lián)關系 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 數(shù)據(jù)庫歸屬業(yè)務 ,
`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 集群初始數(shù)據(jù)量 ,
`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 是否優(yōu)先可切為 master，1 優(yōu)先，0 不優(yōu)先 ,
`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 實例狀態(tài) ,
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 實例信息表
關聯(lián)關系：
cluster_id 與集群表 my_cluster 的集群號 id 對應
host 與機器表 my_ip 的 IP 對應
3.5、數(shù)據(jù)庫表
功能：記錄數(shù)據(jù)庫的信息（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 創(chuàng)建時間 ,
`modify_time` timestamp NOT NULL DEFAULT 2000-01-01 00:00:00 COMMENT 修改時間 ,
`db_department` varchar(32) NOT NULL DEFAULT COMMENT 業(yè)務部門 ,
db_business varchar(32) NOT NULL DEFAULT COMMENT 所屬組 ,
`job_status` varchar(100) NOT NULL COMMENT 數(shù)據(jù)庫狀態(tài) ,
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= 數(shù)據(jù)庫 schema 信息表
關聯(lián)關系：
cluster_id 與集群表 my_cluster 的集群號 id 對應
3.6、實例與域名關系表
功能：記錄數(shù)據(jù)庫實例與域名關系表
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= 數(shù)據(jù)庫實例與域名 id 關系表
關聯(lián)關系：
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= 域名信息表
關聯(lián)關系：
cluster_id 為集群表 my_cluster 的 id 號，需要選擇
domain_flag 為了區(qū)分域名讀寫的屬性，也可以在域名起名的環(huán)節(jié)制定規(guī)則，來區(qū)分讀寫域名，例如 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 數(shù)據(jù)庫 schema 的 id ,
PRIMARY KEY (`id`),
UNIQUE KEY `un_database_user_host` (`database_id`,`username`,`userhost`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT= 用戶信息表
關聯(lián)關系：
database_id 與數(shù)據(jù)庫表 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 備份保留個數(shù) ,
`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
關聯(lián)關系：
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
關聯(lián)關系：
cluster_id 與集群表的 id 號對應
db_id 與實例表 my_db 的 id 對應
四、元信息最佳使用實踐 4.1、qmysql 工具 4.1.1、qmysql 工具功能
代碼：python 開發(fā)，用于方便 DBA 查詢集群、實例的相關信息，方便使用
原理：即利用多表元信息關聯(lián)查詢，查詢出相關的信息
優(yōu)點：
查詢集群拓撲情況，快速展示
快速登錄實例
快速登錄機器
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 多層元信息與查詢的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業(yè)資訊頻道！
