mysqlbackup 回复特定表_mysql

mysqlbackup 恢复特定表

mysqlbackup使用TTS恢复指定表.     ************************************************************* 4.恢复特定表 *************************************************************    --4.1新建测试环境  CREATE DATABASE `wind`  DEFAULT CHARACTER SET gbk ;  use wind;   create table t1 ( sid int not null , sname varchar(100)  not null )engine=innodb charset=gbk  ;      DELIMITER // create PROCEDURE proc1() BEGIN       DECLARE i int DEFAULT 0;     set i=1 ; set autocommit=0;   WHILE i<=1000000 DO INSERT INTO t1 values(i,'mysql测试'); set i=i+1;  END WHILE;  commit; set autocommit=1;       END   // DELIMITER ;  call proc1;   create table t2 as select * from t1;    --4.2.全备   # rm -rf /backup && mkdir /backup    #mysqlbackup --defaults-file=/usr/local/mysql/my.cnf  / --host=127.0.0.1   --port=3306  --protocol=tcp  / --user=root  --password=123  --use-tts --include-tables='wind.t2' / --with-timestamp   --backup-dir=/backup   / --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index / backup-and-apply-log        # cat /backup/2015-04-02_12-41-45/meta/backup_variables.txt  | grep 'end' end_lsn=138866623      --4.3 增量备份   mysql> select count(*) from t1; +----------+ | count(*) | +----------+ |  1000000 | +----------+ 1 row in set (0.75 sec)  mysql> select count(*) from t2; +----------+ | count(*) | +----------+ |  1000000 | +----------+ 1 row in set (0.68 sec)   mysql> delete from t2  limit 10; Query OK, 10 rows affected (0.00 sec)  mysql> commit; Query OK, 0 rows affected (0.00 sec)  mysql> select count(*) from t2; +----------+ | count(*) | +----------+ |   999990 | +----------+ 1 row in set (0.80 sec)    # rm -rf /backupinc && mkdir /backupinc  --第一次增量备份  #mysqlbackup --defaults-file=/usr/local/mysql/my.cnf  / --host=127.0.0.1   --port=3306  --protocol=tcp  / --user=root  --password=123  --use-tts --include-tables='wind.t2' / --with-timestamp  --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index / --incremental --start-lsn=138866623  / --incremental-backup-dir=/backupinc backup      # cat /backupinc/2015-04-02_12-44-02/meta/backup_variables.txt  | grep 'end'  end_lsn=138868639   --第二次增量备份   mysql> select count(*) from wind.t2; +----------+ | count(*) | +----------+ |   999990 | +----------+ 1 row in set (0.83 sec)  mysql> desc t2     -> ; +-------+--------------+------+-----+---------+-------+ | Field | Type         | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | sid   | int(11)      | NO   |     | NULL    |       | | sname | varchar(100) | NO   |     | NULL    |       | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)  mysql> update t2 set sname='ocpyang mysql test!' limit 5000; Query OK, 5000 rows affected (0.24 sec) Rows matched: 5000  Changed: 5000  Warnings: 0  mysql> commit; Query OK, 0 rows affected (0.00 sec)   mysql> select count(*) from wind.t2 where sname='ocpyang mysql test!'; +----------+ | count(*) | +----------+ |     5000 | +----------+ 1 row in set (0.86 sec)      #mysqlbackup --defaults-file=/usr/local/mysql/my.cnf  / --host=127.0.0.1   --port=3306  --protocol=tcp  / --user=root  --password=123  --use-tts --include-tables="wind.t2" / --with-timestamp  --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index / --incremental --start-lsn=138868639  / --incremental-backup-dir=/backupinc  backup    # cat /backupinc/2015-04-02_12-46-48/meta/backup_variables.txt  | grep end end_lsn=139571560     --4.4 合并增量备份到全备  ls /backupinc/ 2015-04-02_12-44-02  2015-04-02_12-46-48  ls /backup  2015-04-02_12-41-45   mysqlbackup --backup-dir=/backup/2015-04-02_12-41-45/ / --incremental-backup-dir=/backupinc/2015-04-02_12-44-02 / --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index / apply-incremental-backup    mysqlbackup --backup-dir=/backup/2015-04-02_12-41-45 / --incremental-backup-dir=/backupinc/2015-04-02_12-46-48 / --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index / apply-incremental-backup        --4.5  模拟删除指定表  #cat /usr/local/mysql/my.cnf |grep datadir datadir=/usr/local/mysql/data   mysql> select count(*) from t2; +----------+ | count(*) | +----------+ |   999990 | +----------+ 1 row in set (0.80 sec)   mysql> select count(*) from wind.t2 where sname='ocpyang mysql test!'; +----------+ | count(*) | +----------+ |     5000 | +----------+ 1 row in set (0.86 sec)   mysql> drop table t2;     --4.6 还原指定表(使用mysql用户)   chown -R mysql /backup chgrp -R mysql /backup  chown -R mysql /usr/local/mysql chgrp -R mysql /usr/local/mysql   [mysql@mysql ~]$ whoami   #避免权限问题 mysql  /***********************NOTE:权限问题出现的错误   150402 13:29:26 mysqlbackup: INFO: Importing table: wind.t2.  mysqlbackup: ERROR: mysql query: 'ALTER TABLE wind.t2 IMPORT TABLESPACE':  Internal error: Cannot reset LSNs in table '"wind"."t2"' : Tablespace not found  mysqlbackup: ERROR: Failed to import tablespace wind.t2.  mysqlbackup failed with errors!   *************************************************/     mysqlbackup --host=127.0.0.1   --port=3306  --protocol=tcp  / --user=root  --password=123  / --datadir=/usr/local/mysql/data/   / --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index / --backup-dir=/backup/2015-04-02_12-41-45/ / --include-tables='wind/.t2$' / copy-back      [mysql@mysql ~]$ mysqlbackup --host=127.0.0.1   --port=3306  --protocol=tcp  / > --user=root  --password=123  / > --datadir=/usr/local/mysql/data/   / > --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index / > --backup-dir=/backup/2015-04-02_12-41-45/ / > --include-tables='wind/.t2$' / > copy-back MySQL Enterprise Backup version 3.12.0 Linux-2.6.18-194.el5-x86_64 [2015/03/10]  Copyright (c) 2003, 2015, Oracle and/or its affiliates. All Rights Reserved.   mysqlbackup: INFO: Starting with following command line ...  mysqlbackup --host=127.0.0.1 --port=3306 --protocol=tcp --user=root          --password=xxx --datadir=/usr/local/mysql/data/          --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index          --backup-dir=/backup/2015-04-02_12-41-45/ --include-tables=wind/.t2$          copy-back    mysqlbackup: INFO:  IMPORTANT: Please check that mysqlbackup run completes successfully.            At the end of a successful 'copy-back' run mysqlbackup            prints "mysqlbackup completed OK!".  150402 13:38:25 mysqlbackup: INFO: MEB logfile created at /backup/2015-04-02_12-41-45/meta/MEB_2015-04-02.13-38-25_copy_back.log   mysqlbackup: INFO: MySQL server version is '5.6.23-enterprise-commercial-advanced-log'.  mysqlbackup: INFO: Got some server configuration information from running server.  --------------------------------------------------------------------                        Server Repository Options: --------------------------------------------------------------------   datadir = /usr/local/mysql/data/   innodb_data_home_dir = /usr/local/mysql/innodb_data   innodb_data_file_path = ibdata1:800M;ibdata2:800M:autoextend   innodb_log_group_home_dir = /usr/local/mysql/mysql_logs/innodb_log/   innodb_log_files_in_group = 3   innodb_log_file_size = 2147483648   innodb_page_size = 16384   innodb_checksum_algorithm = innodb   innodb_undo_directory = .   innodb_undo_tablespaces = 0   innodb_undo_logs = 128  --------------------------------------------------------------------                        Backup Config Options: --------------------------------------------------------------------   datadir = /backup/2015-04-02_12-41-45/datadir   innodb_data_home_dir = /backup/2015-04-02_12-41-45/datadir   innodb_data_file_path = ibdata1:800M;ibdata2:800M:autoextend   innodb_log_group_home_dir = /backup/2015-04-02_12-41-45/datadir   innodb_log_files_in_group = 3   innodb_log_file_size = 2147483648   innodb_page_size = 16384   innodb_checksum_algorithm = innodb   mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 150402 13:38:25 mysqlbackup: INFO: Copy-back operation starts with following threads   1 read-threads    1 write-threads  mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup.  Point-In-Time-Recovery will not be possible.  If this is online backup then server may not have started with --log-bin.  You may specify its location with --log-bin-index option. 150402 13:38:25 mysqlbackup: INFO: Creating table: wind.t2. 150402 13:38:25 mysqlbackup: INFO: Copying /backup/2015-04-02_12-41-45/datadir/wind/t2.ibd. 150402 13:38:26 mysqlbackup: INFO: Completing the copy of all non-innodb files. 150402 13:38:27 mysqlbackup: INFO: Importing table: wind.t2. 150402 13:38:28 mysqlbackup: INFO: Analyzing table: wind.t2. 150402 13:38:29 mysqlbackup: INFO: Copy-back operation completed successfully. 150402 13:38:29 mysqlbackup: INFO: Finished copying backup files to '/usr/local/mysql/data'  mysqlbackup completed OK!      # ls -ll /usr/local/mysql/data/wind/ total 57360 -rw-rw----. 1 mysql mysql       61 Apr  2 13:26 db.opt -rw-rw----. 1 mysql mysql     8590 Apr  2 13:38 t2.frm -rw-rw-r--. 1 mysql mysql 58720256 Apr  2 13:38 t2.ibd   /*******  ALTER TABLE t2 discard TABLESPACE;  ALTER TABLE t2 IMPORT TABLESPACE;  *********/   --4.7验证表恢复情况   mysql> use wind; Database changed mysql> show tables; +----------------+ | Tables_in_wind | +----------------+ | t2             | +----------------+ 1 row in set (0.00 sec)  mysql> select count(*) from t2; +----------+ | count(*) | +----------+ |   999990 | +----------+ 1 row in set (1.34 sec)     mysql> select count(*) from wind.t2 where sname='ocpyang mysql test!'; +----------+ | count(*) | +----------+ |     5000 | +----------+ 1 row in set (0.78 sec)         

欢迎大家阅读《mysqlbackup 回复特定表_mysql》,跪求各位点评，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)