How to know the date of each record that was entered in the database?

Asked

Viewed 2,565 times

0

Good afternoon!

I have a small database that stores the records of several people. Unfortunately no behavior was created to record the date and time of the record saved in the database.

You can find out the date and time the record was entered into the database?

I tried to use this command:

select TABLE_NAME, CREATE_TIME from information_schema.TABLES where TABLE_SCHEMA = ''

But it only shows the date of the last update in the table but I need the date of each table record.

I count on your help, thank you very much. (:

php mysql sql mysql-workbench
  • 2

    You can create a Trigger to change the values of two columns like criado_em and ultima_atualizacao

  • Good afternoon, thanks for the reply, could demonstrate/teach how I do this Rigger?

  • I would recommend you save the date by your application and not by Rigger. The tables that you need to know the date that was inserted/updated you insert a column in this table to save the insertion/update date, and when entering a new record you send the date together.

  • 2

    But he wants to rescue the date and time of the records that are already saved from a certain table that has no field recording date and time.

  • 2

    @Victorcarnaval In this case there’s no way.

1 answer

2

It is good practice in commercial systems to keep a log of operations(who/did what/when), this gives you the possibility to do:

  • An audit in your system in case of direct or judicial request.
  • History of operation. Your case.
  • Get complementary information for partial backup restore due to system misuse.

As for the situation you find yourself in maybe the internal SQL log system can save you.

Binary log is a set of log files that contains information about the changes in the data made in the instance of the Mysql server.

The log is enabled by starting the server with the option --log-bin. So the first thing you have to do is check how your server initializes the Mysql instance. Usually in enterprise instances or web provisioning this option is selected. In case of home servers or small office usually this option is waived.

If you have never used option --log-bin I’m sorry but these dates are unrecoverable.

But if you’re lucky all your firm’s operations have been logged in to the mysql shell and type:

mysql> SHOW BINARY LOGS;

This command will display the Mysql operation log file set:

+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+

To know which directory these files are in type:

mysql> SHOW VARIABLES LIKE 'datadir';

+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| datadir       | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ |
+---------------+---------------------------------------------+
1 row in set (0.00 sec)

Go to this directory and use the tool mysqlbinlog to read the contents of the logs:

C:\ProgramData\MySQL\MySQL Server 8.0\Data\> mysqlbinlog binlog.000015

uma linha do binlog

Each recorded operation starts with the date in the format ddyymm and time.

Browser other questions tagged php mysql sql mysql-workbench

You are not signed in. Login or sign up in order to post.