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

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

lock_type: {

Note: I am using innodb storage engine and mariadb 10.3

  • Do not use LOCK TABLES if you are using InnoDB. Please describe further what you are trying to do and "why".
    – Rick James
    Jan 4 '19 at 5:26
  • @RickJames Thanks for having a look on my question. I have updated it. Please have a look again.
    – SkyRar
    Jan 4 '19 at 8:07

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.