首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
数据库的恢复,恢复误删除表黑科技之relay
发布时间:2019-12-29 03:25
浏览次数:

 

初学mysql时,可能不太明白delimiter的真正用途,delimiter在mysql很多地方出现,比如存储过程、触发器、函数等。

数据库恢复是和数据库备份相对应的操作,它是将数据库备份重新加载到系统中的过程。数据库恢复可以创建备份完成时数据库中存在的相关文件,但是备份以后的所有数据库修改都将丢失。

事务(transaction)其实就是一序列数据库命令,他们可以组合在一起当做一个单一逻辑单元

Preface

学过oracle的人,再来学mysql就会感到很奇怪,百思不得其解。

   SQL Server进行数据库恢复时,系统将自动进行安全性检查,以防止误操作而使用了不完整的信息或其他的数据备份覆盖现有的数据库。当出现以下几种情况时,系统将不能恢复数据库。

在一个事务被标记为完成之前(commited),任何改变可以被回滚(rolled back)

 

其实就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。
默认情况下,delimiter是分号(;) 

  (1)还原操作中的数据库名称与备份集中记录的数据库名称不匹配。

例子:转账,它可以包括(减少发送钱账户的金额量,增加收款人的金额量,给发送方和接收方都增加交易记录)

    I've stuck twice in my previous experiments in backing up dropped tables.I am still not sure that why I got failure yesterday.Therefore,I decide to do that again with the same steps on another environment.Let's see the details.

在命令行客户端中,如果有一行命令以分号结束,
那么回车后,mysql将会执行该命令。如输入下面的语句
mysql> select * from test_table;
然后回车,那么MySQL将立即执行该语句。

  (2)需要通过还原操作自动创建一个或多个文件,但已有同名的文件存在。

如果连接被打断,或者说一些操作失败了,所有的操作都需要被回滚

 

但有时候,不希望MySQL这么做。在为可能输入较多的语句,且语句中包含有分号。
如试图在命令行客户端中输入如下语句

  (3)还原操作中命名的数据库已在服务器上,但是与数据库备份中包含的数据库不是同一个数据库,例如数据库名称虽相同,但是数据库的创建方式不同。

→ Total failure is better than partial failure

Procedure

mysql> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT)
mysql>     RETURNS varchar(255)
mysql> BEGIN
mysql> IF ISNULL(S) THEN
mysql>    RETURN '';
mysql> ELSEIF N<15 THEN
mysql>     RETURN LEFT(S, N);
mysql> ELSE
mysql>     IF CHAR_LENGTH(S) <=N THEN
mysql>    RETURN S;
mysql>     ELSE
mysql>    RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5));
mysql>     END IF;
mysql> END IF;
mysql> END;

   如果重新创建一个数据库,可以禁止这些安全检查。

 

 

  默认情况下,不可能等到用户把这些语句全部输入完之后,再执行整段语句。
  因为mysql一遇到分号,它就要自动执行。
       即,在语句RETURN '';时,mysql解释器就要执行了。
      这种情况下,就需要事先把delimiter换成其它符号,如//或$$。

一。数据库恢复模型

每一个事务的终止信号都是通过commit或者roll back来传达的,没有commit,我们做出的改变不会被保存

1. All the operations on master zlm1.

mysql> delimiter //
mysql> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT)
mysql>     RETURNS varchar(255)
mysql> BEGIN
mysql> IF ISNULL(S) THEN
mysql>    RETURN '';
mysql> ELSEIF N<15 THEN
mysql>     RETURN LEFT(S, N);
mysql> ELSE
mysql>     IF CHAR_LENGTH(S) <=N THEN
mysql>    RETURN S;
mysql>     ELSE
mysql>    RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5));
mysql>     END IF;
mysql> END IF;
mysql> END;//

根据保存数据的需要和对存储介质使用的考虑,SQL Server提供了3种数据库恢复模型:简单恢复、完全恢复、大容量日志记录恢复。

