I think the easiest and safest way is to let Moodle do it.
Visit (Site) Administration► Server ► Cleanup and look for Keep logs for
There you can set, how long you want to preserve logs, older logs will be deleted. I am using this setting and its working fine. (But still I create regular complete backups, just in case )
Hope this helps.
So, to clarify, I need to delete everything in the mdl_log table but need some coaching on the safest way to do this.
You can try delete by steps..older then 1000 days, 365 days etc
Or maximum brute force is not to use phpmyadmin but directly mysql console if you have access to it. (google some tutorials for mysql console, try some select commands to get a grip with it)
Delete query for mdl_log table is
DELETE FROM `mdl_log` WHERE id < X
and I would delete it in steps too. So for first run I will delete all log that id < 100000 , then 200000 (it depends on size of your table) etc... (the automatic cleanup job in Moodle does the same, it just delete all logs that have time value < limit you set in GUI).
Good luck, hope this helped a little
On my production server, I adjusted the pruning setting to 150 days and checked the timestamp on the first and ending log entries and it would appear that the logs are now down to around 150 days worth (the first is some time at the end of August). I'm concerned though because I've now pruned the mdl_log table however it is still 700MB in size, so there does not appear to be any benefit in reducing the size...do I need to run an OPTIMIZE or REPAIR command on the table to whittle things down to the actual size now?