How to Monitor MySQL/MariaDB through Prometheus & Grafana ? #prometheus #grafana #mysql #mariadb

hey guys i lovely good morning to everyone my name is amnas and you're watching the learning destinations so today i am going to cover very useful topic and the topic is how you can monitor your my sequel or the maria dv with the prometheus and the grafner guys this is the another coverage under the playlist of the monitoring sections and where i have previously already covered the installations of grafner installations of prometheus and its integrations with the graphna and in my last tutorial where i have covered how to monitor the jenkins with the help of prometheus and the graffner so if you haven't yet watched you can visit the my playlist monitoring tool and you can watch these all latest coverage and now today i am going to cover how to monitor the my sequel or the mariadb with the help of from my thesis and the graph now so guys one more request if you haven't yet subscribed my channel then please subscribe my channel and press the bell icon as well now i am jumping into that today's agenda so now quickly understand today's agenda and in today's agenda the first sections i am going to show you that how we can install the mysql d exporter guys mysql d exporter is an open source project which gives us facility to collect all database metrics and finally we can publish it through the help of prometheus server then after that we will see that how we can configure the mysql d exporter so we can start as a service on our linux node as like as like let me give you one example how we start system ctl start httpd for the wave server in the same way we need to configure for system d file so we can start it mysql d as a service after that we can add this mysql d exporter inside the promise this yes con file so we can finally publish the data publish the matrix through the from enthusiast servers and we can query it from the prometheuses dashboard then we will start the promises services after that we will add run few queries uh like a mysql db and mario db matrix query from the prometheus and finally we will prepare the mysql mariadb dashboard and add it in the graph now for better visualization and finally we will run some queries over there and in such a way we will do the verification and testing so now let's move towards the tactical okay guys so now i am going to start the practical sessions and as i mentioned first we need to install the uh mysql exporter and it is publicly available and you can download it from here i'm just going to download it whatever the command as of now i am running you don't need to worry it because i am going to mention all these command under the description sections and you can take it from there so i have already exported this mysql now i need to untie it so this is the file is ready now now we need to move this file under the usr local bin sections and then we change the permissions we give the triple seven permissions okay now let's move toward the next sections okay guys so now i am going to create one of the user inside the database this user help us to give the correct permissions on the db so prometheus can easily fetch all the database related metrics with us okay so i have already installed in my case i have already installed the media tv here and just now i am going to login inside that so i i hope you have already installed the db or you just want to play with the this kind of setup so you need to pre-install the mario db or the mysql device so in my case i have installed the mariadb as we know that like mysql has been occupied by the if i am not wrong then it is has been occupied by the oracle right so now it is available as like the mariah dv with the name of maria dp okay so as of now let me show you we need to create one user so i'm going to select start from my sql dot user what i have been select user so as of now we have these users available okay so now i'm going to create one user and i'm going to give the name mysql dxporter and identify and this is the password [Music] this is the very simple password however i do not recommend you whether you keep uh some strong password here and since this is just for testing that's why i am just giving the simple password here after that i'm going to grant this user with the following permissions process replications client select on everything to mysql d exporter okay this is done now plus privileges and exit together so this database part is done now now we need to create one of the mysqld exporter.com file and here we are going to mention this address so basically here we are giving the user mysql exporter and the password which we have said that is ready and the host post of the your database and in my case type is one zero nine so i am saving it now and after that i am changing its permissions make sure that once you have installed the prometheus yes so you should have these users available and since my promises here server is running on the same node that's why this user is available in your case if you haven't yet create you can create or if you want to install the promises on the same node then definitely you have to create it if you don't know how to install the promises on the centos then you can search the my my uh tutorial in the same playlist monitoring tool where you can easily find out that how to install the parameters on the centos server okay so after that we need to create a systemd file so we can convert this mysql ds exporter as a service so now i am going to copy and paste these contents inside the system defile so i can use it mysql exporter as a service and here the wave listen address since i am running on the public ip sorry it's a private ivy so just i'm mentioning that it's also okay if you want to just allow for each and every ip like you can mention at 0.0.0 that is also okay but i have now just i'm giving the specific ip address of my this particular machine okay so now this part is done now since we have made pages inside the system device so we need to do the daemon reload and we can enable it as well however this is optional you want to enable or you want to disable it's up to you you can keep either ways and now let's just start the service if everything will be okay the service should be started service is just started now let me tell you one thing since we have configured it on this particular port number 9101 so we should get matrix on 9014 port number so you see that this is the mice equality exporter the matrix is listening under this right so these all are the from the thesis queries actually so these all queries we can simply run from the from a thesis and we can check the metrics so let's log in on the from ethicist server which is 192 and it's already there 9090 if we go to targets okay so as of now you see that there is no target there is no search target which is running on nine one zero four right so in order to make the target what we need to do we need to make changes in prometheus configurations file so it can start monitoring okay so we need to go under the prometheuses file which is etc from atheist and from ts.yaml and here we copy these four lines and then we come here down just make sure your indentation should be perfect because this is the yaml and here we can give the mysql mysql d exporter okay then it is running on the default matrix path so we don't need to define the customized metric path and it is running on port number 9104 so we need to mention here nine one zero four nine one zero four okay save it and restart the from ethicist server system ctl restart prometheus it is restarted let's check the status i think i have done something wrong let me check so yeah as i said that this is the yamil language right so we need to very careful with the indentations let's restart again the status again failed was strong let me check once again okay here let me just quickly fix it this time it should be done i think we are good to go now restart and status okay so it's running now let's refresh it and we can see somewhere nine 9104 which is state unknown let's refresh wait for some time and now it is up you can see that it is up and we can clearly see all the matrix okay so let me open it in the new tab so here we can see all the metrics and now we can simply query and if we type here mysql so it will list all the details here let's come down down down we are going to run some easy query where we can easily identify whether it's connectivity is happening or not successfully so let's suppose that i am going to run a schema thread exit is giving the value let me run some more command some more queries here there are a lot of queries guys i am not much aware about all these queries so you want to if you want to play just go ahead and play with all these options so i am just verifying that whether it is successfully collecting the data from our tables or not so i am going to run this one mysql info schema table rows and execute and you can see that there is a schema called devops and table is environment so this table is already exist here so let me show you okay so if i go through databases these are the database and i'm going to use one of them the box two tables so these are the tables then i'm going to select select everything [Music] this ad is keep coming from a and b right so let's execute it so there is a one column called and under that the value is java okay so you can see that the schema is devops right the database name and table is environment that means it is clearly proving that it is successfully uh pulling the matrix from the exact this database okay so in my case i have used the maria db however my sequel and maria debbie both are the both db's are equal so now let's move to the graphene side and we will fetch all these metrics inside the graph now and we will see over there how it look likes from the graph now hey guys so now let's move towards the graphna side and see that i have already installed the grammar which is running on port number 3000 so those who don't know how to install the graph now i have already made the videos and you can go under the same playlist where you can find out how to install the graph now on centos linux so this is my graph now and where i have already added data source from my thesis and i have already made video on that also that how you can use the data source how you can add the data source inside the graph now so which you can find out under the same playlist okay so if we go on this data source and if we try to uh run the same query which we have run from here right so for that you need to go under the explore sections and here you can select the queries let me type the mysql and i am going to run the same query which i have run from the from my thesis dashboard so if i go here the table rows and run a query and we can see the value here you can see that right the job name is mysqld exporter schema is devops and table is environment right this is the same which we are getting from the promises yes dashboards the same we are getting from the from my thesis uh from the graph now as well now let's make it more interesting by adding the dashboard and for dashboard you can go to the import sections guys graphene itself provides a lot of fancy dashboard sections right so all you need to do just export it here so i have already chosen one of which looks pretty much fantastic and it totally depends on you you can go and search there are many more dashboard available and you can choose as per your convenient okay so i'm going to use this one and i say the url id okay load and after that here i need to change the id which is 7 3 which is let me show you where it gone here we can take it 7362 so we need to paste here 7362 and select from the thesis as a data source import so now you can see that it is saying mysql uptime so this db is approx up from last 4.4 hours and it is giving there are various options various metrics you can track it from here so you just go one by one right and play with all these options so here you can see everything buffer size then mysql network traffic you can see mysql temporary object so everything you can see here right then after that so this is one of the and you can save this dashboard add a note to describe save current variable values on the dashboard save it now you can see the same dashboard from here you go to dashboard home right and mysql overview this is one of the dashboard which we have just created right so there are various metrics you can track it from here okay this is all about the things that how you can uh monitor your mysql or the mario db with the help of probably these yes and the graph now so this is all about it for today and if you have any questions just feel free and write me in the comment sections and i will help you guys so i am done for the day please feel free to write if you have any questions you can directly ask me in the comment section please share your personal experience also that how do you feel after watching this video whether i should improve in any other areas you can also give me your suggestions and your suggestion is always appreciated so guys one more request please don't forget to subscribe my channel and share it with your friends and the colleague as well so i will see you in my next tutorial till the time thank you so much for watching it you have a very lovely day guys

Channel: Avinashkumar - The Learning Destination

Views: 8,660

Keywords:

Id: q2C1K69pRBY

Length: 18min 20sec (1100 seconds)

Published: Wed Feb 09 2022