WEBVTT 00:00.000 --> 00:27.520 This is the last set shot we're here we have we were waiting for you 00:27.520 --> 00:37.600 So let's start. So this is the last session of the 00:37.600 --> 00:46.640 MySQL Devroom here so mySQL in ODB data recovery we never used in ODB have some people 00:46.640 --> 00:58.000 Okay, way I'm so this is me doing in Jalan Jones today I am LeFred you can find 00:58.000 --> 01:04.560 me on many social media our work for a long time this is what normal I still like the old 01:04.560 --> 01:13.840 explain format you know the new one it's very hard for me so I am here for I'm a local 01:14.800 --> 01:20.320 So this is why I also the last one to speak because you don't care you know no plane nothing 01:20.320 --> 01:30.960 I will go back home by my car so let's start so first why why would you do that so please raise all your 01:30.960 --> 01:42.400 ends everybody come on it's okay thank you so if you if you do back apps you can remove your hands 01:44.720 --> 01:52.880 already you are liars guy our work for a consulting company with markers and we have to do 01:52.880 --> 02:01.360 we saw a lot of people without backups so if you don't have backups if you don't keep 02:01.360 --> 02:07.360 it look come on be close it's dematerial always say she's loving down everything so I 02:07.360 --> 02:14.480 know need of it right replica come on Miguel spending so many times to do replicas set and 02:14.480 --> 02:24.960 everything no need for replica disaster recovery plan what a joke it's fun but it's really 02:26.720 --> 02:36.720 I think I don't remember the year it was 2014 or 13 I wasn't called during Christmas 02:36.720 --> 02:43.360 and and also the weekend of the New Year's Eve and I had to do disaster recovery 02:43.360 --> 02:49.520 both we can for the same company on the same data so people doesn't care 02:50.640 --> 02:58.720 why should I care but also why we should we do this recovery of you know the 02:58.720 --> 03:05.760 bit just because we can do it it's fun to do stuff like that so we need to you know we need to 03:05.760 --> 03:14.080 to leave the the wildlife so to do this to do so we already did that we cover that 03:14.080 --> 03:19.840 base you know the data so when you format your disk when your disk is scrap broken 03:20.640 --> 03:28.800 some people okay good if not yeah welcome you will see how to do it and it's good to do it 03:28.800 --> 03:34.880 now because if you had to do it like several years ago it was really a pain so there were some 03:34.960 --> 03:41.760 tools in it's plus plus that you had to we compile for every table for example when you 03:41.760 --> 03:50.000 had to do or I lost my two thousand tables could you fix them yes okay we can do 03:50.000 --> 03:54.400 no things so let's do that so it was very very hard so there was this percorate 03:54.400 --> 04:02.800 recovery tool for in ODB that was it's not updated since 2011 so probably this is what you 04:02.880 --> 04:11.680 used a lot then there is there was also undrop for the D.B. from T.D.B. so from Alex 04:11.680 --> 04:18.160 Puzminski last committee 2018 and there was also a fork of it from Marco Tusa 04:19.200 --> 04:27.360 but last committee 2018 so this is quite a very old tools boring tools but they were working 04:27.360 --> 04:35.520 but it was very very inefficient yes and also they don't support the new version of 04:35.520 --> 04:45.600 in ODB so but it could save I would not say live but it could save some data so it helped 04:45.600 --> 04:53.440 and I use them in predictions several times but now there are some new tools and there are some 04:53.440 --> 05:02.720 tools made by the NVG engineers which is called in ODB RS who already use this nobody good 05:03.680 --> 05:08.400 you are the right place to learn something that's good so the first committee was in July 05:10.000 --> 05:16.320 and of July 2020 for so it's very recent and the last committee was in August I'm checking 05:16.320 --> 05:23.280 often if they do a new commit but there is no change in the format yet so they don't need to do that 05:23.280 --> 05:30.000 so this is the tool we're going to use today and I will show you a journey on how to recover 05:30.000 --> 05:41.760 the data but I also use other tools in my Swiss army knife for in ODB so it's I.B.D. to S.D.I. that you 05:41.760 --> 05:47.600 should know because it's provided with my school as S.D.I. to D.L. from Marchello I don't know if it's 05:47.680 --> 05:57.440 Marchello here but it was with us these days in ODB sort my school shell I.B.D. Ninja 05:57.440 --> 06:03.200 this is optional I just wanted to play with it to see what it was and of course the Linux box 06:03.200 --> 06:13.440 if you don't use Linux I'm sorry for you so what we need to do that the first thing let's say 06:13.440 --> 06:20.160 if you have if you are a good debate you like you all you all are I'm sure so the first thing 06:20.160 --> 06:28.160 you need to have a backup if you don't that's an issue you need to have a logical backup 06:28.560 --> 06:34.240 everybody has physical backups and in case of as a logical backups and when I say you have a backup 06:34.240 --> 06:40.640 it means you have the backup and you test it because I also see that in the production oh yeah of course 06:40.720 --> 06:49.760 you have a backup a plenty of backups there empty wood it's true so you need to have that if not 06:49.760 --> 06:59.200 man that you're not that that's good to be a you have a D.D.L. D.L. D.L. D. What is this yes you need to have 06:59.200 --> 07:07.520 all your schema your recent schema to know what they're look like if you don't have that you could have 07:07.680 --> 07:16.320 troubles you have the inner D.D.D.S.D.I. files for your tables everybody does that 07:16.320 --> 07:24.320 mark us you do that every day right every morning I know yeah sure so but it's if you want to be 07:24.320 --> 07:29.600 the part of the good very good D.D.S this is something I really encourage you to do it's always good to 07:29.600 --> 07:38.720 see that this can help you later know you space ideas everybody know the space ideas of the tables 07:38.720 --> 07:46.480 of course right so yeah but if you cross if you are on the red line that I don't have this 07:46.480 --> 07:55.040 at all then we will see what we can do nobody knows I would consider me that I was a good 07:55.040 --> 08:04.800 D.D.A when I was working with production never did that so but all this element will make it the 08:04.800 --> 08:11.760 recovery easy so if you don't have all these elements the recovery would be could be painful 08:11.760 --> 08:21.440 unless you follow this session which is good so first if you want to be to perform a successful 08:21.760 --> 08:28.320 recovery so we need to know if the data that we will try to recover or that we will discover 08:28.320 --> 08:36.400 will match the data and the schema that we were looking for right and so this is very very important 08:36.400 --> 08:41.840 and this is why you need to have everything I discussed before and I will show you how we can 08:41.840 --> 08:49.920 discover it and then we will just for the DXSI see that it match because we will check it with what we 08:49.920 --> 08:56.400 had right so for example if you want to just save all your D.D.A.L. you can use the mySQL 08:56.400 --> 09:03.280 shell dump instance and just keep the D.D.A.L. no data I just want the D.D.A. information and 09:04.480 --> 09:14.560 so D.A.L. only and I have somewhere all my D.A.S. this is you can take that every week or 09:14.560 --> 09:20.640 when you know you are changing your table but this is easy and at least you keep that safe 09:21.920 --> 09:28.240 very easy so what we have in it this is the information we have and we have the D.D.A.L that we 09:28.240 --> 09:38.080 are looking for for example right so this is quite easy so the S.D.A. because you know we don't have 09:38.080 --> 09:44.000 a farer anymore we are using data dictionary since mySQL A but it's also possible to have some 09:44.000 --> 09:54.240 information about this the data layout and from the table space and so we use this S.D.A.I to 09:54.240 --> 10:01.920 get that information so because now it's all in the same table space so to extract that information 10:01.920 --> 10:10.400 we use I.B.D. to S.D.I which is when you install mySQL you have that it's part of the mySQL 10:10.400 --> 10:17.840 server and you have that information and it provides you all that information. I told that nobody 10:18.880 --> 10:24.400 saved the table space the space A.D.S. maybe you're going to start doing this in case of it's 10:24.400 --> 10:31.760 very easy you have them in information schema and this is all the space of the tables so at least 10:31.760 --> 10:40.080 this is the way you can get them ready so let's go let's try to recover data in a D.D.B.D. 10:40.080 --> 10:47.680 so this is the system I am using mySQL 9.1 the data here it's a dedicated disk right that 10:47.680 --> 10:54.080 try to to make oh let's put all my data in one disk and we're going to use the employee test 10:54.080 --> 11:03.680 database that I hope everybody knows then sorry this is a fake person and don't then an innocent 11:03.680 --> 11:15.600 D.B.A will just remove the file employee I.B.D. from the five system why not why not and if you know 11:15.600 --> 11:24.080 the person of course you would do that so no worries right so let's remove the file then what 11:24.080 --> 11:33.360 happens if you query the file what will happen if you query the data in in in mySQL it crash 11:33.440 --> 11:41.280 no so you do if you do here we do a select first and we see the information so if the data is 11:41.280 --> 11:48.960 in memory it will work until you don't do something you don't go to check data that you don't 11:50.160 --> 11:55.600 pages that you need to go or if you write data then it could have an issue so you may not see 11:55.600 --> 12:03.120 the issue directly right this is why it is complicated so here just for info let's have a look 12:03.120 --> 12:10.080 at the 10 first record before we delete it just to see when we're going to recover that will 12:10.080 --> 12:22.880 recover that because we could recover plenty of other stuff yeah but after a while yeah this is the 12:22.880 --> 12:29.920 last 10 records it's just for after can be recover everything that I wanted to show you after a while 12:30.000 --> 12:38.160 of you restart the machine or if you what could happen this table space is missing and thank you 12:38.160 --> 12:47.760 the innocent db so now what we need to do to fix this the best is to okay I have a backup yeah we 12:47.760 --> 12:54.720 don't okay I have a logical backup now we don't okay we are screwed yes we are so what we do 12:54.800 --> 13:02.000 we will do this try to recover in a db so the first thing to do when this happen stop the machine 13:02.000 --> 13:09.920 stop the world don't try to make it even worse right and so we stop my skill and amount to the 13:09.920 --> 13:17.680 disk because here it was an innocent dba that we moved the data so the disk is not damaged but it 13:17.760 --> 13:22.960 could happen that the disk is damaged and then you have issue so it's better to 13:22.960 --> 13:30.240 or unmon the disk and save the disk usually you can also if you're not able to do that or like 13:30.240 --> 13:36.240 this one is easy you should be able to do but like it was in the in the past you would save the 13:36.240 --> 13:44.640 day the disk and send it FTP somebody fix we recover data for you so what we do it's we did it 13:44.640 --> 13:56.240 disk and we will save the disk in a in a in a file right here for me to to work using it it's safer 13:56.240 --> 14:02.400 but it's not mandatory but you could do that so this is what I do here we just work on the data 14:02.400 --> 14:12.960 disk loop because maybe I want to work on my local lab or something like that then we will need 14:12.960 --> 14:22.960 to extract all the db pages so it's almost like it was with the all the tools in c++ so from the 14:22.960 --> 14:29.360 disk image and we're going to use the db arrest so it's written in rust to do this so what we do we 14:29.360 --> 14:37.680 run this page extractor by table space recovery and we take the data disk to the output is recovery 14:38.480 --> 14:46.800 output directory and I put the loop and you see the disk was five gigabytes and it's very fast to do 14:48.800 --> 14:54.720 so this is the first thing then let's have a look what we have in this recovery by table space 14:54.720 --> 15:00.880 this is what we have we have some pages plenty of five that with pages different 15:01.680 --> 15:10.880 size right so we need to provide of course the SQL creates statement to the tool as an SQL 15:10.880 --> 15:17.680 file to be able to extract the data so it needs to know how the data is designed of course we have 15:17.680 --> 15:24.880 it because we do the dump all the time because we have already I all the time no we are lying we don't 15:24.880 --> 15:34.560 have that so if we have it it's easy if we have the sdi we can regenerate the SQL again and it's also 15:34.560 --> 15:42.000 easy if we have if we don't have that information we need to generate that information so if we have 15:42.000 --> 15:52.080 no backup if we have nothing then we need to recreate so we need to to find which page is the 15:52.080 --> 15:58.720 information that we want to recover because remember what I did with I said earlier we don't have 15:58.720 --> 16:09.280 FFR files anymore now the information so the sdi it's part of the table space so when we recover 16:09.280 --> 16:19.200 all the pages somewhere dsdi it's there so we could find it so we use ibd2sdi on all the files 16:19.200 --> 16:25.760 that the that have pages to see maybe we will find something that looks like what we are looking for 16:25.760 --> 16:32.560 if it's not an hdi if there is no information it will just fail if it works it will give us 16:32.560 --> 16:39.200 the number of the page we want and we will find it so here we see okay there is no sdi for this 16:39.200 --> 16:47.680 information oh here there is one sdi and so on this year is year one one page we will be able to 16:47.680 --> 16:54.080 find the information we want and again for all the pages I'm going to see oh look this one 16:54.080 --> 17:00.480 003 it's for the department department manager so what we were looking for is the employee 17:00.480 --> 17:11.600 I don't put it here but so we'll parse them and we will find it so no so you see that on some 17:11.600 --> 17:21.440 so we have pages but we don't have a sdi right and we so we we go from 0 to the end and we had 17:21.440 --> 17:27.440 some pages but not the employee right we were looking for the employee so we say oh what's going on 17:27.440 --> 17:34.480 here what can we do let's see no no employee no employee and that's it so so far we know that this 17:35.360 --> 17:42.480 this page is point to this data right but this is not what we are looking for do you remember 17:42.480 --> 17:49.280 I know we don't have this now but we know because we we are doing an exercise that the employee 17:49.280 --> 17:56.160 robot was the space number two but if we check the space number two of page two what we had is this 17:57.120 --> 18:19.760 so it was not okay why any ID no no idea why don't you so because the page we it's not sorted 18:20.320 --> 18:27.120 we had the data but it's we dumbed the data from the disk and it was maybe in any sense or maybe 18:27.120 --> 18:34.960 not completely fresh or whatever but it it is not the page when we get the pages we get all the 18:34.960 --> 18:41.920 pages that belongs to to that file but maybe not sorted so we need to sort them because when you do 18:41.920 --> 18:51.200 as the I believe to SDI we go to a certain offset to check the SDI so if there is no SDI information 18:51.200 --> 19:00.320 in that offset it fails so we do sort it so we can see that it's a bit bigger but not much here 19:00.320 --> 19:08.720 don't know really why and then when we run I believe to SDI on that page we get the information so we 19:08.720 --> 19:14.720 need to if we don't find the information we need to sort it we could load the data if we knew 19:14.720 --> 19:22.240 directly this was the file and if we knew that DSK of the employee we could skip this part because 19:22.240 --> 19:30.720 we can we can reparse the unsorted but to get out the SDI we need to sort the page right so woohoo 19:31.680 --> 19:38.720 out of a victory we already find this the I so we have the we can now check oh we can get that 19:41.360 --> 19:50.880 yeah there is this new tool but it's not not not mandatory but yeah and it was not also 19:50.880 --> 19:58.480 it's not support nine one zero yet but yeah I just made a small very easy patch to do that 19:59.760 --> 20:08.000 and we can see that now it works it gives you information of all in a table space 20:08.800 --> 20:14.720 with this file so it was just to see that the file we created it's correct and it has the SDI 20:14.800 --> 20:21.680 many pages and whatever so now we can generate the SQL file so you do I believe to SDI 20:22.720 --> 20:32.720 and we do SDI to the LL from Marchello and it creates this SQL that we can 20:32.720 --> 20:41.760 load we can use to recover the data so now we use page explorer from InoDBRS we use the SQL we 20:41.760 --> 20:50.000 created limit one to see if it works if it's correct I will not let's say I have a page of a table 20:50.000 --> 20:58.800 of 100 gigabytes and to see if it's correct I will not parse everything so I will parse only 20:59.680 --> 21:06.480 one recording out of it and you see here okay looks like so this is what we have and 21:07.440 --> 21:17.600 it's correct so we can use it so we can run that on the sorted or unsorted page doesn't 21:17.600 --> 21:27.200 matter to when we do the explorer right if it works if not we will have an issue that is 21:27.200 --> 21:32.880 oh the SQL doesn't match the data we are looking for it will fail so this is not the right 21:32.880 --> 21:43.360 SQL file you give it to me we can also open the file just for fun and see if it is really 21:43.360 --> 21:49.840 what we were looking for and we remember these names were in the select when we did the select 21:49.840 --> 21:59.120 earlier so this is okay and yeah we can see if it's really the data we were looking for so now 21:59.120 --> 22:08.400 that we did let's run without limit to recover all the data so this is what we do and now we have one 22:08.400 --> 22:15.920 file here a gason file so it exports everything as a gason and we have for 31 looks 22:15.920 --> 22:23.760 megabyte gason file that we will recreate as a CSV so this is what I do usually so I 22:23.760 --> 22:33.120 check what it is in it and I run a CSV that I can load into yeah we see this is the number 22:33.120 --> 22:42.160 of employees we remember the count we did earlier we can check the first two and we can 22:42.160 --> 22:49.120 remember oh they are not the one we were looking for the first two it's just because they are not 22:50.080 --> 22:58.160 we remember we we we this data it was not sorted because we use the unsorted one to 22:59.200 --> 23:05.680 to export the data but if we check out for these names we can see that they are 23:08.480 --> 23:14.480 is this one yeah this one we can see it is the one we are looking for 23:14.560 --> 23:22.880 so now we can restart my scale the machine or whatever but in my case I will use this one 23:23.680 --> 23:29.280 the one we stopped I will put the foreign keys because our phone keys in the employee database drop 23:29.280 --> 23:33.360 the table even if the table space is done we need to drop it because you know to be 23:34.240 --> 23:40.400 expect in the data dictionary it should be still there so it will say oh I cannot open the file 23:40.480 --> 23:47.200 but at least it will be able to recreate so we recreate the table using the 23:47.200 --> 23:55.120 SQL with it and then we load so I do a importable employee CSV says the unix 23:55.920 --> 24:04.240 we add all the records now everything is back in the database very quickly very easy 24:04.640 --> 24:20.960 so the what would pass if you go just one's life's back another one 24:25.760 --> 24:33.600 yeah here yeah but we need to do it to do it 24:34.560 --> 24:38.960 that's a good point good catch you deserve a dolphin 24:48.240 --> 24:56.720 so so the resources everything is on GitHub everything is open source you can go there 24:56.720 --> 25:02.720 so thank you very much it was probably to make it fast