From single MySQL instance to HA
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
During this session, I will show how we can start from a single instance to MySQL InnoDB Cluster, the automated HA solution for MySQL, passing by the following architecture: - Single MySQL - Source / Asynchronous Replica - InnoDB ReplicaSet - InnoDB Cluster I will cover the limitations of each options and how to migrate from one to the next one with minimal downtime.
 Hello, welcome everybody to my session from single MySQL instance to HA. So this is what I call the journey to MySQL in a DB cluster. So thank you for joining this session at Fusdum. So let's start with who am I, right? So my name is Frédéric Descamps. I am known as Lefret. You can follow me on Twitter if you are looking for MySQL information. I am MySQL evangelist. So I am part of the MySQL community team. I am managing MySQL since a lot of time. My first version I installed was 3.20. I am a DevOps believer living in Belgium and I have a blog where you can find a lot of information related to MySQL called Lefret.be. And as you can see, the address are still closed in Belgium. So I apologize for this haircut right now. So evolution for 2HA, right? So this is what we are doing today. So we start from a single instance to full HA, we are using MySQL. So the start is made by a single MySQL instance. This is you just install MySQL and you are using MySQL. Some tips that are very necessary if you don't want to lose data when there is a poor cut or something like that, it's to exclusively use an asset compliance storage engine. So in our case, it's EnoDB and keep the durability to the default. So meaning that you are writing in the EnoDB logs files all the transactions that you perform. So at least when there is a crash, you do the crash recovery and you don't lose the data that is being committed. So we are running with a one machine. We are very happy. Everything works as expected. But what's next? So next level is that our database becomes more and more important and losing it, losing the database might be an issue. So what can we do? And what do we need to plan? And to do that, we need to put some targets. And these targets are the RTO. For start with, we're going to put hours and the RPU. RPU to one day. What does that mean? So these RTO and RPU are some metrics that we use to design an architecture for our database. And RTO means the recovery time objective. So all on does it take to recover the service? So all on does it take me for the service to be up again and be usable? And the RPU is the recovery point objective, meaning defining how many of data can I lose? The maximum of data that I can lose. So the RTO, to start, we do the RTO hours. So we have some hours to recover and set up again the system. And RPU, it's one day. We can lose one day of data at max. So when we have these targets or these goals, what are solutions? The solution, it's very common solution known by everybody, but not deployed by everybody. So warning here, please, if you don't do it and do it now and certainly before an upgrade, this solution, it's backups. You need to have backups, physical backups, there are commercial version, open-source version in MySQL. We use MySQL Enterprise backup and logical backups that it's just a logical backup that you can restore everywhere. And the logical backups, usually people are doing MySQL dump, please try to avoid MySQL dump as much as possible and use the new MySQL shell dump and load utility, which is much faster, much convenient. And you can use it to dump schema, dump full instance, and also to migrate these instance to the cloud, for example. It works perfect. BitOCI. So now we are happy. We have this MySQL instance, there is durability if there is an issue. We have a backup. We can recover. It takes time to recover the full backup, of course. And we can lose data, but one day of RPO. That's a lot, right? So if we want to reduce that to minutes, I can lose data, but one day, I want to, I agree to lose some data, but not too much of data. So I will change now my goals, my targets, so RPO, so I still have a lot of time to recover, but I don't want to lose much of data. And here we need to concentrate on two issues, right? First issue would be, for example, we have a human issue, a user that has SQL access to delete star from table, blah, blah, blah, and forgot the workloads. It deletes all this data, right? So in this case, how can we recover the data? So we need to use bin locks and have durable bin locks, of course. Durable is always better. So meaning that when it's committed, it's written on disk and we are sure we don't lose them. So this is default in my SQL 8. So we have lock bin on and sing bit lock with on. Watch out, reducing durability, meaning you are losing durability. So this seems strange, but it's the fact, right? And it's common. So now we have that binary lock. So our user make a delete, we can restore our backup and then we play this binary locks. So these binary locks are files where all the changes on the database are stored and can be processed later by the DBA because in a DBA locks, the DBA cannot do anything with it. So this one, the DBA can use them. So the next level is that my data is still very important. It has a heavy workload and I want to lose less than a second of data. So minutes, but now it's even seconds. And not only that user that does something stupid, but also that some there is an issue on the system or a system engineer does something stupid could happen to that it deletes data directly on the on the myscale data directory. I've seen that already. People goes there and say, oh, this is a big file. Let's delete it. And it's a very bad idea. So now RTO hours, RPO less than a second. We don't want to lose data. How can we do that? So we have to, of course, still, if we don't want to less than a second, we need to have this sing durability here. It's very, very important. So we need to have the durability. We can enable GTID. This is not mandatory, but it's optional. And it's much convenient, certainly for the next steps later. And this is where it's also important is to offload the binary logs in real time. So all the enable GTID. So first thing to do is to specify or to see if we can restart or not myscale. If you can restart, it's easy. You do set persist only GTID mode equal on set persist only and for GTID consistency equal on these two variables needs to be enabled. As you can see, I don't modify my CNF manually. And let do it from the SQL using myscale eight. So in the myscale mode, I do set persist and myscale does all that for me. And it's very convenient to this settings when this commands when you are using the cloud, for example, where you might not have my CNF access. So this is when you can afford a restart. If you don't want to offer a restart, you can do modify. So the GTID mode progressively. So the first thing to do is to do set persist and for GTID consistency equal on. As you can see, I don't do persist only, but they do set persist. So I modify the running value to and then I modify the GTID mode from off to on going step by step to off permissive, on permissive, on and final, right? So this is how we do that. You modify that without a restart of myscale. Then we want to offload the binary locks. And to do that, we're going to create a dedicated user that will be used to do that in myscale. So we're going to create this user in my case, I call it cat bin lock. You will give it password. We say, okay, let's use SSL. Then we will grant the replication privileges we need. And finally, on a dedicated machine, what we're going to do, we're going to use the myscale bin lock program to stream and read from the remote server and stream all the binary locks there on the file system. Of course, this is the command. The command, you can make it better using a system descript or whatever you want with loops, checks or, but this is the basic what it needs to do. And when you have that running, all the binary locks are stream live to another machine. So this is very, very useful. After that, if you haven't you, you can do point in time recovery. And if you want to do it faster, if you have a lot of data to process because there was a heavy load, for example, in your system, this is a blog post that explains you how you could do it much faster in parallel. So please go to read it. I invite you to read it. So this was now we have this myscale server single instance, we are doing backup somewhere, we are doing saving the binary locks somewhere else, all it's durable, we are very happy. And now my service, it's more and more important. So my business going well, my application is going well, and I need to pay even more attention to my SQL. And because when we are done, we are losing money, I want to be up much faster than in several hours. How can I do? So I want to reduce my RTO, my recovery time objective to minutes, RTO less than a second, it's quite fine, let's do that. So the next step to be able to achieve this, it's to use my SQL in a DB replica sets. So my SQL in a DB replica sets are based on the native asynchronous replication known by a lot of my SQL DBs, right? So this is where we use binary logs that we have enabled earlier, where we use GTID that we have also enabled earlier and stuff like that. But it's easier and easier, it's better, right? So let's see how we can do that. It has data provisioning included using my SQL clone. So this is wonderful. You don't because sometime I've seen a lot of people, the issue they have with creating replicas, it's how do we provision the data the first time, right? So now to provision the data, you don't need to take care of it. Like I said, it's all built in and it will take care of it and it's very, very, very convenient. Of course, you can have two or more nodes, but it has a manual failover, but not an issue because we have minutes to recover. So it's okay. And it has transparent automatic route routing using MySQL router, right? So with MySQL router, you will have different ports and depending where you connect to, you will do writes or you will do, you will reach a read only server. So the architecture looks like this. So you see we have MySQL shell that orchestrates everything. You have MySQL replication there with the instances and you have a router that the applications connect to to reach the database. So now from that single instance we have, if we want to move to inodb replicaset, what do we need to do? So on the current server, what we do is first thing we do configure replicaset instance. So we do DBA. DBA is the global object in the shell that allows you to use the admin API. As you can see the shell here I'm using in JavaScript. So GS means JavaScript. You can use the shell, MySQL shell in Python or in SQL mode. So now I do configure replicaset instance. It will check my instance if there is something missing in the RwB logs there. It's the GTID enable because this requires GTID. So it checks all that. And also it checks the user you are using to connect to it. And usually by default while you just install a server you are using root and it says, okay, root cannot connect from everywhere. Do you want me to change that? Or do you want to create a dedicated user for that? I always recommend to use a dedicated user for that. So when you have done the configuration, you do the DBA create a replicaset and you put the output of this. This replicaset inside you assign it to a variable and the variable I call it arrest. But it's whatever name you want. And you give it a name and the name of my cluster, it's my replicaset. It's my replicaset. Now you just install another MySQL server with the shell. And on that machine you just do DBA configure replicaset instance again. It will do check for the user, ask you if you want to create a new user or not and make the modification. And when it's done, on the primary, so the single instance we had earlier where we create the replicaset, we do add instance and it will add the instance for you and it will create that replicaset. So when on the new instance you do configure replicaset instance, this is what we have. So it says, okay, we want to configure it. It's a bit small on video for sure, but you will have the slides also. So it says, okay, which user, what do you want to do with the user? Like I said, we always recommend to create a new one. So we're going to create a new one, option two. And we do give a name and the name it's cluster admin. So we do cluster admin, we give a password and then it says, oh, and for GTID consistency it's not enabled because it's not a default in MySQL 8. GTID mode is not enabled. Do you want to enable it? And server ID, every time you install MySQL server ID is one, it says, do you want to make a very unique value? You said yes. Then it says, I will do the change. Do you want me to restart MySQL for you? You said yes. It's all done. And the next step when you do add instance, it will tell you, okay, now we need to do the provisioning of the data. So what we call the recovery method, and it gives you the possibility to do clone, incremental recovery or abort. Clone is what you should do there. Incremental recovery meaning it will play the bin locks from the beginning. But it's almost nobody in production as the binary lock since they created the server on the same server. So I use clone, please. You do see it does that for you. And as you can see, it works perfect, very fast in parallel. And what does clone? So it makes this kind of snapshot of all the data from the source and send it to the replica. Using the shell and no need to open different ports and stuff. It's all go there using the MySQL port very easy. So when we have created that, it says, okay, the instance no, it's added. What you can see, we can do status and on the status, we can see that our replicaset it's available and that it has my SQL single my SQL. So it's our first servers first instance, we created that is read, write online and ask the role primary. So it's the second on the screen. And the first one on the screen is the new one we have added that is secondary read only, right? Perfect. And it use asynchronous application. So now we have this single instance where you can write data, the other one where we can read data out of it and where we can use in case of an issue. Then the next component that I was explaining from the full solution. So the shell, this is what we used to orchestrate and to create all that. MySQL, we have done that and was the router. So on the router, we need to bootstrap. So on the machine where the router is, you do mysql router bootstrap using the credential where to connect the system user to use. So in this case, mysql router. So this is the operating system user and it will bootstrap make all the configuration for you and you can start it and use it. It's done. It tells you here for the classic protocol, you need to use the port 6646. Or read writes, or read only 6647 and for X protocol, the same with different ports, of course. So now in the shell, now that we have created routers, in the shell, you have the possibility to see which router you created. So you can do list router and you see them here. You can see, oh, we have one router in replicasset. So now that we have this replicasset running, when an issue happen on the primary node, what do we need to do? Like I said, we have manual failover, meaning you need to monitor what's going on. You need to know that you have an issue. It's not a big issue because we have some minutes to recover the service when it's done. So when it's done, your page you need to tell you, oh, mysql primary node is gone. We don't have mysql. Please do something. So here we are connected to the replicasset using the router. We do status and it says, OK, you have a primary issue. The status is unavailable. You need to perform something. So what is the something? The something is to force the primary instance. So we're going to migrate the raw primary to the machine that it's still up. One of the machine that is up. In our case, we have single mysql and mysql2. So mysql2 needs to take the lead. So this is what we do. And as you can see, failover, it's done very easily and quickly. So this is easy to do. The only thing is that it's manual and then you need to check. And we cannot do it automatic because we are using a synchronous application. We have no idea of the network partition, quorum and stuff like that. All that it's maintained and it's managed by group application, for example. And this is the next level. The next level, my service is very important. I would like to be always up and to have this automatic failover and never lose data. How can I do that? So the objective has changed now. The RTO becomes seconds because we want it to automatic. So we don't want to page somebody, take the time to connect, blah, blah, blah. No time for it. And the RPO zero, we don't want to lose data. Do we have a solution for it? Yes, this is mysql2DB cluster. So mysql2DB cluster is based on mysql native group application, but it's easier. Like I said earlier, easier is always better. It has data provisioning included. Clone again. You need at least three nodes to make this automation or more, but you always use an amount of nodes to avoid split brain situation, of course. It has automatic failover, like I said earlier. We again use router and we have consistency level that are configurable for session globally to avoid store reads if it is an issue for you. We can, there is a lot of possibility in consistency level. I will be glad to answer questions if you have questions on that later. And this is the architecture of the InnoDB cluster. So we have the router, we have the servers that replicates using rubrication and the shell to orchestrate all that. So how do we migrate from this single instance, from this replica set to InnoDB cluster, what we do? The first thing on the primary of the replica set, we will do drop the metadata schema and then we will create the cluster. Then on the second instance, we do in SQL, stop replica, reset a replica to forget about that. And then on the primary node where we have this cluster object we created, we add the new instance, the previous instance, we do add instance, and it's added. And now on the third machine, so new machine we just installed, we do configure instance, get cluster again on one of the machine where we have the cluster and just add that instance. And it will do everything automatically for you like magic and it's perfect. Then don't forget the router we had, we need to bootstrap it again using group replication and InnoDB cluster. Then we need then to force the writes in the configuration and we can restart it and it's up and running. So we can do innoDB cluster status and we can see that we have a cluster running with three nodes and all it's healthy online and we can tolerate up to one failure for automatic failure. So one node crash, no problem. Another node crash again, then manually you need to decide which one has the lead to because of the split brain situation that could happen. So what's next when we have that? So the next step is for example to have this innoDB cluster and have an asynchronous replica for disaster recovery somewhere else. And to do that we have asynchronous replication source connection failover in 8022, so something we have added. We have a list of servers that if the source is not there, is not available anymore, the replica will select another source from that list. In 8023 that leads it's updated supporting group replication. So that's very easy to do. You could have also a delayed mass scale replica somewhere just in case you don't want to do point in time recovery too much or you want to find data a bit from the past. So some hours delayed for example. And also you could have mass scale innoDB cluster with an asynchronous group replication node somewhere. So this is possible. It's more complicated but it is possible. And of course more to come in the future. As you can see the mass scale team, HA, shell, replication server, we're all working to improve that more and more and to give you a solution more and more. So I think it's time for questions. We'll be just there to answer your question. Thank you very much and waiting for your question.
 Hello, welcome everybody to my session from single MySQL instance to HA.  So this is what I call the journey to MySQL in a DB cluster.  So thank you for joining this session at Fusdum.  So let's start with who am I, right?  So my name is Frédéric Descamps.  I am known as Lefret.  You can follow me on Twitter if you are looking for MySQL information.  I am MySQL evangelist.  So I am part of the MySQL community team.  I am managing MySQL since a lot of time.  My first version I installed was 3.20.  I am a DevOps believer living in Belgium and I have a blog where you can find a lot of  information related to MySQL called Lefret.be.  And as you can see, the address are still closed in Belgium.  So I apologize for this haircut right now.  So evolution for 2HA, right?  So this is what we are doing today.  So we start from a single instance to full HA, we are using MySQL.  So the start is made by a single MySQL instance.  This is you just install MySQL and you are using MySQL.  Some tips that are very necessary if you don't want to lose data when there is a poor cut  or something like that, it's to exclusively use an asset compliance storage engine.  So in our case, it's EnoDB and keep the durability to the default.  So meaning that you are writing in the EnoDB logs files all the transactions that you perform.  So at least when there is a crash, you do the crash recovery and you don't lose the  data that is being committed.  So we are running with a one machine.  We are very happy.  Everything works as expected.  But what's next?  So next level is that our database becomes more and more important and losing it, losing  the database might be an issue.  So what can we do?  And what do we need to plan?  And to do that, we need to put some targets.  And these targets are the RTO.  For start with, we're going to put hours and the RPU.  RPU to one day.  What does that mean?  So these RTO and RPU are some metrics that we use to design an architecture for our database.  And RTO means the recovery time objective.  So all on does it take to recover the service?  So all on does it take me for the service to be up again and be usable?  And the RPU is the recovery point objective, meaning defining how many of data can I lose?  The maximum of data that I can lose.  So the RTO, to start, we do the RTO hours.  So we have some hours to recover and set up again the system.  And RPU, it's one day.  We can lose one day of data at max.  So when we have these targets or these goals, what are solutions?  The solution, it's very common solution known by everybody, but not deployed by everybody.  So warning here, please, if you don't do it and do it now and certainly before an upgrade,  this solution, it's backups.  You need to have backups, physical backups, there are commercial version, open-source  version in MySQL.  We use MySQL Enterprise backup and logical backups that it's just a logical backup that  you can restore everywhere.  And the logical backups, usually people are doing MySQL dump, please try to avoid MySQL  dump as much as possible and use the new MySQL shell dump and load utility, which is much  faster, much convenient.  And you can use it to dump schema, dump full instance, and also to migrate these instance  to the cloud, for example.  It works perfect.  BitOCI.  So now we are happy.  We have this MySQL instance, there is durability if there is an issue.  We have a backup.  We can recover.  It takes time to recover the full backup, of course.  And we can lose data, but one day of RPO.  That's a lot, right?  So if we want to reduce that to minutes, I can lose data, but one day, I want to, I agree  to lose some data, but not too much of data.  So I will change now my goals, my targets, so RPO, so I still have a lot of time to recover,  but I don't want to lose much of data.  And here we need to concentrate on two issues, right?  First issue would be, for example, we have a human issue, a user that has SQL access  to delete star from table, blah, blah, blah, and forgot the workloads.  It deletes all this data, right?  So in this case, how can we recover the data?  So we need to use bin locks and have durable bin locks, of course.  Durable is always better.  So meaning that when it's committed, it's written on disk and we are sure we don't  lose them.  So this is default in my SQL 8.  So we have lock bin on and sing bit lock with on.  Watch out, reducing durability, meaning you are losing durability.  So this seems strange, but it's the fact, right?  And it's common.  So now we have that binary lock.  So our user make a delete, we can restore our backup and then we play this binary locks.  So these binary locks are files where all the changes on the database are stored and  can be processed later by the DBA because in a DBA locks, the DBA cannot do anything  with it.  So this one, the DBA can use them.  So the next level is that my data is still very important.  It has a heavy workload and I want to lose less than a second of data.  So minutes, but now it's even seconds.  And not only that user that does something stupid, but also that some there is an issue  on the system or a system engineer does something stupid could happen to that it deletes data  directly on the on the myscale data directory.  I've seen that already.  People goes there and say, oh, this is a big file.  Let's delete it.  And it's a very bad idea.  So now RTO hours, RPO less than a second.  We don't want to lose data.  How can we do that?  So we have to, of course, still, if we don't want to less than a second, we need to have  this sing durability here.  It's very, very important.  So we need to have the durability.  We can enable GTID.  This is not mandatory, but it's optional.  And it's much convenient, certainly for the next steps later.  And this is where it's also important is to offload the binary logs in real time.  So all the enable GTID.  So first thing to do is to specify or to see if we can restart or not myscale.  If you can restart, it's easy.  You do set persist only GTID mode equal on set persist only and for GTID consistency equal  on these two variables needs to be enabled.  As you can see, I don't modify my CNF manually.  And let do it from the SQL using myscale eight.  So in the myscale mode, I do set persist and myscale does all that for me.  And it's very convenient to this settings when this commands when you are using the  cloud, for example, where you might not have my CNF access.  So this is when you can afford a restart.  If you don't want to offer a restart, you can do modify.  So the GTID mode progressively.  So the first thing to do is to do set persist and for GTID consistency equal on.  As you can see, I don't do persist only, but they do set persist.  So I modify the running value to and then I modify the GTID mode from off to on going  step by step to off permissive, on permissive, on and final, right?  So this is how we do that.  You modify that without a restart of myscale.  Then we want to offload the binary locks.  And to do that, we're going to create a dedicated user that will be used to do that in myscale.  So we're going to create this user in my case, I call it cat bin lock.  You will give it password.  We say, okay, let's use SSL.  Then we will grant the replication privileges we need.  And finally, on a dedicated machine, what we're going to do, we're going to use the myscale  bin lock program to stream and read from the remote server and stream all the binary locks  there on the file system.  Of course, this is the command.  The command, you can make it better using a system descript or whatever you want with  loops, checks or, but this is the basic what it needs to do.  And when you have that running, all the binary locks are stream live to another machine.  So this is very, very useful.  After that, if you haven't you, you can do point in time recovery.  And if you want to do it faster, if you have a lot of data to process because there was  a heavy load, for example, in your system, this is a blog post that explains you how  you could do it much faster in parallel.  So please go to read it.  I invite you to read it.  So this was now we have this myscale server single instance, we are doing backup somewhere,  we are doing saving the binary locks somewhere else, all it's durable, we are very happy.  And now my service, it's more and more important.  So my business going well, my application is going well, and I need to pay even more  attention to my SQL.  And because when we are done, we are losing money, I want to be up much faster than in  several hours.  How can I do?  So I want to reduce my RTO, my recovery time objective to minutes, RTO less than a second,  it's quite fine, let's do that.  So the next step to be able to achieve this, it's to use my SQL in a DB replica sets.  So my SQL in a DB replica sets are based on the native asynchronous replication known  by a lot of my SQL DBs, right?  So this is where we use binary logs that we have enabled earlier, where we use GTID that  we have also enabled earlier and stuff like that.  But it's easier and easier, it's better, right?  So let's see how we can do that.  It has data provisioning included using my SQL clone.  So this is wonderful.  You don't because sometime I've seen a lot of people, the issue they have with creating  replicas, it's how do we provision the data the first time, right?  So now to provision the data, you don't need to take care of it.  Like I said, it's all built in and it will take care of it and it's very, very, very  convenient.  Of course, you can have two or more nodes, but it has a manual failover, but not an issue  because we have minutes to recover.  So it's okay.  And it has transparent automatic route routing using MySQL router, right?  So with MySQL router, you will have different ports and depending where you connect to,  you will do writes or you will do, you will reach a read only server.  So the architecture looks like this.  So you see we have MySQL shell that orchestrates everything.  You have MySQL replication there with the instances and you have a router that the applications  connect to to reach the database.  So now from that single instance we have, if we want to move to inodb replicaset, what  do we need to do?  So on the current server, what we do is first thing we do configure replicaset instance.  So we do DBA.  DBA is the global object in the shell that allows you to use the admin API.  As you can see the shell here I'm using in JavaScript.  So GS means JavaScript.  You can use the shell, MySQL shell in Python or in SQL mode.  So now I do configure replicaset instance.  It will check my instance if there is something missing in the RwB logs there.  It's the GTID enable because this requires GTID.  So it checks all that.  And also it checks the user you are using to connect to it.  And usually by default while you just install a server you are using root and it says, okay,  root cannot connect from everywhere.  Do you want me to change that?  Or do you want to create a dedicated user for that?  I always recommend to use a dedicated user for that.  So when you have done the configuration, you do the DBA create a replicaset and you put  the output of this.  This replicaset inside you assign it to a variable and the variable I call it arrest.  But it's whatever name you want.  And you give it a name and the name of my cluster, it's my replicaset.  It's my replicaset.  Now you just install another MySQL server with the shell.  And on that machine you just do DBA configure replicaset instance again.  It will do check for the user, ask you if you want to create a new user or not and make  the modification.  And when it's done, on the primary, so the single instance we had earlier where we create  the replicaset, we do add instance and it will add the instance for you and it will  create that replicaset.  So when on the new instance you do configure replicaset instance, this is what we have.  So it says, okay, we want to configure it.  It's a bit small on video for sure, but you will have the slides also.  So it says, okay, which user, what do you want to do with the user?  Like I said, we always recommend to create a new one.  So we're going to create a new one, option two.  And we do give a name and the name it's cluster admin.  So we do cluster admin, we give a password and then it says, oh, and for GTID consistency  it's not enabled because it's not a default in MySQL 8.  GTID mode is not enabled.  Do you want to enable it?  And server ID, every time you install MySQL server ID is one, it says, do you want to  make a very unique value?  You said yes.  Then it says, I will do the change.  Do you want me to restart MySQL for you?  You said yes.  It's all done.  And the next step when you do add instance, it will tell you, okay, now we need to do the  provisioning of the data.  So what we call the recovery method, and it gives you the possibility to do clone, incremental  recovery or abort.  Clone is what you should do there.  Incremental recovery meaning it will play the bin locks from the beginning.  But it's almost nobody in production as the binary lock since they created the server  on the same server.  So I use clone, please.  You do see it does that for you.  And as you can see, it works perfect, very fast in parallel.  And what does clone?  So it makes this kind of snapshot of all the data from the source and send it to the replica.  Using the shell and no need to open different ports and stuff.  It's all go there using the MySQL port very easy.  So when we have created that, it says, okay, the instance no, it's added.  What you can see, we can do status and on the status, we can see that our replicaset  it's available and that it has my SQL single my SQL.  So it's our first servers first instance, we created that is read, write online and  ask the role primary.  So it's the second on the screen.  And the first one on the screen is the new one we have added that is secondary read only,  right?  Perfect.  And it use asynchronous application.  So now we have this single instance where you can write data, the other one where we  can read data out of it and where we can use in case of an issue.  Then the next component that I was explaining from the full solution.  So the shell, this is what we used to orchestrate and to create all that.  MySQL, we have done that and was the router.  So on the router, we need to bootstrap.  So on the machine where the router is, you do mysql router bootstrap using the credential  where to connect the system user to use.  So in this case, mysql router.  So this is the operating system user and it will bootstrap make all the configuration  for you and you can start it and use it.  It's done.  It tells you here for the classic protocol, you need to use the port 6646.  Or read writes, or read only 6647 and for X protocol, the same with different ports,  of course.  So now in the shell, now that we have created routers, in the shell, you have the possibility  to see which router you created.  So you can do list router and you see them here.  You can see, oh, we have one router in replicasset.  So now that we have this replicasset running, when an issue happen on the primary node,  what do we need to do?  Like I said, we have manual failover, meaning you need to monitor what's going on.  You need to know that you have an issue.  It's not a big issue because we have some minutes to recover the service when it's done.  So when it's done, your page you need to tell you, oh, mysql primary node is gone.  We don't have mysql.  Please do something.  So here we are connected to the replicasset using the router.  We do status and it says, OK, you have a primary issue.  The status is unavailable.  You need to perform something.  So what is the something?  The something is to force the primary instance.  So we're going to migrate the raw primary to the machine that it's still up.  One of the machine that is up.  In our case, we have single mysql and mysql2.  So mysql2 needs to take the lead.  So this is what we do.  And as you can see, failover, it's done very easily and quickly.  So this is easy to do.  The only thing is that it's manual and then you need to check.  And we cannot do it automatic because we are using a synchronous application.  We have no idea of the network partition, quorum and stuff like that.  All that it's maintained and it's managed by group application, for example.  And this is the next level.  The next level, my service is very important.  I would like to be always up and to have this automatic failover and never lose data.  How can I do that?  So the objective has changed now.  The RTO becomes seconds because we want it to automatic.  So we don't want to page somebody, take the time to connect, blah, blah, blah.  No time for it.  And the RPO zero, we don't want to lose data.  Do we have a solution for it?  Yes, this is mysql2DB cluster.  So mysql2DB cluster is based on mysql native group application, but it's easier.  Like I said earlier, easier is always better.  It has data provisioning included.  Clone again.  You need at least three nodes to make this automation or more, but you always use an  amount of nodes to avoid split brain situation, of course.  It has automatic failover, like I said earlier.  We again use router and we have consistency level that are configurable for session globally  to avoid store reads if it is an issue for you.  We can, there is a lot of possibility in consistency level.  I will be glad to answer questions if you have questions on that later.  And this is the architecture of the InnoDB cluster.  So we have the router, we have the servers that replicates using rubrication and the  shell to orchestrate all that.  So how do we migrate from this single instance, from this replica set to InnoDB cluster, what  we do?  The first thing on the primary of the replica set, we will do drop the metadata schema and  then we will create the cluster.  Then on the second instance, we do in SQL, stop replica, reset a replica to forget about  that.  And then on the primary node where we have this cluster object we created, we add the  new instance, the previous instance, we do add instance, and it's added.  And now on the third machine, so new machine we just installed, we do configure instance,  get cluster again on one of the machine where we have the cluster and just add that instance.  And it will do everything automatically for you like magic and it's perfect.  Then don't forget the router we had, we need to bootstrap it again using group replication  and InnoDB cluster.  Then we need then to force the writes in the configuration and we can restart it and it's  up and running.  So we can do innoDB cluster status and we can see that we have a cluster running with  three nodes and all it's healthy online and we can tolerate up to one failure for automatic  failure.  So one node crash, no problem.  Another node crash again, then manually you need to decide which one has the lead to because  of the split brain situation that could happen.  So what's next when we have that?  So the next step is for example to have this innoDB cluster and have an asynchronous replica  for disaster recovery somewhere else.  And to do that we have asynchronous replication source connection failover in 8022, so something  we have added.  We have a list of servers that if the source is not there, is not available anymore, the  replica will select another source from that list.  In 8023 that leads it's updated supporting group replication.  So that's very easy to do.  You could have also a delayed mass scale replica somewhere just in case you don't want to do  point in time recovery too much or you want to find data a bit from the past.  So some hours delayed for example.  And also you could have mass scale innoDB cluster with an asynchronous group replication  node somewhere.  So this is possible.  It's more complicated but it is possible.  And of course more to come in the future.  As you can see the mass scale team, HA, shell, replication server, we're all working to  improve that more and more and to give you a solution more and more.  So I think it's time for questions.  We'll be just there to answer your question.  Thank you very much and waiting for your question.
mysql_ha_0_179.png mysql_ha_180_431.png mysql_ha_432_503.png mysql_ha_504_1499.png mysql_ha_1500_1631.png mysql_ha_1632_2975.png mysql_ha_2976_3251.png mysql_ha_3252_3683.png mysql_ha_3684_3803.png mysql_ha_3804_3959.png mysql_ha_3960_5099.png mysql_ha_5100_6647.png mysql_ha_6648_7931.png mysql_ha_7932_9707.png mysql_ha_9708_10655.png mysql_ha_10656_10859.png mysql_ha_10860_11483.png mysql_ha_11484_13739.png mysql_ha_13740_16043.png mysql_ha_16044_16883.png mysql_ha_16884_19043.png mysql_ha_19044_19643.png mysql_ha_19644_22667.png mysql_ha_22668_24131.png mysql_ha_24132_25631.png mysql_ha_25632_28139.png mysql_ha_28140_28619.png mysql_ha_28620_30407.png mysql_ha_30408_32363.png mysql_ha_32364_34283.png mysql_ha_34284_34979.png mysql_ha_34980_36983.png mysql_ha_36984_37103.png mysql_ha_37104_37676.png