MySQL Sandbox

Hoje vamos falar sobre uma excelente ferramenta que todo DBA MySQL precisa ter em mãos, estou falando do MySQL Sandbox.

MySQL Sandbox é desenvolvido por Giuseppe Maxia (The Data Charmer), esta ferramenta auxilia na instalação de servidores para testes. Se você precisa testar algum bug, algum caso especifico relacionado a replicação(suporta master/slave e master/master) esta é a ferramenta que tens que conhecer.

INSTALAÇÃO:

Vá até http://mysqlsandbox.net/ e baixe a versao mais atual (Pode ser via launchpad):

yum install perl perl-ExtUtils-MakeMaker perl-Test-Simple
wget https://launchpad.net/mysql-sandbox/mysql-sandbox-3/mysql-sandbox-3/+download/MySQL-Sandbox-3.0.44.tar.gz
tar -zxvf MySQL-Sandbox-3.0.44.tar.gz
cd MySQL-Sandbox-3.0.44
perl Makefile.PL
make
make test
make install

CRIANDO UMA INSTÂNCIA:

Para criar uma única instância, tudo o que precisamos é o pacote (tar.gz .rpm .deb) da versão do MySQL que desejamos instalar e o comando make_sandbox:

[root@localhost ~]# make_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
unpacking /root/mysql-5.6.17-linux-glibc2.5-i686.tar.gz
Executing low_level_make_sandbox --basedir=/root/5.6.17 \
	--sandbox_directory=msb_5_6_17 \
	--install_version=5.6 \
	--sandbox_port=5617 \
	--no_ver_after_name \
	--my_clause=log-error=msandbox.err
    The MySQL Sandbox,  version 3.0.44
    (C) 2006-2013 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /root/sandboxes
sandbox_directory              = msb_5_6_17
sandbox_port                   = 5617
check_port                     = 
no_check_port                  = 
datadir_from                   = script
install_version                = 5.6
basedir                        = /root/5.6.17
tmpdir                         = 
my_file                        = 
operating_system_user          = root
db_user                        = msandbox
remote_access                  = 127.%
bind_address                   = 127.0.0.1
ro_user                        = msandbox_ro
rw_user                        = msandbox_rw
repl_user                      = rsandbox
db_password                    = msandbox
repl_password                  = rsandbox
my_clause                      = log-error=msandbox.err
master                         = 
slaveof                        = 
high_performance               = 
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > 
force                          = 
no_ver_after_name              = 1
verbose                        = 
load_grants                    = 1
no_load_grants                 = 
no_run                         = 
no_show                        = 
do you agree? ([Y],n) 
loading grants
.. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_6_17

Para usarmos esta instância, podemos chamar o script use que se encontra dentro da pasta que o MySQL Sandbox acabou de criar.

[root@localhost ~]# $HOME/sandboxes/msb_5_6_17/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > 

Podemos gerenciar esta instância usando os scripts start / stop / restart / status, que também se encontram dentro da pasta criada.

CRIANDO REPLICAÇÃO MASTER SLAVE:

Para criar um conjunto de instâncias configuradas como master / slave (padrão: 1 master e 2 slaves, mas podemos alterar este comportamento passando o parâmetro --how_many_nodes ) vamos utilizar o comando make_replication_sandbox:

[root@localhost ~]# make_replication_sandbox mysql-5.6.17-linux-glibc2.5-i686.tar.gz 
installing and starting master
installing slave 1
installing slave 2
starting slave 1
.... sandbox server started
starting slave 2
.. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_mysql-5_6_17

Para usar as instâncias criadas, podemos utilizar o script use que está localizado dentro das pastas nodeN (onde N é o numero da instância) e master:

[root@localhost ~]# #MASTER
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/master/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 1
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node1/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave1 [localhost] {msandbox} ((none)) > ^DBye
[root@localhost ~]# #SLAVE 2
[root@localhost ~]# /root/sandboxes/rsandbox_mysql-5_6_17/node2/use 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave2 [localhost] {msandbox} ((none)) > 

Para instâncias configuradas como replicação, podemos gerenciar cada uma delas também chamando o script start / stop / restart / status que está dentro das pastas master/nodeN ou podemos chamar os scripts terminados com _all (start_all / stop_all / restart_all / status_all) que se encontram na pasta raiz da sandbox criada.

