Introdução
Vamos aprender a trabalhar com manipulação de dados em um banco de dados, esse documento não visa tornar nenhuma pessoa um dba, apenas mostra conceitos de como trabalhar com Database, fazer migração, criação de tabelas, etc.
MySql vs MariaDB
O MariaDb é licenciado pela GPL, enquanto que o MySql tem dupla licença. Cada um lida com pools de threads de maneira diferente. O MariaDB por ser open source se torna melhor na maioria dos casos.
Os passos a seguir servem tanto para MySql quanto para o MariaDB, já que mariadb é um fork do MySql.
O que é SQL?
O SQL é a linguagem usada para geração, manipulação e recuperação de dados dentro de um banco de dados relacional. O SQL é dividido em algumas categorias:
Schema Statement
São instruções usadas para criar objetos de bando de dados (montar a base ou esqueleto da tabela), aqui entram a criações de tabelas, índices, restrições e muito mais.
Data Statement
São instruções usadas para criar, manipular e recuperar dados armazenados no banco.
Transaction Statement
São instruções usadas para iniciar, encerrar e desfazer transações.
Instruções de dados SQL
O SQL possui 4 instruções de dados, sendo: insert, update, delete e select.
Insert
A instrução insert é usada para inserir dados numa tabela, é através dessa instrução que vamos adicionando dados na tabela.
O insert tem a seguinte estrutura:
# Estrutura:
INSERT INTO tabela (colunas)
VALUES (valores)
#### Exemplo ####
## Listando a estrutura da tabela:
mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.002 sec)
## Listando os 2 ultimos valores:
mysql> select * from
-> (select * from employees ORDER BY emp_no DESC LIMIT 2)
-> sub ORDER BY emp_no ASC;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 499998 | 1956-09-05 | Patricia | Breugel | M | 1993-10-13 |
| 499999 | 1958-05-01 | Sachin | Tsukuda | M | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.001 sec)
# Inserindo Dados:
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
VALUES ('500000', '1996-09-05', 'Joao', 'Nogueira', 'M', '2021-01-05');
Query OK, 1 row affected (0.009 sec)
## Listando os 2 ultimos valores:
mysql> select * from
-> (select * from employees ORDER BY emp_no DESC LIMIT 2)
-> sub ORDER BY emp_no ASC;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 499999 | 1958-05-01 | Sachin | Tsukuda | M | 1997-11-30 |
| 500000 | 1996-09-05 | Joao | Nogueira | M | 2021-01-05 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.001 sec)
Update
Caso seja necessário fazer alguma alteração nas informações que já estejam na tabela, usamos a instrução update, com isso vamos mudar apenas o sobrenome do funcionário que adicionamos:
# Atualizando o sobrenome:
mysql> UPDATE employees
-> SET last_name = 'Nogueira Junior'
-> WHERE emp_no = '500000' AND first_name = 'Joao';
Query OK, 1 row affected (0.011 sec)
Rows matched: 1 Changed: 1 Warnings: 0
## Listando os 2 ultimos valores:
mysql> select * from
-> (select * from employees ORDER BY emp_no DESC LIMIT 2)
-> sub ORDER BY emp_no ASC;
+--------+------------+------------+-----------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------------+--------+------------+
| 499999 | 1958-05-01 | Sachin | Tsukuda | M | 1997-11-30 |
| 500000 | 1996-09-05 | Joao | Nogueira Junior | M | 2021-01-05 |
+--------+------------+------------+-----------------+--------+------------+
2 rows in set (0.001 sec)
Delete
A instrução delete é usada para deletar um dado da tabela, vamos deletar o funcionário que acabamos de adicionar:
# Deletando:
mysql> DELETE from employees
-> WHERE emp_no = '500000' AND first_name = 'Joao';
Query OK, 1 row affected (0.010 sec)
## Listando os 2 ultimos valores:
mysql> select * from
-> (select * from employees ORDER BY emp_no DESC LIMIT 2)
-> sub ORDER BY emp_no ASC;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 499998 | 1956-09-05 | Patricia | Breugel | M | 1993-10-13 |
| 499999 | 1958-05-01 | Sachin | Tsukuda | M | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.001 sec)
Select
A instrução select já vem sendo usada, sua função é exibir informações que estejam nas tabelas.
O que é um banco de dados?
Um banco de dados é apenas um conjunto de informações relacionadas umas com as outras.
Tipos de chaves de um banco de dados
No banco de dados, mais precisamente nas tabelas, trabalhamos com alguns tipos de chaves.
Chave primária
É uma dado que identifica de forma única na tabela uma informação, normalmente usamos algo contendo a informação id, como account_id, user_id e por ai vai.
As colunas de uma primária nunca devem ter seus valores alterados após a atribuição inicial.
Para definir uma chave primária, usamos o exemplo abaixo:
CONSTRAINT <NOME> PRIMARY KEY (<COLUNA>)
NOME é o nome que vamos dar e COLUNA é qual a coluna vai ser responsável por ser chave primária.
EX: CONSTRAINT pk_pessoa PRIMARY KEY pessoa_id
Chave composta
É uma chave primária, mas consiste em duas ou mais colunas.
Chave estrangeira
São informações de uma tabela que levam para outras tabelas, são como chaves primárias de uma tabela dentro de outra tabela, e com essa informação podemos mapear os dados dentre várias tabelas.
Para definir uma chave estrangeira, usamos o exemplo abaixo:
CONSTRAINT <NOME> FOREIGN KEY (<COLUNA>)
Uma tabela com uma chave entrangeira só vai poder adicionar uma informação na tabela se uma referencia não existir na tabela que estamos pegando a COLUNA.
Tipos de dados
Tanto no SQL quanto em linguagens de programação, existem tipos de dados, ou tipos de variáveis como é mais comumente chamado, cada tipo permite uma aplicação diferente.
Dados do tipo caractere
Os dados do tipo caractere podem ser armazenados como strings de tamanho fixo ou variável, a diferença é:
Strings de tamanho fixo
São sempre preenchidas a direita com espaços e sempre consomem a mesma quantidade de bytes para armazenar.
Strings de tamanho variável
Nem sempre consomem a mesma quantidade de bytes para armazenar e não são preenchidas a direita com espaços.
Existem dois tipos de variáveis para caracteres, são eles:
CHAR
Possui o valor máximo de colunas sendo 255 bytes. Indicado quando se sabe o tamanho da string que vai ser armazenado como abreviação de estado (SP, MG, RJ etc).
VARCHAR
Possui o valor máximo de colunas sendo 65.535 bytes, é muito indicado para armazenar e-mails, documentos XML e etc. Indicado quando não se sabe o tamaho da string.
Segue um exemplo de como definir os dados acima:
VARCHAR(40)
CHAR(15)
No exemplo acima, os valores são a quantidade máxima de caracteres que serão armazenados, com a diferença que no char(15), caso seja armazenado apenas 1 caractere, serão reservados mais 14 espaços vazios, enquanto que no varchar será alocado apenas 1 espaço para o caractere armazenado.
Conjunto de caracteres
Aqui entram o tipo de caractere que poderá ser armazenado no sentido de linguagem, pois existem muitos idiomas e podemos configurar qual tipo de caractere vai ser armazenado (seria como configurar qual idioma será reconhecido ou algo do tipo).
# Listar os caracteres reconhecidos pelo banco:
MariaDB [(none)]> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.002 sec)
# Listar o charset dos bancos criados dentro do SQL:
MariaDB [(none)]> SELECT SCHEMA_NAME 'database', default_character_set_name 'charset',
-> DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;
+--------------------+---------+--------------------+
| database | charset | collation |
+--------------------+---------+--------------------+
| information_schema | utf8 | utf8_general_ci |
| mysql | utf8mb4 | utf8mb4_general_ci |
| performance_schema | utf8 | utf8_general_ci |
| foreign_sales | utf8 | utf8_general_ci |
| employees | utf8mb4 | utf8mb4_general_ci |
+--------------------+---------+--------------------+
5 rows in set (0.005 sec)
# Listar todas as colunas de uma tabela (e ver o charset 'Collation'):
mysql> SHOW FULL COLUMNS FROM departments;
+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| dept_no | char(4) | utf8mb4_general_ci | NO | PRI | NULL | | select,insert,update,references | |
| dept_name | varchar(40) | utf8mb4_general_ci | NO | UNI | NULL | | select,insert,update,references | |
+-----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.004 sec)
Durante a criação de uma tabela, podemos específicar o charset de uma coluna com o exemplo abaixo:
varchar(10) character set utf8mb4
Para definir isso para todo o banco de dados, você pode colocar no arquivo de configuração, na sessão [mysqld] ou através do comando create database foreign_sales character set utf8mb4.
Dados de Texto
É usado quando o varchar não da mais conta, pois com o tipo texto, podemos exceder o limite de 64KB do varchar, existem alguns tipos de texto, são eles:
| Tipo de texto | Numero máximo de bytes |
|---|---|
| Tinytext | 255 |
| Text | 65.535 |
| Mediumtext | 16.777.215 |
| Longtext | 4.294.967.295 |
Dados numéricos
Os dados do tipo numérico não se classificam apenas pela quantidade de informações que podem guardar, mas o tipo de números que podem guardar (se tem pontuação ou não).
Existem 2 tipos de dados principais que vamos querer guardar, são eles:
Int
São números inteiros, ou seja, não aceitam casas decimais.
Para números do tipo Int podemos marcar eles como sendo
unsigned, isso indica ao servidor que os dados armazenados serão maiores ou iguais a 0.Float
Esse tipo de dado permite o uso de casas decimais, como 10.35.
Boolean
Similar ao mesmo tipo em programação, seu estado se define a True (verdadeiro) ou False (falso), mas nesse caso, utiliza números para essa simbologia, como 1 ou 0.
O valores do tipo Int possuem vários tipos, onde cada tipo armazena uma grande quantidade de valores:
| Tipo | Escopo com sinal | Escopo sem Sinal |
|---|---|---|
| Tinyint | -128 a 127 | 0 a 255 |
| Smallint | -32.768 a 32.767 | 0 a 65.535 |
| Mediumint | -8.388.608 a 8.388.607 | 0 a 16.777.215 |
| Int | -2.147.483.648 a 2.147.483.647 | a 0 4.294.967.295 |
| Bigint | -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807 | 0 a 18.446.744.073.709.551.615 |
Definindo valores:
# Criando dados do tipo INT, o número 3 indica apenas que alguns aplicativos que consultarem o banco vão exibir apenas 3 dígitos, mas podem ser armazenados muito mais que 3 dígitos.
int(3)
# Permitindo no máximo 4 digitos, dois a esquerda e dois a direita da vírgula:
float(4,2)
# Mesmo que o int, mas armazena uma grande quantidade de valores.
BIGINT
Dados temporais
Trata-se de dados como a hora e data do sistema. Existem alguns tipos como podemos ver abaixo:
| Tipo | Formato |
|---|---|
| Date | AAA-MM-DD |
| Datetime | AAA-MM-DD HH:MI:SS |
| Timestamp | AAA-MM-DD HH:MI:SS |
| Year | AAA |
| Time | HH:MI:SS |
| Linux Timestamp | NNNNNNNNN |
Pode-se usar os comandos abaixo para obter os valores mostrado na coluna Exemplos:
# Listando o Timestamp do Linux:
MariaDB [(none)]> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1641399860 |
+------------------+
1 row in set (0.000 sec)
# Listando o timestamp atual:
MariaDB [(none)]> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2022-01-05 16:23:00 |
+---------------------+
1 row in set (0.001 sec)
# Listando o time atual:
MariaDB [(none)]> SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 16:25:12 |
+--------------+
1 row in set (0.000 sec)
# Listando o date atual:
MariaDB [(none)]> SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2022-01-05 |
+--------------+
1 row in set (0.000 sec)
Instalação
Vamos instalar o MySQL, um sistema de banco de dados relacional..
## Para instalar o MySQL:
$ apt-get install mysql-server
## Para instalar o MariaDB:
$ apt-get install mariadb-server
# ou "yum install mariadb-server" caso esteja utilizando um sistema baseado em RedHat
## Configuração básica e primordial do MySQL:
$ sudo mysql_secure_installation
Set root password? [Y/n] n
Remove anonymous users? [Y/n] n
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] n
Reload privilege tables now? [Y/n] Y
# Estas foram as opções que eu escolhi para tornar o uso o mais fácil possível. Se quiser definir senhas e outras configurações, fique a vontade.
As perguntas feitas acima são:
1 - O Script nos apresenta o Validate Password Componnent, um componente do MySQL usado para validar senhas, verificando se as senhas são seguras.
2 - Você deve definir a força da senha, entre: 0 = low, 1 = medium e 3 = strong. Depois de escolher a força da senha, você deve definir uma senha.
3 - O script quer saber se deseja continuar com a senha fornecida.
4 - Informa que o MySQL tem um usuário anônimo, permitindo que qualquer pessoa consiga logar e que isso é apenas para teste, e pergunta se desejamos remover esse usuário.
5 - É informado que o root deve logar apenas em localhost (conectado localmente na máquina) e pergunta se desejamos remover o acesso root via rede.
6 - Pergunta se desejamos recarregar as tabelas de privilégio para certificar das mudanças imediatamente.
Instalar um Banco de Dados de Exemplo
Para facilitar os estudos, vamos fazer o download e importar para o banco uma base de dados e tabelas já criadas que podemos encontrar no GitHub.
# Baixa o db:
wget https://github.com/datacharmer/test_db/archive/master.zip
# Descompacte:
unzip master.zip
# Entre na pasta:
cd test_db-master
# Importe o db:
mysql -u root < employees.sql
# Listando os databases sem entrar no mysql (mariadb):
$ sudo mysqlshow
+--------------------+
| Databases |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
Passos iniciais com o banco
# Acessando o db:
$ sudo mysql -u root
## Caso tenha colocado senha, coloque a opção '-p':
$ sudo mysql -u root -p
# Entre no banco chamado emplyees:
MariaDB [(none)]> use employees;
Database changed
# Liste as tabelas existentes dentro do nosso banco de dados:
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.001 sec)
# Liste a estrutura da tabela, vamos ver informações contidas nas colunas de uma tabela:
mysql> describe departments;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no | char(4) | NO | PRI | NULL | |
| dept_name | varchar(40) | NO | UNI | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.003 sec)
# Liste algumas informações:
mysql> select dept_no,dept_name from departments;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
9 rows in set (0.002 sec)
# Estrutura: select <CAMPOS> from <TABELA>;
# Você pode mudar colocar um '*' no lugar de <CAMPOS>, assim ele vai mostrar todas as tabelas.
Replicação
Master/Slave - Assíncrona
A replicacao mais simples é chamado de master/slave.
O slave vai ler o master e pedir para sincronizar, esse tipo de conexao é chamada de assincrona, isso significa que caso o Slave fique fora do ar, ou a conexao caia, quando subir ele sincroniza de novo e baixa somente o que mudou.
SBR (Statement Base Replication)
É uma replicacao baseada em comando, ou seja, 'e gravado o comando em si no log binario, e quando o slave ler isso, ele vai executar o mesmo comando.
RBR (Row Base Replication)
É uma replicacao baseada em linha, ou seja, 'e criado um registro e esse registro 'e gravado no binlog, o slave copia o registro do binlog para o relaylog.
MBR (Mixed Base Replication)
Replicacao de base mista, o servidor vai decidir qual passo acima ser'a melhor, baseado em transacoes seguras ou inseguras.
Slave/Master - Assíncrono
Um Slave possui varios Masters e sincroniza com os bancos de dados de todos os masters, criando um sistemas de backup onde todo os bancos de todos os Masters ficam centralizalidos em apenas uma máquina.
PS.: Apenas os bancos de dados escolhidos são sincronizados.
Master/Slave - Semi-sincrono (via plugin)
Só habilita a escrita no banco do Master quando ao menos 1 Slave confirmar que está sincronizado, se nenhum dos Slave que temos confirmar, a escrita vai ficar pendente até dar erro por timeout ou até um Slave confirmar a sincronização.
Master/Master - Assíncrono
Um Master replica para outro Master, ainda temos os Masters criando o binlog em ambos os masters, e ao mesmo tempo, os masters lendo o relaylog, isso porque eles são Master e Slave ao mesmo tempo.
----------- -----------
| | | |
| Master 1 | | Master 2 |
| BinLog | | BinLog |
| | <-----------> | |
| Slave | | Slave |
| RelayLog | | RelayLog |
| | | |
----------- -----------
Vamos ver um exemplo da configuração em dois servidores:
- Server 1 (/etc/mysql/mysql.conf.d/mysqld.cnf)
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
binlog_do_db = banco
expire_logs_days = 10
max_binlog_size = 500M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
- Server 2 (/etc/mysql/mysql.conf.d/mysqld.cnf)
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
binlog_do_db = banco
expire_logs_days = 10
max_binlog_size = 500M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
Explicando algumas opções:
auto-increment-increment
O incremento normalmente é de 1 em 1, nesse caso, vamos incrementar o incremento, basicamente o incremento passa a ser de 2 em 2:
auto-increment-increment = 2
auto-increment-offset
Basicamente é o ID do primeiro registro, nesse caso será 1, mas no server 2 será 2:
auto-increment-offset = 1
Dessa forma, nunca teremos um registro em um servidor que tenha o mesmo id em outro, criando complicações e problemas no banco.
Server 1 Server 2
registro1 = id1 registro1 = id2
registro2 = id3 registro2 = id4
registro3 = id5 registro3 = id6
binlog_do_db
Diz qual o banco de dados vamos replicar.
Criar um usuário com os seguintes privilégios:
Server 1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'IP-Server2' IDENTIFIED BY 'SENHA';
Server 1> FLUSH TABLES WITH READ LOCK;
Server 1> SHOW MASTER STATUS;
## anotar master_log_file e master_log_pos
Server 2> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'IP-Server1' IDENTIFIED BY 'SENHA';
Server 2> FLUSH TABLES WITH READ LOCK;
Server 2> SHOW MASTER STATUS;
## anotar master_log_file e master_log_pos
Se nao funcionar:
# Server 1> create user 'repl'@'192.168.121.159' IDENTIFIED BY "wfepowponwpog";
# Server 1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.121.159';
# Informar no master 2 que ele é slave do master1:
Server 2> STOP SLAVE;
Server 2> CHANGE MASTER TO master_host='IP-server1', master_user='repl', master_password='xxx', master_log_file='mysql-bin.xxx', master_log_pos=xxx;
Server 2> START SLAVE;
# Informar no master 1 que ele é slave do master2:
Server 1> STOP SLAVE;
Server 1> CHANGE MASTER TO master_host='IP-server2', master_user='repl', master_password='senha', master_log_file='mysql-bin.xxx', master_log_pos=xxx;
Server 1> START SLAVE;
____________________________________________________________________________________________________
master1 = 192.168.121.159
master2 = 192.168.121.89
____________________________________________________________________________________________________
EXEMPLO DO SERVER 1
____________________________________________________________________________________________________
Server 1> create user 'repl'@'192.168.121.89' IDENTIFIED WITH mysql_native_password BY "wfepowponwpog";
Server 1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.121.89';
Server 1> FLUSH TABLES WITH READ LOCK;
Server 1> SHOW MASTER STATUS;
## anotar master_log_file e master_log_pos
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 726 | banco | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# Informar no master 1 que ele é slave do master2:
Server 1> STOP REPLICA;
Server 1> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.121.89', SOURCE_USER='repl', SOURCE_PASSWORD='wfepowponwpog', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=0;
Server 1> START REPLICA;
SHOW REPLICA STATUS \G
____________________________________________________________________________________________________
EXEMPLO DO SERVER 2
____________________________________________________________________________________________________
Server 2> create user 'repl'@'192.168.121.159' IDENTIFIED WITH mysql_native_password BY "wfepowponwpog";
Server 2> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.121.159';
Server 2> FLUSH TABLES WITH READ LOCK;
Server 2> SHOW MASTER STATUS;
## anotar master_log_file e master_log_pos
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 698 | banco | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# Informar no master 2 que ele é slave do master1:
Server 2> STOP REPLICA;
Server 2> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.121.159', SOURCE_USER='repl', SOURCE_PASSWORD='wfepowponwpog', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=0;
Server 2> START REPLICA;
SHOW REPLICA STATUS \G
Replicacao de dados (Group replication)
Temos vários Masters dentro de um grupo, quando algum Master desse grupo recebe uma escrita, o grupo precisa dizer se isso é válido ou não.
Replicacao de dados (InnoDB Cluster)
Ainda temos um grupo de servers, vamos ter um servidor com o MysQL Router, ele vai decidir qual servidor do grupo vai ser a instância primária, e com isso vai redirecionar as requisições para ela, muito boa para ter uma alta disponibilidade, a conexão do cliente é feita com o MysQL Router e não com servidor onde está o banco de dados.
Arquivos gerados pelo MySQL
Para isso, vamos analisar o conteúdo da pasta do mysql em /var/lib/mysql, que é a pasta padrão que o MySQL usa para manter os diversos arquivos.
| Arquivos | Descrição |
|---|---|
| auto.cnf | Se não existir, vai ser criado sempre que o servidor do mysql for iniciado. Se já existir, vai ser lido, nele temos o UUID do MySQL, usado principalmente em replicação (nao deve ser apagado). |
| binlog* | São logs do MySQL que contém além de tudo as transações e muitos outros logs (É muito usado para replicação). Em alguns crashs o server pode ler esses arquivos para se recuperar. Aqui podemos escolher 3 formatos de registros: - raw Mostra a linha do mysql que foi alterada. - Statement Mostra o comando que foi usado para fazer a alteração. - Mixed Usa os dois formatos acima. |
| *.pem | Certificado usado pelo Mysql no SSL. |
| *.err | Contém os erros do mysql. |
| ib_buffe_pool | Grava ponteiros, mapas para subir o server mais rapidamente. |
| ib_logfile | Contém instruções que alteram o SQL, assim caso tenha algum crash, o sistema consegue se recuperar. |
| ibdata | Onde os dados são propriamente gravados, relacionado ao Table Space. |
| ibtmp | Mesma coisa acima, mas para tabelas temporárias que não são necessárias para o funcionamento do mysql. |
| mysql.ibd | É um table space que grava bastante informação, antigamente ficava no arquivo 'mysql'. |
| undo_* | Responsável pelo controle de transação, commit, rollback, pode ser colocado em outro disco. |
| ''#innodb_temp' | É um diretório usado em conjunto com o ibtmp para criação de tabela temporária. |
| mysql | É um diretório. - general_log.csm São logs gerais. - slow_log Grava tudo que demore, como uma consulta, uma alteração no banco. |
| performance_schema | Contém informações do server, como erros, stages etc. |
MySQL Router
O MySQL Router é muito usado para alta disponibilidade e escalabilidade, roteando o tráfego do in/out do banco de dados para outros servidores mysql.
MySql Schema
Vamos ver algumas informações sobre o Schema do MySQL.
Data Dictionaries Tables
Guarda informações de Objetos como: Tabelas, Colunas, indices etc. Antigamente era guardado em arquivos.
Uma forma de ver as tabelas aqui é usando o comando select * from INFORMATION_SCHEMA.<TABELA>;, vamos ver algumas dessas tabelas abaixo:
| Tabela | Descrição |
|---|---|
| Catalogs | Contém informações de catálogos (é o dicionário de dados). |
| character_sets | Contém informações sobre os conjuntos de caracteres disponíveis. |
| check_constraints | Contém informações sobre restrições definidas em tabelas (somente no mysql >=8.0.16). |
| collations | Contém informações sobre agrupamentos para cada conjunto de caracteres. |
| column_statistics | Estatísticas do histograma para valores de colunas. |
| column_type_elements | Contém informações sobre os tipos usados pelas colunas. |
| columns | Contém informações sobre colunas em tabelas. |
| dd_properties | Tabela que identifica propriedades do dicionário de dados, como sua versão. |
| events | Contém informações sobre eventos do agendador de eventos. |
| foreign_keys | Contém informações sobre chaves estrangeiras. |
| foreign_key_column_usage | Contém informações sobre chaves estrangeiras. |
| index_partitions | Contém informações sobre partições usadas por índices. |
| index_stats | Usado para armazenar estatísticas de índices dinâmico gerado quando o ANALYZE TABLE é executado. |
| indexes | Contém informações sobre índices de tables. |
| innodb_ddl_log | Armazena logs DDL para operações DDL contra falhas. |
| parameter_type_elements | Contém informações sobre procedumentos e funções armazenados. |
| parameters | Contém informações sobre procedimentos e funções armazenadas. |
| resources_groups | Contém informações sobre grupos de recursos. |
| routines | Contém informações sobre procedimento e funções armazenadas. |
| schemata | Contém informações sobre esquemas. |
| tables | Contém informações sobre tabelas em bancos de dados. |
Grant System Tables
Contém os dados dos usuários e suas permissões e previlégios.
Uma forma de ver as tabelas aqui é usando o comando select * from mysql.<TABELA>;, vamos ver algumas dessas tabelas abaixo:
| Tabela | Descrição |
|---|---|
| user | Conta de usuários, privilégios globais e outros. |
| global_grants | Atribuição de provilégios globais dinâmicos aos usuários |
| table_priv | Privilégios a nível de tabela |
| column_priv | Privilégios a nível de coluna |
| default_roles | Lista os perfis padrão a serem ativados em um usuário (atua como grupos, onde vamos dar o privilégio para o grupo) |
Privilégios estático são nativos do MySQL como: select, insert, update, delete. Já os dinâmicos aparecem com a adição de algum plugin, e essa permissão está relacionada a esse componente que não está no MySQL por padrão.
Object Information System Tables
Contém os dados sobre Store Procedure, componentes, funções, plugins e etc.
Log System Tables
Contém os dados sobre os Logs de consulta.
Uma forma de ver as tabelas aqui é usando o comando select * from mysql.<TABELA>;, vamos ver algumas dessas tabelas abaixo:
| Tabela | Descrição |
|---|---|
| slow_log | Contém tabelas de log de consulta mais lenta (podemos programar consultas que demorem X tempo serão gravadas aqui |
| general_log | Tabela de log de consulta geral |
Replication System Tables
Usado para operações de replicação.
Optimizer System Tables
Usado para otimizar as consultas.
MYSQLD
Vamos ver algumas opções que podemos usar no arquivo de configuração do servidor do MySQL, seu caminho por padrão fica em /etc/mysql/mysql.conf.d/mysqld.cnf.
| Opção | Descrição | Exemplo |
|---|---|---|
| basedir | Caminho do mysql (onde está instalado). | basedir = /etc/mysql |
| datadir | Caminho onde ficarão os arquivos, geralmente é a pasta 'data'. | datadir = /var/lib/mysql |
| tempdir | Local onde ficarão os arquivos temporários. | tmpdir = /tmp |
| lc-messages | Linguagem usada, principalmente em informações no console. | lc_messages = pt_BR |
| default-time-zone | Indica qual o timezone será usado para marcar as datas e horas no servidor MySQL, por padrão é usado do sistema. | default-time-zone = "-3:00" |
| port | É a porta usada para comunicação com o MysQL, usado quando o cliente vai se conectar com o servidor. | port = 3306 |
Bonus
Algumas funções no SQL não precisam específicar o FROM, mas existem outras que necessitam de um from, nesses casos, podemos usar a tabela chamada dual.
# Exibindo a data/hora do banco:
MariaDB [(none)]> select now();
+---------------------+
| now() |
+---------------------+
| 2022-01-05 13:30:00 |
+---------------------+
1 row in set (0.001 sec)
# Usando o from:
MariaDB [(none)]> select now() from dual;
+---------------------+
| now() |
+---------------------+
| 2022-01-05 13:30:27 |
+---------------------+
1 row in set (0.001 sec)
Para restringir caracteres numa coluna onde deva ser M ou F, usamos assim:
genero CHAR(1) CHECK (genero IN ('M','F'))
Nesse caso usamos a check constraint, outra forma de fazer isso é usando enum, como a seguir:
genero ENUM('M','F')
Alguns Erros
Para o erro Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection siga o passo abaixo:
# Remoto
ALTER USER 'USERNAME'@'IP' IDENTIFIED WITH mysql_native_password BY 'SENHA';
# Local:
ALTER USER 'USERNAME'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SENHA';
# Limitando a saída:
mysql> select dept_no,dept_name from departments limit 3;
+---------+------------------+
| dept_no | dept_name |
+---------+------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
+---------+------------------+
3 rows in set (0.002 sec)
# Contando a quantidade de registros dentro de uma tabela:
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.078 sec)
# Isso significa que temos mais de 300k de linhas nessa tabela.
# Criando um filtro com WHERE, listando apenas funcionarios contratados depois dos anos 2k:
mysql> select * from employees where hire_date > "2000-01-01 limit 10";
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+------------+--------+------------+
| 47291 | 1960-09-09 | Ulf | Flexer | M | 2000-01-12 |
| 60134 | 1964-04-21 | Seshu | Rathonyi | F | 2000-01-02 |
| 72329 | 1953-02-09 | Randi | Luit | F | 2000-01-02 |
| 205048 | 1960-09-12 | Ennio | Alblas | F | 2000-01-06 |
| 222965 | 1959-08-07 | Volkmar | Perko | F | 2000-01-13 |
| 226633 | 1958-06-10 | Xuejun | Benzmuller | F | 2000-01-04 |
| 227544 | 1954-11-17 | Shahab | Demeyer | M | 2000-01-08 |
| 422990 | 1953-04-09 | Jaana | Verspoor | F | 2000-01-11 |
| 424445 | 1953-04-27 | Jeong | Boreale | M | 2000-01-03 |
| 428377 | 1957-05-09 | Yucai | Gerlach | M | 2000-01-23 |
| 463807 | 1964-06-12 | Bikash | Covnot | M | 2000-01-28 |
| 499553 | 1954-05-06 | Hideyuki | Delgrande | F | 2000-01-22 |
+--------+------------+------------+------------+--------+------------+
12 rows in set, 1 warning (0.121 sec)
# Funcionarios contratados depois dos anos 2k e que sejam mulheres:
mysql> select * from employees where hire_date > "2000-01-01" AND gender="F";
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+------------+--------+------------+
| 60134 | 1964-04-21 | Seshu | Rathonyi | F | 2000-01-02 |
| 72329 | 1953-02-09 | Randi | Luit | F | 2000-01-02 |
| 205048 | 1960-09-12 | Ennio | Alblas | F | 2000-01-06 |
| 222965 | 1959-08-07 | Volkmar | Perko | F | 2000-01-13 |
| 226633 | 1958-06-10 | Xuejun | Benzmuller | F | 2000-01-04 |
| 422990 | 1953-04-09 | Jaana | Verspoor | F | 2000-01-11 |
| 499553 | 1954-05-06 | Hideyuki | Delgrande | F | 2000-01-22 |
+--------+------------+------------+------------+--------+------------+
7 rows in set (0.118 sec)
# Mesma coisa acima, mas aqui, vamos listar apenas as pessoas que começam com a letra 'S':
mysql> select * from employees where hire_date > "2000-01-01" and first_name like "S%";
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 60134 | 1964-04-21 | Seshu | Rathonyi | F | 2000-01-02 |
| 227544 | 1954-11-17 | Shahab | Demeyer | M | 2000-01-08 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.109 sec)
# Mesma coisa acima, mas aqui, vamos listar apenas as pessoas que começam com a letra 'S' ou 'P':
mysql> select * from employees where hire_date > "2000-01-01" and (first_name like "S%" OR first_name like "P%");
<br />
<br />
## Instruções de Schema SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter table TABELA MODIFY COLUNA parametros