List:Backup« Previous MessageNext Message »
From:Hleb Valoshka Date:March 21 2013 10:04am
Subject:proper way to backup slave server (myisam)
View as plain text  
Hello!

We use mysqlhotcopy to make backups. What is the proper way to use it on
slave server?

I've found in doc that I should stop the sql thread and check the value of
Slave_open_temp_tables, if it's greater that 0 then restart sql thread and
repeat this procedure until Slave_open_temp_tables becomes zero.

I wrote some code to test it and then run it. But during the 5 minutes
Slave_open_temp_tables did not change, it was 13. (btw, io thread was
stopped too)

I see that Percona's innobackupex works almost the same way (they don't
stop io thread).

May be I've missed something? We use Mysql 5.1.

That's my code:

total_timeout=300
timeout=3

mysql_execute(){
    mysql -S$mysql_sock -u$mysql_user -p$mysql_pwd -e "$@"
}

get_slave_open_temp_tables(){
    mysql_execute 'SHOW STATUS LIKE "slave_open_temp_tables"\G;' | \
    awk '{ if (/Value:/) print $2; }'
}

try_to_stop_slave(){
    cnt=$total_timeout
    mysql_execute 'STOP SLAVE IO_THREAD;'
    while [ $cnt -gt 0 ]; do
        mysql_execute 'STOP SLAVE SQL_THREAD;'
    tables=`get_slave_open_temp_tables`
    if [ -z "$tables" ]; then
        die "Can't get Slave_open_temp_tables"
    fi
    if [ "$tables" = "0" ]; then
        log "Slave_open_temp_tables is 0, it's safe to start backup"
        break
    else
        log "Slave_open_temp_tables is $tables, restarting replication"
        mysql_execute 'START SLAVE SQL_THREAD;'
        cnt=`expr $cnt - $timeout`
        sleep $timeout
    fi
    done

    if [ $cnt -le 0 ]; then
    try_to_start_slave
    die "Slave_open_temp_tables is still nonzero after $total_timeout sec,
bailng out"
    fi
}

log "Stopping replication..."
try_to_stop_slave

Thread
proper way to backup slave server (myisam)Hleb Valoshka21 Mar