[MySQL] – Utilizando os metadados do information_schema


Bom pessoal, neste post vou falar um pouco sobre algumas informações que ajudam o dia-a-dia de um DBA MySQL utilizando os Metadados do MySQL mais precisamente o information_schema. O information_schema é a estrutura de dicionário de dados utilizada pelo MySQL, nisso está estrutura provém informações referente a quaisquer objetos que estão relacionados neste banco de dados. Podemos extrair desta estrutura informações como privilégios de usuários, nomes de procedimentos armazenados e funções de um determinado schema. Bom chega de teoria vamos a prática.

use information_schema;

O Comando abaixo, verifica os privilégios atribuídos a um usuário no MySQL:

mysql> select grantee, privilege_type from information_schema.user_privileges;

Seleção_034
O Comando abaixo, verifica os nomes das tabelas de um determinado esquema(schema) e o tipo de tabela no MySQL:

mysql> select table_name, table_type, engine from information_schema.tables where table_schema = 'sakila';

Seleção_035
O Comando abaixo, verifica as sessões que estão em execução e em espera no MySQL:

mysql> select id, user, host, db, state from information_schema.processlist;

Seleção_037
O Comando abaixo, verifica os esquemas(schemas) existentes no MySQL:

mysql> select schema_name from information_schema.schemata;

Seleção_038
O Comando abaixo, verifica o nome de um esquema(schema), tamanho deste esquema(schema) em megabytes e espaço livre no MySQL:

mysql> SELECT  table_schema AS 'Nome do Banco de Dados',  Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Tamanho Armazenado (MB)',  Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Espaço Livre (MB)' FROM information_schema.tables GROUP BY table_schema ;

Seleção_039
O Comando abaixo, verifica o collation e character-set utilizado pelos esquemas(schemas) do MySQL:

mysql> select schema_name, default_collation_name, default_character_set_name from information_schema.schemata;

Seleção_040
Também podemos utilizar funções fornecidas pelo MySQL que consultam as informações do information_schema. Segue abaixo algumas delas abaixo:

SHOW DATABASES; ou SHOW SCHEMAS;
SHOW TABLES;
SHOW TABLE STATUS FROM `sakila`;
SHOW FUNCTION STATUS WHERE `Db`='sakila';
SHOW PROCEDURE STATUS WHERE `Db`='sakila';
SHOW TRIGGERS FROM `sakila`;
SHOW EVENTS FROM `sakila`;

Seleção_042
Seleção_041
Agora vem o plus deste Post. Para facilitar você leitor e DBA MySQL investi um pouco do meu tempo para desenvolver um procedimento armazenado no MySQL para facilitar a visualização de informações de objetos pertencentes a um banco de dados armazenado no MySQL e seus objetos.
Como os Scripts já estão prontos , vou demostrar como fazer para utiliza-los , primeiro passo será conectar no MySQL , o segundo passo é selecionar o schema do “mysql” e por ultimo executarmos o script de PL/MySQL em nosso banco de dados.
0_script
1_script
2_script
Como são dois scripts você terá que executar um de cada vez para depois utiliza-lo.
Agora vou demonstrar a utilização do script que passa como parâmetro o nome da base de dados que vamos precisar tirar o relatório , veja abaixo:

mysql> call rel_schema('sakila')\G

1_exec
Agora o segundo script  tira um relatório de todos os esquemas(schemas) do seu banco de dados MySQL  com exceção dos esquemas(schemas) de gerenciamento do SGBD que no caso é o “information_schema”, “mysql”, “performace_schema”. Agora para executar este script não é necessário nenhuma passagem de parâmetro, pois o procedimento faz a seleção dos esquemas (schemas) que precisaram fazer o relátorio com base nos esquemas(schemas) relacionado na tabela “schemata” do “information_schema”.

mysql> call relatorioschemas()\G

2_exec
Segue abaixo, o link para download dos scripts:
http://sdrv.ms/PcsdR2
Bom pessoal, que estes procedimentos armazenados e dicas possam ajudar melhor a cada um no gerenciamento do seu banco de dados MySQL, assim podendo estimar informações e obter estatísticas com base em dados do próprio SGBD. Qualquer dúvida sobre o Post estarei disponível para ajudar. Que a Graça e Paz estejam com todos vocês.

Share

    Comments

    1. Emerson,
      Muito bom post, quanto ao assunto MS SQL SERVER previsão para posts, já sairam em Oracle e Mysql e nada de MS SQL SERVER,
      Até mais…

    2. Oi Emerson, estou pesquisando algo como fazer uma cópia de um schema MySQL (no mesmo servidor) mas, por motivo de exigência de alta performance, utilizando os Arquivos de Dados ao invés de um Dump do Schema. Seria um backup e restore dos arquivos (copiando os arquivos de dados do HD).

    Deixe uma resposta

    O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

    © 2019 All Rights Reserved. Cooperati.