Salveee manos! Neste mini artigo irei compartilhar com vocês 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. 🙂

Primeiro iremos instalar o MySQL e MariaDB

Instalando MySQL/MariaDB Server

Família Debian:

# apt-get update; apt-get install mysql-server -y
# apt-get update; apt-get install mariadb-server -y

Família Red Hat:

# yum update; yum install mysql-server -y
# yum update; yum install mysql-server -y

Instalando MySQL/MariaDB Client

Para executar os comandos que acessem o Banco, é necessário instalar o mysql-client.

Família Debian:

# apt-get update; apt-get install mysql-client -y
# apt-get update; apt-get install mariadb-client -y

Família RedHat:

# yum update; yum install mysql-client -y
# yum update; yum install mariadb-client -y

Hardening do MySQL:

Iremos melhorar um pouco a segurança do MySQL, iremos utilizar o mysql_secure_installation, lógico que tem diversas formas de melhorar a segurança do mysql, mas estou ensinando o básico.

# mysql_secure_installation
 
Enter current password for root (enter for none): # Pressione Enter para definir senha para o root.
Set root password [Y/n] # Pressione Y para definir uma senha
New password: # (confirme a sua senha nova e pressione Enter)
Re-enter new password: # (insira a senha nova, novamente e pressione Enter)
Remove anonymous users? [Y/n] – Y # Para remover o usuário anonimo de testes.
Disallow root login remotely? [Y/n] – Y # Para desabilitar o acesso remoto ao banco de dados, deixar habilitado somente localhost.
Remove test database and access to it [Y/n] – Y # Remover o banco de dados de teste.
Reload privilege tables now? [Y/n] - Y  # Atualizar privilégios das tabelas.

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)

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:

Manipulação de Dados
SELECT Somente Leitura
INSERT Inserção de Dados
UPDATE Atualização de Dados
DELETE Remoção de Dados

Manipulação de Tabelas
CREATE Criação de novas Tabelas/Bases
ALTER Modificação de Tabelas/Colunas
DROP Remocação de Tabelas/Bases
Para dar uma permissão a um usuário específico, você pode utilizar esta estrutura:

Permissão total em todos os bancos:
GRANT [tipo de permissão] ON [nome da base de dados].[nome da tabela] TO ‘[nome do usuário]’@‘localhost’;

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 [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! 😉

Curta as nossas redes sociais e compartilhe com seus brothers. o/

Referências

https://dev.mysql.com/doc/refman/5.7/en/
https://www.digitalocean.com/community/tutorials/como-criar-um-novo-usuario-e-conceder-permissoes-no-mysql-pt

Agradecimentos! Valeu Alê pela ajuda =D