MySQL 8.0: Logical Backups, Snapshots and PITR like a rockstar
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Descamps, Frédéric
Logical dumps are becoming popular again. MySQL Shell parallel dump & load utility changed to way to deal with logical dumps, certainly when using instances in the cloud. MySQL 8.0 released also an awesome physical snapshot feature with CLONE. In this session, I will show how to use these two ways of saving your data and how to use the generated backup to perform point-in-time recovery like a rockstar with MySQL 8.0 in 2022!
 Hello, welcome to my talk on FOSDEM22, MySQL Dev Room. So today we're going to see how we can do logical back-ups, snapshot, and point-in-time recovery with MySQL 8 like a rockstar. So let's start by some presentation. So my name is Frédéric Descamps. I am MySQL evangelist in Oracle. You can follow me on Twitter at LeFret. I'm hacking MySQL since a lot of times. So my first version was 3.21. I'm living in Belgium here and have a website where you can find a lot of information related to MySQL. So in 2022, there are some best practices when you are using MySQL and I'm going to cover them right now. So first is that you need to run MySQL 8.27 or later. MySQL 8.28 just came out this week. Use only inner DB. Has binary lock enabled. It is required for doing point-in-time recovery. And the binary locks must, of course, use the raw format. Don't use statement-based or mixed-based replication anymore. And of course, enable GTID. So what is point-in-time recovery? It's, of course, using the binary locks. So point-in-time recovery, it's a technique whereby an administrator can restore or recover a set of data at a certain point, usually in the past point-in-time. So in MySQL, point-in-time recovery consists in restoring a dump of the data and then replay the binary lock from a specific point or to the end and bypassing some information. So this is neat. It's used to fixing a problem. So this is where we bypass some transaction or when we do a live migration and I will talk about that in a moment. So all this point-in-time recovery works in MySQL. So this is what we have. You can see here we have time. So we have four days. And we make dump every day. So a backup here, in this case, every day. And you can see on the bottom, the binary locks. So how does it work? If we want to recover at this point, so the point here marked between day three and day four, what do we need to do? So the first thing we need to do is we need to restore the dump of day three. So the last backup before the point we want to recover. So this is what we do. So we restore that dump on a database system. And then we replay the binary lock 0, 0, 8, which is the binary lock since day three up to the point we want to recover. But usually after a backup is made and verified, this is very important to verify your backup, binary locks files are purged from the MySQL server. You cannot keep binary locks forever on your database production, for example. So what happens? This is what we have. Every day when we do a backup and or a dump, we can see that when we verify the backup, the binary locks are purged. So meaning when we have this situation right now, what can we do? The recoverable point in time are day one, day two, day three, day four, just the time when the dump was made or the backup was made. And any time after the last one and while we have the binary locks. So this is a very important concept to understand. So as you can see, we can only recover to the exact time of the backup dump and do point in time recovery only from the last one where we have the binary locks. And this is why we recommend to also stream the binary locks somewhere else, another server, a NAS, the cloud, wherever you want, but to also save these binary locks. This will allow to make time recovery at any point back in time. Of course, this depends of what are your requirements. You can maybe save them for three months, a year that I have no idea. It all depends what you give as a DBA to the users. So here you can see we do the dumps. We save them somewhere. And at the same time, we stream all the binary locks on somewhere else, not on the MySQL server, and we keep them. This gives us the possibility to do this, to recover any time from the first backup to the end of the binary locks. The binary locks we have. So point in time recovery for fixing something. What should we perform point in time recovery? So if a user made a mistake, we need to find back data to a certain point in time. So if somebody says, oh, one week ago at noon, I saw some information, but that information is not there anymore. What was it? How was it at that time? So we can go back in time to find that information. And we need to have an overview of the database at a certain time. Same stuff. This is for fixing an issue. Now, when we need point in time for live migration, it's when we do large migration to the cloud, for example, the load time of the data can take longer than the binary lock retention we have on the system. So the MySQL server production you have on premise, or in another cloud that you want to migrate from, that MySQL server should be used as the replication source. But if the binary locks are not there since the dump, it will fail. So you need to do point in time recovery up to the time of the binary locks you have on the production system. So here on day one, we have the dump, usually a logical dump when we go to the cloud, and we start the load to the cloud instance. In Y, we stream the binary locks to a safe place. Then you can see here at day four, so it took four days to load all the data to the cloud, we can see that when it's over, the dataset was in GTID 003. But on the source on premise, we can see that we don't have that information anymore. Because now the binary lock here, we can see the binary lock is 0010, 003 was purged. So here we are 0010. So what do we need to do before being able to start replication? Because if we start replication here, it will fail. If we say, oh, the next transaction, next GTID, it has been purged from the source and it won't work. So what we need to do is we need to replay all the binary locks from 003 up to the point we are now. In this case, it's a 010, but of course, it can be even more, right? Because the production is still running on the on premise server. When we recover everything, we can start replication and it will work. And then we are ready to do a live migration, right? So now let's talk a bit about backups. So backups, physical, logical, snapshot, all that terms are what usually you encounter in the documentation or when people talk in Slack or wherever in the forums, everybody says backups, but what does that mean really, right? So for a year, physical backups were recommended. And I recommended that a lot of time for a lot of systems. But with the increase of the use of cloud for my SQL, logical backups are coming back to the forefront because physical backups are not always possible in the cloud, right? So first with MySQL Dump. So but as you may know, this tool, it's not optimal in the MySQL Dump. It's all tool, which is single-traded for dump and also single-traded to load the data. So we should avoid to use MySQL Dump. And this is why we in MySQL came up with the MySQL Shell Dump and Load Utility. So this is what you need if you want to migrate to the cloud. This is the tool you need to use. If you don't care about the cloud, but you need to do a logical dump, this is also what you need. So MySQL Shell and Load Utility was introduced with MySQL 8021. It supports export of all or selected schema. So just specify schema or all of them, the full instance. It supports local storage. That local storage can be a mount to S3, for example, and OCI object storage natively. So no need to mount it on your system. You can directly store the data to OCI object storage. It supports dump from 5.6 since 8.0.26, 5.7, and 8.0. It can fix your schema. So it can force inodb. So you can dump some data that was, for example, not inodb and said it has to be inodb. For example, you don't have primary keys. You can say, oh, add an invisible primary key. So this is very, very good when you load the data. And for example, primary key, it's very recommended inodb and must have when you do use HA and replication. You can dump and load all in parallel. And it's not here, but you can also start the dump and start the load at the same time and much more information. So I recommend you to go and check on the manual about dump and load utility. So to illustrate what we're going to do now with all what I'm saying, we're going to use for this presentation the following system. So I'm using an ampere computer instance. So it's always free instance in OCI. And so it's ARM machine with us for OCPO and 24 gigabyte of RAM. I will use my SQL server 8027 and my SQL shell 8027. Because I made this just before ATO came out, so but now you have 8028 out. So please use 8028. I'm also using sysbench1020 to generate some load and data and a specified table to play with that I will call for them T1. So this is sysbench. It's running as you can see. So I said well, Tp read and write and after I will also use all Tp insert only to generate even more binary locks. And it starts in the background. I use that screen in my shell. So meaning that I can detach it and let it run forever. And my Fosdm T1 table is very easy. It has an ID. It has a virtual for name. It was the timestamp when it has been a center and the timestamp when it has been updated and the primary key. It's the ID. So nothing very complicated here. You can see we have five walls. I'm using this just because it's much easier to find information in such an easy table than in the data generated by sysbench, which is complicated to find information in it. So we do the logical adept. As we plan, we use our logical dump as a backup or at least for initial dump. And we won't focus on the specific schema with the full instance we have. So we're going to use util dump instance. So this is how we do it. So we run my shell where we connect. As you can see, we are on the local machine. We use util dump instance and a name and a folder where it will be output. And I use the data generated from bash. Very easy to do that. And this is how it looks like. So it starts by default. It use for threads in parallel. As I'm using a machine with four O CPU, I don't need more. So I let it do the default, but you can configure that if you have a machine with much more CPU power. And if you plan to migrate large machine to the cloud, I always advise to use computing stance with a lot of power to do that, to make it very fast. So this is, you can see how it works. It compresses the data automatically. It generates everything it needs. And we have also some information that is very important, which is the data. And the metadata of the dump, it's in a file called at.json in the directory where we emit the dump, right, the target directory. So for example, if I check this file, as you can see, it was backup. I did that the 11th of January at.json. And you can see here we have the GTID executed with, which was the GTID of the data set we have. And it also tells us that the dump is consistent, right? So this is what we need to be able to restore a consistent dump. Then we can see that our dump, yeah, like I said, it's consistent. This is the GTID we needed. And on the MySQL server, we can see that CIS bench, of course, in the background is still running and keeps generating data because the GTID executed, it's higher than what we had here. As you can see here, it's higher than this one. So now, snapshot, physical hot dumps. So there are multiple ways of doing snapshots. We can do hot backups, so using map extra backup. And these have plenty of features. And it can do full backups or partial backups, incremental backups. And it can be very complicated to operate. And of course, it's also not supported in every cloud platform where you don't have access to physically to the server. File system snapshot, they are not always hot because it depends also of the technique you are using and the file system you are using. So maybe sometime you need to freeze everything to be able to do that snapshot and then continue. Or we can use something called MySQL clone. This is where I want to put my focus and the highlight, it's clone. So clone was introduced in MySQL 8017. It permits cloning of the data locally or from remote MySQL server instance. And the clone data, it's a physical snapshot of the data stored in InnoDB. That includes schema, table, table space, and all the data-additionary metadata we need. Of course, clone, it's only compatible when you are using InnoDB. So you should not have other type of storage engine. And of course, this creates a fully functional data directory. This is the output of it. And this is what we're going to use, the same technique to provision a server when we do, for example, InnoDB cluster or replica set. So how does it work? So in SQL, because this is a SQL command, we just do clone, because I want to clone it locally because I have only one machine now. I do clone local data directory, I give, and where I want to put that snapshot. And that's it. As simple as that. So clone, it's very fast. It's in parallel. It works very well. I know a lot of company who has moved to this technique. And this is a very recommended technique if you need a full consistent snapshot. Because we didn't stop a MySQL, we have still access to the server, we can see that in the performance schema, there is a table, clone status that provides us the GTID executed when we did this using clone. Now let's have a look at binary locks. So all the data change are stored in the binary locks. So the MySQL workload for writes are written in the binary locks. And we can have information. We can show binary locks and we can see all the binary locks we have. And as you can see, if you have access to the file system, which you may not have in the cloud, we can see that they are on the file system in the data here, for example, here, and we see all the binaries. By default, the size of a binary lock is one gigabyte. For this example, I will reduce to create more binary locks. So I will reduce the size by 10 using setPresistMaxBinLockSize. So keeping the bin lock safe, as you may know or not, the MySQL bin lock has the possibility of reading the binary locks from a remote server and store them on the disk using the option, row, read from remote server. So we're going to create a script to use the MySQL DOM to do that, to connect to our server and stream the data locally. And we're going to use systemD to start and stop our script to make it much easier. So if you are interested by that script, these are the source on GitHub. You can have them and play with them. Of course, I don't provide you support for the script, but if you find some issues or whatever we did, I'm always happy to hear from you. So now we need to create a dedicated user for a streaming process. I always recommend that every application has their own user to not use root every time and so on. So we're going to create a user. I will call it bin lock streamer, I will identify it by its own password. I will require SSL to encrypt the communication. I will grant replication slave for that user on every table. And also I will allow the user to read performance schema file instance. This is for a script I used to find what is the last bin lock we need to restore to start streaming from in case of the Rust network issue or the process has been restarted. So and to avoid to store the credential in the script itself, we're going to use the MySQL config editor. MySQL config editor works very well, but you've seen that this is a tool that is not that popular. So I try to make it more popular by always propose to use it. So this is how it works. So we're going to use MySQL config editor set. We're going to create a logging pass. I call it local host name. So this is my local machine, the user bin lock streamer and the password. And we enter the password we just created. So we don't have in clear text the password somewhere. Now because I created that system, the system, I will copy it on the system. I reload the demon and then I can start it. Right. Bin lock streamer at local host because I created the script the way you can have multiple stream on from the same machine. So the same machine, the computing stand could stream and save data from multiple other servers. And then we can see the system that and where it streams the data. Right. Nothing complicated so far. But at least we are streaming the data live and we can see it. If we go where we save the data, we can see here that we are streaming live all the data coming from our prediction server. So this is very, very important. And now let's make point in time recovery like a work star almost with some example. First normally. So let's have a look here. So I am on the system and I will do, I will change the name of one of the user in my table and I do update you want set name equal demo. Oops, I forgot the work close. Right. So after the application was still, I didn't notice that directly me or the user doing it. And then I keep inserting data. And in this case, I will insert another record, which is referred. So if I check what's going on here, what we can see is that we have lost all the names, the previous name of the users. We don't know what they were and we don't know what happened else in the system. Right. So and all the C's bench information also was still running. So it means I cannot restore the backup. I lose all the data that C's bench created. And I will also lose the data, for example, in this case, the insert, the threat in my table. Right. So thank you, demo. So what's the action plan to restore to recover that data? So the first thing to do is we need to restore the last dump. Then we need to set the GTID purge to the GTID executed of the dump itself. Then we need to find the GTID of the transaction we want to bypass. So that update without the work close, we need to find it to bypass it. And finally, happen the GTID to the GTID purge. Right. So the GTID will happen to the GTID purge. So we will bypass it when we're going to replay the binary logs. So then there is a one question we need to answer. It's do we use another instance? So we can use another instance and then just bypass that binary log, restore the dump and start a replication, and that's it. Or we use the same machine because we don't have any other machine. So we, and in that case, we need to replay the binary logs manually. And this is what we're going to do. Right. So I choose to perform the point in time recovery on the same machine to show you how we can accelerate the process if this is the only option we have, meaning having only one single machine. So before we start, we need to have some action point that we need to do. And if we plan to do point in time recovery on the same instance, you need to stop the application, of course. So in CIS bench, in our case, we need to stop it. Then we need to check the last GTID executed. This is not necessary, but at least we have an ID up to where we need to go. And also not needed, but for us to show it works, we're going to do a select count on the CIS bench table because I was inserting a lot of data to have an estimation if we were able to restore that much of data or not. Of course, we need to stop the bin log streaming process because it is not needed at this point. So we check that. So we do the select GTID purge. We have the information here. We can see we have a very large number with more than 5.8 million transaction. And then we check on T1 and we have the information about all these tables in T1. So and we stop the streaming because it's not needed. So the first thing we do, we're going to restore the last dump we have. So we have again several options, and I will show you both. First, restore the logical dump we made with the MySQL shell or restore the snapshot made with Chrome. So let's start first with the MySQL shell utility. So to restore a dump with the MySQL shell utility, we need a server running. So we need to remove all non-system schema. So I will drop the schema FOSDOM. I will drop the schema SB test. And then I will load the data. So I will load all my dump. I want to show the metadata. Here you can see here I have show metadata. And I don't write that in the binary logs because it's not needed. And we have that information. You can see that it provides us the GTID executed of the metadata. And it does that. You can see that it does all this loading barrel for the nine tables it has in two schema in seven seconds. And we can already check what the table looks like after the dump. So we can see here we have the table like it was at the dump backup. Now we still need to set back the GTID. So if we check the GTID perch and the GTID executed, because we haven't done anything, the system still has the GTID executed. It has up to when we stop the application. Then we need to reset master to remove all that GTID information. And then we're going to set the GTID perch of the dump with it. So we had in the metadata. So we added here. And now we can see that the information has perched all the binary logs up to that point. And this is where the data set looks like now. Then we're going to restart the restore of the of the binary logs during what we call the point in time. But first, we're going to also see the same restore using clone. So as the plan is to restore the snapshot on the same server, we need first to save two important files in the data, my SQL data directory, auto CNF, because it contains the server UID and we want to keep it the same. And my SQL the auto CNF containing all the configuration change done using set persist. So this one, we want to keep them. And if you have some dedicated keys in the data, you should also save them to not recreate new ones. So we do that. So we copy that information. We put that in the snapshot directory. And then we know stop my SQL and we empty the data there. We copy back the snapshot to my SQL. So we copy back all the files with the snapshot and we put that in the SQL data here. Then we change the ownership of the files to belongs to my SQL and we can start my SQL. And we can see that all the GTID are already in place. This is the GTID set of the restored data. It's automatically using clone. As you can see, clone is very easy also to do that. And so now we have restored the data using them or using clone and the dump utility from the shell I mean. And now what we need to do is to find the GTID to bypass. So we need to find a transaction where we did something wrong. So where we modify all the user as being demo because if we replay all the binary locks, we will replay also that transaction and we'll be at the same point as we went when we stopped my SQL meaning that all the data we wanted has been gone by this wrong transaction. So we're going to use the bin lock command minus V minus base 64 output decoding rows and the bin lock files we're using grep to find where it comes from. And we find that this come in the my SQL compute bin lock 000029. So we can check it here. Grab FOSDEM because it's the only one I type in FOSDEM and we can see we did update demo, no work, close, nothing. This is exactly what we did wrong. So we found it and we also found the GTID we need to bypass. You can see it here. This is the GTID we want. We need to bypass. So now we're going to also purge that GTID. We're going to tell to my SQL, okay, please add this GTID to the GTID purge. And this is very interesting because not everybody knows. I think so when I see some question, I use this plus. So I don't need to put all the GTID purge and this one included. I will just do plus here, set global GTID purge plus and the GTID I want. And this one has been happened to the GTID purge, right, and executed. And then we could replay the binary locks one by one to our MySQL server, but that can lead to a very long operation using MySQL bin lock because it's single-threaded. So unfortunately on a cloud managed instance, this is the only feasible method to replay the binary locks and repeat that for all the binary locks. But we are not using a cloud instance here, so we can do it like a rock star. So we will let believe to MySQL that those binary locks, stream binary locks that we're going to use are in fact relay locks. And so the applier threads can, the workers here will be able to do it in parallel, right? So let's have a look. We find the name of the relay lock on our system and it's called mycomputerelate.bin. Then we're going to copy all that information, all the relay bin we have, and we're going to replace, we're going to copy them on the data here or where the binary locks are usually and we're going to rename them as relay bin, the name we got before. After that, we need also, of course, to change the ownership of these files and create the index, also the index file. This is what we do here with the LS and I put the output in here in the index file, right? And I change the ownership of my to be MySQL for that file too. Then we can verify that we can ingest relay locks in parallel. We can see that we have a logical plot, replica parallel workers are four. This is enough for my system because I have only four CPU, but if you have more, don't hesitate to add that, right? And if you can restart MySQL, it's always good to put log replica updates to zero to not generate again binary locks for this. We start the process of doing the point in time recovery. So we need to change the server ID to something we don't have for the current server right now. So we're going to put 99 in my case. Then we're going to change the bin lock transaction dependency tracking to write set. Meaning that the dependency information is generated from the source right set and any transaction that write different tuples can be parallelized. So we have commit order, right set and right set session. So we put it right set here. And then we're going to change replication source to relay lock file. And I use the relay lock here put zero zero two. It's better to put the relay lock to when we need the snapshot because we need to process them to see if they have some information we need or not. But it's usually fast enough. But in case of you can put here the lock file of the latest dump with it. Then we put real opos for and the source also I put dummy and I don't start replication. I just start the SQL thread. So this will generate ingest all the relay lock very fast in parallel. You can verify the progress of all this in performance schema in replication apply or status by coordinator and replication apply or status by worker. You can see here all the workers what they are doing. When it's done we can verify here. Right. All the GVAD executed. We do the select and we can see that it's exactly the same amount. And if you want to check in our table. So for the empty one we can see that the transaction we did after that one are there. So you can see here the thread is here. But the one we bypassed that was modifying all these as demo there is gone. So this is perfectly what we wanted to do because we were able to bypass the wrong transaction. So of course don't forget to put back the value of the server ID before you start again your application. So and in the cloud if you want to do that the same technique in OCI for example for we're using my SQL database service this time so not a compute instance. So this is what you do right. You will have your my SQL database service. You can stream the binary locks using a compute instance that will save the binary locks to object storage and you can use my SQL shell to do the dump and load but this is not mandatory to do the logical dump and load because as you know the backup and the snapshot are managed already by the my SQL team so you already have consistent snapshots every day. The binary lock are purchased every hour by default in MDS. So this is why I recommend that you need to stream these binary locks on object storage for example from a dedicated compute instance that will be used for that. So if you are interested in doing this with object storage in MDS I already wrote some articles on that so I point them to you here. So this is the end of my presentation. So thank you now if we have a question we can go live and discuss this all together. Thank you very much for following.
 Hello, welcome to my talk on FOSDEM22, MySQL Dev Room.  So today we're going to see how we can do logical back-ups, snapshot, and point-in-time  recovery with MySQL 8 like a rockstar.  So let's start by some presentation.  So my name is Frédéric Descamps.  I am MySQL evangelist in Oracle.  You can follow me on Twitter at LeFret.  I'm hacking MySQL since a lot of times.  So my first version was 3.21.  I'm living in Belgium here and have a website where you can find a lot of information related  to MySQL.  So in 2022, there are some best practices when you are using MySQL and I'm going to cover  them right now.  So first is that you need to run MySQL 8.27 or later.  MySQL 8.28 just came out this week.  Use only inner DB.  Has binary lock enabled.  It is required for doing point-in-time recovery.  And the binary locks must, of course, use the raw format.  Don't use statement-based or mixed-based replication anymore.  And of course, enable GTID.  So what is point-in-time recovery?  It's, of course, using the binary locks.  So point-in-time recovery, it's a technique whereby an administrator can restore or recover  a set of data at a certain point, usually in the past point-in-time.  So in MySQL, point-in-time recovery consists in restoring a dump of the data and then replay  the binary lock from a specific point or to the end and bypassing some information.  So this is neat.  It's used to fixing a problem.  So this is where we bypass some transaction or when we do a live migration and I will  talk about that in a moment.  So all this point-in-time recovery works in MySQL.  So this is what we have.  You can see here we have time.  So we have four days.  And we make dump every day.  So a backup here, in this case, every day.  And you can see on the bottom, the binary locks.  So how does it work?  If we want to recover at this point, so the point here marked between day three and day  four, what do we need to do?  So the first thing we need to do is we need to restore the dump of day three.  So the last backup before the point we want to recover.  So this is what we do.  So we restore that dump on a database system.  And then we replay the binary lock 0, 0, 8, which is the binary lock since day three up  to the point we want to recover.  But usually after a backup is made and verified, this is very important to verify your backup,  binary locks files are purged from the MySQL server.  You cannot keep binary locks forever on your database production, for example.  So what happens?  This is what we have.  Every day when we do a backup and or a dump, we can see that when we verify the backup,  the binary locks are purged.  So meaning when we have this situation right now, what can we do?  The recoverable point in time are day one, day two, day three, day four, just the time  when the dump was made or the backup was made.  And any time after the last one and while we have the binary locks.  So this is a very important concept to understand.  So as you can see, we can only recover to the exact time of the backup dump and do point  in time recovery only from the last one where we have the binary locks.  And this is why we recommend to also stream the binary locks somewhere else, another server,  a NAS, the cloud, wherever you want, but to also save these binary locks.  This will allow to make time recovery at any point back in time.  Of course, this depends of what are your requirements.  You can maybe save them for three months, a year that I have no idea.  It all depends what you give as a DBA to the users.  So here you can see we do the dumps.  We save them somewhere.  And at the same time, we stream all the binary locks on somewhere else, not on the MySQL  server, and we keep them.  This gives us the possibility to do this, to recover any time from the first backup to  the end of the binary locks.  The binary locks we have.  So point in time recovery for fixing something.  What should we perform point in time recovery?  So if a user made a mistake, we need to find back data to a certain point in time.  So if somebody says, oh, one week ago at noon, I saw some information, but that information  is not there anymore.  What was it?  How was it at that time?  So we can go back in time to find that information.  And we need to have an overview of the database at a certain time.  Same stuff.  This is for fixing an issue.  Now, when we need point in time for live migration, it's when we do large migration to the cloud,  for example, the load time of the data can take longer than the binary lock retention  we have on the system.  So the MySQL server production you have on premise, or in another cloud that you want  to migrate from, that MySQL server should be used as the replication source.  But if the binary locks are not there since the dump, it will fail.  So you need to do point in time recovery up to the time of the binary locks you have on  the production system.  So here on day one, we have the dump, usually a logical dump when we go to the cloud, and  we start the load to the cloud instance.  In Y, we stream the binary locks to a safe place.  Then you can see here at day four, so it took four days to load all the data to the cloud,  we can see that when it's over, the dataset was in GTID 003.  But on the source on premise, we can see that we don't have that information anymore.  Because now the binary lock here, we can see the binary lock is 0010, 003 was purged.  So here we are 0010.  So what do we need to do before being able to start replication?  Because if we start replication here, it will fail.  If we say, oh, the next transaction, next GTID, it has been purged from the source and  it won't work.  So what we need to do is we need to replay all the binary locks from 003 up to the point  we are now.  In this case, it's a 010, but of course, it can be even more, right?  Because the production is still running on the on premise server.  When we recover everything, we can start replication and it will work.  And then we are ready to do a live migration, right?  So now let's talk a bit about backups.  So backups, physical, logical, snapshot, all that terms are what usually you encounter  in the documentation or when people talk in Slack or wherever in the forums, everybody  says backups, but what does that mean really, right?  So for a year, physical backups were recommended.  And I recommended that a lot of time for a lot of systems.  But with the increase of the use of cloud for my SQL, logical backups are coming back  to the forefront because physical backups are not always possible in the cloud, right?  So first with MySQL Dump.  So but as you may know, this tool, it's not optimal in the MySQL Dump.  It's all tool, which is single-traded for dump and also single-traded to load the data.  So we should avoid to use MySQL Dump.  And this is why we in MySQL came up with the MySQL Shell Dump and Load Utility.  So this is what you need if you want to migrate to the cloud.  This is the tool you need to use.  If you don't care about the cloud, but you need to do a logical dump, this is also what  you need.  So MySQL Shell and Load Utility was introduced with MySQL 8021.  It supports export of all or selected schema.  So just specify schema or all of them, the full instance.  It supports local storage.  That local storage can be a mount to S3, for example, and OCI object storage natively.  So no need to mount it on your system.  You can directly store the data to OCI object storage.  It supports dump from 5.6 since 8.0.26, 5.7, and 8.0.  It can fix your schema.  So it can force inodb.  So you can dump some data that was, for example, not inodb and said it has to be inodb.  For example, you don't have primary keys.  You can say, oh, add an invisible primary key.  So this is very, very good when you load the data.  And for example, primary key, it's very recommended inodb and must have when you do use HA and  replication.  You can dump and load all in parallel.  And it's not here, but you can also start the dump and start the load at the same time  and much more information.  So I recommend you to go and check on the manual about dump and load utility.  So to illustrate what we're going to do now with all what I'm saying, we're going to use  for this presentation the following system.  So I'm using an ampere computer instance.  So it's always free instance in OCI.  And so it's ARM machine with us for OCPO and 24 gigabyte of RAM.  I will use my SQL server 8027 and my SQL shell 8027.  Because I made this just before ATO came out, so but now you have 8028 out.  So please use 8028.  I'm also using sysbench1020 to generate some load and data and a specified table to play  with that I will call for them T1.  So this is sysbench.  It's running as you can see.  So I said well, Tp read and write and after I will also use all Tp insert only to generate  even more binary locks.  And it starts in the background.  I use that screen in my shell.  So meaning that I can detach it and let it run forever.  And my Fosdm T1 table is very easy.  It has an ID.  It has a virtual for name.  It was the timestamp when it has been a center and the timestamp when it has been updated  and the primary key.  It's the ID.  So nothing very complicated here.  You can see we have five walls.  I'm using this just because it's much easier to find information in such an easy table  than in the data generated by sysbench, which is complicated to find information in it.  So we do the logical adept.  As we plan, we use our logical dump as a backup or at least for initial dump.  And we won't focus on the specific schema with the full instance we have.  So we're going to use util dump instance.  So this is how we do it.  So we run my shell where we connect.  As you can see, we are on the local machine.  We use util dump instance and a name and a folder where it will be output.  And I use the data generated from bash.  Very easy to do that.  And this is how it looks like.  So it starts by default.  It use for threads in parallel.  As I'm using a machine with four O CPU, I don't need more.  So I let it do the default, but you can configure that if you have a machine with much more  CPU power.  And if you plan to migrate large machine to the cloud, I always advise to use computing  stance with a lot of power to do that, to make it very fast.  So this is, you can see how it works.  It compresses the data automatically.  It generates everything it needs.  And we have also some information that is very important, which is the data.  And the metadata of the dump, it's in a file called at.json in the directory where we emit  the dump, right, the target directory.  So for example, if I check this file, as you can see, it was backup.  I did that the 11th of January at.json.  And you can see here we have the GTID executed with, which was the GTID of the data set we  have.  And it also tells us that the dump is consistent, right?  So this is what we need to be able to restore a consistent dump.  Then we can see that our dump, yeah, like I said, it's consistent.  This is the GTID we needed.  And on the MySQL server, we can see that CIS bench, of course, in the background is still  running and keeps generating data because the GTID executed, it's higher than what we  had here.  As you can see here, it's higher than this one.  So now, snapshot, physical hot dumps.  So there are multiple ways of doing snapshots.  We can do hot backups, so using map extra backup.  And these have plenty of features.  And it can do full backups or partial backups, incremental backups.  And it can be very complicated to operate.  And of course, it's also not supported in every cloud platform where you don't have  access to physically to the server.  File system snapshot, they are not always hot because it depends also of the technique  you are using and the file system you are using.  So maybe sometime you need to freeze everything to be able to do that snapshot and then continue.  Or we can use something called MySQL clone.  This is where I want to put my focus and the highlight, it's clone.  So clone was introduced in MySQL 8017.  It permits cloning of the data locally or from remote MySQL server instance.  And the clone data, it's a physical snapshot of the data stored in InnoDB.  That includes schema, table, table space, and all the data-additionary metadata we need.  Of course, clone, it's only compatible when you are using InnoDB.  So you should not have other type of storage engine.  And of course, this creates a fully functional data directory.  This is the output of it.  And this is what we're going to use, the same technique to provision a server when we do,  for example, InnoDB cluster or replica set.  So how does it work?  So in SQL, because this is a SQL command, we just do clone, because I want to clone it  locally because I have only one machine now.  I do clone local data directory, I give, and where I want to put that snapshot.  And that's it.  As simple as that.  So clone, it's very fast.  It's in parallel.  It works very well.  I know a lot of company who has moved to this technique.  And this is a very recommended technique if you need a full consistent snapshot.  Because we didn't stop a MySQL, we have still access to the server, we can see that in the  performance schema, there is a table, clone status that provides us the GTID executed  when we did this using clone.  Now let's have a look at binary locks.  So all the data change are stored in the binary locks.  So the MySQL workload for writes are written in the binary locks.  And we can have information.  We can show binary locks and we can see all the binary locks we have.  And as you can see, if you have access to the file system, which you may not have in  the cloud, we can see that they are on the file system in the data here, for example,  here, and we see all the binaries.  By default, the size of a binary lock is one gigabyte.  For this example, I will reduce to create more binary locks.  So I will reduce the size by 10 using setPresistMaxBinLockSize.  So keeping the bin lock safe, as you may know or not, the MySQL bin lock has the possibility  of reading the binary locks from a remote server and store them on the disk using the  option, row, read from remote server.  So we're going to create a script to use the MySQL DOM to do that, to connect to our  server and stream the data locally.  And we're going to use systemD to start and stop our script to make it much easier.  So if you are interested by that script, these are the source on GitHub.  You can have them and play with them.  Of course, I don't provide you support for the script, but if you find some issues or  whatever we did, I'm always happy to hear from you.  So now we need to create a dedicated user for a streaming process.  I always recommend that every application has their own user to not use root every time  and so on.  So we're going to create a user.  I will call it bin lock streamer, I will identify it by its own password.  I will require SSL to encrypt the communication.  I will grant replication slave for that user on every table.  And also I will allow the user to read performance schema file instance.  This is for a script I used to find what is the last bin lock we need to restore to start  streaming from in case of the Rust network issue or the process has been restarted.  So and to avoid to store the credential in the script itself, we're going to use the  MySQL config editor.  MySQL config editor works very well, but you've seen that this is a tool that is not that  popular.  So I try to make it more popular by always propose to use it.  So this is how it works.  So we're going to use MySQL config editor set.  We're going to create a logging pass.  I call it local host name.  So this is my local machine, the user bin lock streamer and the password.  And we enter the password we just created.  So we don't have in clear text the password somewhere.  Now because I created that system, the system, I will copy it on the system.  I reload the demon and then I can start it.  Right.  Bin lock streamer at local host because I created the script the way you can have multiple stream  on from the same machine.  So the same machine, the computing stand could stream and save data from multiple other servers.  And then we can see the system that and where it streams the data.  Right.  Nothing complicated so far.  But at least we are streaming the data live and we can see it.  If we go where we save the data, we can see here that we are streaming live all the data  coming from our prediction server.  So this is very, very important.  And now let's make point in time recovery like a work star almost with some example.  First normally.  So let's have a look here.  So I am on the system and I will do, I will change the name of one of the user in my table  and I do update you want set name equal demo.  Oops, I forgot the work close.  Right.  So after the application was still, I didn't notice that directly me or the user doing  it.  And then I keep inserting data.  And in this case, I will insert another record, which is referred.  So if I check what's going on here, what we can see is that we have lost all the names,  the previous name of the users.  We don't know what they were and we don't know what happened else in the system.  Right.  So and all the C's bench information also was still running.  So it means I cannot restore the backup.  I lose all the data that C's bench created.  And I will also lose the data, for example, in this case, the insert, the threat in my  table.  Right.  So thank you, demo.  So what's the action plan to restore to recover that data?  So the first thing to do is we need to restore the last dump.  Then we need to set the GTID purge to the GTID executed of the dump itself.  Then we need to find the GTID of the transaction we want to bypass.  So that update without the work close, we need to find it to bypass it.  And finally, happen the GTID to the GTID purge.  Right.  So the GTID will happen to the GTID purge.  So we will bypass it when we're going to replay the binary logs.  So then there is a one question we need to answer.  It's do we use another instance?  So we can use another instance and then just bypass that binary log, restore the dump and  start a replication, and that's it.  Or we use the same machine because we don't have any other machine.  So we, and in that case, we need to replay the binary logs manually.  And this is what we're going to do.  Right.  So I choose to perform the point in time recovery on the same machine to show you how we can  accelerate the process if this is the only option we have, meaning having only one single  machine.  So before we start, we need to have some action point that we need to do.  And if we plan to do point in time recovery on the same instance, you need to stop the  application, of course.  So in CIS bench, in our case, we need to stop it.  Then we need to check the last GTID executed.  This is not necessary, but at least we have an ID up to where we need to go.  And also not needed, but for us to show it works, we're going to do a select count on  the CIS bench table because I was inserting a lot of data to have an estimation if we were  able to restore that much of data or not.  Of course, we need to stop the bin log streaming process because it is not needed at this point.  So we check that.  So we do the select GTID purge.  We have the information here.  We can see we have a very large number with more than 5.8 million transaction.  And then we check on T1 and we have the information about all these tables in T1.  So and we stop the streaming because it's not needed.  So the first thing we do, we're going to restore the last dump we have.  So we have again several options, and I will show you both.  First, restore the logical dump we made with the MySQL shell or restore the snapshot made  with Chrome.  So let's start first with the MySQL shell utility.  So to restore a dump with the MySQL shell utility, we need a server running.  So we need to remove all non-system schema.  So I will drop the schema FOSDOM.  I will drop the schema SB test.  And then I will load the data.  So I will load all my dump.  I want to show the metadata.  Here you can see here I have show metadata.  And I don't write that in the binary logs because it's not needed.  And we have that information.  You can see that it provides us the GTID executed of the metadata.  And it does that.  You can see that it does all this loading barrel for the nine tables it has in two  schema in seven seconds.  And we can already check what the table looks like after the dump.  So we can see here we have the table like it was at the dump backup.  Now we still need to set back the GTID.  So if we check the GTID perch and the GTID executed, because we haven't done anything,  the system still has the GTID executed.  It has up to when we stop the application.  Then we need to reset master to remove all that GTID information.  And then we're going to set the GTID perch of the dump with it.  So we had in the metadata.  So we added here.  And now we can see that the information has perched all the binary logs up to that point.  And this is where the data set looks like now.  Then we're going to restart the restore of the of the binary logs during what we call  the point in time.  But first, we're going to also see the same restore using clone.  So as the plan is to restore the snapshot on the same server, we need first to save  two important files in the data, my SQL data directory, auto CNF, because it contains the  server UID and we want to keep it the same.  And my SQL the auto CNF containing all the configuration change done using set persist.  So this one, we want to keep them.  And if you have some dedicated keys in the data, you should also save them to not recreate  new ones.  So we do that.  So we copy that information.  We put that in the snapshot directory.  And then we know stop my SQL and we empty the data there.  We copy back the snapshot to my SQL.  So we copy back all the files with the snapshot and we put that in the SQL data here.  Then we change the ownership of the files to belongs to my SQL and we can start my SQL.  And we can see that all the GTID are already in place.  This is the GTID set of the restored data.  It's automatically using clone.  As you can see, clone is very easy also to do that.  And so now we have restored the data using them or using clone and the dump utility from  the shell I mean.  And now what we need to do is to find the GTID to bypass.  So we need to find a transaction where we did something wrong.  So where we modify all the user as being demo because if we replay all the binary locks,  we will replay also that transaction and we'll be at the same point as we went when we stopped  my SQL meaning that all the data we wanted has been gone by this wrong transaction.  So we're going to use the bin lock command minus V minus base 64 output decoding rows  and the bin lock files we're using grep to find where it comes from.  And we find that this come in the my SQL compute bin lock 000029.  So we can check it here.  Grab FOSDEM because it's the only one I type in FOSDEM and we can see we did update demo,  no work, close, nothing.  This is exactly what we did wrong.  So we found it and we also found the GTID we need to bypass.  You can see it here.  This is the GTID we want.  We need to bypass.  So now we're going to also purge that GTID.  We're going to tell to my SQL, okay, please add this GTID to the GTID purge.  And this is very interesting because not everybody knows.  I think so when I see some question, I use this plus.  So I don't need to put all the GTID purge and this one included.  I will just do plus here, set global GTID purge plus and the GTID I want.  And this one has been happened to the GTID purge, right, and executed.  And then we could replay the binary locks one by one to our MySQL server, but that can  lead to a very long operation using MySQL bin lock because it's single-threaded.  So unfortunately on a cloud managed instance, this is the only feasible method to replay  the binary locks and repeat that for all the binary locks.  But we are not using a cloud instance here, so we can do it like a rock star.  So we will let believe to MySQL that those binary locks, stream binary locks that we're  going to use are in fact relay locks.  And so the applier threads can, the workers here will be able to do it in parallel, right?  So let's have a look.  We find the name of the relay lock on our system and it's called mycomputerelate.bin.  Then we're going to copy all that information, all the relay bin we have, and we're going  to replace, we're going to copy them on the data here or where the binary locks are usually  and we're going to rename them as relay bin, the name we got before.  After that, we need also, of course, to change the ownership of these files and create the  index, also the index file.  This is what we do here with the LS and I put the output in here in the index file, right?  And I change the ownership of my to be MySQL for that file too.  Then we can verify that we can ingest relay locks in parallel.  We can see that we have a logical plot, replica parallel workers are four.  This is enough for my system because I have only four CPU, but if you have more, don't  hesitate to add that, right?  And if you can restart MySQL, it's always good to put log replica updates to zero to not  generate again binary locks for this.  We start the process of doing the point in time recovery.  So we need to change the server ID to something we don't have for the current server right  now.  So we're going to put 99 in my case.  Then we're going to change the bin lock transaction dependency tracking to write set.  Meaning that the dependency information is generated from the source right set and any  transaction that write different tuples can be parallelized.  So we have commit order, right set and right set session.  So we put it right set here.  And then we're going to change replication source to relay lock file.  And I use the relay lock here put zero zero two.  It's better to put the relay lock to when we need the snapshot because we need to process  them to see if they have some information we need or not.  But it's usually fast enough.  But in case of you can put here the lock file of the latest dump with it.  Then we put real opos for and the source also I put dummy and I don't start replication.  I just start the SQL thread.  So this will generate ingest all the relay lock very fast in parallel.  You can verify the progress of all this in performance schema in replication apply or  status by coordinator and replication apply or status by worker.  You can see here all the workers what they are doing.  When it's done we can verify here.  Right.  All the GVAD executed.  We do the select and we can see that it's exactly the same amount.  And if you want to check in our table.  So for the empty one we can see that the transaction we did after that one are there.  So you can see here the thread is here.  But the one we bypassed that was modifying all these as demo there is gone.  So this is perfectly what we wanted to do because we were able to bypass the wrong transaction.  So of course don't forget to put back the value of the server ID before you start again  your application.  So and in the cloud if you want to do that the same technique in OCI for example for  we're using my SQL database service this time so not a compute instance.  So this is what you do right.  You will have your my SQL database service.  You can stream the binary locks using a compute instance that will save the binary locks to  object storage and you can use my SQL shell to do the dump and load but this is not mandatory  to do the logical dump and load because as you know the backup and the snapshot are managed  already by the my SQL team so you already have consistent snapshots every day.  The binary lock are purchased every hour by default in MDS.  So this is why I recommend that you need to stream these binary locks on object storage  for example from a dedicated compute instance that will be used for that.  So if you are interested in doing this with object storage in MDS I already wrote some  articles on that so I point them to you here.  So this is the end of my presentation.  So thank you now if we have a question we can go live and discuss this all together.  Thank you very much for following.
