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 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:
- - LOCK the table with partition and the final table where I will put the data
- - EXCHANGE the table with the partition
- - Flush the table
- - save the files
- - EXCHANGE AGAIN table with partition
- - reload the record IF ANY from the table I have move back to the partition (delta insert during the first exercise)
- - Flush again
- - 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.
INITIAL data set
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;
Time to save the files in the backup directory
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.
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.
Assume that we have a disaster and data is lost, we will simulate truncating the partition
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):
Now we have to:
- re-attach the old dataset to a new table and exchange with the partitioned table.
- save new data in a mew table
- re-attach the tablespace to partionD and reload the data;
- reload the delta between crash and new inserts
1) Do the different steps to have the original data back
Finally save the DELTA of the data in a different table to do not overwrite it.
4) reload the missed delta after the EMERGENCY
Done the whole dataset is rebuild.
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.