黄杉

Roads Untraveled

  • 博客访问: 3432740
  • 博文数量: 176
  • 用 户 组: 普通用户
  • 注册时间: 2011-09-05 16:54
  • 认证徽章:
个人简介

Happy is the man who is living by his hobby.

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(176)

文章存档

2015年(33)

2014年(129)

2013年(13)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

分类: MySQL

1,xtrabackup简介

关于数据库备份以及备份工具,参考:http://blog.itpub.net/26230597/viewspace-1460065/,这里来介绍xtrabackup已经如何使用xtrabackup进行对数据库的全备份已经恢复。

 

2,xtrabackup下载

下载地址为:http://www.percona.com/downloads/XtraBackup/

Chorm浏览器下面,在“DOWNLOADPERCONA XTRABACKUP”下面,选择版本号以及os版本,有的浏览器打不开,可能会出现“No files found with current filters.”,那么就需要换个浏览器比如chrome就可以打开了,如下图1.png所示:

 

也可以直接在linux下面是用wget的方式下载:

wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.2.9/binary/tarball/percona-xtrabackup-2.2.9-5067-Linux-x86_64.tar.gz

 

 

3,xtrabackup安装

3.1配置datadir

先check下是否已经配置datadir,检查是否有mysql服务,有的话就在my.cnf配置文件里面设置datadir

[root@data01 ~]# more /etc/my.cnf |grepdatadir

[root@data01 ~]#

Ok,看到没有配置datadir,去my.cnf里面配置

vim my.cnf

datadir         = /home/data/mysql/data

再check下:

[root@data01 ~]# more /etc/my.cnf |grepdatadir

datadir         = /home/data/mysql/data

[root@data01 ~]#

[root@data01 ~]#

 

3.2解压

tar -xvfpercona-xtrabackup-2.2.9-5067-Linux-x86_64.tar.gz

 

3.3 copy到/usr/bin/

cp./percona-xtrabackup-2.2.9-Linux-x86_64/bin/innobackupex /usr/bin/innobackupex

cp./percona-xtrabackup-2.2.9-Linux-x86_64/bin/xtrabackup /usr/bin/xtrabackup

cp./percona-xtrabackup-2.2.9-Linux-x86_64/bin/xbcrypt /usr/bin/xbcrypt

cp ./percona-xtrabackup-2.2.9-Linux-x86_64/bin/xbstream/usr/bin/xbstream

PS:新版本已经没有了innobackupex-1.5.1这个小插件了。

 

3.4 查看xtrabackup是否可用

[root@data01 ~]# innobackupex --help |more

Options:

   --apply-log

       Prepare a backup in BACKUP-DIR by applying the transaction log file

       named "xtrabackup_logfile" located in the same directory.Also,

       create new transaction logs. The InnoDB configuration is read from

       the file "backup-my.cnf".

 

   --backup-locks

       This option controls if backup locks should be used instead of FLUSH

       TABLES WITH READ LOCK on the backup stage. The option has no effect

       when backup locks are not supported by the server. This option is

       enabled by default, disable with --no-backup-locks.

 

3.5 源码方式安装

参考:http://www.percona.com/doc/percona-xtrabackup/2.2/installation/compiling_xtrabackup.html

 

4,二大工具组件

xtrabackup:支持innodb存储引擎表,xtradb存储引擎表

innobackupex:支持innodb存储引擎表、xtradb存储引擎表、myisam存储引擎表。

 

5,一些重要参数

--defaults-file

同xtrabackup的--defaults-file参数

--apply-log

对xtrabackup的--prepare参数的封装

--copy-back

做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir ;

--remote-host=HOSTNAME

通过ssh将备份数据存储到进程服务器上;

--stream=[tar]

备份文件输出格式, tar时使用tar4ibd , 该文件可在XtarBackup binary文件中获得.如果备份时有指定--stream=tar, 则tar4ibd文件所处目录一定要在$PATH中(因为使用的是tar4ibd去压缩, 在XtraBackup的binary包中可获得该文件)。

在使用参数stream=tar备份的时候,你的xtrabackup_logfile可能会临时放在/tmp目录下,如果你备份的时候并发写入较大的话 xtrabackup_logfile可能会很大(5G+),很可能会撑满你的/tmp目录,可以通过参数--tmpdir指定目录来解决这个问题。

--tmpdir=DIRECTORY

当有指定--remote-host or --stream时, 事务日志临时存储的目录, 默认采用MySQL配置文件中所指定的临时目录tmpdir

--redo-only--apply-log组,

强制备份日志时只redo ,跳过rollback。这在做增量备份时非常必要。

--use-memory=#

该参数在prepare的时候使用,控制prepare时innodb实例使用的内存量,用来取代my.cnf中的buffer_pool_size值,如果你是独立服务器,而且内存足够大的话,为了加快备份恢复的效率,你可以调大--use-memory这个参数值

--throttle=IOS

同xtrabackup的--throttle参数

--sleep=是给ibbackup使用的,指定每备份1M数据,过程停止拷贝多少毫秒,也是为了在备份时尽量减小对正常业务的影响,具体可以查看ibbackup的手册;

--compress[=LEVEL]

对备份数据迚行压缩,仅支持ibbackup,xtrabackup还没有实现;

--include=REGEXP

对 xtrabackup参数--tables的封装,也支持ibbackup。备份包含的库表,例如:--include="test.*",意思是要备份 test库中所有的表。如果需要全备份,则省略这个参数;如果需要备份test库下的2个表:test1和test2,则写成:--include="test.test1|test.test2"。也可以使用通配符,如:--include="test.test*"。

--databases=LIST

列出需要备份的databases,如果没有指定该参数,所有包含MyISAM和InnoDB表的database都会被备份;

--uncompress

解压备份的数据文件,支持ibbackup,xtrabackup还没有实现该功能;

--slave-info

备份从库, 加上--slave-info备份目录下会多生成一个xtrabackup_slave_info 文件, 这里会保存主日志文件以及偏移, 文件内容类似于:CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=0

--socket=SOCKET

指定mysql.sock所在位置,以便备份进程登录mysql.

--safe-slave-backup

则会暂停Slave的SQL线程,等待到没有打开的临时表的时候开始备份.备份结束后SQL线程会自动启动,这样就可以确保一致性的复制状态.

6,xtrabackup全备份

6.1 全备份

只备份不压缩:

xtrabackup --defaults-file=/etc/my.cnf--user=root --password="" --port=3306 --backup--target-dir=/data/backups/mysql/$(date +%Y%m%d)/

 

备份压缩:

xtrabackup --defaults-file=/etc/my.cnf--user=root --password="" --port=3306 --backup    --socket=/usr/local/mysql/mysql.sock--stream=tar --target-dir=/data/backups/mysql/$(date +%Y%m%d)/ |gzip >alldbfullbackup.tar.gz

参考:http://www.percona.com/doc/percona-xtrabackup/2.2/howtos/recipes_ibkx_compressed.html

 

