json_capa

Hoje vamos falar um pouco sobre um assunto muito desejado e esperado, tanto por desenvolvedores quanto por DBAs.
O MySQL suporta armazenar documentos JSON????
A resposta é um grande e sonoro SIM!!!
Na versão 5.7.8 foi adicionado o data type JSON e com ele você pode armazenar e manipular documentos JSON.

Isso significa que posso fazer no MySQL tudo que faço no MongoDB?

Não, pelo menos por enquanto não. Eu ainda não vejo dessa forma, na maturidade atual do recurso ele já pode ajudar muito a suprir algumas necessidades em armazenar e manipular documentos JSON, mas não temos(ainda!?) uma engine completa e altamente robusta para isso!

Vamos ver alguns exemplos de como armazenar, buscar e manipular documentos JSON. Em um próximo POST vou falar sobre performance e melhores práticas.

Para esse exemplo estou utilizando o MySQL 5.7.11 que foi instalado no Ubuntu 15.10.
Veja como fazer instalação do MySQL 5.7 em alguns ambientes que não sejam derivados do RedHat e do Debian.
FreeBSD
SlackWare
Ubuntu 15.10

Criando uma tabela e uma coluna com o data type JSON

CREATE TABLE pessoas
(
 id INT AUTO_INCREMENT PRIMARY KEY
 , pessoa JSON
) ENGINE = InnoDB;

Mas Diego, por que utilizar o data type JSON se podemos utilizar o TEXT ou o BLOB?
A resposta é simples, utilizando o data type JSON você ganha algumas ferramentas valiosas, como uma validação dos documentos, ou seja, nada de gravar documentos incompletos ou com erro de sintaxe, ganha opção de realizar busca e manipulação dos documentos sem o uso de REGEXP, o que vai facilitar muito a sua vida, sem contar com os recursos de performance(que vou falar em outro post).

Inserindo os dados

INSERT INTO pessoas 
(pessoa) 
VALUES
('{"nome": "Fulano", "sexo": "M", "dtNascimento": "1990-01-01", "cidade": "Curitiba", "uf": "PR"}');

 

select_json_1

Mas também podemos inserir um array de objetos.

INSERT INTO pessoas (pessoa) VALUES('[{"nome": "Fulano"}, {"nome": "Beltrano"}, {"nome": "Ciclano"}]');

select_json_2

 

O MySQL vai tratar como types diferentes.

select_json_3_JSON_type

Para os próximo exemplos vou alterar os dados da tabela pessoas.

TRUNCATE TABLE pessoas;
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Fulano", "sexo":"m", "idade":18, "localizacao":{"cidade":"Curitiba", "UF":"PR"} , "areas_interesse": ["A", "B", "c"]}');
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Beltrana", "sexo":"f", "idade":19, "localizacao":{"cidade":"Florianópolos", "UF":"SC"} , "areas_interesse": ["A", "C", "F"]}');
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Ciclano", "sexo":"m", "idade":20, "localizacao":{"cidade":"Porto Alegre", "UF":"RS"} , "areas_interesse": ["A", "B", "D"]}');
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Zezinho", "sexo":"m", "idade":21, "localizacao":{"cidade":"São Paulo", "UF":"SP"} , "areas_interesse": ["E"]}');
INSERT INTO pessoas (pessoa) VALUES('{"nome": "Luizinha", "sexo":"m", "idade":22, "localizacao":{"cidade":"Rio de Janeiro", "UF":"RJ"} , "areas_interesse": ["A", "E"]}');

 

Selecionando e filtrando dados dos documentos JSON

Assim como já acontece em outras linguagens de programação e outros bancos de dados que manipulam documentos JSON, o caracter sifrão($) é utilizado para representar o documento, e após podem ser utilizados seletores.

JSON_EXTRACT

Retorna o valor de uma chave do documento.

SELECT JSON_EXTRACT(pessoa, '$.nome') FROM pessoas;
select_json_extract_1

SELECT JSON_EXTRACT(pessoa, '$.areas_interesse') FROM pessoas;
select_json_extract_2

Mais alguns exemplos

SELECT JSON_EXTRACT(pessoa, '$.localizacao') FROM pessoas;
SELECT JSON_EXTRACT(pessoa, '$.localizacao.UF') FROM pessoas;
SELECT * FROM pessoas WHERE JSON_EXTRACT(pessoa, '$.localizacao.cidade') = 'Curitiba';

JSON_UNQUOTE

Remove as aspas quando o valor de uma chave for string.

SELECT JSON_UNQUOTE(JSON_EXTRACT(pessoa, '$.nome')) AS nome FROM pessoas;

 JSON_KEYS

Retornas as chaves de um documento.

SELECT JSON_KEYS(pessoa) FROM pessoas;
SELECT JSON_KEYS(pessoa, '$.localizacao') FROM pessoas;