CRIANDO REPLICAÇÃO MULTI MASTER :

Para criarmos instâncias configuradas como multi-master’s, vamos utilizar o comando make_replication_sandbox com a opção --master_master:

[root@localhost ~]# make_replication_sandbox --master_master mysql-5.6.17-linux-glibc2.5-i686.tar.gz
installing node 1
installing node 2
# server: 1: 
# server: 2: 
# server: 1: 
# server: 2: 
Circular replication activated
group directory installed in $HOME/sandboxes/rcsandbox_mysql-5_6_17

É isso aí pessoal, podemos achar mais informações usando o parâmetro –help nos comandos make_multiple_custom_sandbox make_multiple_sandbox make_replication_sandbox make_sandbox make_sandbox_from_installed and make_sandbox_from_source.

Gostou ? Ajude e Compartilhe!
Publicado em instalação, MySQL, replicacao | 3 Comentários

Esquentando o InnoDB Buffer Pool

Uma das mais importantes configurações para quem usa InnoDB é o innodb_buffer_pool_size, ele basicamente armazena dados e índices em memória, quando o MySQL recebe uma query e as paginas que contem o resultado da pesquisa/índice estao armazenadas no buffer, o MySQL não precisar ler essas informações do disco, o que é muito mais rápido (velocidade da memória vs velocidade do disco) .

Como estas informações estão armazenadas na memória, cada vez que o MySQL é re-iniciado, o buffer é apagado, e para “esquentar” o buffer normalmente leva-se algum tempo.

Para agilizar este processo, vamos utilizar 2 variáveis para salvar e restaurar as referencias das paginas que estão armazenadas no buffer, esta é uma nova funcionalidade adicionada no MySQL 5.6 (essa opção já pode ser encontrada em algumas versões anteriores do Percona Server e MariaDB )

Se o seu servidor já está rodando e com o buffer “quente”, vamos setar a variável innodb_buffer_pool_dump_at_shutdown para fazer efeito no próximo shutdown ou restart.

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

Agora, no próximo shutdown ou restart, um arquivo chamado ib_buffer_pool será criado no datadir do MySQL (você pode alterar este nome configurando a variável innodb_buffer_pool_filename ).

Próximo passo é configurar o MySQL para que ele leia o conteúdo deste arquivo e carregue os dados de volta para o InnoDB buffer, para isso, vamos adicionar a variável innodb_buffer_pool_load_at_startup ao my.cnf (e também a variável innodb_buffer_pool_dump_at_shutdown, para que o MySQL salve o buffer pool a cada shutdown/restart):

[mysqld]
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Agora podemos reiniciar o MySQL, como mencionado anteriormente, um arquivo será criado no datadir do MySQL, o conteúdo é similar ao seguinte:

[root@marcelodb data]# tail ib_buffer_pool
0,69618
0,69619
0,69620
0,69621
0,69622
0,69623
0,69624
0,69625
0,69626
0,69627

Quando reiniciamos o MySQL, veremos 2 mensagens no log:

2014-01-07 19:53:54 7fad34bc7700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2014-01-07 19:53:54 7fad34bc7700 InnoDB: Buffer pool(s) dump completed at 140107 19:53:54

Isto significa que o MySQL escreveu o arquivo contendo as referencias as paginas que estavam armazenadas no InnoDB buffer poll.

2014-01-07 19:54:01 7f68eee67700 InnoDB: Buffer pool(s) load completed at 140107 19:54:01

Esta mensagem, significa que o MySQL terminou de restaurar o conteúdo do InnoDB buffer pool de volta.

Este progresso pode ser controlado através das variáveis Innodb_buffer_pool_dump_status e Innodb_buffer_pool_load_status:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+------------------------+
| Variable_name                  | Value                  |
+--------------------------------+------------------------+
| Innodb_buffer_pool_load_status | Loaded 5121/6441 pages |
+--------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140108 16:55:05 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

Case deseja, pode se salvar/restaurar o conteúdo do InnoDB buffer pool através da variáveis innodb_buffer_pool_dump_now e innodb_buffer_pool_load_now.

Caso utilize InnoDB como storage engine em alguma tabela, este processo pode trazer grandes benefícios e minimizar os impactos de um restart.

Gostou ? Ajude e Compartilhe!
Publicado em InnoDB, MySQL, optimização, performance | 6 Comentários

