MySQL – Como adicionar chave estrangeira em tabela nova ou existente

TL; DR

Com adicionar chave estrangeira em uma tabela nova:

CREATE TABLE child (ID INT PRIMARY KEY, parent_ID INT, FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID) ON UPDATE NO ACTION ON DELETE NO ACTION);

Com adicionar chave estrangeira em uma tabela existente:

ALTER TABLE child ADD FOREIGN KEY my_fk (parent_id) REFERENCES parent(ID) ON UPDATE NO ACTION ON DELETE NO ACTION;

MySQL tem a habilidade the forçar um registro a existir em uma tabela pai quando você está adicionando / editando ou deletando um registro na tabela filho, isso é chamada de Chave Estrangeira ou Foreign Key.

Como podemos ver acima, você pode tanto criar uma tabela com FK desde o começo ou adicionar ela após a tabela já ter sido criada. Além da sintaxe que refere a coluna na tabela filho e o nome da tabela e coluna na tabela pai que vamos referenciar, é possível controlar o comportamento da tabela pai quando se modificar algum registro que está referenciado na tabela filho. Isso é controlada com os parametros ON UPDATE e ON DELETE. As restrições são as seguintes:

Vamos utilizar a tabela abaixo como exemplo:

mysql> SELECT * FROM parent;
+----+
| ID |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM child;
+----+-----------+
| ID | parent_ID |
+----+-----------+
|  1 |         1 |
+----+-----------+
1 row in set (0.00 sec)
  • RESTRICT or NO ACTION – Comportamento padrão caso seja omitido  ON UPDATE or ON DELETE. Isso quer dizer que quando seja feito alguma alteração na tabela pai, e ela tenha um registro na tabela filho apontado para ela, essa operação (UPDATE ou DELETE) vai ser bloqueada:
    mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_ID`) REFERENCES `parent` (`ID`))
    

    Mesmo não recomendado, em casos extremos, podemos desabilitar esse check que o MySQL faz usando o comando abaixo:

    SET foreign_key_checks=0;

    Tenha em mente que desabilitar o check faz com que não tenha sentido algum ter a chave estrangeira na tabela, considere remover!

  • SET DEFAULT – É reconhecido como uma sintaxe válida, mas é interpredado como RESTRICT.
  • CASCADE – Qualquer ação na tabela pai, vai ser replicada para os registro referenciados na tabela filho:
    mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT * FROM parent;
    +----+
    | ID |
    +----+
    |  2 |
    +----+
    1 row in set (0.00 sec)
    
    mysql> SELECT * FROM child;
    +----+-----------+
    | ID | parent_ID |
    +----+-----------+
    |  1 |         2 |
    +----+-----------+
    1 row in set (0.00 sec)
  • SET NULL – Qualquer alteração na tabela pai, vai resetar a coluna na tabela filho para NULL (tenha certeza que a coluna não esteja definada como NOT NULL):
    mysql> UPDATE parent SET ID = 2 WHERE ID = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT * FROM parent;
    +----+
    | ID |
    +----+
    |  2 |
    +----+
    1 row in set (0.01 sec)
    
    mysql> SELECT * FROM child;
    +----+-----------+
    | ID | parent_ID |
    +----+-----------+
    |  1 |      NULL |
    +----+-----------+
    1 row in set (0.00 sec)

MySQL Load Balancing com ProxySQL – Tutorial Master e Slave

Seguindo a série de blog posts sobre ProxySQL. Hoje vamos falar de como podemos colocar o proxysql em frente ao seu conjunto de master e slave(s) e distribuir escritas para os slaves sem ter que alterar o código da aplicação. Você pode entender mais sobre como instalar o ProxySQL e seus conceitos básicos aqui.

Neste tutorial, vamos utilizar 4 servidores:

  1. ProxySQL (192.168.112.60)
  2. Node1 – Master (192.168.112.61)
  3. Node2 – Slave-1 (192.168.112.62)
  4. Node3 – Slave-3 (192.168.112.63)

Servidores

Com o proxysql devidamente instalado vamos nos conectar na interface de administração e vamos adicionar nossos três servidores e configurar o Replication Hostgroup. Basicamente vamos dizer ao ProxySQL qual vai ser o HG do master e do slave. Lembrando que o ProxySQL diferencia master e slave baseado na variável read_only:

$ mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='proxysql> '

#proxysql> 
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.61');
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.62');
INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20, '192.168.112.63');
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) VALUES (10, 20, 'Master / Slave App 1');
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

Quando verificamos a nossa lista de servidores, todos ainda estão no HG 20, inclusive o master:

proxysql> SELECT hostgroup_id, hostname, status FROM mysql_servers;
+--------------+----------------+--------+
| hostgroup_id | hostname | status |
+--------------+----------------+--------+
| 20 | 192.168.112.61 | ONLINE |
| 20 | 192.168.112.63 | ONLINE |
| 20 | 192.168.112.62 | ONLINE |
+--------------+----------------+--------+
3 rows in set (0.00 sec)

Existe uma thread no ProxySQL que é responsável por ser conectar em cada servidor listado na tabela mysql_servers e checkar o valor da variável read_only. Na tabela mysql_server_read_only_log podemos verificar o log destes checks:

proxysql> SELECT * FROM mysql_server_read_only_log LIMIT 3;
+----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+
| 192.168.112.61 | 3306 | 1529175123875168 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) |
| 192.168.112.62 | 3306 | 1529175123876409 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) |
| 192.168.112.63 | 3306 | 1529175123877369 | 0 | NULL | timeout on creating new connection: Access denied for user 'monitor'@'192.168.112.60' (using password: YES) |
+----------------+------+------------------+-----------------+-----------+-------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Como podemos ver acima, o Proxy não está conseguindo conectar aos nossos servidores. Por questões de segurança, vamos alterar o usuário que o proxy utiliza para realizar este check:

UPDATE global_variables SET variable_value='p_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='M0n170Rpwd!' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

Agora temos que criar este usuário no nosso master:

CREATE USER p_monitor@192.168.112.60 IDENTIFIED BY 'M0n170Rpwd!';

Com o usuário devidamente criado no MySQL, podemos verificar na interface admin do ProxySQL que agora temos 4 servidores:

proxysql> SELECT hostgroup_id, hostname, status FROM mysql_servers ORDER BY hostgroup_id;
+--------------+----------------+--------+
| hostgroup_id | hostname | status |
+--------------+----------------+--------+
| 10 | 192.168.112.61 | ONLINE |
| 20 | 192.168.112.61 | ONLINE |
| 20 | 192.168.112.62 | ONLINE |
| 20 | 192.168.112.63 | ONLINE |
+--------------+----------------+--------+
4 rows in set (0.01 sec)

O nosso Master está no tanto no HG 10 (escrita) e no HG20 (leitura). Podemos remover o master do HG de leitura configurando a variável mysql-monitor_writer_is_also_reader para falso (ela vem ativada por padrão).

Usuários

Próximo passo é criar no ProxySQL o usuário que a aplicação utiliza para conectar no banco de dados. Podemos criar ele manualmente ou importar todos os usuário do MySQL para o ProxySQL como descrito neste post. Neste tutorial, vou criar o usuário manualmente. Lembrando que o usuário deve ser/estar criado também no MySQL com os devidos GRANTS aplicados :

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('marcelo', 'marcelo', 10);
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

Reparem que se rodarmos um select na tabela mysql_users vamos ver o password do usuário em plaintext:

proxysql> SELECT username, password FROM mysql_users;
+----------+----------+
| username | password |
+----------+----------+
| marcelo | marcelo |
+----------+----------+
1 row in set (0.00 sec)

Porém a tabela runtime_mysql_users vai ter computado o hash para o password. Por questões de segurança, toda a vez que criarmos um usuário manualmente no ProxySQL e inserirmos o password em plaintext, devemos rodar um comando extra pra que a versão da tabela de runtime, seja salva também no banco de dados main, e consequentemente, vamos salvar da main para o disco novamente para que o a versão com hash do password persista caso o serviço do ProxySQL seja reiniciado:

proxysql> SAVE MYSQL USERS FROM RUNTIME; SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

proxysql> SELECT username, password FROM mysql_users;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| marcelo | *8E36BAA4C91256FAEF957292B1C224C102754D25 |
+----------+-------------------------------------------+
1 row in set (0.00 sec)

Regras

Pronto? Quase lá. Agora temos que criar a mágica!!!

Toda a vez que o usuário marcelo se conectar ao ProxySQL, ele vai mandar todas as queries para o HG 10 (configurado no campo default_hostgroup da tabela mysql_users). O HG 10 é um mapeamento para o nosso servidor master. Agora temos que instruir o ProxySQL a redirecionar todos os nossos comandos SELECT para o HG 20 que está configurado a fazer um balanceamento em todos os nossos servidores, incluindo os slaves. Vamos fazer esta configuração na tabela mysql_query_rules . Existe uma pequena excessão que temos que levar em consideração que são os comandos SELECT . . . FOR UPDATE , pois este comando é executado quando queremos colocar um lock na linha que estamos lendo, com o intuito de alterar esta linha, então temos que tomar o cuidado para que este select seja enviado também ao HG 10:

INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,match_digest,apply) VALUES(1,'marcelo',10,1,'^SELECT.*FOR UPDATE',1);
INSERT INTO mysql_query_rules (rule_id,username,destination_hostgroup,active,match_digest,apply) VALUES(2,'marcelo',20,1,'^SELECT ',1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

Pronto, agora o ProxySQL já está configurador para distribuir os selects que são seguros para os slaves. Tudo que temos que fazer agora é configurar nossas aplicações para se conectar no proxy ao invés de conectar diretamente no servidor. Lembrando que por padrão a porta do ProxySQL para aplicação é a 6033.

Abaixo segue um gráfico coletado com o PMM mostrando a diferença de somente o master servindo o tráfico contra o momento em que o proxy começa a distribuir as queries:

Como podemos ver, node1(Master) vinha com um tráfego bem alto quando comparado aos outros dois servidores, a partir do momento que que o ProxySQL iniciou a distribuir o tráfego, a distribuição de tráfego ficou homogênea nos 3 servidores, o mesmo pode se observar no gráfico de load dos servidores.

Failover

ProxySQL será capaz de identificar quando um failover acontece. Assim que o master estiver indisponível e algum slave for promovido para master, o proxy irá identificar que o slave não está mais com a variável read_only configurada e irá mover o slave para o HG 10.

Importante: O ProxySQL é somente um proxy responsável por redirecionar o tráfico e capaz de identificar que um failover aconteceu, ele não irá fazer o failover automático como executar um CHANGE MASTER TO nos demais slaves.

 

Slaves atrasados

ProxySQL pode parar de enviar trafego para um slave se ele estiver atrasado por mais de X segundos. Para que o proxy consiga verificar o status do slave, o usuário utilizado pela thread de monitor precisa do grant  REPLICATION CLIENT.  Podemos adicionar este grant com o comando abaixo:

GRANT REPLICATION CLIENT ON *.* TO p_monitor@192.168.112.60

Agora, vamos configurar o ProxySQL para não enviar mais trafego para servidores atrasados por mais de 10 segundos:

UPDATE mysql_servers SET max_replication_lag = 10 WHERE hostgroup_id=20;
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;

Pronto, agora toda vez que o slave atrasar, ele terá seu status alterado para SHUNNED na tabela runtime_mysql_servers:

proxysql> SELECT * FROM runtime_mysql_servers;
+--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 192.168.112.61 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.168.112.61 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 20 | 192.168.112.63 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | |
| 20 | 192.168.112.62 | 3306 | SHUNNED | 1 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+----------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Podemos verificar o atraso que o ProxySQL recebeu do slave consultado a tabela mysql_server_replication_lag_log:

proxysql> SELECT * FROM mysql_server_replication_lag_log WHERE hostname = '192.168.112.62' ORDER BY time_start_us DESC LIMIT 1;
+----------------+------+------------------+-----------------+----------+-------+
| hostname | port | time_start_us | success_time_us | repl_lag | error |
+----------------+------+------------------+-----------------+----------+-------+
| 192.168.112.62 | 3306 | 1529190811923215 | 953 | 604 | NULL |
+----------------+------+------------------+-----------------+----------+-------+
1 row in set (0.00 sec)

O atraso é monitorado a cada  mysql-monitor_replication_lag_interval milisegundos (10 segundos por padrão).

ProxySQL – Sincronizar / Importar usuários

No meu último post expliquei alguns fundamentos básicos do ProxySQL. Hoje eu vou falar como podemos importar os usuários do MySQL para o ProxySQL.

Para realizar esta tarefa, vamos usar o proxysql-admin uma ferramenta criada pela Percona para auxiliar na administração do proxy. Se vocês está utilizando o ProxySQL da percona a ferramenta já vem instalada por padrão, caso contrário, pode baixar diretamente do repositório no Github.

Vamos utilizar a opção --syncusers que vai se conectar no servidor MySQL indicado, vai buscar todos os usuários na tabela mysql.user e vai importar eles para o ProxySQL:

proxysql-admin --cluster-username=[mysql-user] --cluster-password=[mysql-password] --cluster-port=[mysql-port] --cluster-hostname=[mysql-host] --syncusers

A ferramenta foi criada originalmente para aumentar a compatibilidade entre o ProxySQL e o Percona XtraDB Cluster, então a terminologia dosparâmetros e mensagens vão sempre mencionar o cluster, porém a funcionalidade descrita deste post também é 100% compatível com Master Slave.

Aqui vai um exemplo do comando em prática:

$ proxysql-admin --cluster-username=root --cluster-password=sekret --cluster-port=3306 --cluster-hostname=192.168.112.61 --syncusers

Syncing user accounts from Percona XtraDB Cluster to ProxySQL

Synced Percona XtraDB Cluster users to the ProxySQL database!

Vale lembrar que toda a vez que um usuário é adicionado ou troca a senha, temos que também atualizar este usuário no proxysql. Com o proxysql-admin basta rodar o --syncusers e a ferramenta se encarrega do resto.

Como remover coluna de tabela no MySQL

Fala galera, Neste artigo para iniciantes vou mostrar como remover uma ou mais colunas de tabela no MySQL.

Para este artigo vou utilizar uma tabela exemplo:

CREATE TABLE tb(
c1 INT PRIMARY KEY,
c2 char(1),
c3 varchar(2)
) ENGINE=InnoDB;

Para remover qualquer coluna vamos utilizar o comando ALTER TABLE

Como Remover uma coluna de tabela:

ALTER TABLE tb DROP COLUMN c2;

Como Remover multiplas colunas de tabela:

Vale lembrar que podemos remover várias colunas em ao mesmo tempo

ALTER TABLE tb DROP COLUMN c2, DROP COLUMN c3;

Se você estiver utilizando MySQL 5.6 em diante, esta operação pode ser “online”, permitindo outras sessões ler e escrever na tabela enquanto o comando está sendo executado:

ALTER TABLE tb DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

Referencia:

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

Desmistificando ProxySQL

Fala galera. Neste post vou falar um pouco sobre o ProxySQL . O ProxySQL é um proxysql para que implementa o protocolo do MySQL o que lhe permite fazer coisas que outros proxies não fazem. Ele vem ganhando muita atenção nos últimos tempos e é capaz de integrar com vários produtos do ecossistema MySQL, como Replicação (master – slave), Percona XtraDB Cluster e Group Replication. Uma  das funcionalidades mais úteis (que faz o produto se destacar perante os demais) é a habilidade de escalar escritas para os slaves , ele é capaz de identificar que tipo de query está sendo recebida (Escrita ou Leitura) e enviar a query para o master ou para o(s) slave(s). Isso tudo sem ter que alterar uma linha de código na aplicação.

Neste artigo vou abordar alguns pontos importantes para entendermos como o proxy funciona.

Instalação

A Instalação pode ser feita das seguintes maneiras:

  1. Baixando a os pacotes para a sua versão de sistema operacional (rpm / deb) do repositório oficial no github https://github.com/sysown/proxysql/releases :
    sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm
    
  2. Baixando a os pacotes para a sua versão de sistema operacional (rpm / deb) do site da Percona https://www.percona.com/downloads/proxysql/ :
    sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm
  3. Instalando o repositório da Percona para a sua versão de sistema operacional (preferido):
    sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
    sudo yum install proxysql

 

 

sudo yum install https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql-1.4.9-1-centos7.x86_64.rpm

Agora só precisamos iniciar o serviço

sudo service proxysql start 

Interfaces

ProxySQL faz uma divisão da área de administração do proxy com a área em que as aplicações conectam e executam queries. A área de administração escuta na porta 6032 e a área  para as aplicações escuta na 6033 (3306 ao contrário).

Camadas

Outra parte importante para entender como o proxy funciona são as camadas que ele utiliza, vou utilizar parte do diagrama oficial (disponível aqui) para explicar:

+-------------------------+
|      1. RUNTIME         |   
+-------------------------+
       /|\          |
        |           |
        |           |
        |          \|/
+-------------------------+
|     2.  MEMORY          |   
+-------------------------+ 
       /|\          |      
        |           |      
        |           |       
        |          \|/      
+-------------------------+  
|     3.   DISK           |  
+-------------------------+  

ProxySQL sempre vai ler as informações da camada 1.Runtime que reside em memória.
Quando conectamos na porta 6032 sempre vamos estar conectados a camada 2.Memory, que como o nome diz, reside em memória.
E Por final, temos a camada 3.Disk, como as demais camadas residem em memória, precisamos de uma camada para persistir os dados em disco.

Qual o benefício deste layout? Ele nos permite realizar várias alterações e aplicar elas somente no momento em que terminamos  todas elas, podemos fazer uma analogia a transações, aonde depois de executar várias queries, executamos um commit.
Quando o proxysql iniciar, ele lê os dados do disco para a memória e lê os dados da memória para runtime. Quando realizarmos uma alteração, vamos estar manipulando a memória, vamos daí executar um comando LOAD MYSQL [SERVERS | USERS | QUERY RULES] TO RUNTIME para ler os dados da memória para runtime, e conseqüentemente, vamos executar um SAVE MYSQL [SERVERS | USERS | QUERY RULES] TO DISK para salvar os dados da memória para o disco.

Hostgroups

ProxySQL agrupa servidores em algo chamado hostgroup. Em uma topologia em que temos um master e dois slaves, vamos criar o HostGroup(HG) 1 e especificar que o master faz parte dele, e vamos criar o HG2 e especificar que ambos os slaves fazem parte dele. A criação de HG é feita juntamente com a inserção de servidores na tabela mysql_servers e não existe uma numeração fixa. Você pode atribuir a número/ID que quizer. Posteriormente, vamos configurar usuários e regras para queries que vão identificar se a query é uma leitura, ela deve ser direcionada para o HG 2 (leitura / aonde estão nossos slaves) e o proxysql vai enviar a query para um dos servidores presentes neste HG. Caso a query não seja uma leitura, ela vai ser direcionada para o HG de escrita.

Autenticação

ProxySQL possui funcionalidades como firewall por exemplo, em que você pode bloquear uma query de ser executada. Uma vez que o proxy identifica esta query, ele retorna uma mensagem de erro, sem mesmo ter enviado ela para algum hostgroup. Para que funcionalidades como esta sejam possíveis o proxy requer uma autenticação além da autenticação no servidor do MySQL. Para que um usuário do MySQL possa conectar via ProxySQL temos que criar o mesmo no proxy.

 

 

Fica aqui um link de uma apresentação que fiz a um tempo atrás referente a algumas features do ProxySQL – https://www.slideshare.net/MarceloAltmann/proxy-sql-para-mysql

Agora que você entendeu um pouco de como funciona o ProxySQL, já pode começar a utilizar / testar em suas aplicações.

Backup online no MySQL com xtrabackup

Meu último post referente a backups foi a bastante tempo atrás. Embora ainda válido, não é aconselhável para ser utilizado como backup diário, então resolvi mostrar uma outra opção, o XtraBackup.

Com XtraBackup, você pode realizar backups sem interromper leituras e escritas (ele requer lock por um período bem curto de tempo para pegar coordenadas do binlog).
Hoje vou mostrar como realizar backup completos utilizando a ferramenta.

Instalação:

Para realizar a instalação, eu aconselho utilizar os repositórios para Yum / Apt-get:

Centos / Redhat:

sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
sudo yum install percona-xtrabackup-24

Debian / Ubuntu

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-xtrabackup-24

Backup:

Para realizar o backup, vamos usar o script innobackupex:

innobackupex --user=root --password='Passw0rd!' /backups/

O script irá produzir várias mensagens, devemos verificar se ele termina com:

...
170429 21:07:12 completed OK!

Paralelismo:

O Xtrabackup permite que você configure múltiplas threads para realizar a cópia das tabelas utilizando a opção –parallel :

innobackupex --user=root --password='Passw0rd!' --parallel=8 /backups/

Podemos observar que cada thread recebe um número que é mostrado entre []:

170429 21:12:27 [05] Copying ./ibdata1 to /backups/2017-04-29_21-12-27/ibdata1
170429 21:12:27 [06] Copying ./mysql/plugin.ibd to /backups/2017-04-29_21-12-27/mysql/plugin.ibd
170429 21:12:27 [06]        ...done
170429 21:12:27 [02] Copying ./mysql/servers.ibd to /backups/2017-04-29_21-12-27/mysql/servers.ibd
170429 21:12:27 [02]        ...done
170429 21:12:27 [03] Copying ./mysql/help_topic.ibd to /backups/2017-04-29_21-12-27/mysql/help_topic.ibd
170429 21:12:27 [07] Copying ./mysql/help_category.ibd to /backups/2017-04-29_21-12-27/mysql/help_category.ibd
170429 21:12:27 [07]        ...done

Compactando:

O Xtrabackup também permite que você compacte o backup utilizando –compress e –compress-threads (normalmente utiliza-se o mesmo numero de threads que –parallel):

innobackupex --user=root --password='Passw0rd!' --parallel=8 --compress --compress-threads=8 /backups/

Por exemplo, um backup que ocupava 702M passa a ocupar 387M compactado:

702M	/backups/2017-04-29_21-12-27
387M	/backups/2017-04-29_21-15-53

Restaurando:

Para restaurar o backup, temos que (1) descompactar com –decompress caso nosso backup seja compactado(você precisa ter o qpress instalado) e (2) aplicar as transações que ocorreram enquanto o backup estava sendo realizado (Basicamente um crash recovery que o InnoDB faz ao iniciar o MySQL):

innobackupex --decompress /backups/2017-04-29_21-18-04/
innobackupex --apply-log --use-memory=4G /backups/2017-04-29_21-18-04

Para agilizar o processo de aplicar os logs, podemos configurar –use-memory que vai se comportar como se fosse o InnoDB Buffer Pool.
Os arquivos .qp vao continuar no diretório, você pode remove-los manualmente executando o comando abaixo:

find /backups/2017-04-29_21-18-04  -name "*.qp" -exec rm -f {} \;

É isso aí. Agora só precisamos copiar os arquivos para o datadir do MySQL e configurar o usuário mysql para ser o owner e group owner da pasta e de todos os arquivos.

Por hoje é só. E lembrem, Backup que não é testado, NÃO É BACKUP!

MySQL 8.0 – SET PERSIST

Fala galera. MySQL 8 DMR foi disponibilizado hohe e ele traz algumas novas funcionalidades bem legais. Uma delas é a habilidade de persistir variáveis/configurações entre restart’s. Essa opção é bastante interessante se trocamos variáveis dinâmicas. Isso nos poupa de ter que editar o arquivo .cnf toda a vez que executamos um SET no MySQL(Ou mesmo quando não temos acesso aos arquivos no disco).
A nova sintaxe para o comando é a seguinte:

SET PERSIST option=value;
SET @@persist.option=value;

O MySQL vai criar um arquivo chamado mysqld-auto.cnf. Este arquivo estará localizado na pasta do datadir. Este arquivo irá conter todas as variáveis que foram setadas com o PERSIST e este arquivo será carregado depois de todos os outos (my.cnf / –defaults-file / ~/.my.cnf / …). Isto quer dizer que as variáveis configuradas neste arquivo vão prevalecer perante as demais.

Estas configurações podem ser removidas restaurando seu valor para o valor padrão ou editando o arquivo manualmente no disco(não recomendado).

A funcionalidade pode ser controlada pela configuração persisted-globals-load. Ela é ativado por default. Case seja setada para off (persisted-globals-load=OFF) o MySQL irá ignorar as variáveis presentes no arquivo mysqld-auto.cnf.

As variáveis poderão ser listadas na tabela performance_schema.variables_info . Na coluna variable_source podemos filtrar a origem do valor:

  • COMPILED – Compilada no source do MySQL(Valores padrões)
  • GLOBAL – Parte do arquivo glogal
  • SERVER – Parte do arquivo $MYSQL_HOME/my.cnf file
  • EXPLICIT – Parte do arquivo setado pela opção –defaults-file
  • EXTRA – Parte do arquivo setado pela opçãodefaults-extra-file
  • USER – Parte do arquivo ~/.my.cnf
  • LOGIN – Parte da opção de login path
  • COMMAND_LINE – Opções setadas na linha de comando
  • PERSISTED – Parte das configurações do arquivo mysqld-auto.cnf
  • DYNAMIC – Configurações alteradas dinamicamente

Com estas opções, podemos extrair informações importantes da tabela, como por exemplo, todas as configurações alteradas depois que o servidor foi iniciado:

select * from performance_schema.variables_info where variable_source like 'DYNAMIC';

MySQL 8.0 está disponível para Download no http://dev.mysql.com/downloads/mysql/8.0.html. Tenha em mente que está é uma versão que deve ser usada para teste e não deve ser utilizada em produção. AINDA!

Testem o MySQL 8.0 e digam o que acharam.

MySQL 5.7 utilizando vagrant

Fala galera.
Hoje vou escrever um post rapidinho pra mostrar uma maneira facil de configurar uma vm com mysql 5.7 usando vagrant.

Vagrant é um utilitário de linha de comando que disponibiliza ferramentas para administrar softwares de máquinas virtuais, como por exemplo o virtualbox. Você pode encontrar mais detalhes sobre o Vagrant na documentação. Clique no link installation, se tiver dúvidas quanto a sua instalação.

Eu escrevi um script que está disponível no github. Ele cria uma máquina virtual usando centos 7 e instala o mysql 5.7 nela:

git clone https://github.com/altmannmarcelo/mysql-vagrant.git
cd mysql-vagrant
vagrant up
vagrant ssh mysql57
mysql

Se você olhar nos arquivos que foram clonados do git, vai ver um arquivo chamado bootstrap.sh. Este arquivo vai configurar o repositório do mysql, instalar o pacote do mysql 5.7 e resetar o password do usuário root.

Qualquer dúvida é só postar nos comentários.

Incorrect datetime value – convertendo para timestamp

Fala pessoal.
Hoje eu passei por um problema que em um primeiro momento parecia claramente um bug. Mas olhando um pouco melhor para ele, fez todo sentido.

Eu tenho uma tabela, que aqui vou chamar de t1. Essa tabela tem um campo usando datetime. Durante um projeto de normalização do banco, estamos trocando alguns campos de datetime para timestamp.
Quando eu tentei converter a tabela eu recebi o seguinte erro:

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select * from t1;
+---------------------+
| date                |
+---------------------+
| 2010-03-28 01:28:20 |
+---------------------+
1 row in set (0,00 sec)

mysql> alter table t1 modify date timestamp not null default current_timestamp;
ERROR 1292 (22007): Incorrect datetime value: '2010-03-28 01:28:20' for column 'date' at row 1

Olhando o formato da data, ela parece uma data valida. Depois de um tempo me dei por conta de que nosso timezone é Europe/Dublin. Quando fui pesquisar quando que o horario de verão entrou no ano de 2010, adivinha o que eu achei ???

Isso mesmo, esse foi exatamente o dia que o horario de verão entrou em Dublin naquele ano. Basicamente a 1 da manhã os relógios adiantaram uma hora. Isso que dizer que 01:28:20 nunca existiu no meu timezone. Por que esse valor estava no banco em primeiro lugar é outra discução. Mas por que o banco não reclamou sobre isso antes ?

Segundo a documentação: http://dev.mysql.com/doc/refman/5.6/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

MySQL converte campos TIMESTAMP do timezone original para UTC no caso de armazenamento, e converte de volta de UTC para o timezone do servidor no caso de consulta. (Isso não ocorre para outros tipos de campos, como DATETIME.)

Então, como arrumar ?
Primeiro vamos identificar as linhas que tem uma data invalida e arrumar elas (No meu caso, adicionar uma hora nela). Uma maneira de fazer isso é usando as queries abaixo:

mysql> SELECT date FROM t1 WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
+---------------------+
| date |
+---------------------+
| 2010-03-28 01:28:20 |
+---------------------+
1 row in set (0,00 sec)

mysql> UPDATE t1 SET date=DATE_ADD(date, INTERVAL 1 hour) WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
Query OK, 1 row affected (0,10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT date FROM t1 WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(date)) <> date;
Empty set (0,00 sec)

mysql> alter table t1 modify date timestamp not null default current_timestamp;
Query OK, 1 row affected (0,05 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t1;
+---------------------+
| date |
+---------------------+
| 2010-03-28 02:28:20 |
+---------------------+
1 row in set (0,00 sec)

Identificamos as linhas, arrumamos elas e o MySQL permitiu a conversão para TIMESTAMP.

Por hoje é só.