English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
Todo el que ha trabajado en sistemas de gran escala sabe que no se puede subestimar el papel de los registros, ya que a menudo, en las etapas posteriores del proyecto, las decisiones de optimización y actualización del proyecto se basan en la información de los registros. Por lo tanto, al estudiar MySQL, no se puede omitir la parte de registro. Todas las optimizaciones aplicadas en las entrevistas que mencionamos se obtienen de los registros. Estudiar los registros de MySQL sistemáticamente nos ayuda a ubicar problemas con precisión y mejorar nuestro nivel de trabajo. Además, una serie de registros posteriores se centrarán en la operación de DBA, estudiando sistemáticamente las configuraciones de todos los aspectos de MySQL, conociendo a nuestro enemigo y haciendo que MySQL se convierta en un almacén de datos a mano alzada.
I. Tipos de registro de MySQL
Por defecto, todos los registros de MySQL se almacenan en forma de archivo en el directorio raíz de la base de datos:
[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
Los tipos de registro de MySQL son los siguientes:
1. Registro de errores (error), información relacionada con el inicio, ejecución o parada del ejemplo de servicio de MySQL.
2. Registro de consulta común (general), todas las sentencias SQL o comandos ejecutados por el ejemplo de servicio de MySQL.
3. Registro binario (binary), todas las sentencias de actualización ejecutadas en la base de datos, sin incluir las sentencias select y show.
4. Registro de consulta lenta (slow), las sentencias SQL que exceden el valor de configuración de long_query_time o no utilizan índices.
Segundo, caché de registro de MySQL
Un sistema rápido, estable y confiable, la caché en él juega un papel crucial. El procesamiento del registro de MySQL también utiliza un mecanismo de caché. El registro de MySQL se almacena inicialmente en la memoria del servidor MySQL, y si se excede la capacidad de almacenamiento especificada, los registros en la memoria se escriben (o refrescan) a la almacenamiento externo, y se almacenan permanentemente en el disco duro en forma de tabla de base de datos o archivo.
Tercero, registro de errores de MySQL (error log)
El registro de errores del MySQL principalmente registra los detalles de inicio y parada del ejemplo de servicio de MySQL cada vez, así como las advertencias o información de errores producidas durante la ejecución del ejemplo de MySQL. A diferencia de otros registros, el registro de errores de MySQL debe estar activado y no se puede desactivar.
Por defecto, el nombre del archivo del registro de errores es: nombre_de_maquina.err. Pero el registro de errores no registra toda la información de errores, solo se registrarán los errores críticos (críticos) que se producen durante la ejecución del ejemplo de servicio de MySQL.
mysql> show variables like 'log_error'\G *************************** 1. fila *************************** Nombre_variable: log_error Valor: /usr/local/webserver/extend_lib/mysql/data/roverliang.err 1 fila en conjunto (0.02 sec)
Cuarto, registro de consulta común de MySQL (general log)
El registro de bitácora de consulta común de MySQL registra todas las operaciones del ejemplo de servicio de MySQL, como select, update, insert, delete y otras operaciones, sin importar si la operación se ejecuta con éxito o no. Además, también incluye información sobre la conexión y desconexión del cliente MySQL con el servidor MySQL, sin importar si la conexión tiene éxito o no. Hay tres parámetros relacionados con el registro de consulta común de MySQL.
[]()general_log mysql> show variables like 'general_log'; +---------------+-------+ | Nombre_de_la Variable | Valor | +---------------+-------+ | general_log | APAGADO | +---------------+-------+ 1 fila en conjunto (0.01 sec)
Se puede usar set @@global.general_log = 1 para activar el registro de consultas comunes.
mysql> set @@global.general_log =1; mysql> show variables like 'general_log'; +---------------+-------+ | Nombre_de_la Variable | Valor | +---------------+-------+ | general_log | ENCENDIDO | +---------------+-------+
Pero modificar los variables de MySQL de esta manera solo tendrá efecto durante la ejecución de la instancia actual de MySQL. Una vez que MySQL se reinicie, se restablecerá a su estado predeterminado. La manera de hacerlo permanentemente es modificar el archivo my.cnf de mysql. Añadir después del archivo de configuración:
general_log = 1
general_log_file
Una vez que se activa el registro de consultas comunes, la instancia del servicio de MySQL crea automáticamente el archivo de registro de consultas comunes. El parámetro general_log_file configura la ubicación física del archivo de registro de consultas comunes. Por ejemplo:
mysql> show variables like 'general_log_file'; +------------------+-----------------------------------------------------------+ | Nombre_de Variable | Valor | +------------------+-----------------------------------------------------------+ | general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log | +------------------+-----------------------------------------------------------+
Atención: ya que el registro de consultas comunes casi registra todas las operaciones de MySQL, para los servidores de bases de datos con acceso a datos frecuente, si se activa el registro de consultas comunes de MySQL, se reducirá significativamente el rendimiento de la base de datos. Por lo tanto, se recomienda desactivar el registro de consultas comunes. Solo en períodos especiales, como cuando se necesita rastrear ciertos registros de consultas especiales, se puede abrir temporalmente el registro de consultas comunes.
log_output
El parámetro log_output configura el contenido del registro de consultas comunes y el registro de consultas lentas almacenado en la tabla de la base de datos. Puede usar set @@global.log_output='table' para almacenar el registro de consultas comunes y el registro de consultas lentas en las tablas general y slow_log de la base de datos del sistema mysql. Es importante destacar que los motores de almacenamiento de estas dos tablas son CSV, por lo que al consultar el nuevo contenido del registro de consultas comunes, se puede usar la sentencia SQL;
set @@global.log_output = 'table'; mysql> show variables like 'log_output'; +---------------+-------+ | Nombre_de_la Variable | Valor | +---------------+-------+ | log_output | TABLE | +---------------+-------+
Cinco, Registro de consultas lentas (slow log) de MySQL
Los problemas relacionados con el registro de consultas lentas, los entrevistadores de entrevistas prefieren hablar sobre este tema. Anteriormente, se podía hablar mucho sobre la arquitectura maestro-esclavo de MySQL y la optimización de MySQL desde varios aspectos, pero no se había investigado realmente cómo activar el registro de consultas lentas y la configuración relacionada.
El registro de consultas lentas de MySQL permite rastrear de manera efectiva las consultas que llevan mucho tiempo de ejecución o que no utilizan índices. Esto incluye consultas SELECT, UPDATE, DELETE y INSERT, proporcionando ayuda para optimizar las consultas. Una diferencia con el registro de consultas normal es que el registro de consultas lentas solo contiene consultas que se ejecutaron con éxito. Los parámetros relacionados con el registro de consultas lentas de MySQL incluyen5unidades.
1、slow_query_log
slow_query_log configura si se activa el registro de consultas lentas.
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Nombre_de_la Variable | Valor | +----------------+-------+ | slow_query_log | DESACTIVADO | +----------------+-------+
2、slow_query_log_file
Una vez que se activa el registro de consultas lentas, la instancia de MySQL crea automáticamente el archivo de registro de consultas lentas. El archivo especificado por slowquerylog_file almacena el contenido del registro de consultas lentas. El método de modificación es el mismo que en el texto anterior. Edite directamente el archivo my.cnf.
3、long_query_time
long_query_time establece el umbral de tiempo para las consultas lentas. El valor predeterminado es10s。
4、log_quries_not_using_indexes
log_quries_not_using_indexes ¿registra las consultas que no utilizan índices en el registro de consultas lentas, independientemente de la velocidad de la consulta.
mysql> set @@global.log_queries_not_using_indexes=1; mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Valor | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+
5, log_output
Se han configurado la forma de salida de los registros de consultas comunes y de las consultas lentas, y los valores posibles son dos: file, table;
Seis, Ver registro de consultas lentas de MySQL
El parámetro log_output puede configurar la forma de salida de los registros de consultas lentas. Por defecto es FILE, pero puede configurarse como 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) sin signo | | sql_text | mediumtext | | thread_id | bigint(21) sin signo | +----------------+---------------------+
Entre ellos: lock_time representa el tiempo que el SQL está bloqueado por el bloqueo durante la ejecución. rows_send representa el número de filas devueltas después de ejecutar SQL. rows_examined representa el número de registros escaneados durante la ejecución del SQL.
No es común usar TABLE para almacenar los registros de consultas lentas, en situaciones de gran volumen de negocio, puede afectar al servicio principal del sistema. Podemos usar el modo FILE para almacenar los registros de log. Al instalar MySQL, el herramienta mysqldumpslow.pl para el análisis de registros de consultas lentas ya está instalada por defecto en el directorio bin de MySQL. En el sistema Windows, es posible que necesite ajustar algunas configuraciones, lo cual no está dentro del alcance de esta introducción, así que, si estudia servicios de sistema, mejor pase a Linux. En Linux, los comandos y herramientas pueden usarse con el comando en sí mismo. + --Utilice la opción help para ver el documento de ayuda.
-s indica por qué método se ordena
opciones subyacentes: c, t, l, r
c: número de veces que se ejecuta SQL
t: tiempo de ejecución
l: tiempo de espera de bloqueo
r: devuelve el número de datos
at, al, ar son los promedios correspondientes a t, l, r. -t: representa devolver los primeros N registros.
-g: grep abreviatura. Coincidencia de coincidencia borrosa
Las formas comunes son las siguientes:
//devuelve el número más alto de veces de acceso20 sentencias SQL ./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //devuelve el número más alto de registros return20 sentencias SQL ./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //devuelve las sentencias SQL que contienen like ./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log
Séptima parte: registro binario (binary)
El registro binario es diferente de los varios tipos de registros mencionados anteriormente, el registro binario no se puede ver directamente mediante cat o el visor de texto less. Se necesita un herramienta profesional. El registro binario principalmente graba los cambios en la base de datos, por lo que se puede usar para la sincronización de bases de datos maestras y esclavas. El contenido incluye todas las operaciones de actualización de la base de datos, use statement, insert statement, delete statement, update statement, create statement, alter statement, drop statement. Una frase más sencilla y comprensible sería: todas las operaciones que involucran cambios en los datos deben registrarse en el registro binario.
Iniciar registro binario Ver si el registro binario está activado utilizando show variables like 'log_bin'\G.
mysql> show variables like 'log_bin'\G
*************************** 1. fila ***************************
Nombre_de_variable: log_bin
Valor: DESACTIVADO
1 fila en conjunto (0.00 seg)
mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): La variable 'log_bin' es una variable de solo lectura
mysql>
Ver que log_bin está desactivado por defecto y es una variable de solo lectura, que debe configurarse en my.cnf y luego reiniciar MySQL. service mysql restart Después de reiniciar MySQL, en el directorio data se encontrará que se ha creado1.000001El archivo. En realidad, cada vez que MySQL se reinicia, se crea un archivo de este tipo en el directorio, con nombres incrementales. Además, MySQL también crea un archivo de índice de registro binario en el directorio, que se puede ver mediante el comando show variables like 'log_bin_index'\G para obtener la ubicación del archivo de índice, y luego usar el comando cat para verlo. Se descubrirá que contiene la ruta relativa de los archivos binarios.
Para ver el registro binario, se puede usar la herramienta integrada de MySQL. La ubicación específica está en el directorio bin de mysql. Opciones comunes del comando mysqlbinlog: }}
-s Mostrar el contenido del registro de manera concisa
-v Mostrar el contenido del registro de manera detallada
-d=nombre_de_base_de_datos Mostrar solo el contenido del registro de la base de datos especificada
-o=n Ignorar las primeras n líneas de comandos MySQL en el registro
-r=archivo Escribir el contenido especificado en el archivo especificado
--start-datetime
Mostrar el contenido del registro en el rango de tiempo especificado
--stop-datetime
--start-posición
Mostrar el contenido del registro en el intervalo de posición especificado
--stop-posición
Obtener el archivo de registro binario utilizado actualmente
mysql> show master status; +----------+----------+--------------+------------------+-------------------+ | Archivo | Posición | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------+----------+--------------+------------------+-------------------+ | 1.000002 | 120 | | | | +----------+----------+--------------+------------------+-------------------+ 1 fila en conjunto (0.00 seg)
Usar el registro binario para recuperar datos
La sintaxis es很简单:
mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p
Después de mysqlbinlog se puede seguir --start-datetime,--stop-datetime, start-posición, stop-posición y otros parámetros.
--start-datetime,--stop-datetime estos parámetros pueden realizar la recuperación de datos basada en el punto de tiempo;
start-posición, stop-La posición puede realizar operaciones de recuperación de datos más detalladas;
Parámetros relacionados con el registro binario de 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_ERROR | | binlog_format | STATEMENT | | binlog_gtid_simple_recovery | DESACTIVADO | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ACTIVADO | | binlog_row_image | COMPLETO | | binlog_rows_query_log_events | DESACTIVADO | | binlog_stmt_cache_size | 32768 | | binlogging_impossible_mode | IGNORAR_ERROR | | innodb_api_enable_binlog | DESACTIVADO | | innodb_locks_unsafe_for_binlog | DESACTIVADO | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | simplified_binlog_gtid_recovery | DESACTIVADO | | sync_binlog | 0 | +-----------------------------------------+----------------------+
max_binlog_size
maxbinlogsize 单个二进制日志文件的大小。如果超过该值，则生成新的文件，后缀名为+1;
binlog_cache_size
binlogcachesize 存放二进制日志的内存缓存大小
sync_binlog
sync_binlog 写入几次二进制日志到缓存中，开始同步刷新到外存（硬盘）中。
log_slave_updates
logslvaeupdates 用于主从复制
Limpieza de los registros binarios
En principio, debe primero hacer una copia de seguridad física de los registros que se preparan para limpiar y almacenarlos permanentemente en otro dispositivo de almacenamiento. Luego, se recomienda utilizar los dos métodos de limpieza de bajo riesgo que se mencionan a continuación:
Primera opción:
purge master logs before '2017-02-16 00:00:00';
Segunda opción:
Establezca el parámetro expire_logs_days directamente en el archivo de configuración de MySQL my.cnf para establecer el número de días de expiración de los archivos binarios, los archivos binarios caducados se eliminarán automáticamente. Se recomienda iniciar otro plan de tareas planificadas para hacer copias de seguridad de los archivos binarios regularmente, para evitar que algunos datos se descubran varios días después de que ocurrió un error y los archivos binarios se eliminaron automáticamente.
expire_logs_days=90
Ocho, registro de transacción de InnoDB
Los registros de transacción de InnoDB son diferentes de los registros mencionados anteriormente, los registros de transacción de InnoDB son mantenidos por el motor de almacenamiento InnoDB por sí mismo y su contenido no puede ser leído por el administrador de la base de datos. Entonces, cualquier sistema de alto rendimiento debe utilizar la caché para mejorar la eficiencia de acceso a los datos. Entonces, en otras palabras, cualquier sistema de alto rendimiento debe utilizar la caché, desde todos los niveles hablando, la caché desempeña un papel crucial. Subiendo a un nivel más alto, refinar: la caché y la cola son el camino obligatorio para lograr un alto rendimiento. Entonces, para la base de datos, este es un problema muy difícil, para garantizar que los datos se lean y almacenen con mayor eficiencia, se debe utilizar la caché. Pero para garantizar la consistencia de los datos, es necesario garantizar que todos los datos se almacenen de manera precisa y sin errores en la base de datos, incluso si ocurre un accidente, también se debe garantizar que los datos sean recuperables. Sabemos que InnoDB es un motor de almacenamiento transaccional seguro y que la consistencia es una característica importante de ACID. El motor de almacenamiento InnoDB principalmente realiza la consistencia de los datos a través de los registros de transacción de InnoDB, los registros de transacción de InnoDB incluyen los registros de redo y los registros de rollback.
Registro de redo (redo)
Los registros de redo principal graban las transacciones que se han completado completamente, es decir, los registros de los que se ha ejecutado commit, por defecto, los registros de redo se graban en iblogfile0 y iblogfile1en los registros de redo.
[root@roverliang data]# pwd /usr/local/webserver/mysql/data [root@roverliang data]# ls ib* ibdata1 ib_logfile0 ib_logfile1
Registro de rollback (undo)
El registro de rollback principal graba las transacciones no completadas que ya se han completado en parte y se han escrito en el disco duro, por defecto, la información del registro de rollback se graba en el archivo de espacio de tabla, el archivo de espacio de tabla compartido ibdata1o espacio de tabla exclusivo no se ve en el archivo ibd.
Como podemos ver en la imagen superior, los registros de deshacer por defecto se graban en el archivo ibdta1del. Mi versión del sistema mysql es:5.6.24.
Mecanismo de Checkpoint
Después de que el servidor MySQL se colapse, al reiniciar el servicio de MySQL, debido a la existencia de los registros de rediseño (redo) y los registros de deshacer (undo), InnoDB realiza operaciones de deshacer (rollback) de todas las transacciones que se han completado parcialmente y se han escrito en el disco duro a través de los registros de deshacer (undo). Luego, se reejecutan todos los eventos de transacción en los registros de rediseño (undo) para recuperar todos los datos. Pero debido a la gran cantidad de datos, para acortar el tiempo de recuperación, InnoDB introduce el mecanismo de Checkpoint.
Página sucia (dirty page)
Cuando una transacción necesita modificar un registro, InnoDB primero lee el bloque de datos donde se encuentra el registro desde el almacenamiento externo al disco duro, después de que la transacción se comprometa, InnoDB modifica el registro en la página de datos, en este momento, la página de datos en la caché ya no es la misma que el bloque de datos en el almacenamiento externo, en este momento, la página de datos en la caché se llama página sucia (dirty page), la página sucia se actualiza al almacenamiento externo y se convierte en una página limpia (clean page).
Nota: Una página de memoria por defecto es4K, o4multiplo de K. Puedes imaginar la memoria como un libro que se puede borrar, cada vez que MySQL lee datos, solicita algunas páginas limpias de la memoria, y luego escribe en ellas. Después de que los datos se actualicen al disco duro, estas páginas de datos se borran inmediatamente y están disponibles para otros programas.
Número de secuencia del registro (log sequence number)
El número de secuencia del registro (LSN) es el punto de final de cada registro en el espacio de registro, representado por el desplazamiento en bytes, utilizado en Checkpoint y recuperación.
Principio del mecanismo de Checkpoint. Supongamos que en un momento determinado, todas las páginas sucias (dirty page) se han actualizado al disco duro, todos los registros de rediseño (redo) antes de este momento no necesitan ser rediseñados. El sistema utiliza el final del registro de rediseño en este momento como Checkpoint, y los registros de rediseño antes del Checkpoint también no necesitan ser rediseñados, pueden ser eliminados con confianza. Para utilizar mejor el espacio del registro de rediseño (redo), InnoDB utiliza una estrategia de rotación para usar el espacio del registro de rediseño, por lo tanto, el archivo de registro de rediseño de InnoDB debe ser al menos2a través del mecanismo de Checkpoint, a través del registro de rediseño (redo) se realiza una operación de rediseño (undo) de las transacciones que se han completado pero no se han llevado a cabo completamente en el almacenamiento externo debido a la recuperación de la base de datos, lo que garantiza la consistencia de los datos y también puede acortar el tiempo de recuperación.
Parámetros del registro de rediseño (redo) de InnoDB
innodb_log_buffer_size: Establece el tamaño del búfer de registro de rediseño.
innodb_log_files_in_group: establece la cantidad de registros de rehacer (redo) en el grupo de archivos de registro.
innodb_log_file_size: establece el tamaño del archivo de registro de rehacer, cuanto mayor sea el archivo, más tiempo llevará la recuperación.
innodb_mirrored_log_groups: cantidad de grupos de archivos de registro de rehacer (redo) espejados, solo se puede establecer en1.
innodb_log_group_home_dir: establece el directorio donde se almacenan los archivos de grupo de registro, el valor predeterminado está en la raíz del directorio de la base de datos.
Parámetros del registro de rollback (undo) de InnoDB
innodb_undo_directory: establece el directorio donde se almacena el registro de rollback.
innodb_undo_logs: establece el tamaño del segmento de registro de rollback, el valor predeterminado es128k
innodb_undo_tablespace: establece cuántos archivos de registro de rollback componen el registro de rollback, el valor predeterminado es 0.
Advertencia: Después de instalar MySQL, debe configurar los parámetros de registro de rollback en my.cnf. Si configura los parámetros de registro de rollback después de crear la base de datos, MySQL emitirá un error y, una vez creados los registros de rollback, no se puede modificar ni agregar.
Nueve, respaldo de archivos de registro
Al hacer copias de seguridad, puede usar flush logs para cerrar todos los archivos de registro actuales y luego generar nuevos archivos de registro. Después de cerrar los archivos de registro, puede realizar copias de seguridad físicas. Además, flush logs puede agregar tipos específicos de registros:
flush error logs flush general logs flush binary logs flush slow logs
Aviso: El contenido de este artículo se ha obtenido de la red, es propiedad de los respectivos propietarios. El contenido ha sido contribuido y subido por usuarios de Internet de manera autónoma. Este sitio no posee los derechos de propiedad, no ha sido editado por humanos y no asume ninguna responsabilidad legal. Si encuentra contenido sospechoso de copyright, por favor envíe un correo electrónico a: notice#oldtoolbag.com (al enviar un correo electrónico, reemplace # con @) para denunciar y proporcionar evidencia relevante. Una vez confirmado, este sitio eliminará inmediatamente el contenido sospechoso de infracción.