但其实Sesssions都是有自动提交功能(auto-commit)的,也就是任何命令都能被马上提交,不需要roll back和commit,这种auto-commit方式都是被默认的(set sutocommit = 0 关闭autocommit)

  1 //Check data at first.
  2 zlm@192.168.56.100:3306 [sysbench]>show tables;
  3 +--------------------+
  4 | Tables_in_sysbench |
  5 +--------------------+
  6 | sbtest1            |
  7 | sbtest2            |
  8 | sbtest3            |
  9 | sbtest4            |
 10 | sbtest5            |
 11 +--------------------+
 12 5 rows in set (0.00 sec)
 13 
 14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest5;
 15 +----------+
 16 | count(*) |
 17 +----------+
 18 |    10000 |
 19 +----------+
 20 1 row in set (0.00 sec)
 21 
 22 zlm@192.168.56.100:3306 [sysbench]>show binary logs;
 23 +------------------+-----------+
 24 | Log_name         | File_size |
 25 +------------------+-----------+
 26 | mysql-bin.000091 |      1288 |
 27 | mysql-bin.000092 |       217 |
 28 | mysql-bin.000093 |       241 |
 29 | mysql-bin.000094 |       217 |
 30 | mysql-bin.000095 |      4128 |
 31 | mysql-bin.000096 |       241 |
 32 | mysql-bin.000097 |  11461585 |
 33 | mysql-bin.000098 |       410 |
 34 | mysql-bin.000099 |       241 |
 35 | mysql-bin.000100 |       974 |
 36 | mysql-bin.000101 |       217 |
 37 | mysql-bin.000102 |       217 |
 38 | mysql-bin.000103 |       194 |
 39 +------------------+-----------+
 40 13 rows in set (0.01 sec)
 41 
 42 zlm@192.168.56.100:3306 [sysbench]>show master status;
 43 +------------------+----------+--------------+------------------+-------------------------------------------------+
 44 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 45 +------------------+----------+--------------+------------------+-------------------------------------------------+
 46 | mysql-bin.000103 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
 47 +------------------+----------+--------------+------------------+-------------------------------------------------+
 48 1 row in set (0.00 sec)
 49 
 50 //Generate a Xtrabackup then send it to the slave zlm2.
 51 [root@zlm1 16:27:18 /data/backup]
 52 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
 53 
 54 [root@zlm1 16:34:09 /data/backup]
 55 #scp -r 2018-07-31_16-31-46/ zlm2:/data/backup/
 56 
 57 //Continue to do some operations(DML).
 58 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest5 limit 2000;
 59 Query OK, 2000 rows affected (0.19 sec)
 60 
 61 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest5 limit 3000;
 62 Query OK, 3000 rows affected (0.04 sec)
 63 
 64 //Drop the table.
 65 zlm@192.168.56.100:3306 [sysbench]>drop table sbtest5;
 66 Query OK, 0 rows affected (0.04 sec)
 67 
 68 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 69 Query OK, 0 rows affected (0.05 sec)
 70 
 71 zlm@192.168.56.100:3306 [sysbench]>show master status;
 72 +------------------+----------+--------------+------------------+-------------------------------------------------+
 73 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 74 +------------------+----------+--------------+------------------+-------------------------------------------------+
 75 | mysql-bin.000104 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715699 |
 76 +------------------+----------+--------------+------------------+-------------------------------------------------+
 77 1 row in set (0.00 sec)
 78 
 79 //Shutdown the master.
 80 zlm@192.168.56.100:3306 [sysbench]>exit
 81 Bye
 82 
 83 [root@zlm1 16:36:01 ~]
 84 #mysqladmin shutdown
 85 
 86 //Send the binlogs which contain the operations above to slave zlm2 either.
 87 [root@zlm1 16:36:10 ~]
 88 #cd /data/mysql/mysql3306/logs
 89 
 90 [root@zlm1 16:41:45 /data/mysql/mysql3306/logs]
 91 #ls -l
 92 total 12188
 93 -rw-r----- 1 mysql mysql     1288 Jul 22 11:27 mysql-bin.000091
 94 -rw-r----- 1 mysql mysql      217 Jul 22 11:28 mysql-bin.000092
 95 -rw-r----- 1 mysql mysql      241 Jul 25 19:18 mysql-bin.000093
 96 -rw-r----- 1 mysql mysql      217 Jul 25 19:18 mysql-bin.000094
 97 -rw-r----- 1 mysql mysql     4128 Jul 29 08:42 mysql-bin.000095
 98 -rw-r----- 1 mysql mysql      241 Jul 29 08:42 mysql-bin.000096
 99 -rw-r----- 1 mysql mysql 11461585 Jul 29 09:52 mysql-bin.000097
100 -rw-r----- 1 mysql mysql      410 Jul 29 16:27 mysql-bin.000098
101 -rw-r----- 1 mysql mysql      241 Jul 29 16:27 mysql-bin.000099
102 -rw-r----- 1 mysql mysql      974 Jul 29 16:57 mysql-bin.000100
103 -rw-r----- 1 mysql mysql      217 Jul 30 17:01 mysql-bin.000101
104 -rw-r----- 1 mysql mysql      217 Jul 30 18:12 mysql-bin.000102
105 -rw-r----- 1 mysql mysql   954995 Jul 31 16:35 mysql-bin.000103
106 -rw-r----- 1 mysql mysql      217 Jul 31 16:36 mysql-bin.000104
107 -rw-r----- 1 mysql mysql      616 Jul 31 16:35 mysql-bin.index
108 
109 [root@zlm1 16:42:01 /data/mysql/mysql3306/logs]
110 #scp mysql-bin.00010{3,4} zlm2:/data/backup
111 mysql-bin.000103                                                                                                   100%  933KB 932.6KB/s   00:00    
112 mysql-bin.000104                                                                                                   100%  217     0.2KB/s   00:00

  这样只有当//出现之后,mysql解释器才会执行这段语句。

1.简单恢复模型

→ START TRANSACTION or BEGIN

 

其外。再mysql的存储过程中,需要注意的一点是:

  简单恢复模型可以将数据库恢复到上次备份处,但是无法将数据库还原到故障点或待定的即时点。它常用于恢复最新的完整数据库备份、差异备份。

BEGIN;

**2. Operations on slave zlm2.**

drop PROCEDURE if EXISTS proc_while_test;
delimiter ;;
CREATE DEFINER = root@localhost PROCEDURE proc_while_test(IN n int)
BEGIN 
 DECLARE i int;
 DECLARE s int;
  SET i = 0;
 SET s = 0;
 WHILE i <= n DO
  SET s = s + i;
          set i++;
  set i = i + 1;
 end WHILE;
 select s;
end
;;
delimiter ;

 简单恢复模型的有点是允许高性能大容量复制操作,以及可以回收日志空间。但是必须重组最新的数据库或者差异备份后的更改。

