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)

版权声明：本文博主原创文章，博客，未经同意不得转载。