mysql_pitr_rockstar_0_143.png mysql_pitr_rockstar_144_1055.png mysql_pitr_rockstar_1056_4319.png mysql_pitr_rockstar_4320_6743.png mysql_pitr_rockstar_6744_7535.png mysql_pitr_rockstar_7536_9491.png mysql_pitr_rockstar_9492_15803.png mysql_pitr_rockstar_15804_17051.png mysql_pitr_rockstar_17052_21899.png mysql_pitr_rockstar_21900_22283.png mysql_pitr_rockstar_22284_26051.png mysql_pitr_rockstar_26052_26855.png mysql_pitr_rockstar_26856_27059.png mysql_pitr_rockstar_27060_29039.png mysql_pitr_rockstar_29040_29891.png mysql_pitr_rockstar_29892_31187.png mysql_pitr_rockstar_31188_31715.png mysql_pitr_rockstar_31716_32939.png mysql_pitr_rockstar_32940_33551.png mysql_pitr_rockstar_33552_33815.png mysql_pitr_rockstar_33816_33923.png mysql_pitr_rockstar_33924_34187.png mysql_pitr_rockstar_34188_34727.png mysql_pitr_rockstar_34728_34775.png mysql_pitr_rockstar_34776_34955.png mysql_pitr_rockstar_34956_35027.png mysql_pitr_rockstar_35028_35183.png mysql_pitr_rockstar_35184_35267.png mysql_pitr_rockstar_35268_36299.png mysql_pitr_rockstar_36300_37259.png mysql_pitr_rockstar_37260_38087.png mysql_pitr_rockstar_38088_41999.png mysql_pitr_rockstar_42000_44687.png mysql_pitr_rockstar_44688_45239.png mysql_pitr_rockstar_45240_45755.png mysql_pitr_rockstar_45756_45923.png mysql_pitr_rockstar_45924_46835.png mysql_pitr_rockstar_46836_47435.png mysql_pitr_rockstar_47436_49835.png mysql_pitr_rockstar_49836_49955.png mysql_pitr_rockstar_49956_50795.png mysql_pitr_rockstar_50796_52652.png