English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
Quem já fez sistemas grandes sabe que a importância dos logs não pode ser subestimada. Frequentemente, no final do projeto, as decisões de otimização e atualização são tomadas com base nos logs. Portanto, ao estudar MySQL, a parte de logs não pode ser ignorada. Tudo o que discutimos sobre otimização em nossas entrevistas de emprego é extraído dos logs. Estudar os logs do MySQL sistematicamente nos ajuda a localizar problemas com precisão e a melhorar nosso nível de trabalho. Além disso, uma série de logs futuros focará principalmente na operação do DBA, abordando sistematicamente as configurações de todos os aspectos do MySQL, para que possamos conhecê-los e conhecê-los, tornando o MySQL uma biblioteca de dados que podemos manipular com facilidade.
Um, Tipos de Logs do MySQL
Pelo padrão, todos os logs do MySQL são armazenados em forma de arquivo na raiz do banco de dados:
[root@roverliang data]# pwd /usr/local/webserver/extend_lib/mysql/data [root@roverliang data]# ls auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid test
Os tipos de logs do MySQL são os seguintes:
1. Log de erro (error), informações relacionadas ao início, execução ou encerramento do exemplo de serviço do MySQL.
2. Log de consulta comum (general), todas as instruções SQL ou comandos executados pelo exemplo de serviço do MySQL.
3. Log binário (binary), todas as atualizações executadas no banco de dados, exceto as instruções select e show.
4. Log de consulta lenta (slow), consultas SQL cujo tempo de execução excede o valor configurado de long_query_time ou consultas SQL que não usam índice.
Segundo, cache de logs do MySQL
Um sistema rápido, estável e confiável, a cache nele desempenha um papel crucial. O processamento de logs do MySQL também usa um mecanismo de cache. O log do MySQL é inicialmente armazenado na memória do servidor do MySQL, se exceder a capacidade de armazenamento especificada, o log na memória é escrito (ou atualizado) para a memória externa, permanentemente armazenado no disco rígido em forma de tabela de banco de dados ou arquivo.
Terceiro, log de erro do MySQL (error log)
O log de erro do MySQL registra detalhes de cada inicialização, parada e informações de aviso ou erro geradas durante a execução do exemplo de serviço do MySQL. Diferente dos outros logs, o log de erro do MySQL deve ser ativado e não pode ser desativado.
Pelo padrão, o nome do arquivo do log de erro é: nome_do_host.err. No entanto, o log de erro não registra todas as informações de erro, apenas os erros críticos (críticos) que ocorrem durante a execução do exemplo de serviço do MySQL serão registrados.
mysql> show variables like 'log_error'\G *************************** 1. linha *************************** Nome_da_variável: log_error Valor: /usr/local/webserver/extend_lib/mysql/data/roverliang.err 1 linha em conjunto (0.02 sec)
Quarto, log de consulta comum do MySQL (general log)
O registro de log de consulta comum do MySQL registra todas as operações do exemplo de serviço do MySQL, como select, update, insert, delete e outras operações, independentemente de a operação ser executada com sucesso ou não. Além disso, inclui informações sobre a conexão e desconexão do cliente do MySQL com o servidor do MySQL, independentemente de a conexão ser bem-sucedida ou falhar. Existem três parâmetros relacionados ao log de consulta comum do MySQL.
[]()general_log mysql> show variables like 'general_log'; +---------------+-------+ | Nome_da Variável | Valor | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 linha em conjunto (0.01 sec)
Pode-se usar set @@global.general_log = 1 Para ativar o log de consulta comum.
mysql> set @@global.general_log =1; mysql> show variables like 'general_log'; +---------------+-------+ | Nome_da Variável | Valor | +---------------+-------+ | general_log | ON | +---------------+-------+
Mas, ao modificar os variáveis do MySQL dessa forma, eles só estarão em vigor durante a execução do instância atual do MySQL. Assim que o MySQL reiniciar, ele será restaurado ao estado padrão. A maneira permanente de fazer isso é modificar o arquivo my.cnf do mysql. Adicione ao arquivo de configuração:
general_log = 1
general_log_file
Assim que o log de consulta comum for ativado, o serviço do MySQL criará automaticamente o arquivo de log de consulta comum. O parâmetro general_log_file configura a localização física do arquivo de log de consulta comum. Por exemplo:
mysql> show variables like 'general_log_file'; +------------------+-----------------------------------------------------------+ | Nome_da Variável | Valor | +------------------+-----------------------------------------------------------+ | general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log | +------------------+-----------------------------------------------------------+
Atenção: Como o log de consulta comum almost sempre registra todas as operações do MySQL, para servidores de banco de dados com alta frequência de acesso a dados, se o log de consulta comum do MySQL for ativado, ele pode reduzir significativamente o desempenho do banco de dados. Portanto, é recomendável desativar o log de consulta comum. Apenas em períodos especiais, como a necessidade de rastrear某些 especiais consultas de log, pode-se ativar temporariamente o log de consulta comum.
log_output
O parâmetro log_output configura o conteúdo do log de consulta comum e do log de consulta lenta para ser armazenado em tabelas do banco de dados. Pode-se usar set @@global.log_output='table' para armazenar o log de consulta comum e o log de consulta lenta nas tabelas general e slow_log do banco de dados do sistema MySQL. É importante notar que os motores de armazenamento dessas tabelas são CSV, portanto, ao visualizar novos conteúdos do log de consulta comum, pode-se usar consultas SQL;
set @@global.log_output = 'table'; mysql> show variables like 'log_output'; +---------------+-------+ | Nome_da Variável | Valor | +---------------+-------+ | log_output | TABLE | +---------------+-------+
Cinco, Log de Consultas Lentas do MySQL (slow log)
Questões relacionadas ao log de consultas lentas do MySQL, os entrevistadores de entrevistas são muito favoráveis de discutir这方面. Antes só podíamos falar sobre a arquitetura mestre-e-servidor do MySQL e otimizar MySQL de várias maneiras, mas na vida cotidiana não realmente entender como ativar o log de consultas lentas e as configurações relacionadas.
O uso do log de consultas lentas do MySQL pode rastrear eficazmente consultas que demoram muito ou que não usam índices. Isso inclui consultas select, update, delete e insert, fornecendo ajuda para otimizar consultas. Uma outra diferença em relação ao log de consultas comuns é que o log de consultas lentas contém apenas consultas que foram executadas com sucesso. Parâmetros relacionados ao log de consultas lentas do MySQL incluem5unidades.
1、slow_query_log
slow_query_log configura se o log de consultas lentas está ativado.
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Nome_da Variável | Valor | +----------------+-------+ | slow_query_log | DESATIVADO | +----------------+-------+
2、slow_query_log_file
Quando o log de consultas lentas é ativado, o instância do MySQL cria automaticamente o arquivo de log de consultas lentas. O arquivo especificado por slowquerylog_file armazena o conteúdo do log de consultas lentas. A maneira de modificá-lo é a mesma que a descrita acima. Edite o arquivo my.cnf diretamente.
3、long_query_time
long_query_time configura o limiar de tempo para consultas lentas. O valor padrão é10s。
4、log_quries_not_using_indexes
log_quries_not_using_indexes registro as consultas que não usam índices na log de consultas lentas, independentemente da velocidade da consulta.
mysql> set @@global.log_queries_not_using_indexes=1; mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+
5, log_output
The output format of the general query log and slow query log has been set, with two values: file, table;
6. View MySQL Slow Query Log
The log_output parameter can set the output format of the slow query log. The default is FILE, which can be set to TABLE;
mysql> desc mysql.slow_log; +----------------+---------------------+ | Field | Type | +----------------+---------------------+ | start_time | timestamp | | user_host | mediumtext | | query_time | time | | lock_time | time | | rows_sent | int(11) | | rows_examined | int(11) | | db | varchar(512) | | last_insert_id | int(11) | | insert_id | int(11) | | server_id | int(10) unsigned | | sql_text | mediumtext | | thread_id | bigint(21) unsigned | +----------------+---------------------+
Among them: lock_time indicates the time the SQL was locked during execution. rows_send indicates the number of rows returned after executing the SQL. rows_examined indicates the number of records scanned during the execution of the SQL.
However, it is not common to use TABLE to store slow query logs. In cases with large business volume, it may affect the main service of the system. We can use the FILE method for log storage. When installing MySQL, the mysqldumpslow.pl tool for slow query log analysis is already installed by default in the MySQL bin directory. Using this tool on Windows may require some configuration, which is not within the scope of this article. If you want to learn about system services, it's better to move to Linux. Under Linux, you can use the command itself and the tools. + --Use the 'help' option to view the help document.
-s representa como classificar
Opções de sub-opção: c, t, l, r
c: número de vezes que o SQL é executado
t: tempo de execução
l: tempo de espera de bloqueio
r: retorna o número de dados
at, al, ar são os valores médios correspondentes a t, l, r. -t: representa retornar os primeiros N registros.
-g: grep abreviação. Contém correspondência fuzzy
Uso comum conforme abaixo:
//retorna o número mais alto de acessos20 comandos SQL ./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //retorna o número mais alto de registros return20 comandos SQL ./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //retorna comandos SQL contendo like ./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log
Sete, log binário (binary)
O log binário é diferente dos outros tipos de logs mencionados anteriormente, o binário não pode ser verificado diretamente com cat ou o visualizador de texto less. É necessário usar ferramentas especializadas. O log binário principalmente registra as mudanças no banco de dados, portanto, pode ser usado para sincronização de master e slave. O conteúdo inclui todas as operações de atualização do banco de dados, use statements, insert statements, delete statements, update statements, create statements, alter statements, drop statements. Uma frase mais concisa e fácil de entender seria: todas as operações que envolvem a mudança de dados devem ser registradas no log binário.
Iniciar log binário Verifique se o log binário está ativado usando show variables like 'log_bin'\G.
mysql> show variables like 'log_bin'\G
*************************** 1. linha ***************************
Nome_da_variável: log_bin
Valor: DESATIVADO
1 linha em conjunto (0.00 seg)
mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): A variável 'log_bin' é uma variável só de leitura
mysql>
Ver que o log_bin está por padrão desativado e é uma variável só de leitura, precisa ser configurado no my.cnf e reiniciar o MySQL. service mysql restart Após reiniciar o MySQL, no diretório data será notado que foi gerado um1.000001O arquivo. Na verdade, a cada reinício do MySQL, será gerado um arquivo assim no diretório, com nomes incrementais. Além disso, o MySQL também criará um arquivo de índice de log binário no diretório, que pode ser verificado com o comando show variables like 'log_bin_index'\G para ver a localização do arquivo de índice, e então usar o comando cat para verificar. Será notado que ele registra o caminho relativo dos arquivos binários.
查看二进制日志 可以使用MySQL 中自带的工具。具体位置在mysql的bin目录下。 mysqlbinlog命令的常用选项：
-s 以精简的方式显示日志内容
-v 以详细的方式显示日志内容
-d=数据库名 只显示指定数据库的日志内容
-o=n 忽略日志中前n行MySQL命令
-r=file 将指定内容写入指定文件
--start-datetime
Exibir o conteúdo do log dentro do intervalo de tempo especificado
--stop-datetime
--start-posição
Exibir o conteúdo do log dentro do intervalo de posição especificado
--stop-posição
Obter o arquivo de log binário atualmente em uso
mysql> show master status; +----------+----------+--------------+------------------+-------------------+ | Arquivo | Posição | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------+----------+--------------+------------------+-------------------+ | 1.000002 | 120 | | | | +----------+----------+--------------+------------------+-------------------+ 1 linha em conjunto (0.00 seg)
Recuperação de dados usando o log binário
A sintaxe é simples:
mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p
após o mysqlbinlog pode seguir --start-datetime,--stop-datetime, start-posição, stop-posição e outros parâmetros.
--start-datetime,--stop-datetime esses dois parâmetros podem ser usados para a recuperação de dados com base em um ponto de tempo;
start-posição, stop-A posição pode ser usada para细化 a recuperação de dados;
Parâmetros relacionados ao log binário do MySQL
mysql> show variables like '%binlog%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | IGNORAR_erro | | binlog_format | STATEMENT | | binlog_gtid_simple_recovery | DESATIVADO | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | LIGADO | | binlog_row_image | COMPLETO | | binlog_rows_query_log_events | DESATIVADO | | binlog_stmt_cache_size | 32768 | | binlogging_impossible_mode | IGNORAR_erro | | innodb_api_enable_binlog | DESATIVADO | | innodb_locks_unsafe_for_binlog | DESATIVADO | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | simplified_binlog_gtid_recovery | DESATIVADO | | sync_binlog | 0 | +-----------------------------------------+----------------------+
max_binlog_size
maxbinlogsize Tamanho do arquivo de log binário individual. Se exceder esse valor, um novo arquivo será gerado com sufixo.+1;
binlog_cache_size
binlogcachesize Tamanho do cache de memória para armazenamento de logs binários.
sync_binlog
sync_binlog Escrita várias vezes no cache de binários, início da sincronização da atualização para a memória externa (disco rígido).
log_slave_updates
logslvaeupdates 用于主从复制
Limpeza do log binário
Em princípio, é necessário fazer backup dos logs que estão prontos para serem limpos de maneira física para outro dispositivo de armazenamento, mantendo-os permanentemente. Em seguida, recomenda-se usar os dois métodos de limpeza de baixo risco listados a seguir:
Primeira opção:
purge master logs before '2017-02-16 00:00:00';
Segunda opção:
Configure o parâmetro expire_logs_days diretamente no arquivo de configuração do MySQL my.cnf para definir o número de dias de expiração dos arquivos binários, os arquivos binários expirados serão automaticamente excluídos. Recomenda-se iniciar outro plano de tarefa periódica antes da exclusão, para fazer backup dos arquivos binários regularmente. Para evitar que alguns dados sejam descobertos com erros após alguns dias e os arquivos binários sejam automaticamente excluídos.
expire_logs_days=90
Oitavo, log de transação do InnoDB
O log de transação do InnoDB é diferente dos logs mencionados anteriormente, o log de transação do InnoDB é mantido automaticamente pelo mecanismo de armazenamento InnoDB e seu conteúdo não pode ser lido pelo administrador do banco de dados. Portanto, qualquer sistema de alta performance deve usar cache, em todos os níveis, a cache desempenha um papel crucial. Em um nível mais alto, podemos extrair: a cache e a fila são caminhos necessários para a alta performance. Para o banco de dados, isso é um problema complicado, para garantir que os dados sejam lidos e armazenados de maneira mais eficiente, é necessário usar cache. Mas para garantir a consistência dos dados, é necessário garantir que todos os dados sejam armazenados corretamente no banco de dados, mesmo que ocorra um acidente, também é necessário garantir que os dados sejam recuperáveis. Sabemos que o InnoDB é um mecanismo de armazenamento seguro para transações e a consistência é uma característica importante da ACID das transações. O mecanismo de armazenamento InnoDB realiza a consistência dos dados através do log de transação do InnoDB, que inclui o log de reexecução (redo) e o log de rollback (undo).
Log de reexecução (redo)
O log de reexecução principalmente registra transações que foram completamente concluídas, ou seja, logs que executaram commit, por padrão, os valores do log de reexecução são registrados em iblogfile0 e iblogfile1no log de reexecução.
[root@roverliang data]# pwd /usr/local/webserver/mysql/data [root@roverliang data]# ls ib* ibdata1 ib_logfile0 ib_logfile1
Log de rollback (undo)
O log de rollback principalmente registra transações que foram parcialmente concluídas e escritas no disco rígido, por padrão, as informações do log de rollback são registradas no arquivo de espaço de dados, arquivo de espaço de dados compartilhado ibdata1ou no espaço de tabela exclusivo, não visto no arquivo ibd.
Como podemos ver na figura acima, o log de rollback por padrão é gravado no arquivo ibdta1Meu sistema MySQL está na versão:5.6.24.
Mecanismo de Checkpoint
Após o colapso do servidor MySQL, ao reiniciar o serviço do MySQL, devido à existência dos logs de reexecução (redo) e de rollback (undo), o InnoDB realiza operações de rollback (rollback) nas transações que foram parcialmente concluídas e escritas no disco. Em seguida, todas as transações no log de reexecução (undo) são reexecutadas para restaurar todos os dados. No entanto, devido ao grande volume de dados, para reduzir o tempo de recuperação, o InnoDB introduziu o mecanismo de Checkpoint.
Página suja (dirty page)
Quando uma transação precisa modificar um registro, o InnoDB primeiro lê o bloco de dados onde o registro está armazenado do armazenamento externo para o disco rígido. Após o commit da transação, o InnoDB modifica o registro na página de dados. Neste momento, a página de dados em cache já não coincide com o bloco de dados no armazenamento externo. A página de dados em cache é chamada de página suja (dirty page). A página suja é atualizada para o armazenamento externo, tornando-se uma página limpa (clean page).
Notas: Uma página de memória padrão é4K, ou4Multiplo de K. Você pode imaginar a memória como um livro que pode ser apagado, onde, a cada vez que o MySQL lê dados, ele solicita algumas páginas limpas da memória, e depois escreve neles. Quando os dados são atualizados para o disco, essas páginas são imediatamente apagadas, disponíveis para outros programas.
Número sequencial do log (log sequence number)
O número sequencial do log (LSN) é o ponto final de cada log no espaço de log, representado pelo deslocamento em bytes, usado no Checkpoint e na recuperação.
Princípio do mecanismo de Checkpoint. Suponha que, em um ponto de tempo, todas as páginas sujas (dirty page) foram atualizadas para o disco rígido. A partir deste ponto de tempo, todos os logs de reexecução (redo) não precisam ser reexecutados. O sistema define o final do log de reexecução deste ponto de tempo como Checkpoint. Os logs de reexecução antes do Checkpoint não precisam ser reexecutados novamente e podem ser excluídos com segurança. Para melhorar o uso do espaço do log de reexecução (redo), o InnoDB adota uma estratégia de rotação para usar o espaço do log de reexecução, portanto, o arquivo de log de reexecução do InnoDB deve ter pelo menos2Por meio do mecanismo de Checkpoint, através do log de reexecução (redo), é possível reexecutar (undo) as transações que foram concluídas no momento do colapso do banco de dados, mas não foram completamente escritas no armazenamento externo, garantindo a consistência dos dados e reduzindo o tempo de recuperação.
Parâmetros do log de reexecução (redo) do InnoDB
innodb_log_buffer_size: Define o tamanho do buffer de log de reexecução.
innodb_log_files_in_group: Define o número de arquivos de log de reexecução (redo) no grupo de arquivos de log.
innodb_log_file_size: Define o tamanho do arquivo de log de reexecução, quanto maior o arquivo, mais tempo levará para restaurar.
innodb_mirrored_log_groups: Define o número de grupos de arquivos de log de reexecução, pode ser configurado apenas como1.
innodb_log_group_home_dir: Define o diretório onde os arquivos do grupo de log são armazenados, o valor padrão está na raiz do diretório do banco de dados.
Parâmetros do log de rollback (undo) do InnoDB
innodb_undo_directory: Define o diretório onde o log de rollback é armazenado.
innodb_undo_logs: Define o tamanho do segmento de log de rollback, o valor padrão é128k
innodb_undo_tablespace: Define quantos arquivos de log de rollback compõem o log de rollback, o valor padrão é 0.
Aviso especial: Após a instalação do MySQL, você precisa configurar os parâmetros do log de rollback no my.cnf. Se você configurar os parâmetros do log de rollback após criar o banco de dados, o MySQL emitirá um erro e, após a criação do log de rollback, não será possível modificar ou adicionar mais.
Nove: Backup de arquivos de log
Ao fazer backup, você pode usar flush logs para fechar todos os arquivos de log atuais, criar novos arquivos de log e, após fechar os arquivos de log, pode fazer backup fisicamente. Além disso, flush logs pode adicionar tipos específicos de logs:
flush error logs flush general logs flush binary logs flush slow logs
Declaração: O conteúdo deste artigo foi extraído da Internet, pertence ao respectivo proprietário, foi contribuído e carregado voluntariamente pelos usuários da Internet, este site não possui direitos de propriedade, não foi editado manualmente e não assume responsabilidades legais relacionadas. Se você encontrar conteúdo suspeito de violação de direitos autorais, bem-vindo a enviar e-mail para: notice#w3Aviso: Este conteúdo foi extraído da Internet, pertence ao respectivo proprietário, foi submetido e carregado voluntariamente pelos usuários da Internet, este site não possui direitos de propriedade, não foi editado manualmente e não assume responsabilidades legais relacionadas. Se você encontrar conteúdo suspeito de violação de direitos autorais, bem-vindo a enviar e-mail para: notice#w