Criptografia de colunas no MySQL – Simples, fácil e objetivo

O MySQL não possui uma função nativa para criptografar uma tabela ou todo o banco de dados, mas ele possui funções nativas para criptografia de colunas.

Criptografia de colunas no MySQL

Ao optar por usar criptografia de colunas no MySQL, você deve ter em mente que isso irá ter um custo no desempenho do seu banco de dados. Criptografia sempre gasta tempo de CPU, seja ele em banco de dados ou em aplicações. Ciente disso, você deve fazer uma analise do banco de dados para levantar as colunas que são sensíveis ao negócio e que realmente necessitam ser criptografadas.

Para criptografar uma coluna sensível de uma tabela (exemplo: email VARCHAR(150) da tabela clientes), primeiro precisamos alterar o tipo da coluna para varbinary(255):

alter table `clientes` modify column email varbinary(255);

Agora, para criptografar esta coluna, execute o comando abaixo:

UPDATE `clientes` SET email = AES_ENCRYPT(email,’chave-para-criptografia’);

Pronto, a coluna email está criptografada com o algorítimo de criptografia AES.

Para recuperar os e-mails da tabela clientes de forma descriptografada basta executar o comando abaixo:

SELECT CAST(AES_DECRYPT(email,’chave-para-criptografia’) AS char(255)) FROM `clientes`;

Além do algoritmo de criptografia AES o MySQL também possui os algoritmos de criptografia DES, MD5, SHA-1 e SHA-2. Evite usar os algoritmos MD5 e SHA-1, pois esses dois algoritmos de criptografia foram expostos.

Atenção!

Colunas do tipo VARBINARY não podem ser indexadas.

Replicação do MySQL em 10 passos

Configurando replicação do MySQL em 10 passos.

PASSO 1

Adicione o código abaixo no my.cnf do servidor MASTER depois da sessão [mysqld]:

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

PASSO 2

Reinicie o serviço do MySQL no servidor MASTER.

PASSO 3

Crie um usuário MySQL no servidor MASTER. Use o IP do servidor SLAVE como host a partir de onde o usuário irá se conectar e de a esse usuário o privilégio “REPLICATION SLAVE:

GRANT REPLICATION SLAVE ON *.* TO ‘newUser’@’192.168.0.215’;

PASSO 4

Execute o seguinte comando no prompt de comando do mysql do servidor MASTER:

FLUSH TABLES WITH READ LOCK;

Este comando irá trancar todas as tabelas permitindo apenas leitura nelas, o que impedirá qualquer modificação nos dados durante a configuração da replicação.

PASSO 5

Agora execute o comando abaixo ainda no prompt do servidor MASTER:

SHOW MASTER STATUS;

Este comando retornará o nome e a sequencia numérica do arquivo de log binário. Precisamos anotar esses valores pois iremos precisar deles no passo 9 na configuração do servidor SLAVE.

PASSO 6

Ainda no prompt do servidor MASTER, execute o comando:

UNLOCK TABLES;

Depois de executar este comando, vá para o servidor SLAVE para configurarmos ele.

PASSO 7

Adicione o código abaixo no my.cnf do servidor SLAVE depois da sessão [mysqld]:

server-id=2

PASSO 8

Reinicie o serviço do MySQL no servidor SLAVE.

PASSO 9

Execute o comando abaixo no prompt de comando do mysql no servidor SLAVE:

CHANGE MASTER TO
MASTER_HOST=’192.168.0.205′,
MASTER_USER=’newUser’,
MASTER_PASSWORD=’senha’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000001′,
MASTER_LOG_POS=xxxxx,
MASTER_CONNECT_RETRY=10;

Explicando os parâmetros.

MASTER_HOST é o IP do servidor MASTER.
MASTER_USER é o usuário que criamos para fazer a replicação lá no passo 3.
MASTER_PASSWORD é a senha que definimos pro usuário da replicação.
MASTER_LOG_FILE é o nome do arquivo que anotamos no passo 5 (se por acaso o banco replicado não contiver todas as informações do banco master, então coloque esta opção como a do exemplo).
MASTER_LOG_POS é a sequencia numérica anotada no passo 5.

PASSO 10

Ainda no prompt do servidor SLAVE, execute o comando abaixo para finalizar a configuração:

START SLAVE;

