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.
