Is there a way to analyze a MySQL query and determine whether it's read-only? I have a main database and a replica that is automatically kept in sync with the main one. It would be nice, for each query I make, to perform all data- and database-altering queries on the main database, and all read-only queries on the replica. I'm envisioning some kind of query analyzer, and then based on its output, using the correct database, but maybe there's a different way to do that that I'm not aware of.
I think it will be fairly complicated to do both completely automatic as well as efficient. Imagine a transaction like:
BEGIN TRANSACTION
SELECT ... FROM T1 WHERE ...
UPDATE T2 SET ... = ...
SELECT ... FROM T3 WHERE ...
END TRANSACTION

Would you direct statement 1 and 3 to your replica whereas statement 2 would be asked against your master?
If not, you need to analyze at the transaction level. That, in turn, can be really complicated due to branching in the code.
A viable option would be to do read-only reporting against your replica