3

I want to reverse engineer what exactly happens to the MySQL database when a product is added in Magento. I will have to reproduce the query, because I would like to manually enter new records using a triggered procedure. Is it possible to somehow capture how the changes were made on the database?

Thanks in advance.

  • What about the source code? – Vérace Aug 18 '14 at 16:20
3

You could enable binary logging. If you have not done so already.

To find out if binary logging is enabled, run this:

SHOW MASTER STATUS;

If you get back, Empty set, it is not enabled.

If you have not, please add this to my.cnf under the [mysqld] section

[mysqld]
log-bin = mysql-bin

Then, restart mysql.

From that point forward, you could look for all changes within timeframes.

For example, suppose you run SHOW BINARY LOGS; and get something like this:

mysql> show binary logs;
+----------------+------------+
| Log_name       | File_size  |
+----------------+------------+
| bin-log.000645 |    5933323 |
| bin-log.000646 | 1073745654 |
| bin-log.000647 | 1073759862 |
| bin-log.000648 | 1073757229 |
| bin-log.000649 | 1073776952 |
| bin-log.000650 | 1073791824 |
| bin-log.000651 | 1073743420 |
| bin-log.000652 | 1073785139 |
| bin-log.000653 |  338795678 |
+----------------+------------+
9 rows in set (0.00 sec)

You could then use the mysqlbinlog utility to dump a text representation of all the SQL commands executed within specified timeframes along with the timestamps.

The options to use are

What to do next

Go find the folder where the binary logs are kept. From the command line run this

ls -l bin-log.*
total 7737092
-rw-rw---- 1 mysql mysql    5933323 Aug 17 17:26 bin-log.000645
-rw-rw---- 1 mysql mysql 1073745654 Aug 17 19:04 bin-log.000646
-rw-rw---- 1 mysql mysql 1073759862 Aug 17 20:45 bin-log.000647
-rw-rw---- 1 mysql mysql 1073757229 Aug 17 22:18 bin-log.000648
-rw-rw---- 1 mysql mysql 1073776952 Aug 18 00:05 bin-log.000649
-rw-rw---- 1 mysql mysql 1073791824 Aug 18 05:18 bin-log.000650
-rw-rw---- 1 mysql mysql 1073743420 Aug 18 08:59 bin-log.000651
-rw-rw---- 1 mysql mysql 1073785139 Aug 18 11:31 bin-log.000652
-rw-rw---- 1 mysql mysql  338795678 Aug 18 12:30 bin-log.000653
-rw-rw---- 1 mysql mysql        351 Aug 18 11:31 bin-log.index

The datetime stamp for each file represents the last time the binlog received SQL commands.

You would request the commands by the timeframe and by the binlog names ordered by timestamp

To see all SQL commands executed from midnight to 8:00AM today, you run this

SQLCMDS=/tmp/SQLCommands.txt
DT1="2014-08-18 00:00:00"
DT2="2014-08-18 08:00:00"
BINLOGS="bin-log.000649 bin-log.000650 bin-log.000651"
mysqlbinlog --start-datetime="${DT1}" --stop-datetime="${DT2}" ${BINLOGS} > ${SQLCMDS}

The output file, /tmp/SQLCommands.txt, will contain every INSERT, UPDATE, DELETE, and DDL command executed in the timeframe you requested. Just make sure you supply the necessary binlogs. If you include all binlogs, the mysqlbinlog dump will still work but would take longer because it would parse through binary logs it does not need (i.e., you don't need to parse bin-log.000645 - bin-log.000648, bin-log.000652, bin-log.000653).

Give it a Try !!!

CAVEAT

If you want to see everything, including the SELECTs, you go with Craig Efrein's answer.

UPDATE 2014-08-21 10:45 EDT

@CraigEfrein made a excellent point in his comment

Activating bin-logs also have an impact on performance and disk space.

To reduce disk I/O based on binary logging, you should config binlogs to be stored on a separate disk. See my old post How do I determine how much data is being written per day through insert, update and delete operations? (under the subheading SSD LIFESPAN)

| improve this answer | |
  • @Rápli András Try Rolando's binlog option first, as it is the easiest way and with less performance overhead than the general log for modifications only. – jynus Aug 18 '14 at 20:21
  • 1
    Hello @RápliAndrás, Rolando's answer was right on for sure. Just one quick point to keep in mind. Activating bin-logs also have an impact on performance and disk space. They cost a lot less in CPU for sure than the general query log, but this is not something you just turn on and forget about. Good luck! – Craig Efrein Aug 21 '14 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.