This chapter will examine how we can use the change-tracking mechanism, in order to record structure and data changes done from the phpMyAdmin interface and to obtain reports about such changes.
Each software application has its own idea of what changes are important to track for its users. This section describes tracking systems that exist in other applications, and compares them to the one offered by phpMyAdmin.
Having access to historic data that displays all of the changes made to an information system is a feature that is taken for granted in many software products. The "undo" feature of any serious word-processing software is an example of being able to go back in time, albeit one step at the time. A more complex example would be the history feature of MediaWiki (the core software of Wikipedia). It enables us to go back to any state of a given page, to see the changes between any two versions, and even to mark any older version as the current one. Tracking information includes the author (or IP address), the date and time of change, and a comment.
In MySQL itself, the logging system (binlog) records all changes made to the database; however, in this case, the goal is two-fold:
- To allow master-slave synchronization
- To enable restoration via the
mysqlbinlogcommand-line utility
phpMyAdmin's tracking system allows the user to specify which table is going to be tracked, so it can be called an opt-in system. By default, no table is tracked unless a developer elects to do so; and when a developer activates tracking for a table, changes start to be recorded even if performed by someone else. Only the changes done via phpMyAdmin are recorded.
Furthermore, for a given table, we can indicate which statements we are interested in tracking. The list of statements is divided into two groups: data definition and data manipulation.
Suppose that a team is working on a project that involves making changes to the structure of tables. With tracking activated and assuming that each developer logs in to MySQL with his or her own account, we now have access to historic data, including information about which developer dropped some critical column! Of course, this tracking is not tamper-proof; after all, it's stored in a MySQL table so the security of this tracking information depends on who has access to the tracking table.