Mysql – How to lock all tables except some specific table

mariadbmariadb-10.3MySQL

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

Best Answer

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

Related Solutions

Mysql – Does MySQL guarantee atomic upgrade from read lock to write lock

Indeed, the part of the manual you quoted does just what it says:

-- Connection #1
LOCK TABLE mytable READ; -- lock is acquired

-- Connection #2
LOCK TABLE mytable WRITE; -- execution is suspended, lock is NOT acquired

-- Connection #1
LOCK TABLE mytable WRITE; -- execution is suspended
-- READ lock was released, allowing Connection #2 to acquire the WRITE lock
-- Connection #2 resumes

Solution: do switch to InnoDB and use transactions. Performance of the InnoDB engine improved a lot in the recent versions.

-- Connection #1
BEGIN;
SELECT * FROM mytable LOCK IN SHARE MODE; -- lock is acquired

-- Connection #2
BEGIN;
SELECT * FROM mytable FOR UPDATE; -- execution is suspended, lock is NOT acquired

-- Connection #1
SELECT * FROM mytable FOR UPDATE;
-- deadlock is detected, Connection #2 is rolled back

Mysql – How to set mariadb read_write for some specific tables while starting as read_only=1

Plan A: Turn off read_only and hope that no one abuses it (accidentally or deliberately).

Plan B: Run the 'cache' stuff as some user with SUPER so that it can bypass read_only. This seems more secure than Plan A, but opens up other risks.

Best Answer

Related Solutions

Mysql – Does MySQL guarantee atomic upgrade from read lock to write lock

Mysql – How to set mariadb read_write for some specific tables while starting as read_only=1

Related Question