备份过程如下所示:


  1. [root@data01 mysql]# xtrabackup--defaults-file=/etc/my.cnf --user=root --password=\"\" --port=3306--backup --socket=/usr/local/mysql/mysql.sock --stream=tar--target-dir=/data/backups/mysql/$(date +%Y%m%d)/ |gzip > alldbfullbackup.tar.gz
  2. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
  3. xtrabackup: uses posix_fadvise().
  4. xtrabackup: cd to /home/data/mysql/data
  5. xtrabackup: open files limit requested 0,set to 1024
  6. xtrabackup: using the following InnoDBconfiguration:
  7. xtrabackup: innodb_data_home_dir = ./
  8. xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
  9. xtrabackup: innodb_log_group_home_dir = ./
  10. xtrabackup: innodb_log_files_in_group = 3
  11. xtrabackup: innodb_log_file_size = 67108864
  12. >> log scanned up to (423916780)
  13. xtrabackup: Generating a list oftablespaces
  14. [01] Streaming ./IBdata1
  15. >> log scanned up to (423916780)
  16. >> log scanned up to (423916780)
  17. >> log scanned up to (423916780)
  18. >> log scanned up to (423916780)
  19. >> log scanned up to (423916780)
  20. [01] ...done
  21. [01] Streaming ./IBdata2
  22. >> log scanned up to (423916780)
  23. >> log scanned up to (423916780)
  24. >> log scanned up to (423916780)
  25. [01] ...done
  26. [01] Streaming./mysql/slave_master_info.ibd
  27. [01] ...done
  28. [01] Streaming./mysql/slave_relay_log_info.ibd
  29. [01] ...done
  30. [01] Streaming./mysql/innodb_index_stats.ibd
  31. [01] ...done
  32. [01] Streaming./mysql/slave_worker_info.ibd
  33. [01] ...done
  34. [01] Streaming./mysql/innodb_table_stats.ibd
  35. [01] ...done
  36. [01] Streaming ./sbtest/sbtest1.ibd
  37. [01] ...done
  38. [01] Streaming ./sbtest/sbtest5.ibd
  39. [01] ...done
  40. [01] Streaming ./sbtest/sbtest8.ibd
  41. [01] ...done
  42. [01] Streaming ./sbtest/sbtest4.ibd
  43. [01] ...done
  44. [01] Streaming ./sbtest/sbtest2.ibd
  45. [01] ...done
  46. [01] Streaming ./sbtest/sbtest6.ibd
  47. [01] ...done
  48. [01] Streaming ./sbtest/sbtest7.ibd
  49. [01] ...done
  50. [01] Streaming ./sbtest/sbtest3.ibd
  51. [01] ...done
  52. [01] Streaming ./test/t1.ibd
  53. [01] ...done
  54. [01] Streaming ./test/t3.ibd
  55. [01] ...done
  56. [01] Streaming ./test/t.ibd
  57. [01] ...done
  58. [01] Streaming ./user_db/t1.ibd
  59. [01] ...done
  60. [01] Streaming ./user_db/test.ibd
  61. [01] ...done
  62. [01] Streaming ./user_db/test2.ibd
  63. [01] ...done
  64. [01] Streaming ./user_db/t3.ibd
  65. >> log scanned up to (423916780)
  66. [01] ...done
  67. [01] Streaming ./user_db/UC_USER.ibd
  68. >> log scanned up to (423916780)
  69. [01] ...done
  70. [01] Streaming ./user_db/t.ibd
  71. [01] ...done
  72. >> log scanned up to (423916780)
  73. xtrabackup: The latest check point (forincremental): \'423916780\'
  74. xtrabackup: Stopping log copying thread.
  75. .>> log scanned up to (423916780)
  76.  
  77. xtrabackup: Transaction log of lsn(423916780) to (423916780) was copied.
  78. [root@data01 mysql]#
  79.  
  80. 备份结束后,可以看到如下结果,备份目录为null,都压缩到了alldbfullbackup.tar.gz备份压缩包里面,如下所示:
  81. [root@data01 mysql]# du -sh *
  82. 4.0K 20150318
  83. 5.1M alldbfullbackup.tar.gz
  84. [root@data01 mysql]#



PS1:可以用gzip边备份边压缩,不过推荐是备份完了之后对整个备份目录进行压缩。

 

6.2 全量恢复

先删除测试表数据:


  1. mysql> select * from t1;
  2. +---+----+
  3. | a | b |
  4. +---+----+
  5. | 5 | aa |
  6. +---+----+
  7. 1 row in set (0.03 sec)
  8.  
  9. mysql> delete from t1;
  10. Query OK, 1 row affected (0.02 sec)
  11.  
  12. mysql> select * from t1;
  13. Empty set (0.00 sec)
  14.  
  15. mysql> exit
  16. Bye


进行恢复

xtrabackup --defaults-file=/etc/my.cnf--prepare --target-dir=/data/backups/mysql/20150318

停止msyql数据库

service mysqld5612 stop

复制已经恢复好的文件到mysql数据目录并且赋予mysql账号执行权限

cd /data/backups/mysql/20150318

rsync -rvt --exclude'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile'  ./ /home/data/mysql/data

chown -R mysql:mysql /home/data/mysql/data

启动mysql数据库

service mysqld5612 start

 

恢复完毕,check表t1数据,数据恢复回来了:


  1. mysql> use test;
  2. Database changed
  3. mysql> select * from t1;
  4. +---+----+
  5. | a | b |
  6. +---+----+
  7. | 5 | aa |
  8. +---+----+
  9. 1 row in set (0.09 sec)
  10.  
  11. mysql>


 

PS2: xtrabackup只备份InnoDB数据文件,表结构是不备份的,所以恢复的时候,你必须有对应表结构文件(.frm)如下所示,只备份了*.ibd文件,如下所示:


  1. [root@data01 20150318]# ll
  2. 总用量 262168
  3. -rw-r-----. 1 root root 134217728 3月 18 02:31 IBdata1
  4. -rw-r-----. 1 root root 134217728 3月 18 02:31 IBdata2
  5. drwx------. 2 root root 4096 3月 18 02:31 mysql
  6. drwx------. 2 root root 4096 3月 18 02:31 sbtest
  7. drwx------. 2 root root 4096 3月 18 02:31 test
  8. drwx------. 2 root root 4096 3月 18 02:31 user_db
  9. -rw-r-----. 1 root root 93 3月 18 02:31 xtrabackup_checkpoints
  10. -rw-r-----. 1 root root 2560 3月 18 02:31 xtrabackup_logfile
  11. [root@data01 20150318]# cd test/
  12. [root@data01 test]# ll
  13. 总用量 288
  14. -rw-r-----. 1 root root 98304 3月 18 02:31 t1.ibd
  15. -rw-r-----. 1 root root 98304 3月 18 02:31 t3.ibd
  16. -rw-r-----. 1 root root 98304 3月 18 02:31 t.ibd
  17. [root@data01 test]#


 

7 innobackupex全备份

备份过程如下图2.png所示:


 

 

7.1 全备份

innobackupex --defaults-file=/etc/my.cnf--user=root --password="" --target-dir=/data/backups/mysql/$(date+%Y%m%d)/ --no-timestamp --parallel=2

 

