Alguma saída melhor do log do MySQL InnoDB "no futuro"?

Esta era uma situação bastante rara. Espero nunca acabar lá novamente, com um InnoDB "número de sequência de log está no futuro!" erro. Por causa dos meus detalhes particulares, reconstruir/restaurar os dados do meu servidor foi o último recurso. Alguns truques para ajudar foram boas ideias, mas no final, decidi continuar melhorando meu script Perl para jogar esse jogo bobo e fazer o máximo de shows/hora que pudesse. Que diabos, é um bom teste de estresse do sistema.

Lembre-se: o objetivo é aumentar um único contador ("log sequence number") que é armazenado em algum lugar nos cabeçalhos de ib_logfile0 e ib_logfile1 . Isso é para falsificar o InnoDB para que ele ignore uma aparente distorção do tempo e continue com a vida. Mas ninguém sabe como editar esse número. Ou se eles sabem, ninguém está falando.

Aqui está o meu produto final. YMMV, mas usar a função REPEAT do mysql para gerar os dados internamente é altamente eficiente.

 #!/usr/bin/perl
 use DBI;
 $table = shift || die;
 $dbh = DBI->connect("DBI:mysql:junk:host=localhost", "user", "pass"); #Edit "junk" (DB name), user, and pass to suit.
 $dbh->do("DROP TABLE IF EXISTS $table");
 $dbh->do("CREATE TABLE $table (str TEXT) ENGINE=INNODB");
 $sth = $dbh->prepare("INSERT INTO $table (str) VALUES (REPEAT(?,1000000))");
 foreach (1..50) {
    $sth->execute('0123456789');   # 10 MB
 }
 $dbh->do("DELETE FROM $table");

Minha receita sugerida:

1. Crie um banco de dados 'lixo'
2. Salve o script perl acima como junk.pl .
3. Execute junk.pl data1 , junk.pl data2 , e junk.pl data3 , etc. tudo de uma vez, para tantos núcleos de CPU quanto o servidor de banco de dados tiver, para iniciar. Abra vários shells e envolva cada execução em um loop Bash: while true; do date; junk.pl dataX; done.

Veja seu LSN crescer, talvez em outro loop:

 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 124 3871092821
 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 124 4209892586
 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 125 85212387

O grande número é um INT de 32 bits não assinado que será encapsulado em 4 GB, aumentando o número menor a cada vez. Neste caso acima, ele apenas rolou de 124 para 125. Seu objetivo está oculto no mysqld.log que o enviou pesquisando no Google para esta solução ridícula em primeiro lugar. Depois de cruzar a linha de chegada, é isso! Sopre os chifres! Solte o confete!

Barra lateral: Isso descobriu um bug interessante no mysqld 5.0 w/REPEAT: se você for para 20 MB, ele vira algum contador interno e rola para ~ 96 KB. Nenhum aviso ou erro em qualquer lugar. Eu não ia perder tempo rastreando isso. 10 MB funciona muito bem. Se você atingir algum outro limite, ele pode reclamar. Eu tenho vários buffers innodb aumentados do padrão. Tempere a gosto. Como sempre, observe mysqld.log em uma janela.

Você tem três (3) opções:

OPÇÃO 01 : Execute rsync de Master to Slave (Downtime no Master)

• Passo 01 : Executar reset master;no mestre (Zaps Binary Logs)
• Passo 02: service mysql stopno mestre
• Passo 03: service mysql stopno escravo
• Passo 04: rsync /var/lib/mysql do mestre para o escravo
• Passo 05: service mysql startno mestre
• Etapa 06: Use o primeiro log binário no mestre como o log para iniciar a replicação. Use o tamanho do arquivo desse log como a posição para iniciar a replicação de
• Passo 07 : service mysql stop --skip-slave-startno escravo
• Etapa 08: Execute o comando CHANGE MASTER TO para configurar a replicação do log e a posição verificada na Etapa 06
• Etapa 09 : Execute start slave;no escravo e deixe a replicação acompanhar

OPÇÃO 02 : Execute rsync de Master to Slave (tempo de inatividade mínimo no mestre)

• Passo 01 : Executar reset master;no mestre (Zaps Binary Logs)
• Passo 02: service mysql stopno escravo
• Passo 03: rsync /var/lib/mysql do mestre para o escravo
• Passo 04: Repita o Passo 03 até que dois rsyncs consecutivos levem o mesmo tempo
• Passo 05: service mysql stopno mestre
• Passo 06: rsync /var/lib/mysql do mestre para o escravo
• Passo 07: service mysql startno mestre
• Etapa 08: Use o primeiro log binário no mestre como o log para iniciar a replicação. Use o tamanho do arquivo desse log como a posição para iniciar a replicação de
• Passo 09 : service mysql stop --skip-slave-startno escravo
• Etapa 10: Execute o comando CHANGE MASTER TO para configurar a replicação do log e a posição verificada na Etapa 08
• Etapa 11: Execute start slave;no escravo e deixe a replicação acompanhar

OPÇÃO 03 : Use o XtraBackup

Esta ferramenta de software não apenas fará uma cópia não intrusiva de um mestre em execução, mas também criará os ib_logfiles correspondentes para você. Você teria que configurar a replicação

Eu postei no StackExchange antes sobre este assunto

• Como posso mover um banco de dados de um servidor para outro?
• MySQL Community Edition: Posso replicar todo o meu servidor com outro servidor de uma só vez?
• Como você sincroniza novamente o banco de dados MySQL mestre com as alterações do banco de dados escravo se o mestre ficar offline?
• https://serverfault.com/questions/257394/create-a-mysql-slave-from-another-slave-but-point-it-at-the-master/257426#257426

