MySQL: How to turn one database to read-only?

Question

I need to turn one database into read-only, so I can clone the database and make schema modifications on the clone before we switch the application to the new database. I know the way to turn the MySQL database server into read-only, but that will make it impossible to do the schema change on the new cloned database.

I searched and didn't find any answer. I suspect there is no such feature. In that case, what would you recommend for upgrading a live database without pausing the service?

Answer 1

You could revoke write access for specific users during this period.

Answer 2

If you're using strictly InnoDB, you can put all of your schema changes into a single transaction, then everything that needs to be read only will be read only while the changes are made. There won't be any need for a second database then.

You'll also put your data transformations in the same transaction.

Answer 3

You could revoke update/insert/delete privilege on the account that is using that database. If the application is logged in as root then you have bigger problems on your hands.