来源：自学PHP网 时间：2015-04-16 10:50 作者： 阅读:次
[导读] mysqlbackup使用TTS恢复指定表 *************************************************************4 恢复特定表*************************************************************--4 1新建测试环境C...
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)
|
自学PHP网专注网站建设学习,PHP程序学习,平面设计学习，以及操作系统学习
京ICP备14009008号-1@版权所有www.zixuephp.com
网站声明：本站所有视频，教程都由网友上传，站长收集和分享给大家学习使用，如由牵扯版权问题请联系站长邮箱904561283@qq.com
