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)
天意易趣网拍卖系统
前台主要功能：首选服务 注销登陆 查看使用帮助 修改添加登陆帐号拍卖商品管理 管理拍卖商品 推荐拍卖商品 删除特定拍卖 已经结束商品 拍卖分类管理 新闻管理 添加文章 删除修改 栏目管理 新闻CSS设定 新闻JS生成 初始化新闻 参数设置 用户管理 未审核用户管理 普通用户管理 高级用户管理 黄金用户管理 管理所有用户 数据库管理 压缩数据库 备份数据库 恢复数据库 批量处理 系统指标测试V1.
下载