备份过程如下:


  1. [root@data01 ~]# innobackupex--defaults-file=/etc/my.cnf --user=root --password=\"\"/data/backups/mysql/$(date +%Y%m%d)/ --no-timestamp --parallel=2
  2.  
  3. InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
  4. and Percona LLC and/or its affiliates2009-2013. All Rights Reserved.
  5.  
  6. This software is published under
  7. the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.
  8.  
  9. Get the latest version of PerconaXtraBackup, documentation, and help resources:
  10. http://www.percona.com/xb/p
  11.  
  12. 150318 19:32:40 innobackupex: Connecting to MySQL server withDSN\'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup\'as \'root\' (using password: NO).
  13. 150318 19:32:40 innobackupex: Connected to MySQL server
  14. 150318 19:32:40 innobackupex: Executing a version checkagainst the server...
  15. 150318 19:32:40 innobackupex: Done.
  16. 150318 19:32:40 innobackupex: Starting the backup operation
  17.  
  18. IMPORTANT: Please check that the backup runcompletes successfully.
  19.           At the end of a successful backup run innobackupex
  20.           prints \"completed OK!\".
  21.  
  22. innobackupex: Using server version 5.6.12-log
  23.  
  24. innobackupex: Created backup directory/data/backups/mysql/20150318
  25.  
  26. 150318 19:32:40 innobackupex: Starting ibbackup with command:xtrabackup --defaults-file=\"/etc/my.cnf\" --defaults-group=\"mysqld\" --backup --suspend-at-end--target-dir=/data/backups/mysql/20150318 --tmpdir=/tmp --extra-lsndir=\'/tmp\'--parallel=2
  27. innobackupex: Waiting for ibbackup(pid=3648) to suspend
  28. innobackupex: Suspend file\'/data/backups/mysql/20150318/xtrabackup_suspended_2\'
  29.  
  30. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
  31. xtrabackup: uses posix_fadvise().
  32. xtrabackup: cd to /home/data/mysql/data
  33. xtrabackup: open files limit requested 0,set to 1024
  34. xtrabackup: using the following InnoDBconfiguration:
  35. xtrabackup: innodb_data_home_dir = ./
  36. xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
  37. xtrabackup: innodb_log_group_home_dir = ./
  38. xtrabackup: innodb_log_files_in_group = 3
  39. xtrabackup: innodb_log_file_size = 67108864
  40. >> log scanned up to (423918562)
  41. xtrabackup: Generating a list oftablespaces
  42. xtrabackup: Starting 2 threads for paralleldata files transfer
  43. [02] Copying ./IBdata1 to/data/backups/mysql/20150318/IBdata1
  44. [01] Copying ./IBdata2 to/data/backups/mysql/20150318/IBdata2
  45. >> log scanned up to (423918562)
  46. >> log scanned up to (423918562)
  47. [02] ...done
  48. [02] Copying ./mysql/slave_master_info.ibdto /data/backups/mysql/20150318/mysql/slave_master_info.ibd
  49. [02] ...done
  50. [02] Copying./mysql/slave_relay_log_info.ibd to/data/backups/mysql/20150318/mysql/slave_relay_log_info.ibd
  51. [02] ...done
  52. [02] Copying ./mysql/innodb_index_stats.ibdto /data/backups/mysql/20150318/mysql/innodb_index_stats.ibd
  53. [02] ...done
  54. [02] Copying ./mysql/slave_worker_info.ibdto /data/backups/mysql/20150318/mysql/slave_worker_info.ibd
  55. [02] ...done
  56. [02] Copying ./mysql/innodb_table_stats.ibdto /data/backups/mysql/20150318/mysql/innodb_table_stats.ibd
  57. [02] ...done
  58. [02] Copying ./sbtest/sbtest1.ibd to/data/backups/mysql/20150318/sbtest/sbtest1.ibd
  59. [02] ...done
  60. [02] Copying ./sbtest/sbtest5.ibd to/data/backups/mysql/20150318/sbtest/sbtest5.ibd
  61. [02] ...done
  62. [02] Copying ./sbtest/sbtest8.ibd to/data/backups/mysql/20150318/sbtest/sbtest8.ibd
  63. [02] ...done
  64. [02] Copying ./sbtest/sbtest4.ibd to/data/backups/mysql/20150318/sbtest/sbtest4.ibd
  65. [02] ...done
  66. [02] Copying ./sbtest/sbtest2.ibd to/data/backups/mysql/20150318/sbtest/sbtest2.ibd
  67. [02] ...done
  68. [02] Copying ./sbtest/sbtest6.ibd to/data/backups/mysql/20150318/sbtest/sbtest6.ibd
  69. [02] ...done
  70. [02] Copying ./sbtest/sbtest7.ibd to/data/backups/mysql/20150318/sbtest/sbtest7.ibd
  71. [02] ...done
  72. [02] Copying ./sbtest/sbtest3.ibd to/data/backups/mysql/20150318/sbtest/sbtest3.ibd
  73. [02] ...done
  74. [02] Copying ./test/t1.ibd to/data/backups/mysql/20150318/test/t1.ibd
  75. [02] ...done
  76. [02] Copying ./test/t3.ibd to/data/backups/mysql/20150318/test/t3.ibd
  77. [02] ...done
  78. [02] Copying ./test/t.ibd to/data/backups/mysql/20150318/test/t.ibd
  79. [02] ...done
  80. [02] Copying ./user_db/t1.ibd to/data/backups/mysql/20150318/user_db/t1.ibd
  81. [02] ...done
  82. [02] Copying ./user_db/test.ibd to/data/backups/mysql/20150318/user_db/test.ibd
  83. [02] ...done
  84. [02] Copying ./user_db/test2.ibd to/data/backups/mysql/20150318/user_db/test2.ibd
  85. [02] ...done
  86. [01] ...done
  87. [01] Copying ./user_db/t3.ibd to /data/backups/mysql/20150318/user_db/t3.ibd
  88. [02] Copying ./user_db/UC_USER.ibd to/data/backups/mysql/20150318/user_db/UC_USER.ibd
  89. [01] ...done
  90. [01] Copying ./user_db/t.ibd to/data/backups/mysql/20150318/user_db/t.ibd
  91. >> log scanned up to (423918562)
  92. [01] ...done
  93. [02] ...done
  94. >> log scanned up to (423918562)
  95. xtrabackup: Creating suspend file\'/data/backups/mysql/20150318/xtrabackup_suspended_2\' with pid \'3648\'
  96.  
  97. 150318 19:32:45 innobackupex: Continuing after ibbackup hassuspended
  98. 150318 19:32:45 innobackupex: Executing FLUSH TABLES WITHREAD LOCK...
  99. 150318 19:32:45 innobackupex: All tables locked and flushedto disk
  100.  
  101. 150318 19:32:45 innobackupex: Starting to backup non-InnoDBtables and files
  102. innobackupex: in subdirectories of \'/home/data/mysql/data/\'
  103. innobackupex: Backing up files\'/home/data/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}\'(53 files)
  104. >> log scanned up to (423918562)
  105. innobackupex: Backing up files\'/home/data/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}\'(74 files)
  106. >> log scanned up to (423918562)
  107. innobackupex: Backing up file\'/home/data/mysql/data//sbtest/sbtest3.frm\'
  108. innobackupex: Backing up file\'/home/data/mysql/data//sbtest/sbtest4.frm\'
  109. innobackupex: Backing up file\'/home/data/mysql/data//sbtest/sbtest6.frm\'
  110. innobackupex: Backing up file\'/home/data/mysql/data//sbtest/sbtest8.frm\'
  111. innobackupex: Backing up file\'/home/data/mysql/data//sbtest/sbtest1.frm\'
  112. innobackupex: Backing up file\'/home/data/mysql/data//sbtest/db.opt\'
  113. innobackupex: Backing up file\'/home/data/mysql/data//sbtest/sbtest2.frm\'
  114. innobackupex: Backing up file\'/home/data/mysql/data//sbtest/sbtest7.frm\'
  115. innobackupex: Backing up file\'/home/data/mysql/data//sbtest/sbtest5.frm\'
  116. innobackupex: Backing up file\'/home/data/mysql/data//test/t3.frm\'
  117. innobackupex: Backing up file\'/home/data/mysql/data//test/t1.frm\'
  118. innobackupex: Backing up file\'/home/data/mysql/data//user_db/UC_USER.frm\'
  119. >> log scanned up to (423918562)
  120. innobackupex: Backing up file\'/home/data/mysql/data//user_db/t.frm\'
  121. innobackupex: Backing up file\'/home/data/mysql/data//user_db/t3.frm\'
  122. innobackupex: Backing up file\'/home/data/mysql/data//user_db/test.frm\'
  123. innobackupex: Backing up file\'/home/data/mysql/data//user_db/db.opt\'
  124. innobackupex: Backing up file\'/home/data/mysql/data//user_db/test2.frm\'
  125. innobackupex: Backing up file\'/home/data/mysql/data//user_db/t1.frm\'
  126. 150318 19:32:48 innobackupex: Finished backing up non-InnoDBtables and files
  127.  
  128. 150318 19:32:48 innobackupex: Executing FLUSHNO_WRITE_TO_BINLOG ENGINE LOGS...
  129. 150318 19:32:48 innobackupex: Waiting for log copying tofinish
  130.  
  131. xtrabackup: The latest check point (forincremental): \'423918562\'
  132. xtrabackup: Stopping log copying thread.
  133. .>> log scanned up to (423918562)
  134.  
  135. xtrabackup: Creating suspend file\'/data/backups/mysql/20150318/xtrabackup_log_copied\' with pid \'3648\'
  136. xtrabackup: Transaction log of lsn(423918562) to (423918562) was copied.
  137. 150318 19:32:49 innobackupex: All tables unlocked
  138.  
  139. innobackupex: Backup created in directory\'/data/backups/mysql/20150318\'
  140. innobackupex: MySQL binlog position:filename \'mysql-bin.000064\', position 120
  141. 150318 19:32:49 innobackupex: Connection to database serverclosed
  142. 150318 19:32:49 innobackupex: completed
  143. [root@data01 ~]#


 

  1. mysql> use test;
  2. Database changed
  3. mysql> select * from t1;
  4. +---+----+
  5. | a | b |
  6. +---+----+
  7. | 5 | aa |
  8. +---+----+
  9. 1 row in set (0.09 sec)
  10.  
  11. mysql> delete from t1;
  12. Query OK, 1 row affected (0.06 sec)
  13.  
  14. mysql> select * from t1;
  15. Empty set (0.00 sec)
  16.  
  17. mysql> drop table t1;
  18. Query OK, 0 rows affected (0.08 sec)
  19.  
  20. mysql>