Instalar a versão mais recente do MySQL via yum

Normalmente quando instalamos o MySQL via yum, a versão que vem por padrão nos repositórios do SO são versões antigas(por antigas, leia, não a ultima versão disponível em mysql.com/downloads).
Para ter a ultima versão instalada, sempre temos que ir em mysql.com/downloads, baixar e instalar os pacotes manualmente, certo? Errado, MySQL lançou recentemente seu Repositório YUM.
Basicamente, este repositório lhe permite instalar as ultimas versões GA(General Available) do MySQL Community Server, Workbench e Connector/ODBC.

COMO INSTALAR ?
Temos 2 opções para adicionar este repositório:

1. Usando os pacotes RPM
Baixe o .rpm para a sua versão de SO em dev.mysql.com/downloads/repo/ e instale:

wget http://dev.mysql.com/get/mysql-community-release-el6-3.noarch.rpm/from/http://repo.mysql.com/
sudo yum localinstall -y mysql-community-release-el6-3.noarch.rpm

2. Adicionando um arquivo manualmente na pasta /etc/yum.repos.d/
Adicione o conteúdo abaixo em um arquivo chamado mysql-community.repo:

[mysql-community]
name=MySQL Community Server 
baseurl=URL
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Substitua a linha contendo baseurl=URL de acordo com seu SO:
EL6 (Centos6 / Redhat 6):

baseurl=http://repo.mysql.com/yum/mysql-community/el/6/$basearch/

Fedore 18 and 19:

baseurl=http://repo.mysql.com/yum/mysql-community/fc/$releasever/$basearch/

