Mysqlbackup restores a specific table _ MySQL

Mysqlbackup uses TTS to restore a specified table ********************************** * ************************** 4. restore a specific table ********* **************************************** * *********** -- 41 create a test environment C

Mysqlbackup uses TTS to restore a specified table. **************************************** * ********************* 4. restore a specific table ************************************* * ********************** -- 4.1 CREATE a test environment 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 autocommi T = 0; WHILE I <= 1000000 doinsert into t1 values (I, 'MySQL test'); set I = I + 1; END WHILE; commit; set autocommit = 1; END // DELIMITER; call proc1; create table t2asselect * from t1; -- 4. 2. full backup # 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-d Ir =/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 incremental backup 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 -- first incremental backup # mysqlbackup -- defaults-file =/usr/local/mysql/my. cnf \ -- host = 127.0.0.1 -- port = 3306 -- protoco L = 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 (*) for the second incremental backup (*) 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> updat E t2 set sname = 'ospyang 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 = 'ospyang 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 =/back Upinc backup # cat/backupinc/2015-04-02_12-46-48/meta/backup_variables.txt/ \ -- incremental-backup-dir =/backupinc/2015-04-02_12-44-02 \ -- log-bin-index =/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index \ apply-incrementa L-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 simulate deleting a specified table # cat/usr/local/mysql/my. cnf | grep datadirdatadir =/usr/local/mysql/datamysql> select count (*) from t2; + ---------- + | count (*) | + ---------- + | 999990 | + ---------- + 1 row in s Et (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 restore a specified table (using a mysql User) chown-R mysql/backupchgrp-R mysql/backupchown-R mysql/usr/local/mysqlchgrp-R mysql/usr/local/mysql [mysql @ mysql ~] $ Whoami # Avoid permission issues mysql/************************* NOTE: permission error 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-backMySQL Enterprise Backup version 3.12.0 Linux-2.6.18-194.el5-x86_64 [] Copyright (c) 2003,201 5, Oracle Nd/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: P Lease 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-defined cial-advanced-log '. mysqlbackup: INFO: Got some server configuration information from running server. -------------------------------------------------------------------- Server Repository Options :------ Export datadir =/usr/local/mysql/data/innodb_data_home_dir =/usr/local/mysql/innodb_data innodb_data_file_path = ibdata1: 800 M; ibdata2: 800 M: autoextend logs =/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 =. snapshot = 0 snapshot = 128 snapshot Backup Config Options: export datadir =/backup/snapshot/datadir innodb_data_home_dir =/backup/snapshot/datadir innodb_data_file_path = ibdata1: 800 M; ibdata2: 800 M: autoexte Nd bytes =/backup/2015-04-02_12-41-45/datadir records = 3 innodb_log_file_size = 2147483648 innodb_page_size = 16384 bytes = innodb mysqlbackup: INFO: Creating 14 buffers each of size 16777216.150402 13:38:25 mysqlbackup: INFO: copy-back operation starts with following threads1 read-threads 1 write-threads mysqlbackup: INFO: cocould 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 61 Apr 2 db. opt-rw ----. 1 mysql 8590 Apr 2 t2.frm-rw-r --. 1 mysql 58720256 Apr 2 t2.ibd/******* alter table t2 discard TABLESPACE; alter table t2 import tablespace; * *******/-- 4.7 verify table recovery mysql> use wind; Database changedmysql> 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 = 'ospyang mysql test! '; + ---------- + | Count (*) | + ---------- + | 5000 | + ---------- + 1 row in set (0.78 sec)