Ao longo dos anos fui acumulando notas sobre operações rápidas e frequentes que precisava de executar numa série de tecnologias. Até agora, estas receitas têm sido apenas para minha referência e tentam ser a documentação que eu gostaria de ver online acerca de como fazer rapidamente a tarefa X na tecnologia Y. Hoje decidi partilhar algumas notas sobre uma dessas tecnologias, o sistema de gestão de bases de dados MySQL.

Nota: Estas notas são válidas apenas na data de escrita deste artigo (ver acima) e não necessariamente para todas as versões do MySQL, porque as coisas mudam continuamente. Estou a testar estas receitas de novo no MySQL 5.7.17 no macOS.

Nota 2: Tu és responsável pelos teus dados. Vê tudo o que estiver neste artigo com um olho crítico e aplica-as receitas apenas após as teres testado. E, nunca é demasiado dizer, faz backups!

O que podes aprender com este post

  • Como instalar, configurar e arrancar um servidor MySQL no macOS
  • Comandos rápidos para fazer administração
  • Quem és tu na consola MySQL?
  • Como ver as perguntas que estão a correr e as estatísticas
  • Como matar uma pergunta pendurada
  • Como achar as perguntas lentas
  • Quantas perguntas estão a correr e quantas são ligações em pausa?
  • Como estabelecer o tempo limite para as perguntas correrem
  • Como estabelecer o número máximo de ligações na consola MySQL
  • Como estabelecer o número máximo de ligações num ficheiro de configuração
  • Como determinar o número máximo de ligações concorrentes por utilizador que ocorreram neste servidor
  • Como configurar a buffer pool do motor Innodb
  • Como estabelecer o set de caracteres no MySQL
  • Como saber quais as maiores 10 tabelas em tamanho
  • Como saber quais as tabelas com updates feitos há mais tempo
  • Como saber qual o tamanho dos dados (em MB) das diferentes bases de dados
  • Como descobrir todos os utilizadores MySQL e as suas permissões
  • Como descobrir as permissões do utilizador MYUSER
  • Como descobrir as permissões do utilizador ‘website’ no IP ‘1.1.1.1’
  • Como dar todas as permissões possíveis ao utilizador ‘myuser’ no localhost na base de dados ‘magento’
  • Como revogar permissões
  • Como fazer reset da password do utilizador MYUSER
  • Como fazer reset da password do root
  • Como estabelecer quotas para os utilizadores
  • Como importar dados em massa para o MySQL
  • Como exportar uma tabela para um ficheiro csv
  • Como extrair a DDL (definições de criação) de uma tabela
  • Como fazer backups
  • Como escrever uma sessão MySQL para um ficheiro de logs
  • Como verificar se uma tabela está saudável, fragmentada e como repará-la

Vamos começar pelo princípio

Como instalar, configurar e arrancar um servidor MySQL no macOS

  1. Se estiveres só a testar isto e não tiveres um servidor MySQL onde te ligar podes fazer download para Mac daqui, descompactas o ficheiro DMG e clicas em “seguinte” até estar instalado.
  2. Infelizmente o pacote não cria “aliás” para os comandos por isso precisamos de o fazer directamente no terminal adicionando o seguinte ao .bash_profile:
echo "alias mysql=/usr/local/mysql/bin/mysql" >> ~/.bash_profile
echo "alias mysqladmin=/usr/local/mysql/bin/mysqladmin" >> ~/.bash_profile
echo "alias mysqldumpslow=/usr/local/mysql/bin/mysqldumpslow" >> ~/.bash_profile
echo "alias mysqldump=/usr/local/mysql/bin/mysqldump" >> ~/.bash_profile
source ~/.bash_profile
  1. A única configuração que vamos fazer por agora é definir o utilizador e password (que foi gerada durante a instalação) no ficheiro .my.cnf. Isto obviamente não é seguro para ambientes de produção, mas para experimentarmos, poupa-nos ter de escrever um utilizador e password de cada vez. Faz o seguinte: vim ~/.my.cnf e adiciona isto ao ficheiro:
[client]
user=root
password=yourSecurePassword