Eu fiz essas coisas muitas vezes para a empresa de hospedagem do meu empregador. Um cliente tinha 3,7 TB para mover e demorou cerca de 16 horas. 64 GB é muito pequeno em comparação.

Descobri que talvez haja uma maneira mais legal de resolver esse problema trabalhando em tabelas particionadas. Eu precisava eliminar partições de alguns anos atrás e tive que adicionar algumas para 2014. Quase todas as partições relatam esse erro, assim como as antigas. Acidente muito desagradável.

Então, enquanto DROPPING old e usando REORGANIZE da partição MAXVALUE (a última), ele criará novos arquivos que estão ok, então eu recebo cada vez menos avisos. Enquanto isso, ajuda a incrementar o contador de sequência de log, então não preciso inserir dados falsos. Eu tenho isso acontecendo em um servidor mestre btw ...

Então, é isso:

ALTER TABLE Events DROP PARTITION p1530 , p1535 , p1540 , p1545 , 
p1550, p1555 , p1560 , p1565 , p1570 , p1575 , p1580 , p1585 , p1590 , 
p1595 , p1600 , p1605 , p1610 , p1615 , p1620 , p1625 , p1630 , p1635 , 
p1640 , p1645 , p1650 , p1655 , p1660 , p1665 , p1670 , p1675 , p1680 , 
p1685 , p1690 , p1695 , p1700 , p1705 , p1710 , p1715 , p1720 , p1725 , 
p1730 , p1735 , p1740 , p1745 , p1750 , p1755 , p1760 , p1765 , p1770 , 
p1775 , p1780 , p1785 , p1790 , p1795 , p1800 , p1805 , p1810 , p1815 , 
p1820 , p1825 , p1830 , p1835 , p1840;

E isto:

ALTER table Events REORGANIZE PARTITION p3000 INTO (
PARTITION p3500 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION p3510 VALUES LESS THAN (TO_DAYS('2013-01-04')),
PARTITION p3520 VALUES LESS THAN (TO_DAYS('2013-01-07')),
PARTITION p3530 VALUES LESS THAN (TO_DAYS('2013-01-10'))
...
PARTITION p4740 VALUES LESS THAN (TO_DAYS('2014-01-08')),
PARTITION p9000 VALUES LESS THAN MAXVALUE)

Isso eliminará efetivamente cada partição na alteração e a recriará com uma cópia temporária do conteúdo do que estava lá. Você pode fazer isso por tabela, se quiser, meu aplicativo permite que isso aconteça, então não precisa se preocupar com backups sincronizados etc.

Agora, para o resto da tabela, como não toquei em todas as partições no processo, algumas ficarão com o aviso de sequência de log, para aquelas que estão quebradas , mas cobertas por esta ação de reorganização, provavelmente executarei isso:

ALTER TABLE Events REBUILD PARTITION p0, p1;

ou aquilo

ALTER TABLE Events OPTIMIZE PARTITION p0, p1;

Então, isso me fez pensar, você poderia fazer isso com tabelas simples de baunilha, adicionar partições temporárias por hash e depois removê-las (ou mantê-las, posso recomendar fortemente partições).

Estou usando mariadb no entanto, não mysql (então XtraDB)

Talvez isso ajude alguém. Ainda estou rodando, até agora tudo bem. Mudar o ENGINE parece fazer o trabalho também, então eu o trago entre o MyIsam e eles de volta ao InnoDB.

É bastante lógico, se você mudar ENGINE, a tabela desaparece do innodb, então não será mais um problema.

ALTER TABLE Events ENGINE=MyISAM;
ALTER TABLE Events ENGINE=InnoDB;

parece funcionar aqui. Posso confirmar algumas coisas em tabelas particionadas:

• ALTER TABLE xyz ENGINE=InnoDB é muito lento, para Aria (mariadb) duas vezes mais rápido, mas em geral uma maneira lenta de incrementar o contador de sequência de log
• ALTER TABLE xyz REBUILD PARTITION ALL é a maneira mais rápida de 'consertar' as tabelas e ajudar a incrementar o contador
• ALTER TABLE xyz ANALYZE PARTITION ALL é lento em comparação com o anterior e não reescreve partições que verificam que estão ok. REBUILD garante uma reescrita em um esquema de tabela temporária.

Usei os últimos em várias mesas. Os avisos acontecem quando ele está tentando abrir os arquivos e há um para cada definição de partição aberta com problemas de contador. Quase rolou no balcão hoje para as últimas mesas. Eu acho que uma vez que tudo é processado, é necessário liberar os logs binários.

atualização : posso concluir algumas coisas agora que consegui resolver esse problema.

• Meu travamento foi causado pela reorganização de partições em uma tabela no formato Aria (MariaDB).
• (para mim) fazer uma reconstrução das partições funcionou melhor e mais rápido para aumentar o contador de sequências. Alterar o mecanismo é lento e você precisa fazer isso duas vezes para afetar o innodb. alterar para o innoDB é bastante lento em relação ao MyIsam ou Aria.
• Atualizei para o MariaDB 5.3 e não para 5.5 (era: 5.2) e funciona bem. Eu acho que há muitos problemas com aria, partições em 5.5 (e bugs confirmados) para usar essa combinação.
• Realmente deveria haver uma maneira melhor de redefinir o contador de sequência de log.