UPDATE accounts SET balance = balance - 100.00 WHERE acc_nr = 254231426;

  1 //Prepare to restore the backup of Xtrabackup.
  2 [root@zlm2 16:37:11 /data/backup]
  3 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/2018-07-31_16-31-46/
  4 
  5 [root@zlm2 16:36:42 /data/mysql/mysql3306/data]
  6 #ls -l
  7 total 409732
  8 -rw-r----- 1 mysql mysql        56 Jul 30 17:25 auto.cnf
  9 -rw-r----- 1 mysql mysql     31332 Jul 30 21:08 error.log
 10 -rw-r----- 1 mysql mysql       871 Jul 30 21:08 ib_buffer_pool
 11 -rw-r----- 1 mysql mysql 104857600 Jul 30 21:08 ibdata1
 12 -rw-r----- 1 mysql mysql 104857600 Jul 30 21:08 ib_logfile0
 13 -rw-r----- 1 mysql mysql 104857600 Jul 30 17:22 ib_logfile1
 14 -rw-r----- 1 mysql mysql 104857600 Jul 30 17:22 ib_logfile2
 15 -rw-r----- 1 mysql mysql       131 Jul 30 21:08 master.info
 16 drwxr-x--- 2 mysql mysql      4096 Jul 30 17:22 mrbs
 17 drwxr-x--- 2 mysql mysql      4096 Jul 30 17:22 mysql
 18 drwxr-x--- 2 mysql mysql      8192 Jul 30 17:23 performance_schema
 19 -rw-r----- 1 mysql mysql       201 Jul 30 20:08 relay-bin.000001
 20 -rw-r----- 1 mysql mysql       936 Jul 30 20:08 relay-bin.000002
 21 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000003
 22 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000004
 23 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000005
 24 -rw-r----- 1 mysql mysql       454 Jul 30 20:08 relay-bin.000006
 25 -rw-r----- 1 mysql mysql       430 Jul 30 21:08 relay-bin.000007
 26 -rw-r----- 1 mysql mysql       133 Jul 30 20:11 relay-bin.index
 27 -rw-r----- 1 mysql mysql        53 Jul 30 21:08 relay-log.info
 28 -rw-r----- 1 mysql mysql       346 Jul 30 18:17 slow.log
 29 drwxr-x--- 2 mysql mysql      8192 Jul 30 17:22 sys
 30 drwxr-x--- 2 mysql mysql      4096 Jul 30 17:23 sysbench
 31 -rw-r----- 1 mysql mysql        21 Jul 30 17:23 xtrabackup_binlog_pos_innodb
 32 -rw-r----- 1 mysql mysql       595 Jul 30 17:23 xtrabackup_info
 33 -rw-r----- 1 mysql mysql         1 Jul 30 17:23 xtrabackup_master_key_id
 34 
 35 [root@zlm2 16:36:41 /data/mysql/mysql3306/data]
 36 #ps aux|grep mysqld
 37 root      3900  0.0  0.0 112640   960 pts/1    R+   16:38   0:00 grep --color=auto mysqld
 38 
 39 [root@zlm2 16:36:44 /data/mysql/mysql3306/data]
 40 #rm -rf *
 41 
 42 //Copy back the datafiles.
 43 [root@zlm2 16:38:31 /data/backup]
 44 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-31_16-31-46/
 45 
 46 [root@zlm2 16:38:48 /data/mysql/mysql3306/data]
 47 #ls -l
 48 total 421940
 49 -rw-r----- 1 root root       784 Jul 31 16:39 ib_buffer_pool
 50 -rw-r----- 1 root root 104857600 Jul 31 16:39 ibdata1
 51 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile0
 52 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile1
 53 -rw-r----- 1 root root 104857600 Jul 31 16:39 ib_logfile2
 54 -rw-r----- 1 root root  12582912 Jul 31 16:39 ibtmp1
 55 drwxr-x--- 2 root root      4096 Jul 31 16:39 mrbs
 56 drwxr-x--- 2 root root      4096 Jul 31 16:39 mysql
 57 drwxr-x--- 2 root root      8192 Jul 31 16:39 performance_schema
 58 drwxr-x--- 2 root root      8192 Jul 31 16:39 sys
 59 drwxr-x--- 2 root root      4096 Jul 31 16:39 sysbench
 60 -rw-r----- 1 root root        21 Jul 31 16:39 xtrabackup_binlog_pos_innodb
 61 -rw-r----- 1 root root       595 Jul 31 16:39 xtrabackup_info
 62 -rw-r----- 1 root root         1 Jul 31 16:39 xtrabackup_master_key_id
 63 
 64 [root@zlm2 16:40:26 /data/mysql/mysql3306/data]
 65 #sh /root/mysqld.sh
 66 
 67 [root@zlm2 16:40:33 /data/mysql/mysql3306/data]
 68 #mysql
 69 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.101' (111)
 70 
 71 [root@zlm2 16:40:37 /data/mysql/mysql3306/data]
 72 #chown -R mysql.mysql *
 73 
 74 [root@zlm2 16:41:00 /data/mysql/mysql3306/data]
 75 #sh /root/mysqld.sh
 76 
 77 [root@zlm2 16:41:02 /data/mysql/mysql3306/data]
 78 #mysql
 79 Welcome to the MySQL monitor.  Commands end with ; or g.
 80 Your MySQL connection id is 2
 81 Server version: 5.7.21-log MySQL Community Server (GPL)
 82 
 83 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 84 
 85 Oracle is a registered trademark of Oracle Corporation and/or its
 86 affiliates. Other names may be trademarks of their respective
 87 owners.
 88 
 89 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 90 
 91 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
 92 +----------+
 93 | count(*) |
 94 +----------+
 95 |    10000 |
 96 +----------+
 97 1 row in set (0.03 sec)
 98 
 99 //Continue to restore the incremental data with sql_thread.
