¿Cómo puedo saber cuándo se actualizó por última vez una tabla MySQL?

No tengo la base de datos information_schema, usando mysql versión 4.1.16, por lo que en este caso puede consultar esto:

SHOW TABLE STATUS FROM your_database LIKE 'your_table';

Devolverá estas columnas:

| Name      | Engine | Version | Row_format | Rows | Avg_row_length 
| Data_length | Max_data_length | Index_length | Data_free | Auto_increment 
| Create_time         | Update_time         | Check_time | Collation         
| Checksum | Create_options | Comment |

Como puede ver, hay una columna llamada: "Update_time" que le muestra la última hora de actualización para your_table.

Me sorprende que nadie haya sugerido rastrear el tiempo de la última actualización por fila:

mysql> CREATE TABLE foo (
  id INT PRIMARY KEY
  x INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
  KEY (updated_at)
);

mysql> INSERT INTO foo VALUES (1, NOW() - INTERVAL 3 DAY), (2, NOW());

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | NULL | 2013-08-18 03:26:28 |
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

mysql> UPDATE foo SET x = 1234 WHERE id = 1;

Esto actualiza la marca de tiempo a pesar de que no lo mencionamos en la ACTUALIZACIÓN.

mysql> SELECT * FROM foo;
+----+------+---------------------+
| id | x    | updated_at          |
+----+------+---------------------+
|  1 | 1235 | 2013-08-21 03:30:20 | <-- this row has been updated
|  2 | NULL | 2013-08-21 03:26:28 |
+----+------+---------------------+

Ahora puedes consultar MAX():

mysql> SELECT MAX(updated_at) FROM foo;
+---------------------+
| MAX(updated_at)     |
+---------------------+
| 2013-08-21 03:30:20 |
+---------------------+

Es cierto que esto requiere más almacenamiento (4 bytes por fila para la MARCA de TIEMPO).
Pero esto funciona para tablas InnoDB antes de la versión 5.7.15 de MySQL, que INFORMATION_SCHEMA.TABLES.UPDATE_TIME no lo hace.

Para una lista de los cambios recientes en la tabla use esto:

SELECT UPDATE_TIME, TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
ORDER BY UPDATE_TIME DESC, TABLE_SCHEMA, TABLE_NAME

Crearía un disparador que atrapa todas las actualizaciones / inserciones / eliminaciones y escribiría la marca de tiempo en una tabla personalizada, algo como tablename / timestamp

Solo porque no me gusta la idea de leer las tablas internas del sistema de db server directamente

Lo más sencillo sería comprobar la marca de tiempo de los archivos de tabla en el disco. Por ejemplo, puede comprobar en su directorio de datos

cd /var/lib/mysql/<mydatabase>
ls -lhtr *.ibd

Esto debería darle la lista de todas las tablas con la tabla cuando se modificó por última vez la vez más antigua, primero.

Simplemente tome la fecha de archivo modificada del sistema de archivos. En mi idioma que es:

 tbl_updated = file.update_time(
        "C:\ProgramData\MySQL\MySQL Server 5.5\data\mydb\person.frm")

Salida:

1/25/2013 06:04:10 AM

Si está ejecutando Linux, puede usar inotify para ver la tabla o el directorio de la base de datos. inotify está disponible desde PHP, nodo.js, perl y yo sospechamos de la mayoría de los otros idiomas. Por supuesto, debe haber instalado inotify o haber hecho que su ISP lo instale. Muchos ISP no lo harán.

No estoy seguro de si esto sería de algún interés. Usar mysqlproxy entre mysql y clientes, y hacer uso de un script lua para actualizar un valor clave en memcached de acuerdo con los cambios de tabla interesantes ACTUALIZAR,ELIMINAR,INSERTAR fue la solución que hice hace poco. Si el wrapper soportaba hooks o disparadores en php, esto podría haber sido eaiser. Ninguno de los envoltorios hace esto.

Análisis del nivel de SG:

Encuentre dónde se almacena la base de datos en el disco:

grep datadir /etc/my.cnf
datadir=/var/lib/mysql

Compruebe las modificaciones más recientes

cd /var/lib/mysql/{db_name}
ls -lrt

Debería funcionar en todos los tipos de bases de datos.

Aunque hay una respuesta aceptada, no siento que sea la correcta. Es la forma más sencilla de lograr lo que se necesita, pero incluso si ya está habilitado en InnoDB (en realidad los documentos le dicen que todavía debe obtener NULL ...), si lee MySQL docs , incluso en la versión actual (8.0) usar UPDATE_TIME no es la opción correcta, porque:

Las marcas de tiempo no se conservan cuando se reinicia el servidor o cuando la tabla es desalojada del diccionario de datos InnoDB cache.

Si entiendo correctamente (no puedo verificarlo en un servidor en este momento), la marca de tiempo se restablece después del reinicio del servidor.

En cuanto a las soluciones reales (y, bueno, costosas), tienes la solución de Bill Karwin con CURRENT_TIMESTAMP y me gustaría proponer una diferente, que se basa en disparadores (estoy usando esa).

Comienza creando una tabla separada (o tal vez tenga alguna otra tabla que se pueda usar para este propósito) que funcionará como un almacenamiento para variables globales (aquí marcas de tiempo). Necesita almacenar dos campos: nombre de tabla (o cualquier valor que desee mantener aquí como id de tabla) y marca de tiempo. Después de tenerlo, debe inicializarlo con este id de tabla + fecha de inicio (NOW () es una buena opción :)).

Ahora, se mueve a las tablas que desea observar y agrega disparadores DESPUÉS DE INSERTAR/ACTUALIZAR/ELIMINAR con este procedimiento o similar:

CREATE PROCEDURE `timestamp_update` ()
BEGIN
    UPDATE `SCHEMA_NAME`.`TIMESTAMPS_TABLE_NAME`
    SET `timestamp_column`=DATE_FORMAT(NOW(), '%Y-%m-%d %T')
    WHERE `table_name_column`='TABLE_NAME';
END

Esto es lo que hice, espero que ayude.

<?php
    mysql_connect("localhost", "USER", "PASSWORD") or die(mysql_error());
    mysql_select_db("information_schema") or die(mysql_error());
    $query1 = "SELECT `UPDATE_TIME` FROM `TABLES` WHERE
        `TABLE_SCHEMA` LIKE 'DataBaseName' AND `TABLE_NAME` LIKE 'TableName'";
    $result1 = mysql_query($query1) or die(mysql_error());
    while($row = mysql_fetch_array($result1)) {
        echo "<strong>1r tr.: </strong>".$row['UPDATE_TIME'];
    }
?>

Almacena en caché la consulta en una variable global cuando no está disponible.

Cree una página web para forzar la recarga de la caché cuando la actualice.

Agregue una llamada a la página de recarga en sus scripts de implementación.