MySQL is a free, open-source relational database management system (RDBMS for short). It is capable to provide high-performance, high-availability, Robust transaction,… etc.
MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. MariaDB Server is an enterprise-grade database, extensible and secure at every layer.
Nodelizer helps you to monitor following metrics about MySQL / MariaDB installed on your server.
Installing module: rptmonitor uses PyMySQL module to interact with MySQL/MariaDB. So this module should be installed for monitoring MySQL/MariaDB.
For PyMySQL Installation :
- installing with pip:
pip install PyMySQL&
pip3 install PyMySQL(python3)
Alternatively (e.g. if pip is not available), a tarball can be downloaded from GitHub and installed with Setuptools:
curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
# (X.X is the desired PyMySQL version (e.g. 0.5 or 0.6) or look for latest).
python setup.py install
# The folder PyMySQL* can be safely removed now.
‘pip’ is preferred installer program.
- installing with pip:
Edit the agent’s config.cfg file.
- Fill in values for variables – mysql_server, mysql_usrnm, mysql_pswd. Uncomment the lines containing these variables (remove # symbol).
- You can also customize port &/or socket if not running the default, through mysql_socket, mysql_port config variables.
To enable replication checks, remove # symbol before mysql_replication config variable.
- You’ll see Seconds Behind Master – metric in MySQL/MariaDB Stats if replication is enabled.
- Imp Note : The user to connect to the database does not require any special privileges unless you wish to monitor replication (which will require SUPER or REPLICATION CLIENT privileges). It is recommended you create a specific user with no privileges (except for connect). See the documentation for user management instructions for MySql and MariaDB.
Restart rptmonitor :
For deb/rpm package supported Linux distros :
sudo /etc/init.d/rptmonitor restart
For openSUSE(11.4+) with systemd:
sudo systemctl restart rptmonitor.service
For other Linux distro, FreeBSD 8+, Mac OS X+ :
sudo python /usr/local/bin/rptmonitor/monitor.py restart
Alert for Replication :
Seconds Behind Master –
- metric shows the time in seconds that the Slave’s SQL thread lags while processing Master’s binary log.
- A continuous increase in this value is not a very good sign as it means that the slave is not able to catch up with its master.
- (You’ll see this metric in MySQL Stats if replication is enabled in config.)
- If MySQL replication fails completely then the agent will return -1 for Seconds Behind Master – metric. So an alert for this metric can notify you if there’s a failure.
Other Recommended Metric Alerts :
- Threads Connected – Number of clients currently connected. If none or too high, something is wrong.
- Aborted Connects – Number of failed connection attempts. If this counter is increasing, clients are probably trying and failing to connect to the database.
- Slow Queries – Number of queries that took more than long_query_time seconds to execute. Slow queries produce more memory usage, CPU usage, disk reads. Check slow_query_log for them.
- Max Used Connections – maximum no. of connections MySQL has had open at the same time since the server was last restarted. Helps you decide the max no. of connections your server should support. Helps in traffic analysis.
- Questions – Count of executed statements sent by client. Alert on sudden changes in query volume – drastic drops in throughput, can indicate a serious problem.