1

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...

3
  • Looks like statements with implicit commits (e.g. DDL) will still get through a READ LOCK: dev.mysql.com/doc/refman/5.0/en/flush.html
    – dwjv
    Oct 2 '15 at 14:18
  • Hi @dwjv , ok but this means all implicit commits (either DML) will be wrote? in this situation , how stay the sync between the binlog and the snapshot take at the environment, doesn't make sense this...
    – ceinmart
    Oct 2 '15 at 14:52
  • 1
    DDL has an implicit commit, you cannot control it. DML can be controlled. a FLUSH TABLE WITH READ LOCK is a valid way to do this. It's the way I've always done it.
    – dwjv
    Oct 2 '15 at 15:18
0

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}'
0

I would feel safer to actually shutdown mysqld for the brief duration of the snapshot.

1
  • Hi @Rick, I use this mysql for zabbix monitor and any 20 second without database access could trigger false alarms , so this is last option.
    – ceinmart
    Oct 24 '15 at 19:21

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.