Skip to main content
edited body
Source Link
ceinmart
  • 384
  • 1
  • 4
  • 20

The lock asis released when the connection is finished.

The lock as released when the connection is finished.

The lock is released when the connection is finished.

Source Link
ceinmart
  • 384
  • 1
  • 4
  • 20

Found the reason my script failed.

The lock as 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}'