MySQL monitoring using Prometheus and Grafana | MySQL Exporter

hello people welcome back to my channel and let's learn about MySQL monitoring using Prometheus so MySQL is a relational database and Prometheus is a metric tool where you basically collect metrics from multiple places and then plot graphs through it so why there's a need for MySQL monitoring so if you're using a hobby project then that is fine like this video is not for you but if you're working on a production system where you have my SQL instances connected and there's a and that is a scalable system so it becomes very necessary for you to monitor your MySQL for any anomalies or any issues so let's say there's a high dis I Ops usage so how you will come to know that okay the dis iops is high if there is say like a auto vacuum or something running in the background which may increase the CPU or the RAM on your MySQL server so what to do in that case so if there is a monitoring tool so you can also have some alerts so that it notifies you and your system is safe so let's learn how we can do this MySQL monitoring using Prometheus so there are some prerequisities that needs to be fulfilled one is what Docker must be installed and Docker compose must be installed I have already created this post on install Docker on your machine and basically this covers most of the operating system let's say windows ubun 2 and I'll add more once I write another block post so it may possible that if you're seeing that you can see more list here so you can choose your operating system and then follow the link to basically install Docker I've created another blog post install Docker compos on Linux I'll create for Windows also and I'll paste all these links in the description below so you can go through these links and fulfill your prerequisites so what we are going to do that there's a MySQL server and this MySQL server will push the metric to this Prometheus server using a MySQL exporter and then basically we'll plot these beautiful graphs out of it so let's see what we are going to do so there's a host operating system which can be a Windows operating system or Linux operating system anything whatever you are using so there's a host operating system and then there's a Docker environment so when you create Docker containers so it has its own environment within the host operating system so here what we are going to do we are going to install Prometheus container grafana container MySQL container and this my SQL exporter container also and then what will happen this my my SQL exporter will scrape the metric or you can say pull the metric from this MySQL server and basically the Prometheus server then then we'll scrape the metric from this MySQL exporter so the Prometheus will call this MySQL exporter in some interval of time and then basically once it calls MySQL exporter so there will be a connection between MySQL exporter and MySQL so this MySQL exporter will take all the metrics from here and give it to the Prometheus and then using using this graphical interface tool that is grafana you can view the metrics that are present in this Prometheus the Prometheus will be running on the port 9090 both on the host and inside the docker container it is 9090 that is exposed the MySQL exporter will be on 9104 Port the mySQL database server will be on 3306 these are all default ports and then grafana will be on Port 3000 there's also a Prometheus MySQL file that will be present on the host operating system which will mount to the Prometheus container and then this prometheus. yaml file will do what it will have the configuration like at what interval uh it needs to call MySQL exporter server to fetch the data for MySQL so we'll see all those configurations and there's another my CNF file which will be mounted on this MySQL exporter and this will basically give the configuration like to which MySQL server you need to connect using its username and password so this is the full setup that we are going to do so let's tighten your seat belt to understand everything in detail I have already created this blog post on MySQL monitoring using Prometheus if you want to copy paste some data from here like all these configurations Etc you can use this blog post also I'll put it in the description below so the first thing it says what is like you need to create this file that is prome MySQL the name can be anything whatever you want but in our case we are taking this file name so I'll go to my terminal here and you can see I'm inside this YouTube playground folder and then I'm going to use the command that is touch Prometheus MySQL and I'll do hit enter so basically it will create the file the next thing I'm going to do is I'll open this file inside the uh my ID envirment and I'm using intell ID so what I'm going to do I'll click on open I'm using uh the windows w SL so it will be inside WSL home and then basically workspace and then we have YouTube playground so I'll click okay and you can see here that the Prometheus MySQL yaml file is present and it is empty so for defining any Docker compos file what we need to do we need to define the version of the syntax that we are going to use so we will say version equal to 3 so we we'll be using the version three of Docker compose and then you need to define the services so the services are what the individual entities that we are going to create so like this Prometheus grafana MySQL and my exporter so these are the services so we'll give the details of these Services here the first service that we are going to Define is the MySQL service and we are going to use the image MySQL this is basically going to fetch the latest tag which is the latest version whichever is available on the docker Hub the container name will be my SQL it will always restart even if there's a crash and there are some important envirment variables that is what the mySQL database that we going to automatically create is test the MySQL user uh sample user we are going to create the password for this MySQL sample user will be password and the password for the root user will again be password the port is 3306 on both host and inside the docker container and this is like expose Port that is like uh it's not important configuration then comes the volume part so we are creating this volume my SQL DV in the current folder itself like when we whenever we'll start so a folder will be created my SQL DB and that will be mounted to the volume where live my SQL and why this volume is important it's needed so that the data persist so even if you if your MySQL server crashed you restart it you like delete everything from there all the containers and everything related to my SQL but still the dat will be safe and secure because we are using a volume the second service that we are going to Define is Prometheus and again we are using the image prom Prometheus which you can look up on Docker Hub the volume is this prometheus. yaml file which we'll be creating here later on and it is being mounted to the /c Prometheus prometheus. EML and the port is 9090 again I'm explaining that why this prometheus. EML is important and it needs to be present on the host operating system because you need to provide some configurations like in what interval this Promethea server is going to scrape data from myql exporter and everything like all what is the host name of this MySQL exporter and everything that that configuration will go inside this prometheus. yaml file so this is the Prometheus configuration the third service that we going to Define is grafana again it is being fetched from grafana grafana image the container name is grafana Port is 3,000 both on host and inside the docker container restart is unless stopped that is fine that is not important envirment is what there are this is important like whenever you open the grafana it will ask you for credential to log in into the system so the default is admin admin which we will change later on and again there's a volume part like because we do some configurations like we connect grafana to a Prometheus server so all those data are present and that should be persisted even if the container is deleted or it is removed mode so that's why we are creating a volume for grafana also the fourth service that we going to Define is the my SQL exporter and this basically fetches the data from the mySQL database and gives it to the Promethea server so again we are using this image prom my SQL D exporter the volume that we are saying is that there will be a my CNF file which we are going to create here in the folder and which will contain the configuration like the username and password for the for the MySQL server which we are going to connect and it is on the port 9104 9104 both on the host and inside the container there are two important configuration one is like you need to basically say from where the config file is being red so once we Mount the file that is present on our host operating system to this location we are seeing that the config my CNF is present here only / cfgm my.cnf and also you need to provide the address of your MySQL server so it is IP address and the port so my this MySQL will be running on this IP address I did it I found out the IP address using the if config uh command you can do a Google Search and basically see that what is the IP address of your machine or the IP address of your MySQL server or basically the host name and the port here is 3306 so this is the whole configuration that we have done now let's define one file that is what that will be prometheus. EML file so that will be prometheus. EML file you see that the Prometheus EML file is created then we will provide some important configuration so we are defining the global configuration which will be applicable for everything that we are saying the scrape interval is 2 second we can override on the individual jobs and the scrape config is what we are providing a job name as Prometheus and the static config like the target is Prometheus 9090 which we have deed so we are saying that the Prometheus server from where we want to fetch the data is Prometheus 9090 so this service name becomes the host name inside the docker container and also for my SQL exporter we are we have provided the IP address and the port so IP address of my machine that is 192 1681 18226 which I found out using the if config if I go to my terminal and if I do if config so you can see that there's a EES address and it is 192 168 18226 so this is the IP address of my machine and I'm pointing my my SQL exporter using this IP address and then the port is 9104 which we have already seen here that it is deployed on 9104 both on host and inside the docker container so all the configurations are done the next configuration is for the my CNF file so I'll create do my. CNF we will Define some configuration in this my.cnf file that is the header will be client the user will be root and the password will be password so you must have seen that in this my SQL root password we have given the password that is the password for the root user so that's why we have given the user as root you can use another user also you can create a new user in my SQL and use it give them permission whatever you want so this is the whole configuration that is done now let's go to the terminal and start the services so let's see Docker PS so there's no container Docker PS hyph a there is no container like in exited State also so it is full clear now if I come here everything looks fine there's prometheus. EML file but there's no my. CNA file because it is a DOT file means it starts with DOT that's why it is hidden so we can do it lsy La you can see it is present do my do CNF now let's go to Docker compose hyphen f and this prome s MySQL EML up so we are giving the configuration like hyphen f which reads the file and then up to start it in the foreground mode if you want to do it in the background you can attach a hyph d flag so I'll hit enter and what it does since I have already uh having these images on my local system so that's why it didn't downloaded it but in your case what will happen first it will download the docker images and then it will start let's see the states of our Docker container so if we see Docker PS so you can see YouTube grafana and everything is up so like all the four things are up let's go to the browser and visit each service one by one so let's first open the prome here so that is Local Host 9090 so this is the port so you see that okay it is coming like the promia server is being listed here and like there are some uh things on this the screen which means it is working fine now let's go to grafana so 3030 is the port so I'll hit 3030 and this time what happens uh sorry 3,000 3,000 is the port and it will take you to the grafana so okay this looks fine I'll put the username and password if you remember that the username and password for this one is what admin and admin so I'll go to the browser here and I'll type admin and then again admin and hit login so it ask me to give a new password so I'll set a new password for myself you can do for your own so this is the password new password that has changed and everything comes here so okay the Promethea is also working fine graan is also working fine let's go to Local Host 9104 and hit enter so the my SQL D exporter is also fine and if you see metrix so you can see this my SQL Global status command so like these are the metrics that are coming from MySQL which means it is able to connect so if you go to the diagram here so what happens when I opened here so it tries to fetch the data from here and it the connection and everything is successful because we have provided the correct IP address so that's why in the browser if you see you are getting all the my SQL listed uh metrics let's also connect to the my SQL and see it is accessible or not so for connecting to the MySQL what I'm going to do my SQL hyphen H hyphen H is host I'm running it on Local Host so that's why 127.0.0.1 let's see whether we are able to get the data inside the graph like we are able to plot some graph or not so let's go to the browser again let's go to grafana and here first what we need to do we need to add this connection like we need to establish this connection from grafana to Prometheus that we have shown here so for that what I'm going to do inside the connection there is something called as data sources add data source Prometheus uh name will be Prometheus and the UR will be HTTP Prometheus 9090 why Prometheus because we have given the service name as Prometheus that's why since the service name is Prometheus so that becomes the host name and it is accessible through it so save and test so successfully query with the Prometheus API so the connection is successful so if you see this connection from grafana to promethus is successful we have also this seen this connection my SQL exporter to my SQL D it is going on like it is fine uh we have seen it in browser now let's go to the dashboard and here create dashboard add visualization and I'll select the data source so after selecting the data source let's see type some command so you can see this one all the MySQL related metrics are present here so you can see if I expand it so MySQL Global status means these are some metrics I also don't know everything about all these metrics but okay means it looks like that the metrix from the my SQL is available in the Prometheus and you can add alerts to it also let's say like the CPU goes something this much percentage the ram is this much percentage so you need to raise the alert you need to trigger something so you can do it through it so you need to basically learn this query writing using the promql then only you will understand so you need to do some research and query but let's see we are able to plot something or not so so I'll just type like connection let's see my SQL Global status connection and then I am going to say 1 minute that is rpm and then I'm going to write sum and increase like what is the or let's not uh do these things let's just see that whether it is coming or not so I'll delete this sum also I'll run queries and you can see that there's a graph so this is last 6th hour this is fth so you can see global status connection is constantly increasing so this is one of the metric that is coming so you can see that it is working fine so what we have done we have done all these deployments and everything and basically everything works fine in one shot only that is very good so you can visit my website also here and if you want to copy paste something things so everything is present here the whole configurations what we have seen here everything is present here you can just use it for your your hobby purpose or your production purpose so thank you for watching this video hope you like this video please subscribe to my Channel please share this video with other people and you can help me out by clicking on the thanks button and I also add some details in my descriptions below thank you very much

Channel: CodeWithRajRanjan

Views: 600

Keywords: mysql monitoring, mysql prometheus monitoring, mysql grafana, mysql exporter

Id: cjIb-lKeN5s

Length: 18min 48sec (1128 seconds)

Published: Thu May 23 2024