Backup and Recovery Strategy About Stacy 10+ years of experience on - PowerPoint PPT Presentation

Backup and Recovery Strategy

About Stacy ▪ 10+ years of experience on various flavors of relational databases. ▪ Focus on performance tuning, code reviews, database deployment and infrastructure management for MySQL ▪ In her spare time, she enjoys reading books and doing some volunteer work.

About Kushal ▪ 4+ years of experience on relational databases. ▪ Focus on infrastructure management for MySQL and Oracle RAC, code reviews, troubleshooting and capacity planning ▪ In spare time, like to play indoor and outdoor games

MySQL at Yahoo ▪ MySQL powers many mission-critical products at Yahoo ▪ Different configurations based on product requirements ▪ Products: Yahoo Sports, Flickr, Finance, Tumblr, Daily Fantasy, Ad System, YDS ▪ MySQL Flavor: Percona Server 5.5 to 5.6 including XtraDB Cluster ▪ Operating systems : RHEL 6.x

What are the next 50 minutes about? § Backups • XtraBackup and its options § Recovery • File based • GTID enabled • MTS without GTID § Backup Monitoring § Multi Source Replication Setup

Backups § Why we need backup?

MySQL Database Backup Method • Hot Backup § mysqldump § MySQL Enterprise Backup § Xtrabackup § File System •Cold Backup § File System

Why go for XtraBackup XtraBackup is an open-source hot backup utility § Optimized ● Uses posix_fadvise() not to cache data in memory ● Perform more aggressive read-ahead optimizations on the source files § Reliable ● Checks for page corruption on each page with InnoDB’s buf_page_is_corrupted() function. § No interruption for writes

Xtrabackup Improvements Percona MySQL 5.6.16-64+ use backup locks instead of “ FLUSH TABLES WITH READ LOCK “ It is a light-weight lock. ● Block updates to non-transactional tables and DDL statements for all tables ● New backup lock will not flush tables Storage engines are not forced to close tables and tables are not expelled from the table cache. ● Only waits for conflicting statements to complete (i.e. DDL and updates to non- transactional tables). It never waits for SELECTs, or UPDATEs to InnoDB tables to complete https://www.percona.com/doc/percona-server/5.6/management/backup_locks.html

XtraBackup Records the log sequence number(LSN) when it starts • Copies all InnoDB files which is internally in inconsistent state • Read transaction log files to create its own change log file • Backup locks • Use LOCK TABLES FOR BACKUP to copy non-InnoDB files o Use Lock BINLOG FOR BACKUP to block operation and gets binary log coordinates o Apply xtrabackup logs to make data files consistent state • PRESENTED BY Brian Koch ⎪ March 2014

Yahoo MySQL Database Architecture ▪ Dual masters single writer setup ▪ Each master carries one or more VIP for Write slave Prod master BCP master BCP slave Prod slave Backup server VIP for Read-only

Current Backup Strategy Full backup on Sunday and incremental backup from Monday to Saturday • The backups are stored in external storage volume • The backup job runs on dedicated slave or prod slave • The binary logs are saved periodically • Dedicated Prod master slave Backup filer Xtrabackup job Copy binary logs PRESENTED BY Brian Koch ⎪ March 2014

Xtrabackup Wrapper § xtrabackup.addl_args: --slave-info § xtrabackup.incremental: --incremental § xtrabackup.backup_dir: /mnt/backup/ § xtrabackup.cron_incremental_day: * § xtrabackup.cron_bkup_ret_hour: 0 § xtrabackup.cron_incremental_hour: 7 § xtrabackup.cron_bkup_ret_min: 0 § xtrabackup.cron_incremental_min: 0 § xtrabackup.cron_bkup_ret_wday: 1 § xtrabackup.cron_incremental_wday: 1-6 § xtrabackup.retain_extra_days: 14 § xtrabackup.crontab: on § xtrabackup.cron_day: * § xtrabackup.email: page-oncall-email § xtrabackup.cron_hour: 7 § xtrabackup.backup_vol_threshold: 80 § xtrabackup.cron_min: 0 § xtrabackup.backup_user: root § xtrabackup.cron_wday: 0 § xtrabackup.backup_password: <path to pw file>

Xtrabackup Options Options Comments Use it when taking backup is from the slave. Record its master's binary log file name and its slave-info position in the file xtrabackup_slave_info It specifies the number of threads the xtrabackup child process should use to back up files parallel concurrently, speed up backup process It instructs xtrabackup to compress backup copies of InnoDB data files. compress Speed up the apply log process use-memory stop slave SQL thread before running FLUSH TABLES WITH READ LOCK and wait to start backup safe-slave- until Slave_open_temp_tables in SHOW STATUS is zero. backup incremental Instructs xtrabackup to take incremental backup instead of fullbackup

