Running MySQL on Kubernetes - Patrick M. Galbraith, Oracle

good afternoon everybody I'm Patrick Galbraith and I appreciate you coming to this talk this is probably one of the biggest audiences I've had certainly bigger than last year but we'll go ahead and get started I'm Patrick Galbraith I work for Oracle I'm on the minus QL as a service team just some things about me I've been in this industry for quite a while I've started out in tech well 1986 on electronics but I got into you know working in software and systems administration around 1993 94 found about this new OS called some guy called it Linux at the time and it was I spent a weekend downloading Slackware onto 26 floppies on my Packard Bell and I never could get extra on but it's been a wild ride and it's kind of interesting being back in Seattle because I really started my tech career here though I live in New Hampshire now there used to be these meetings in Ballard at talki Pizza run by Phil Hughes he started the Linux Journal so those were the sweet days and now we come full circle and you know I've been at all any number of companies I am now at Oracle I was at hewlett-packard prior Packard Enterprise blue gecko which was a database company here in Seattle minus QL I was there for three years classmates Slashdot in the u.s. Navy interestingly we were talking about MySQL and kubernetes in 1999 I worked at Andover dotnet they had a company that owned Slashdot and there were these two guys one from Finland one from Sweden David axe mark Monti fit in dias and they came to talk to us about this thing called replication that we need really direly needed for Slashdot and that's how it all began with MySQL for me some things about me I won't get into it too much because there's a lot of material I have to cover and I want to touch on Vitesse too I've got so many slides my big problem is I just always worry do I have material to cover in a talk well that's not a problem so about my team I Brisa I was at Oracle dying for the last year and I've recently moved to minus QL as a service we're building a MySQL service on Oracle's cloud I'm very excited to be starting on that work on MySQL again and working with many former colleagues from MySQL days the what is this presentation about well how many of you are kubernetes users kubernetes administrators okay how about MySQL DDA's ok so kind of a mixed group sometimes I give this talk and I have to kind of explain what containers are so I won't get into that as much but there is this kind of this misconception with MySQL on containers people databases on containers oh that's really scary so it's about my running MySQL in containers and in particular MySQL and kubernetes is about stateful applications and in particular stateful sets operators MySQL operator which there were there are two of them that I will show you and I'm really going to try to get to my the test slides though there have been many great talks on the tests here so I I can't touch the quality that sugu can do on talking about it it's such a great system it's hard to cover it all but anyway so I get a lot of material and time permitting if we have questions I have a number of demos I could play but there are two primary ones that I want to show you so we'll see how it goes so I won't get into this we know what containers are I often explain you know the thing I see have seen in numerous places is you see people who start using containers and to them it's this it's another type of VM you'll see them rum things like supervisor D and they're all kinds of processes running on the container and they're they might even be running chef I I won't say where I've seen that but they might be running a bunch of chef cookbooks inside the container anti-pattern but you know it's a learning process I did the same thing so it's often an education thing and dm's versus containers this is a slide I always show people to kind of hammer home what the difference is and when you look at this you can kind of see okay we're actually running on the host OS there's no hypervisor layer there's a little you know there's some layers that you have to go through but it's much more you know it's part of the OS it's not so much virtualization as much as it is isolation I mean that is a type of virtualization but it's different than a virtual machine and I won't go into VMs versus containers so this is a slide I I enjoy I I liked this graph that it kind of explained because you just you can see the whites in people's eyes when they start debate sort of container oh no it's more of a misconception than reality and I firmly believe that containers and in particular kubernetes makes it easier to run databases on it makes it easier to run any application on I believe testing is made easier no emulation layer databases it's always kind of this question of pets versus cattle the in databases you think of them more as pets a part of multiple databases database instances or one this was something that I discovered when I first started using Cabrini's I thought okay pods are one or more containers on a machine I thought I really don't want to run a bunch of MySQL containers so what does this mean because I always try to understand a new technology by how can a database run on it and in the documentation kubernetes pods are immortal that strikes fear into the heart of a DDA in a database is a very complex stateful application you with a database you want stable access you want a plication x' to always get the same database handle you don't want any surprises and safety you don't want to scale if it's unhealthy that's the persistent storage obviously persistent sticky storage so really it boils down to this for me it's the way I can have explained it database containers are the containers are the cattle the data is the pet and here's a graphic that this all I'll explain this so first of all you have your data that's the pet then you might have a config map or secrets then you have your container and the container runs and it sets things up it runs if it crashes well we have a femoral we have persistent storage we're using block storage or even on the host system that container might go away but we run another container it's the same thing as before and if you're using a stateful set as I'll show you it is the same thing it's just it's like restarting mysqld it's just it it's within a container so this is something to help people understand and not be afraid of it so with kubernetes and MySQL I think of it as a tale of two open source projects MySQL its world's most popular open source database 2bic widgets in around 20 plus years and then of course there's kubernetes the new person on the block it's the fastest growing open source project it's application we know all the things we I don't have to preach to the choir here and the board papers there for your convenience when you download the slides later which I put the most current version up last night I won't say how not ready they were so kubernetes ingredients for stateful acylation applications these are some things that I always think about that really make it much more feasible to run databases and other stateful applications on kubernetes we have labels I mean that's kind of a basic thing with kubernetes it's what allows discovery it allows things to associate know where the stable thing is that you need whether it's stores your network there's services obviously it's the abstraction that lets you connect to an underlying pod there's cube dns which is you know I worked at Dyne I still work it there's we're part of the same company and DNS is something people take for granted until it doesn't work and you want it to work right and this is something that's built within kubernetes with cube that makes it possible to do some really cool things helm charts of course you've tracked it out a little farther and you can think of think of it more as and like an apt package on kubernetes and of course persistent volumes persistent volume claims and storage classes staple sets some interesting things you can do in stateful sets init containers volume claim templates I really should kind of have sidecar containers under there because what I'm going to show you later utilizes this pattern then of course we have operators which takes domain knowledge and encodes it into a controller an application that does something for you that you no longer have to repeatedly do the painful way and then they're there there's all want also one thing you have node selector and affinity this is something I heard discussed by the fellow from Red Hat this morning of being able to schedule certain things such as a database on a certain type of node so originally my one of my first contributions to to kubernetes was I created a I submitted an example of how to run Galera and what it was is it was three pods and each one of those pods had a you know there was also a service and it was hard-coded you know this right here was the pod this here was the the service for the given node and then this was an overall service for all of the nodes it was very unflexible and this was you know a basic way of understanding this is a way of seeing how far kubernetes has come and how much easier it is now to run things like this and much more dynamic it's not this thing where you have to think about it then of course we have another simple way of running MySQL is to run through helm and there are a number of different flavors of MySQL and forks of MySQL you can run and these are three of them right here that's the simplest but there's so many more advanced ways of running MySQL and easier ways of running node selector as I'm and this allows you to specify some affinity and what sort of node you want to run it on you can have ha hard and soft affinity I haven't used this myself but it's something that's I find really intriguing and it's an incredibly useful part of kubernetes now we all get onto stateful sets so what our stateful sets you know we've heard a lot of discussion as to what they are and if you don't know they provide guarantees about ordering and uniqueness of pod resources anything that's related to that pod has a stable name a sticky name throughout restarts and it allows you to also connect directly to the pod you don't necessarily need a service to connect to the pod if like for instance if you have three minus QL servers running a master two slaves you can connect directly to the master for reads and writes and to the slaves you can connect read-only and you can specify those it also ensures ordinality when you start it up it's not like a deployment where it's random there's also one pod per index and they're not interchangeable and they won't it attempt to run with the same name they have a stable network identity this is important with databases in particular well with anything really and it's always named if you create a headless service say for instance you have a headless service called MySQL the each node that comes up will be node MySQL they have the service basically is creating a thing of it as creating as own a domain name for your internally for your service and they're stable across restarts also stable storage and stateful resources you know across restarts you know if your container underlying container dies it comes back up and reattaches to the the storage that was associated with that member of the stateful set and safety it won't scale if it's unhealthy that's very good that's something we want so here's a good here's some animations to explain to you kind of camera hone the difference here's a deployment so deployment you have of a replica set creates one pod creates another pod you notice these aren't in any order and they're not names with a you know each one doesn't have an ordinal name third one could come up and in this order and then of course you could have a crash well there's nothing to prevent another one from popping up so but then we have a staple set so we this one's called mysql' first node comes up second node comes up third node comes up okay you might have something crash no problem it won't scale it'll replace it so these two animations I like to use them is away they were they were actually something that Anthony yay put together and I added the animations too and they're really good for explaining it kind of helped me to understand when I was first utilizing stateful sets so of course I mentioned headless service and this would I mentioned is like creating a domain name for what you're running and you basically specify them without having a cluster IP and whoops I didn't want to go there yet and for read-only if you want to do read-only you create another service if you like or you can correct connect directly to the pod since they're consistently named and then we have storage class in volume clang Timbits in this example we define a storage class name OCI and it's using oracle clouds block storage and then within our state 'full set we have a volume claim template where we have an annotation kind of referring to that and then the storage class name OCI and so that when the the pod comes up it'll use that block storage at the simple here's a simple example of a stateful set this was an example that anthony a had put together i keep it around it's it's not something you'd necessarily use for production but it's a very it's a pattern you often see and one of the one of the operators i show you'll see this pattern you know it's three nodes you have a master and then two slaves and there's a read service that it connects to and then for rights you can connect directly to the master the way it's built is there's there are two init containers one init container sets up a config map and in Detroit sets up a ma'idah MySQL CNF file a comp file for MySQL if it's ordinal 0 it sets it up as the master if it's anything above 0 it sets it up as the slave and then there are two app containers one's a sidecar container that runs backups and again if the or if it's ordinal zero what it'll do is it'll create a clone of that first node and it'll create a netcat so that it's available for the subsequent nodes anything greater than ordinal 0 backup will do the restore and so on and so forth a very good that example if you look at that slide and look at it you can explain to you how a lot of MySQL operators are done and how the approach that they're taking operators now this as I mentioned takes domain knowledge that the things you do as a DBA or as a DevOps engineer it takes those patterns that you do repetitively even within kubernetes prior to their being operators and allows you to it builds it into the app if it's you know as I as I show you here there will be setting up there were two things that I'm gonna show you one is setting up I know DB cluster with group replication the the steps that you manually do that it's a little bit of work but it does that for you you don't have to think about it it's encoded into the application and another operator which uses Orchestrator which press labs built that does the same thing it sets up work a strainer for you does all of the tasks you normally do by hand and it's built into the app and so you can use it more as an abstraction from what you would normally do if you had to do it manually operators extend the crew brunetti api they create custom resource definitions such as you know here's some examples here at CD cluster Prometheus my minus QL it's not supposed to be a space there backup/restore oracle weblogic so on and so forth I'm kind of doing this out of order it leverages kubernetes primitives like replica sets and stateful sets and services they as I mentioned they execute common application tasks and they're usually installed as add appointment here's a good example of what operators do they observe analyze and act so the observed stage is to look at what there currently is in this example there's two running pods there's one is three one one one is three to 10 and then it analyzes it says what are the it does a diff on it basically it says what are the differences from desired config to running config well we should be three to ten and there should be three members and then there's act so act is going to get the desired config it's going to recover one member and that it's going to recover any of those that need to be updated to three to ten to that version I won't play this demo it's a it's a pretty cool demo I took doing a CD cluster and bringing it up and it's it's probably about a minute I sped it up quite a bit but it kind of it's a good example of how the operator pattern works and you can abstract you can extrapolate that farther for other types of things more advanced stateful applications here's a list of my SQL operators and two of these I'm going to demonstrate there's the Oracle MySQL operator and then there's the press labs MySQL operator the Oracle Oracle MySQL operator is your able to deploy highly available MySQL instance on a kubernetes with a single command it watches the API server for custom resource definitions related to MySQL cluster and acts on those resources there's backup and restore and there's also backup schedule that utilizes MySQL 8.0 group like replication and I know DB cluster has automated backup and restore to and from object storage s3 compatible in the demonstration I'll show you it'll be Oracles block storage object storage here's a kind of an overview of what the operator does you have the kubernetes api in the operator and it interacts with a stateful set running the mysql cluster and then within each each one of the pods it's basically set up like this you have the kubernetes api the operator creates CR d's mysql cluster and so on and so forth and then it ends up running a stateful set and within each one of those stateful sets there's the pod has an agent and a MySQL database the operator is acting on what needs to happen and updating the kubernetes api and then the agent also is talking to the kubernetes eye api observing what it needs to do and whatever it needs to do it it does those tasks such as cloning doing a backup doing a restore setting up the cluster and then the agent also as I mentioned does the the restore and the backup and it does that through streaming to object storage there are some simple examples I since I'm gonna do the demo I won't go into this scheduled backup that's the one thing I do when I show it it's pretty cool you such a schedule and a cron like entry for it when you went around the backup and set up which storage provider you want to use connecting to the Oracle MySQL operator there's a couple patterns you can utilize you can write directly to a single pod because it is a stateful set you can do a read/write only service if you like and then of course there's the MySQL router pattern which you'll see in the demonstration MySQL router is a new feature a new piece of software and what it does is it's intelligent about you can tell it which is going to be the primary and the secondary and if you want to pick a node in the cluster to write to and then specify which ones you want to read from it'll do that for you and proxy your minus QL traffic for you and often what you do is you your application pod you have that as a as one of the pods within your application pod and your connect to localhost within the pod and that connects you to the database the demo so this demo is what it's going to do is create a three node cluster created by the Oracle MySQL operator it's going to show components such as - cooperate or container in each pod node with the MySQL and each pods - ql agent sidecar container and then there's going to be a wordpress deployment that consists of a wordpress application container and then the mysql router container and the wordpress container is going to connect to localhost and inevitably through the database through that and what i'm going to do is i'm going to show bringing up the wordpress website and doing a back up doing something really stupid and doing a restore i'm also going to show you running external DNS - oracle clouds dns service i have a domain kubernetes - cluster comm and the website will be set up as that automatically so i'll go ahead and oh good it's full screen I'm in presenter view so it doesn't look the same as over there alright so the initial state we have nothing running just checking out helm so what we're going to do we're going to create the operator with helm I've created a namespace called MySQL operator and that's where it's going to run so we have the logs for the operator I can't see this whole thing uh-oh this is not cool actually you know what I'm gonna do I'm sorry I'm going to run this from an actual program that runs graphics sorry about that okay let me excuse me sorry okay I'm just go ahead and start this okay so we have initial state as I mentioned before let's skip ahead a little bit now I'm just gonna leave it alone last year somebody said you need to resize this and edit and I said I almost started I said no no no you get totally off track so we've created the operator we're looking at the logs so now we're going to look at the cluster we have a convict map we're gonna use the default authentication plug-in we have our password in a secret now here's our cluster with three members and we're the storage and now we have also we have to create a service account for this the maniacae well agent sidecar container so we're gonna create we're gonna create a namespace called WordPress WordPress we're gonna use that namespace for all subsequent operations so we're gonna create that service account now we're going to create the cluster so you can see the operator churning into action there it wasn't quite this fast when I originally recorded it but it is pretty fast so we're looking at the agent logs for the first node I have that running and the other the window to the left that's showing the the PVCs being bound on Oracle blocks to Oracle cloud block storage so I'm just going to log in to kubernetes dashboard and take a look around here well things are coming up you can see the deployments occurring see the containers are starting it looks looks good thus far so now we can see that the third node coming up and now the cluster should be there so what i'm gonna do is i'm going to the operator logs in that top left corner and then all the rest of the windows will be the the agents four zero through two just so you can see things happen like when we do backups like where is the backup going to be scheduled so the way backups work is that the operator determined picks out one of the secondary notes to perform the backup from the backup is MySQL dump currently only we are working on enterprise backup and we do have an architecture that allows you to have different types of backup providers so here's the the - the WordPress container pot I mean deployment and we're going to specify the DD host it's localhost 6-4 4-6 we also are going to create an external DNS deployment this is going to connect to the Oracle cloud DNS service and I'm also going to load a secret from a file here I'm not showing you that file so we're gonna create that now and I'm going to watch the logs this will be interesting because you can see when we as as the the deployment for WordPress comes up you'll see it's connected to Oracle cloud service now now we're going to create the WordPress deployment as I mentioned this is going to run MySQL router and WordPress and it also sets a domain host name for the WordPress service we're running wwq Bernese cluster com so you can see the load balancer now as an external service and a record has been added to DNS so at this point I'm gonna look at just kind of confirm that it's there there it is there's a text record in any record that was created that was kind of fast sorry about that but I was trying to squish things down so that we would have time so we're going to create our WordPress site and I promise this is gonna go really fast that was really typing fast when I did this so we have a blank slate right now for a blog what's the best thing we should do at this point make a backup let's run a backup so we're gonna do that so now we're gonna create a backup this is going to be the site before we customized it so it's going to be called interestingly - ql - backup - one here are the oracle cloud object storage credentials we're creating a secret for it to be able to connect to it we're creating the backup now we're gonna go back to the other screen watch this so if you see in the lower right window you can see that's where the backup happened that's on node that that's on the the third node in the cluster it could have been any one of the secondaries so yeah this is where I got really fast I drank a lot of coffee yerba mate day and I just went to town WordPress you know I just cut and pasted the the readme from the operator source in there I could have done the whole tip some lorem but I didn't do that so now we're gonna make a second backup because well we have a we've done all this work customizing our blog same place ah look at that so this is a command-line tool for Oracle clouds DNS well Oracle cloud in general so look at our backups we have two of them I'm going to describe the second one some information about that that's interesting so let's connect to the cluster I was really dumb dope that was a Gomer pile move anybody know who Gomer pile is I'm getting old yeah yeah you do okay go always sergeant I've never truncated a table or dropped a table good thing there are backups we did a backup so we're in pretty good shape here all the time doing what so we've done a restore restore happened on zero this time ah that's the one from we've got to do number two so let's do number two create a backup for two Bing bong bang ping run it where's it gonna happen it happened on number zero that's cool I mean it it's so much easier than the olden days back when we were at slash dot somebody's calling up sweating bullets because things are broken that's the beauty of kubernetes so to keep it going I'm gonna bring this back up here and go into presenter view this better not go back to the first slide good sometimes it does that it's really annoying so is as I was going to show you this this is the operator dimmer that's what it's basically doing but for this time sake we're gonna move on to the press labs my SQL operator so this press Labs is a company based out of Romania they build WordPress websites and they have a ton of data I just really started looking into this in the last few weeks and it's pretty interesting it uses MySQL or github x' MySQL Orchestrator it's something that a friend of Mines lomi Nowak wrote along with several other developers and it's basically for managing replications topology it uses asynchronous replication it makes it makes use of the percona utility user which allows it to manage the the cluster it uses paconne extra DB 5.7 there's a backup actually there's no restore that's a slide cut and paste thing but it does backup they're working on restore and it uses the custom resources for that there are a number of containers with this the operator itself has two containers there's the operator container itself and there's also the orchestrator container and that connects to every note every MySQL instance within the state full set so that I can manage it there's two init containers there's init minus QL which sets up the configuration files kind of like that pattern I showed you with the real basic master-slave replication and then there's also clone MySQL that of course it's pretty obvious what that does it clones based off of the first node and does the restores then there's of course the application container is a pre Kona container Picone a database and then there's some sidecar containers there's there's a sidecar container which does a lot of the work just like the the MySQL agent did in the previous example I showed you it it takes care of maintaining running the clustered running various actions within the state will set there's also PT heartbeat which just provides information back to Orchestrator and there's also a metrics Explorer container to Prometheus here's kind of a diagram I put together to explain how this works you can see that the two init containers taking care of talking to object storage to do the clone the sidecar container interacting with Orchestrator actually it's not sidecar it connects to MySQL that arrow is wrong something I did right before the talk and of course we have the operator and then PT heartbeat and the Prometheus exporter the do a demo of the press labs operator right now and what this is going to do is show you a three node cluster created by the MySQL operator it's going to show you each of the components the MySQL Orchestrator the MySQL operator container and in each pod with in with the MySQL and sidecar containers I'll show you the promotion of one of the slaves to master and then shutdown of the master kind of a dumb thing to do but it's pretty resilient the the youtube videos for these are also online so I'm going to do what I did before which is play it with an external so as as before we have a clean slate and we're going to add the repo for it then we're going to install the operator at this point the operator will be coming up don't go all right so this is the name of the cluster it has three Brad Lopes and of course it uses secrets because the sidecar container also needs to be able to talk also Orchestrator needs to be able to talk to the nodes we're going to create a namespace to run the syn so we're gonna look at the logs for the operator looks like things are working well the next thing we're going to do is look at the orchestrator logging you can see that it's also connected to every instance that comes up subsequently we also going to do a port forward this is really nice about orchestrators it has a really nice UI for managing your topology there's nothing at first as things come up you will see more nodes come up also watching the the persistent volumes as they get bound just kind of knowing what what's going on and when it's going to be ready to start operating on we look also looking at MySQL things look like things are working well there the first first note is up the master there are no slaves attached to talking to it yet but that'll change quickly you can see now the second note is coming up second pod is coming up and then the third part is starting to come up do a reload we'll see that there is a slave connected third pot is coming up and then pretty soon and everything will be we'll have two slaves and a master ah now we're ready so now we can do some interesting things with this we're going to look at the sidecar container for the first for the master now that looks good we're gonna look at the side cart and logs for the second the first slave now the second slave we're in business now we're gonna go into the master I'm going to look at the I'm gonna confirm that it is indeed a master and it is it's just kind of neat to see this work for me that this like ultimate all the the times I had to manage masters and slaves and write scripts and chef cookbooks chef cookbooks it's all automatically done for you so you can see that we have a master and a slave what does the slave connected to it should be connected to node 0 sure enough it is so now we're going to promote that danger zone I should have queued some Kenny Loggins on this it would have been really cool like I can't top Diana Ross though so show slave status look at that the node zero is now a slave that would look how fast that was to didn't have to go in there and figure out which log position we were at then have my previous boss calling me up freaking out it just works so now we're gonna shut it down that's just wrong oh no it's black that means it's broken and also the slaves are unhappy not to fear orchestrators doing its work it's bringing a backup also kubernetes is doing its work in bringing the pod back up and it's happening so quickly that it it's still maintaining being master in some cases it might take a little bit longer and what will happen is Orchestrator will promote one of the other ones that's kind of neat if you do it right you can go like through this whole cycle and see each one kind of cycling through alright so get back to the slides Wow look at that we're at the bit/s slide I made it so I got the editing view presenter view okay so I've updated the graphics the test is now accompanied by the way the test is a wonderful thing it's what I consider sharding on a silver platter how many of you have written your own sharding systems it's a lot of fun isn't it yeah yeah nice that's great we didn't even have that well we had something like that at Slashdot so the test is a solution for your database solution for deploying scaling and managing large clusters of minus QL instances it's been the YouTube database store since 2011 it's cloud ready the cloud is coming somebody here sitting here told me that once it helps you scale with transparent dynamic sharding and the ability to continue charting in or out cluster management there's tools for backups shard schema management it uses the MySQL client protocol and G RPC there's a connection pooling it has its own lightweight threads that it takes advantage of for go it also protects MySQL with query deduping rewriting sanitize a sanitization there are a number of monitoring tools built in to all by the test binaries if you want to mutton migrate your MySQL application to the cloud and you're looking for something that has a really bulletproof charting architecture or you want to move to sharding that's quite ideal for this you know I don't have a slide for this but the kubernetes says one thing I wanted to stress on this is kubernetes has really made it possible to do things that we couldn't do before how much time oh no is there anybody coming after this oh I'm sorry I'm really sorry are you sure you don't want to see the dump anyway I wish I could cover everything it's it's very difficult there you know my my takeaway in this is that kubernetes makes it possible do a lot of really cool things with databases and running the databases is made easier with kubernetes people would think oh no I don't have that complexity but it makes it easier to run it you don't have to do all of these manual steps and go through the pain that you used to go through in previous times back in the dark ages thank you for coming I'm very sorry I went over [Music] [Applause]

Channel: CNCF [Cloud Native Computing Foundation]

Views: 4,744

Rating: 4.6363635 out of 5

Keywords:

Id: g924HDB81oc

Length: 44min 9sec (2649 seconds)

Published: Sat Dec 15 2018