7.2 恢复

删除数据:


  1. mysql> use test;
  2. Database changed
  3. mysql> select * from t1;
  4. +---+----+
  5. | a | b |
  6. +---+----+
  7. | 5 | aa |
  8. +---+----+
  9. 1 row in set (0.09 sec)
  10.  
  11. mysql> delete from t1;
  12. Query OK, 1 row affected (0.06 sec)
  13.  
  14. mysql> select * from t1;
  15. Empty set (0.00 sec)
  16.  
  17. mysql> drop table t1;
  18. Query OK, 0 rows affected (0.08 sec)
  19.  
  20. mysql>



进行恢复:

innobackupex --defaults-file=/etc/my.cnf --use-memory=4G--apply-log /data/backups/mysql/$(date +%Y%m%d)/

恢复过程记录如下:


  1. [root@data01 ~]# innobackupex--defaults-file=/etc/my.cnf --apply-log /data/backups/mysql/$(date +%Y%m%d)/
  2.  
  3. InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
  4. and Percona LLC and/or its affiliates2009-2013. All Rights Reserved.
  5.  
  6. This software is published under
  7. the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.
  8.  
  9. Get the latest version of PerconaXtraBackup, documentation, and help resources:
  10. http://www.percona.com/xb/p
  11.  
  12. 150318 20:09:35 innobackupex: Starting the apply-logoperation
  13.  
  14. IMPORTANT: Please check that the apply-logrun completes successfully.
  15.           At the end of a successful apply-log run innobackupex
  16.           prints \"completed OK!\".
  17.  
  18.  
  19. 150318 20:09:35 innobackupex: Starting ibbackup with command:xtrabackup --defaults-file=\"/data/backups/mysql/20150318/backup-my.cnf\" --defaults-group=\"mysqld\" --prepare--target-dir=/data/backups/mysql/20150318
  20.  
  21. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
  22. xtrabackup: cd to/data/backups/mysql/20150318
  23. xtrabackup: This target seems to be notprepared yet.
  24. xtrabackup: xtrabackup_logfile detected:size=2654208, start_lsn=(423918562)
  25. xtrabackup: using the following InnoDBconfiguration for recovery:
  26. xtrabackup: innodb_data_home_dir = ./
  27. xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
  28. xtrabackup: innodb_log_group_home_dir = ./
  29. xtrabackup: innodb_log_files_in_group = 1
  30. xtrabackup: innodb_log_file_size = 2654208
  31. xtrabackup: using the following InnoDBconfiguration for recovery:
  32. xtrabackup: innodb_data_home_dir = ./
  33. xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
  34. xtrabackup: innodb_log_group_home_dir = ./
  35. xtrabackup: innodb_log_files_in_group = 1
  36. xtrabackup: innodb_log_file_size = 2654208
  37. xtrabackup: Starting InnoDB instance forrecovery.
  38. xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
  39. InnoDB: Using atomics to ref count bufferpool pages
  40. InnoDB: The InnoDB memory heap is disabled
  41. InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
  42. InnoDB: Memory barrier is not used
  43. InnoDB: Compressed tables use zlib 1.2.3
  44. InnoDB: Using CPU crc32 instructions
  45. InnoDB: Initializing buffer pool, size =100.0M
  46. InnoDB: Completed initialization of bufferpool
  47. InnoDB: Highest supported file format isBarracuda.
  48. InnoDB: The log sequence numbers 423918522and 423918522 in ibdata files do not match the log sequence number 423918562 inthe
  49. InnoDB: Database was not shutdown
  50. InnoDB: Starting crash recovery.
  51. InnoDB: Reading tablespace information fromthe .ibd files...
  52. InnoDB: Restoring possible half-writtendata pages
  53. InnoDB: from the doublewrite buffer...
  54. InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  55. InnoDB: 128 rollback segment(s) are active.
  56. InnoDB: Waiting for purge to start
  57. InnoDB: 5.6.22 started; log sequence number423918562
  58.  
  59. [notice (again)]
  60.   Ifyou use binary log and don\'t use any hack of group commit,
  61.   thebinary log position seems to be:
  62. InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  63.  
  64. xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
  65. InnoDB: FTS optimize thread exiting.
  66. InnoDB: Starting shutdown...
  67. InnoDB: Shutdown completed; log sequencenumber 423919216
  68.  
  69. 150318 20:09:41 innobackupex: Restarting xtrabackup withcommand: xtrabackup --defaults-file=\"/data/backups/mysql/20150318/backup-my.cnf\" --defaults-group=\"mysqld\" --prepare--target-dir=/data/backups/mysql/20150318
  70. for creating ib_logfile*
  71.  
  72. xtrabackup version 2.2.9 based on MySQLserver 5.6.22 Linux (x86_64) (revision id: )
  73. xtrabackup: cd to/data/backups/mysql/20150318
  74. xtrabackup: This target seems to be alreadyprepared.
  75. xtrabackup: notice: xtrabackup_logfile wasalready used to \'--prepare\'.
  76. xtrabackup: using the following InnoDBconfiguration for recovery:
  77. xtrabackup: innodb_data_home_dir = ./
  78. xtrabackup: innodb_data_file_path =IBdata1:128M;IBdata2:128M:autoextend
  79. xtrabackup: innodb_log_group_home_dir = ./
  80. xtrabackup: innodb_log_files_in_group = 3
  81. xtrabackup: innodb_log_file_size = 67108864
  82. xtrabackup: using the following InnoDBconfiguration for recovery:
  83. xtrabackup: innodb_data_home_dir = ./
  84. xtrabackup: innodb_data_file_path = IBdata1:128M;IBdata2:128M:autoextend
  85. xtrabackup: innodb_log_group_home_dir = ./
  86. xtrabackup: innodb_log_files_in_group = 3
  87. xtrabackup: innodb_log_file_size = 67108864
  88. xtrabackup: Starting InnoDB instance forrecovery.
  89. xtrabackup: Using 104857600 bytes forbuffer pool (set by --use-memory parameter)
  90. InnoDB: Using atomics to ref count bufferpool pages
  91. InnoDB: The InnoDB memory heap is disabled
  92. InnoDB: Mutexes and rw_locks use GCC atomicbuiltins
  93. InnoDB: Memory barrier is not used
  94. InnoDB: Compressed tables use zlib 1.2.3
  95. InnoDB: Using CPU crc32 instructions
  96. InnoDB: Initializing buffer pool, size =100.0M
  97. InnoDB: Completed initialization of bufferpool
  98. InnoDB: Setting log file ./ib_logfile101size to 64 MB
  99. InnoDB: Setting log file ./ib_logfile1 sizeto 64 MB
  100. InnoDB: Setting log file ./ib_logfile2 sizeto 64 MB
  101. InnoDB: Renaming log file ./ib_logfile101to ./ib_logfile0
  102. InnoDB: New log files created,LSN=423919216
  103. InnoDB: Highest supported file format isBarracuda.
  104. InnoDB: 128 rollback segment(s) are active.
  105. InnoDB: Waiting for purge to start
  106. InnoDB: 5.6.22 started; log sequence number423919628
  107.  
  108. [notice (again)]
  109.   Ifyou use binary log and don\'t use any hack of group commit,
  110.   thebinary log position seems to be:
  111. InnoDB: Last MySQL binlog file position 0915, file name mysql-bin.000053
  112.  
  113. xtrabackup: starting shutdown withinnodb_fast_shutdown = 1
  114. InnoDB: FTS optimize thread exiting.
  115. InnoDB: Starting shutdown...
  116. InnoDB: Shutdown completed; log sequencenumber 423920158
  117. 150318 20:09:46 innobackupex: completed
  118. [root@data01 ~]#


