MySQL Blogs

Portable table space part III or We can do it (with partition)

First of all, I have to say that as usual I did the three investigation, because I had fun.

MySQL is still fun for me, and also if I am not touching the magic topic of the moment like SSD,

replication and so on, I enjoy what I was doing, and I see how I can save my customer data, with this.

Second, I thanks again the people who helps me in understanding better MySQL, in this case Mattias Jonsson, who points me in right direction.

 

The Solution

The solution is a little bit cumbersome, but it works.

I have assume that:

  • a customer have a data-set which contains a partitioned table
  • we can have short period of read only data

To use the DISCARD tablespace on a partition the process is:

  1. - LOCK the table with partition and the final table where I will put the data
  2. - EXCHANGE the table with the partition
  3. - Flush the table
  4. - save the files
  5. - EXCHANGE AGAIN table with partition
  6. - reload the record IF ANY from the table I have move back to the partition (delta insert during the first exercise)
  7. - Flush again
  8. - save files

To recover do the usual procedure but against the table then EXCHANGE it with the partition.

It is obvious that more time pass more DATA will differ from the saved tablespace and the live data set, so longer it will take to reload the data.

Anyhow below the full process in detail, have fun.

Process details

INITIAL data set

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

(root@localhost) [test_tablespace1]>select * FROM partD;
Empty SET (0.00 sec)
(root@localhost) [test_tablespace1]>show CREATE TABLE tbtest_part \G
*************************** 1. row ***************************
TABLE: tbtest_part
CREATE TABLE: CREATE TABLE 'tbtest_part' (
'a' int(11) NOT NULL DEFAULT '0',
'b' char(3) DEFAULT 'AAA',
'dat' timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
'part' int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (part)
(PARTITION partA VALUES IN (1) ENGINE = InnoDB,
PARTITION partB VALUES IN (2) ENGINE = InnoDB,
PARTITION partC VALUES IN (3) ENGINE = InnoDB,
PARTITION partD VALUES IN (4) ENGINE = InnoDB) */
1 row IN SET (0.02 sec)
(root@localhost) [test_tablespace1]>select COUNT(*) FROM tbtest_part WHERE part=4;
+----------+
| COUNT(*) |
+----------+
| 1960864 |
+----------+
1 row IN SET (0.95 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>create TABLE parD SELECT * FROM tbtest_part LIMIT 0;
 

 

I have to exchange the PARTITION and then use the delta to fill the difference.

lock tables tbtest_part write, partD write;   then run the  ALTER TABLE tbtest_part EXCHANGE PARTITION partD with table partD;

 

 

1
2
3
4
5
6
7

(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part EXCHANGE PARTITION partD WITH TABLE partD;
Query OK, 0 rows affected (0.32 sec)
(root@localhost) [test_tablespace1]>unlock TABLES;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test_tablespace1]>FLUSH TABLE partD WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
 

 

Time to save the files in the backup directory

 

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 1407388
drwx------ 2 mysql mysql 4096 2012-05-07 17:16 ./
drwxr-xr-x 5 mysql mysql 4096 2012-05-07 11:41 ../
-rw-rw---- 1 mysql mysql 65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql 523 2012-05-07 17:16 partD.cfg
-rw-rw---- 1 mysql mysql 8636 2012-05-07 11:41 partD.frm
-rw-rw---- 1 mysql mysql 96468992 2012-05-07 11:45 partD.ibd
-rw-r----- 1 mysql mysql 476 2012-05-05 19:06 tbtest_100MB.cfg
-rw-r----- 1 mysql mysql 8606 2012-05-05 19:06 tbtest_100MB.frm
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.* /home/mysql/backup/

 

 

Let assume that during a period of time N the table will get additional 600 records

such that when we bring it back we have to reinsert them.

 

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part EXCHANGE PARTITION partD WITH TABLE partD;
Query OK, 0 rows affected (1.46 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1960864 |
+----------+
1 row IN SET (0.94 sec)
(root@localhost) [test_tablespace1]>select count(*) FROM partD;
+----------+
| count(*) |
+----------+
| 600 |
+----------+
1 row IN SET (0.00 sec)
(root@localhost) [test_tablespace1]>insert INTO tbtest_part SELECT * FROM partD;
Query OK, 600 rows affected (0.04 sec)
Records: 600 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1961464 |
+----------+
1 row IN SET (0.94 sec)
 

 

Good all data recover, now we must do something to preserver our set, we must save again the partD table to preserve the delta

we miss during the EXCHANGE.

We must also rename the table's files.

 

 

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.ibd /home/mysql/backup/parDdelta.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.frm /home/mysql/backup/parDdelta.frm
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp partD.cfg /home/mysql/backup/parDdelta.cfg

 

 

 

Assume that we have a disaster and data is lost, we will simulate truncating the partition

 

1
2
3
4
5
6
7
8
9
10

(root@localhost) [test_tablespace1]>alter TABLE tbtest_part TRUNCATE partition partD;
Query OK, 0 rows affected (0.27 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row IN SET (0.00 sec)

 

 

Help my data is lost!!!

And assume I still have insert until I put the table on reed only (hoping I can doit at elast for short time):

 

1
2
3
4
5
6
7

(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 200 |
+----------+
1 row IN SET (0.00 sec)

 

 

Now we have to:

  1. re-attach the old dataset to a new table and exchange with the partitioned table.
  2. save new data in a mew table
  3. re-attach the tablespace to partionD and reload the data;
  4. reload the delta between crash and new inserts

let's go:

1) Do the different steps to have the original data back

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

(root@localhost) [test_tablespace1]>create TABLE partD SELECT * FROM partD_temp LIMIT 0;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>ALTER TABLE partD DISCARD tablespace;
Query OK, 0 rows affected (0.03 sec)

Copy old TABLE space FROM my BACKUP area TO DATA directory
(root@localhost) [test_tablespace1]>ALTER TABLE partD IMPORT tablespace;
Query OK, 0 rows affected (30.94 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest_part EXCHANGE PARTITION partD WITH TABLE partD;
Query OK, 0 rows affected (1.43 sec)
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1960864 |
+----------+
1 row IN SET (0.93 sec)

 

 

Finally save the DELTA of the data in a different table to do not overwrite it.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

(root@localhost) [test_tablespace1]> RENAME TABLE partD TO partD_AFTER_EMERGENCY;
Query OK, 0 rows affected (0.08 sec)
2) reload DATA of the partD delta (the 600 entries we saved before)
(root@localhost) [test_tablespace1]>create TABLE parDdelta SELECT * FROM tbtest_part LIMIT 0;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>ALTER TABLE parDdelta DISCARD tablespace;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE parDdelta IMPORT tablespace;
Query OK, 0 rows affected (2.95 sec)
3) reload the DATA FROM the reattached tablespace
(root@localhost) [test_tablespace1]>select count(*) FROM parDdelta;
+----------+
| count(*) |
+----------+
| 600 |
+----------+
1 row IN SET (0.00 sec)
(root@localhost) [test_tablespace1]>insert INTO tbtest_part SELECT * FROM parDdelta;
Query OK, 600 rows affected (0.04 sec)
Records: 600 Duplicates: 0 Warnings: 0

 

 

4) reload the missed delta after the EMERGENCY

 

1
2
3
4
5
6
7
8
9
10

(root@localhost) [test_tablespace1]>insert INTO tbtest_part SELECT * FROM partD_AFTER_EMERGENCY;
Query OK, 200 rows affected (0.06 sec)
Records: 200 Duplicates: 0 Warnings: 0
(root@localhost) [test_tablespace1]>select count(*) FROM tbtest_part WHERE part=4;
+----------+
| count(*) |
+----------+
| 1961664 |
+----------+
1 row IN SET (0.94 sec)

 

 

Done the whole dataset is rebuild.

 

Conclusion

It is possible to have per PARTITION tablespace backup using DISCARD/IMPORT and EXCHANGE.

It is also possible to recover any delta that the application will insert in the meanwhile.

It is obvious that during th process the dataset will be inconsistent, so the best thing to do is to avoid inserts.

Copy the specific tablespace will be much easier and faster, then reloading from a full backup, if the issue is limited to a single tablespace or partition.

What I have done here manually must be automize using a script, that will simplify the process and make it much faster.

 

I hope you enjoy the whole as I did during the tests.

 

 

 

{joscommentenable}