重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
PerconaXtraBackup2.4xtrabackup全量、增量备份恢复流程
xtrabackup常用参数
--backup 创建备份并将备份存放到--target-dir目录
--defaults-file=[MY.CNF] 配置文件的路径
--target-dir=DIRECTORY 指定备份存放的路径。如果目录不存在，xtrabackup会创建相应的目录
--user 备份账户的用户名
--password 备份账户的密码
--datadir=DIRECTORY 数据目录的路径
--copy-back 将之前做的备份拷贝回他们的起始目录
--xtrabackup全量备份
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/MySQL/ --target-dir=/backup/20160722 --user root
--password 'System#2013'
.....
160721 20:30:13 [01] Copying ./mysql/user.frm to /backup/20160722/mysql/user.frm
160721 20:30:13 [01] ...done
160721 20:30:14 [01] Copying ./mysql/proxies_priv.frm to /backup/20160722/mysql/proxies_priv.frm
160721 20:30:14 [01] ...done
160721 20:30:14 [01] Copying ./mysql/proc.MYD to /backup/20160722/mysql/proc.MYD
160721 20:30:14 [01] ...done
160721 20:30:14 [01] Copying ./mysql/help_relation.frm to /backup/20160722/mysql/help_relation.frm
160721 20:30:14 [01] ...done
160721 20:30:14 Finished backing up non-InnoDB tables and files
160721 20:30:14 [00] Writing xtrabackup_binlog_info
160721 20:30:14 [00] ...done
160721 20:30:14 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186651619'
xtrabackup: Stopping log copying thread.
.160721 20:30:14 >> log scanned up to (186651628)
160721 20:30:14 Executing UNLOCK TABLES
160721 20:30:14 All tables unlocked
160721 20:30:14 [00] Copying ib_buffer_pool to /backup/20160722/ib_buffer_pool
160721 20:30:14 [00] ...done
160721 20:30:14 Backup created in directory '/backup/20160722'
MySQL binlog position: filename 'production-bin.000002', position '194', GTID of the last change '026dc034-4dc2-11e6-a9cb-000c29631605:1-17,
cf291e84-2c89-11e6-b6f0-000c29631605:1-44'
160721 20:30:14 [00] Writing backup-my.cnf
160721 20:30:14 [00] ...done
160721 20:30:14 [00] Writing xtrabackup_info
160721 20:30:14 [00] ...done
xtrabackup: Transaction log of lsn (186651619) to (186651628) was copied.
160721 20:30:14 completed OK!
--xtrabackup全量恢复
-删除myisam表
mysql> show create table fire.t10\G
*************************** 1. row ***************************
Table: t10
Create Table: CREATE TABLE `t10` (
`f1` int(11) DEFAULT NULL,
`gc` int(11) GENERATED ALWAYS AS ((`f1` + 1)) STORED,
KEY `gc` (`gc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 186652172
InnoDB: Doing recovery: scanned up to log sequence number 186652181 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 186652181 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 3212, file name production-bin.000001
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: page_cleaner: 1000ms intended loop took 10267ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.11 started; log sequence number 186652181
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 186652200
160721 23:40:39 completed OK!
-第2次准备备份，创建新的日志文件，以使恢复更快；如果不第2次执行--prepare，xtrabackup在restore时会自动创建日志文件。[root@localhost
20160722]#
/install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup
--defaults-file=/etc/my.cnf --prepare --target-dir=/backup/20160722/
.....
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: 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: 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.11 started; log sequence number 186652219
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 186652238
160721 23:44:05 completed OK!
[root@localhost 20160722]# ls -trl
total 196656
drwxr-x---. 2 root root 4096 Jul 21 20:30 fire
drwxr-x---. 2 root root 12288 Jul 21 20:30 sys
drwxr-x---. 2 root root 4096 Jul 21 20:30 performance_schema
drwxr-x---. 2 root root 4096 Jul 21 20:30 mysql
-rw-r-----. 1 root root 111 Jul 21 20:30 xtrabackup_binlog_info
-rw-r-----. 1 root root 785 Jul 21 20:30 ib_buffer_pool
-rw-r-----. 1 root root 428 Jul 21 20:30 backup-my.cnf
-rw-r-----. 1 root root 677 Jul 21 20:30 xtrabackup_info
-rw-r-----. 1 root root 8388608 Jul 21 23:40 xtrabackup_logfile
-rw-r-----. 1 root root 50331648 Jul 21 23:40 ib_logfile1
-rw-r--r--. 1 root root 27 Jul 21 23:44 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root 117 Jul 21 23:44 xtrabackup_checkpoints
-rw-r-----. 1 root root 12582912 Jul 21 23:44 ibtmp1
-rw-r-----. 1 root root 50331648 Jul 21 23:44 ib_logfile0
-rw-r-----. 1 root root 79691776 Jul 21 23:44 ibdata1
-执行restore操作
[root@localhost 20160722]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' \
> ./ /var/lib/mysql
sending incremental file list
./
backup-my.cnf
ib_buffer_pool
ib_logfile0
ib_logfile1
ibdata1
ibtmp1
xtrabackup_binlog_info
xtrabackup_binlog_pos_innodb
xtrabackup_info
fire/
fire/db.opt
fire/emp.frm
fire/emp.ibd
fire/emp2.frm
fire/emp2.ibd
fire/t1.frm
fire/t1.ibd
fire/t10.MYD
fire/t10.MYI
fire/t10.frm
sys/x@0024user_summary_by_statement_type.frm
sys/x@0024wait_classes_global_by_avg_latency.frm
sys/x@0024wait_classes_global_by_latency.frm
sys/x@0024waits_by_host_by_latency.frm
sys/x@0024waits_by_user_by_latency.frm
sys/x@0024waits_global_by_latency.frm
sent 324679831 bytes received 5769 bytes 22392110.34 bytes/sec
total size is 324618115 speedup is 1.00
-更改数据目录权限
[root@localhost 20160722]# chown -R mysql:mysql /var/lib/mysql/
-启动数据库
[root@localhost 20160722]# service mysqld start
Starting mysqld: [ OK ]
-查看恢复的表中数据
mysql>
select table_catalog,table_schema,table_name,table_type,engine,version
from information_schema.tables where table_name='t10';
+---------------+--------------+------------+------------+--------+---------+
| table_catalog | table_schema | table_name | table_type | engine | version |
+---------------+--------------+------------+------------+--------+---------+
| def | fire | t10 | BASE TABLE | MyISAM | 10 |
+---------------+--------------+------------+------------+--------+---------+
1 row in set (0.00 sec)
mysql> select * from fire.t10;
+------+------+
| f1 | gc |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
+------+------+
5 rows in set (0.00 sec)
--xtrabackup增量备份
-执行基础全备
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/mysql/ --target-dir=/backup/20160722 --user root
--password 'System#2013'
--创建测试表并插入数据
mysql> create table dept(id int(5),d_name varchar(15),loc varchar(15));
Query OK, 0 rows affected (0.42 sec)
mysql> insert into dept values(10,'Research','ShangHai');
Query OK, 1 row affected (0.06 sec)
mysql> insert into dept values(20,'Maintenance','GuangZhou');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-执行增量备份一
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/20160722/ --user root --password 'System#2013'
160722 00:26:18 [00] ...done
160722 00:26:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186658935'
xtrabackup: Stopping log copying thread.
.160722 00:26:18 >> log scanned up to (186658944)
160722 00:26:18 Executing UNLOCK TABLES
160722 00:26:18 All tables unlocked
160722 00:26:18 [00] Copying ib_buffer_pool to /backup/inc1/ib_buffer_pool
160722 00:26:18 [00] ...done
160722 00:26:18 Backup created in directory '/backup/inc1'
MySQL binlog position: filename 'production-bin.000001', position '918', GTID of the last change '026dc034-4dc2-11e6-a9cb-000c29631605:1-17,
cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
fd17ba52-4fd8-11e6-bd80-000c29631605:1-3'
160722 00:26:18 [00] Writing backup-my.cnf
160722 00:26:18 [00] ...done
160722 00:26:18 [00] Writing xtrabackup_info
160722 00:26:18 [00] ...done
xtrabackup: Transaction log of lsn (186658935) to (186658944) was copied.
160722 00:26:18 completed OK!
--创建测试表并插入数据
mysql> create table test2 like test;
Query OK, 0 rows affected (0.95 sec)
mysql> insert into test2 select * from test;
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0
-执行增量备份二
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 --user root --password 'System#2013'
160722 00:33:31 [01] ...done
160722 00:33:31 [01] Copying ./mysql/proc.MYD to /backup/inc2/mysql/proc.MYD
160722 00:33:31 [01] ...done
160722 00:33:31 [01] Copying ./mysql/help_relation.frm to /backup/inc2/mysql/help_relation.frm
160722 00:33:31 [01] ...done
160722 00:33:31 Finished backing up non-InnoDB tables and files
160722 00:33:31 [00] Writing xtrabackup_binlog_info
160722 00:33:31 [00] ...done
160722 00:33:31 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186672619'
xtrabackup: Stopping log copying thread.
.160722 00:33:31 >> log scanned up to (186672628)
160722 00:33:31 Executing UNLOCK TABLES
160722 00:33:31 All tables unlocked
160722 00:33:31 [00] Copying ib_buffer_pool to /backup/inc2/ib_buffer_pool
160722 00:33:31 [00] ...done
160722 00:33:31 Backup created in directory '/backup/inc2'
MySQL binlog position: filename 'production-bin.000001', position '1421', GTID of the last change '026dc034-4dc2-11e6-a9cb-000c29631605:1-17,
cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
fd17ba52-4fd8-11e6-bd80-000c29631605:1-5'
160722 00:33:31 [00] Writing backup-my.cnf
160722 00:33:31 [00] ...done
160722 00:33:31 [00] Writing xtrabackup_info
160722 00:33:31 [00] ...done
xtrabackup: Transaction log of lsn (186672619) to (186672628) was copied.
160722 00:33:31 completed OK!
--xtrabackup增量恢复
-准备基础全备
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/20160722/
xtrabackup: innodb_log_file_size = 50331648
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
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: 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: xtrabackup: Last MySQL binlog file position 3212, file name production-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 186652247
InnoDB: Number of pools: 1
160722 01:13:30 completed OK!
-回滚基础全备的数据到第1个增量备份
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/20160722/ --incremental-dir=/backup/inc1/
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/db.MYD to ./mysql/db.MYD
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/general_log.CSV to ./mysql/general_log.CSV
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/help_category.frm to ./mysql/help_category.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/innodb_table_stats.frm to ./mysql/innodb_table_stats.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/slave_master_info.frm to ./mysql/slave_master_info.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/func.MYD to ./mysql/func.MYD
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/user.frm to ./mysql/user.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/proxies_priv.frm to ./mysql/proxies_priv.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/proc.MYD to ./mysql/proc.MYD
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/help_relation.frm to ./mysql/help_relation.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [00] Copying /backup/inc1//xtrabackup_binlog_info to ./xtrabackup_binlog_info
160722 01:16:56 [00] ...done
160722 01:16:56 [00] Copying /backup/inc1//xtrabackup_info to ./xtrabackup_info
160722 01:16:56 [00] ...done
160722 01:16:56 completed OK!
-回滚基础全备的数据到第2个增量备份
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/20160722/ --incremental-dir=/backup/inc2/
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/server_cost.frm to ./mysql/server_cost.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/db.MYD to ./mysql/db.MYD
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/general_log.CSV to ./mysql/general_log.CSV
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/help_category.frm to ./mysql/help_category.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/innodb_table_stats.frm to ./mysql/innodb_table_stats.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/slave_master_info.frm to ./mysql/slave_master_info.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/func.MYD to ./mysql/func.MYD
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/user.frm to ./mysql/user.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/proxies_priv.frm to ./mysql/proxies_priv.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/proc.MYD to ./mysql/proc.MYD
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/help_relation.frm to ./mysql/help_relation.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [00] Copying /backup/inc2//xtrabackup_binlog_info to ./xtrabackup_binlog_info
160722 01:19:13 [00] ...done
160722 01:19:13 [00] Copying /backup/inc2//xtrabackup_info to ./xtrabackup_info
160722 01:19:13 [00] ...done
160722 01:19:13 completed OK!
-准备全部的备份，创建新的日志文件
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup/20160722/
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: Page [page id: space=0, page number=9] log sequence number 186659641 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=0, page number=10] log sequence number 186659944 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=0, page number=12] log sequence number 186662606 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=13, page number=3] log sequence number 186669950 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: 5.7.11 started; log sequence number 186652247
InnoDB: Page [page id: space=82, page number=0] log sequence number 186655532 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=83, page number=0] log sequence number 186663700 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 1421, file name production-bin.000001
xtrabackup: error: The transaction log file is corrupted.
xtrabackup: error: The log was not applied to the intended LSN!
xtrabackup: Log applied to lsn 186652247
xtrabackup: The intended lsn is 186672619
-停止数据库，并删除数据文件下面的所有文件
[root@localhost 20160722]# cd /var/lib/mysql
[root@localhost mysql]# rm -rf *
[root@localhost 20160722]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' \
> ./ /var/lib/mysql
sending incremental file list
./
backup-my.cnf
ib_buffer_pool
ib_logfile0
ib_logfile1
ibdata1
sys/x@0024schema_tables_with_full_table_scans.frm
sys/x@0024session.frm
sys/x@0024statement_analysis.frm
sys/x@0024statements_with_errors_or_warnings.frm
sys/x@0024statements_with_full_table_scans.frm
sys/x@0024statements_with_runtimes_in_95th_percentile.frm
sys/x@0024statements_with_sorting.frm
sys/x@0024statements_with_temp_tables.frm
sys/x@0024user_summary.frm
sys/x@0024user_summary_by_file_io.frm
sys/x@0024user_summary_by_file_io_type.frm
sys/x@0024user_summary_by_stages.frm
sys/x@0024user_summary_by_statement_latency.frm
sys/x@0024user_summary_by_statement_type.frm
sys/x@0024wait_classes_global_by_avg_latency.frm
sys/x@0024wait_classes_global_by_latency.frm
sys/x@0024waits_by_host_by_latency.frm
sys/x@0024waits_by_user_by_latency.frm
sys/x@0024waits_global_by_latency.frm
sent 324910462 bytes received 5845 bytes 24067874.59 bytes/sec
total size is 324848462 speedup is 1.00
-更改数据目录的权限
--启动数据库
[root@localhost mysql]# service mysqld start
Starting mysqld: [ OK ]
mysql> select * from dept;
+------+-------------+-----------+
| id | d_name | loc |
+------+-------------+-----------+
| 10 | Research | ShangHai |
| 20 | Maintenance | GuangZhou |
+------+-------------+-----------+
2 rows in set (0.00 sec)
mysql> select * from test2;
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 2 | Allice | Hanks |
| 5 | David | Beckham |
| 3 | Lily | Weber |
| 4 | Lucy | Willis |
| 1 | Terry | John |
+----+-----------+------------+
5 rows in set (0.00 sec)
分享文章：PerconaXtraBackup2.4xtrabackup全量、增量备份恢复流程
分享链接：http://cqcxhl.com/article/pjdpgh.html
--backup 创建备份并将备份存放到--target-dir目录
--defaults-file=[MY.CNF] 配置文件的路径
--target-dir=DIRECTORY 指定备份存放的路径。如果目录不存在，xtrabackup会创建相应的目录
--user 备份账户的用户名
--password 备份账户的密码
--datadir=DIRECTORY 数据目录的路径
--copy-back 将之前做的备份拷贝回他们的起始目录
--xtrabackup全量备份
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/MySQL/ --target-dir=/backup/20160722 --user root
--password 'System#2013'
.....
160721 20:30:13 [01] Copying ./mysql/user.frm to /backup/20160722/mysql/user.frm
160721 20:30:13 [01] ...done
160721 20:30:14 [01] Copying ./mysql/proxies_priv.frm to /backup/20160722/mysql/proxies_priv.frm
160721 20:30:14 [01] ...done
160721 20:30:14 [01] Copying ./mysql/proc.MYD to /backup/20160722/mysql/proc.MYD
160721 20:30:14 [01] ...done
160721 20:30:14 [01] Copying ./mysql/help_relation.frm to /backup/20160722/mysql/help_relation.frm
160721 20:30:14 [01] ...done
160721 20:30:14 Finished backing up non-InnoDB tables and files
160721 20:30:14 [00] Writing xtrabackup_binlog_info
160721 20:30:14 [00] ...done
160721 20:30:14 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186651619'
xtrabackup: Stopping log copying thread.
.160721 20:30:14 >> log scanned up to (186651628)
160721 20:30:14 Executing UNLOCK TABLES
160721 20:30:14 All tables unlocked
160721 20:30:14 [00] Copying ib_buffer_pool to /backup/20160722/ib_buffer_pool
160721 20:30:14 [00] ...done
160721 20:30:14 Backup created in directory '/backup/20160722'
MySQL binlog position: filename 'production-bin.000002', position '194', GTID of the last change '026dc034-4dc2-11e6-a9cb-000c29631605:1-17,
cf291e84-2c89-11e6-b6f0-000c29631605:1-44'
160721 20:30:14 [00] Writing backup-my.cnf
160721 20:30:14 [00] ...done
160721 20:30:14 [00] Writing xtrabackup_info
160721 20:30:14 [00] ...done
xtrabackup: Transaction log of lsn (186651619) to (186651628) was copied.
160721 20:30:14 completed OK!
--xtrabackup全量恢复
-删除myisam表
mysql> show create table fire.t10\G
*************************** 1. row ***************************
Table: t10
Create Table: CREATE TABLE `t10` (
`f1` int(11) DEFAULT NULL,
`gc` int(11) GENERATED ALWAYS AS ((`f1` + 1)) STORED,
KEY `gc` (`gc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> drop table fire.t10;
Query OK, 0 rows affected (0.21 sec)
-第1次准备备份，使备份的数据文件一致
[root@localhost
20160722]#
/install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup
--defaults-file=/etc/my.cnf --prepare --target-dir=/backup/20160722/Query OK, 0 rows affected (0.21 sec)
-第1次准备备份，使备份的数据文件一致
.....
InnoDB: New log files created, LSN=186651663InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 186652172
InnoDB: Doing recovery: scanned up to log sequence number 186652181 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 186652181 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 3212, file name production-bin.000001
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: page_cleaner: 1000ms intended loop took 10267ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.11 started; log sequence number 186652181
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 186652200
160721 23:40:39 completed OK!
-第2次准备备份，创建新的日志文件，以使恢复更快；如果不第2次执行--prepare，xtrabackup在restore时会自动创建日志文件。
.....
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: 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: 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.11 started; log sequence number 186652219
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 186652238
160721 23:44:05 completed OK!
total 196656
drwxr-x---. 2 root root 4096 Jul 21 20:30 fire
drwxr-x---. 2 root root 12288 Jul 21 20:30 sys
drwxr-x---. 2 root root 4096 Jul 21 20:30 performance_schema
drwxr-x---. 2 root root 4096 Jul 21 20:30 mysql
-rw-r-----. 1 root root 111 Jul 21 20:30 xtrabackup_binlog_info
-rw-r-----. 1 root root 785 Jul 21 20:30 ib_buffer_pool
-rw-r-----. 1 root root 428 Jul 21 20:30 backup-my.cnf
-rw-r-----. 1 root root 677 Jul 21 20:30 xtrabackup_info
-rw-r-----. 1 root root 8388608 Jul 21 23:40 xtrabackup_logfile
-rw-r-----. 1 root root 50331648 Jul 21 23:40 ib_logfile1
-rw-r--r--. 1 root root 27 Jul 21 23:44 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root 117 Jul 21 23:44 xtrabackup_checkpoints
-rw-r-----. 1 root root 12582912 Jul 21 23:44 ibtmp1
-rw-r-----. 1 root root 50331648 Jul 21 23:44 ib_logfile0
-rw-r-----. 1 root root 79691776 Jul 21 23:44 ibdata1
-执行restore操作
> ./ /var/lib/mysql
sending incremental file list
./
backup-my.cnf
ib_buffer_pool
ib_logfile0
ib_logfile1
ibdata1
ibtmp1
xtrabackup_binlog_info
xtrabackup_binlog_pos_innodb
xtrabackup_info
fire/
fire/db.opt
fire/emp.frm
fire/emp.ibd
fire/emp2.frm
fire/emp2.ibd
fire/t1.frm
fire/t1.ibd
fire/t10.MYD
fire/t10.MYI
fire/t10.frm
.....
sys/x@0024user_summary_by_statement_latency.frmsys/x@0024user_summary_by_statement_type.frm
sys/x@0024wait_classes_global_by_avg_latency.frm
sys/x@0024wait_classes_global_by_latency.frm
sys/x@0024waits_by_host_by_latency.frm
sys/x@0024waits_by_user_by_latency.frm
sys/x@0024waits_global_by_latency.frm
sent 324679831 bytes received 5769 bytes 22392110.34 bytes/sec
total size is 324618115 speedup is 1.00
-更改数据目录权限
[root@localhost 20160722]# chown -R mysql:mysql /var/lib/mysql/
-启动数据库
[root@localhost 20160722]# service mysqld start
Starting mysqld: [ OK ]
-查看恢复的表中数据
+---------------+--------------+------------+------------+--------+---------+
| table_catalog | table_schema | table_name | table_type | engine | version |
+---------------+--------------+------------+------------+--------+---------+
| def | fire | t10 | BASE TABLE | MyISAM | 10 |
+---------------+--------------+------------+------------+--------+---------+
1 row in set (0.00 sec)
mysql> select * from fire.t10;
+------+------+
| f1 | gc |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
+------+------+
5 rows in set (0.00 sec)
-执行基础全备
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/mysql/ --target-dir=/backup/20160722 --user root
--password 'System#2013'
--创建测试表并插入数据
mysql> create table dept(id int(5),d_name varchar(15),loc varchar(15));
Query OK, 0 rows affected (0.42 sec)
mysql> insert into dept values(10,'Research','ShangHai');
Query OK, 1 row affected (0.06 sec)
mysql> insert into dept values(20,'Maintenance','GuangZhou');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/20160722/ --user root --password 'System#2013'
.....
160722 00:26:18 [00] Writing xtrabackup_binlog_info160722 00:26:18 [00] ...done
160722 00:26:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186658935'
xtrabackup: Stopping log copying thread.
.160722 00:26:18 >> log scanned up to (186658944)
160722 00:26:18 Executing UNLOCK TABLES
160722 00:26:18 All tables unlocked
160722 00:26:18 [00] Copying ib_buffer_pool to /backup/inc1/ib_buffer_pool
160722 00:26:18 [00] ...done
160722 00:26:18 Backup created in directory '/backup/inc1'
MySQL binlog position: filename 'production-bin.000001', position '918', GTID of the last change '026dc034-4dc2-11e6-a9cb-000c29631605:1-17,
cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
fd17ba52-4fd8-11e6-bd80-000c29631605:1-3'
160722 00:26:18 [00] Writing backup-my.cnf
160722 00:26:18 [00] ...done
160722 00:26:18 [00] Writing xtrabackup_info
160722 00:26:18 [00] ...done
xtrabackup: Transaction log of lsn (186658935) to (186658944) was copied.
160722 00:26:18 completed OK!
--创建测试表并插入数据
mysql> create table test2 like test;
Query OK, 0 rows affected (0.95 sec)
mysql> insert into test2 select * from test;
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 --user root --password 'System#2013'
.....
160722 00:33:31 [01] Copying ./mysql/proxies_priv.frm to /backup/inc2/mysql/proxies_priv.frm160722 00:33:31 [01] ...done
160722 00:33:31 [01] Copying ./mysql/proc.MYD to /backup/inc2/mysql/proc.MYD
160722 00:33:31 [01] ...done
160722 00:33:31 [01] Copying ./mysql/help_relation.frm to /backup/inc2/mysql/help_relation.frm
160722 00:33:31 [01] ...done
160722 00:33:31 Finished backing up non-InnoDB tables and files
160722 00:33:31 [00] Writing xtrabackup_binlog_info
160722 00:33:31 [00] ...done
160722 00:33:31 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '186672619'
xtrabackup: Stopping log copying thread.
.160722 00:33:31 >> log scanned up to (186672628)
160722 00:33:31 Executing UNLOCK TABLES
160722 00:33:31 All tables unlocked
160722 00:33:31 [00] Copying ib_buffer_pool to /backup/inc2/ib_buffer_pool
160722 00:33:31 [00] ...done
160722 00:33:31 Backup created in directory '/backup/inc2'
MySQL binlog position: filename 'production-bin.000001', position '1421', GTID of the last change '026dc034-4dc2-11e6-a9cb-000c29631605:1-17,
cf291e84-2c89-11e6-b6f0-000c29631605:1-44,
fd17ba52-4fd8-11e6-bd80-000c29631605:1-5'
160722 00:33:31 [00] Writing backup-my.cnf
160722 00:33:31 [00] ...done
160722 00:33:31 [00] Writing xtrabackup_info
160722 00:33:31 [00] ...done
xtrabackup: Transaction log of lsn (186672619) to (186672628) was copied.
160722 00:33:31 completed OK!
-准备基础全备
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/20160722/
.....
xtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 50331648
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
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: 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: xtrabackup: Last MySQL binlog file position 3212, file name production-bin.000001
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 186652247
InnoDB: Number of pools: 1
160722 01:13:30 completed OK!
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/20160722/ --incremental-dir=/backup/inc1/
.....
160722 01:16:56 [01] Copying /backup/inc1/mysql/server_cost.frm to ./mysql/server_cost.frm160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/db.MYD to ./mysql/db.MYD
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/general_log.CSV to ./mysql/general_log.CSV
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/help_category.frm to ./mysql/help_category.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/innodb_table_stats.frm to ./mysql/innodb_table_stats.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/slave_master_info.frm to ./mysql/slave_master_info.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/func.MYD to ./mysql/func.MYD
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/user.frm to ./mysql/user.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/proxies_priv.frm to ./mysql/proxies_priv.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/proc.MYD to ./mysql/proc.MYD
160722 01:16:56 [01] ...done
160722 01:16:56 [01] Copying /backup/inc1/mysql/help_relation.frm to ./mysql/help_relation.frm
160722 01:16:56 [01] ...done
160722 01:16:56 [00] Copying /backup/inc1//xtrabackup_binlog_info to ./xtrabackup_binlog_info
160722 01:16:56 [00] ...done
160722 01:16:56 [00] Copying /backup/inc1//xtrabackup_info to ./xtrabackup_info
160722 01:16:56 [00] ...done
160722 01:16:56 completed OK!
-回滚基础全备的数据到第2个增量备份
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/backup/20160722/ --incremental-dir=/backup/inc2/
.....
160722 01:19:13 [01] Copying /backup/inc2/mysql/user.MYD to ./mysql/user.MYD160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/server_cost.frm to ./mysql/server_cost.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/db.MYD to ./mysql/db.MYD
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/general_log.CSV to ./mysql/general_log.CSV
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/help_category.frm to ./mysql/help_category.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/innodb_table_stats.frm to ./mysql/innodb_table_stats.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/slave_master_info.frm to ./mysql/slave_master_info.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/func.MYD to ./mysql/func.MYD
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/user.frm to ./mysql/user.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/proxies_priv.frm to ./mysql/proxies_priv.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/proc.MYD to ./mysql/proc.MYD
160722 01:19:13 [01] ...done
160722 01:19:13 [01] Copying /backup/inc2/mysql/help_relation.frm to ./mysql/help_relation.frm
160722 01:19:13 [01] ...done
160722 01:19:13 [00] Copying /backup/inc2//xtrabackup_binlog_info to ./xtrabackup_binlog_info
160722 01:19:13 [00] ...done
160722 01:19:13 [00] Copying /backup/inc2//xtrabackup_info to ./xtrabackup_info
160722 01:19:13 [00] ...done
160722 01:19:13 completed OK!
[root@localhost ~]# /install/percona/percona-xtrabackup-2.4.2-Linux-x86_64/bin/xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/backup/20160722/
.....
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: Page [page id: space=0, page number=9] log sequence number 186659641 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=0, page number=10] log sequence number 186659944 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=0, page number=12] log sequence number 186662606 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=13, page number=3] log sequence number 186669950 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: 5.7.11 started; log sequence number 186652247
InnoDB: Page [page id: space=82, page number=0] log sequence number 186655532 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=83, page number=0] log sequence number 186663700 is in the future! Current system log sequence number 186652266.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 1421, file name production-bin.000001
xtrabackup: error: The transaction log file is corrupted.
xtrabackup: error: The log was not applied to the intended LSN!
xtrabackup: Log applied to lsn 186652247
xtrabackup: The intended lsn is 186672619
-停止数据库，并删除数据文件下面的所有文件
[root@localhost 20160722]# cd /var/lib/mysql
[root@localhost mysql]# rm -rf *
-执行restore操作
[root@localhost backup]# cd /backup/20160722[root@localhost 20160722]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' \
> ./ /var/lib/mysql
sending incremental file list
./
backup-my.cnf
ib_buffer_pool
ib_logfile0
ib_logfile1
ibdata1
.....
sys/x@0024schema_table_statistics_with_buffer.frmsys/x@0024schema_tables_with_full_table_scans.frm
sys/x@0024session.frm
sys/x@0024statement_analysis.frm
sys/x@0024statements_with_errors_or_warnings.frm
sys/x@0024statements_with_full_table_scans.frm
sys/x@0024statements_with_runtimes_in_95th_percentile.frm
sys/x@0024statements_with_sorting.frm
sys/x@0024statements_with_temp_tables.frm
sys/x@0024user_summary.frm
sys/x@0024user_summary_by_file_io.frm
sys/x@0024user_summary_by_file_io_type.frm
sys/x@0024user_summary_by_stages.frm
sys/x@0024user_summary_by_statement_latency.frm
sys/x@0024user_summary_by_statement_type.frm
sys/x@0024wait_classes_global_by_avg_latency.frm
sys/x@0024wait_classes_global_by_latency.frm
sys/x@0024waits_by_host_by_latency.frm
sys/x@0024waits_by_user_by_latency.frm
sys/x@0024waits_global_by_latency.frm
sent 324910462 bytes received 5845 bytes 24067874.59 bytes/sec
total size is 324848462 speedup is 1.00
[root@localhost 20160722]# chown -R mysql.mysql /var/lib/mysql
--启动数据库
[root@localhost mysql]# service mysqld start
Starting mysqld: [ OK ]
+------+-------------+-----------+
| id | d_name | loc |
+------+-------------+-----------+
| 10 | Research | ShangHai |
| 20 | Maintenance | GuangZhou |
+------+-------------+-----------+
2 rows in set (0.00 sec)
mysql> select * from test2;
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 2 | Allice | Hanks |
| 5 | David | Beckham |
| 3 | Lily | Weber |
| 4 | Lucy | Willis |
| 1 | Terry | John |
+----+-----------+------------+
5 rows in set (0.00 sec)
分享文章：PerconaXtraBackup2.4xtrabackup全量、增量备份恢复流程
分享链接：http://cqcxhl.com/article/pjdpgh.html