​ Por uma questão de segurança por favor muda as permissões no ficheiro para que fique acessível a ti: chmod 0600 ~/.my.cnf.

  1. Agora vamos arrancar o servidor. Vai a System Preferences e podes ver um novo ícone na linha do fundo chamado MySQL, clica aqui e depois em Start MySQL Server (escreve a tua password quando te pedir)

  2. Volta ao terminal e escreve mysql e voilá! Estamos no MySQL!

  3. A password que foi gerada pelo pacote de instalação é temporária pelo que a minha recomendação é mudá-la assim que entrares assim:

    mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('myNewPassword');
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    

    A seguir a isto podes actualizar o .my.cnf com a password nova.

Comandos rápidos para fazer administração

# Mostrar todas as bases de dados
show databases;

# Mostrar o SQL usado para a criação de uma tabela
show create table <table>;

# Ver todas as variáveis de ambiente
show global status;

# Definir o conteúdo de uma variável global
set global <VARIABLE_NAME>=<VALUE>;

# Obter o conteúdo de todas as variáveis começando com 'wait'
show global variables like 'wait%';

# Localizar todos os usuários nesta base de dados
select User, Host from mysql.user;

Quem és tu na consola MySQL?

Não quero virar este post em algo filosófico, mas se a certa altura te esqueceres com que utilizador te ligaste, podes fazer isto para descobrir quem és:

SELECT USER(),CURRENT_USER();

O USER() reporta como tentaste autenticar-te no MySQL O CURRENT_USER() reporta como é que te conseguiste autenticar no MySQL

Sobre perguntas/queries

Como ver as perguntas que estão a correr e as estatísticas

mysqladmin proc stat --verbose
+----+------+-----------+----+---------+------+----------+-----------------------+
| Id | User | Host      | db | Command | Time | State    | Info                  |
+----+------+-----------+----+---------+------+----------+-----------------------+
| 10 | root | localhost |    | Query   | 0    | starting | show full processlist |
+----+------+-----------+----+---------+------+----------+-----------------------+
Uptime: 404  Threads: 1  Questions: 11  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.027

Este comando permite-nos ver o que se está a passar sem termos que nos ligar à consola MySQL. Isto dá-nos todas as perguntas que estão a descorrer, há quanto tempo é que o servidor está a correr, quantas perguntas estão lentas e quantas perguntas estão a ser executadas por segundo, se uma pergunta ficou pendurada e mais algumas coisas.

Como podes ver na coluna Info , um dos comandos que está a ser encapsulado é o

mysql> show full processlist;

Isto significa que podes fazer exactamente a mesma coisa na consola MySQL.

Como matar uma pergunta pendurada

Imagina que, da lista acima, tens uma pergunta na sua lista que está a demorar imenso tempo (em segundos na coluna Time). Então podes simplesmente matá-la como se fosse um processo no teu Mac.

mysql> show full processlist;
+----+------+-----------+------+---------+------+------------+-----------------------+
| Id | User | Host      | db   | Command | Time | State      | Info                  |
+----+------+-----------+------+---------+------+------------+-----------------------+
| 11 | root | localhost | NULL | Query   |    0 | starting   | show full processlist |
| 12 | root | localhost | NULL | Query   |   17 | User sleep | select sleep(100)     |
+----+------+-----------+------+---------+------+------------+-----------------------+
2 rows in set (0.00 sec)
mysql> kill 12;
Query OK, 0 rows affected (0.00 sec)

Como achar as perguntas lentas

O MySQL não só vem com uma opinião sobre o que significa uma “pergunta lenta”, mas também vem com uma pequena ferramenta fixe para analisar os ficheiros de log de perguntas lentas e mostrar-te um resumo chamado mysqldumpslow.

Da documentação MySQL:

O log de perguntas lentas consiste em instruções SQL que demoraram mais de long_query_time segundos a serem executadas e exigiram pelo menos min_examined_row_limit linhas a serem examinadas. Os valores mínimo e padrão de long_query_time são respectivamente 0 e 10.

E uma ressalva:

Por omissão, as instruções administrativas não são registradas neste log, assim como as consultas que não usam índices para pesquisas. Esse comportamento pode ser alterado usando o log_slow_admin_statements e log_queries_not_using_indexes

Por omissão, as perguntas lentas não são registadas nos logs. Vamos ativá-las!

