English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
Chi ha lavorato su sistemi di grandi dimensioni sa che i log non possono essere sottovalutati; spesso, durante la fase finale del progetto, le decisioni di ottimizzazione e aggiornamento del progetto sono basate su decisioni fatte in base ai log. Pertanto, studiare MySQL, la parte dei log non può essere trascurata. Tutto ciò che viene discusso durante l'intervista in termini di ottimizzazione è tratto dai log. Lo studio sistematico dei log di MySQL ci aiuta a localizzare i problemi in modo accurato e ad aumentare il nostro livello professionale. Inoltre, una serie di log successivi si concentreranno principalmente sulle operazioni di DBA, cercando di comprendere sistematicamente le configurazioni di MySQL da tutti i lati, per conoscere noi stessi e il nostro avversario, e far sì che MySQL diventi un deposito di dati a nostra disposizione.
1. Tipi di log di MySQL
Per default, tutti i log di MySQL sono conservati come file nella directory radice del database:
[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
I tipi di log di MySQL sono i seguenti:
1. Log degli errori (error), informazioni relative all'avvio, esecuzione e arresto dell'istanza del servizio MySQL.
2. Log degli interrogatori (general), tutte le espressioni SQL o i comandi eseguiti dall'istanza del servizio MySQL.
3. Log binari (binary), tutte le espressioni SQL di aggiornamento eseguite sul database, escludendo le espressioni select e show.
4. Log delle query lente (slow), le espressioni SQL con un tempo di esecuzione superiore al valore impostato di long_query_time o le espressioni SQL che non utilizzano l'indice.
Secondo, la cache dei log MySQL
Un sistema veloce, stabile e affidabile, in cui la cache gioca un ruolo cruciale. Anche la gestione dei log di MySQL utilizza un meccanismo di cache. I log di MySQL vengono archiviati originariamente nella memoria del server MySQL, e se superano la capacità di archiviazione specificata, i log nella memoria vengono scritti (o aggiornati) su disco esterno, conservati permanentemente su disco rigido come tabelle di database o file.
Terzo, il log degli errori MySQL (error log)
Il log degli errori di MySQL registra i dettagli di ogni avvio, arresto e le informazioni di avviso o errore generate durante l'esecuzione dell'istanza MySQL. A differenza degli altri log, il log degli errori di MySQL deve essere attivato e non può essere disattivato.
Per impostazione predefinita, il nome del file del log degli errori è: nome_host.err. Tuttavia, il log degli errori non registra tutte le informazioni di errore, ma solo gli errori critici (critical) che si verificano durante l'esecuzione dell'istanza del servizio MySQL.
mysql> show variables like 'log_error'\G *************************** 1. row *************************** Nome_variabile: log_error Valore: /usr/local/webserver/extend_lib/mysql/data/roverliang.err 1 riga in set (0.02 sec)
Quarto, il log degli interrogatori MySQL (general log)
Il log degli interrogatori MySQL registra tutte le operazioni dell'istanza del servizio MySQL, come select, update, insert, delete e altre operazioni, indipendentemente dal fatto che l'operazione sia stata eseguita con successo o meno. Include anche le informazioni sulla connessione e la disconnessione del client MySQL con il server MySQL, indipendentemente dal fatto che la connessione sia stata riuscita o meno. Ci sono tre parametri relativi al log degli interrogatori MySQL.
[]()general_log mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 riga in set (0.01 sec)
Puoi attivare il log di query standard utilizzando set @@global.general_log = 1;
mysql> set @@global.general_log =1; mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | ON | +---------------+-------+
Ma modificare i variabili di MySQL in questo modo avrà effetto solo durante l'esecuzione dell'istanza corrente di MySQL. Una volta riavviato MySQL, torneranno al loro stato predefinito. Per farli durare a lungo, è necessario modificare il file my.cnf di mysql. Aggiungi la seguente configurazione alla fine del file:
general_log = 1
general_log_file
Una volta attivato, l'istanza del servizio MySQL crea automaticamente il file di log di query standard. Il parametro general_log_file imposta la posizione fisica del file di log di query standard. Ecco come fare:
mysql> show variables like 'general_log_file'; +------------------+-----------------------------------------------------------+ | Nome_variabile | Valore | +------------------+-----------------------------------------------------------+ | general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log | +------------------+-----------------------------------------------------------+
Attenzione: poiché il log di query standard registra praticamente tutte le operazioni di MySQL, per un server di database con accesso ai dati frequente, l'attivazione del log di query standard di MySQL ridurrà significativamente le prestazioni del database. Pertanto, si consiglia di disattivare il log di query standard. Solo in periodi speciali, come quando è necessario tracciare alcuni log di query speciali, è possibile aprire temporaneamente il log di query standard;
log_output
Il parametro log_output imposta il contenuto del log di query standard e del log di query lente per essere memorizzato nei tabella del database. Puoi usare set @@global.log_output='table' per memorizzare il log di query standard e il log di query lente nei tabella general e slow_log del database di sistema mysql. È importante notare che i motori di archiviazione di queste due tabella sono CSV, quindi puoi utilizzare SQL per visualizzare il contenuto dei nuovi log di query standard;
set @@global.log_output = 'table'; mysql> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+
V, Log delle query lente MySQL (slow log)
Le domande relative al log delle query lente sono molto apprezzate dagli intervistatori durante le interviste. Prima si poteva solo discutere a lungo dell'architettura master-slave di MySQL e dell'ottimizzazione di MySQL da vari aspetti, ma non si è mai veramente informati su come attivare il log delle query lente e le relative configurazioni.
L'uso del log delle query lente MySQL può monitorare efficacemente le query con un tempo di esecuzione eccessivo o che non utilizzano l'indice. Questo include le query select, update, delete e insert, fornendo assistenza per l'ottimizzazione delle query. Un'altra differenza rispetto al log delle query standard è che il log delle query lente contiene solo le query eseguite con successo. I parametri relativi al log delle query lente MySQL sono 5.
1、slow_query_log
slow_query_log Imposta se il log delle query lente è attivato.
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+
2、slow_query_log_file
Una volta attivato il log delle query lente, l'istanza MySQL crea automaticamente il file di log delle query lente. Il file specificato da slowquerylog_file contiene il contenuto del log delle query lente. Il metodo di modifica è lo stesso riportato in precedenza. Modifica il file my.cnf direttamente.
3、long_query_time
long_query_time Imposta il valore di soglia del tempo di query lenta. Il valore predefinito è 10s.
4、log_quries_not_using_indexes
log_quries_not_using_indexes Se registra la query senza l'uso degli indici nel log delle query lente, indipendentemente dalla velocità di esecuzione della query.
mysql> set @@global.log_queries_not_using_indexes=1; mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Nome_variabile | Valore | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+
Punto 5: log_output
È stato impostato il formato di output per i log delle query normali e delle query lente, con due valori: file, table;
Sezione 6: Visualizzazione del log delle query lente di MySQL
Il parametro log_output può impostare il formato di output del log delle query lente. Di default è FILE, può essere impostato su TABLE;
mysql> desc mysql.slow_log; +----------------+---------------------+ | Campo | Tipo | +----------------+---------------------+ | 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 | +----------------+---------------------+
Dove: lock_time rappresenta il tempo di blocco in attesa di SQL durante l'esecuzione. rows_send rappresenta il numero di righe restituite dopo l'esecuzione dell'SQL. rows_examined rappresenta il numero di record scansionati durante l'esecuzione dell'SQL.
Ma non è comune utilizzare TABLE per memorizzare i log delle query lente, in presenza di un grande volume di attività, può influenzare il servizio principale del sistema. Possiamo utilizzare il metodo FILE per memorizzare i log. Durante l'installazione di MySQL, lo strumento mysqldumpslow.pl è già installato nel directory bin di MySQL per l'analisi dei log delle query lente. L'uso di questo strumento su Windows potrebbe richiedere alcune configurazioni, che non sono nel campo di questo articolo. Per imparare sui servizi di sistema, meglio passare a Linux. I comandi e gli strumenti sotto Linux possono essere utilizzati tramite il comando stesso + l'opzione --help per visualizzare la documentazione di aiuto.
-s indica il metodo di ordinamento
Opzioni secondarie: c, t, l, r
c: numero di volte in cui l'SQL viene eseguito
t: tempo di esecuzione
l: tempo di attesa del lock
r: restituisce il numero di dati
at, al, ar sono le medie corrispondenti a t, l, r. -t: indica di restituire i primi N record.
-g: abbreviazione di grep. Include la corrispondenza di ricerca fuzzy
Ecco un esempio di uso comune:
//Restituisce le 20 istruzioni SQL con il maggior numero di accessi ./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //Restituisce le 20 istruzioni SQL con il maggior numero di record di ritorno ./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //Restituisce le istruzioni SQL contenenti like ./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log
Settimo: log binari (binary)
I log binari e i vari log menzionati in precedenza sono diversi, i log binari non possono essere visualizzati direttamente tramite cat o less visualizzatori di testo. È necessario utilizzare strumenti professionali. I log binari registrano principalmente le modifiche del database, quindi possono essere utilizzati per la sincronizzazione dei database master-slave. Il contenuto include principalmente tutte le operazioni di aggiornamento del database, come le istruzioni use, insert, delete, update, create, alter e drop. In una frase più semplice e comprensibile: tutte le operazioni che interessano i cambiamenti dei dati devono essere registrate nei log binari.
启动二进制日志 使用 show variables like 'log_bin'\G 来查看二进制日志是否开启。
mysql> show variables like 'log_bin'\G
*************************** 1. row ***************************
Variable_name: log_bin
Value: OFF
1 riga in set (0.00 sec)
mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql>
看到log_bin 默认是不开启的，并且是个只读的变量，需要在my.cnf中配置，然后重启MySQL。 service mysql restart 重启MySQL后，在data目录会发现生成了一个1.000001的文件。实际上每次MySQL重启，在目录下都会生成一个这样的文件，文件名依次递增。此外，MySQL还会在该目录下创建一个二进制日志的索引文件，可以通过命令show variables like 'log_bin_index'\G来查看索引文件的位置，然后使用cat命令看下。会发现，里面记录着二进制文件的相对路径。
查看二进制日志 可以使用MySQL 中自带的工具。具体位置在mysql的bin目录下。 mysqlbinlog命令的常用选项：
-s 以精简的方式显示日志内容
-v 以详细的方式显示日志内容
-d=数据库名 只显示指定数据库的日志内容
-o=n 忽略日志中前n行MySQL命令
-r=file 将指定内容写入指定文件
--start-datetime
显示指定时间范围内的日志内容
--stop-datetime
--start-position
显示指定位置间隔内的日志内容
--stop-position
获取当前使用的二进制日志文件
mysql> show master status; +----------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------+----------+--------------+------------------+-------------------+ | 1.000002 | 120 | | | | +----------+----------+--------------+------------------+-------------------+ 1 riga in set (0.00 sec)
Ripristino dei dati utilizzando il registro binario
La sintassi è molto semplice:
mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p
mysqlbinlog può essere seguito dai parametri --start-datetime, --stop-datetime, start-position, stop-position, ecc.
I parametri --start-datetime e --stop-datetime possono essere utilizzati per eseguire il ripristino dei dati basato su un punto temporale;
La posizione start-position e stop-position possono essere utilizzate per rendere più dettagliata la ripristino dei dati;
Parametri relativi al registro binario 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 | IGNORE_ERROR | | binlog_format | STATEMENT | | binlog_gtid_simple_recovery | OFF | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlogging_impossible_mode | IGNORE_ERROR | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | simplified_binlog_gtid_recovery | OFF | | sync_binlog | 0 | +-----------------------------------------+----------------------+
max_binlog_size
maxbinlogsize La dimensione del file di log binario singolo. Se supera questo valore, viene generato un nuovo file con suffisso +1;
binlog_cache_size
binlogcachesize La dimensione della cache nella memoria per i log binari
sync_binlog
sync_binlog Scrive più volte i log binari nella cache, quindi inizia a sincronizzare e aggiornare nella memoria esterna (hard disk).
log_slave_updates
logslvaeupdates per la replica principale e secondaria
Pulizia dei log binari
Principaliamente, è necessario prima eseguire il backup fisico dei log da pulire su un altro dispositivo di archiviazione per conservarli permanentemente. Successivamente, si raccomanda di utilizzare i seguenti due metodi di pulizia a basso rischio:
第一种：
Purge master logs before '2017-02-16 00:00:00';
Secondo:
Imposta direttamente il parametro expire_logs_days nel file di configurazione di MySQL my.cnf per stabilire il numero di giorni di scadenza dei file binari, i file binari scaduti verranno eliminati automaticamente. Si consiglia di avviare un compito pianificato periodico per fare il backup dei file binari prima di eliminarli. Così, non si rischia di trovare errori nei dati dopo diversi giorni e i file di log binari vengono eliminati automaticamente.
expire_logs_days=90
Otto: Log delle transazioni InnoDB
I log delle transazioni InnoDB sono diversi dai log menzionati in precedenza, i log delle transazioni InnoDB sono mantenuti autonomamente dal motore di archiviazione InnoDB e il loro contenuto non può essere letto dagli amministratori del database. MySQL utilizza al massimo la cache per migliorare l'efficienza dell'accesso ai dati. A dire il vero, qualsiasi sistema ad alta performance deve utilizzare la cache, a tutti i livelli la cache gioca un ruolo enorme. Per elevare ulteriormente: la cache e la coda sono la via inevitabile per raggiungere un'alta performance. Per quanto riguarda il database, questo è un problema piuttosto complicato, per garantire che i dati siano letti e memorizzati con maggiore efficienza, è necessario utilizzare la cache. Ma per garantire l'interscambio dei dati, è necessario garantire che tutti i dati siano memorizzati accuratamente nel database, anche in caso di imprevisti, è necessario garantire che i dati siano ripristinabili. Sappiamo che InnoDB è un motore di archiviazione sicuro per le transazioni, e la coerenza è una delle caratteristiche importanti delle transazioni ACID. Il motore di archiviazione InnoDB realizza la coerenza dei dati attraverso i log delle transazioni InnoDB, che includono i log di riconciliazione (redo) e i log di rollback (undo).
Log di riconciliazione (redo)
I log di riconciliazione registrano principalmente le transazioni completate interamente, ossia i log eseguiti con commit, di default i valori dei log di riconciliazione sono registrati nei log di riconciliazione iblogfile0 e iblogfile1.
[root@roverliang data]# pwd /usr/local/webserver/mysql/data [root@roverliang data]# ls ib* ibdata1 ib_logfile0 ib_logfile1
Log di rollback (undo)
I log di rollback registrano principalmente le transazioni incomplete che sono state parzialmente completate e scritte sul disco, di default le informazioni dei log di rollback sono registrate nei file di spazio di archiviazione delle tabelle, nei file di spazio di archiviazione condiviso ibdata1 o nei file di spazio di archiviazione non condiviso non visti in ibd.
Dalla figura superiore possiamo notare che i log di rollback sono predefiniti come registrati in ibdta1. La versione del mio sistema MySQL è: 5.6.24.
Meccanismo di Checkpoint
Dopo il crash del server MySQL, quando si riavvia il servizio MySQL, a causa dell'esistenza dei log di redo (redo log) e dei log di rollback (undo log), InnoDB esegue le operazioni di rollback (rollback) delle transazioni parzialmente completate e scritte sul disco utilizzando i log di rollback (undo log). Poi esegue nuovamente tutte le transazioni nel log di redo (undo log) per ripristinare tutti i dati. Tuttavia, a causa della grande quantità di dati, per abbreviare il tempo di recupero, InnoDB introduce il meccanismo di Checkpoint.
Pagina sporca (dirty page)
Quando una transazione deve modificare un record, InnoDB legge prima il blocco di dati in cui si trova il record dall'esterno della memoria al disco. Dopo il commit della transazione, InnoDB modifica il record nella pagina di dati, a questo punto la pagina di dati nella cache non è più uguale al blocco di dati nell'esterno della memoria, la pagina di dati nella cache viene chiamata pagina sporca (dirty page), e la pagina sporca viene aggiornata nell'esterno della memoria, diventando una pagina pulita (clean page).
Nota: una pagina di memoria è di default 4K, o un multiplo di 4K. Puoi immaginare la memoria come un libro che può essere cancellato, ogni volta che MySQL legge i dati, richiede alcune pagine pulite di memoria, e poi scrive su di esse. Dopo che i dati sono stati aggiornati sul disco, queste pagine di dati vengono cancellate immediatamente, disponibili per altri programmi.
Numero di sequenza del log (log sequence number)
Il numero di sequenza del log (LSN) è il punto finale di ogni log nello spazio di log, rappresentato da un offset in byte, utilizzato durante il Checkpoint e il ripristino.
Principio del meccanismo di Checkpoint: ipotizziamo che a un certo punto nel tempo, tutte le pagine sporche (dirty page) siano state aggiornate sul disco. Prima di questo punto, tutti i log di redo (redo log) non devono essere riapplicati. Il sistema utilizza questa posizione finale del log di redo come Checkpoint, e i log di redo prima del Checkpoint non devono essere riapplicati, possono essere cancellati con sicurezza. Per sfruttare meglio lo spazio del log di redo (redo log), InnoDB utilizza una strategia di rotazione per utilizzare lo spazio del log di redo, quindi i file di log di redo di InnoDB devono essere almeno due. Attraverso il meccanismo di Checkpoint, riapplicando i log di redo (redo log) alle transazioni completate ma non ancora scritte completamente fuori dalla cache, si può garantire l'integrità dei dati e anche abbreviare il tempo di recupero.
Parametri del log di redo InnoDB
innodb_log_buffer_size: imposta la dimensione della cache di log di redo.
innodb_log_files_in_group: imposta il numero di file di log di redo nel gruppo di log.
innodb_log_file_size: imposta la dimensione del file di log di redo, maggiore è il file, più tempo ci vuole per il ripristino.
innodb_mirrored_log_groups: imposta il numero di gruppi di file di log di redo mappati, può essere impostato solo a 1.
innodb_log_group_home_dir: imposta la directory in cui vengono memorizzati i file di gruppo di log, il valore predefinito è nella directory radice del database.
Parametri del log di rollback (undo) InnoDB
innodb_undo_directory: imposta la directory in cui il log di rollback viene memorizzato.
innodb_undo_logs: imposta la dimensione del segmento di log di rollback, il valore predefinito è 128k
innodb_undo_tablespace: imposta il numero di file di log di rollback da utilizzare per il log di rollback, il valore predefinito è 0.
Attenzione speciale: dopo l'installazione di MySQL, è necessario impostare i parametri del log di rollback in my.cnf. Se si impostano i parametri del log di rollback dopo aver creato il database, MySQL genererà un errore e, una volta creato il log di rollback, non sarà più possibile modificarlo o aggiungerlo.
Nona sezione: backup dei file di log
Quando si effettua il backup, è possibile utilizzare flush logs per chiudere tutti i file di log attivi e generare nuovi file di log. Dopo aver chiuso i file di log, è possibile eseguire il backup fisico. Inoltre, flush logs può aggiungere tipi di log specifici:
flush error logs flush general logs flush binary logs flush slow logs
Dichiarazione: il contenuto di questo articolo è stato tratto da Internet, il copyright è della proprietà del rispettivo autore, il contenuto è stato contribuito e caricato autonomamente dagli utenti di Internet, questo sito non detiene il diritto di proprietà, non è stato editato manualmente e non assume alcuna responsabilità legale. Se trovi contenuti sospetti di violazione del copyright, ti preghiamo di inviare una e-mail a notice#oldtoolbag.com (al momento dell'invio dell'e-mail, sostituisci # con @) per segnalare il problema e fornire prove pertinenti. Una volta verificata, questo sito eliminerà immediatamente il contenuto sospetto di violazione del copyright.