1
SET DEBUG_SYNC= 'RESET';
3
DROP DATABASE IF EXISTS db1;
4
DROP DATABASE IF EXISTS db2;
5
DROP DATABASE IF EXISTS db3;
6
Create database 1 and a table then populate it
8
CREATE TABLE db1.t1 (a INT) ENGINE=MYISAM;
9
INSERT INTO db1.t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
10
INSERT INTO db1.t1 SELECT * FROM db1.t1;
11
INSERT INTO db1.t1 SELECT * FROM db1.t1;
12
INSERT INTO db1.t1 SELECT * FROM db1.t1;
13
INSERT INTO db1.t1 SELECT * FROM db1.t1;
14
INSERT INTO db1.t1 SELECT * FROM db1.t1;
15
INSERT INTO db1.t1 SELECT * FROM db1.t1;
16
INSERT INTO db1.t1 SELECT * FROM db1.t1;
17
INSERT INTO db1.t1 SELECT * FROM db1.t1;
18
INSERT INTO db1.t1 SELECT * FROM db1.t1;
19
INSERT INTO db1.t1 SELECT * FROM db1.t1;
20
INSERT INTO db1.t1 SELECT * FROM db1.t1;
21
INSERT INTO db1.t1 SELECT * FROM db1.t1;
22
INSERT INTO db1.t1 SELECT * FROM db1.t1;
23
INSERT INTO db1.t1 SELECT * FROM db1.t1;
24
INSERT INTO db1.t1 SELECT * FROM db1.t1;
25
CREATE TABLE db1.t2 (a int) ENGINE=MEMORY;
26
INSERT INTO db1.t2 VALUES (1),(2),(3),(4),(5);
27
CREATE TABLE db1.t3 (a int) ENGINE=INNODB;
28
INSERT INTO db1.t3 VALUES (11),(12),(13);
29
Show initial count of table
30
SELECT COUNT(*) FROM db1.t1;
33
SELECT COUNT(*) FROM db1.t2;
36
SELECT COUNT(*) FROM db1.t3;
40
Create database 2 and a table then populate it and add a trigger
41
that updates the table in database 1
43
CREATE TABLE db2.t2 (A INT);
44
CREATE TRIGGER db2.trg AFTER INSERT ON db2.t2 FOR EACH ROW
46
INSERT INTO db1.t1 VALUES ('99');
50
BACKUP DATABASE db1 TO 'db1.bak';
54
now start the restore and while the restore is running, fire the trigger
55
activate synchronization points for restore.
56
SET DEBUG_SYNC= 'restore_in_progress SIGNAL wait_for_restore WAIT_FOR finish';
57
RESTORE FROM 'db1.bak';
59
Wait for restore to reach its synchronization point.
60
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_restore';
61
breakpoints: Show process list.
62
SELECT id, command, state, info FROM INFORMATION_SCHEMA.PROCESSLIST
63
WHERE info LIKE "RESTORE%";
65
Now do the insert while restore is running.
66
INSERT INTO db2.t2 VALUES (0);
68
breakpoints: Sending finish signal to wake restore.
69
SET DEBUG_SYNC= 'now SIGNAL finish';
70
Reattach to connection 2 and finish.
71
Reattach to connection 1 and finish.
74
Show the count for t1. It should be 1 more than before restore.
75
SELECT COUNT(*) FROM db1.t1;
90
SET DEBUG_SYNC= 'RESET';
92
now start the restore and while the restore is running, fire the trigger
93
activate synchronization points for restore.
94
SET DEBUG_SYNC= 'restore_in_progress SIGNAL wait_for_restore WAIT_FOR finish';
95
RESTORE FROM 'db1.bak';
97
Wait for restore to reach its synchronization point.
98
SET DEBUG_SYNC= 'now WAIT_FOR wait_for_restore';
99
breakpoints: Show process list.
100
SELECT id, command, state, info FROM INFORMATION_SCHEMA.PROCESSLIST
101
WHERE info LIKE "RESTORE%";
103
Now do the select while restore is running.
104
SELECT * FROM db1.t1 limit 10;
106
breakpoints: Sending finish signal to wake restore.
107
SET DEBUG_SYNC= 'now SIGNAL finish';
108
Reattach to connection 2 and finish.
120
Reattach to connection 1 and finish.
123
Show the count for t1. It should be the same as before restore.
124
SELECT COUNT(*) FROM db1.t1;
127
SELECT * FROM db1.t2;
134
SELECT * FROM db1.t3;
139
SET DEBUG_SYNC= 'RESET';
140
Create a database with a table and a view using the MyISAM engine.
142
CREATE TABLE db3.t1(name CHAR(10)) ENGINE=MYISAM;
143
INSERT INTO db3.t1 VALUES('A'),('B'),('C'),('D');
144
CREATE VIEW db3.v1 AS SELECT * FROM db3.t1;
145
Show the data before backup
146
SELECT * FROM db3.t1;
152
SELECT * FROM db3.v1;
159
BACKUP DATABASE db3 TO 'db3.bak';
162
Now drop then restore the database.
164
RESTORE FROM 'db3.bak';
167
Show the table and view.
168
SHOW FULL TABLES FROM db3;
169
Tables_in_db3 Table_type
172
Show the data after restore.
173
SELECT * FROM db3.v1;
179
SELECT * FROM db3.t1;