Forçando transação vitima de rollback em deadlock

Se você utiliza uma Storage Engine que suporta transações, você provavelmente já enfrentou ou ouviu falar de deadlock's.

Na Documentação do MySQL podemos ver:
“Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.”
“Sempre esteja preparado para re-executar uma transação se ela falhar por causa de deadlock. Deadlocks não são perigosos. Apenas re-execute a transação.”

Na empresa em que trabalho, temos um importante processo que de vez em quando falha por causa de deadlock. Decidi então melhorar este processo e fazer o que a documentação diz (re-executar a transação).
Para fazer isso, Eu queria poder reproduzir o scenario do deadlock e a transação vitima do rollback teria que ser a do processo em questão.

Criar um deadlock é simples, você só precisa de 2 sessões em que cada uma esteja segurando o “lock” que a outra está esperando. Por exemplo:
Temos uma tabela que possui 4 linhas (linha 1, linha 2, linha 3, linha 4) e temos duas transações que fazem o seguinte:

T1: travar (lock) linha 1;
T2: travar (lock) linha 4;
T1: tentar travar linha 4 (este comando irá aguardar até que T2 destrave a linha 4);
T2: tentar travar linha 1 (este comando irá aguardar até que T1 destrave a linha 1);

Neste momento, ambas transações estarão esperando entre si o destravamento das duas linhas e neste momento o MySQL irá detectar o deadlock. Vamor ver isso em prática:

