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)
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)
--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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2123648/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Percona XtraBackup 2.4 innobackupex全量、增量備份恢復流程
- 【Xtrabackup】Xtrabackup全備、增量備份及恢復示例
- Percona XtraBackup 實現全備&增量備份與恢復
- MySQL 5.6 xtrabackup 全量和增量的備份和恢復MySql
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- Xtrabackup備份恢復原理
- MySQL運維實戰之備份和恢復（8.1）xtrabackup全量備份MySql運維
- MySQL 之XtraBackup全量增量熱備實踐MySql
- Percona Xtrabackup 快速備份 MySQLMySql
- xtrabackup和innobackuppex的安裝和備份(含增量備份)已經恢復
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- MySQL · 物理備份 · Percona XtraBackup 備份原理MySql
- 【MySQL】Xtrabackup備份及恢復指令碼MySql指令碼
- 【Mysql】xtrabackup 備份和恢復測試MySql
- XtraBackup完整備份與增量備份的原理
- 【Mysql】xbackup全量與增量備份恢復MySql
- Xtrabackup之innobackupex備份恢復詳解薦
- mysql備份和恢復測試(一）--xtrabackupMySql
- Xtrabackup每週增量備份指令碼程式指令碼
- 如何用Percona XtraBackup進行MySQL從庫的單表備份和恢復MySql
- 使用xtrabackup對mysql進行備份和恢復MySql
- 【Xtrabackup】Xtrabackup備份基礎知識
- MySQL：xtrabackup備份MySql
- XtraBackup備份MySQLMySql
- mysql之 Innobackupex（全備+增量）備份恢復MySql
- Xtrabackup實現資料庫備份和災難恢復資料庫
- MySQL Percona XtraBackupMySql
- Percona xtrabackup2.4版本遠端備份和搭建從庫的方法介紹
- xtrabackup備份mysql innodbMySql
- Percona-Xtrabackup 2.3.3 慢查詢不再堵塞備份（二）
- Xtrabackup2.4.8備份、還原、恢復Mysql5.7.19實操MySql
- MySQL innobackupex全量備份恢復MySql
- XtraBackup實踐(一)備份
- MySQL 利用xtrabackup進行增量備份詳細過程彙總MySql
- mysql通過percona xtrabackup全備和mysql binlog實現基於時間點的資料庫恢復MySql資料庫
- mysql innobackupex增量備份恢復MySql
- 使用Xtrabackup遠端備份MysqlMySql
- INNOBACKUPEX的全備和增量備份恢復學習筆記筆記