The reason I am asking is because we identify that Moodle is read intensive, so we would like to off-load the work to many read replicas.
I have never heard of such a thing... My initial reaction is that it sounds over-complex. If your database is an issue then fix the issue.
Are you storing sessions in your database for example? It's usually/often a bad idea if you are.
No, sessions are not stored in the DB.
Ok thank you I will look at my DB to check if something is wrong there
You can do this without any changes to Moodle by running InnoDB cluster:
It's by no means trivial to set up, so you should probably only do this if your site is absolutely massive.
This kind of configuration is something we are experimenting with our Galera cluster with a front end load balancer controlling the traffic paths of the read/write databases. On paper this sounds promising and less risky but we need to finish our testing before I can say whether it is actually worth doing for our situation.
We have been also wanting to experiment with a multi-master topology as that seems to be more logical of an approach but has its own drawbacks and fears.