# Ativar o log de perguntas lentas
mysql> set global slow_query_log='ON';
# No máximo de 5 segundos
mysql> set global long_query_time='5';
# Estes valores são válidos APENAS até fazeres restart do servidor de base de dados! 
# Uma alternativa é fazê-lo no ficheiro de configuração
# Se ainda não o criaste, cria o ficheiro
sudo touch /etc/my.cnf
# Muda o owner do ficheiro para o teu utilizador
sudo chown `whoami` /etc/my.cnf
# Adiciona o conteúdo
echo "[mysqld]" >> /etc/my.cnf
echo "slow_query_log=1" >> /etc/my.cnf
# Tu também podes ativar o seguinte, mas cuidado que o ficheiro de logs pode crescer muito rapidamente
echo "log_queries_not_using_indexes=1" >> /etc/my.cnf
echo "log_slow_admin_statements=1" >> /etc/my.cnf

Se quiseres saber para onde o log vai:

mysql> show global variables like 'slow_query_log_file';
+---------------------+----------------------------------------------+
| Variable_name       | Value                                        |
+---------------------+----------------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log |
+---------------------+----------------------------------------------+
1 row in set (0.00 sec)

Agora podes testar a configuração!

mysql> select sleep(11);
+-----------+
| sleep(11) |
+-----------+
|         0 |
+-----------+
1 row in set (11.01 sec)

E depois fazes: sudo tail -f /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log

/usr/local/mysql/bin/mysqld, Version: 5.7.17 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2017-04-09T12:57:07.073262Z
# User@Host: root[root] @ localhost []  Id:     5
# Query_time: 11.005275  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1491742627;
select sleep(11);

Agora podes usar o mysqldumpslow para veres um resumo simpático das perguntas mais lentas da tua base de dados (assim que tiveres algumas):

sudo /usr/local/mysql/bin/mysqldumpslow /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log

# Para saber as 10 perguntas mais lentas por contagem
sudo /usr/local/mysql/bin/mysqldumpslow -t 10 -s c /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log

# Para saber as 10 perguntas mais lentas por tempo gasto
sudo /usr/local/mysql/bin/mysqldumpslow -t 10 -s t /usr/local/mysql/data/<YOUR_LAPTOP>-slow.log

Quantas perguntas estão a correr e quantas são ligações em pausa?

# Perguntas a correr
mysqladmin processlist |wc -l 
# Perguntas a dormir
mysqladmin processlist |grep -i sleep|wc -l 

Configuração

Como estabelecer o tempo limite para as perguntas correrem

# Qual é o tempo máximo para uma pergunta correr por omissão?
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)
# Isto significa 8 horas!
# Vamos mudar isto para 10 minutos
mysql> set global wait_timeout=600;
Query OK, 0 rows affected (0.00 sec)

Como estabelecer o número máximo de ligações na consola MySQL

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> set global max_connections=170;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 170   |
+-----------------+-------+
1 row in set (0.00 sec)

# Atenção! Este valor só é válido até o servidor de base de dados ser reiniciado!

Como estabelecer o número máximo de ligações num ficheiro de configuração

# Se estiveres num Mac, tens de criar o ficheiro e adicionar o parâmetro max_connections a ele
sudo touch /etc/my.cnf
# Muda o owner para o teu utilizador
sudo chown `whoami` /etc/my.cnf
# Adiciona o conteúdo
echo "[mysqld]" >> /etc/my.cnf
echo "max_connections=505" >> /etc/my.cnf
# Vai a System Preferences e reinicia o MySQL
# E faz a pergunta de novo
mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 505   |
+-----------------+-------+
1 row in set (0.00 sec)

Como determinar o número máximo de ligações concorrentes por utilizador que ocorreram neste servidor

mysql> show global status like "%max_used_connections%";

Como configurar a buffer pool do motor Innodb

Idealmente vais querer o tamanho do buffer pool igual ao tamanho dos dados dos ficheiros innodbs. Se não tiveres espaço suficiente para isso:

Num servidor dedicado de bases de dados, a sugestão é fazer com que o tamanho do buffer pool seja 80% do tamanho da memória física da máquina.

Pode estabelecer qual o tamanho do buffer pool no ficheiro my.cnf assim:

[mysqld]
...
innodb_buffer_pool_size = 2G
...

Ou dinamicamente (a partir doMySQL v.5.7.5) na consola MySQL assim:

