[MySQL] – Utilizando os metadados do information_schema
emerson.fender
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.
1 | use information_schema; |
O Comando abaixo, verifica os privilégios atribuídos a um usuário no MySQL:
1 | mysql> select grantee, privilege_type from information_schema.user_privileges; |
O Comando abaixo, verifica os nomes das tabelas de um determinado esquema(schema) e o tipo de tabela no MySQL:
1 | mysql> select table_name, table_type, engine from information_schema.tables where table_schema = 'sakila'; |
O Comando abaixo, verifica as sessões que estão em execução e em espera no MySQL:
1 | mysql> select id, user, host, db, state from information_schema.processlist; |
O Comando abaixo, verifica os esquemas(schemas) existentes no MySQL:
1 | mysql> select schema_name from information_schema.schemata; |
O Comando abaixo, verifica o nome de um esquema(schema), tamanho deste esquema(schema) em megabytes e espaço livre no MySQL:
1 | 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 ; |
O Comando abaixo, verifica o collation e character-set utilizado pelos esquemas(schemas) do MySQL:
1 | mysql> select schema_name, default_collation_name, default_character_set_name from information_schema.schemata; |
Também podemos utilizar funções fornecidas pelo MySQL que consultam as informações do information_schema. Segue abaixo algumas delas abaixo:
1 2 3 4 5 6 7 | 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`; |
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.
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:
1 | mysql> call rel_schema('sakila')\G |
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”.
1 | mysql> call relatorioschemas()\G |
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.
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…
Cláudio,
Obrigado por visitar nosso blog e estou preparando um material de MSSQL Server que em breve estarei postando.
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).
Muito bom Emerson.
Mas o link para download dos scripts parece não estar funcionando.
http://sdrv.ms/PcsdR2
Boa noite. Muito obrigado por prestigiar o nosso trabalho. Infelizmente não temos mais os arquivos.