MLIR is less a specific IR and more a generic framework for expressing your own custom IR (which is itself composed of many pluggable IRs)--except these IRs are renamed "dialects." One of the MLIR dialects is the LLVM dialect, which can be lowered to LLVM IR.
In the future, it's plausible that dialects for hardware ISAs would be added to MLIR, and thus it would be plausible to completely bypass the LLVM IR layer for optimizations. But the final codegen layer of LLVM IR is not a simple thing (I mean, LLVM itself has three different versions of it), and the fact that GlobalISel hasn't taken over SelectionDAG after even a decade of effort should be a sign of how difficult it is to actually replicate that layer in another system to the point of replacing existing work.
Earlier compilers were a pipeline of specialized IRs. I used to think that MLIR was an acknowledgment that this specialization was necessary. Ok, it is necessary. But MLIR's real contribution is, as you say, a generic framework for expressing your own custom IR.
In the future, it's plausible that dialects for hardware ISAs would be added to MLIR, and thus it would be plausible to completely bypass the LLVM IR layer for optimizations. But the final codegen layer of LLVM IR is not a simple thing (I mean, LLVM itself has three different versions of it), and the fact that GlobalISel hasn't taken over SelectionDAG after even a decade of effort should be a sign of how difficult it is to actually replicate that layer in another system to the point of replacing existing work.