(That is not asked out of idle curiosity, and I've been told the worst intrusions hack the kernel so tripwire would likely be useless, but don't know if those are widespread.)
I operate from the assumption that any serious compromise starting at SQLI injection equates to kernel compromise, so I don't spend a lot of time thinking about host-IDS either.
Tripwire is not a total waste of time, like a network IDS would be, but for most startups a minute spent setting up Tripwire is a minute that could be better spent on appsec.
That is quite a leap to assume root access. A user mode database should have write access only to partitions that are mounted noexec. That would make getting root privileges quite hard even on a vulnerable kernel.
Also with proper hardening you can prevent the kernel from being modified even by root. Things like FreeBSD securelevel that once enabled blocks writing to kernel memory and raw disk devices.
Tripwire is just file integrity. It can help to detect the installation of a backdoor. But that doesn't help at all if the backdoor is just memory resident (or installs at kernel level). It also doesn't help if the attacker breaks in, grabs your data and leaves for good.
File integrity is also a pain in the ass. You have to keep a database of good file hashes and it can't be stored on the server (or the attacker modifies the known good hashes). Generally you also should not even have the file integrity software on the live server filesystem.
Similarly Network IDS has the flaw that you must have well defined profiles of "normal behavior" so it can identify abnormal behavior. The other option is signature-based but that would only detect known exploits.
(That is not asked out of idle curiosity, and I've been told the worst intrusions hack the kernel so tripwire would likely be useless, but don't know if those are widespread.)