T1 > CREATE TABLE t (i INT, PRIMARY KEY(i)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

T1 > INSERT INTO t VALUES (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

T1 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T1 > UPDATE t SET i = 1 WHERE i = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T2 > UPDATE t SET i = 4 WHERE i = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T1 >  UPDATE t SET i = 4 WHERE i = 4;
Query OK, 0 rows affected (9.54 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > UPDATE t SET i = 1 WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Como o MySQL escolhe a transação vitima do rollback ?

O MySQL faz um calculo interno baseado no “peso” da transação. Este peso é calculado levando em consideração o numero de linhas alterada e o numero de linhas travadas(locked) por cada transação.
Este valor pode ser verificado na coluna trx_weight da tabela information_schema.innodb_trx:

T1 > SELECT * FROM  information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 23326
                 trx_state: RUNNING
               trx_started: 2016-02-19 10:10:45
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 5
                 trx_query: SELECT * FROM  information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 3
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

A Dica:
Um fator muito importante na hora do calculo acima, é se alguma transação alterou dados em tabelas não-transacionais. Caso alguma transação tenha alterado dados nestas tabelas, ela é considerada mais pesada que as outras.
Para o meu teste, eu criei uma tabela MyISAM e inseri uma linha nesta tabela para fazer ela “pesar mais”:

T2 > CREATE TABLE t2 (i INT, PRIMARY KEY(i)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.02 sec)

T1 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T1 > UPDATE t SET i = 1 WHERE i = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

T2 > UPDATE t SET i = 4 WHERE i = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T2 > INSERT INTO t2 VALUES (1);
Query OK, 1 row affected (0.00 sec)

T1 > UPDATE t SET i = 4 WHERE i = 4;  -- este comando vai esperar até que T2 destrave a linha 4 ...

T2 > UPDATE t SET i = 1 WHERE i = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

T1 >
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Como vocês podem ver, T1 foi a vitima do rollback neste caso.

Por hoje é só pessoal. Espero que tenham gostado.

MySQL 5.7 senha root gerada automaticamente

Fala pessoal.

Vocês devem ter notado algo diferente quando instalaram o MySQL 5.7 ou 8.0 do zero, seja via yum ou pelos binários que o MySQL disponibiliza. O password do usuário root é gerado automaticamente nestas versões.
Ok, e aonde eu posso encontra-lo ?

Centos
cat /var/log/mysqld.log | grep "A temporary password is generated for" | awk '{print $NF}'
Examplo:

[root@master ~]# cat /var/log/mysqld.log | grep "A temporary password is generated for" | awk '{print $NF}'
a3BGf#TY.pBj

Versão binária
Quando você rodou o comando para inicializar o datadir mysqld --initialize você verá a seguinte mensagem na sua tela:

. . .
2016-01-13T21:05:03.070322Z 1 [Note] A temporary password is generated for root@localhost: vL8n>Hs%kr>s
. . .

Você terá que alterar a senha do usuário no primeiro login. Você pode fazer isso seguindo o passo 2 deste artigo.

É isso aí.
Agora vocês já sabem aonde encontrar a senha do root que foi gerada automaticamente na instalação.

Até a próxima.

Adicionando nova collation no MySQL

Eu sou do tipo de DBA que prefere manter as coisas mais simples possíveis, mas, tem vezes em que não é possível. Alguns dias atrás, me deparei com um problema no qual, nenhuma das collations presentes no MySQL iria garantir a integridade do meu banco de dados, e para evitar uma grande re-escrita do código fonte, me deparei com uma opção até então desconhecida.
Adicionar minha própria collation no MySQL

Esta opção está descrita nesta sessão da documentação.
Neste artigo, vou mostrar como fazer o MySQL identificar vogais com acento agudo como uma letra diferente.

Primeiro, precisamos descobrir onde está o nosso diretório com os charset’s:


mysql [localhost] {msandbox} ((none)) > SHOW VARIABLES LIKE 'character_sets_dir';
+--------------------+---------------------------------------+
| Variable_name | Value |
+--------------------+---------------------------------------+
| character_sets_dir | /mysql/sources/5.6.26/share/charsets/ |
+--------------------+---------------------------------------+
1 row in set (0.00 sec)

Neste caso, vou estar trabalhando com a pasta /mysql/sources/5.6.26/share/charsets/, ela provavelmente será diferente no seu ambiente.
Nós vamos achar um arquivo chamado Index.xml dentro desta pasta, e vamos trabalhar nele.

Neste arquivo, vamos estar utilizando a sintaxe chamada de locate data markup language (LDML), mais especificamente os elementos reset / p / t.
Você pode achar mais informação sobre a implementação de LDML no mysql neste link.

Vamos editar o arquivo e adicionar nossa collation:

[sourcecode language=”plain”]
<charset name="utf8">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
. . .
<collation name="utf8_test_ci" id="1122" version="5.2.0">
<rules>
<reset>A</reset>
<p>\u00c1</p>
<t>\u00e1</t>
<reset>E</reset>
<p>\u00c9</p>
<t>\u00e9</t>
<reset>I</reset>
<p>\u00cd</p>
<t>\u00ed</t>
<reset>O</reset>
<p>\u00d3</p>
<t>\u00f3</t>
<reset>U</reset>
<p>\u00da</p>
<t>\u00fa</t>
</rules>
</collation>
</charset>
[/sourcecode]

Explicando um pouco o que cada linha representa:
[sourcecode language=”plain”]
<reset>A</reset>
<p>\u00c1</p>
<t>\u00e1</t>
[/sourcecode]

  • reset – está resetando a ordem e regras da letra A (e também da letra a)
  • p – está dizendo que \u00c1 (Á) será tratada como uma letra diferente
  • t – está dizendo que \u00e1 (á) será tratada como a versão minuscula de \u00c1 (Á)

A mesma lista de regras se aplica as demais letras que temos no arquivo (E / I / O / U). Agora, tudo o que precisamos é re-iniciar o serviço do MySQL.

Vamos agora testar e verificar como isto funciona na prática, vou criar 2 tabelas, uma com a collation padrão do utf8 e outra tabela com a nossa nova collation:


mysql [localhost] {msandbox} (test) > CREATE TABLE `utf8` ( `name` varchar(50), PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('A');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('Á');
ERROR 1062 (23000): Duplicate entry 'Á' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > INSERT INTO utf8 VALUES ('á');
ERROR 1062 (23000): Duplicate entry 'á' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > SELECT * FROM utf8;
+------+
| name |
+------+
| A |
+------+
1 row in set (0.00 sec)

O MySQL só aceitou uma variação da letra A, todas as próximas falharam por serem consideradas iguais. Vamos ver como funciona na nossa collation:


mysql [localhost] {msandbox} (test) > CREATE TABLE `utf8_test` ( `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_test_ci , PRIMARY KEY (`name`)) ENGINE=InnoDB DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('A');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('Á');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > INSERT INTO utf8_test VALUES ('á');
ERROR 1062 (23000): Duplicate entry 'á' for key 'PRIMARY'
mysql [localhost] {msandbox} (test) > SELECT * FROM utf8_test;
+------+
| name |
+------+
| A |
| Á |
+------+
2 rows in set (0.00 sec)

Neste caso, o MySQL aceitou 2 variações, A / Á. “a” foi bloqueada pois é idêntica a letra “A” e “á” foi bloqueada pois é idêntica a letra “Á”.

Por hoje é isso, espero que tenham gostado.

MySQL & NoSQL – Memcached Plugin

Muita gente já deve ter ouvido falar nos bancos de dados NoSQL e uma das ferramentas NoSQL muito utilizada é o memcached, no qual adicionamos uma camada de cache entre a aplicação e o banco de dados. Desde a versão 5.6 do MySQL, foi disponibilizado um plugin de integração entre o MySQL e o Memcached. Neste artigo publicado no iMasters eu mostro como instalar e configurar esta integração.

MySQL Fabric – Parte 1 Instalação

MySQL Fabric é uma ferramenta que está inclusa no MySQL Utilities que ajuda a gerenciar servidores MySQL.
Ele funciona basicamente adicionando uma nova camada entre a aplicação e os servidores MySQL, que auxilia no processo de sharding e alta disponibilidade.

Para instalar nosso ambiente com MySQL Fabric, vamos precisar de 4 servidores, eu utilizei os seguintes nomes e IPs:

fabric1 (192.168.0.200) - fabric
mysql1 (192.168.0.201) - mysql master
mysql2 (192.168.0.202) - mysql slave
mysql3 (192.168.0.203) - mysql slave

Obs.: Estou rodando CentOS 6.5 em todos os servidores.

1. Adicione o repositório mysql nos 4 servidores, leia Instalar a versão mais recente do MySQL via yum para mais informações:

rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum update

2. Instale os pacotes mysql mysql-server mysql-utilities:

yum install mysql mysql-server mysql-utilities
chkconfig mysqld on
/etc/init.d/mysqld start

3. Nos servidores mysql1,mysql2,mysql3 adicione as seguintes configurações no arquivo my.cnf:

[mysqld]
...
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=192.168.0.201
report-port=3306
server-id=1
log-bin=mysql1-bin.log

4. No servidor mysql1, adicione os usuários para a replicação (um para cada servidor mysql):


GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd'; "

5. No servidor mysql1 adicione o usuário MySQL para o servidor fabric:


GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';"

6. Nos servidores mysql2 and mysql3, configure a replicação:

CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

7. No servidor fabric1 adicione o usuário fabric no MySQL:


GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';

[root@fabric1 ~]# mysql -u root -e "GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';"

8. No servidor fabric1, configure o usuário e password no grupos [storage] and [servers] no arquivo /etc/mysql/fabric.cfg :

[storage]
...
password = fabricpwd
...
[servers]
password = reppwd
user = replication

9. No servidor fabric1, configure o banco de dados que será utilizado pelo MySQL Fabric, será perguntado para configurar uma senha, esta senha será utilizada nas próximas vezes que utilizarmos o mysqlfabric:


mysqlfabric manage setup

[root@fabric1 ~]# mysqlfabric manage setup
[INFO] 1406131468.176740 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc: 
Repeat Password:
Password set.

10. No servidor fabric1, inicie o fabric:


mysqlfabric manage start &

11. No servidor fabric1, adicione um grupo:

mysqlfabric group create GLOBAL1

[root@fabric1 ~]# mysqlfabric group create GLOBAL1
Password for admin: 
Procedure :
{ uuid        = 5e4a6bdb-60f0-4e34-87ba-4c56b7616b35,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

12. No servidor fabric1, adicione mysql1, mysql2 e mysql3 ao grupo GLOBAL1:


mysqlfabric group add GLOBAL1 192.168.0.201
mysqlfabric group add GLOBAL1 192.168.0.202
mysqlfabric group add GLOBAL1 192.168.0.203

[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.201
Password for admin: 
Procedure :
{ uuid        = 39efb9c4-6195-4c41-aa02-0bfdc228bfe2,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.202
Password for admin: 
Procedure :
{ uuid        = c8babfb9-d836-44c0-b4fd-015cd1df8298,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.203
Password for admin: 
Procedure :
{ uuid        = c86bba70-69ac-4923-9c54-1a8aaab6d97e,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

13. No servidor fabric1, pegue o uuid do servidor master:

mysqlfabric group lookup_servers GLOBAL1

[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}

14. No servidor fabric1, adicione o seu master como master do grupo GLOBAL1:

mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'

[root@fabric1 ~]# mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'
Password for admin: 
[WARNING] 1406131951.712366 - Executor-2 - Error () trying to process transactions in the relay log for candidate (('Command (START SLAVE SQL_THREAD, ()) failed: 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO', 1200)).
[INFO] 1406131951.824763 - Executor-2 - Master has changed from None to 2e157d1e-1281-11e4-80dc-080027aa0242.
Procedure :
{ uuid        = 733ae69d-fb12-447b-b86b-041703491315,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'PRIMARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}
[root@fabric1 ~]# 

Agora temos nosso ambiente MySQL Fabric funcionando .
Não perca os próximos post para aprender mais sobre essa nova ferramenta.

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.

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.

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 🙂

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.