• ADADADADAD

    MySQL中如何进行XtraBackup实战[ mysql数据库 ]

    mysql数据库 时间:2024-11-26 22:07:36

    作者:文/会员上传

    简介:

    1.使用xtrabckup对mysql进行全备mysql1@hdh-votst2:/mvnobss/users/mysql1>xtrabackup --defaults-file=mysql/bin/my.cnf --target-dir=bak/ --backupxtrabackup version 2

    以下为本文的正文内容,内容仅供参考!本站为公益性网站,复制本文以及下载DOC文档全部免费。

    1.使用xtrabckup对mysql进行全备
    mysql1@hdh-votst2:/mvnobss/users/mysql1>xtrabackup --defaults-file=mysql/bin/my.cnf --target-dir=bak/ --backup
    xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /mvnobss/users/mysql1/mysql/mydata
    xtrabackup: open files limit requested 10240, set to 1048576
    xtrabackup: using the following InnoDB configuration:
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 2
    xtrabackup: innodb_log_file_size = 268435456
    >> log scanned up to (63557721)
    xtrabackup: Generating a list of tablespaces
    [01] Copying ./ibdata1 to /mvnobss/users/mysql1/bak/ibdata1
    >> log scanned up to (63557721)
    [01] ...done
    [01] Copying ./test/t1.ibd to /mvnobss/users/mysql1/bak/test/t1.ibd
    [01] ...done
    [01] Copying ./test/t2.ibd to /mvnobss/users/mysql1/bak/test/t2.ibd
    [01] ...done
    [01] Copying ./test/t3.ibd to /mvnobss/users/mysql1/bak/test/t3.ibd
    [01] ...done
    [01] Copying ./mysql/innodb_index_stats.ibd to /mvnobss/users/mysql1/bak/mysql/innodb_index_stats.ibd
    [01] ...done
    [01] Copying ./mysql/slave_relay_log_info.ibd to /mvnobss/users/mysql1/bak/mysql/slave_relay_log_info.ibd
    [01] ...done
    [01] Copying ./mysql/slave_master_info.ibd to /mvnobss/users/mysql1/bak/mysql/slave_master_info.ibd
    [01] ...done
    [01] Copying ./mysql/innodb_table_stats.ibd to /mvnobss/users/mysql1/bak/mysql/innodb_table_stats.ibd
    [01] ...done
    [01] Copying ./mysql/slave_worker_info.ibd to /mvnobss/users/mysql1/bak/mysql/slave_worker_info.ibd
    [01] ...done
    >> log scanned up to (63557721)
    xtrabackup: The latest check point (for incremental): '63557721'
    xtrabackup: Stopping log copying thread.
    .>> log scanned up to (63557721)

    xtrabackup: Transaction log of lsn (63557721) to (63557721) was copied.



    2.使用innobackupex对mysql进行全备
    mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --user=root --password=123 bak/

    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

    This software is published under
    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

    Get the latest version of Percona XtraBackup, documentation, and help resources:
    http://www.percona.com/xb/p

    160120 15:45:52 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=mysql/bin/my.cnf;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
    160120 15:45:52 innobackupex: Connected to MySQL server
    160120 15:45:52 innobackupex: Executing a version check against the server...
    160120 15:45:54 innobackupex: Done.
    160120 15:45:54 innobackupex: Starting the backup operation

    IMPORTANT: Please check that the backup run completes successfully.
    At the end of a successful backup run innobackupex
    prints "completed OK!".

    innobackupex: Using server version 5.6.21-70.1-log

    innobackupex: Created backup directory /mvnobss/users/mysql1/bak/2016-01-20_15-45-54

    160120 15:45:54 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="mysql/bin/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/mvnobss/users/mysql1/bak/2016-01-20_15-45-54 --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp'
    innobackupex: Waiting for ibbackup (pid=21045) to suspend
    innobackupex: Suspend file '/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/xtrabackup_suspended_2'

    xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /mvnobss/users/mysql1/mysql/mydata
    xtrabackup: open files limit requested 10240, set to 1048576
    xtrabackup: using the following InnoDB configuration:
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 2
    xtrabackup: innodb_log_file_size = 268435456
    >> log scanned up to (63557721)
    xtrabackup: Generating a list of tablespaces
    [01] Copying ./ibdata1 to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/ibdata1
    >> log scanned up to (63557721)
    [01] ...done
    [01] Copying ./test/t1.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/test/t1.ibd
    [01] ...done
    [01] Copying ./test/t2.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/test/t2.ibd
    [01] ...done
    [01] Copying ./test/t3.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/test/t3.ibd
    [01] ...done
    [01] Copying ./mysql/innodb_index_stats.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/innodb_index_stats.ibd
    [01] ...done
    [01] Copying ./mysql/slave_relay_log_info.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/slave_relay_log_info.ibd
    [01] ...done
    [01] Copying ./mysql/slave_master_info.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/slave_master_info.ibd
    [01] ...done
    [01] Copying ./mysql/innodb_table_stats.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/innodb_table_stats.ibd
    [01] ...done
    [01] Copying ./mysql/slave_worker_info.ibd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54/mysql/slave_worker_info.ibd
    [01] ...done
    >> log scanned up to (63557721)
    xtrabackup: Creating suspend file '/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/xtrabackup_suspended_2' with pid '21045'

    160120 15:45:57 innobackupex: Continuing after ibbackup has suspended
    160120 15:45:57 innobackupex: Executing LOCK TABLES FOR BACKUP...
    160120 15:45:57 innobackupex: Backup tables lock acquired

    160120 15:45:57 innobackupex: Starting to backup non-InnoDB tables and files
    innobackupex: in subdirectories of '/mvnobss/users/mysql1/mysql/mydata/'
    innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/db.opt'
    innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t3.frm'
    innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t1.frm'
    innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t2.frm'
    innobackupex: Backing up files '/mvnobss/users/mysql1/mysql/mydata//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
    >> log scanned up to (63557721)
    innobackupex: Backing up files '/mvnobss/users/mysql1/mysql/mydata//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
    >> log scanned up to (63557721)
    160120 15:45:58 innobackupex: Finished backing up non-InnoDB tables and files

    160120 15:45:58 innobackupex: Executing LOCK BINLOG FOR BACKUP...
    160120 15:45:58 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    160120 15:45:58 innobackupex: Waiting for log copying to finish

    xtrabackup: The latest check point (for incremental): '63557721'
    xtrabackup: Stopping log copying thread.
    .>> log scanned up to (63557721)

    xtrabackup: Creating suspend file '/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/xtrabackup_log_copied' with pid '21045'
    xtrabackup: Transaction log of lsn (63557721) to (63557721) was copied.
    160120 15:45:59 innobackupex: Executing UNLOCK BINLOG
    160120 15:45:59 innobackupex: Executing UNLOCK TABLES
    160120 15:45:59 innobackupex: All tables unlocked

    innobackupex: Backup created in directory '/mvnobss/users/mysql1/bak/2016-01-20_15-45-54'
    innobackupex: MySQL binlog position: filename 'mysql-bin-yt-votst2-3306.000009', position 3396
    160120 15:45:59 innobackupex: Connection to database server closed
    160120 15:45:59 innobackupex: completed OK!



    3.使用innobackupex对mysql进行增量备份
    --创建测试数据
    mysql> use test
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed
    mysql> create table t4(id int,name varchar(10));
    Query OK, 0 rows affected (0.02 sec)

    mysql> insert into t4 values(1,'tl'),(2,'lj'),(3,'lx');
    Query OK, 3 rows affected (0.00 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from t4;
    +------+------+
    | id | name |
    +------+------+
    | 1 | tl |
    | 2 | lj |
    | 3 | lx |
    +------+------+
    3 rows in set (0.00 sec)


    mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --user=root --password=123 \
    > --incremental --incremental-basedir=bak/2016-01-20_15-45-54/ bak/incr/

    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

    This software is published under
    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

    Get the latest version of Percona XtraBackup, documentation, and help resources:
    http://www.percona.com/xb/p

    160120 15:57:54 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=mysql/bin/my.cnf;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
    160120 15:57:54 innobackupex: Connected to MySQL server
    160120 15:57:54 innobackupex: Executing a version check against the server...
    160120 15:57:54 innobackupex: Done.
    160120 15:57:54 innobackupex: Starting the backup operation

    IMPORTANT: Please check that the backup run completes successfully.
    At the end of a successful backup run innobackupex
    prints "completed OK!".

    innobackupex: Using server version 5.6.21-70.1-log

    innobackupex: Created backup directory /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54

    160120 15:57:54 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="mysql/bin/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54 --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='bak/2016-01-20_15-45-54/'
    innobackupex: Waiting for ibbackup (pid=21280) to suspend
    innobackupex: Suspend file '/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/xtrabackup_suspended_2'

    xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
    incremental backup from 63557721 is enabled.
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /mvnobss/users/mysql1/mysql/mydata
    xtrabackup: open files limit requested 10240, set to 1048576
    xtrabackup: using the following InnoDB configuration:
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 2
    xtrabackup: innodb_log_file_size = 268435456
    >> log scanned up to (63564362)
    xtrabackup: Generating a list of tablespaces
    xtrabackup: using the full scan for incremental backup
    [01] Copying ./ibdata1 to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/ibdata1.delta
    [01] ...done
    [01] Copying ./test/t1.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/test/t1.ibd.delta
    [01] ...done
    >> log scanned up to (63564362)
    [01] Copying ./test/t4.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/test/t4.ibd.delta
    [01] ...done
    [01] Copying ./test/t2.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/test/t2.ibd.delta
    [01] ...done
    [01] Copying ./test/t3.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/test/t3.ibd.delta
    [01] ...done
    [01] Copying ./mysql/innodb_index_stats.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/innodb_index_stats.ibd.delta
    [01] ...done
    [01] Copying ./mysql/slave_relay_log_info.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/slave_relay_log_info.ibd.delta
    [01] ...done
    [01] Copying ./mysql/slave_master_info.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/slave_master_info.ibd.delta
    [01] ...done
    [01] Copying ./mysql/innodb_table_stats.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/innodb_table_stats.ibd.delta
    [01] ...done
    [01] Copying ./mysql/slave_worker_info.ibd to /mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/mysql/slave_worker_info.ibd.delta
    [01] ...done
    >> log scanned up to (63564362)
    xtrabackup: Creating suspend file '/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/xtrabackup_suspended_2' with pid '21280'

    160120 15:57:57 innobackupex: Continuing after ibbackup has suspended
    160120 15:57:57 innobackupex: Executing LOCK TABLES FOR BACKUP...
    160120 15:57:57 innobackupex: Backup tables lock acquired

    160120 15:57:57 innobackupex: Starting to backup non-InnoDB tables and files
    innobackupex: in subdirectories of '/mvnobss/users/mysql1/mysql/mydata/'
    innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/db.opt'
    innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t4.frm'
    innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t3.frm'
    innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t1.frm'
    innobackupex: Backing up file '/mvnobss/users/mysql1/mysql/mydata//test/t2.frm'
    innobackupex: Backing up files '/mvnobss/users/mysql1/mysql/mydata//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
    >> log scanned up to (63564362)
    innobackupex: Backing up files '/mvnobss/users/mysql1/mysql/mydata//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
    >> log scanned up to (63564362)
    160120 15:57:58 innobackupex: Finished backing up non-InnoDB tables and files

    160120 15:57:58 innobackupex: Executing LOCK BINLOG FOR BACKUP...
    160120 15:57:58 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    160120 15:57:58 innobackupex: Waiting for log copying to finish

    xtrabackup: The latest check point (for incremental): '63564362'
    xtrabackup: Stopping log copying thread.
    .>> log scanned up to (63564362)

    xtrabackup: Creating suspend file '/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54/xtrabackup_log_copied' with pid '21280'
    xtrabackup: Transaction log of lsn (63564362) to (63564362) was copied.
    160120 15:57:59 innobackupex: Executing UNLOCK BINLOG
    160120 15:57:59 innobackupex: Executing UNLOCK TABLES
    160120 15:57:59 innobackupex: All tables unlocked

    innobackupex: Backup created in directory '/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54'
    innobackupex: MySQL binlog position: filename 'mysql-bin-yt-votst2-3306.000009', position 3720
    160120 15:57:59 innobackupex: Connection to database server closed
    160120 15:57:59 innobackupex: completed OK!



    4.删除数据库,模拟故障
    mysql1@hdh-votst2:/mvnobss/users/mysql1/mysql/mydata>pwd
    /mvnobss/users/mysql1/mysql/mydata
    mysql1@hdh-votst2:/mvnobss/users/mysql1/mysql/mydata>rm -rf *

    mysql1@hdh-votst2:/mvnobss/users/mysql1/mysql/mydata>mysql -uroot -p123
    Warning: Using a password on the command line interface can be insecure.
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/mvnobss/users/mysql1/mysql/mydata/mysqld-yt-votst2-3306.sock' (2)

    5.恢复备份
    --恢复全备(注意redo-only这个参数,因为还有其他增量备份要处理,所以这个参数需要加上)
    mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --apply-log --redo-only bak/2016-01-20_15-45-54/

    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

    This software is published under
    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

    Get the latest version of Percona XtraBackup, documentation, and help resources:
    http://www.percona.com/xb/p

    160120 16:52:15 innobackupex: Starting the apply-log operation

    IMPORTANT: Please check that the apply-log run completes successfully.
    At the end of a successful apply-log run innobackupex
    prints "completed OK!".


    160120 16:52:15 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mvnobss/users/mysql1/bak/2016-01-20_15-45-54 --apply-log-only

    xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
    xtrabackup: cd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54
    xtrabackup: This target seems to be not prepared yet.
    xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(63557721)
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 1
    xtrabackup: innodb_log_file_size = 2097152
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 1
    xtrabackup: innodb_log_file_size = 2097152
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: Using atomics to ref count buffer pool pages
    InnoDB: The InnoDB memory heap is disabled
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Memory barrier is not used
    InnoDB: Compressed tables use zlib 1.2.3
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, size = 100.0M
    InnoDB: Completed initialization of buffer pool
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: The log sequence numbers 57998804 and 57998804 in ibdata files do not match the log sequence number 63557721 in the ib_logfiles!
    InnoDB: Database was not shutdown normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files...
    InnoDB: Restoring possible half-written data pages
    InnoDB: from the doublewrite buffer...
    InnoDB: Last MySQL binlog file position 0 1865, file name mysql-bin-yt-votst2-3306.000009

    [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 1865, file name mysql-bin-yt-votst2-3306.000009

    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 63557721
    160120 16:52:16 innobackupex: completed OK!


    --恢复增量备份(注意这里incremental-dir参数后面必须是绝对路径,否则恢复报错)
    mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --apply-log --incremental-dir=/mvnobss/users/mysql1/bak/incr/2016-01-20_15-57-54 bak/2016-01-20_15-45-54/


    --再次执行一次恢复操作(为了创建innodb的专用日志文件)
    mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --apply-log bak/2016-01-20_15-45-54/

    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

    This software is published under
    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

    Get the latest version of Percona XtraBackup, documentation, and help resources:
    http://www.percona.com/xb/p

    160120 17:03:55 innobackupex: Starting the apply-log operation

    IMPORTANT: Please check that the apply-log run completes successfully.
    At the end of a successful apply-log run innobackupex
    prints "completed OK!".


    160120 17:03:55 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mvnobss/users/mysql1/bak/2016-01-20_15-45-54

    xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
    xtrabackup: cd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54
    xtrabackup: This target seems to be already prepared.
    xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 2
    xtrabackup: innodb_log_file_size = 268435456
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 2
    xtrabackup: innodb_log_file_size = 268435456
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: Using atomics to ref count buffer pool pages
    InnoDB: The InnoDB memory heap is disabled
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Memory barrier is not used
    InnoDB: Compressed tables use zlib 1.2.3
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, size = 100.0M
    InnoDB: Completed initialization of buffer pool
    InnoDB: Setting log file ./ib_logfile101 size to 256 MB
    InnoDB: Progress in MB: 100 200
    InnoDB: Setting log file ./ib_logfile1 size to 256 MB
    InnoDB: Progress in MB: 100 200
    InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
    InnoDB: New log files created, LSN=63565599
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: 128 rollback segment(s) are active.
    InnoDB: Waiting for purge to start
    InnoDB: 5.6.22 started; log sequence number 63565836

    [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 3720, file name mysql-bin-yt-votst2-3306.000009

    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: FTS optimize thread exiting.
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 63567481

    160120 17:04:00 innobackupex: Restarting xtrabackup with command: xtrabackup --defaults-file="/mvnobss/users/mysql1/bak/2016-01-20_15-45-54/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mvnobss/users/mysql1/bak/2016-01-20_15-45-54
    for creating ib_logfile*

    xtrabackup version 2.2.8 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
    xtrabackup: cd to /mvnobss/users/mysql1/bak/2016-01-20_15-45-54
    xtrabackup: This target seems to be already prepared.
    xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 2
    xtrabackup: innodb_log_file_size = 268435456
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 2
    xtrabackup: innodb_log_file_size = 268435456
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: Using atomics to ref count buffer pool pages
    InnoDB: The InnoDB memory heap is disabled
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Memory barrier is not used
    InnoDB: Compressed tables use zlib 1.2.3
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, size = 100.0M
    InnoDB: Completed initialization of buffer pool
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: 128 rollback segment(s) are active.
    InnoDB: Waiting for purge to start
    InnoDB: 5.6.22 started; log sequence number 63567481

    [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 3720, file name mysql-bin-yt-votst2-3306.000009

    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: FTS optimize thread exiting.
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 63569162
    160120 17:04:02 innobackupex: completed OK!

    --拷贝文件到datadir目录
    mysql1@hdh-votst2:/mvnobss/users/mysql1>innobackupex --defaults-file=mysql/bin/my.cnf --copy-back bak/2016-01-20_15-45-54/

    mysql1@hdh-votst2:/mvnobss/users/mysql1>ls -l mysql/mydata/
    total 602132
    -rw-rw-r-- 1 mysql1 mysql 79691776 2016-01-20 17:10 ibdata1
    -rw-rw-r-- 1 mysql1 mysql 268435456 2016-01-20 17:10 ib_logfile0
    -rw-rw-r-- 1 mysql1 mysql 268435456 2016-01-20 17:10 ib_logfile1
    drwxrwxr-x 2 mysql1 mysql 4096 2016-01-20 17:10 mysql
    drwxrwxr-x 2 mysql1 mysql 4096 2016-01-20 17:10 performance_schema
    drwxrwxr-x 2 mysql1 mysql 4096 2016-01-20 17:10 test
    -rw-rw-r-- 1 mysql1 mysql 37 2016-01-20 17:10 xtrabackup_binlog_pos_innodb
    -rw-rw-r-- 1 mysql1 mysql 675 2016-01-20 17:10 xtrabackup_info


    --启动mysql
    mysql1@hdh-votst2:/mvnobss/users/mysql1>mysql/bin/Start_Mysql.sh


    --验证数据是否恢复
    mysql1@hdh-votst2:/mvnobss/users/mysql1>mysql -uroot -p123 test
    Warning: Using a password on the command line interface can be insecure.
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.6.21-70.1-log Percona Server (GPL), Release 70.1, Revision 698

    Copyright (c) 2009-2014 Percona LLC and/or its affiliates
    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | t1 |
    | t2 |
    | t3 |
    | t4 |
    +----------------+
    4 rows in set (0.00 sec)

    mysql> select * from t4;
    +------+------+
    | id | name |
    +------+------+
    | 1 | tl |
    | 2 | lj |
    | 3 | lx |
    +------+------+
    3 rows in set (0.00 sec)

    至此数据恢复成功。

    MySQL中如何进行XtraBackup实战.docx

    将本文的Word文档下载到电脑

    推荐度:

    下载
    热门标签: mysqlxtrabackup