# 2G x 1024 x 1024 x 1024 to get bytes
mysql> set global innodb_buffer_pool_size=2147483648;

Como estabelecer o set de caracteres no MySQL

# Adiciona na secção [mysqld] do /etc/my.cnf:
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'

Descobrir informação

Como saber quais as maiores 10 tabelas em tamanho

SELECT TABLE_SCHEMA, TABLE_NAME,data_length/1024*1024 FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` !='information_schema' AND `TABLE_SCHEMA` !='mysql' ORDER BY `data_length` DESC LIMIT 10; 

Como saber quais as tabelas com updates feitos há mais tempo

SELECT CONCAT (`TABLE_SCHEMA`, "." , `TABLE_NAME`) as name , `UPDATE_TIME` FROM `TABLES` WHERE `UPDATE_TIME` IS NOT NULL ORDER BY `UPDATE_TIME` LIMIT 10; 

Como saber qual o tamanho dos dados (em MB) das diferentes bases de dados

SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

Utilizadores

Como descobrir todos os utilizadores MySQL e as suas permissões

SELECT * from `INFORMATION_SCHEMA`.`USER_PRIVILEGES`;

Como descobrir as permissões do utilizador MYUSER

SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES where grantee like '%MYUSER%';

Como descobrir as permissões do utilizador ‘website’ no IP ‘1.1.1.1’

SHOW GRANTS FOR website@'1.1.1.1'; 

Como dar todas as permissões possíveis ao utilizador ‘myuser’ no localhost na base de dados ‘magento’

GRANT ALL PRIVILEGES ON magento.* TO 'myuser'@'localhost';

Como revogar permissões

REVOKE SELECT,INSERT,UPDATE,DELETE ON mydatabase.* FROM 'myuser'@'localhost';

Como fazer reset da password do utilizador MYUSER

use mysql;
set password for '<MYUSER>'@'%' = PASSWORD('myPass');
flush privileges;

Como fazer reset da password do root

# Adicionoa esta linha ao início do /etc/my.cnf (secção [mysqld]):
init_file=/var/lib/mysql/mysql.init

# Cria o ficheiro /var/lib/mysql/mysql.init com os seguintes conteúdos:
SET PASSWORD FOR 'root'@'localhost's=PASSWORD('mynewpassword');
FLUSH PRIVILEGES;

# Muda o owner para o utilizador MySQL local
chown <MYSQL_USER> /var/lib/mysql/mysql.init

# Reinicia o servidor
# Apaga o ficheiro mysql.init e remove a linha com o init_file do /etc/my.cnf

Como estabelecer quotas para os utilizadores

Isto é feito através de GRANTS que o utilizador tem na base de dados. Podes controlar coisas como as seguintes: MAX_CONNECTIONS_PER_HOUR, MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_USER_CONNECTIONS.

Por exemplo:

GRANT ALL ON mydatabase.* TO 'myuser'@'%' WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 10;

Importação/Exportação

Como importar dados em massa para o MySQL

# O truque aqui é desabilitar os índices durante a importação em massa dos dados
ALTER TABLE table_name DISABLE KEYS;
# ...importar...
ALTER TABLE table_name ENABLE KEYS;

Como exportar uma tabela para um ficheiro csv

mysql <MYDATABASE> -e "SELECT * FROM <MYTABLE>" | sed 's/\t/","/g;s/^/"/;s/$/"/;' > <MYTABLE>.csv

Como extrair a DDL (definições de criação) de uma tabela

mysqldump -f -d --single-transaction <MYTABLE> > ~/Desktop/createMyTable.sql

Como fazer backups

# Fazer backup a todas as bases de dados
mysqldump --all-databases > ~/Desktop/fullBackup.sql

# Fazer backup só à base de dados mydb
mysqldump --databases mydb > ~/Desktop/mydbBackup.sql

# Fazer backup só à tabela mytable da base de dados mydb
mysqldump mydb mytable > ~/Desktop/mytableBackup.sql

Como escrever uma sessão MySQL para um ficheiro de logs

mysql> tee mysession.log
Logging to file 'mysession.log'

Outros

Como verificar se uma tabela está saudável, fragmentada e como repará-la

mysql> check table <MYTABLE>;
mysql> repair table <MYTABLE>;

Espero que isto ajude!

Se encontrares erros nestas receitas, por favor levanta um issue aqui.