Free MySQL Monitoring With Zabbix Monitoring Software

there we go another video about zabbix that will include a tutorial we're manual or you can call whatever you want how you can monitor on my sequel or Maria DB database engine using an open-source monitoring solution called zabbix for me this week was with a lot a lot of things and well basically because of those things I have some spare minute to create additional video this week for all of you so just let's get started when I say a database monitoring my sequel Maria DB database monitoring you might think like wait but you've had one video about how DBC monitoring yeah that's true and that also works for to my sequel and Maria DB but ODBC monitoring is slightly different thing Oh DBC monitoring basically is used to monitor the data inside your database so we are operating with actual data and this one the my sequel monitoring where Maria DB monitoring is the database back-end monitoring and we'll be collecting the performance metrics why is this important well I guess I don't have to explain why it is important to keep your database in a good shape especially if it if it is production database but if you don't have any kind of production or development database engines specifically my sequel where Maria DB then I assume you do have a zabbix server installed for some other tasks and there is a high possibility to your zabbix server is running and using my sequel or Maria DB database as a back-end database for the server don't make that mistake and think that okay I have a monitoring setup and I am monitoring 200 thousands computers and servers and network devices and applications but I am not monitoring my monitoring solution that's very big mistake so if you have a my sequel or Maria DB database configured which yours Abuk you should definitely monitor it and how we'll be doing that nothing complicated it will not involve any kind of custom scripts no plugins no no no some complicated stuff from the github all of the things will be used and achieved using a built-in functionality inside a zabbix and also a built-in template a built-in template that is called template DB my sequel this one is available right after the installation of dis AMEX so you don't have to search for it and the internet or whatever else if you have a zabbix if nobody had deleted the official templates from your system then just go to the configuration templates and you will find it here this one has like 14 items one trigger a couple of graph screens and what possibly could happen let's say you found a template you know oh wait I have a my sequel database so create a coast for your my sequel database specify the IP address link this template and then what happens I've created the same thing mmm the host for my my sequel link at the template and oh well it doesn't work most of the items all of them except my sequel versions are not supported we're not very explainable error messages cannot convert value to numeric type okay whatever that means well this one makes some sense ax is denied for users Avex at localhost using password no so we're trying to identification excuse ER but without any password that makes sense but if we don't know what is this about how we'll try to fix it I can open the item ok I should specify the password somewhere where but yeah so there we can find information it requires user parameter my sequel dot status which is defined in the user parameter underscore my sequel cough where to find it well we need to go to the CLI and this file this one user parameter underscore my sequel conf actually is available as soon as you install the zabbix agent on your machines so when you deploy the zabbix agent that user parameter file will be already there and you just have to make a couple of changes inside it where you can find it oh let's go to the Etsy zabbix SABIC s-- agent d and you probably already wanna add cons but that's not the case we need to go to sabbaq sabbaq underscore agent DD directory and inside it you will find this precious file user parameter underscore my sequel comm so let's open the file let's open the file and you can see that mostly you'll find a comments and just couple of rows that actually are user parameters so the thing that adds a custom checks a custom item to your zabbix and these user parameters are executing just echo show global status were variable name equals the first one and we have a star here so this is a flexible user parameter which means that we can pass a parameter from our front end and that is done in in the template so we can see my sequel status come again and my sequel status a flexible parameter so it would use a combo kit and it would execute show global status were variable name equals first parameter in that case would be com begin and what do we need to change for it to make it work for all the following commands home should be set to the directory that has dot my dot CNF file where the password information be careful it's not my dot CNF it's not this file not the one it is da in the beginning so you we you need to create a new file and let's do it straightaway and inside this file we need to specify a password in the username that will be used for identification let's add it in Etsy zabbix where let's make a TMP just for the sake of the test TMP dot my dot CNF right and first of all you need to add on my sequel in the brackets then a username equals zabbix and a password is us were the equal zabbix if you have different credentials then of course write different username and password for my test lab environment with just an empty installation I have created a user name in the database called zabbix and also with the password zabbix and my static server is running with these credentials so save the file the file is inside a TMP now let's set it again the user parameter file home let's search for those things where it's my mouse there we go home EDC zabbix no it's not it's time P right temporary folder TMP another one here in the bottom o equals not at C zabbix bought TMP and the last one is here so in total we have three places where you need to change the home directory to the directory where you created a doubt my dot CNF file with a username and password inside so let's save this one right and quit since we made a changes in one of the configuration files of the zabbix agent we always need to restart cervix agent because Avex agent reads the configuration including the config file changes only upon a start and now we can we can actually check will items be working with a zabbix get the coast is localhost My Mind's a box agent is installed on a local host and the key oh my sequel status comm dodd begin like this ah known they're able username is epics okay maybe it was maybe it was a user user let's try again yep there we go so we can indeed get a value oh I'll just tell one more time change this from the user name to user otherwise it will not work that's it so now we can go back to the front end and let's good don't do this step I'm just doing that for the testing purposes to make things faster you don't want to wait 10 minutes until those items will become supported where we can just full clone this ghost call my sequel monitoring works so we have a new one and I will reload the configuration cache now we should refresh the page let's wait till this one becomes green let's check for the latest data my sequel monitoring works ok the interval is 1 minute so we'll still have to wait 1 minute until we'll start receiving some data not a big problem what else they can say well this template has only 14 items but it's not limited to it using password equals know what my sequel status my sequel thing thing I forgot something my sequel think my sequel thing my sequel item and connected to serve at local X is denied for users app using password No [Music] okay most of the things are working but one item doesn't my sequel dot Bing my sequel dot Bing home not be and PE let's check it with what a zabbix get yeah indeed how equals T and B yeah let's let's leave it as it is not sure right now why this one is not working but my sequel thing can be configured even without that user parameter of files in this case we're mostly interested in those my sequel status see like begin operations per second bytes received per second bytes cent per seconds and a lot of these different things and when I told that that's absolutely not set not at what did I meant so how is this value checked it's comes from my sequel dot status item user parameter so this one and that executes show global status show global status in the database so basically show global status this one and there are 413 variables that you can use we could I don't know what can we do let's pick this one aborted clients let's go to the front-end let's create new item on our host configuration hosts my single monitoring works item create a new item aborted clients my sequel non-status aborted clients same variable name from show global status output I will make update interval one second just to make it faster add this one I will again zabbix server - capital are config gosh - below just to make it faster and yeah there we go - the monitoring latest data aborted clients where do we have them there we go Ted so the same value that we had here which basically means that there this aborted clients 10 based on your needs based on your database engine and what do you want to look for you can create a new items using the same template my sequel monitoring my sequel database monitoring pick any variables from this output like I know DB locks were the rowlocks something that might be relevant for you create a new items and set up a monitoring of your my sequel or Maria DB database engine all works except this one I see equal status my sequel dot ping I'm not sure why and I don't want to do a troubleshooting now in this video I guess it might be related to just some versions up to my sequel or Maria DB ok oh that's it that's it for this video thank you as usual likes comments whatever else and see you later in the next videos goodbye

Channel: Dmitry Lambert

Views: 24,576

Keywords: mysql, mariadb, database, monitoring, innodb, myisam, zabbx, zabbix, tutorial, manual, howto, template, items, trigger, user parameter, zabbix agent, zabbix server

Id: 755gDTuHVjM

Length: 15min 30sec (930 seconds)

Published: Wed Feb 13 2019