MySQL – FLUSH TABLES WITH READ LOCK Not Working

backuplockingMySQL

I use this command to "lock" my database, flush logs , take a LVM snapshot and release it.
Testing the recover + binlogs , appear it's not working.
(I discovery this when during the recover, applying the binlogs give error of duplicate key where supose not should exists at that moment)

The behave I get is the data files still be writting after the flush.
So , what's wrong here?
I miss something?

# echo "FLUSH TABLES WITH READ LOCK ; flush logs" | mysql -vvv
--------------
FLUSH TABLES WITH READ LOCK
--------------

Query OK, 0 rows affected (0.00 sec)


# mysql --version
mysql  Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using  EditLine wrapper

I not sure , but after flush with read lock , if I connect to any database and try create a table, it's should lock and wait until the UNLOCK , don't ?

This isn't happen, it create the table successfully.

EDIT

I found a documentation where say to run SET GLOBAL read_only = ON after the flush and SET GLOBAL read_only = OFF.
All other suggestions, blogs, tutorials, manuals don't mention this is needed.
More doubts!!!

EDIT 2

Insert and updates still working too…

Best Answer

Found the reason my script failed.

The lock is released when the connection is finished.

Appear be quite obvious, but I don't remember read at any place about this behave and since few commands are able to execute from prompt with mysqladmin (not this case) induce to wrong interpretation of FLUSH behave.

My first script, where fails to lock look like this : 

    # Trava banco de dados e faz flush dos dados em disco
    echo
    echo "Travando banco e sincronizando dados..."
    echo "FLUSH TABLES WITH READ LOCK" | mysql -vvv 2>&1 | awk '{print " | "$0}'

    echo "Forcando novo binlog"
    echo "FLUSH LOGS" | mysql -vvv 2>&1 | awk '{print " | "$0}'

    # sincroniza FS
    sync
    sleep 1

    # cria snap , com 10G de dados
    echo
    echo "Criando snapshot..."
    echo "  lvcreate --snapshot $VG/lvmysql -n $vN -L 10G"
    lvcreate --snapshot $VG/lvmysql -n $vN -L 10G

    # libera banco de dados
    echo
    echo "Liberando banco de dados..."
    echo "UNLOCK TABLES " | mysql -vvv  2>&1 | awk '{print " | "$0}'

    echo
    echo "Snapshot criado : "
    lvs -o +lv_time | awk '{print "  "$0}'

The problem is the FLUSH running in standalone session, when this session close it's released.

To solve I run all at unique session.
This is how I rewrite it and work properly 

    VG=system
    vN=lv_bkp1
    # Trava banco de dados e faz flush dos dados em disco
    echo
    {
    echo "
    select '-- Travando banco e sincronizando dados...' ;
    FLUSH TABLES WITH READ LOCK  ;

    select '-- Forcando novo binlog' ;
    FLUSH LOGS ;

    select '-- Logando posicao do bin-log, caso necessario...' ;
    SHOW MASTER STATUS ;

    select '-- ! sync' ;
    system sync ;

    select '-- ! sleep 1' ;
    system sleep 1 ;

    select '-- Criando snapshot...' ;
    system lvcreate --verbose --snapshot $VG/lvmysql -n $vN -L 10G ;

    select '-- Liberando banco de dados...' ;
    UNLOCK TABLES ;
    "
    } 2>&1 | mysql --unbuffered -vvv 2>&1 | awk '{print " | "$0}'

    echo
    lvs -o +lv_time | awk '{print "  "$0}'
Related Question