JSON_LENGTH

Retorna a quantidade de chaves que existem no documento, ou a partir de uma profundidade específica.

SELECT JSON_LENGTH(pessoa) FROM pessoas;
SELECT JSON_LENGTH(pessoa, '$.localizacao' ) FROM pessoas;

Da para utilizar essa função para saber se uma chave existe, isso pode ser bem útil, já que o que eu mais escuto é que o “bom” de armazenar documentos, cada um pode ter uma estrutura diferente.

SELECT * FROM pessoas WHERE JSON_LENGTH(pessoa, '$.localizacao.UF') = 1;
SELECT * FROM pessoas WHERE JSON_LENGTH(pessoa, '$.localizacao') >= 1;

 

JSON_SEARCH

Retorna o path de um valor que será buscado no documento. Caso o valor não seja localizado, o valor NULL será retornado. o primeiro parâmetro é o documento JSON, o segundo deve ser o valor “one” ou o valor “all”, caso o valor do segundo parâmetro seja “one”, a função irá retornar a primeira chave(elemento) que possui esse valor, e caso o valor do parâmetro seja “all”, será retornada todas as chaves(elementos) que possuem o valor, o terceiro parâmetro é a string que será buscada e aqui é necessário uma atenção especial, pois o valor que será buscado é case sensitive., o quarto valor é o caracter de escape que deve ser utilizado para realizar a busca, caso não tenho o valor NULL deve ser informado, esse parâmetro é opcional, o quinto parâmetro é o path que a busca deve ser realizada, esse parâmetro também não é obrigatório.

Para entender melhor o diferença dos valores “one”e “all”no segundo parâmetro faça esse teste.

INSERT INTO pessoas (pessoa) VALUES('{"nome": "A", "sexo":"A", "idade":"A", "localizacao":{"cidade":"A", "UF":"A"} , "areas_interesse": ["A", "A"]}');
SELECT JSON_SEARCH(pessoa, 'one', 'A') FROM pessoas;
SELECT JSON_SEARCH(pessoa, 'all', 'A') FROM pessoas;

Veja como fazer para selecionar quem tem o valor “D” informado no elemento areas_interesse.

SELECT * FROM pessoas WHERE JSON_SEARCH(pessoa, 'one', 'D', NULL, '$.areas_interesse') IS NOT NULL;

Ou quem é da cidade de Curitiba.

SELECT * FROM pessoas WHERE JSON_SEARCH(pessoa, 'one', 'Curitiba', NULL, '$.localizacao.cidade') IS NOT NULL;

Adicionando Elementos em um documento JSON

JSON_SET

Insere ou altera um elemento no documento. Sua utilização é bem simples, e ela recebe 3 elementos. O primeiro é o documento, o segundo é o path e nome do novo elemento, e o terceiro é o valor.

SELECT JSON_SET('{"a":"a1", "b":"b2"}', '$.c', 'c3');
SELECT JSON_SET('{"a":"a1", "b":"b2"}', '$.a', 'c3');
UPDATE pessoas SET pessoa = JSON_SET(pessoa, '$.dtNascimento', '1990-01-01') WHERE JSON_SEARCH(pessoa, 'one', 'Fulano', NULL, '$.nome') IS NOT NULL;
UPDATE pessoas SET pessoa = JSON_SET(pessoa, '$.dtNascimento', '1991-02-02') WHERE JSON_EXTRACT(pessoa, '$.nome') = 'Beltrana';

 

json_select_dtnascimento

Agora que temos um data em nosso documento, também podemos realizar pesquisas por datas, pois o MySQL indexa as datas nos formatos DATE, DATETIME ou TIMESTAMP.

json_select_dtnascimento_2

JSON_ARRAY_APPEND

Adiciona um novo elemento em um array.

json_select_extract_3

UPDATE pessoas SET pessoa = JSON_ARRAY_APPEND(pessoa, '$.areas_interesse', 'J') WHERE id = 4;

json_select_extract_4

JSON_REMOVE

Remove uma chave ou um elemento de um array.

Removendo uma chave

json_select_extract_5
UPDATE pessoas SET pessoa = JSON_REMOVE(pessoa, '$.dtNascimento') WHERE id = 1;

json_select_extract_6

 

Removendo um elemento de um array

Para remover um elemento de um array é necessário informar a posição do elemento, essa posição é o índice interno do array. O primeiro elemento sempre é o índice 0(zero) e assim por diante, sempre incrementando 1.
Para facilitar essa remoção eu utilizei outra função que já falei nesse artigo, que é a função JSON_SEARCH.

json_select_extract_7

Vamos remover o elemento “E”.

 

UPDATE pessoas
SET pessoa = JSON_REMOVE(pessoa, JSON_UNQUOTE(JSON_SEARCH(pessoa, 'one', 'E', NULL, '$.areas_interesse')))
WHERE id = 4;