100 [root@zlm2 16:39:55 /data/backup]
101 #ls -l
102 total 944
103 drwxr-x--- 7 root root   4096 Jul 31 16:38 2018-07-31_16-31-46
104 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103
105 -rw-r----- 1 root root    217 Jul 31 16:42 mysql-bin.000104
106 
107 [root@zlm2 16:43:19 /data/backup]
108 #for i in $(ls mysql-bin.0*)
109 > do
110 >     ext=$(echo $i | cut -d'.' -f2);
111 >     cp $i relay-bin.$ext;
112 > done
113 
114 [root@zlm2 16:45:20 /data/backup]
115 #ls -l
116 total 1884
117 drwxr-x--- 7 root root   4096 Jul 31 16:38 2018-07-31_16-31-46
118 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103
119 -rw-r----- 1 root root    217 Jul 31 16:42 mysql-bin.000104
120 -rw-r----- 1 root root 954995 Jul 31 16:45 relay-bin.000103
121 -rw-r----- 1 root root    217 Jul 31 16:45 relay-bin.000104
122 
123 [root@zlm2 16:45:23 /data/backup]
124 #ls ./relay-bin.0* > relay-bin.index
125 
126 [root@zlm2 16:45:31 /data/backup]
127 #ls -l
128 total 1888
129 drwxr-x--- 7 root root   4096 Jul 31 16:38 2018-07-31_16-31-46
130 -rw-r----- 1 root root 954995 Jul 31 16:42 mysql-bin.000103
131 -rw-r----- 1 root root    217 Jul 31 16:42 mysql-bin.000104
132 -rw-r----- 1 root root 954995 Jul 31 16:45 relay-bin.000103
133 -rw-r----- 1 root root    217 Jul 31 16:45 relay-bin.000104
134 -rw-r--r-- 1 root root     38 Jul 31 16:45 relay-bin.index
135 
136 [root@zlm2 16:45:33 /data/backup]
137 #cat relay-bin.index 
138 ./relay-bin.000103
139 ./relay-bin.000104
140 
141 [root@zlm2 16:45:37 /data/backup]
142 #chown mysql.mysql relay*
143 
144 [root@zlm2 16:45:51 /data/backup]
145 #ls -l
146 total 1888
147 drwxr-x--- 7 root  root    4096 Jul 31 16:38 2018-07-31_16-31-46
148 -rw-r----- 1 root  root  954995 Jul 31 16:42 mysql-bin.000103
149 -rw-r----- 1 root  root     217 Jul 31 16:42 mysql-bin.000104
150 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103
151 -rw-r----- 1 mysql mysql    217 Jul 31 16:45 relay-bin.000104
152 -rw-r--r-- 1 mysql mysql     38 Jul 31 16:45 relay-bin.index
153 
154 [root@zlm2 16:45:52 /data/backup]
155 #cp relay* /data/mysql/mysql3306/data
156 
157 [root@zlm2 16:46:08 /data/backup]
158 #cd /data/mysql/mysql3306/data
159 
160 [root@zlm2 16:46:25 /data/mysql/mysql3306/data]
161 #ls -l
162 total 422908
163 -rw-r----- 1 mysql mysql        56 Jul 31 16:41 auto.cnf
164 -rw-r----- 1 mysql mysql      5989 Jul 31 16:46 error.log
165 -rw-r----- 1 mysql mysql       784 Jul 31 16:39 ib_buffer_pool
166 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ibdata1
167 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ib_logfile0
168 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile1
169 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile2
170 -rw-r----- 1 mysql mysql  12582912 Jul 31 16:41 ibtmp1
171 -rw-r----- 1 mysql mysql         0 Jul 31 16:40 innodb_status.3949
172 -rw-r----- 1 mysql mysql      3835 Jul 31 16:46 innodb_status.3979
173 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mrbs
174 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mysql
175 -rw-r----- 1 mysql mysql         5 Jul 31 16:41 mysql.pid
176 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 performance_schema
177 -rw-r----- 1 root  root     954995 Jul 31 16:46 relay-bin.000103
178 -rw-r----- 1 root  root        217 Jul 31 16:46 relay-bin.000104
179 -rw-r--r-- 1 root  root         38 Jul 31 16:46 relay-bin.index
180 -rw-r----- 1 mysql mysql       173 Jul 31 16:41 slow.log
181 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 sys
182 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 sysbench
183 -rw-r----- 1 mysql mysql        21 Jul 31 16:39 xtrabackup_binlog_pos_innodb
184 -rw-r----- 1 mysql mysql       595 Jul 31 16:39 xtrabackup_info
185 -rw-r----- 1 mysql mysql         1 Jul 31 16:39 xtrabackup_master_key_id
186 
187 [root@zlm2 16:46:26 /data/mysql/mysql3306/data]
188 #chown mysql.mysql relay*
189 
190 [root@zlm2 16:46:37 /data/mysql/mysql3306/data]
191 #ls -l
192 total 422908
193 -rw-r----- 1 mysql mysql        56 Jul 31 16:41 auto.cnf
194 -rw-r----- 1 mysql mysql      6137 Jul 31 16:52 error.log
195 -rw-r----- 1 mysql mysql       784 Jul 31 16:39 ib_buffer_pool
196 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ibdata1
197 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:41 ib_logfile0
198 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile1
199 -rw-r----- 1 mysql mysql 104857600 Jul 31 16:39 ib_logfile2
200 -rw-r----- 1 mysql mysql  12582912 Jul 31 16:41 ibtmp1
201 -rw-r----- 1 mysql mysql         0 Jul 31 16:40 innodb_status.3949
202 -rw-r----- 1 mysql mysql      3835 Jul 31 16:53 innodb_status.3979
203 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mrbs
204 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 mysql
205 -rw-r----- 1 mysql mysql         5 Jul 31 16:41 mysql.pid
206 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 performance_schema
207 -rw-r----- 1 mysql mysql    954995 Jul 31 16:46 relay-bin.000103
208 -rw-r----- 1 mysql mysql       217 Jul 31 16:46 relay-bin.000104
209 -rw-r--r-- 1 mysql mysql        38 Jul 31 16:46 relay-bin.index
210 -rw-r----- 1 mysql mysql       173 Jul 31 16:41 slow.log
211 drwxr-x--- 2 mysql mysql      8192 Jul 31 16:39 sys
212 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:39 sysbench
213 -rw-r----- 1 mysql mysql        21 Jul 31 16:39 xtrabackup_binlog_pos_innodb
214 -rw-r----- 1 mysql mysql       595 Jul 31 16:39 xtrabackup_info
215 -rw-r----- 1 mysql mysql         1 Jul 31 16:39 xtrabackup_master_key_id
216 
217 zlm@192.168.56.101:3306 [(none)]>show master status;
218 +------------------+----------+--------------+------------------+-------------------------------------------------+
219 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
220 +------------------+----------+--------------+------------------+-------------------------------------------------+
221 | mysql-bin.000002 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
222 +------------------+----------+--------------+------------------+-------------------------------------------------+
223 1 row in set (0.00 sec)
224 
225 zlm@192.168.56.101:3306 [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=194,master_host='xxx';
226 Query OK, 0 rows affected (0.01 sec)
227 
228 zlm@192.168.56.101:3306 [(none)]>show slave statusG
229 *************************** 1. row ***************************
230                Slave_IO_State: 
231                   Master_Host: xxx
232                   Master_User: 
233                   Master_Port: 3306
234                 Connect_Retry: 60
235               Master_Log_File: 
236           Read_Master_Log_Pos: 4
237                Relay_Log_File: relay-bin.000103
238                 Relay_Log_Pos: 194
239         Relay_Master_Log_File: 
240              Slave_IO_Running: No
241             Slave_SQL_Running: No
242               Replicate_Do_DB: 
243           Replicate_Ignore_DB: 
244            Replicate_Do_Table: 
245        Replicate_Ignore_Table: 
246       Replicate_Wild_Do_Table: 
247   Replicate_Wild_Ignore_Table: 
248                    Last_Errno: 0
249                    Last_Error: 
250                  Skip_Counter: 0
251           Exec_Master_Log_Pos: 0 //No data here yet.
252               Relay_Log_Space: 955366
253               Until_Condition: None
254                Until_Log_File: 
255                 Until_Log_Pos: 0
256            Master_SSL_Allowed: No
257            Master_SSL_CA_File: 
258            Master_SSL_CA_Path: 
259               Master_SSL_Cert: 
260             Master_SSL_Cipher: 
261                Master_SSL_Key: 
262         Seconds_Behind_Master: NULL
263 Master_SSL_Verify_Server_Cert: No
264                 Last_IO_Errno: 0
265                 Last_IO_Error: 
266                Last_SQL_Errno: 0
267                Last_SQL_Error: 
268   Replicate_Ignore_Server_Ids: 
269              Master_Server_Id: 0
270                   Master_UUID: 
271              Master_Info_File: /data/mysql/mysql3306/data/master.info
272                     SQL_Delay: 0
273           SQL_Remaining_Delay: NULL
274       Slave_SQL_Running_State: 
275            Master_Retry_Count: 86400
276                   Master_Bind: 
277       Last_IO_Error_Timestamp: 
278      Last_SQL_Error_Timestamp: 
279                Master_SSL_Crl: 
280            Master_SSL_Crlpath: 
281            Retrieved_Gtid_Set: //No data here.
282             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696
283                 Auto_Position: 0
284          Replicate_Rewrite_DB: 
285                  Channel_Name: 
286            Master_TLS_Version: 
287 1 row in set (0.00 sec)
288 
289 [root@zlm2 16:59:42 /data/backup]
290 #ls -l
291 total 1888
292 drwxr-x--- 7 root  root    4096 Jul 31 16:38 2018-07-31_16-31-46
293 -rw-r----- 1 root  root  954995 Jul 31 16:42 mysql-bin.000103
294 -rw-r----- 1 root  root     217 Jul 31 16:42 mysql-bin.000104
295 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103
296 -rw-r----- 1 mysql mysql    217 Jul 31 16:45 relay-bin.000104
297 -rw-r--r-- 1 mysql mysql     38 Jul 31 16:45 relay-bin.index
298 
299 //Find out the end position to set util clause.
300 [root@zlm2 16:59:43 /data/backup]
301 #mysqlbinlog --base64-output=decode-rows relay-bin.000103 > 103.log
302 
303 [root@zlm2 17:01:22 /data/backup]
304 #tail -20 103.log
305 #180731 16:35:17 server id 1003306  end_log_pos 954724 CRC32 0xe3e63622     Delete_rows: table id 114 flags: STMT_END_F
306 # at 954724
307 #180731 16:35:17 server id 1003306  end_log_pos 954755 CRC32 0x84cf5136     Xid = 31
308 COMMIT/*!*/;
309 # at 954755 //This position is which I need to set until to.
310 #180731 16:35:29 server id 1003306  end_log_pos 954820 CRC32 0xef03ef84     GTID    last_committed=2    sequence_number=3    rbr_only=no
311 SET @@SESSION.GTID_NEXT= '2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699'/*!*/;
312 # at 954820
313 #180731 16:35:29 server id 1003306  end_log_pos 954948 CRC32 0x0309b10f     Query    thread_id=2    exec_time=0    error_code=0
314 use `sysbench`/*!*/;
315 SET TIMESTAMP=1533047729/*!*/;
316 DROP TABLE `sbtest5` /* generated by server */
317 /*!*/;
318 # at 954948
319 #180731 16:35:37 server id 1003306  end_log_pos 954995 CRC32 0xc1ca182a     Rotate to mysql-bin.000104  pos: 4
320 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
321 DELIMITER ;
322 # End of log file
323 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
324 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
325 
326 zlm@192.168.56.101:3306 [(none)]>start slave sql_thread until relay_log_file='relay-bin.000103',relay_log_pos=954755;
327 Query OK, 0 rows affected (0.00 sec)
328 
329 zlm@192.168.56.101:3306 [(none)]>show slave statusG
330 *************************** 1. row ***************************
331                Slave_IO_State: 
332                   Master_Host: xxx
333                   Master_User: 
334                   Master_Port: 3306
335                 Connect_Retry: 60
336               Master_Log_File: 
337           Read_Master_Log_Pos: 4
338                Relay_Log_File: relay-bin.000103
339                 Relay_Log_Pos: 954755
340         Relay_Master_Log_File: 
341              Slave_IO_Running: No
342             Slave_SQL_Running: No
343               Replicate_Do_DB: 
344           Replicate_Ignore_DB: 
345            Replicate_Do_Table: 
346        Replicate_Ignore_Table: 
347       Replicate_Wild_Do_Table: 
348   Replicate_Wild_Ignore_Table: 
349                    Last_Errno: 0
350                    Last_Error: 
351                  Skip_Counter: 0
352           Exec_Master_Log_Pos: 954755 //This is the terminal relay log position I set just now.
353               Relay_Log_Space: 955366
354               Until_Condition: Relay
355                Until_Log_File: relay-bin.000103
356                 Until_Log_Pos: 954755
357            Master_SSL_Allowed: No
358            Master_SSL_CA_File: 
359            Master_SSL_CA_Path: 
360               Master_SSL_Cert: 
361             Master_SSL_Cipher: 
362                Master_SSL_Key: 
363         Seconds_Behind_Master: NULL
364 Master_SSL_Verify_Server_Cert: No
365                 Last_IO_Errno: 0
366                 Last_IO_Error: 
367                Last_SQL_Errno: 0
368                Last_SQL_Error: 
369   Replicate_Ignore_Server_Ids: 
370              Master_Server_Id: 0
371                   Master_UUID: 
372              Master_Info_File: /data/mysql/mysql3306/data/master.info
373                     SQL_Delay: 0
374           SQL_Remaining_Delay: NULL
375       Slave_SQL_Running_State: 
376            Master_Retry_Count: 86400
377                   Master_Bind: 
378       Last_IO_Error_Timestamp: 
379      Last_SQL_Error_Timestamp: 
380                Master_SSL_Crl: 
381            Master_SSL_Crlpath: 
382            Retrieved_Gtid_Set: 
383             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698
384                 Auto_Position: 0
385          Replicate_Rewrite_DB: 
386                  Channel_Name: 
387            Master_TLS_Version: 
388 1 row in set (0.00 sec)
389 
390 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
391 +----------+
392 | count(*) |
393 +----------+
394 |     5000 |
395 +----------+
396 1 row in set (0.00 sec)

  在上面的例子中,set i++;是会导致整个while循环报错。在mysql的存储过程中,i++是不允许这样写的。需要写成i=i+1这种形式才可以。

2.完全恢复模型

UPDATE accounts SET balance = balance + 100.00 WHERE acc_nr = 676535301;

 

  完全恢复模型使用数据库备份和事务日志备份提供将数据库恢复到故障点或特定即时点的能力。为保证这种恢复程度,包括大容量操作(如SELECT INTO、CREATE INDEX和大容量装载数据)在内的所有操作都将完整地记入日志。

INSERT INTO transfers (acc_nr, amount, reference) VALUES (254231426, -100.00, ’Dentist’);

3. The supplemented experiment.

完全恢复模型的优点是可以恢复到任意即时点,这样数据文件的丢失和损坏不会导致工作损失,但是如果事务日志损坏,则必须重新做最新的日志备份后进行修改。

INSERT INTO transfers (acc_nr, amount, reference) VALUES (676535301, 100.00, ’John Smith’);

  1 //Shudown the instance.
  2 [root@zlm2 18:27:23 /data/mysql/mysql3306/data]
  3 #ps aux|grep mysqld
  4 mysql     3979  0.0 20.0 1072196 204196 pts/1  Sl   16:41   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
  5 root      4265  0.0  0.0 112640   960 pts/0    R+   18:27   0:00 grep --color=auto mysqld
  6 
  7 [root@zlm2 18:27:28 /data/mysql/mysql3306/data]
  8 #mysqladmin shutdown
  9 
 10 [root@zlm2 18:27:42 /data/mysql/mysql3306/data]
 11 
 12 //Copy back the data one more time(no need to executed with option "--apply-log" first this time).
 13 [root@zlm2 18:28:13 /data/backup]
 14 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-31_16-31-46/
 15 
 16 //Copy the relay logs to datadir directory again.
 17 [root@zlm2 18:29:22 /data/backup]
 18 #ls -l
 19 total 1904
 20 -rw-r--r-- 1 root  root   16082 Jul 31 17:01 103.log
 21 drwxr-x--- 7 root  root    4096 Jul 31 16:38 2018-07-31_16-31-46
 22 -rw-r----- 1 root  root  954995 Jul 31 16:42 mysql-bin.000103
 23 -rw-r----- 1 root  root     217 Jul 31 16:42 mysql-bin.000104
 24 -rw-r----- 1 mysql mysql 954995 Jul 31 16:45 relay-bin.000103
 25 -rw-r----- 1 mysql mysql    217 Jul 31 16:45 relay-bin.000104
 26 -rw-r--r-- 1 mysql mysql     38 Jul 31 16:45 relay-bin.index
 27 
 28 [root@zlm2 18:29:45 /data/backup]
 29 #cp relay* /data/mysql/mysql3306/data
 30 
 31 [root@zlm2 18:30:25 /data/mysql/mysql3306/data]
 32 #ls -l
 33 total 422884
 34 -rw-r----- 1 root root       784 Jul 31 18:29 ib_buffer_pool
 35 -rw-r----- 1 root root 104857600 Jul 31 18:29 ibdata1
 36 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile0
 37 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile1
 38 -rw-r----- 1 root root 104857600 Jul 31 18:28 ib_logfile2
 39 -rw-r----- 1 root root  12582912 Jul 31 18:29 ibtmp1
 40 drwxr-x--- 2 root root      4096 Jul 31 18:29 mrbs
 41 drwxr-x--- 2 root root      4096 Jul 31 18:29 mysql
 42 drwxr-x--- 2 root root      8192 Jul 31 18:29 performance_schema
 43 -rw-r----- 1 root root    954995 Jul 31 18:30 relay-bin.000103
 44 -rw-r----- 1 root root       217 Jul 31 18:30 relay-bin.000104
 45 -rw-r--r-- 1 root root        38 Jul 31 18:30 relay-bin.index
 46 drwxr-x--- 2 root root      8192 Jul 31 18:29 sys
 47 drwxr-x--- 2 root root      4096 Jul 31 18:29 sysbench
 48 -rw-r----- 1 root root        21 Jul 31 18:29 xtrabackup_binlog_pos_innodb
 49 -rw-r----- 1 root root       595 Jul 31 18:29 xtrabackup_info
 50 -rw-r----- 1 root root         1 Jul 31 18:29 xtrabackup_master_key_id
 51 
 52 [root@zlm2 18:31:11 /data/mysql/mysql3306/data]
 53 #chown -R mysql.mysql *
 54 
 55 [root@zlm2 18:31:19 /data/mysql/mysql3306/data]
 56 #sh /root/mysqld.sh
 57 
 58 [root@zlm2 18:31:23 /data/mysql/mysql3306/data]
 59 #mysql
 60 Welcome to the MySQL monitor.  Commands end with ; or g.
 61 Your MySQL connection id is 2
 62 Server version: 5.7.21-log MySQL Community Server (GPL)
 63 
 64 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 65 
 66 Oracle is a registered trademark of Oracle Corporation and/or its
 67 affiliates. Other names may be trademarks of their respective
 68 owners.
 69 
 70 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 71 
 72 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
 73 +----------+
 74 | count(*) |
 75 +----------+
 76 |    10000 |
 77 +----------+
 78 1 row in set (0.04 sec)
 79 
 80 zlm@192.168.56.101:3306 [(none)]>show master status;
 81 +------------------+----------+--------------+------------------+-------------------------------------------------+
 82 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 83 +------------------+----------+--------------+------------------+-------------------------------------------------+
 84 | mysql-bin.000003 |      210 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698 |
 85 +------------------+----------+--------------+------------------+-------------------------------------------------+
 86 1 row in set (0.00 sec)
 87 
 88 zlm@192.168.56.101:3306 [(none)]>reset master;
 89 Query OK, 0 rows affected (0.03 sec)
 90 
 91 zlm@192.168.56.101:3306 [(none)]>show master status;
 92 +------------------+----------+--------------+------------------+-------------------+
 93 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 94 +------------------+----------+--------------+------------------+-------------------+
 95 | mysql-bin.000001 |      154 |              |                  |                   |
 96 +------------------+----------+--------------+------------------+-------------------+
 97 1 row in set (0.00 sec)
 98 
 99 zlm@192.168.56.101:3306 [(none)]>show slave statusG
100 Empty set (0.00 sec)
101 
102 zlm@192.168.56.101:3306 [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696';
103 Query OK, 0 rows affected (0.00 sec)
104 
105 zlm@192.168.56.101:3306 [(none)]>show master status;
106 +------------------+----------+--------------+------------------+-------------------------------------------------+
107 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
108 +------------------+----------+--------------+------------------+-------------------------------------------------+
109 | mysql-bin.000001 |      154 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
110 +------------------+----------+--------------+------------------+-------------------------------------------------+
111 1 row in set (0.00 sec)
112 
113 zlm@192.168.56.101:3306 [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=194,master_host='xxx';
114 Query OK, 0 rows affected (0.01 sec)
115 
116 zlm@192.168.56.101:3306 [(none)]>show slave statusG
117 *************************** 1. row ***************************
118                Slave_IO_State: 
119                   Master_Host: xxx
120                   Master_User: 
121                   Master_Port: 3306
122                 Connect_Retry: 60
123               Master_Log_File: 
124           Read_Master_Log_Pos: 4
125                Relay_Log_File: relay-bin.000103
126                 Relay_Log_Pos: 194
127         Relay_Master_Log_File: 
128              Slave_IO_Running: No
129             Slave_SQL_Running: No
130               Replicate_Do_DB: 
131           Replicate_Ignore_DB: 
132            Replicate_Do_Table: 
133        Replicate_Ignore_Table: 
134       Replicate_Wild_Do_Table: 
135   Replicate_Wild_Ignore_Table: 
136                    Last_Errno: 0
137                    Last_Error: 
138                  Skip_Counter: 0
139           Exec_Master_Log_Pos: 0
140               Relay_Log_Space: 955366
141               Until_Condition: None
142                Until_Log_File: 
143                 Until_Log_Pos: 0
144            Master_SSL_Allowed: No
145            Master_SSL_CA_File: 
146            Master_SSL_CA_Path: 
147               Master_SSL_Cert: 
148             Master_SSL_Cipher: 
149                Master_SSL_Key: 
150         Seconds_Behind_Master: NULL
151 Master_SSL_Verify_Server_Cert: No
152                 Last_IO_Errno: 0
153                 Last_IO_Error: 
154                Last_SQL_Errno: 0
155                Last_SQL_Error: 
156   Replicate_Ignore_Server_Ids: 
157              Master_Server_Id: 0
158                   Master_UUID: 
159              Master_Info_File: /data/mysql/mysql3306/data/master.info
160                     SQL_Delay: 0
161           SQL_Remaining_Delay: NULL
162       Slave_SQL_Running_State: 
163            Master_Retry_Count: 86400
164                   Master_Bind: 
165       Last_IO_Error_Timestamp: 
166      Last_SQL_Error_Timestamp: 
167                Master_SSL_Crl: 
168            Master_SSL_Crlpath: 
169            Retrieved_Gtid_Set: 
170             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696
171                 Auto_Position: 0
172          Replicate_Rewrite_DB: 
173                  Channel_Name: 
174            Master_TLS_Version: 
175 1 row in set (0.00 sec)
176 
177 zlm@192.168.56.101:3306 [(none)]>start slave sql_thread until sql_before_gtids='2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699';
178 Query OK, 0 rows affected (0.51 sec)
179 
180 zlm@192.168.56.101:3306 [(none)]>show slave statusG
181 *************************** 1. row ***************************
182                Slave_IO_State: 
183                   Master_Host: xxx
184                   Master_User: 
185                   Master_Port: 3306
186                 Connect_Retry: 60
187               Master_Log_File: 
188           Read_Master_Log_Pos: 4
189                Relay_Log_File: relay-bin.000103
190                 Relay_Log_Pos: 954755
191         Relay_Master_Log_File: 
192              Slave_IO_Running: No
193             Slave_SQL_Running: No
194               Replicate_Do_DB: 
195           Replicate_Ignore_DB: 
196            Replicate_Do_Table: 
197        Replicate_Ignore_Table: 
198       Replicate_Wild_Do_Table: 
199   Replicate_Wild_Ignore_Table: 
200                    Last_Errno: 0
201                    Last_Error: 
202                  Skip_Counter: 0
203           Exec_Master_Log_Pos: 954755
204               Relay_Log_Space: 955366
205               Until_Condition: SQL_BEFORE_GTIDS //This time we use this option to start slave sql_thread.
206                Until_Log_File: 
207                 Until_Log_Pos: 0
208            Master_SSL_Allowed: No
209            Master_SSL_CA_File: 
210            Master_SSL_CA_Path: 
211               Master_SSL_Cert: 
212             Master_SSL_Cipher: 
213                Master_SSL_Key: 
214         Seconds_Behind_Master: NULL
215 Master_SSL_Verify_Server_Cert: No
216                 Last_IO_Errno: 0
217                 Last_IO_Error: 
218                Last_SQL_Errno: 0
219                Last_SQL_Error: 
220   Replicate_Ignore_Server_Ids: 
221              Master_Server_Id: 0
222                   Master_UUID: 
223              Master_Info_File: /data/mysql/mysql3306/data/master.info
224                     SQL_Delay: 0
225           SQL_Remaining_Delay: NULL
226       Slave_SQL_Running_State: 
227            Master_Retry_Count: 86400
228                   Master_Bind: 
229       Last_IO_Error_Timestamp: 
230      Last_SQL_Error_Timestamp: 
231                Master_SSL_Crl: 
232            Master_SSL_Crlpath: 
233            Retrieved_Gtid_Set: 
234             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715698
235                 Auto_Position: 0
236          Replicate_Rewrite_DB: 
237                  Channel_Name: 
238            Master_TLS_Version: 
239 1 row in set (0.00 sec)
240 
241 zlm@192.168.56.101:3306 [(none)]>select count(*) from sysbench.sbtest5;
242 +----------+
243 | count(*) |
244 +----------+
245 |     5000 |
246 +----------+
247 1 row in set (0.00 sec)

3.大容量日志记录恢复模型

COMMIT;

 

  大容量日志记录恢复模型为某些大规模或大容量复制操作提供最佳性能和最少日志使用空间。在这种模型中,大容量复制操作的数据丢失程度要比完全恢复模型严重,因为在这种模型下,只记录操作的最小日志,无法逐个控制这些操作。它只允许数据库恢复到事务日志备份的结尾处,不支持即时点恢复。

 

**    Ultimately,the incremental data comes back gain.The experiment was finished successfully.**

  大容量日志记录恢复模型的优点是可以节省日志空间,但是如果日志损坏或者日志备份后发生了大容量操作,则必须重做自上次备份后所做的更改。

同时事务:

 

 不同的hi付模型针对不同的性能、磁盘和磁带空间以及保护数据丢失的需要。恢复模型决定总体备份策略,包括可以使用的备份类型,即选择一种恢复模型,可以确定如何备份数据以及能承受何种程度的数据丢失,由此确定了数据的恢复过程。

复杂情况:多种同时事务

 

二。查看备份信息

情况1:按顺序执行事务

由于恢复数据库与备份数据库之间往往存在较长的时间差,难以记住备份设备和备份文件及其所备份的数据库,需要对这些信息进行查看。

  很简单去实行,在一些情况下工作的很好

  需要查看的信息通常包括:备份集内的数据和日志文件、备份首部信息、介质首部信息。可以使用SQL Server管理平台和Transact-SQL语句查看这些信息。

  但是有时过慢了:

 1.使用SQL Server管理平台查看备份信息

    CPU可以保持空闲当I/O转换过程中,反之亦然

  使用SQL Server查看所有备份介质属性的操作步骤如下:

    慢的语句可能会阻碍快的那个

  (1) 打开SQL Server管理平台,在对象资源管理器中,展开结点”服务器树“→”服务器对象“→”备份设备“,右击某个具体备份设备名称,在弹出的快捷菜单上选择”属性“命令,打开”备份设备“属性窗口。

    事务可能需要去等待用户输入

(2)在”备份设备“属性窗口选择”媒体内容“选择卡,打开的窗口,在列表框中列出所选备份媒体的有关信息。

情况2:事务的交错执行

2.使用Transact-SQL语句查看备份信息

  等待时间一半都减少特别明显了

 RESTORE HEADERONLY语句的格式为:

  在单命令事务中也能进行工作(分裂成多种更小的操作)

RESTORE HEADERONLY

  但是难以正确实施进行

FROM <backpi_device>

  这就是DBMS所做的事

[WITH {NOUNLOAD|UNLOAD}

 

[[,]FILE =file_number]

ACID规则:

[[,]PASSWORD={password|@password_var}]

ACID规则是摆正多个并行事务能被可靠处理的一组方法策略

[[,]MEDIAPASSWORD={mediapassword|@mediapassword_var}]

Atomicity:原子性   Consistency:一致性   Isolation:交易之间不被相互作用     Durability:持久性

<backup_device>::={

复杂的是隔离或可串行化的:确保并行执行相当于对某个顺序的顺序执行。

{'logical_backup_device_name'|@logical_backup_device_name_var}

这就是并发控制的全部内容。

|{DISK|TAPE}={'physical_backup_device_name'|@physical_backup_name_var}

注意:为了保持DB编程的可管理性,DBMS确保串行化是至关重要的,想象一下担心其他查询会妨碍运行的任何查询!

}]

 

各选项含义如下:

并发控制:

(1)<backup_device>:指定备份操作时要使用的逻辑或物理设备。

查询被分成许多较小的操作。

(2)FILE=file_number:标识要处理的备份集。

允许对这些操作进行交错。

(3)PASSWORD={password|@password_var}:备份集密码。

启用并发性

(4)MEDIAPASSWORD={mediapassword|@mediapassword_var}:媒体集密码。

 

RESTORE HEADERONLY 语句返回的结果集包括:备份集名称、备份集类型、备份集的有效时间、服务器名称、数据库名称、备份大小等信息。

但如果使用相同的数据,则会出现问题。

举例:使用Transact-SQL语句得到back4数据库备份的信息。

不能更新(Lost Update)

RESTORE HEADERONLY FROM back4

不好识别(Dirty Reads)

三。恢复数据库

不能一致识别(Inconsistent Reads)

1.使用SQL Server管理平台恢复数据库

 

其操作步骤如下:

并发控制的目的:

(1)在SQL Server管理平台的”对象资源管理器“中,展开数据库文件夹,右击要进行还原的数据库图标,这里以Sales数据库为例,从弹出的快捷菜单中选择”任务/还原/数据库“选项,打开”还原数据库“窗口。

允许通用的并发,但禁止危险的相互作用。

(2)在”还原数据库“窗口的”常规“选项卡中,”目标数据库“下拉列表框用于选择要还原的数据库;”目标时间点“文本框用于设置还原时间点,可以保留默认值,也可以通过单击旁边的浏览按钮打开”时点还原“对话框,选择具体的日期和时间,对于完整数据库备份恢复,只能恢复到完全备份完成的时间点;”还原的源“区域中的”源数据库“下拉列表框用于选择要还原的备份的数据库存的名称;”源设备“文本框用于设置还原的备份设备的位置;”选择用于还原的备份集“网格用于选择还原的备份。

 

(3)选择”选项“项。在其中进行还原选项和恢复状态的设置。其中,”覆盖现有数据库“复选框被选中表示恢复操作覆盖所有现有数据库及相关文件;”保留复制设置“复选框被选中表示将已发布的数据库还原到创建该数据库的服务器之外的服务器时,保留复制设置;”还原每个备份之前进行提示“复选框被选中表示在还原每个备份设置之前要求用户确认;”限制访问还原的数据库“复选框被选中表示还原后的数据库仅供db_owner、dbreator或sysadmin的成员使用;”将数据库文件还原为“区域可选择数据文件和日志文件的路径。

Lost update:

(4)设置完成后,单击”确定“按钮,即可还原数据库,并在还原成功后出现消息对话框,要求用户确认还原已成功完成。

图片 1

友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有