Xtrabackup with Network Streaming Target mkdir /home/tmp/backup screen -S backup nc -l 9999 | tar -ixf - -C /home/tmp/backup Source screen -S backup sudo innobackupex --user=root –password=** --slave-info --stream=tar ./ | nc <target> 9999 Target The backups will be in target:/home/tmp/backup sudo innobackupex --apply-log --use-memory=16G --parallel=8 /home/tmp/backup sudo innobackupex --move-back /home/tmp/backup sudo chown -R mysql:mysql /home/y/…/mysql start mysql_server PRESENTED BY Brian Koch ⎪ March 2014

Backup Option: --compress § Saved backup space 2/3 § Take more time to complete backup § Take less time if multiple compress-threads are used Database Full backup Compresse Full bkup Compressed Compress- Server size d size time bkup time thread db1 1.3 TB 360 GB 220 mins 255 mins 1 db2 1.3 TB 360 GB 220 mins 180 mins 8 PRESENTED BY Brian Koch ⎪ March 2014

Database Restore Comparison DB Backup Transfer Decompress Apply Total Space Time size backup to log time saved saved target server Full backup 1.3TB 210 mins 0 15 225 mins mins Full 360GB 75 mins 65 mins 15 155 ~70% ~30% compressed mins mins backup PRESENTED BY Brian Koch ⎪ March 2014

The Issues Could Happen The database that has a lot of partitions could run into • Error number 24 means 'Too many open files’ Added “ ulimit -n 65535“ in the backup script Transaction logs could be small • xtrabackup: error: log block numbers mismatch: xtrabackup: error: expected log block no. 98032273, but got no. 102226569 from the log file. xtrabackup: error: it looks like InnoDB log has wrapped around before xtrabackup could process all records due to either log copying being too slow, or log files being too small. xtrabackup: Error: xtrabackup_copy_logfile() failed. Increase transaction log size or take backup from off-peak time. PRESENTED BY Brian Koch ⎪ March 2014

CAVEATS The backup folder should be dedicated. • If it contains other folders or other files, the incremental backup may not be right. The backup purging time and backup time shall be different. • Information_schema is not backed up. So the table create time stamp could • be lost. PRESENTED BY Brian Koch ⎪ March 2014

CAVEATS --- continued ▪ event master : status is ENABLED slave: status is SLAVESIDE_DISABLED ▪ Master and slave are NOT same Taking backup from master to rebuild slave, event is enabled causes issue

Create event cross all database servers ▪ Login to each server set sql_log_bin=0; create event event_name …. BEGIN IF @@global.read_only=0 THEN call del_test_data(); end if; END Ensures master and slave identical

Backup and Recovery Strategy -- continue

Take Database Backup 0 12 * * 1 root find /backup/dbbk -maxdepth 1 -type d -ctime +14 | xargs rm -rf 0 0 * * 0 root /home/y/bin/innobackupex_y 0 0 * * 1-6 root /home/y/bin/innobackupex_y --incremental PRESENTED BY Brian Koch ⎪ March 2014

Point-Time-Recovery Assume there are compressed backups: full backup: /mnt/backup/saca/2016-04-07_05-05-01 Incremental backup: /mnt/backup/saca/2016-04-08_05-00-00 1. Transfer backups to the restore server 2. Decompress the backups first § sudo innobackupex /home/syuan/backup/2016-04-07_05-05-01 --use-memory=32G --decompress --parallel=16 § sudo innobackupex /home/syuan/backup/2016-04-08_05-00-00 --use-memory=8G --decompress --parallel=8 3. Apply logs § sudo innobackupex --apply-log --redo-only /home/syuan/backup/2016-04-07_05-05-01 --use-memory=32G -- parallel=16 § sudo innobackupex --apply-log --redo-only /home/syuan/backup/2016-04-07_05-05-01 --incremental- dir=/home/syuan/backup/2016-04-08_05-00-00 --use-memory=8G --parallel=8 PRESENTED BY Brian Koch ⎪ March 2014 § sudo innobackupex --apply-log /home/syuan/backup/2016-04-07_05-05-01 --use-memory=32G --parallel=16

Point-Time-Recovery - continued 4. Move the backups into right directory sudo innobackupex --move-back /home/syuan/backup/2016-04-07_05-05-01 5. Start database ● sudo chown -R mysql:mysql /home/y/var/mysql ● yinst start mysql_server 6. Update root password 7. mysqlbinlog -stop-datetime -stop-position PRESENTED BY Brian Koch ⎪ March 2014

Slave Rebuild Get the replication coordinates ● If the backup is taken from the master, check the file xtrabackup_binlog_info in the backup folder If the backup is from slave , please check the file xtrabackup_slave_info ● Set up the replication CHANGE MASTER TO MASTER_HOST='master-host-name.yahoo.com', MASTER_USER='replicant', MASTER_PASSWORD='pass-word', MASTER_LOG_FILE='mysqld-bin.004464', MASTER_LOG_POS=49402644; START SLAVE; SHOW SLAVE STATUS\G PRESENTED BY Brian Koch ⎪ March 2014