Percona XtraBackup 2.4 xtrabackup全量、增量备份恢复流程
发表于:2025-01-20 作者:千家信息网编辑
千家信息网最后更新 2025年01月20日,xtrabackup常用参数--backup 创建备份并将备份存放到--target-dir目录--defaults-file=[MY.CNF] 配置文件的路径--target-dir=DIRECTO
千家信息网最后更新 2025年01月20日Percona XtraBackup 2.4 xtrabackup全量、增量备份恢复流程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)
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/
..... InnoDB: New log files created, LSN=186651663
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_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 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] Writing xtrabackup_binlog_info
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] Copying ./mysql/proxies_priv.frm to /backup/inc2/mysql/proxies_priv.frm
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_files_in_group = 2
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] Copying /backup/inc1/mysql/server_cost.frm to ./mysql/server_cost.frm
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] Copying /backup/inc2/mysql/user.MYD to ./mysql/user.MYD
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: 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.frm
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 20160722]# chown -R mysql.mysql /var/lib/mysql
--启动数据库
[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)
--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/
..... InnoDB: New log files created, LSN=186651663
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_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 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] Writing xtrabackup_binlog_info
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] Copying ./mysql/proxies_priv.frm to /backup/inc2/mysql/proxies_priv.frm
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_files_in_group = 2
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] Copying /backup/inc1/mysql/server_cost.frm to ./mysql/server_cost.frm
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] Copying /backup/inc2/mysql/user.MYD to ./mysql/user.MYD
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: 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.frm
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 20160722]# chown -R mysql.mysql /var/lib/mysql
--启动数据库
[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)
备份
数据
文件
目录
增量
基础
全备
准备
数据库
日志
路径
权限
账户
测试
一致
会创
参数
密码
常用
拷贝
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
组装服务器的缺点
互联网科技公司实习报告
江苏戴尔服务器续保调试
重庆綦江众道生鲜软件开发
越秀专业的小程序软件开发
中山市凯源网络技术有限公司
服务器地址注册
中兴服务器管理口登陆后全黑
星巴克网络技术员
宁波海曙刀片服务器
网络安全基金龙头
优剪互联网科技有限公司电话
魅力石屏网络安全周
php+数据库模式
黑色沙漠艾尔比亚服务器
计算网络技术专业相关工作
和平精英国际版哪个服务器人多
达梦数据库口令过期
网络安全书签制作图片
地址软件开发公司
安全狗通过服务器ip
浚峰网络技术有限公司怎么样
崇明区工商软件开发代理价格
数据库关联链接是什么原因
网络安全 广播稿300字
网络安全法法条测试答案
多媒体数据库解决关键技术
黄浦区节能软件开发定制价格
剑灵游蝶数据库
怎么找数据库在哪