• percona_xtrabackup


    原理

    percona xtrabackup备份过程主要分为以下几点:

    1、xtrabackup在启动时会记住LSN(log sequence number),然后复制所有的数据文件

    2、xtrabackup会运行一个后台进程,用于监控事务日志(redo.log),并从事务日志中复制最新的修改

    3、准备过程(prepare),xtrabackup使用之前复制的事务日志,对各个数据文件执行灾难恢复

    4、执行flush tables with read lock来阻止新的写入进来,并把MyISAM表数据刷写到硬盘上,之后复制MyISAM数据文件,最后释放锁。

     其中1-3步骤是xtrabackup来实现,第4步由innobackupex来实现。

    备份MyISAM和Innodb表最终会处于一致,在prepare过程结束后,Innodb表数据已经前滚到整个备份数据结束的点,而不是回滚到xtrabackuo刚开始的点。

    通过复制数据文件、日志文件和提交日志到数据文件(前滚)实现各种复合备份方式。

    innobackupex备份对MyISAM表之前要对全库进行加READ LOCK,阻塞写操作,对Innodb表备份不会阻塞读写。

    备份演练

    1、创建备份目录
    mkdir -p /data1/backup
    
    2、拷贝全量数据
    innobackupex --defaults-file=/data1/mysql6011/my6011.cnf --user=root --password='xxx' --tmpdir=/data1/tmp/ --socket=/tmp/mysql6011.sock --no-timestamp --slave-info /data1/backup/mysql6011 > innobackupex.log  2>&1
    
    注意点:
    a、设置tmp为目录较大的位置
    b、设置no-timestamp,否则以时间作为前缀
    c、设置备份的目录,/data1/backup先提前创建好,然后再以mysql6011作为备份的目录名称
    d、配置文件中character_set_client=utf8,导致错误
    
    3、拷贝配置文件到另一台机器上

    4、回放redolog(apply-log)

    如果给主库搭建从库:

    http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html 

    binlog点的位置为xtrabackup_binlog_info文件中

    apply-log产生的日志:

    [root@eos177 data1]# innobackupex -defaults-file=/data1/mysql6011/my6011.cnf --apply-log /data1/mysql6011/
    
    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona Inc 2009-2011.  All Rights Reserved.
    
    This software is published under
    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
    
    IMPORTANT: Please check that the apply-log run completes successfully.
               At the end of a successful apply-log run innobackupex
               prints "completed OK!".
    
    
    
    140220 10:37:43  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/data1/mysql6011/my6011.cnf" --prepare --target-dir=/data1/mysql6011
    
    xtrabackup_55 version 1.6.3 for Percona Server 5.5.9 Linux (x86_64) (revision id: 292)
    xtrabackup: cd to /data1/mysql6011
    xtrabackup: This target seems to be not prepared yet.
    xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(37043716524)
    xtrabackup: Temporary instance for recovery is set as followings.
    xtrabackup:   innodb_data_home_dir = ./
    xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 1
    xtrabackup:   innodb_log_file_size = 2097152
    140220 10:37:43 InnoDB: Using Linux native AIO
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    140220 10:37:43 InnoDB: The InnoDB memory heap is disabled
    140220 10:37:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    140220 10:37:43 InnoDB: Compressed tables use zlib 1.2.3
    140220 10:37:43 InnoDB: Using Linux native AIO
    140220 10:37:43 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
    140220 10:37:43 InnoDB: Initializing buffer pool, size = 100.0M
    140220 10:37:43 InnoDB: Completed initialization of buffer pool
    140220 10:37:43 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 37043716524
    140220 10:37:43  InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files...
    InnoDB: Doing recovery: scanned up to log sequence number 37043837591 (6 %)
    140220 10:37:43  InnoDB: Starting an apply batch of log records to the database...
    InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
    InnoDB: Apply batch completed
    InnoDB: Last MySQL binlog file position 0 218163482, file name ./mysql-bin.000038
    140220 10:37:44  InnoDB: Waiting for the background threads to start
    140220 10:37:45 Percona XtraDB (http://www.percona.com) 1.1.5-20.0 started; log sequence number 37043837591
    
    [notice (again)]
      If you use binary log and don't use any hack of group commit,
      the binary log position seems to be:
    InnoDB: Last MySQL binlog file position 0 218163482, file name ./mysql-bin.000038
    
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    140220 10:37:45  InnoDB: Starting shutdown...
    140220 10:37:49  InnoDB: Shutdown completed; log sequence number 37043847066
    
    140220 10:37:49  innobackupex: Restarting xtrabackup with command: xtrabackup_55  --defaults-file="/data1/mysql6011/my6011.cnf" --prepare --target-dir=/data1/mysql6011
    for creating ib_logfile*
    
    xtrabackup_55 version 1.6.3 for Percona Server 5.5.9 Linux (x86_64) (revision id: 292)
    xtrabackup: cd to /data1/mysql6011
    xtrabackup: This target seems to be already prepared.
    xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
    xtrabackup: Temporary instance for recovery is set as followings.
    xtrabackup:   innodb_data_home_dir = ./
    xtrabackup:   innodb_data_file_path = ibdata1:100M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 3
    xtrabackup:   innodb_log_file_size = 1363148800
    140220 10:37:49 InnoDB: Using Linux native AIO
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    140220 10:37:49 InnoDB: The InnoDB memory heap is disabled
    140220 10:37:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    140220 10:37:49 InnoDB: Compressed tables use zlib 1.2.3
    140220 10:37:49 InnoDB: Using Linux native AIO
    140220 10:37:49 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
    140220 10:37:49 InnoDB: Initializing buffer pool, size = 100.0M
    140220 10:37:49 InnoDB: Completed initialization of buffer pool
    140220 10:37:49  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile0 size to 1300 MB
    InnoDB: Database physically writes the file full: wait...
    InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300
    140220 10:37:52  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile1 size to 1300 MB
    InnoDB: Database physically writes the file full: wait...
    InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300
    140220 10:37:54  InnoDB: Log file ./ib_logfile2 did not exist: new to be created
    InnoDB: Setting log file ./ib_logfile2 size to 1300 MB
    InnoDB: Database physically writes the file full: wait...
    InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300
    140220 10:37:57 InnoDB: highest supported file format is Barracuda.
    InnoDB: The log sequence number in ibdata files does not match
    InnoDB: the log sequence number in the ib_logfiles!
    140220 10:37:57  InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files...
    InnoDB: Last MySQL binlog file position 0 218163482, file name ./mysql-bin.000038
    140220 10:37:57  InnoDB: Waiting for the background threads to start
    140220 10:37:58 Percona XtraDB (http://www.percona.com) 1.1.5-20.0 started; log sequence number 37043847180
    
    [notice (again)]
      If you use binary log and don't use any hack of group commit,
      the binary log position seems to be:
    InnoDB: Last MySQL binlog file position 0 218163482, file name ./mysql-bin.000038
    
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    140220 10:37:58  InnoDB: Starting shutdown...
    140220 10:38:02  InnoDB: Shutdown completed; log sequence number 37043853783
    140220 10:38:02  innobackupex: completed OK!

    copy-back

    innobackupex: Error: Original data directory '/data1/mysql6011' does not exist! at /etc/dbCluster/innobackupex line 361.
    [root@eos177 data1]# innobackupex -defaults-file=/data1/backup/mysql6011/my6011.cnf /data1/backup/mysql6011 --copy-back
    
    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona Inc 2009-2011.  All Rights Reserved.
    
    This software is published under
    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
    
    IMPORTANT: Please check that the copy-back run completes successfully.
               At the end of a successful copy-back run innobackupex
               prints "completed OK!".
    
    innobackupex: Starting to copy MyISAM tables, indexes,
    innobackupex: .MRG, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .opt, and .frm files
    innobackupex: in '/data1/backup/mysql6011'
    innobackupex: back to original data directory '/data1/mysql6011'
    innobackupex: Copying directory '/data1/backup/mysql6011/performance_schema'
    innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_checkpoints'
    innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_slave_info'
    innobackupex: Copying directory '/data1/backup/mysql6011/mysql'
    innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_binary'
    innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_binlog_info'
    innobackupex: Copying directory '/data1/backup/mysql6011/zjmdmm'
    innobackupex: Copying file '/data1/backup/mysql6011/my6011.cnf'
    innobackupex: Copying directory '/data1/backup/mysql6011/elink'
    innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_binlog_pos_innodb'
    innobackupex: Copying directory '/data1/backup/mysql6011/test'
    
    innobackupex: Starting to copy InnoDB tables and indexes
    innobackupex: in '/data1/backup/mysql6011'
    innobackupex: back to original InnoDB data directory '/data1/mysql6011'
    innobackupex: Copying file '/data1/backup/mysql6011/ibdata1'
    
    innobackupex: Starting to copy InnoDB log files
    innobackupex: in '/data1/backup/mysql6011'
    innobackupex: back to original InnoDB log directory '/data1/mysql6011'
    innobackupex: Copying file '/data1/backup/mysql6011/ib_logfile0'
    innobackupex: Copying file '/data1/backup/mysql6011/ib_logfile2'
    innobackupex: Copying file '/data1/backup/mysql6011/ib_logfile1'
    innobackupex: Finished copying back files.
    
    140220 10:41:29  innobackupex: completed OK!

    使用xtrabackup进行备份数据的压缩和加密

    http://noops.me/?p=159

  • 相关阅读:
    python连接数据库异步存储
    pythonscrapy之MySQL同步存储
    头有点大
    scrapy反爬虫
    《猫抓老鼠》
    Linux下系统监控工具nmon
    探索式测试学习资料
    开始探索式测试学习之前的思考
    Software Quality Characteristics 软件质量特性
    自动化测试整理 STAF/STAX & Robot Framework
  • 原文地址:https://www.cnblogs.com/gsblog/p/3553155.html
Copyright © 2020-2023  润新知