Download as PDF, PPTX
!["MySQL has no single unbreakable backup solution [yet]." EffectiveMySQL.com - Performance, Scalability & Business Continuity](https://image.slidesharecdn.com/mysqlbackupandrecoveryessentials-ss-131205161324-phpapp02/75/MySQL-Backup-and-Recovery-Essentials-10-2048.jpg)
![Restore Options mysqldump USAGE $ time mysql –u[user] -p -f < dump1.sql > dump1.out 2>&1 real 14m13.817s user 1m6.960s sys 0m1.516s $ echo $? 0 $ ls -l dump1.out -rw-rw-r-- 1 uid gid 0 2012-04-08 04:07 dump1.out EffectiveMySQL.com - Performance, Scalability & Business Continuity](https://image.slidesharecdn.com/mysqlbackupandrecoveryessentials-ss-131205161324-phpapp02/75/MySQL-Backup-and-Recovery-Essentials-18-2048.jpg)
![Binary Logs Possible with Binary Logs #my.cnf [mysqld] log-bin=mysql-bin expire-logs-days=5 WARNING: If you care about your data, enable binary logging mysql> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.019662 | 104857736 | | mysql-bin.019663 | 104857699 | | mysql-bin.019664 | 104857850 | $ ls -ltr /var/log/mysql | tail -rw-rw---- 1 mysql adm 104857736 2011-09-04 22:00 mysql-bin.019662 -rw-rw---- 1 mysql adm 104857699 2011-09-04 22:08 mysql-bin.019663 EffectiveMySQL.com - Performance, Scalability & Business Continuity](https://image.slidesharecdn.com/mysqlbackupandrecoveryessentials-ss-131205161324-phpapp02/75/MySQL-Backup-and-Recovery-Essentials-37-2048.jpg)
![Compression Utility Comp (s) Dec (s) Saving lzo (-3) pigz (-1) pigz [-6] gzip [-6] bzip2 lzo (-9) lzma xz 21 43 105 232 540 20m 58m 59m 34 33 25 78 175 82 180 160 48% 64% 69% 69% 74% 58% 78% 78% Depends greatly on data types EffectiveMySQL.com - Performance, Scalability & Business Continuity](https://image.slidesharecdn.com/mysqlbackupandrecoveryessentials-ss-131205161324-phpapp02/75/MySQL-Backup-and-Recovery-Essentials-44-2048.jpg)
Uploaded byRonald Bradford
MySQL Backup and Recovery Essentials
The document provides an overview of MySQL backup and recovery strategies, emphasizing the importance of both effective backup options and reliable restoration methods. It discusses various backup tools such as mysqldump, xtrabackup, and file copying, along with their respective pros and cons, and stresses the need for verification and testing at each step of the backup and recovery process. Ultimately, the author highlights that a well-planned backup strategy is essential for business continuity and data integrity.
MySQL Backup and Recovery Essentials
- 1. MySQL Backup & Recovery Essentials RonaldBradford http://ronaldbradford.com Buenos Aires, Argentina 2013-10 EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 2. "No one caresif you can backup, only that you can restore." Adapted from W. Curtis Preston - Backup & Recovery (O'Reilly 2009) EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 3. Agenda Backup Options Tools available RestoreOptions Necessary Nomenclature Business Requirements Slides at http://j.mp/EM-BandR EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 4. ABOUT AUTHOR Ronald Bradford Alltime top MySQL blogger Published Author (4++ books) Oracle ACE Director MySQL community member of the year (2009 & 2013) 24 years of RDBMS experience,14 years with MySQL MySQL Inc (2006-2008) Oracle Corporation (1996-1999) EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 5. Four Step Overview EffectiveMySQL.com -Performance, Scalability & Business Continuity
- 6. Backup Basics 1 Static ConsistentBackup + Master Binary Logs EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 7. Recovery Basics 2 Static Restore + Pointin Time Recovery EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 8. Verification Necessary at EVERYstep 3 Commands complete without error No errors in logs Results match expectations Approximate Precise EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 9. Testing 4 “Testing is abouttrying to break your software, not checking that it works!” Ronald Bradford, circa 2006 EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 10. "MySQL has nosingle unbreakable backup solution [yet]." EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 11. B&R Options EffectiveMySQL.com -Performance, Scalability & Business Continuity
- 12. B&R Options Included Open Source mysqldump XtraBackup OSfilecopy mydumper OS Specific Commercial filesystem snapshot MEB EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 13. EXAMPLE DB SELECT FROM SUM(data_length+index_length)/1024/1024 AStotal_mb, SUM(data_length)/1024/1024 AS data_mb, SUM(index_length)/1024/1024 AS index_mb, COUNT(DISTINCT table_schema) AS schema_cnt, COUNT(*) AS tables, CURDATE() AS today, VERSION() information_schema.tablesG *************************** 1. row *************************** total_mb: 5344.63 data_mb: 4545.49 index_mb: 799.13 TIP: Your daily verification schema_cnt: 7 step should include this tables: 103 today: 2012-04-03 VERSION(): 5.1.61-0ubuntu0.11.10.1-log Available on GitHub with Effective MySQL: Backup and Recovery Book EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 14. Backup Options mysqldump Pros Included withMySQL server ASCII output SQL statement Remote capabilities EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 15. Backup Options mysqldump Cons Single threaded Lockingby default (*) Slow restore for large DBs Single threaded EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 16. Backup Options mysqldump Recommendations Great for5-10GB No locking with InnoDB (*) Essential for recording schema objects EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 17. Backup Options mysqldump USAGE $time mysqldump --all-databases > /mysql/backup/dump1.sql real 1m31.631s user 1m12.533s sys 0m10.893s $ echo $? 0 $ ls -lh /mysql/backup/dump1.sql -rw-rw-r-- 1 uid gid 2.9G 2012-04-03 03:04 /mysql/ backup/dump1.sql TIP: Always time and record long running processes for verification EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 18. Restore Options mysqldump USAGE $time mysql –u[user] -p -f < dump1.sql > dump1.out 2>&1 real 14m13.817s user 1m6.960s sys 0m1.516s $ echo $? 0 $ ls -l dump1.out -rw-rw-r-- 1 uid gid 0 2012-04-08 04:07 dump1.out EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 19. Backup Options mysqldump USAGE $mysqldump --all-databases --no-data > /mysql/backup/schema.sql $ mysqldump --all-databases --no-data --no-create-info --events --routines > /mysql/backup/objects.sql TIP: Include daily dumps of database objects EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 20. Backup Options VERIFICATION $ mysqldump--all-databases --no-data --no-create-info --events --routines > /mysql/backup/objects.sql mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES $ echo $? 2 TIP: Error checking is essential and easy to implement EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 21. Backup Options File Copy Pros Nosoftware needed Consistent EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 22. Backup Options File Copy Cons MySQLunavailable Not consistent (**) Time to warm server caches Restore must match configuration Must backup right files EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 23. Backup Options File Copy Recommendations Greatfor slaves When access disabled EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 24. Backup Options File CopyUSAGE $ sudo service mysql stop $ sudo /etc/init.d/mysqld stop $ $ $ $ # # Ubuntu # RHEL mkdir /mysql/backup/cp1 cp -r /var/lib/mysql /mysql/backup/cp1 echo $? cp /etc/mysql/my.cnf /mysql/backup/cp1 Other directories? $ sudo service mysql start EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 25. Backup Options SNAPSHOT Pros Fastest Database agnostic(*) EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 26. Backup Options SNAPSHOT Cons Must bepre-configured (LVM) Extra Disk I/O Inconsistent (*) FLUSH TABLES duration EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 27. Backup Options SNAPSHOT USAGE $ $ $ $ $ $ sudosu sync ; lvcreate -L1G -s -n dbsnapshot /dev/db/p0 mkdir -p /mnt/dbsnapshot mount -o ro /dev/db/dbsnapshot /mnt/dbsnapshot du -sh /mnt/dbsnapshot ls -al /mnt/dbsnapshot $ mkdir /mysql/backup/snapshot1 $ cp -r /mnt/dbsnapshot/* /mysql/backup/snapshot1 $ sudo su $ mylvmbackup TIP: mylvmbackup does all the hard work http://effectiveMySQL.com/article/configuring-a-new-hard-drive-for-lvm http://effectiveMySQL.com/article/using-mysql-with-lvm http://www.lenzg.net/mylvmbackup/ EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 28. Backup Options XtraBACKUP Pros Non-blocking (InnoDB) OpenSource Supports incremental, compression etc EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 29. Backup Options XtraBACKUP Cons Blocking fornon-InnoDB tables EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 30. Backup Options XtraBACKUP USAGE $time innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=passwd --no-timestamp /mysql/backup/xtrabackup/first real 2m30.667s user 0m21.933s sys 0m14.713s $ echo $? 0 EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 31. restore Options XtraBACKUP USAGE $ $ $ $ $ sudo sudo sudo sudo sudo su– mysql service mysql stop # Ubuntu rm -rf /var/lib/mysql # data directory mkdir -m /var/lib/mysql chown mysql:mysql /var/lib/mysql $ time innobackupex --copy-back /mysql/backup/xtrabackup/first/ $ echo $? EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 32. Backup Options MySQL ENTERPRISEBACKUP (MEB) Pros Non Blocking (InnoDB) Commercial Support Supports incremental, compression etc media management software (MMS) Oracle Secure Backup (OSB) - SBT EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 33. Backup Options MySQL ENTERPRISEBACKUP (MEB) Cons Warm for non-InnoDB Cost EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 34. Backup Options MEB USAGE $sudo su - mysql $ time /opt/meb/bin/mysqlbackup --user=root --password=passwd --backup-dir=/mysql/backup/meb/first backup-and-apply-log real 3m30.879s user 0m17.081s sys 0m14.565s $ echo $? 0 $ du -sh /mysql/backup/meb/first 5.6G /mysql/backup/meb/first $ ls -lh /mysql/backup/meb/first/datadir/ibd* -rw-rw-r-- 1 uid gid 5.4G 2012-04-03 03:25 /mysql/ backup/meb/first/datadir/ibdata1 EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 35. RESTORE Options MEB USAGE $ $ $ $ $ sudo sudo sudo sudo sudo su– mysql service mysql stop # Ubuntu rm -rf /var/lib/mysql # data directory mkdir -m /var/lib/mysql chown mysql:mysql /var/lib/mysql $ time /opt/meb/bin/mysqlbackup --defaults-file=/etc/mysql/my.cnf --backup-dir=/mysql/backup/meb/first --innodb-log-files-in-group=2 copy-back $ echo $? EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 36. Point in Time EffectiveMySQL.com- Performance, Scalability & Business Continuity
- 37. Binary Logs Possible withBinary Logs #my.cnf [mysqld] log-bin=mysql-bin expire-logs-days=5 WARNING: If you care about your data, enable binary logging mysql> SHOW BINARY LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.019662 | 104857736 | | mysql-bin.019663 | 104857699 | | mysql-bin.019664 | 104857850 | $ ls -ltr /var/log/mysql | tail -rw-rw---- 1 mysql adm 104857736 2011-09-04 22:00 mysql-bin.019662 -rw-rw---- 1 mysql adm 104857699 2011-09-04 22:08 mysql-bin.019663 EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 38. Backup Options cp rsync Slave --log-slave-updates DRBD- Disk Replicated Block Device mysqlbinlog --read-from-remote-server (New in 5.6) EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 39. Backup needs Static Backup Uselesswithout Binary Log position mysql> SHOW MASTER STATUSG File: mysql-bin.020616 Position: 63395562 Binlog_Do_DB: Binlog_Ignore_DB: WARNING: Can work on slave and provide the wrong information EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 40. Backup Needs $ mysqldump--master-data (or --dump-slave) CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER ='repl',MASTER_PASSWORD='******', MASTER_LOG_FILE= 'mysql-bin.000146', MASTER_LOG_POS=810715371; Xtrabackup $ cat xtrabackup_binlog_info mysql-bin.000001 37522 MEB $ grep binlog meta/backup_variables.txt binlog_position=mysql-bin.000017:5555 mydumper $ cat export-20120407-230027/metadata Log: mysql-bin.000017 Pos: 8328 EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 41. Restore Options mysqlbinlog $ mysqlbinlog/path/to/mysql-bin.000146 --start-position=810715371 | mysql -uroot -p $ mysqlbinlog /path/to/mysql-bin.000147 /path/to/mysql-bin.00148 ... etc | mysql -uroot -p MySQL replication EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 42. Advanced Features EffectiveMySQL.com - Performance,Scalability & Business Continuity
- 43. Advanced Compression Incremental Remote Parallel Partial For another presentation EffectiveMySQL.com- Performance, Scalability & Business Continuity
- 44. Compression Utility Comp (s) Dec (s) Saving lzo(-3) pigz (-1) pigz [-6] gzip [-6] bzip2 lzo (-9) lzma xz 21 43 105 232 540 20m 58m 59m 34 33 25 78 175 82 180 160 48% 64% 69% 69% 74% 58% 78% 78% Depends greatly on data types EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 45. Terminology MTTD - MeanTime To Detect MTTR - Mean Time to Recover RPO - Recovery Point Objective RDO - Recovery Data Objective SLA - Service Level Agreement Determining business priorities is important for any strategy EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 46. Conclusion EffectiveMySQL.com - Performance,Scalability & Business Continuity
- 47. CONCLUsiON This is anintroduction Advance features are important Best option depends Replication is important in your strategy Test, Test, Test. - “Chaos Monkey” Slides at http://j.mp/EM-BandR EffectiveMySQL.com - Performance, Scalability & Business Continuity
- 48. PRESENTATIONS More presentations at http://ronaldbradford.com/mysql-presentations/ http://effectivemysql.com/presentation/ EffectiveMySQL.com- Performance, Scalability & Business Continuity
- 49. 220 pages dedicatedto B&R http://j.mp/EM-book2
- 50.