Registros binários do MySQL

Resumo : neste tutorial, você aprenderá sobre os logs binários do MySQL, incluindo conceitos essenciais, configurações e instruções relacionadas.

Introdução aos logs binários do MySQL

Logs binários são arquivos que armazenam as alterações no banco de dados MySQL. Esses logs contêm uma série de eventos que representam as modificações em dados e objetos de banco de dados, como tabelas, visualizações, bancos de dados e assim por diante.

Por exemplo, quando você cria uma nova tabela, o MySQL registra a CREATE TABLEinstrução correspondente no log binário. Além disso, quando você exclui uma linha de uma tabela, o MySQL registra a DELETEinstrução no log binário.

No entanto, se você executar uma SELECTinstrução, o MySQL não a registrará no log binário porque a SELECTinstrução não altera o banco de dados.

O MySQL usa os logs binários para os seguintes propósitos:

• Replicação : os logs binários fornecem uma maneira confiável e eficiente de replicar dados entre servidores MySQL.
• Recuperação : Os logs binários também desempenham um papel crucial na recuperação pontual .

MySQL suporta vários tipos de logs binários, incluindo:

• Baseado em instrução ( STATEMENT) – permite que o registro seja baseado em linha.
• Baseado em linha ( ROW) – permite que o log use o baseado em linha.
• Formato misto ( MIXED) – permite que o log use formato misto.

Cada formato tem suas vantagens e casos de uso.

O log binário adiciona alguma sobrecarga que pode afetar o desempenho. Portanto, você precisa monitorá-lo de perto. Além disso, você pode querer desativá-lo nos servidores de desenvolvimento e teste .

Configuração de log binário

A seguir estão configurações típicas de log binário:

1) Habilitando log binário

O MySQL habilita os logs binários por padrão. Para verificar se o log binário está habilitado, siga estas etapas:

Primeiro, abra o Prompt de Comando no Windows ou Terminal em sistemas do tipo Unix e conecte-se ao servidor MySQL :

mysql -u root -pLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, recupere o valor da log_binvariável:

show global variables like 'log_bin';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Se log_binfor ON, significa que o log binário está habilitado. Se for OFF, o log binário está desabilitado.

Para habilitar o log binário, adicione a seguinte linha ao arquivo de configuração do MySQL ( my.cnfou my.ini):

log-bin=mysql-binLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

2) Formato de log binário

A binlog_formatvariável armazena o formato de log binário. Por exemplo:

show global variables like 'binlog_format';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A saída indica que o MySQL usa o ROWformato de log binário. Para especificar um formato de log binário diferente, adicione a seguinte linha ao arquivo de configuração do MySQL:

binlog_format=STATEMENTLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Os valores válidos são ROW, STATEMENTe MIXED.

3) Localização do log binário

Por padrão, o MySQL armazena os logs binários no diretório de dados . Se quiser armazenar os logs binários em um local diferente, você pode especificar um caminho absoluto para o diretório desejado no arquivo de configuração do MySQL :

log-bin=/absolute/path/to/binary/logs/Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

4) Retenção de log binário

Os logs binários podem consumir espaço em disco ao longo do tempo. Para evitar problemas de armazenamento, você pode controlar a vida útil dos arquivos de log binários por meio de uma política de retenção de log binário .

Para definir a política de retenção de log binário, você pode definir o número de dias, o tamanho máximo do arquivo de log binário e o número de arquivos de log no arquivo de configuração.

Por exemplo:

expire_logs_days=7 # 7 daysLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Este exemplo define o período de retenção para 7 dias. Se os logs tiverem mais de 7 dias, o MySQL irá removê-los durante o processo de rotação regular

max_binlog_size=100MLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, definimos o tamanho máximo de um arquivo de log binário como 100 megabytes. Observe que você pode especificar o tamanho em bytes, quilobytes (K), megabytes (M) ou gigabytes (G).

max_binlog_files=10Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, definimos o número máximo de arquivos de log binários como 10. Quando o número de arquivos de log for 10, o MySQL exclui (ou limpa) os arquivos de log mais antigos para abrir espaço para novos.

5) Criptografia de logs binários

Para aumentar a segurança, você pode criptografar os logs binários usando a encrypt-binlogopção:

encrypt-binlog=1Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Instruções de log binário

Apresentaremos a instrução mais comumente usada para lidar com logs binários.

1) MOSTRAR LOGOS BINÁRIOS

Para mostrar uma lista de logs binários disponíveis, você usa a SHOW BINARY LOGSinstrução:

SHOW BINARY LOGS;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Exemplo de saída:

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |     11322 | No        |
| binlog.000002 |       201 | No        |
| binlog.000003 |       483 | No        |
+---------------+-----------+-----------+
3 rows in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

2) MOSTRAR STATUS DO MESTRE

Para exibir informações sobre o log binário do servidor de origem e a posição de replicação, use a SHOW MASTER STATUSinstrução:

SHOW MASTER STATUS;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Exemplo de saída:

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      483 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

3) PURGAR REGISTROS BINÁRIOS

Para remover logs binários antigos com base em um determinado arquivo, você usa a PURGE BINARY LOGSinstrução. Por exemplo, o seguinte remove os arquivos binários 000001 e 000002:

PURGE BINARY LOGS TO 'binlog.000003';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Se você exibir uma lista de logs binários, verá apenas o arquivo de log binlog.000003restante:

SHOW BINARY LOGS;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000003 |       483 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

4) REGISTROS BINÁRIOS LIBERADOS

Para forçar o servidor MySQL a fechar o arquivo de log atual e abrir um novo, você usa a FLUSH BINARY LOGSinstrução:

FLUSH BINARY LOGS;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Se você emitir uma instrução que altere o banco de dados, verá um novo arquivo de log. Por exemplo, vamos criar um novo banco de dados chamado sampledb:

CREATE DATABASE sampledb;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O MySQL registrará o novo evento em um novo arquivo de log binário.

A instrução a seguir exibe a lista de arquivos de log:

SHOW BINARY LOGS;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000003 |       527 | No        |
| binlog.000004 |       157 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O utilitário mysqlbinlog: examinando arquivos de log binários

Para visualizar as instruções SQL registradas nos logs binários, use o mysqlbinlogutilitário.

Primeiro, abra o prompt de comando no Windows ou o Terminal em sistemas do tipo Unix.

Segundo, visualize o conteúdo do arquivo de log binário binlog.000003:

mysqlbinlog /var/lib/mysql/binlog.000003Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Ele mostrará o conteúdo do arquivo de log binlog.000003.

Se quiser salvar o conteúdo em um arquivo, você pode usar o redirecionamento >em sistemas do tipo Unix. Por exemplo, o seguinte salva o conteúdo do arquivo de log binário no binlog.txtarquivo.

mysqlbinlog /var/lib/mysql/binlog.000003 > ~/binlog.txtLinguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Resumo

• MySQL usa logs binários para registrar os eventos de mudança nos dados e objetos de banco de dados.
• Use os logs binários para fins de replicação e recuperação pontual.
• Use o mysqlbinlogutilitário para visualizar o conteúdo dos arquivos de log binários.