Pronto! Se tudo deu certo, sua replicação já está configurada e funcionando.

Para verificar o status do servidor SLAVE, você pode executar o comando no prompt de comandos do mysql:

SHOW SLAVE STATUS;

Comandos CHECK, OPTIMIZE e ANALYZE do MySQL

O MySQL possui 3 comandos muito úteis para manutenção de tabelas, são eles o check, optimize e analyze. Veja a seguir para que cada um deles serve.

CHECK

Valida a integridade das tabelas.

Exemplo: check table_name;

OPTIMIZE

Recupera espaços não usados e refaz os índices. Este comando é muito útil, pois além de refazer todos os índices, o que corrige problemas de índice corrompido, também reorganiza o armazenamento dos índices para não ficar buracos (analogicamente falando, esta reorganização funciona como o desfragmentador de discos do Windows).

Exemplo: optimize table_name;

ANALYZE

Refaz e otimiza as estruturas dos índices. Refaz os índices assim como o comando optimize, mas não recupera os espaços não usados. Em contra partida, este comando otimiza as estruturas dos índices, deixando-os ordenados conforme suas definições da criação dos mesmos, o que proporciona um ganho de desempenho nas querys que usam esses índices.

Exemplo: analyze table_name;

3 comandos avançados para controle de transação – MySQL

Neste post vou mostrar para você como fazer rollback parcial de uma transação usando savepoint. Você irá aprender a como definir pontos de retornos dentro da transação para não precisar desfazer toda transação.

Comandos avançados para controle de transação

SAVEPOINT <identifier>

Define um ponto de chamada dentro de um conjunto de instruções SQL a partir de onde um ROLLBACK TO SAVEPOINT deve ser executado.

Exemplo:

START TRANSACTION;
comandos SQL…
SAVEPOINT cadastrocliente;
comandos SQL…
COMMIT;

ROLLBACK TO SAVEPOINT <identifier>

Desfaz todas as instruções SQL que foram realizadas após o ponto de chamada informado.

Atenção! Este comando não finaliza a transação.

START TRANSACTION;
comandos SQL…
SAVEPOINT cadastrocliente;
comandos SQL…
ROLLBACK TO SAVEPOINT cadastrocliente;
comandos SQL…
COMMIT;

RELEASE SAVEPOINT <identifier>

Libera um ponto de chamada previamente definido.

Atenção! Este comando não finaliza a transação.

START TRANSACTION;
comandos SQL…
SAVEPOINT cadastrocliente;
comandos SQL…
ROLLBACK TO SAVEPOINT cadastrocliente;
RELEASE SAVEPOINT cadastrocliente;
comandos SQL…
COMMIT;

O RELEASE SAVEPOINT serve para liberar o identificador do SAVEPOINT para ser utilizado novamente dentro da mesma transação.

Os comandos COMMIT e ROLLBACK liberam da memória todos os SAVEPOINT que foram previamente definidos na transação.

MySQL – Tipos de colunas para armazenamento numérico

Veja abaixo quais são os tipos de colunas que o MySQL suporta para armazenamento de colunas numéricas e quais são as características de cada tipo.

TINYINT(size)

Intervalo entre 0 e 255; possíveis valores para armazenamento de -128 à 127; ocupa 1 byte

SMALLINT(size)

Intervalo entre 0 e 65.535; possíveis valores para armazenamento de -32.768 à 32.767; ocupa 2 bytes

MEDIUMINT(size)

Intervalo entre 0 e 16.777.215; possíveis valores para armazenamento de -8.388.608 à 8.388.607; ocupa 3 bytes

INT(size)

Intervalor entre 0 e 4.294.967.295; possíveis valores para armazenamento de -2.147.483.648 à 2.147.483.647; ocupa 4 bytes

BIGINT(size)

Intervalo entre 0 e 18.446.744.073.709.551.615; possíveis valores para armazenamento de -9.223.372.036.854.775.808 à 9.223.372.036.854.775.807; ocupa 8 bytes

FLOAT(size,n)

Número decimal com n dígitos a direita do ponto decimal, ocupa 4 bytes

DOUBLE(size,n)

Número decimal com n digitos a direita do ponto decimal, ocupa 8 bytes

DECIMAL(size,n)

Armazenamento duplo como String com n dígitos a direita do ponto decimal; o tamanho ocupado é baseado no valor numérico.