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)
Wie man den Douyin-Moment mit engen Freunden zum Leuchten bringt
Was soll ich tun, wenn mein Computer hochfährt und auf dem Bildschirm ein schwarzer Bildschirm ohne Signal angezeigt wird?
Kaspersky-Firewall
Plugin.exe-Anwendungsfehler
nvidia geforce 940mx
Wissen Sie, ob Sie die andere Person sofort kündigen, nachdem Sie ihr auf Douyin gefolgt sind?
So berechnen Sie die Bearbeitungsgebühr für die Rückerstattung der Eisenbahn 12306
Die zehn besten Börsen für digitale Währungen
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
