Online Backups With Percona XtraBackup

Percona Xtrabackup提供:
快速可靠的备份 备份期间不间断的事务处理
通过更好的压缩节省磁盘空间和网络带宽 自动备份验证
由于恢复时间更快,运行时间更长
本次网络研讨会将讨论Percona Xtrabackup的各种不同功能,包括:
完整备份和增量备份
备份的压缩、流媒体和加密 备份到云端(swift)。
Percona Xtradb集群/Galera集群。 Percona服务器特定功能
MySQL5.7支持 使用Percona Xtrabackup的工具
*限制

展开查看详情

1. 1 / 46 Online Backups with Percona Webinar - May 2017

2. 2 / 46 Dimitri Vanoverbeke @dim0 MySQL Solution Engineer

3. 3 / 46 Agenda Why do I need backups again I have replication What is Percona XtraBackup Backup Process Restore Process Incremental Compression, Streaming, Encryption Cloud Backups - α Percona Server Speci c Features

4. 4 / 46 Replication is not a DR solution! Make sure you understand what your requirements are: RTO RPO Discuss this with your business owners, what is acceptable?

5. 5 / 46 Having backups is not enough! TEST THEM! Populate test systems using your backups Have automation in place to perform the backups and restores Document them!

6. 6 / 46 What is

7. 7 / 46 Backup challenges that exist MySQL is multi-engine MySQL can be quite busy Do we need to shut it down? How much load will we generate? How quickly can we restore?

8. 8 / 46 What is Backups for:

9. 9 / 46 What is Backups for:

10. 10 / 46 What is https://www.percona.com/software/mysql-database/percona-xtrabackup Online (Non Blocking) InnoDB Backups Physical Data les Copied

11. 11 / 46 What is https://www.percona.com/software/mysql-database/percona-xtrabackup Online (Non Blocking) InnoDB Backups Less Impact on Production Physical Data les Copied Faster Restore Time Than Logical Backups

12. 12 / 46 What is https://www.percona.com/software/mysql-database/percona-xtrabackup Online (Non Blocking) InnoDB Backups Less Impact on Production Physical Data les Copied Faster Restore Time Than Logical Backups Open Source Blocking Support MyISAM... Incremental/Delta Compression/Encryption Cloud Backup

13. 13 / 46 (Data) Backup Process

14. 14 / 46 (Data) Backup Process - 1. Backup Backup .ibd les while in use /var/lib/mysql/ibdata1 /var/lib/mysql/imdb/aka_name.ibd /var/lib/mysql/imdb/aka_title.ibd ...

15. 15 / 46 (Data) Backup Process - 1. Backup Backup .ibd les while in use /var/lib/mysql/ibdata1 /var/lib/mysql/imdb/aka_name.ibd /var/lib/mysql/imdb/aka_title.ibd ... track changes through trx logs /var/lib/mysql/ib_log le0 /var/lib/mysql/ib_log le1

16. 16 / 46 (Data) Backup Process - 2. Prepare Apply changes from trx logs onto backupped .ibd les.

17. 17 / 46 (Data) Backup Process - 1. Backup # xtrabackup --backup --target-dir=/data/backups/ 161001 22:16:49 Connecting to MySQL server host: localhost, user: root, password: not Using server version 5.7.14-8-log xtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id xtrabackup: uses posix_fadvise(). ... InnoDB: Number of pools: 1 161001 22:16:49 >> log scanned up to (5147619414) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0 161001 22:16:50 [01] Copying ./ibdata1 to /data/backups/ibdata1 161001 22:16:50 [01] Copying ./mysql/plugin.ibd to /data/backups/mysql/plugin.ibd 161001 22:16:50 [01] Copying ./mysql/servers.ibd to /data/backups/mysql/servers.ibd 161001 22:16:50 [01] Copying ./mysql/help_topic.ibd to /data/backups/mysql/help_topic.i ... 161001 22:17:13 [01] Copying ./imdb/users.ibd to /data/backups/imdb/users.ibd 161001 22:17:13 >> log scanned up to (5147619414) 161001 22:17:14 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 161001 22:17:14 Executing FLUSH TABLES WITH READ LOCK...

