IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK！".
161213 12:27:13 version_check Connecting to MySQL server with DSN "dbi:mysql:;mysql_read_default_group=xtrabackup" as "root" （using password: YES）. Failed to connect to MySQL server: DBI connect（";mysql_read_default_group=xtrabackup","root",...） failed: Can"t connect to local MySQL server through socket "/var/lib/mysql/mysql.sock" （2） at - line 1314. 161213 12:27:13 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: （null） Using server version 5.7.16 innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux （x86_64） （revision id: e41c0be） xtrabackup: uses posix_fadvise（）. xtrabackup: cd to /usr/local/mysql/data xtrabackup: open files limit requested 0, set to 1024 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 = 50331648 InnoDB: Number of pools: 1 161213 12:27:13 >> log scanned up to （2671643） xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0 161213 12:27:13 [01] Copying ./ibdata1 to /mysqlbackup/2016-12-13_12-27-13/ibdata1 161213 12:27:14 >> log scanned up to （2671643） 161213 12:27:15 [01] ...done 161213 12:27:15 >> log scanned up to （2671643） 161213 12:27:15 [01] Copying ./mysql/plugin.ibd to /mysqlbackup/2016-12-13_12-27-13/mysql/plugin.ibd 161213 12:27:15 [01] ...done ...... 161213 12:27:16 [01] Copying ./wl/zx.ibd to /mysqlbackup/2016-12-13_12-27-13/wl/zx.ibd 161213 12:27:16 [01] ...done 161213 12:27:16 >> log scanned up to （2671643） 161213 12:27:16 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 161213 12:27:16 Executing FLUSH TABLES WITH READ LOCK... 161213 12:27:16 Starting to backup non-InnoDB tables and files 161213 12:27:16 [01] Copying ./mysql/db.opt to /mysqlbackup/2016-12-13_12-27-13/mysql/db.opt 161213 12:27:16 [01] ...done ...... 161213 12:27:18 [01] Copying ./wl/wl.frm to /mysqlbackup/2016-12-13_12-27-13/wl/wl.frm 161213 12:27:18 [01] ...done 161213 12:27:18 [01] Copying ./wl/zx.frm to /mysqlbackup/2016-12-13_12-27-13/wl/zx.frm 161213 12:27:18 [01] ...done 161213 12:27:18 Finished backing up non-InnoDB tables and files 161213 12:27:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point （for incremental）: "2671634" xtrabackup: Stopping log copying thread. .161213 12:27:18 >> log scanned up to （2671643）
161213 12:27:18 Executing UNLOCK TABLES 161213 12:27:18 All tables unlocked 161213 12:27:18 [00] Copying ib_buffer_pool to /mysqlbackup/2016-12-13_12-27-13/ib_buffer_pool 161213 12:27:18 [00] ...done 161213 12:27:18 Backup created in directory "/mysqlbackup/2016-12-13_12-27-13" 161213 12:27:18 [00] Writing backup-my.cnf 161213 12:27:18 [00] ...done 161213 12:27:18 [00] Writing xtrabackup_info 161213 12:27:18 [00] ...done xtrabackup: Transaction log of lsn （2671634） to （2671643） was copied. 161213 12:27:19 completed OK！ #在指定的目录下生成了一个时间目录 [root@rhel7 ~]# ls -l /mysqlbackup/ total 4 drwxr-x---. 7 root root 4096 Dec 13 12:27 2016-12-13_12-27-13 [root@rhel7 ~]# du -sm /mysqlbackup/* 90 /mysqlbackup/2016-12-13_12-27-13注意：innobackupex会去读取my.cnf文件中的[mysqld]和[xtrabackup]部分，也可以使用--defaults-file指定参数文件。2）恢复全备的数据备份的数据不能直接使用，需要先做prepare操作（可以理解为应用日志的过程，commit的事务提交，未commit的事务回滚）#使用--apply-log应用日志， [root@rhel7 ~]# innobackupex --apply-log /mysqlbackup/2016-12-13_12-27-13/ 161213 12:36:24 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！".
innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux （x86_64） （revision id: e41c0be） xtrabackup: cd to /mysqlbackup/2016-12-13_12-27-13/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=（2671634） 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 = 8388608 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 = 8388608 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool （set by --use-memory parameter） InnoDB: PUNCH HOLE support not available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize（） is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Not using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 2671634 InnoDB: Doing recovery: scanned up to log sequence number 2671643 （0%） InnoDB: Doing recovery: scanned up to log sequence number 2671643 （0%） InnoDB: Database was not shutdown normally！ InnoDB: Starting crash recovery. InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file "./ibtmp1" size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File "./ibtmp1" size is now 12 MB. InnoDB: 96 redo rollback segment（s） found. 1 redo rollback segment（s） are active. InnoDB: 32 non-redo rollback segment（s） are active. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number 2671643
xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2671671 InnoDB: Number of pools: 1 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 = 50331648 InnoDB: PUNCH HOLE support not available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize（） is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Not using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Setting log file ./ib_logfile101 size to 48 MB InnoDB: Setting log file ./ib_logfile1 size to 48 MB InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 InnoDB: New log files created, LSN=2671671 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 2672140 InnoDB: Doing recovery: scanned up to log sequence number 2672149 （0%） InnoDB: Doing recovery: scanned up to log sequence number 2672149 （0%） InnoDB: Database was not shutdown normally！ InnoDB: Starting crash recovery. InnoDB: Removed temporary tablespace data file: "ibtmp1" InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file "./ibtmp1" size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File "./ibtmp1" size is now 12 MB. InnoDB: 96 redo rollback segment（s） found. 1 redo rollback segment（s） are active. InnoDB: 32 non-redo rollback segment（s） are active. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number 2672149 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 2672168 161213 12:36:30 completed OK！ #查看prepare后数据目录的大小 [root@rhel7 ~]# ls -l /mysqlbackup/ total 4 drwxr-x---. 7 root root 4096 Dec 13 12:36 2016-12-13_12-27-13 [root@rhel7 ~]# du -sm /mysqlbackup/* 206 /mysqlbackup/2016-12-13_12-27-13prepare完成后恢复数据到原MySQL的数据目录注意：MySQL的数据目录必须为空，MySQL服务也必须停止，否则恢复会报错（除非恢复部分备份）#创建一个测试库zx [root@rhel7 ~]# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.16 Source distribution
Copyright （c） 2000, 2016, 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 databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | wl | +--------------------+ 6 rows in set （0.10 sec）
mysql> exit Bye #停止MySQL数据库并创建新的data目录 [root@rhel7 ~]# service mysqld stop Shutting down MySQL.. SUCCESS！ [root@rhel7 ~]# cd $MYSQL_HOME [root@rhel7 mysql]# ls bin COPYING data docs include lib man mysql-test README share support-files [root@rhel7 mysql]# mv data data_bak [root@rhel7 mysql]# mkdir data #恢复数据 [root@rhel7 mysql]# innobackupex --copy-back /mysqlbackup/2016-12-13_12-27-13/ 161213 13:03:50 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK！".
innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux （x86_64） （revision id: e41c0be） 161213 13:03:50 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0 161213 13:03:50 [01] ...done 161213 13:03:51 [01] Copying ib_logfile1 to /usr/local/mysql/data/ib_logfile1 161213 13:03:51 [01] ...done 161213 13:03:52 [01] Copying ibdata1 to /usr/local/mysql/data/ibdata1 161213 13:03:54 [01] ...done 161213 13:03:55 [01] Copying ./mysql/plugin.ibd to /usr/local/mysql/data/mysql/plugin.ibd 161213 13:03:55 [01] ...done 161213 13:03:55 [01] Copying ./mysql/servers.ibd to /usr/local/mysql/data/mysql/servers.ibd 161213 13:03:55 [01] ...done 161213 13:03:55 [01] Copying ./mysql/help_topic.ibd to /usr/local/mysql/data/mysql/help_topic.ibd 161213 13:03:55 [01] ...done 161213 13:03:55 [01] Copying ./mysql/help_category.ibd to /usr/local/mysql/data/mysql/help_category.ibd 161213 13:03:55 [01] ...done ....... 161213 13:03:57 [01] Copying ./ibtmp1 to /usr/local/mysql/data/ibtmp1 161213 13:03:57 [01] ...done 161213 13:03:57 completed OK！ #修改data目录下的权限 [root@rhel7 mysql]# chown mysql:mysql -R data #启动MySQL数据库查看恢复是否成功 [root@rhel7 mysql]# service mysqld start Starting MySQL. SUCCESS！ [root@rhel7 mysql]# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.16 Source distribution
Copyright （c） 2000, 2016, 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 databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | wl | +--------------------+ 6 rows in set （0.00 sec） #数据恢复成功，测试数据库zx不见了。4.增量备份的操作步骤1）创建备份，增量备份需要有一个全量备份做基础，所以先做一个全量备份再做增量备份#创建全备 [root@rhel7 mysql]# innobackupex --user=root --password=123456 /mysqlbackup/ [root@rhel7 mysql]# ls -l /mysqlbackup/ total 4 drwxr-x---. 8 root root 4096 Dec 13 13:10 2016-12-13_13-10-48 #删除一个测试库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | wl | | zx | +--------------------+ 7 rows in set （0.00 sec）
mysql> drop database zx; Query OK, 0 rows affected （0.07 sec） #创建增量备份（基于全备） [root@rhel7 mysql]# innobackupex --user=root --password=123456 --incremental /mysqlbackup/ --incremental-basedir=/mysqlbackup/2016-12-13_13-10-48/ ...... [root@rhel7 mysql]# ls -l /mysqlbackup/ total 8 drwxr-x---. 8 root root 4096 Dec 13 13:10 2016-12-13_13-10-48 drwxr-x---. 7 root root 4096 Dec 13 13:16 2016-12-13_13-16-17 [root@rhel7 mysql]# du -sm /mysqlbackup/* 90 /mysqlbackup/2016-12-13_13-10-48 3 /mysqlbackup/2016-12-13_13-16-17 #删除一个测试库 mysql> drop database wl; Query OK, 0 rows affected （0.07 sec） #创建增量备份（基于上次增量备份） [root@rhel7 mysql]# innobackupex --user=root --password=123456 --incremental /mysqlbackup/ --incremental-basedir=/mysqlbackup/2016-12-13_13-16-17/ ...... [root@rhel7 mysql]# ls -l /mysqlbackup/ total 12 drwxr-x---. 8 root root 4096 Dec 13 13:10 2016-12-13_13-10-48 drwxr-x---. 7 root root 4096 Dec 13 13:16 2016-12-13_13-16-17 drwxr-x---. 6 root root 4096 Dec 13 13:18 2016-12-13_13-18-30 [root@rhel7 mysql]# du -sm /mysqlbackup/* 90 /mysqlbackup/2016-12-13_13-10-48 3 /mysqlbackup/2016-12-13_13-16-17 4 /mysqlbackup/2016-12-13_13-18-302）恢复增量备份同全量备份一样恢复里也需要prepare#全量备份的prepare --redo-only表示只提交commit的事务 [root@rhel7 mysql]# innobackupex --apply-log --redo-only /mysqlbackup/2016-12-13_13-10-48/ #第一次增量备份prepare [root@rhel7 mysql]# innobackupex --apply-log --redo-only /mysqlbackup/2016-12-13_13-10-48/ --incremental-dir=/mysqlbackup/2016-12-13_13-16-17/ #最后一次增量备份prepare 不再需要redo-only参数 [root@rhel7 mysql]# innobackupex --apply-log /mysqlbackup/2016-12-13_13-10-48/ --incremental-dir=/mysqlbackup/2016-12-13_13-18-30/ #停止MySQL服务创建新的data目录 [root@rhel7 mysql]# service mysqld stop Shutting down MySQL.. SUCCESS！ [root@rhel7 mysql]# rm -rf data [root@rhel7 mysql]# mkdir data #恢复数据 [root@rhel7 mysql]# innobackupex --copy-back /mysqlbackup/2016-12-13_13-10-48/ #修改data目录权限 [root@rhel7 mysql]# chown mysql:mysql -R data #启动MySQL服务并验证 [root@rhel7 mysql]# service mysqld start Starting MySQL... SUCCESS！ [root@rhel7 mysql]# mysql -uroot -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.16 Source distribution
Copyright （c） 2000, 2016, 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 databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | wl | +--------------------+ 6 rows in set （0.00 sec）
mysql> show tables in wl; Empty set （0.00 sec） #测试库zx没有了，wl库还在，但是库里的表已经没有了。前边只是提供了简单的恢复方法，如果数据库备份之后运行了一段时间，数据库宕机了，如果只恢复备份的数据那就会丢失一部分数据。那这部分数据要怎么恢复呢。答案就是应用binlog。在启用binlog的数据库中，备份完成后日志中会输出关于binlog的信息，生成的备份目录中也会有一个xtrabackup_binlog_info文件保存备份时的binlog位置。161214 10:24:34 Executing UNLOCK TABLES 161214 10:24:34 All tables unlocked 161214 10:24:34 [00] Copying ib_buffer_pool to /mysqlbackup//2016-12-14_10-24-27/ib_buffer_pool 161214 10:24:34 [00] ...done 161214 10:24:34 Backup created in directory "/mysqlbackup//2016-12-14_10-24-27" #binlog位置 MySQL binlog position: filename "mysql-bin.000001", position "154" 161214 10:24:34 [00] Writing backup-my.cnf 161214 10:24:34 [00] ...done 161214 10:24:34 [00] Writing xtrabackup_info 161214 10:24:34 [00] ...done xtrabackup: Transaction log of lsn （2677865） to （2677874） was copied. 161214 10:24:34 completed OK！
[root@rhel7 2016-12-14_10-24-27]# ls -l total 77876 -rw-r-----. 1 root root 425 Dec 14 10:24 backup-my.cnf -rw-r-----. 1 root root 307 Dec 14 10:24 ib_buffer_pool -rw-r-----. 1 root root 79691776 Dec 14 10:24 ibdata1 drwxr-x---. 2 root root 4096 Dec 14 10:24 mysql drwxr-x---. 2 root root 8192 Dec 14 10:24 performance_schema drwxr-x---. 2 root root 8192 Dec 14 10:24 sys drwxr-x---. 2 root root 87 Dec 14 10:24 test -rw-r-----. 1 root root 21 Dec 14 10:24 xtrabackup_binlog_info -rw-r-----. 1 root root 113 Dec 14 10:24 xtrabackup_checkpoints -rw-r-----. 1 root root 469 Dec 14 10:24 xtrabackup_info -rw-r-----. 1 root root 2560 Dec 14 10:24 xtrabackup_logfile #记录binlog位置的文件 [root@rhel7 2016-12-14_10-24-27]# cat xtrabackup_binlog_info mysql-bin.000001 154有了这个binlog日志位置就可以应用binlog恢复备份到宕机时间的数据mysqlbinlog --start-position=154 mysql-bin.000001 | mysql -uroot -p123456更多XtraBackup相关教程见以下内容：MySQL管理之使用XtraBackup进行热备 http://www.linuxidc.com/Linux/2014-04/99671.htm使用Xtrabackup进行MySQL备份 http://www.linuxidc.com/Linux/2016-11/137734.htmMySQL开源备份工具Xtrabackup备份部署 http://www.linuxidc.com/Linux/2013-06/85627.htmMySQL Xtrabackup备份和恢复 http://www.linuxidc.com/Linux/2011-12/50275.htmPercona Xtrabackup 安装 http://www.linuxidc.com/Linux/2016-11/137735.htm使用Xtrabackup进行MySQL数据库全备和全备还原 http://www.linuxidc.com/Linux/2016-11/137736.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址
易网时代-编程资源站