Veja que também foi necessário utilizar a função JSON_UNQUOTE para remover as aspas, assim fazendo a função JSON_REMOVE remover o elemento corretamente.

json_select_extract_8

 

Eu busquei fazer os exemplos para atender a uma necessidade, mas além das funções que mostrei aqui existe outras, veja mais em https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.htmlhttps://dev.mysql.com/doc/refman/5.7/en/json.html

Espero que esse artigo ajude vocês, e em breve irei fazer outro falando da performance e dicas para melhores práticas.

Gostou? compartilha esse artigo ai :)
Curta a página da PerformanceDB no Facebook e receba sempre em primeira mão os novos artigos.

federated_select_performancedb

Muitas vezes temos a necessidade de realizar consultas ou até mesmo JOINs com tabelas que estão em outro servidor MySQL/MariaDB. E no nosso cenário não tenos um MySQL Cluster, nem um MySQL Galera Cluster, nem sequer uma replicação, seja isso por qualquer motivo(projetos diferentes, empresas diferentes, etc…).
Para isso o MySQL tem(e faz muito tempo) uma engine específica, a engine FEDERATED. Sim ela permite realizar uma conexão com outro servidor MySQL/MariaDB e executar uma consulta em uma tabela. Mas tenha em mente que a performance vai depender de vários itens, como a velocidade da comunicação pela internet/rede local, quantidade de dados e itens relacionados a performance em geral(estrutura da tabela, índices, engine…).

No exemplo estou usando o MySQL 5.6.28, mas esse mesmo exemplo vai funcionar para o MySQL 5.5 e 5.7, no MySQL 5.1 e 5.0 não testei, se não for isso é muito parecido.

 

Para esse teste, estou usando 2 servidores que vou chamar de mysql56_1 e mysql56_2. O Objetivo é realizar um query que tenha um JOIN com uma tabela que está no mysql56_1 e outra no mysql56_2.

No servidor mysql56_1, tenho o database cidades, com a tabela cidades. Nessa tabela eu tenho o UF e o nome da cidade.
federated_cidades

No servidor mysql56_2, tenho o database estados, com a tabela uf. Nessa tabela eu tenho o UF e o nome do estado.
federated_estados

O objetivo é fazer um select no servidor mysql56_1 e mostrar o nome da cidade e o nome do estado.

Habilitando a engine

A engine FEDERATED não vem habilitada por padrão no MySQL, e a primeira coisa a ser feita é habilitar.

show_engines_sem

A engine já vem instalada, basta habilitar e isso é bem simples, vá até o seu my.cnf(normalmente em /etc/my.cnf) e adicionar a palavra federated dentro da sessão do mysqld, salve e reinicie o serviço do MySQL.

[mysqld]
federated

deferated_show_engines_com

Essa engine só precisa estar habilitada em um dos servidores. Apenas no servidor que a tabela será criada.
No meu exemplo, eu habilitei no mysql56_1.

Criando a tabela

Temos duas opções para criar a tabela FEDERATED, podemos criar um SERVER CONNECTION ou CONNETION.  A diferença é que quando criamos um SERVER CONNECTION, podemos reutilizar a mesma conexão para outras tabelas e a CONNECTION todos os dados da conexão devem ser declarados no comando de CREATE TABLE.

CREATE SERVER

Esse é o modo que eu gosto de utilizar.
A primeira coisa que precisamos fazer é criar um servidor(Não um servidor físico, e sim uma conexão com outro servidor).
Vou criar a conexão no mysql56_1 se conectando no mysql56_2.

CREATE SERVER conn_mysql56_2_estados
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'usr_estados', HOST '192.168.0.21', PASSWORD '123', DATABASE 'estados');

Onde:

conn_mysql56_2_estados: é o nome da conexão, ele será utilizado no momento de criar a tabela FEDERATED.
mysql: Em FOREIGN DATA WRAPPER mysql significa que é uma conexão com outro servidor MySQL, esse valor não deve ser alterado, pois até o momento é a única disponível.
usr_estados: é o nome do usuário no servidor mysql56_2.
192.168.0.21: é o IP do mysql56_2, também pode ser utilizado DNS.
123: é a senha so usuário usr_estados.
estados: é o nome do banco de dados que eu estou me conectando no servidor mysql56_2.

Agora podemos criar a tabela.