18. 18 / 46 (Data) Backup Process - 1. Backup (2) 161001 22:17:14 Starting to backup non-InnoDB tables and les 161001 22:17:14 [01] Copying ./mysql/db.opt to /data/backups/mysql/db.opt 161001 22:17:14 [01] Copying ./mysql/db.frm to /data/backups/mysql/db.frm 161001 22:17:14 [01] Copying ./mysql/db.MYI to /data/backups/mysql/db.MYI 161001 22:17:14 [01] Copying ./mysql/db.MYD to /data/backups/mysql/db.MYD 161001 22:17:17 [01] Copying ./imdb/users.frm to /data/backups/imdb/users.frm 161001 22:17:17 Finished backing up non-InnoDB tables and les 161001 22:17:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '5147619454' xtrabackup: Stopping log copying thread. .161001 22:17:17 >> log scanned up to (5147619463) 161001 22:17:17 Executing UNLOCK TABLES 161001 22:17:17 All tables unlocked 161001 22:17:17 [00] Copying ib_buffer_pool to /data/backups/ib_buffer_pool 161001 22:17:17 [00] ...done 161001 22:17:17 Backup created in directory '/data/backups/' 161001 22:17:17 [00] Writing backup-my.cnf 161001 22:17:17 [00] ...done 161001 22:17:17 [00] Writing xtrabackup_info 161001 22:17:17 [00] ...done xtrabackup: Transaction log of lsn (5147619405) to (5147619463) was copied. 161001 22:17:18 completed OK!

19. 19 / 46 (Data) Backup Process - 2. Prepare (1) # xtrabackup --prepare --target-dir=/data/backups/ xtrabackup version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58 xtrabackup: cd to /data/backups xtrabackup: This target seems to be not prepared yet. ... xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) ... InnoDB: Log scan progressed past the checkpoint lsn 5147619405 InnoDB: Doing recovery: scanned up to log sequence number 5147619463 (0%) InnoDB: Doing recovery: scanned up to log sequence number 5147619463 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog le position 333, le name perconaserver-bin.000007 ... InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number 5147619463 InnoDB: xtrabackup: Last MySQL binlog le position 333, le name perconaserver-bin.00 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 5147619482

20. 20 / 46 (Data) Backup Process - 2. Prepare (2) ... InnoDB: Setting log le ./ib_log le101 size to 64 MB InnoDB: Setting log le ./ib_log le1 size to 64 MB InnoDB: Renaming log le ./ib_log le101 to ./ib_log le0 InnoDB: New log les created, LSN=5147619482 InnoDB: Highest supported le format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 5147619852 InnoDB: Doing recovery: scanned up to log sequence number 5147619861 (0%) InnoDB: Doing recovery: scanned up to log sequence number 5147619861 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog le position 333, le name perconaserver-bin ... InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number 5147619861 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 5147619880 161001 22:30:05 completed OK!

21. 21 / 46 Restore Process

22. 22 / 46 Restore Process After preparation, the backup directory == data directory

23. 23 / 46 Restore Process After preparation, the backup directory == data directory # cp -R /data/backup/ /var/lib/mysql # chown -R mysql:mysql /var/lib/mysql # systemctl start mysqld

24. 24 / 46 Incremental Backups

25. 25 / 46 Incremental Backups

26. 26 / 46 Incremental Backups Only Copy InnoDB Pages That Changed (still copies all non InnoDB Tables)

27. 27 / 46 Incremental Backups Only Copy InnoDB Pages That Changed (still copies all non InnoDB Tables) Pro: Smaller backup size Faster To Backup Incremental & Delta

28. 28 / 46 Incremental Backups Only Copy InnoDB Pages That Changed (still copies all non InnoDB Tables) Pro: Con: Smaller backup size Slower to restore than full Faster To Backup On , all data is read Incremental & Delta

29. 29 / 46 Incremental Backups - Process