Anyone used percona-xtrabackup?



  • Thinking of replacing automysql backup with xtrabackup

    https://www.percona.com/software/mysql-database/percona-xtrabackup/feature-comparison



  • We used it at Change when I was there.



  • And how was the experience, is it good? I will be testing this but would like to hear from ML



  • @Reid-Cooper i've seen on another post that you've been using Percona backup, Could you please share some feedback on this?



  • I got a script for Xtrabackup but need some help in breaking it down and understand it well to customise it to our requirements or tweak if necessary

    #!/bin/bash
    
    while [ ${#} -gt 0 ]
    do
        a=${1}
        shift
        case "${a}" in
            --socket)
                socket=${1}
                shift
                ;;
            --data)
                data=${1}
                shift
                ;;
            --dest)
                dest=${1}
                shift
                ;;
            --days)
                days=${1}
                shift
                ;;
            --months)
                months=${1}
                shift
                ;;
            --noslave)
                noslave=1
                shift
                ;;
            --nolock)
                nolock="--no-lock"
                shift
                ;;
        esac
    done
    
    if [[ ! $socket || ! $data || ! $dest || ! $days ]]; then
     echo "Usage:"
     echo "  $0 --socket /tmp/mysql.sock --data /var/lib/mysql --dest /home/backup --days 7 [--months 2] [--noslave] [--nolock]"
     exit 1
    elif [[ ! -S $socket ]]; then
     echo "Error, $socket is not a valid socket";
     exit 1;
    elif [[ ! -f ${data}/ibdata1 ]]; then
     echo "Error, $data does not appear to be a valid data directory";
     exit 1;
    elif [[ ! -d ${dest} ]]; then
     echo "Error, $dest does not appear to be a valid target directory";
     exit 1;
    elif [[ ! $days -gt 0 ]]; then
     echo "Error, $days is not a valid number of days";
     exit 1
    fi;
    
    error() {
        echo -e "$1"
        cat ${log}
        exit 1
    }
    
    # Probably don't want to change these
    ts=$(date +%Y-%m-%d)
    target=${dest}/${ts}
    cnf=${dest}/my.cnf
    log=${dest}/backup.log
    
    echo "Starting backup job - $(date)" > $log
    cd ${dest}
    
    # If it's the beginning of the month, drop the day off
    if [[ $months -gt 0 ]] && [[ "$(date +%d)" == "01" ]]; then
            target=${dest}/${ts}
    fi;
    
    # Check we've got sufficient disk space for a copy of $data
    dbsize=$(du -ks ${data} 2> /dev/null | awk '{print $1}')
    free=$(df -kP ${dest} | tail -n1 | awk '{print $4}')
    if [[ $free -lt $dbsize ]]; then
        error "Insufficient disk space free for backup, ${dbsize}k required but only only ${free}k avail"
    fi;
    
    # Make sure slave is running
    if [[ $noslave -ne 1 ]]; then
        slave=$(mysql --socket ${socket} --host=localhost -e 'show slave status \G'  | egrep -c '(Slave_IO_Running|Slave_SQL_Running): Yes')
        if [[ $slave -ne 2 ]]; then
            error "Slave is not running"
        fi;
    fi;
    
    # Build the my.cnf for xtrabackup
    cat /etc/mysql/my.cnf > $cnf
    cat /root/.my.cnf >> $cnf
    echo "[xtrabackup]" >> $cnf
    echo "datadir = ${data}" >> $cnf
    echo "target_dir = ${dest}/" >> $cnf
    
    # Run the actual backup
    innobackupex --defaults-file=${cnf} --socket=${socket} --no-timestamp --slave-info $target --host=localhost ${nolock} >> ${dest}/backup.log 2>&1
    err=$?
    echo ERR CODE $err >> $log
    if [[ $err -ne 0 ]]; then error "Innobackupex backup returned ${err}, backup failed"; fi;
    
    # Apply the transaction logs so it's consistent
    innobackupex --socket=${socket} --apply-log $target --host=localhost >> ${dest}/backup.log 2>&1
    err=$?
    echo ERR CODE $err >> ${dest}/backup.log
    if [[ $err -ne 0 ]]; then error "Innobackupex log apply returned ${err}, backup failed"; fi;
    
    err=0
    
    # Double check we've got frm files for each IBD/MYI
    for x in $(find ${target} -type f -name '*.ibd' -o -name '*.MYI' | grep -v FTS); do
        b=$(echo $x | cut -d. -f-1)
        if [[ ! -e ${b}.frm ]]; then
            echo "$x exists but no ${b}.frm, backup failed";
            err=1
        fi;
    done;
    
    # Double check all the databases were copied
    for x in $(find ${data} -mindepth 1 -maxdepth 1 -type d); do
        db=`basename $x`;
        if [[ ! -e ${target}/${db} ]]; then
            error "Database $db is missing from target, backup failed"
            err=1
        fi;
    done;
    
    if [[ $err -ne 0 ]]; then error "Sanity checks failed, aborting backup"; fi;
    
    # Create the startup script
    cat > ${target}/start.sh <<EOF
    #!/bin/bash
    
    p=\`dirname \$0\`
    if [[ \$p == "." ]]; then p=\`pwd\`; fi;
    
    if [[ ! -e \${p}/ibdata1 ]]; then
        echo "This script does not appear to be in a proper mysql data directory"
        exit 1
    fi;
    
    if [[ \$# -ne 1 ]]; then
        echo "Unable to start mysql, you must provide a tcp/ip port to listen on"
        echo "IE: \$0 \$\$"
        exit 1
    fi;
    
    port=\$1
    
    if [[ ! "\$port" =~ ^[0-9]{4,5}\$ ]]; then
       echo "Invalid port \$port, expecting a 4-5 digit number"
       exit 1
    fi
    
    echo
    echo Spawning mysql for \$p using port \${port} and socket /tmp/mysql.sock.\${port}
    echo Connect: mysql --socket=/tmp/mysql.sock.\${port} --host=localhost
    echo Shutdown: mysqladmin shutdown --socket=/tmp/mysql.sock.\${port} --host=localhost
    echo
    
    chown -R mysql:mysql \$p
    /usr/bin/mysqld_safe --pid-file=\${p}/mysql.pid --datadir=\${p} --basedir=/usr --port=\${port} --socket=/tmp/mysql.sock.\${port} --plugin-load=innodb=ha_innodb_plugin.so
    EOF
    
    # Copy the log into the target, tarball it and clean up
    cp ${log} ${target}
    cd ${dest}
    chmod 700 ${target}/start.sh
    chmod 700 ${target}
    tar zcvf ${ts}.tgz ${ts} > /dev/null 2>&1 
    if [[ $? -ne 0 ]]; then
        error "Tar failed to complete"
    fi;
    rm -rf ${target}
    
    # Double check the tarball has some size to it
    if [[ $(du -ks ${target}.tgz | awk '{print $1}') -lt 10000 ]]; then
            error "Backup ${target}.tgz is unusually small, backup probably failed"
    fi;
    
    # Setup the latest symlink
    rm -f ${dest}/latest.tgz
    ln -s ${ts}.tgz ${dest}/latest.tgz
    
    # Remove old backups
    ls -rt ${dest}/[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9].tgz 2> /dev/null | head -n-${days} | xargs rm -f
    ls -rt ${dest}/[0-9][0-9][0-9][0-9]-[0-9][0-9].tgz 2> /dev/null | head -n-${months} | xargs rm -f
    
    # Cleanup
    rm -f ${dest}/stderr ${dest}/stdout ${dest}/my.cnf
    
    echo "Done backup job - $(date)" >> $log
    


  • You need to mark it as code when you post a script. Otherwise it's a mess.



  • @scottalanmiller said:

    You need to mark it as code when you post a script. Otherwise it's a mess.

    was wondering how this is done! 🙂



  • So what does this first block means:

    while [ ${#} -gt 0 ]
    do
        a=${1}
        shift
        case "${a}" in
            --socket)
                socket=${1}
                shift
                ;;
            --data)
                data=${1}
                shift
                ;;
            --dest)
                dest=${1}
                shift
                ;;
            --days)
                days=${1}
                shift
                ;;
            --months)
                months=${1}
                shift
                ;;
            --noslave)
                noslave=1
                shift
                ;;
            --nolock)
                nolock="--no-lock"
                shift
                ;;
        esac
    done
    


  • @Ambarishrh That first block is where it is sifting through the command line arguments parsing out which flags have been passed so that it can set the settings for the rest of the script.



  • So for example, if it detects that you passed the --socket flag if would hit that condition and then look for the ${1} which would be the following parameter and sets the socket variable to that value and tells it to move on or shift to the next argument and continue the loop.