Note que temos o parâmetro gpgcheck=1, se este parâmetro estive configurado como 1, devemos adicionar as chamadas GnuPG key’s (veja http://dev.mysql.com/doc/refman/5.6/en/checking-gpg-signature.html para mais detalhes).

INSTALANDO OS PACOTES:
Agora que temos o repositório ativado, vamos usar os comandos normais do yum para instalar a ultima versão do MySQL Community Server:

yum install mysql-server

LISTANDO OS PACOTES DISPONÍVEIS:
Para listar os pacotes disponíveis no repositório, use o comando abaixo:

sudo yum --disablerepo=\* --enablerepo=mysql-community list available
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
mysql-community-client.x86_64                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-common.i686                                                                                                                                       5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-common.x86_64                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-devel.i686                                                                                                                                        5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-devel.x86_64                                                                                                                                      5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded.i686                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded.x86_64                                                                                                                                   5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded-devel.i686                                                                                                                               5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-embedded-devel.x86_64                                                                                                                             5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs.i686                                                                                                                                         5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs.x86_64                                                                                                                                       5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs-compat.i686                                                                                                                                  5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-libs-compat.x86_64                                                                                                                                5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-server.x86_64                                                                                                                                     5.6.14-3.el6                                                                                                                              mysql-community
mysql-community-test.x86_64                                                                                                                                       5.6.14-3.el6                                                                                                                              mysql-community
mysql-connector-odbc.x86_64                                                                                                                                       5.2.6-1.el6                                                                                                                               mysql-community
mysql-workbench-community.x86_64                                                                                                                                  6.0.7-4.el6                                                                                                                               mysql-community

Lembre de testar, testar e testar antes de alterar alguma coisa em produção 🙂

Gostou ? Ajude e Compartilhe!
Publicado em instalação, MySQL | 1 Comentário

Auditando MySQL Com Mcafee Audit Plugin

mysql-audit

Auditar o MySQL com as funcionalidades padrão, não é uma tarefa fácil, pode se tentar algumas tecnicas utilizando tcpdump, escrever algum script baseado no general log (log geral), utilizar o MySQL proxy, ou pode se utilizar algum plugin designado a isso (como por exemplo Mcafee MySQL Audit Plugin ou MySQL Enterprise Audit Log Plugin) .

Neste poste vou abortar o Mcafee MySQL Audit Plugin (https://github.com/mcafee/mysql-audit), em um proximo post abortarei o MySQL Enterprise Audit Log Plugin.

A instalação é simples, requere apenas alguns passos, estou utilizando o MySQL 5.5 32 bits, então vou baixar a versão do plugin 32 bits para MySQL 5.5 em https://github.com/mcafee/mysql-audit/downloads

[root@mysql-audit marcelo]# wget https://github.com/downloads/mcafee/mysql-audit/audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
[root@mysql-audit marcelo]# unzip audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
Archive:  audit-plugin-mysql-5.5-1.0.3-371-linux-i386.zip
   creating: audit-plugin-mysql-5.5/
   creating: audit-plugin-mysql-5.5/lib/
  inflating: audit-plugin-mysql-5.5/lib/libaudit_plugin.so  
  inflating: audit-plugin-mysql-5.5/COPYING  
  inflating: audit-plugin-mysql-5.5/THIRDPARTY.txt  
  inflating: audit-plugin-mysql-5.5/README.txt

Proximo passo é copiar o arquivo libaudit_plugin.so para o plugin dir do MySQL:

mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'plugin_dir';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| plugin_dir    | /var/marcelo/5.5.33/lib/plugin/ |
+---------------+---------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > quit
Bye
[root@mysql-audit audit-plugin-mysql-5.5]# cp lib/libaudit_plugin.so /var/marcelo/5.5.33/lib/plugin/

Para que o plugin funcione, o Mcafee MySQL Audit Plugin precisa de alguns offsets do MySQL server, alguns já estão inclusos no código fonte, e outros não, precisamos extrair alguns offsets (veja https://github.com/mcafee/mysql-audit/wiki/Troubleshooting para mais informações).
Vamos precisar do GBD para extrair os offsets, o pacote pode ser obtido via yum install gdb or apt-get install gdb

[root@mysql-audit marcelo]# wget https://raw.github.com/mcafee/mysql-audit/v1.0.3/offset-extract/offset-extract.sh
[root@mysql-audit marcelo]# ./offset-extract.sh 5.5.33/bin/mysqld
//offsets for: 5.5.33/bin/mysqld (5.5.33)
{"5.5.33","3172729c5bf6e81c8d87fe26fe248204", 3816, 3844, 2368, 2700, 44, 1656},

Agora, vamos fazer algumas alterações no arquivo .cnf(my.cnf) para ativar o plugin:

# Adicione dentro da sessão [mysqld] 
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=3816, 3844, 2368, 2700, 44, 1656
audit_json_file=1

Agora o que precisamos fazer é apenas reinicar o serviço do MySQL, por padrão, um arquivo chamado mysql-audit.json é criado dentro do datadir do MySQL, basta agora tu extrair a informação que achares relevante, veja abaixo alguns exemplos:

Tentativas de conexão não bem sucedias:

[root@mysql-audit data]# cat mysql-audit.json | grep '\"cmd\":\"Failed Login\"'
{"msg-type":"activity","date":"1381764013653","thread-id":"65","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764016225","thread-id":"66","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764026005","thread-id":"67","query-id":"0","user":"root","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}

Tentativas de conexão bem sucedias:

[root@mysql-audit data]# cat mysql-audit.json | grep '\"cmd\":\"Connect\"'
{"msg-type":"activity","date":"1381763915626","thread-id":"60","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763915626","thread-id":"61","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763915626","thread-id":"62","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1381763916006","thread-id":"64","query-id":"0","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"Connect","query":"Connect"}

Todas as atividades de um host especifico:

[root@mysql-audit data]# cat mysql-audit.json | grep '\"ip\":\"10.10.2.68\"'
{"msg-type":"activity","date":"1381763919836","thread-id":"60","query-id":"4798","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"SELECT SUM(K) FROM sbtest8 WHERE id BETWEEN 151257 AND 151257+99"}
{"msg-type":"activity","date":"1381763919844","thread-id":"62","query-id":"4799","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest6","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest6 WHERE id BETWEEN 141568 AND 141568+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919847","thread-id":"47","query-id":"4800","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"update","objects":[{"db":"test","name":"sbtest3","obj_type":"TABLE"}],"query":"UPDATE sbtest3 SET k=k+1 WHERE id=150189"}
{"msg-type":"activity","date":"1381763919848","thread-id":"60","query-id":"4801","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest8 WHERE id BETWEEN 175916 AND 175916+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919869","thread-id":"64","query-id":"4803","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"select","objects":[{"db":"test","name":"sbtest10","obj_type":"TABLE"}],"query":"SELECT DISTINCT c FROM sbtest10 WHERE id BETWEEN 132850 AND 132850+99 ORDER BY c"}
{"msg-type":"activity","date":"1381763919881","thread-id":"57","query-id":"4784","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"commit","query":"COMMIT"}
{"msg-type":"activity","date":"1381763919930","thread-id":"37","query-id":"4802","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"commit","query":"COMMIT"}
{"msg-type":"activity","date":"1381764013653","thread-id":"65","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764016225","thread-id":"66","query-id":"0","user":"msandbox","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1381764026005","thread-id":"67","query-id":"0","user":"root","priv_user":"","ip":"10.10.2.68","cmd":"Failed Login","query":"Failed Login"}

Todos os comandos DELETE em uma especifica tabela(sbtest8) de um especifico host:

[root@mysql-audit data]# cat mysql-audit.json | grep -i '\"cmd\":\"delete\"' | grep -i '\"name\":\"sbtest8\"' | grep '\"ip\":\"10.10.2.68\"'
{"msg-type":"activity","date":"1381763914163","thread-id":"53","query-id":"3366","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=150652"}
{"msg-type":"activity","date":"1381763914947","thread-id":"44","query-id":"3513","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=194099"}
{"msg-type":"activity","date":"1381763915718","thread-id":"36","query-id":"3630","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=151405"}
{"msg-type":"activity","date":"1381763916273","thread-id":"48","query-id":"3799","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=148546"}
{"msg-type":"activity","date":"1381763918698","thread-id":"40","query-id":"4437","user":"msandbox","priv_user":"msandbox","ip":"10.10.2.68","cmd":"delete","objects":[{"db":"test","name":"sbtest8","obj_type":"TABLE"}],"query":"DELETE FROM sbtest8 WHERE id=149492"}

Como pode ver, tu pode extrair e ajustar o log para se enquadrar as tuas necessidades, tu pode também adicionar algumas configurações ao plugin para mudar um pouco o seu comportamento (https://github.com/mcafee/mysql-audit/wiki/Configuration para mais informações) :

audit_record_cmds – lista de comandos que deseja gravar no log, por exemplo, se tu deseja gravar apenas tentativas de conexão configure esta opção como: audit_record_cmds=”Failed Login,Connect”
audit_record_objs – lista de banco de dados/tabelas que deseja gravar no log, por exemplo, se tu deseja gravar apenas atividades no banco de dados test, configure esta opcao como: audit_record_objs=”test.*” , se tu deseja gravar apenas atividades nas tabelas sbtest1 e sbtest2 do banco de dados chamado test, configure esta opção como: audit_record_objs=”test.sbtest1,test.sbtest2″

É isso ai, use sua imaginação e comece a auditar o seu MySQL.

Gostou ? Ajude e Compartilhe!
Publicado em instalação, mysqld, Security | 7 Comentários

Multi-Source Replication com MySQL 5.7 – exemplo

Dando continuidade ao post anterior vamos ver um exemplo de como configurar 2 masters e 1 slave utilizando multi-source replication.
Como foi dito anteriormente, por enquanto esta funcionalidade esta disponível somente na versão labs.
A configuração em si é muito simples, vamos precisar de 2 masters utilizando GTID(veja este outro post e aprenda como configurar) e o slave com as opções para garantir o chamado “crash-safe”.
Master 1 e 2:

gtid-mode=on
enforce-gtid-consistency

Slave

master_info_repository=TABLE
relay_log_info_repository=TABLE
gtid-mode=on
enforce-gtid-consistency

Vamos primeiro criar o usuário para replicação:

master1 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'127.0.0.1' IDENTIFIED BY '123';
master2 [localhost] {msandbox} ((none)) > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'127.0.0.1' IDENTIFIED BY '123';

Vamos configurar o slave normalmente, com apenas um novo detalhe, foi introduzida a opção FOR CHANNEL. Essa opção basicamente vai identificar cada master que o nosso slave vai se conectar:

slave [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_PORT=7773 FOR CHANNEL="channel1";
slave [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="slave_user", MASTER_PASSWORD="123", MASTER_PORT=7774 FOR CHANNEL="channel2";

Agora basta iniciar a replicação:

slave [localhost] {msandbox} ((none)) > START SLAVE FOR ALL CHANNELS;

Vamos inserir alguns dados em ambos os masters para ver se eles são replicados no servidor slave:

master1 [localhost] {msandbox} ((none)) > CREATE TABLE test.tst1 (ID INT);
Query OK, 0 rows affected (0.12 sec)

master1 [localhost] {msandbox} ((none)) > INSERT INTO test.tst1 SET ID=1;
Query OK, 1 row affected (0.01 sec)

master2 [localhost] {msandbox} ((none)) >  CREATE TABLE test.tst2 (ID INT);
Query OK, 0 rows affected (0.06 sec)

master2 [localhost] {msandbox} ((none)) > INSERT INTO test.tst2 SET ID=2;
Query OK, 1 row affected (0.01 sec)

slave [localhost] {msandbox} ((none)) > USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
slave [localhost] {msandbox} (test) > SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tst1           |
| tst2           |
+----------------+
2 rows in set (0.00 sec)

slave [localhost] {msandbox} (test) > SELECT * FROM tst1;
+------+
| ID   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

slave [localhost] {msandbox} (test) > SELECT * FROM tst2;
+------+
| ID   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Fácil não???

Alguns comandos uteis para se utilizar:

START | STOP SLAVE [SQL_THREAD | IO_THREAD] FOR CHANNEL=”channel name";
START | STOP SLAVE [SQL_THREAD | IO_THREAD] FOR ALL CHANNELS;
SHOW SLAVE STATUS FOR CHANNEL='channel name'\G
FLUSH RELAY LOGS FOR CHANNEL="channel name";
RESET SLAVE FOR CHANNEL="channel name";

Isso ai, temos nosso slave recebendo dados de ambos os master via multi-source replication.

Gostou ? Ajude e Compartilhe!
Publicado em replicacao | 11 Comentários

MySQL Multi Source Replication

Na semana passada, durante o evento MySQL Connect, foi lançada a versão de testes do MySQL 5.7 (MySQL 5.7.2 DRM), dentre as novas funcionalidades, uma que chamou bastante a minha atenção foi a replicação de múltiplos masters (multi source replication). Atualmente o MySQL não possui uma funcionalidade oficialmente (build-in) , pode se conseguir esse resultado com alguns hacks, como, a cada x segundos executar um change master to no slave para ficar trocando de master, mas como o nome diz, isso é um “hack”.

Veja como configurar aqui

Não confundam muti source replication com multi master, veja as figura abaixo para entender a diferença:

MySQL Multi Master Replication

MySQL Multi Master Replication


Multi Master Replication – Na figura acima, temos 2 master’s e 1 slave, onde que o master 1 é master do master 2, master 2 é master do master 1 e slave, para que o slave receba as atualizações do master 1, elas tem que passar pelo master 2, para posteriormente serem aplicadas no slave.
MySQL Multi Source Replication

MySQL Multi Source Replication


Multi Source Replication – Na figura acima, temos 2 master’s e 1 slave, master 1 é master somente do servidor slave e master 2 é master somente do servidor slave, o slave por sua vez, recebe as atualizações de ambos os master’s diretamente.

No próximo post, vou explicar como configurar 1 slave recebendo atualizações de 2 masters via multi source replication.
A versão para testes pode ser baixada pelo site labs.mysql.com, se ficou curioso, baixa la e testa.
Lembrando que essa é uma versão de testes, então, não utilize em produção!!!

Gostou ? Ajude e Compartilhe!
Publicado em MySQL, replicacao | 1 Comentário

MySQL forçar usuário a trocar a senha

Desde a versão 5.6.6 MySQL introduziu o habilidade the forcar usuários a trocar suas senhas.
Tu pode fazer isso digitando ALTER USER PASSWORD EXPIRE.

ALTER USER 'marcelo'@'localhost' PASSWORD EXPIRE;

Na próxima vez que marcelo tentar conectar ao MySQL via localhost, o servidor ira bloquear todos os comandos até que o usuario use SET PASSWORD;

mysql> \s
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Para alterar a senha, e utilizar o server normalmente, basta digitar:

SET PASSWORD = PASSWORD('senha');
#Ou utilizando o hash gerado por SELECT PASSWORD('senha');
SET PASSWORD = '*AD77F56D2FD78299B87609DCC0423260B5AADB03';

Mas tome cuidado, se tu marcar a senha de um usuário como expirada, ele não poderá conectar via clientes com versão anterior a 5.6.10:

ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

Peter Zaitsev escreveu sobre isso em um de seus posts, em algumas instalações via RPM, o MySQL seta uma senha temporario para o usuario root e te forca a trocar ela no proximo login.

Mais informações podem ser encontradas em MySQL Password Expiration Documentation e MySQL ALTER USER Documentation

Gostou ? Ajude e Compartilhe!
Publicado em Uncategorized | Deixe um comentário

Por que Replicação Atrasa no MySQL?

Recentemente respondi algumas questões referente a lag(atraso) na replicação, o que percebi é que muita gente não intende corretamente como este processo funciona internamente no MySQL e o porque do lag acontecer:

Veja a figura abaixo, ela representa replicação assíncrona no MySQL, recomendo que você leia meu outro post sobre o assunto:

“Como Funciona A Replicação No MySQL?”

How replication works

Como pode ser visto, existe uma grade diferença quanto ao ponto de entrada de dados no master e no slave, enquanto o master possui múltiplas threads inserindo/alterado/deletando dados ao mesmo tempo, no slave, existe somente uma única thread responsável por aplicar todas essas transações no banco de dados.

Vamos imaginar que uma transação(UPDATE por exemplo) demorou 50 segundos para rodar no master, durante e depois este período, outras conexões executaram comandos no master, o que vai acontecer quando o slave receber e executar essa transação? Isso mesmo, as demais transações pendentes e que forem sendo inseridas no relay log, vão ter que esperar a SQL_THREAD terminar de executar a transação(o nosso UPDATE que demorou 50 segundos no master) para serem processadas.

Se você utiliza MySQL a partir da versão 5.6.3 e sua aplicação divide os dados em múltiplos bancos internamente no MySQL, você pode minimizar este “gargalo” utilizando slave-parallel-workers, com esta opção você poderá configurar o numero de SQL_THREAD'S para executar transações simultâneas desde que elas sejam em banco de dados diferentes.

Gostou ? Ajude e Compartilhe!
Publicado em binlog, performance, replicacao | Deixe um comentário

Como Funciona A Replicação No MySQL?

Veja a figura abaixo, ela representa como a replicação assíncrona funciona no MySQL:
How replication works

Levando em consideração a numeração na figura, entenda como o processo funciona:

  • Item numero 1 na figura representa os clientes executando queries no master, note que o master é capaz de processar múltiplas conexões simultaneamente (que pode ser configurado pela variável max_connections). 
  • O Master executa essas queries, e salva elas em forma de log (item numero 2 na figura) para que o(s) slave(s) possa(m) replica-las e assim ter os mesmo dados que o servidor Master.
  • O slave por sua vez, trabalha com 2 threads para replicar os dados do servidor Master:

    • IO_THREAD – responsável por conectar-se ao Master, e buscar as novas transações do binary log (item numero 3 na figura) e gravar-las em seu log(relay log, item numero 4 na figura) para posteriormente serem aplicadas ao servidor slave.

    • SQL_THREAD – responsável por ler as novas transações do relay log (item numero 5 na figura) e aplica-las no servidor slave (item numero 6 na figura).
Gostou ? Ajude e Compartilhe!
Publicado em binlog, replicacao | Deixe um comentário

Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format = STATEMENT | MIXED

Desde a versão 5.1.64, o MySQL introduziu um nova variável chamada slave_max_allowed_packet, que foi introduzida para permitir um volume grande de dados quando inserindo ou atualizando registros utilizando replicação baseada em linha (row-based), fazendo com que a replicação não pare caso tu exceda o valor extipulado na variável max_allowed_packet.

O problema é que se tu utiliza a variável binlog_format=STATEMENT ou binlog_format=MIXED, MySQL ignora essa nova opção e continua utilizando max_allowed_packet como limite máximo, mas ele continua a reportar o problema em slave_max_allowed_packet (que por default vem configurada a 1Gb), o que causa a IO_THREAD fornecer a mensagem de erro errada.

Solução:
Rode a seguinte query no master:

master> SHOW VARIABLES LIKE 'binlog_format';

Se tu receber como retorno STATEMENT ou MIXED, tu precisa ajustar o valor da variável max_allowed_packet, uma boa opção se possível, é ter estas duas variáveis configuradas com igual valor no master e no slave.

http://bugs.mysql.com/bug.php?id=68490

http://bugs.mysql.com/bug.php?id=69104

Gostou ? Ajude e Compartilhe!
Publicado em binlog, bug, replicacao | Deixe um comentário