How to lock all tables except some specific table?

Question

I am trying to set a stage server which replicate some tables like articles, user etc from master. If I start the slave server as read_only I am not able to my stage site up because there are some tables like cache or session which needs to be rewritten.

So I am trying to use replicate_do_table and then locking those tables manually using below snippets and let other tables like cache and session rewritable.

In short I want read_only lock for all the tables in a database excluding some specific tables to read-write access.

I found below snippet on mysql website. But how can I select only specific tables without writing all the table names manually inside LOCK TABLES

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

Note: I am using innodb storage engine and mariadb 10.3

Answer 1

Do not use LOCK TABLES with InnoDB. For this situation consider the following...

• Leave read_only = OFF
• Provide a readonly login for your application when it goes to the Slave in addition to a readwrite login for when it talks to the Master.

The app user would have grants something like:

GRANT SELECT         ON app_db.*   TO app_user@slave
GRANT ALL PRIVILEGES ON sessions.* TO app_user@slave   -- and cache.*
GRANT ALL PRIVILEGES ON app_db.*   TO app_user@master