程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> xtrabackup全備方案,備份恢復全過程記錄

xtrabackup全備方案,備份恢復全過程記錄

編輯:MySQL綜合教程

xtrabackup全備方案,備份恢復全過程記錄   [html]  mysql> use inno   Database changed   mysql> insert into mm select 1;   Query OK, 1 row affected (0.01 sec)   Records: 1  Duplicates: 0  Warnings: 0      mysql> insert into mm select 2;   Query OK, 1 row affected (0.00 sec)   Records: 1  Duplicates: 0  Warnings: 0      mysql> insert into mm select 3;   Query OK, 1 row affected (0.00 sec)   Records: 1  Duplicates: 0  Warnings: 0      mysql> select * from mm;   +------+   | a    |   +------+   |    1 |   |    2 |   |    3 |   +------+   3 rows in set (0.00 sec)      mysql> show create table mm;   +-------+-------------------------------------------------------------------------------------+   | Table | Create Table                                                                        |   +-------+-------------------------------------------------------------------------------------+   | mm    | CREATE TABLE `mm` (     `a` int(11) DEFAULT NULL   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |   +-------+-------------------------------------------------------------------------------------+   1 row in set (0.00 sec)      mysql> exit   mysql> use inno   Database changed   mysql> create table t(a int)       -> ;   Query OK, 0 rows affected (0.06 sec)      mysql> show create table t;   +-------+------------------------------------------------------------------------------------+   | Table | Create Table                                                                       |   +-------+------------------------------------------------------------------------------------+   | t     | CREATE TABLE `t` (     `a` int(11) DEFAULT NULL   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |   +-------+------------------------------------------------------------------------------------+   1 row in set (0.01 sec)      mysql> insert into t select 1;   Query OK, 1 row affected (0.00 sec)   Records: 1  Duplicates: 0  Warnings: 0      mysql> insert into t select 2;   Query OK, 1 row affected (0.01 sec)   Records: 1  Duplicates: 0  Warnings: 0      mysql> insert into t select 3;   Query OK, 1 row affected (0.00 sec)   Records: 1  Duplicates: 0  Warnings: 0      mysql> select * from t;   +------+   | a    |   +------+   |    1 |   |    2 |   |    3 |   +------+   3 rows in set (0.00 sec)      mysql>   以上是准備環境,以下開始進行測試; [html]  全備:   [root@Slave02 back_up]# xtrabackup_55 --defaults-file=/etc/my.cnf  --backup  --target-dir=/data/back_up/   xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (i686) (revision id: undefined)   xtrabackup: uses posix_fadvise().   xtrabackup: cd to /usr/local/mysql/data   xtrabackup: Target instance is assumed as followings.   xtrabackup:   innodb_data_home_dir = ./   xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend   xtrabackup:   innodb_log_group_home_dir = ./   xtrabackup:   innodb_log_files_in_group = 2   xtrabackup:   innodb_log_file_size = 5242880   130327 14:11:12 InnoDB: Using Linux native AIO   130327 14:11:12  InnoDB: Warning: allocated tablespace 2, old maximum was 0   >> log scanned up to (893330211)   [01] Copying ./ibdata1         to /data/back_up//ibdata1   >> log scanned up to (893330211)   >> log scanned up to (893330211)   [01]        ...done   [01] Copying ./inno/mm.ibd         to /data/back_up//inno/mm.ibd   [01]        ...done   [01] Copying ./inno/t.ibd         to /data/back_up//inno/t.ibd   [01]        ...done   xtrabackup: The latest check point (for incremental): '893330211'   >> log scanned up to (893330211)   xtrabackup: Stopping log copying thread.   xtrabackup: Transaction log of lsn (893330211) to (893330211) was copied.   [root@Slave02 back_up]#    [root@Slave02 back_up]# ls   ibdata1  inno  xtrabackup_checkpoints  xtrabackup_logfile   [root@Slave02 back_up]# cd inno/   [root@Slave02 inno]# ls   mm.ibd  t.ibd   [root@Slave02 inno]# ll   total 208   -rw-r--r-- 1 root root 98304 Mar 27 14:11 mm.ibd   -rw-r--r-- 1 root root 98304 Mar 27 14:11 t.ibd   [root@Slave02 inno]#    [root@Slave02 inno]# cp /usr/local/mysql/data/inno/*.frm  /data/back_up/inno/   --復制表結構文件進行到備份目錄內;   [root@Slave02 inno]# cd /data/back_up/inno/   [root@Slave02 inno]# ls   mm.frm  mm.ibd  t.frm  t.ibd   ----此目錄內有完整的IBD數據文件和frm表結構文件;   [root@Slave02 inno]#    [root@Slave02 inno]# cd ..   [root@Slave02 data]# ls   book         log.1             mysql-bin.000004    prod   ibdata1      mysql             mysql-bin.index     Slave02.err   ib_logfile0  mysql-bin.000001  mysqld_multi.log    Slave02.pid   ib_logfile1  mysql-bin.000002  mysql.pid           slow.log   inno         mysql-bin.000003  performance_schema  test   ---以下開始測試全庫innodb的恢復過程   [root@Slave02 data]# rm -rf inno/      ----刪除inno裡的所有文件(ibd,from等)   [root@Slave02 data]# ls   book         mysql             mysql-bin.index     Slave02.err   ibdata1      mysql-bin.000001  mysqld_multi.log    Slave02.pid   ib_logfile0  mysql-bin.000002  mysql.pid           slow.log   ib_logfile1  mysql-bin.000003  performance_schema  test   log.1        mysql-bin.000004  prod   [root@Slave02 data]# cp -r /data/back_up/inno/ /usr/local/mysql/data/   --使用備份的數據進行還原到相關目錄   [root@Slave02 data]# ls   book         log.1             mysql-bin.000004    prod   ibdata1      mysql             mysql-bin.index     Slave02.err   ib_logfile0  mysql-bin.000001  mysqld_multi.log    Slave02.pid   ib_logfile1  mysql-bin.000002  mysql.pid           slow.log   inno         mysql-bin.000003  performance_schema  test   [root@Slave02 data]# cd inno/   [root@Slave02 inno]# ll                                             ---查看到相關的目前權限不對,進行修改   total 240   -rw-r----- 1 root root  8554 Mar 27 14:20 mm.frm   -rw-r--r-- 1 root root 98304 Mar 27 14:20 mm.ibd   -rw-r----- 1 root root  8554 Mar 27 14:20 t.frm   -rw-r--r-- 1 root root 98304 Mar 27 14:20 t.ibd   [root@Slave02 inno]#    [root@Slave02 inno]# ll   total 240   -rw-r----- 1 root root  8554 Mar 27 14:20 mm.frm   -rw-r--r-- 1 root root 98304 Mar 27 14:20 mm.ibd   -rw-r----- 1 root root  8554 Mar 27 14:20 t.frm   -rw-r--r-- 1 root root 98304 Mar 27 14:20 t.ibd   [root@Slave02 inno]# chown -R mysql:mysql /usr/local/mysql/data/inno/   [root@Slave02 inno]# ll -la   total 256   drwx------ 2 mysql mysql  4096 Mar 27 14:20 .   drwxr-xr-x 8 mysql mysql  4096 Mar 27 14:20 ..   -rw-r----- 1 mysql mysql  8554 Mar 27 14:20 mm.frm   -rw-r--r-- 1 mysql mysql 98304 Mar 27 14:20 mm.ibd   -rw-r----- 1 mysql mysql  8554 Mar 27 14:20 t.frm   -rw-r--r-- 1 mysql mysql 98304 Mar 27 14:20 t.ibd   [root@Slave02 inno]#    [root@Slave02 inno]# service mysqld restart        -----重啟msyql,進行校驗下數據;   Shutting down MySQL.                                       [  OK  ]   Starting MySQL..                                           [  OK  ]   [root@Slave02 inno]# mysql -uroot -proot   Welcome to the MySQL monitor.  Commands end with ; or \g.   Your MySQL connection id is 1   Server version: 5.5.13-log Source distribution      Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.      Oracle is a registered trademark of Oracle Corporation and/or its   affiliates. Other names may be trademarks of their respective   owners.      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.      mysql> use inno;   Database changed   mysql> show tables;   +----------------+   | Tables_in_inno |   +----------------+   | mm             |   | t              |   +----------------+   2 rows in set (0.00 sec)      mysql> select * from mm;   ---恢復無誤正常;   +------+   | a    |   +------+   |    1 |   |    2 |   |    3 |   |    4 |   +------+   4 rows in set (0.00 sec)      mysql>    mysql> select * from t;   +------+   | a    |   +------+   |    1 |   |    2 |   |    3 |   +------+   3 rows in set (0.00 sec)      mysql>     

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved