Sometimes we want to find out what has changed in the database when some application action was made. One very simple way to do that is to use MySQL data dump with “–skip-opt” option + diff. Of course if your database is very big, it may take a while. “–skip-opt” option will cause mysqldump to write one line with INSERT for each record - something we will need for diff to work well.
Here we go, the first snapshot:
% mysqldump -u root -p --skip-opt moodle31 > snapshot1.sqlThen I’ve opened my Moodle 3.1 installation and changed “Due date” in “Default assignment settings” - from 1 week to 2 weeks.
Second snapshot:
% mysqldump -u root -p --skip-opt moodle31 > snapshot2.sqlAnd simple diff:
% diff snapshot1.sql snapshot2.sql
3957a3958
> INSERT INTO `mdl_config_log` VALUES (1014,2,1477218080,'assign','duedate','1209600','604800');
4718c4719
< INSERT INTO `mdl_config_plugins` VALUES (814,'assign','duedate','604800');
---
> INSERT INTO `mdl_config_plugins` VALUES (814,'assign','duedate','1209600');
13367c13368
< INSERT INTO `mdl_sessions` VALUES (4,0,'ob7ot4ngt6nddto7p5snvq3qb3',2,NULL,1477217567,1477217568,'127.0.0.1','127.0.0.1');
---
> INSERT INTO `mdl_sessions` VALUES (4,0,'ob7ot4ngt6nddto7p5snvq3qb3',2,NULL,1477217567,1477218080,'127.0.0.1','127.0.0.1');
15451c15452
< INSERT INTO `mdl_user` VALUES (2,'manual',1,0,0,0,1,'admin','$2y$10$p3IwPwFK9QAJumwKLj9mRe1MxaC3I1OUC1APcyCChcUFMMZIAmqrG','','Admin','User','nexor1984@gmail.com',0,'','','','','','','','','','','','','en','gregorian','','99',1476995496,1477217567,1476995496,1477217567,'127.0.0.1','',0,'','',1,1,0,1,1,0,0,1476995516,0,NULL,'','','','');
---
> INSERT INTO `mdl_user` VALUES (2,'manual',1,0,0,0,1,'admin','$2y$10$p3IwPwFK9QAJumwKLj9mRe1MxaC3I1OUC1APcyCChcUFMMZIAmqrG','','Admin','User','nexor1984@gmail.com',0,'','','','','','','','','','','','','en','gregorian','','99',1476995496,1477218080,1476995496,1477217567,'127.0.0.1','',0,'','',1,1,0,1,1,0,0,1476995516,0,NULL,'','','','');
16508c16509
< -- Dump completed on 2016-10-23 12:19:07
---
> -- Dump completed on 2016-10-23 12:21:59We can see straight away that:
- new record has been inserted into mdl_config_log
- the value in mdl_config_plugins has changed from 604800 to 1209600
- timestamps in mdl_sessions and mdl_user has been updated
Let’s make another test - let’s see what happens when we enable mobile web services in Moodle (I did that using Moodle UI between first and second snapshot).
% mysqldump -u root -p --skip-opt moodle31 > snapshot1.sql
% mysqldump -u root -p --skip-opt moodle31 > snapshot2.sql
% diff snapshot1.sql snapshot2.sql
2492c2492
< INSERT INTO `mdl_config` VALUES (35,'enablewebservices','0');
---
> INSERT INTO `mdl_config` VALUES (35,'enablewebservices','1');
2901c2901
< INSERT INTO `mdl_config` VALUES (447,'enablemobilewebservice','0');
---
> INSERT INTO `mdl_config` VALUES (447,'enablemobilewebservice','1');
2918a2919
> INSERT INTO `mdl_config` VALUES (465,'webserviceprotocols','rest');
3955a3957
> INSERT INTO `mdl_config_log` VALUES (1013,2,1476995587,NULL,'enablemobilewebservice','1','0');
6446c6448
< INSERT INTO `mdl_external_services` VALUES (1,'Moodle mobile web service',0,NULL,0,'moodle',1476995420,1476995523,'moodle_mobile_app',1,1);
---
> INSERT INTO `mdl_external_services` VALUES (1,'Moodle mobile web service',1,NULL,0,'moodle',1476995420,1476995598,'moodle_mobile_app',1,1);
12876a12879
> INSERT INTO `mdl_role_capabilities` VALUES (1234,1,7,'webservice/rest:use',1,1476995587,2);
13359c13362
< INSERT INTO `mdl_sessions` VALUES (2,0,'t0ivjdjkmoo24jfar7ovrqbq31',2,NULL,1476995496,1476995566,'127.0.0.1','127.0.0.1');
---
> INSERT INTO `mdl_sessions` VALUES (2,0,'t0ivjdjkmoo24jfar7ovrqbq31',2,NULL,1476995496,1476995587,'127.0.0.1','127.0.0.1');
16499c16502
< -- Dump completed on 2016-10-20 22:33:01
---
> -- Dump completed on 2016-10-20 22:33:22