This is the last set shot we're here we have we were waiting for you
So let's start. So this is the last session of the
MySQL Devroom here so mySQL in ODB data recovery we never used in ODB have some people
Okay, way I'm so this is me doing in Jalan Jones today I am LeFred you can find
me on many social media our work for a long time this is what normal I still like the old
explain format you know the new one it's very hard for me so I am here for I'm a local
So this is why I also the last one to speak because you don't care you know no plane nothing
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
ends everybody come on it's okay thank you so if you if you do back apps you can remove your hands
already you are liars guy our work for a consulting company with markers and we have to do
we saw a lot of people without backups so if you don't have backups if you don't keep
it look come on be close it's dematerial always say she's loving down everything so I
know need of it right replica come on Miguel spending so many times to do replicas set and
everything no need for replica disaster recovery plan what a joke it's fun but it's really
I think I don't remember the year it was 2014 or 13 I wasn't called during Christmas
and and also the weekend of the New Year's Eve and I had to do disaster recovery
both we can for the same company on the same data so people doesn't care
why should I care but also why we should we do this recovery of you know the
bit just because we can do it it's fun to do stuff like that so we need to you know we need to
to leave the the wildlife so to do this to do so we already did that we cover that
base you know the data so when you format your disk when your disk is scrap broken
some people okay good if not yeah welcome you will see how to do it and it's good to do it
now because if you had to do it like several years ago it was really a pain so there were some
tools in it's plus plus that you had to we compile for every table for example when you
had to do or I lost my two thousand tables could you fix them yes okay we can do
no things so let's do that so it was very very hard so there was this percorate
recovery tool for in ODB that was it's not updated since 2011 so probably this is what you
used a lot then there is there was also undrop for the D.B. from T.D.B. so from Alex
Puzminski last committee 2018 and there was also a fork of it from Marco Tusa
but last committee 2018 so this is quite a very old tools boring tools but they were working
but it was very very inefficient yes and also they don't support the new version of
in ODB so but it could save I would not say live but it could save some data so it helped
and I use them in predictions several times but now there are some new tools and there are some
tools made by the NVG engineers which is called in ODB RS who already use this nobody good
you are the right place to learn something that's good so the first committee was in July
and of July 2020 for so it's very recent and the last committee was in August I'm checking
often if they do a new commit but there is no change in the format yet so they don't need to do that
so this is the tool we're going to use today and I will show you a journey on how to recover
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
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
Marchello here but it was with us these days in ODB sort my school shell I.B.D. Ninja
this is optional I just wanted to play with it to see what it was and of course the Linux box
if you don't use Linux I'm sorry for you so what we need to do that the first thing let's say
if you have if you are a good debate you like you all you all are I'm sure so the first thing
you need to have a backup if you don't that's an issue you need to have a logical backup
everybody has physical backups and in case of as a logical backups and when I say you have a backup
it means you have the backup and you test it because I also see that in the production oh yeah of course
you have a backup a plenty of backups there empty wood it's true so you need to have that if not
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
all your schema your recent schema to know what they're look like if you don't have that you could have
troubles you have the inner D.D.D.S.D.I. files for your tables everybody does that
mark us you do that every day right every morning I know yeah sure so but it's if you want to be
the part of the good very good D.D.S this is something I really encourage you to do it's always good to
see that this can help you later know you space ideas everybody know the space ideas of the tables
of course right so yeah but if you cross if you are on the red line that I don't have this
at all then we will see what we can do nobody knows I would consider me that I was a good
D.D.A when I was working with production never did that so but all this element will make it the
recovery easy so if you don't have all these elements the recovery would be could be painful
unless you follow this session which is good so first if you want to be to perform a successful
recovery so we need to know if the data that we will try to recover or that we will discover
will match the data and the schema that we were looking for right and so this is very very important
and this is why you need to have everything I discussed before and I will show you how we can
discover it and then we will just for the DXSI see that it match because we will check it with what we
had right so for example if you want to just save all your D.D.A.L. you can use the mySQL
shell dump instance and just keep the D.D.A.L. no data I just want the D.D.A. information and
so D.A.L. only and I have somewhere all my D.A.S. this is you can take that every week or
when you know you are changing your table but this is easy and at least you keep that safe
very easy so what we have in it this is the information we have and we have the D.D.A.L that we
are looking for for example right so this is quite easy so the S.D.A. because you know we don't have
a farer anymore we are using data dictionary since mySQL A but it's also possible to have some
information about this the data layout and from the table space and so we use this S.D.A.I to
get that information so because now it's all in the same table space so to extract that information
we use I.B.D. to S.D.I which is when you install mySQL you have that it's part of the mySQL
server and you have that information and it provides you all that information. I told that nobody
saved the table space the space A.D.S. maybe you're going to start doing this in case of it's
very easy you have them in information schema and this is all the space of the tables so at least
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.
so this is the system I am using mySQL 9.1 the data here it's a dedicated disk right that
try to to make oh let's put all my data in one disk and we're going to use the employee test
database that I hope everybody knows then sorry this is a fake person and don't then an innocent
D.B.A will just remove the file employee I.B.D. from the five system why not why not and if you know
the person of course you would do that so no worries right so let's remove the file then what
happens if you query the file what will happen if you query the data in in in mySQL it crash
no so you do if you do here we do a select first and we see the information so if the data is
in memory it will work until you don't do something you don't go to check data that you don't
pages that you need to go or if you write data then it could have an issue so you may not see
the issue directly right this is why it is complicated so here just for info let's have a look
at the 10 first record before we delete it just to see when we're going to recover that will
recover that because we could recover plenty of other stuff yeah but after a while yeah this is the
last 10 records it's just for after can be recover everything that I wanted to show you after a while
of you restart the machine or if you what could happen this table space is missing and thank you
the innocent db so now what we need to do to fix this the best is to okay I have a backup yeah we
don't okay I have a logical backup now we don't okay we are screwed yes we are so what we do
we will do this try to recover in a db so the first thing to do when this happen stop the machine
stop the world don't try to make it even worse right and so we stop my skill and amount to the
disk because here it was an innocent dba that we moved the data so the disk is not damaged but it
could happen that the disk is damaged and then you have issue so it's better to
or unmon the disk and save the disk usually you can also if you're not able to do that or like
this one is easy you should be able to do but like it was in the in the past you would save the
day the disk and send it FTP somebody fix we recover data for you so what we do it's we did it
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
but it's not mandatory but you could do that so this is what I do here we just work on the data
disk loop because maybe I want to work on my local lab or something like that then we will need
to extract all the db pages so it's almost like it was with the all the tools in c++ so from the
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
run this page extractor by table space recovery and we take the data disk to the output is recovery
output directory and I put the loop and you see the disk was five gigabytes and it's very fast to do
so this is the first thing then let's have a look what we have in this recovery by table space
this is what we have we have some pages plenty of five that with pages different
size right so we need to provide of course the SQL creates statement to the tool as an SQL
file to be able to extract the data so it needs to know how the data is designed of course we have
it because we do the dump all the time because we have already I all the time no we are lying we don't
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
easy if we have if we don't have that information we need to generate that information so if we have
no backup if we have nothing then we need to recreate so we need to to find which page is the
information that we want to recover because remember what I did with I said earlier we don't have
FFR files anymore now the information so the sdi it's part of the table space so when we recover
all the pages somewhere dsdi it's there so we could find it so we use ibd2sdi on all the files
that the that have pages to see maybe we will find something that looks like what we are looking for
if it's not an hdi if there is no information it will just fail if it works it will give us
the number of the page we want and we will find it so here we see okay there is no sdi for this
information oh here there is one sdi and so on this year is year one one page we will be able to
find the information we want and again for all the pages I'm going to see oh look this one
003 it's for the department department manager so what we were looking for is the employee
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
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
some pages but not the employee right we were looking for the employee so we say oh what's going on
here what can we do let's see no no employee no employee and that's it so so far we know that this
this page is point to this data right but this is not what we are looking for do you remember
I know we don't have this now but we know because we we are doing an exercise that the employee
robot was the space number two but if we check the space number two of page two what we had is this
so it was not okay why any ID no no idea why don't you so because the page we it's not sorted
we had the data but it's we dumbed the data from the disk and it was maybe in any sense or maybe
not completely fresh or whatever but it it is not the page when we get the pages we get all the
pages that belongs to to that file but maybe not sorted so we need to sort them because when you do
as the I believe to SDI we go to a certain offset to check the SDI so if there is no SDI information
in that offset it fails so we do sort it so we can see that it's a bit bigger but not much here
don't know really why and then when we run I believe to SDI on that page we get the information so we
need to if we don't find the information we need to sort it we could load the data if we knew
directly this was the file and if we knew that DSK of the employee we could skip this part because
we can we can reparse the unsorted but to get out the SDI we need to sort the page right so woohoo
out of a victory we already find this the I so we have the we can now check oh we can get that
yeah there is this new tool but it's not not not mandatory but yeah and it was not also
it's not support nine one zero yet but yeah I just made a small very easy patch to do that
and we can see that now it works it gives you information of all in a table space
with this file so it was just to see that the file we created it's correct and it has the SDI
many pages and whatever so now we can generate the SQL file so you do I believe to SDI
and we do SDI to the LL from Marchello and it creates this SQL that we can
load we can use to recover the data so now we use page explorer from InoDBRS we use the SQL we
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
of 100 gigabytes and to see if it's correct I will not parse everything so I will parse only
one recording out of it and you see here okay looks like so this is what we have and
it's correct so we can use it so we can run that on the sorted or unsorted page doesn't
matter to when we do the explorer right if it works if not we will have an issue that is
oh the SQL doesn't match the data we are looking for it will fail so this is not the right
SQL file you give it to me we can also open the file just for fun and see if it is really
what we were looking for and we remember these names were in the select when we did the select
earlier so this is okay and yeah we can see if it's really the data we were looking for so now
that we did let's run without limit to recover all the data so this is what we do and now we have one
file here a gason file so it exports everything as a gason and we have for 31 looks
megabyte gason file that we will recreate as a CSV so this is what I do usually so I
check what it is in it and I run a CSV that I can load into yeah we see this is the number
of employees we remember the count we did earlier we can check the first two and we can
remember oh they are not the one we were looking for the first two it's just because they are not
we remember we we we this data it was not sorted because we use the unsorted one to
to export the data but if we check out for these names we can see that they are
is this one yeah this one we can see it is the one we are looking for
so now we can restart my scale the machine or whatever but in my case I will use this one
the one we stopped I will put the foreign keys because our phone keys in the employee database drop
the table even if the table space is done we need to drop it because you know to be
expect in the data dictionary it should be still there so it will say oh I cannot open the file
but at least it will be able to recreate so we recreate the table using the
SQL with it and then we load so I do a importable employee CSV says the unix
we add all the records now everything is back in the database very quickly very easy
so the what would pass if you go just one's life's back another one
yeah here yeah but we need to do it to do it
that's a good point good catch you deserve a dolphin
so so the resources everything is on GitHub everything is open source you can go there
so thank you very much it was probably to make it fast