修复:RESTORETHE BACKUP

停止mysql服务

[root@data01 ~]# service mysqld5612 stop

Shutting down MySQL.. SUCCESS!

[root@data01 ~]#

然后修复—copy-back,innobackupex--defaults-file=/etc/my.cnf --copy-back /data/backups/mysql/$(date +%Y%m%d)/,修复过程如下:


  1. [root@data01 ~]# innobackupex--defaults-file=/etc/my.cnf --copy-back /data/backups/mysql/$(date +%Y%m%d)/
  2.  
  3. InnoDB Backup Utility v1.5.1-xtrabackup;Copyright 2003, 2009 Innobase Oy
  4. and Percona LLC and/or its affiliates2009-2013. All Rights Reserved.
  5.  
  6. This software is published under
  7. the GNU GENERAL PUBLIC LICENSE Version 2,June 1991.
  8.  
  9. Get the latest version of PerconaXtraBackup, documentation, and help resources:
  10. http://www.percona.com/xb/p
  11.  
  12. 150318 20:18:38 innobackupex: Starting the copy-backoperation
  13.  
  14. IMPORTANT: Please check that the copy-backrun completes successfully.
  15.           At the end of a successful copy-back run innobackupex
  16.           prints \"completed OK!\".
  17.  
  18. innobackupex: Starting to copy files in\'/data/backups/mysql/20150318\'
  19. innobackupex: back to original datadirectory \'/home/data/mysql/data\'
  20. innobackupex: Copying\'/data/backups/mysql/20150318/xtrabackup_binlog_pos_innodb\' to\'/home/data/mysql/data/xtrabackup_binlog_pos_innodb\'
  21. innobackupex: Copying\'/data/backups/mysql/20150318/xtrabackup_info\' to\'/home/data/mysql/data/xtrabackup_info\'
  22. innobackupex: Creating directory\'/home/data/mysql/data/performance_schema\'
  23. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_waits_history.frm\' to\'/home/data/mysql/data/performance_schema/events_waits_history.frm\'
  24. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_waits_summary_global_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_waits_summary_global_by_event_name.frm\'
  25. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/threads.frm\' to\'/home/data/mysql/data/performance_schema/threads.frm\'
  26. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_stages_history_long.frm\'to \'/home/data/mysql/data/performance_schema/events_stages_history_long.frm\'
  27. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/file_summary_by_instance.frm\'to \'/home/data/mysql/data/performance_schema/file_summary_by_instance.frm\'
  28. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_user_by_event_name.frm\'to \'/home/data/mysql/data/performance_schema/events_waits_summary_by_user_by_event_name.frm\'
  29. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_account_by_event_name.frm\'to \'/home/data/mysql/data/performance_schema/events_statements_summary_by_account_by_event_name.frm\'
  30. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_user_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_statements_summary_by_user_by_event_name.frm\'
  31. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_waits_current.frm\' to\'/home/data/mysql/data/performance_schema/events_waits_current.frm\'
  32. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/table_lock_waits_summary_by_table.frm\'to\'/home/data/mysql/data/performance_schema/table_lock_waits_summary_by_table.frm\'
  33. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/socket_instances.frm\' to\'/home/data/mysql/data/performance_schema/socket_instances.frm\'
  34. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/setup_actors.frm\' to\'/home/data/mysql/data/performance_schema/setup_actors.frm\'
  35. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/table_io_waits_summary_by_table.frm\'to\'/home/data/mysql/data/performance_schema/table_io_waits_summary_by_table.frm\'
  36. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_stages_summary_by_account_by_event_name.frm\'to \'/home/data/mysql/data/performance_schema/events_stages_summary_by_account_by_event_name.frm\'
  37. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/cond_instances.frm\' to\'/home/data/mysql/data/performance_schema/cond_instances.frm\'
  38. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/users.frm\' to\'/home/data/mysql/data/performance_schema/users.frm\'
  39. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/db.opt\' to\'/home/data/mysql/data/performance_schema/db.opt\'
  40. innobackupex: Copying \'/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_instance.frm\'to\'/home/data/mysql/data/performance_schema/events_waits_summary_by_instance.frm\'
  41. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_stages_summary_by_thread_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_stages_summary_by_thread_by_event_name.frm\'
  42. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_statements_current.frm\'to \'/home/data/mysql/data/performance_schema/events_statements_current.frm\'
  43. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/objects_summary_global_by_type.frm\'to\'/home/data/mysql/data/performance_schema/objects_summary_global_by_type.frm\'
  44. innobackupex: Copying \'/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_thread_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_waits_summary_by_thread_by_event_name.frm\'
  45. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_host_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_waits_summary_by_host_by_event_name.frm\'
  46. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/socket_summary_by_event_name.frm\'to \'/home/data/mysql/data/performance_schema/socket_summary_by_event_name.frm\'
  47. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_stages_history.frm\' to\'/home/data/mysql/data/performance_schema/events_stages_history.frm\'
  48. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_statements_history.frm\'to \'/home/data/mysql/data/performance_schema/events_statements_history.frm\'
  49. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_thread_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_statements_summary_by_thread_by_event_name.frm\'
  50. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_stages_summary_global_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_stages_summary_global_by_event_name.frm\'
  51. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/setup_timers.frm\' to\'/home/data/mysql/data/performance_schema/setup_timers.frm\'
  52. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/hosts.frm\' to\'/home/data/mysql/data/performance_schema/hosts.frm\'
  53. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_stages_summary_by_host_by_event_name.frm\'to \'/home/data/mysql/data/performance_schema/events_stages_summary_by_host_by_event_name.frm\'
  54. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/host_cache.frm\' to\'/home/data/mysql/data/performance_schema/host_cache.frm\'
  55. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/setup_objects.frm\' to\'/home/data/mysql/data/performance_schema/setup_objects.frm\'
  56. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/setup_consumers.frm\' to\'/home/data/mysql/data/performance_schema/setup_consumers.frm\'
  57. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/session_account_connect_attrs.frm\'to \'/home/data/mysql/data/performance_schema/session_account_connect_attrs.frm\'
  58. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_host_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_statements_summary_by_host_by_event_name.frm\'
  59. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/socket_summary_by_instance.frm\'to \'/home/data/mysql/data/performance_schema/socket_summary_by_instance.frm\'
  60. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_statements_summary_global_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_statements_summary_global_by_event_name.frm\'
  61. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_stages_summary_by_user_by_event_name.frm\'to \'/home/data/mysql/data/performance_schema/events_stages_summary_by_user_by_event_name.frm\'
  62. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/mutex_instances.frm\' to\'/home/data/mysql/data/performance_schema/mutex_instances.frm\'
  63. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/setup_instruments.frm\' to\'/home/data/mysql/data/performance_schema/setup_instruments.frm\'
  64. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_statements_summary_by_digest.frm\'to \'/home/data/mysql/data/performance_schema/events_statements_summary_by_digest.frm\'
  65. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/rwlock_instances.frm\' to\'/home/data/mysql/data/performance_schema/rwlock_instances.frm\'
  66. innobackupex: Copying \'/data/backups/mysql/20150318/performance_schema/file_summary_by_event_name.frm\'to \'/home/data/mysql/data/performance_schema/file_summary_by_event_name.frm\'
  67. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_waits_summary_by_account_by_event_name.frm\'to\'/home/data/mysql/data/performance_schema/events_waits_summary_by_account_by_event_name.frm\'
  68. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_stages_current.frm\' to\'/home/data/mysql/data/performance_schema/events_stages_current.frm\'
  69. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/session_connect_attrs.frm\' to\'/home/data/mysql/data/performance_schema/session_connect_attrs.frm\'
  70. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/performance_timers.frm\' to\'/home/data/mysql/data/performance_schema/performance_timers.frm\'
  71. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_waits_history_long.frm\'to \'/home/data/mysql/data/performance_schema/events_waits_history_long.frm\'
  72. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/file_instances.frm\' to\'/home/data/mysql/data/performance_schema/file_instances.frm\'
  73. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/events_statements_history_long.frm\'to\'/home/data/mysql/data/performance_schema/events_statements_history_long.frm\'
  74. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/table_io_waits_summary_by_index_usage.frm\'to \'/home/data/mysql/data/performance_schema/table_io_waits_summary_by_index_usage.frm\'
  75. innobackupex: Copying\'/data/backups/mysql/20150318/performance_schema/accounts.frm\' to\'/home/data/mysql/data/performance_schema/accounts.frm\'
  76. innobackupex: Creating directory\'/home/data/mysql/data/mysql\'
  77. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/event.MYI\' to\'/home/data/mysql/data/mysql/event.MYI\'
  78. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/columns_priv.frm\' to\'/home/data/mysql/data/mysql/columns_priv.frm\'
  79. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/db.MYD\' to\'/home/data/mysql/data/mysql/db.MYD\'
  80. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/slave_master_info.ibd\' to\'/home/data/mysql/data/mysql/slave_master_info.ibd\'
  81. innobackupex: Copying \'/data/backups/mysql/20150318/mysql/user.MYI\'to \'/home/data/mysql/data/mysql/user.MYI\'
  82. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_transition_type.frm\' to\'/home/data/mysql/data/mysql/time_zone_transition_type.frm\'
  83. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/procs_priv.frm\' to\'/home/data/mysql/data/mysql/procs_priv.frm\'
  84. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_topic.MYI\' to\'/home/data/mysql/data/mysql/help_topic.MYI\'
  85. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_name.MYI\' to\'/home/data/mysql/data/mysql/time_zone_name.MYI\'
  86. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/slave_relay_log_info.ibd\' to\'/home/data/mysql/data/mysql/slave_relay_log_info.ibd\'
  87. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/innodb_index_stats.ibd\' to\'/home/data/mysql/data/mysql/innodb_index_stats.ibd\'
  88. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/columns_priv.MYI\' to\'/home/data/mysql/data/mysql/columns_priv.MYI\'
  89. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/slow_log.CSV\' to\'/home/data/mysql/data/mysql/slow_log.CSV\'
  90. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/func.MYI\' to\'/home/data/mysql/data/mysql/func.MYI\'
  91. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/db.frm\' to\'/home/data/mysql/data/mysql/db.frm\'
  92. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/servers.MYD\' to\'/home/data/mysql/data/mysql/servers.MYD\'
  93. innobackupex: Copying \'/data/backups/mysql/20150318/mysql/time_zone.MYI\'to \'/home/data/mysql/data/mysql/time_zone.MYI\'
  94. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/proc.MYI\' to\'/home/data/mysql/data/mysql/proc.MYI\'
  95. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/tables_priv.MYI\' to\'/home/data/mysql/data/mysql/tables_priv.MYI\'
  96. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/slave_worker_info.frm\' to\'/home/data/mysql/data/mysql/slave_worker_info.frm\'
  97. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone.MYD\' to\'/home/data/mysql/data/mysql/time_zone.MYD\'
  98. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/ndb_binlog_index.frm\' to\'/home/data/mysql/data/mysql/ndb_binlog_index.frm\'
  99. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/slow_log.CSM\' to\'/home/data/mysql/data/mysql/slow_log.CSM\'
  100. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/ndb_binlog_index.MYD\' to\'/home/data/mysql/data/mysql/ndb_binlog_index.MYD\'
  101. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/tables_priv.frm\' to\'/home/data/mysql/data/mysql/tables_priv.frm\'
  102. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/event.frm\' to\'/home/data/mysql/data/mysql/event.frm\'
  103. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/func.MYD\' to \'/home/data/mysql/data/mysql/func.MYD\'
  104. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_keyword.MYD\' to\'/home/data/mysql/data/mysql/help_keyword.MYD\'
  105. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/procs_priv.MYI\' to\'/home/data/mysql/data/mysql/procs_priv.MYI\'
  106. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/slave_relay_log_info.frm\' to\'/home/data/mysql/data/mysql/slave_relay_log_info.frm\'
  107. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_leap_second.MYI\' to \'/home/data/mysql/data/mysql/time_zone_leap_second.MYI\'
  108. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_transition.MYI\' to\'/home/data/mysql/data/mysql/time_zone_transition.MYI\'
  109. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/func.frm\' to\'/home/data/mysql/data/mysql/func.frm\'
  110. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_keyword.frm\' to\'/home/data/mysql/data/mysql/help_keyword.frm\'
  111. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/procs_priv.MYD\' to \'/home/data/mysql/data/mysql/procs_priv.MYD\'
  112. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_topic.MYD\' to\'/home/data/mysql/data/mysql/help_topic.MYD\'
  113. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/plugin.MYI\' to\'/home/data/mysql/data/mysql/plugin.MYI\'
  114. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/innodb_index_stats.frm\' to\'/home/data/mysql/data/mysql/innodb_index_stats.frm\'
  115. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_category.frm\' to\'/home/data/mysql/data/mysql/help_category.frm\'
  116. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_transition_type.MYD\' to\'/home/data/mysql/data/mysql/time_zone_transition_type.MYD\'
  117. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/user.frm\' to\'/home/data/mysql/data/mysql/user.frm\'
  118. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/general_log.frm\' to\'/home/data/mysql/data/mysql/general_log.frm\'
  119. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_category.MYD\' to\'/home/data/mysql/data/mysql/help_category.MYD\'
  120. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_relation.frm\' to\'/home/data/mysql/data/mysql/help_relation.frm\'
  121. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/proxies_priv.MYI\' to\'/home/data/mysql/data/mysql/proxies_priv.MYI\'
  122. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/tables_priv.MYD\' to\'/home/data/mysql/data/mysql/tables_priv.MYD\'
  123. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_transition_type.MYI\' to\'/home/data/mysql/data/mysql/time_zone_transition_type.MYI\'
  124. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_transition.MYD\' to\'/home/data/mysql/data/mysql/time_zone_transition.MYD\'
  125. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/general_log.CSM\' to\'/home/data/mysql/data/mysql/general_log.CSM\'
  126. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/proxies_priv.MYD\' to\'/home/data/mysql/data/mysql/proxies_priv.MYD\'
  127. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/ndb_binlog_index.MYI\' to\'/home/data/mysql/data/mysql/ndb_binlog_index.MYI\'
  128. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/slave_master_info.frm\' to \'/home/data/mysql/data/mysql/slave_master_info.frm\'
  129. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_relation.MYI\' to\'/home/data/mysql/data/mysql/help_relation.MYI\'
  130. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/db.MYI\' to \'/home/data/mysql/data/mysql/db.MYI\'
  131. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_leap_second.MYD\' to\'/home/data/mysql/data/mysql/time_zone_leap_second.MYD\'
  132. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_leap_second.frm\' to \'/home/data/mysql/data/mysql/time_zone_leap_second.frm\'
  133. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_name.MYD\' to\'/home/data/mysql/data/mysql/time_zone_name.MYD\'
  134. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/proc.frm\' to \'/home/data/mysql/data/mysql/proc.frm\'
  135. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_relation.MYD\' to\'/home/data/mysql/data/mysql/help_relation.MYD\'
  136. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/proxies_priv.frm\' to \'/home/data/mysql/data/mysql/proxies_priv.frm\'
  137. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/servers.MYI\' to\'/home/data/mysql/data/mysql/servers.MYI\'
  138. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_keyword.MYI\' to\'/home/data/mysql/data/mysql/help_keyword.MYI\'
  139. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/servers.frm\' to\'/home/data/mysql/data/mysql/servers.frm\'
  140. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_transition.frm\' to\'/home/data/mysql/data/mysql/time_zone_transition.frm\'
  141. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_category.MYI\' to\'/home/data/mysql/data/mysql/help_category.MYI\'
  142. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone.frm\' to\'/home/data/mysql/data/mysql/time_zone.frm\'
  143. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/slave_worker_info.ibd\' to\'/home/data/mysql/data/mysql/slave_worker_info.ibd\'
  144. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/innodb_table_stats.frm\' to\'/home/data/mysql/data/mysql/innodb_table_stats.frm\'
  145. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/slow_log.frm\' to\'/home/data/mysql/data/mysql/slow_log.frm\'
  146. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/user.MYD\' to\'/home/data/mysql/data/mysql/user.MYD\'
  147. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/time_zone_name.frm\' to\'/home/data/mysql/data/mysql/time_zone_name.frm\'
  148. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/plugin.MYD\' to\'/home/data/mysql/data/mysql/plugin.MYD\'
  149. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/innodb_table_stats.ibd\' to\'/home/data/mysql/data/mysql/innodb_table_stats.ibd\'
  150. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/columns_priv.MYD\' to\'/home/data/mysql/data/mysql/columns_priv.MYD\'
  151. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/plugin.frm\' to\'/home/data/mysql/data/mysql/plugin.frm\'
  152. innobackupex: Copying \'/data/backups/mysql/20150318/mysql/general_log.CSV\'to \'/home/data/mysql/data/mysql/general_log.CSV\'
  153. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/event.MYD\' to\'/home/data/mysql/data/mysql/event.MYD\'
  154. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/proc.MYD\' to\'/home/data/mysql/data/mysql/proc.MYD\'
  155. innobackupex: Copying\'/data/backups/mysql/20150318/mysql/help_topic.frm\' to\'/home/data/mysql/data/mysql/help_topic.frm\'
  156. innobackupex: Creating directory\'/home/data/mysql/data/sbtest\'
  157. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest3.frm\' to\'/home/data/mysql/data/sbtest/sbtest3.frm\'
  158. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest4.frm\' to\'/home/data/mysql/data/sbtest/sbtest4.frm\'
  159. innobackupex: Copying \'/data/backups/mysql/20150318/sbtest/sbtest6.frm\'to \'/home/data/mysql/data/sbtest/sbtest6.frm\'
  160. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest1.ibd\' to\'/home/data/mysql/data/sbtest/sbtest1.ibd\'
  161. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest8.frm\' to\'/home/data/mysql/data/sbtest/sbtest8.frm\'
  162. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest1.frm\' to\'/home/data/mysql/data/sbtest/sbtest1.frm\'
  163. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest5.ibd\' to \'/home/data/mysql/data/sbtest/sbtest5.ibd\'
  164. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/db.opt\' to\'/home/data/mysql/data/sbtest/db.opt\'
  165. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest8.ibd\' to\'/home/data/mysql/data/sbtest/sbtest8.ibd\'
  166. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest4.ibd\' to\'/home/data/mysql/data/sbtest/sbtest4.ibd\'
  167. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest2.frm\' to\'/home/data/mysql/data/sbtest/sbtest2.frm\'
  168. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest2.ibd\' to\'/home/data/mysql/data/sbtest/sbtest2.ibd\'
  169. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest6.ibd\' to\'/home/data/mysql/data/sbtest/sbtest6.ibd\'
  170. innobackupex: Copying \'/data/backups/mysql/20150318/sbtest/sbtest7.ibd\'to \'/home/data/mysql/data/sbtest/sbtest7.ibd\'
  171. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest7.frm\' to\'/home/data/mysql/data/sbtest/sbtest7.frm\'
  172. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest3.ibd\' to\'/home/data/mysql/data/sbtest/sbtest3.ibd\'
  173. innobackupex: Copying\'/data/backups/mysql/20150318/sbtest/sbtest5.frm\' to\'/home/data/mysql/data/sbtest/sbtest5.frm\'
  174. innobackupex: Creating directory\'/home/data/mysql/data/test\'
  175. innobackupex: Copying\'/data/backups/mysql/20150318/test/t1.ibd\' to\'/home/data/mysql/data/test/t1.ibd\'
  176. innobackupex: Copying\'/data/backups/mysql/20150318/test/t3.frm\' to\'/home/data/mysql/data/test/t3.frm\'
  177. innobackupex: Copying\'/data/backups/mysql/20150318/test/t3.ibd\' to\'/home/data/mysql/data/test/t3.ibd\'
  178. innobackupex: Copying\'/data/backups/mysql/20150318/test/t.ibd\' to \'/home/data/mysql/data/test/t.ibd\'
  179. innobackupex: Copying\'/data/backups/mysql/20150318/test/t1.frm\' to\'/home/data/mysql/data/test/t1.frm\'
  180. innobackupex: Creating directory\'/home/data/mysql/data/user_db\'
  181. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/UC_USER.frm\' to\'/home/data/mysql/data/user_db/UC_USER.frm\'
  182. innobackupex: Copying \'/data/backups/mysql/20150318/user_db/t.frm\'to \'/home/data/mysql/data/user_db/t.frm\'
  183. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/t1.ibd\' to\'/home/data/mysql/data/user_db/t1.ibd\'
  184. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/t3.frm\' to\'/home/data/mysql/data/user_db/t3.frm\'
  185. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/test.frm\' to\'/home/data/mysql/data/user_db/test.frm\'
  186. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/db.opt\' to \'/home/data/mysql/data/user_db/db.opt\'
  187. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/test.ibd\' to\'/home/data/mysql/data/user_db/test.ibd\'
  188. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/test2.ibd\' to\'/home/data/mysql/data/user_db/test2.ibd\'
  189. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/t3.ibd\' to\'/home/data/mysql/data/user_db/t3.ibd\'
  190. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/UC_USER.ibd\' to\'/home/data/mysql/data/user_db/UC_USER.ibd\'
  191. innobackupex: Copying \'/data/backups/mysql/20150318/user_db/test2.frm\'to \'/home/data/mysql/data/user_db/test2.frm\'
  192. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/t.ibd\' to\'/home/data/mysql/data/user_db/t.ibd\'
  193. innobackupex: Copying\'/data/backups/mysql/20150318/user_db/t1.frm\' to\'/home/data/mysql/data/user_db/t1.frm\'
  194.  
  195. innobackupex: Starting to copy InnoDBsystem tablespace
  196. innobackupex: in\'/data/backups/mysql/20150318\'
  197. innobackupex: back to original InnoDB datadirectory \'/home/data/mysql/data\'
  198. innobackupex: Copying \'/data/backups/mysql/20150318/IBdata1\'to \'/home/data/mysql/data/IBdata1\'
  199. innobackupex: Copying\'/data/backups/mysql/20150318/IBdata2\' to \'/home/data/mysql/data/IBdata2\'
  200.  
  201. innobackupex: Starting to copy InnoDB undotablespaces
  202. innobackupex: in\'/data/backups/mysql/20150318\'
  203. innobackupex: back to\'/home/data/mysql/data\'
  204.  
  205. innobackupex: Starting to copy InnoDB logfiles
  206. innobackupex: in\'/data/backups/mysql/20150318\'
  207. innobackupex: back to original InnoDB logdirectory \'/home/data/mysql/data\'
  208. innobackupex: Copying\'/data/backups/mysql/20150318/ib_logfile1\' to\'/home/data/mysql/data/ib_logfile1\'
  209. innobackupex: Copying\'/data/backups/mysql/20150318/ib_logfile2\' to\'/home/data/mysql/data/ib_logfile2\'
  210. innobackupex: Copying \'/data/backups/mysql/20150318/ib_logfile0\'to \'/home/data/mysql/data/ib_logfile0\'
  211. innobackupex: Finished copying back files.
  212.  
  213. 150318 20:18:50 innobackupex: completed
  214. [root@data01 ~]#


 

