Shulou(Shulou.com)05/31 Report--

The importance of meta-information 1.1.What is mysql meta-information

Mysql meta-information refers to recording the basic information of mysql, such as server information, IP information, instance information, cluster information, database information, database user information, domain name information, domain name and instance relationship information, backup task information, backup result information, etc.

1.2. MySQL meta-information function

Standardization

To standardize the basic information of mysql

Automation

It can be easily called by shell and python scripts, and it is convenient to obtain information.

Logicalization

Clear the logic of mysql, such as instance, server, cluster, etc.

Second, meta-information architecture

1. Server (my_server)

Function brief: manage server information

2. Cluster (my_cluster)

Function brief: manage cluster information

3. Instance (my_db)

Function brief: manage instance information

4. Database (my_database)

Function brief: manage database information

5. User (my_database_user)

Function description: manage database user information

6. Domain name (my_domain)

Function brief: manage domain name information

7. Database backup task (my_backup_task)

Feature brief: manage backup tasks

8. Database backup record (my_backup_info)

Function brief: manage backup record information

Third, create the table SQL in detail

Note: because one machine has 2 IP, so there is ip,ip2

CREATE TABLE `my_ server` (

`id`int (11) NOT NULL AUTO_INCREMENT

`ip`char (15) NOT NULL COMMENT'IP address'

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

`hostname` varchar (50) DEFAULT NULL COMMENT 'hostname'

`valid`valid` varchar (1) NOT NULL COMMENT'is valid, 1 is valid, 0 is invalid'

`idc`varchar (32) DEFAULT NULL COMMENT 'computer room information'

`create_ time`datetime DEFAULT NULL COMMENT 'machine add time'

`machine_ type`varchar (50) DEFAULT NULL COMMENT 'machine package'

`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 machine information table'

Function: a table used to record server information

3.2.The IP information table

CREATE TABLE `my_ ip` (

`id`int (11) NOT NULL AUTO_INCREMENT

`ip`char (15) NOT NULL COMMENT'IP address'

`hostname` varchar (50) DEFAULT NULL COMMENT 'hostname'

`valid`valid` varchar (1) NOT NULL COMMENT'is valid, 1 is valid, 0 is invalid'

`idc`varchar (32) DEFAULT NULL COMMENT 'computer room information'

`create_ time`datetime DEFAULT NULL COMMENT 'machine add time'

`machine_ type`varchar (50) DEFAULT NULL COMMENT 'machine package'

`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 Information Table'

Function: used to record mysql-related IP information, used to select IP for my_db

Association relationship ip manages the ip/ip2 of the my_server

3.3. Cluster table

Function: used to record information about a cluster

CREATE TABLE `my_ cluster` (

`id`int (11) NOT NULL AUTO_INCREMENT

`cluster_ name`varchar (256) NOT NULL COMMENT 'cluster name'

`cluster_ port`int (11) DEFAULT NULL COMMENT 'Cluster Port'

`The business_ info` varchar (256) NOT NULL COMMENT 'database attribution business'

`cluster_ developpenc` tinyint (1) NOT NULL DEFAULT'0' COMMENT '0blol onlineline record1developpence2lytest`

The founder of `add_user_ id`int (11) DEFAULT NULL COMMENT', responsible for the dba' of this cluster

`add_ time`datetime DEFAULT NULL COMMENT 'add time

`modify_ time`datetime DEFAULT NULL COMMENT 'modification time'

`valid`varchar (1) whether the NOT NULL DEFAULT'1' COMMENT 'cluster is valid or offline, 1 is valid, 0 is invalid, offline'

`data_init_ size`int (11) NOT NULL DEFAULT'0' COMMENT 'initial data volume of cluster'

`data_ increment 'varchar (100) NOT NULL DEFAULT' 0' COMMENT 'Cluster growth'

`version`varchar (20) NOT NULL DEFAULT '5.5.27' COMMENT' mysql version, 5.5.27pence5.6.21min5.7.15'

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

`backup_ backup `tinyint (1) NOT NULL DEFAULT'1' COMMENT 'whether to backup, 1 backup, 0 no backup'

Cluster_desc varchar (500) not null default 'COMMENT' description'

PRIMARY KEY (`id`)

UNIQUE KEY `ux_ clustername` (`cluster_ name`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='mysql Cluster Information Table'

Function: record the relevant information of the cluster instance of mysql

CREATE TABLE `my_ DB` (

`id`int (11) NOT NULL AUTO_INCREMENT

`cluster_ id`int (11) NOT NULL COMMENT 'cluster number'

`host` char (15) NOT NULL COMMENT 'instance IP'

`port`varchar (8) NOT NULL COMMENT 'instance port number'

`add_user_ id`int (11) DEFAULT NULL COMMENT 'added user'

`time added add_ time`datetime DEFAULT NULL COMMENT'

`time modified by modify_ time`datetime DEFAULT NULL COMMENT'

`valid`tinyint (1) whether the NOT NULL DEFAULT'1' COMMENT 'instance is valid, 1 is valid, 0 is invalid'

Read and write of `service`varchar (32) NOT NULL COMMENT 'instance, Write&Read,Read,Bakup,Out-of-service'

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

`tinyint (4) xtrabackup_ 'NOT NULL DEFAULT' 0'

`candidate_ master`tinyint (4) DEFAULT'0' COMMENT 'whether priority can be cut as master,1 priority, 0 does not give priority'

`no_ master`tinyint (4) DEFAULT'0'

`mha_write_into_ confus` 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 'initializes db'

'job_ status` varchar (100) NOT NULL COMMENT' instance status'

Db_desc varchar (500) not null default 'COMMENT' description'

PRIMARY KEY (`id`)

UNIQUE KEY `ux_ hostportservicename` (`host`, `port`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='mysql instance information table'

Relationship:

Cluster_id corresponds to the cluster number id of the cluster table my_cluster

Host corresponds to the IP of machine table my_ip

3.5, database table

Function: record the information of the database (schema)

CREATE TABLE `my_ database` (

`id`int (11) NOT NULL AUTO_INCREMENT

`cluster_ id`int (11) NOT NULL COMMENT 'Cluster 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 responsible'

`add_user_ id`int (11) DEFAULT NULL COMMENT 'add the dba' of the library

`valid`varchar (1) whether the NOT NULL DEFAULT'1' COMMENT 'library is valid, 1 is valid, 0 is invalid'

`Time`timestamp NOT NULL DEFAULT '2017-01-01 add_ COMMENT' creation time'

`modify_ time`timestamp NOT NULL DEFAULT '2000-01-01 00 COMMENT' modification time'

`db_ department`varchar (32) NOT NULL DEFAULT''COMMENT' Business Unit'

Db_business varchar (32) Group to which NOT NULL DEFAULT''COMMENT' belongs

`job_ status` varchar (100) NOT NULL COMMENT 'database status'

Database_desc varchar (500) not null default 'COMMENT' description'

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=' database schema information table'

Relationship:

Cluster_id corresponds to the cluster number id of the cluster table my_cluster

3.6. Relationship table between instance and domain name

Function: record the relationship table between database instance and domain name

CREATE TABLE `my_db_ domain` (

`id`int (11) NOT NULL AUTO_INCREMENT

The instance number of the `db_ id`int (11) NOT NULL COMMENT 'my_ DB table'

`domain_ id`int (11) NOT NULL COMMENT 'domain name id number'

PRIMARY KEY (`id`)

UNIQUE KEY `ux_db_ id` (`db_ id`, `domain_ id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=' database instance and domain name id relation table'

Relationship:

Domain_id corresponds to the instance id number of my_domain

Db_id corresponds to instance id of instance table my_db

3.7. Domain name information table

Function: record domain name information

CREATE TABLE `my_ domain` (

`id`int (11) NOT NULL AUTO_INCREMENT

Cluster id' to which the `cluster_ id` int (11) NOT NULL DEFAULT'0' COMMENT 'domain name belongs

`read-write identification of the domain name `int (11) NOT NULL COMMENT', 1, read-only, 0, read-write'

`domain_ name`varchar (64) NOT NULL COMMENT 'domain name'

PRIMARY KEY (`id`)

UNIQUE KEY `ux_domain_ name` (`domain_ name`)

KEY `ix_ clusterid` (`cluster_ id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=' domain name information table'

Relationship:

Cluster_id is the id number of the cluster table my_cluster. You need to select

In order to distinguish between domain name read and write attributes, domain_flag can also make rules in the process of domain name naming to distinguish between read and write domain names. For example, domainname_w is a written domain name and domainname_r is a read domain name.

3.8, user table

CREATE TABLE `my_database_ user` (

`id`int (11) NOT NULL AUTO_INCREMENT

`username`varchar (30) NOT NULL COMMENT 'username'

`userhost` varchar (60) NOT NULL COMMENT 'permission, IP' that can be sourced

`userpwd` varchar (30) NOT NULL COMMENT 'password'

`privs`varchar (2000) NOT NULL COMMENT 'permission'

The DBA' added by `add_user_ id`int (11) NOT NULL COMMENT'

`add_ time`datetime (6) NOT NULL COMMENT 'add time'

`valid` int (11) NOT NULL COMMENT'is valid, 1 is valid, 0 is invalid'

`modify_ time`datetime (6) NOT NULL COMMENT 'modification time'

The id' of the `database_ id` int (11) NOT NULL COMMENT 'database schema

PRIMARY KEY (`id`)

UNIQUE KEY `un_database_user_ host` (`username`, `username`, `userhost`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT=' user information table'

Relationship:

Database_id corresponds to the id number of the database table my_database

3.9. Backup task table

CREATE TABLE `my_backup_ task` (

`id`int (11) NOT NULL AUTO_INCREMENT

`db_ names`varchar (20) DEFAULT NULL COMMENT 'contains library name'

`backup_ type` int (11) NOT NULL default 0 COMMENT 'backup type (0meme hoTBAK'), (1recorder DUMP')'

`backup_ backup time`varchar (50) NOT NULL COMMENT 'backup date'

`backup_ crontime`varchar (30) NOT NULL COMMENT 'backup crontab time'

`backup_ dir`varchar (150) NOT NULL COMMENT 'backup address'

`expire_ counts`int (11) NOT NULL COMMENT 'number of backups retained'

`isvalid` int (11) NOT NULL default 1 COMMENT'is valid, 1 valid 0 is invalid, default 1 is valid'

`modify_ time`datetime (6) DEFAULT NULL COMMENT 'modification time'

`last_start_ time`datetime (6) DEFAULT NULL COMMENT 'recent backup start time'

`last_end_ time`datetime (6) DEFAULT NULL COMMENT 'recent backup end time'

`last_ size`double DEFAULT NULL COMMENT 'Last backup file size (M)'

`cluster_ id`int (11) NOT NULL COMMENT 'cluster number'

`db_ id`int (11) NOT NULL COMMENT 'instance number'

`cost_ time`int (11) NOT NULL COMMENT 'backup time (min)'

`For last_ status` int (11) NOT NULL default 1 COMMENT 'result of the last backup, 0 failed with warning' 1 succeeded with warning'

PRIMARY KEY (`id`)

UNIQUE KEY `un_clusterid_ backup` (`cluster_ id`, `backup_ type`)

) ENGINE=InnoDB AUTO_INCREMENT=4633 DEFAULT CHARSET=utf8

Relationship:

Cluster_id corresponds to the id number of the cluster table my_cluster

Db_id corresponds to the id of the instance table my_db

3.10. Backup information record table

CREATE TABLE `my_backup_ info` (

`id`int (11) NOT NULL AUTO_INCREMENT

`db_ names`varchar (20) DEFAULT NULL COMMENT 'include library'

`backup_ type`int (11) NOT NULL default 0 COMMENT 'backup method, 0xtraj 1dump'

`backup_ dir`varchar (150) DEFAULT NULL COMMENT 'backup final result'

`start_ time`datetime (6) DEFAULT NULL COMMENT 'backup start time'

`end_ time`datetime (6) DEFAULT NULL COMMENT 'backup end time'

`size`double DEFAULT NULL COMMENT 'file size (M)'

Backup result of `status`status` int (11) NOT NULL COMMENT', 0meme failed with warning 1succeeded with warning'

`message`varchar (256) DEFAULT NULL COMMENT 'backup details'

`cluster_ id`int (11) NOT NULL COMMENT 'cluster number'

`db_ id`int (11) NOT NULL COMMENT 'instance number'

`cost_ time`int (11) NOT NULL COMMENT 'backup time (min)'

PRIMARY KEY (`id`)

KEY `ix_cluster_ id` (`cluster_ id`)

KEY `ix_db_ id` (`db_ id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Relationship:

Cluster_id corresponds to the id number of the cluster table

Db_id corresponds to the id of the instance table my_db

4. Best practices for meta-information 4.1, qmysql tools 4.1.1, qmysql tool functions

Code: python development, used to facilitate DBA query cluster, instance-related information, easy to use

Principle: that is, using multi-table meta-information association query to query the relevant information.

Advantages:

Query cluster topology for quick display

Quickly log in to an instance

Quickly log in to the machine

4.1.2, qmysql function summary

4.1.3, qmysql function-query cluster topology by cluster number

4.1.4. Qmysql function-query cluster topology by cluster name

4.1.5. Qmysql function-query cluster topology by library name

4.1.6, qmysql function-query cluster topology by service name

4.1.7. Log in to the instance

Mysql. Instance number, you can log in to the specified instance

4.1.8. Log in to the machine remotely

Ssh. Instance number

You can log in to the machine remotely.

The above is all the contents of the article "sample Analysis of mysql Multi-layer Meta-Information and query". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.