Percona XtraBackup 2.4 xtrabackup全量、增量备份恢复流程
发表于:2024-09-26 作者:千家信息网编辑
千家信息网最后更新 2024年09月26日,xtrabackup常用参数--backup 创建备份并将备份存放到--target-dir目录--defaults-file=[MY.CNF] 配置文件的路径--target-dir=DIRECTO
千家信息网最后更新 2024年09月26日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安全错误
数据库的锁怎样保障安全
asp数据库放在哪
阿里云数据库不能访问吗
寻仙卡号服务器
软件开发 合同模板
pdm如何直接转换为数据库
信息安全变更为网络安全
微信公众号有没有后台数据库
网络安全宣传传单内容
软件开发时点统计什么意思
电力行业网络安全标准清单
中德两国启动网络安全认可
笔记本怎么开通服务器
数据库中的services
成都鹏成网络技术
网络安全知识竞赛团队赛
审判软件开发
运营网络技术咨询概况
vb2010数据库编程
辽源软件开发系统价目表
国家网络安全法 建议措施
暨南大学软件开发博士
天津app软件开发公司多吗
咋能打开数据库
支付宝网络技术短信费
华为elk数据库版本号
ibm服务器备份
应用软件开发专业
魔兽世界欧服服务器维护
魅族自动同步数据库
网络安全重于泰山手抄报内容