赋予mysql用户访问权限,然后重启mysql数据服务,查看表t1是否已经恢复了,如下所示:


  1. [root@data01 ~]# chown -R mysql.mysql/home/data/mysql/data/
  2. [root@data01 ~]#
  3. [root@data01 ~]# service mysqld5612 start
  4. Starting MySQL.....
  5. [root@data01 ~]# mysql
  6. Welcome to the MySQL monitor. Commands end with ; or \\g.
  7. Your MySQL connection id is 2
  8. Server version: 5.6.12-log Sourcedistribution
  9.  
  10. Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved.
  11.  
  12. Oracle is a registered trademark of OracleCorporation and/or its
  13. affiliates. Other names may be trademarksof their respective
  14. owners.
  15.  
  16. Type \'help;\' or \'\\h\' for help. Type \'\\c\' toclear the current input statement.
  17.  
  18. mysql> use test;
  19. Database changed
  20. mysql> select * from t1;
  21. +---+----+
  22. | a | b |
  23. +---+----+
  24. | 5 | aa |
  25. +---+----+
  26. 1 row in set (0.08 sec)


PS:这里是比较简单的测试,更多的功能以及参数,大家可以去使用innobackupex --help|more 查看需要自己要的参数,然后自己去尝试,也可以参考官网的例子:http://www.percona.com/doc/percona-xtrabackup/2.2/how-tos.html


8应用场景总结

       xtrabackup备份恢复工具比较适合数据增长型数据库。对于数据增长型的库,由于数据的增长导致数据备份和恢复的空间和时间上的压力较大。而xtrabackup也有增量备份的功能,在短时间内可以通过进行增量备份来保证数据的安全性。而长期来看,仍然需要间断性的进行全库备份。此外,由于xtrabackup对innodb的数据库不进行锁定,因此对要求不影响线上服务的数据备份和恢复较适合。

       而对于数据量无明显增长,且更新为主的数据更新型数据库,xtrabackup显得过于复杂。xtrabackup操作反而不如mysqldump的性能高。

  ----------------------------------------------------------------------------------------------------------------
<版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!>
原博客地址:    http://blog.itpub.net/26230597/viewspace-1465772/
原作者:黄杉(mchdba)
----------------------------------------------------------------------------------------------------------------

阅读(14442) | 评论(1) | 转发(5) |
给主人留下些什么吧!~~

lyhabc2017-03-05 13:23:21

解释很详细

评论热议
请登录后评论。

登录 注册