CREATE TABLE mysql56_2_uf (
 uf char(2) NOT NULL,
 nome varchar(50) DEFAULT NULL,
 PRIMARY KEY (`uf`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='conn_mysql56_2_estados/uf';

Onde:

FEDERATED: é a engine da nossa “tabela remota”.
conn_mysql56_2_estados: é o nome da conxão criada no comando CREATE SERVER.
uf: é o nome da tabela alvo, ou seja, a tabela no servidor remoto que vamos fazer o link com a tabela mysql56_2_uf.

Atnção! Veja que no momento em que criei a tabela mysql56_2_uf eu criei com a mesma estrutura que da tabela uf do servidor mysql56_2, mas isso não é uma regra, a tabela pode ser criada apenas com as colunas necessárias.

CONNECTION

A outra opção é não criar uma conexão com um servidor, mas sim declarar toda a string de conexão no momento da criação da tabela.

CREATE TABLE mysql56_2_uf (
 uf char(2) NOT NULL,
 nome varchar(50) DEFAULT NULL,
 PRIMARY KEY (`uf`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://usr_estados:123@192.168.0.21/estados/uf';

Onde:

FEDERATED: é a engine da nossa “tabela remota”.
mysql://: é o FOREIGN DATA WRAPPER esse valor não deve ser alterado, pois até o momento é a única disponível.
usr_estados: é o nome do usuário no servidor mysql56_2.
123: é a senha so usuário usr_estados.
192.168.0.21: é o IP do mysql56_2, também pode ser utilizado DNS.
estados: é o nome do banco de dados que eu estou me conectando no servidor mysql56_2.
uf: é o nome da tabela alvo, ou seja, a tabela no servidor remoto que vamos fazer o link com a tabela mysql56_2_uf.

Atnção! Veja que no momento em que criei a tabela mysql56_2_uf eu criei com a mesma estrutura que da tabela uf do servidor mysql56_2, mas isso não é uma regra, a tabela pode ser criada apenas com as colunas necessárias.

Utilizando a tabela FEDERATED

Agora bata utilizar como se fosse uma tabela qualquer do seu servidor.

federated_select_in_federated_table

Vamos ao JOIN!

federated_select_in_federated_table_join

 

Espero que esse tutorial ajude muito vocês!

Gostou? compartilha esse artigo ai :)

Uma tarefa um tanto quanto chata de ser realizada é fazer um delete em uma tabela em um registro que possui filhos(ou dependência, como preferir) e que na criação da Foreign Key não é definido a opção ON DELETE CASCADE. Então você irá tentar apagar e irá aparecer essa mensagem de erro:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails...

Isso significa que para que seja possível apagar o registro desejado, antes será necessário apagar os filhos desse registro.

Nesse momento que bate aquela tristeza enorme, pois você não tem o MER e terá que olhar várias até achar os filhos.

Para ajudar nesse processo eu desenvolvi essa procedure que lista as tabelas que possuem filhos(apenas as tabelas que possuem filhos, pode ser que exista um FK mas a tabela não possui nenhum filho, nesse caso não irá apresentar como resultado) de um registro.

DELIMITER $
CREATE PROCEDURE sp_getDataDependency(_P_DB_ VARCHAR(64), _P_TABLE_ VARCHAR(64), _P_VALUE_ VARCHAR(200))
BEGIN
 -- This procedure returns the dependencies of a table
 -- The dependencias are checked by FOREIGN KEYS
 -- Contacts: Diego Hellas/diegohellas@gmail.com
 -- 2015-06-23
 -- _P_DB_: Database name
 -- _P_TABLE_: Table name
 -- _P_VALUE_: Value to be checked
 DECLARE v_constratint VARCHAR(64);
 DECLARE v_table VARCHAR(64);
 DECLARE v_FK_name VARCHAR(64);
 DECLARE v_FK_table_name VARCHAR(64);
 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE cursor_childrens CURSOR FOR SELECT CONSTRAINT_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = _P_DB_ AND REFERENCED_TABLE_NAME = _P_TABLE_;
 DECLARE cursor_references CURSOR FOR SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = _P_DB_ AND TABLE_NAME = @v_table AND CONSTRAINT_NAME = @v_constratint;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
 SELECT DATABASE() INTO _P_DB_;
 SET @v_query = CONCAT('DROP TABLE IF EXISTS getDataDependency'); 
 PREPARE dropTable from @v_query; 
 EXECUTE dropTable; 
 DEALLOCATE PREPARE dropTable;
 SET @v_query = CONCAT('CREATE TEMPORARY TABLE getDataDependency(TABLE_NAME VARCHAR(64), COLUMN_NAME VARCHAR(64))'); 
 PREPARE createTable from @v_query; 
 EXECUTE createTable; 
 DEALLOCATE PREPARE createTable;
 OPEN cursor_childrens;
 cursor_childrens: LOOP
 FETCH cursor_childrens INTO v_constratint, v_table;
 IF v_finished = 1 THEN 
 LEAVE cursor_childrens;
 END IF;
 SET @v_constratint = v_constratint;
 SET @v_table = v_table;
 -- --------------------------------
 OPEN cursor_references;
 cursor_references: LOOP
 FETCH cursor_references INTO v_FK_name, v_FK_table_name;
 SET @qtyResults = 0;
 SET @v_query = CONCAT('SELECT COUNT(*) FROM ', _P_DB_ , '.' , v_FK_table_name , ' WHERE ', v_FK_name, ' = ', _P_VALUE_ , ' INTO @qtyResults'); 
 PREPARE selectTable from @v_query; 
 EXECUTE selectTable; 
 DEALLOCATE PREPARE selectTable; 
 IF @qtyResults > 0 THEN 
 SET @v_query = CONCAT('INSERT INTO getDataDependency(TABLE_NAME, COLUMN_NAME) VALUES("', v_FK_table_name ,'", "', v_FK_name ,'")'); 
 PREPARE insertTable from @v_query; 
 EXECUTE insertTable; 
 DEALLOCATE PREPARE insertTable; 
 END IF;
 LEAVE cursor_references;
 END LOOP cursor_references;
 CLOSE cursor_references;
 -- ---------------------------------
 END LOOP cursor_childrens;
 CLOSE cursor_childrens;
 SELECT * FROM getDataDependency;
END$
DELIMITER ;

Como usar:

call sp_getDataDependency(‘Database name’, ‘Table name’, ‘PK_value’);

Isso irá me ajudar muito, espero que ajude vocês também.

Abraço

 

Um procedimento comum após a instalação do MySQL é alterar o datadir e normalmente é para outra unidade de disco. Nos servidores que eu administro sempre busco deixar o datadir em uma unidade de disco e a gravação de logs em outra, isso faz não existir concorrência de I/O no momento de leitura ou gravação de dados.

Eu normalmente utilizo CentOS para o MySQL mas devido a um projeto acabei utilizando Ubuntu 14.04. Toda a instalação, tuning, etc… foi maravilhosamente bem, até o momento em que eu foi alterar o datadir do MySQL.

Então eu parei o serviço do MySQL com o comando /etc/init/d/mysql stop , e editei o /etc/mysql/my.cnf para alterar as variáveis de datadir e variáveis de diretório das tabelas InnoDB.

datadir         = /database/mysql
innodb_data_home_dir = /database/mysql
innodb_log_group_home_dir = /database/mysql

Nesse momento os problemas começaram, o serviço do MySQL não levantou de forma alguma, lendo o log de erros ficou claro que o problema era permissão para acessar os arquivos no novo diretório.

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2015-04-07 16:31:39 13641 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

Eu achei bem estranha essa mensagem, pois quando copiei eu tomei o cuidado para copiar as permissões corretamente utilizando o comando cp -pR /var/lib/mysql/ /database/. Nesse momento comecei a brincar com as permissões, testeis todas as combinações que vieram na minha cabeça e a mensagem de erro era sempre a mesma. Nesse momento me dei conta que o problema não estava no MySQL, mas sim no SO.
Algum tempo atrás eu tive problemas com o comando LOAD DATA INFILE no ubuntu, onde a mensagem exibida era.

ERROR 29 (HY000): File '....' not found (Errcode: 13)

Acho que o que fez eu ligar uma coisa na outra foi o Errcode/errno 13.

Acontece que o Ubuntu tem um cara chamado apparmor e é necessário configurar ele para indicar onde o MySQL para ler/gravar os seus dados.

Para resolver o problema edite o arquivo /etc/apparmor.d/usr.sbin.mysqld

Procure por

# Allow data dir access
 /var/lib/mysql/ r,
 /var/lib/mysql/** rwk,

Comente essas duas linhas e adiciona novas linhas com o novo diretório

# Allow data dir access
 #/var/lib/mysql/ r,
 #/var/lib/mysql/** rwk,
 /database/mysql/ r,
 /database/mysql/** rwk,

Recarregue o apparmor

/etc/init.d/apparmor reload

Inicie o serviço do MySQL

/etc/init.d/mysql start

Após alterar o datadir e reiniciar o serviço do MySQL, execute o mysql_upgrade, ele irá corrigir algum possível problema em tabelas internas do MySQL. Veja mais em https://dev.mysql.com/doc/refman/5.6/en/mysql-upgrade.html

Pronto, agora o serviço do MySQL pode ser iniciado com sucesso.

 

Quando temos uma replicação envolvendo mais de 1 servidor master temos que tomar o cuidado para que a replicação não pare de funcionar, e o primeiro problema que qualquer DBA enfrenta é o erro de chave primária duplicada.

Duplicate entry '1' for key 'PRIMARY'

Porém é muito simples e fácil não passar por esse problema.
Temos 2 variáveis fundamentais e é muito simples a sua correta configuração.
Obs: O que eu mais vejo por ai é configurado da forma errada.

auto_increment_increment

Define o intervalo dos valores do das colunas auto_increment.

auto_increment_offset

Define o valor inicial das colunas auto_increment.

Agora vem a parta mais fácil, que é a configuração.
Vamos supor que temos uma infraestrutura com 3 servidores MySQL, vamos chamar de A, B e C. Eles estão configurados com uma replicação em formato de anal, onde todos são masters.

A
/    \
B  — C

O valor da variável auto_increment_increment sempre será o número de servidores masters que fazem parte da replicação, e o valor da variável auto_increment_offset será diferente em todos os servidores e o seu valor é sequencial, começando pelo número 1.

Então teremos:

Servidor A:
auto_increment_increment = 3
auto_increment_offset = 1
Servidor B:
auto_increment_increment = 3
auto_increment_offset = 2
Servidor C:
auto_increment_increment = 3
auto_increment_offset = 3

Pronto, isso é o suficiente para que os erros de chave primária duplicada não aconteçam mais na replicação.

Aulas ao vivo sem que você precise sair de casa.

Uma informação legal, mais do que legal, importante, mais do que importante, necessária!

Quem nunca perguntou ou foi perguntado sobre qual o tamanho de um banco de dados ou de uma tabela?
Quando as tabelas são MyISAM da para olhar o tamanho dos arquivos, ou se for InnoDB e estiver com o innodb_per_tables habilitada até conseguimos, mas temos que nos conectarmos no servidor, acessar o diretórios e ai olhar os arquivos, depois somar os valores, etc…

Com esses simples SELECTs conseguimos essas respostas.

Verificar os tamanhos dos databases

SELECT table_schema AS 'DB', FORMAT(SUM(TABLE_ROWS), 0) AS 'Linhas', ROUND(SUM(DATA_LENGTH + INDEX_LENGTH )/1024/1024, 3) AS 'MB'
FROM information_schema.TABLES
GROUP BY table_schema 
WITH ROLLUP;

Verificar os tamanhos das tabelas de um database

SELECT TABLE_NAME AS 'Tabela', FORMAT(SUM(TABLE_ROWS ), 0) AS 'Linhas', ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024, 3 ) AS 'MB'
FROM information_schema.TABLES
WHERE table_schema = 'nome do database'
GROUP BY TABLE_NAME
WITH ROLLUP;

Obs: Os valores apresentados podem sofrer variação, principalmente quando o banco não está sendo usado, isso se deve ao fato desses valores serem atualizados quando o mysqld achar mais conveniente, e normalmente ele prefere nos momentos em que não tem concorrência.

Aulas ao vivo sem que você precise sair de casa.

Isso não é um tarefa normal muito menos rotineira. Porém volta e meia me deparo com situações onde eu ou colegas necessitam localizar algum valor no MySQL, porém sem fazer ideia de em qual tabela se encontra.
Para isso desenvolvi essa procedure.
Ela se encarrega de procurar algum valor em todas as colunas de todas.

Use com moderação, pois dependendo da quantidade de dados que você tiver, pode afetar o desempenho do servidor.

DELIMITER $
CREATE PROCEDURE sp_searchValueInDB(_P_VALUE_ varchar(100), _P_DB_ varchar(64), _P_TYPE_ ENUM('char', 'numeric', 'date'))
BEGIN
 -- This procedure performs searches in all columns of a database.
 -- The execution performance will depend greatly on the size of your database.
 -- Contacts: Diego Hellas/diegohellas@gmail.com
 -- 2014-10-13
 -- _P_VALUE_: what is sought
 -- _P_DB_: Database name for search
 -- _P_TYPE_: Type of data that will be fetched, it helps reduce the columns to be searched, which makes it faster.
 DECLARE v_collumn VARCHAR(64);
 DECLARE v_table VARCHAR(64);
 DECLARE v_query VARCHAR(500);
 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE cursor_collumns CURSOR FOR SELECT COLUMN_NAME, TABLE_NAME FROM tmp_collumns;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

 -- Drop tmp table if exists
 SET @v_query = CONCAT('DROP TABLE IF EXISTS tmp_collumns'); 
 PREPARE dropTable from @v_query; 
 EXECUTE dropTable; 
 DEALLOCATE PREPARE dropTable; 

 -- Cretae tmp table for the CURSOR use
 SET @v_query = CONCAT('CREATE TABLE tmp_collumns (COLUMN_NAME varchar(64), TABLE_NAME varchar(64))'); 
 PREPARE createTable from @v_query; 
 EXECUTE createTable; 
 DEALLOCATE PREPARE createTable; 
 
 -- Select only the types of expected columns
 IF _P_TYPE_ = 'char' THEN
 INSERT INTO tmp_collumns SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = _P_DB_ AND DATA_TYPE IN("CHAR", "VARCHAR", "BINARY", "VARBINARY", "BLOB", "TEXT", "TINYTEXT" , "ENUM", "SET");
 END IF;
 IF _P_TYPE_ = 'numeric' THEN
 INSERT INTO tmp_collumns SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = _P_DB_ AND DATA_TYPE IN("tinyint", "smallint" "mediunint", "int", "bigint", "decimal", "bit", "double", "float");
 END IF;
 IF _P_TYPE_ = 'date' THEN
 INSERT INTO tmp_collumns SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = _P_DB_ AND DATA_TYPE IN("DATE","TIME", "DATETIME", "TIMESTAMP", "YEAR");
 END IF;

 
 -- CURSOR LOOP
 OPEN cursor_collumns;
 cursor_collumns: LOOP
 FETCH cursor_collumns INTO v_collumn, v_table;
 SET @qtyResults = 0;
 IF v_finished = 1 THEN 
 LEAVE cursor_collumns;
 END IF;
 set @v_query=concat('SELECT COUNT(*) FROM ', v_table, ' WHERE ', v_collumn, ' LIKE "%', _P_VALUE_, '%" INTO @qtyResults'); 
 prepare searchValueQuery from @v_query;
 execute searchValueQuery;
 IF @qtyResults > 1 THEN 
 SELECT v_table, v_collumn, @qtyResults;
 END IF;
 DEALLOCATE PREPARE searchValueQuery;
 END LOOP cursor_collumns;
 CLOSE cursor_collumns;


 -- Drop tmp table if exists
 SET @v_query = CONCAT('DROP TABLE IF EXISTS tmp_collumns'); 
 PREPARE dropTable from @v_query; 
 EXECUTE dropTable; 
 DEALLOCATE PREPARE dropTable; 
END$
DELIMITER ;

Após criar a procedure, basta fazer bom uso.
Ex: call sp_searchValueInDB(‘meu_db’, ‘admin’, ‘char’);

Aulas ao vivo sem que você precise sair de casa.

Se tem algo que é extremamente chato de fazer, principalmente quando não existe online de um Banco de Dados, é localizar uma coluna no meio de dezenas de tabelas.

Com esse SELECT, você pode facilmente localizar as tabelas que contém alguma coluna com o nome que você precisa.

SELECT TABLE_SCHEMA AS DB, TABLE_NAME AS Tabela, COLUMN_NAME AS coluna
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE
 TABLE_SCHEMA = 'nome do seu database'
 AND COLUMN_NAME = 'nome da sua coluna'

Também pode user utilizado o operador LIKE no lugar o =
Ex: AND COLUMN_NAME LIKE ‘%nome da sua coluna%’

Eu uso muito esse SELECT, espero que ajude vocês também.

Esse é um assunto pouco abordado, muito negligenciado e EXTREMAMENTE importante.
Nesse artigo vamos ver 3 formas de realizar o backup, restore, otimizações e o tão polêmico backup incremental no MySQL.

Vamos ver o MYSQLDUMP, INTO OUTFILE/LOAD DATA INFILE e LOG BINÁRIO. São 3 formas distintas de realizar uma tarefa tão importante.

MYSQLDUMP

Método tradicional e bem difundido, ele realiza a extração das estruturas de databasestables e dados para um arquivo texto, onde o conteúdo são comandos DDL e DML. Apesar de ser o método usado por quase todo mundo, as suas opções são bem pouco exploradas. Existem muitas reclamações sobre desempenho, gerar lock tables, não poder realizar backp incremental, etc… Tudo isso pode ser verdade, desde que não utilizadas as opções corretas.

Artigo sobre mysqldump

INTO OUTFILE/LOAD DATA INFILE

Ao contrário do MYSQLDUMP, usar o INTO OUTFILE/LOAD DATA INFILE não é nem um pouco convencional, e isso se deve ao fato de ele ser extremamente trabalhoso. Para realizar o backup dessa maneira é necessário escrever as sentenças SQL para cada uma das tabelas que deseja realizar o backup. Outro fator importante é que as estruturas das tabelas não irão estar no backup, essas estruturas necessitam de um backup através do MYSQLDUMP.

Mas se é tão trabalhoso assim, qual o motivo de eu pensar em usar ele? A resposta é: velocidade no momento do restore. Segundo a documentação do MySQL, esse processo é cerca de 20x mais rápido que quando comparado com um restore de um backup gerado pelo MYSQLDUMP. Mas isso pode melhorar ainda mais se alguns procedimentos forem adotados.

LOG BINÁRIO

O LOG BINÁRIO é um arquivo de log, onde é armazenado todos os comandos DDL e DML que alteram dados que são executados no MySQL, e se ele armazena tudo que altera dados e estruturas, ele pode ser utilizado como um método de backup.
Esse método requer um planejamento bem grande, tem muitos pontos que se não for tomados os devidos cuidados, algo pode sair errado, porem ele pode se tornar a maneira mais fácil de realizar backups.
ATENÇÃO, o backup através do LOG BINÁRIO sempre será incremental e nunca FULL, por isso sempre guarde com segurança todos os arquivos do log binário. Através do log binário é possível voltar para qualquer ponto na linha do tempo.

 

E você, como você garante que seus backups estão íntegros e atualizados? 

 

Aulas ao vivo sem que você precise sair de casa.

Nesse post vamos ver como utilizar o log binário para realizar backups e restore.

Esse é o método de backup mais complicado para ser gerenciado, porém se bem arquitetado é uma poderosa ferramenta de backup.
O logbin é um log de eventos que guarda todas as alterações que são feitas no servidor MySQL, seja de comandos DDL ou DML. Ele é usado em sua grande maioria dos casos apenas para a replicação, mas ele pode(e deve) ser utilizado como backup(incremental), uma vez que tudo que que altera o seu servidor é registrado.

O primeiro passo é habilitar o log binário, para isso edite o my.cnf

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log

Como todas as alterações são escritas no logbin, ele tende a crescer bastante e é interessante adicionar opções, como por exemplo, mas cuidado, entende bem para que serve cada uma das opções.

Remover os arquivos com mais de X dias
expire_logs_days = 10

Quando o arquivo chegar em um determinado tamanho, outro será gerado.
max_binlog_size = 100M

Veja mais sobre logbin nesse link: http://dev.mysql.com/doc/refman/5.6/en/binary-log.html

Após fazer as alterações no my.cnf, reinicie o MySQL: /etc/init.d/mysql restart

Para verificar se ele realmente está ativado, pode ser consultada as variáveis do MySQL.

mysql> show variables like ‘log_bin';
+——————+——-+
| Variable_name | Value |
+——————+——-+
| log_bin               | ON     |
+——————+——-+
1 row in set (0.00 sec)

Você precisa do MySQL e nós temos a melhor solução.

Uma coisa BEM IMPORTANTE é ter em mente que apenas após o ser ativado é que os comandos DDL e DML começam a ser gravados no logbin, os dados que já estavam antes no servidor não vão para dentro do logbin. E outra coisa, é que o quando usamos o logbin para backup, ele é um backup incremental, ou seja, para que seja possíevl restaurar é necessário ter feito cópias de todos os logbins e restaurar todos na sequencia correta, e estar ciente que isso pode demorar muito, pois é um histórico todo de alterações, por isso é mais comum usar o logbin apenas em situações de emergência, para restaurar a partir de um certo ponto o backup.

Uma vez, eu desenvolvi uma rotina de backup em que era feito um backup full com o mysqldump durante a madrugada, e durante o dia, a cada 30 minutos era realizada a copia dos logs binários, com isso, caso tivesse algum problema, o período máximo de dados perdidos que teria, seria de 30 minutos.

Para restaurar o backup é bem simples, o MySQL tem o aplicativo mysqlbinlog, com ele podemos extrair os dados do logbin e depois importar para o MySQL. Para mais informações sobre mysqlbinlog, veja esse link: http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html

Exportando os dados do logbin para um arquivo de texto:

mysqlbinlog [opções] <caminho completo para o logbin>

Ex: mysqlbinlog /var/log/mysql/mysql-bin.007

Isso irá mostrar ne tela, o conteúdo inteiro do logbin, o que não nos interessa. Então temos que redirecionar a saída para um arquivo texto, igual fazemos com o mysqldumo.

Ex: mysqlbinlog /var/log/mysql/mysql-bin.007 > /backup/xpto/restore_logbin_xpto.sql

Algumas opções interessantes de parâmetros que temos para usar o mysqlbinlog. Elas pode ser utilizadas sozinhas ou

–start-datetime
Define a partir de que data os dados devem ser lidos do logbin.

Ex: mysqlbinlog –start-datetime=”2014-09-01 00:00:00″ /var/log/mysql/mysql-bin.007 > /backup/xpto/restore_logbin_xpto.sqltper

–stop-datetime
Define até qual data os dados devem ser lidos o logbin.

Ex: mysqlbinlog –stop-datetime=”2014-09-02 23:59:59″ /var/log/mysql/mysql-bin.007 > /backup/xpto/restore_logbin_xpto.sql

Usando as duas opções em conjunto

Ex: mysqlbinlog –start-datetime=”2014-09-01 00:00:00″ –stop-datetime=”2014-09-02 23:59:59″ /var/log/mysql/mysql-bin.007 > /backup/xpto/restore_logbin_xpto.sql
Após isso, temos um arquivo texto com comandos DDL e DML que podem ser importados pelo MySQL.

Ex: mysql -uroot -p db_name2 < /backup/xpto/restore_logbin_xpto.sql

Na teoria, gerenciamento de backup com o logbin é a mais simples, mas na pratica é a mais complicada, então antes faça vários testes para ter certeza que ele irá atender a sua necessidade e que todas as suas dúvidas foram sanadas.

Aulas ao vivo sem que você precise sair de casa.