How to Set MySQL/MariaDB Read-Only Except for a Single Table

Here are the grants you just mentioned

GRANT RELOAD, PROCESS ON *.* TO 'my_app'@'%' IDENTIFIED BY PASSWORD '*'
GRANT ALL PRIVILEGES ON `my_app`.* TO 'my_app'@'%' 

Based on this, you should not be able to write to the Slave because SUPER is a system level privilege (SUPER only appears in mysql.user as Super_priv) and not a DB level privilege (SUPER does not appear in mysql.db). The first GRANT lacks SUPER privilege. The second GRANT does not have a SUPER privilege context at all.

Having SUPER privilege has a lot of firepower because you can run the following

• CHANGE MASTER TO
• KILL
• SET GLOBAL
• PURGE BINARY LOGS

Not every user needs this. Giving the SUPER privilege to just anyone can actually hamper a DBA from logging into mysql if max_connections is reached.

When you ran SET GLOBAL READ_ONLY=ON; you stop users that do not have the SUPER privilege.

If every user has the SUPER, then setting read_only does not help.

Run this query

SELECT user,host FROM mysql.user WHERE super_priv = 'Y';

Whatever rows appear are the users that can still make changes even with read_only on.

SUGGESTIONS

What you really need to do is run

FLUSH TABLES WITH READ LOCK;

and hold that DB Connection open until you run your snapshot to completion.

How can you do so ? In my post How can I optimize a mysqldump of a large database?, I mention doing this in conjunction with a SLEEP command. Here is an except from that post under the heading "Option 4 : USE YOUR IMAGINATION" Point #2:

Using separate process, run "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" before launching mysqldumps. Kill this process after mysqldumps are complete. This is helpful if a database contains both InnoDB and MyISAM

Here is some code from my 3-year-old post Writing transactions to .MYD file to take Backup

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

In your particular case, just replace the mysqldump with your LVM snapshot code.

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

*** Snapshot Code Goes Here ***

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

GIVE IT A TRY !!!