Posted on: July 24, 2020 07:43 PM

Posted by: Renato

Categories: mysql linux

Views: 4075

Comandos úteis do MySQL/MariaDB  

Alguns comandos úteis de MySQL / MariaDB, eu sou SysAdmin e quase não tenho a demanda de administrar banco de dados MySQL/MariaDB, porém sempre surge alguma tarefa que demande algo como: dump do banco, criar/excluir usuário, alterar permissão de usuário, migrar, acessar remoto e entre outras… Enfim é sempre um inferno lembrar dos comandos né? segue abaixo os comandos que eu mais utilizo. 🙂

Acessando o MySQL/MariaDB

Acessando localhost
mysql -u [nomedousuario] -p [senha];

# mysql -u nomedousuario -p 

Acessando remoto
mysql -u [nomedousuario] -p [senha] -h [dns ou ip 192.168.1.20];

# mysql -u nomedousuario -p -h 192.168.1.20

Gerenciamento de usuários

“Lembrando que sempre que for rodar um comando no mysql, precisa colocar ponto e virgula [;] no final de cada comando.”

Listando usuários (precisa estar logado).

mysql>  SELECT User FROM mysql.user;
+------------------+
| User             |
+------------------+
| debian-sys-maint |
| mysql.session    |
| mysql.sys        |
| gerson.carneiro  |
| root             |
+------------------+
Query OK, 0 rows affected (0.00 sec)

Listando os usuário e permissão de acesso (local/ip/todos).

mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| gerson           | %         |
| linuxnaweb       | 10.8.0.5  |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
Query OK, 0 rows affected (0.00 sec)

Gerenciamento de usuários

Algumas informações relevantes!
No comando DROP USER ou CREATE USER ‘gerson’@’%’; após o @ pode ser definido alguns parâmetros, como:
% => acessa de qualquer host;
localhost => para acessar somente localhost;
192.168.1.2 => para acessar de um endereço IP específico;

Criando usuário para acessar local:

mysql> CREATE USER 'nomedousuario'@'localhost' IDENTIFIED BY 'senhadousuario';
Query OK, 0 rows affected (0.00 sec)

Criando usuário para ser acessado por qualquer host:

mysql> CREATE USER 'nomedousuario'@'%' IDENTIFIED BY 'senhadousuario';
Query OK, 0 rows affected (0.00 sec)

Criando usuário acessar de um ip específico:

mysql> CREATE USER 'nomedousuario'@'10.8.0.5' IDENTIFIED BY 'senhadousuario';
Query OK, 0 rows affected (0.00 sec)

Alterando senha de usuário:

mysql> SET PASSWORD FOR 'teste'@'localhost' = '123456';
Query OK, 0 rows affected (0.00 sec)

Excluindo usuário:

mysql> DROP USER 'gerson'@'%';
Query OK, 0 rows affected (0.00 sec)

Ou

mysql> DELETE FROM user WHERE user = 'gerson';

Renomeando/Alterando usuário:

mysql> RENAME USER 'gerson'@'%' TO 'teste'@'127.0.0.1';

Lembrando que sempre que executar os comandos para aplicar as modificações, certifique-se que executou o comando: FLUSH PRIVILEGES;

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Permissão de usuários

O MySQL/MariaDB trabalha com as seguintes permissões:

Para dar uma permissão a um usuário específico, você pode utilizar esta estrutura:

GRANT [tipo de permissão] ON [nome da base de dados].[nome da tabela] TO ‘[nome do usuário]’@'localhost’;

Permissão total em todos os bancos:

GRANT ALL PRIVILEGES ON * . * TO 'linuxnaweb'@'localhost';

Gerenciando databases

Criando database:

mysql> CREATE DATABASE nomedatadabase;
Query OK, 1 row affected (0.00 sec)

Acessando uma database:

mysql> USE nomedadatabase;
Database changed

Listando database:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| nomedadatabase     |
+--------------------+
Query OK, 0 rows affected (0.00 sec)

Deletando database:

mysql> DROP DATABASE teste;
Query OK, 0 rows affected (0.00 sec)

Listando tabelas de uma database:

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
... >> dei uma resumida, porque fica muito grande.
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

Gerenciando processos no MySQL

Listando processos:

mysql> SHOW FULL PROCESSLIST\G;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave
       I/O thread to update it
Info: NULL

Listando os processos em lista:

mysql> SHOW FULL PROCESSLIST;
+----+------+-----------+-------+---------+------+----------+-----------------------+
| Id | User | Host      | db    | Command | Time | State    | Info                  |
+----+------+-----------+-------+---------+------+----------+-----------------------+
| 10 | root | localhost | mysql | Query   |    0 | starting | show full processlist |
+----+------+-----------+-------+---------+------+----------+-----------------------+
1 row in set (0.00 sec)

Listando processos em uma database específica:

mysql> SELECT * from INFORMATION_SCHEMA.PROCESSLIST where db = 'nomedadatabase';

Listando processos e ordenando com o status não “dormindo” sleep:

mysql> SELECT * from information_schema.processlist where COMMAND != 'Sleep'  order by time;

Matando processos:

mysql> kill 12;
Query OK, 0 rows affected (0.00 sec)

Matando todos os processos que estão ativos no momento

SELECT concat(‘KILL ‘,id,‘;’) from INFORMATION_SCHEMA.PROCESSLIST
where time > 1 and command != “sleep”

Fazendo Backup e Restore

Fazendo backup da database:
Parâmetros utilizados:
mysqldump -u [usuário] -p <senha> [nomedadatabase] > [nomedoarquivo]-$(date +%F).sql

# mysqldump -u root -p mysql > dump-$(date +%F).sql
Enter password: 
root@ubuntu-16-04-06:~# ls -lh
total 1.2M
-rw-r--r-- 1 root root 1.2M Nov 24 07:48 dump-2019-11-24.sql

Restaurando dump/backup:
Foram utilizados os mesmos parâmetros do comando acima.

# mysql -u root -p nomedadatabase < dump-2019-11-24.sql

Comando interessante! 🙂

Executar comandos no host que o mysql está instalado:
O comando system, permite que execute os comandos no host em que está instalado.

mysql> system ifconfig;
enp0s3    Link encap:Ethernet  HWaddr 08:00:27:7e:0f:dc  
          inet addr:192.168.68.108  Bcast:192.168.68.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe7e:fdc/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:21068 errors:0 dropped:0 overruns:0 frame:0
          TX packets:6188 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:27070338 (27.0 MB)  TX bytes:566874 (566.8 KB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:176 errors:0 dropped:0 overruns:0 frame:0
          TX packets:176 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1 
          RX bytes:13296 (13.2 KB)  TX bytes:13296 (13.2 KB)

É isso galera, espero que ajude, tentei reunir em um só artigo todos os comandos que são mais utilizados, mas caso falte algum que utiliza bastante no dia-dia, coloque nos comentários que eu irei incluir no artigo! 😉

Renato

Developer

No comments yet! Be the first to comment