Integrating MySQL tools with Systemd Service

Mydbops

Jul 10, 2020

Mins to Read

All

Illustration representing Integrating MySQL tools with Systemd Service

Streamlining MySQL Task Scheduling with Systemd and Percona Tools

‍

Systemd offers a powerful alternative to cron for managing and scheduling MySQL administration tasks. This blog post explores how to leverage systemd services to integrate various Percona Toolkit utilities like pt-heartbeat and pt-kill for optimal MySQL performance and health.

In my day to day tasks as a DB Engineer at Mydbops we have been using multiple MySQL tools for multiple use cases to ensure an optimal performance and availability for servers managed by our Remote DBA Team.

A tool like pt-online-schema can be used for any DDL changes ( Overview to DDL algorithm ), if any tool which needs to scheduled for longer period we tend to use screen or cron.

Some of the problems we face when we demonise the process or use screen for running processes.

The daemon process gets killed when the server reboot happens.
The screen might accidentally terminate while closing it.
To flexibility to start or stop the process when required.

These common problem can be overcome by using systemd service.

‍

What is Systemd?

Systemd is a system and service manager for Linux operating systems. When running as the first process on boot (as PID 1), it acts as an init system that brings up and maintains userspace services.

List of few use cases that can be made simple with systemd.service.

Integrating Pt-heartbeat with Systemd Service
Integrating Auto kill using pt-kill with Systemd Service.
Integrating multi query killer with Systemd service

‍

Integrating Pt-heartbeat with Systemd Service

We had the requirement to schedule pt-heartbeat to monitor replication lag for one of our clients under our database managed Services. Here is problem statement pt-heartbeat process was running as a daemon process, the usual problem we were facing was when the system is rebooted for any maintenance , the pt-heartbeat process gets killed and we start receiving the replication lag alerts and then it needs a manual fix.

‍

Script for pt-heartbeat

 
/usr/bin/pt-heartbeat --daemonize -D mydbops --host=192.168.33.11 --master-server-id 1810 --user=pt-hbt --password=vagrant --table heartbeat --insert-heartbeat-row --update

Now let us integrate it with systemd

 
$ cd /etc/systemd/system/

$ vi pt-heartbeat.service

 
##pt-heartbeat systemd service file
[Unit]
Description="pt-heartbeat"
After=network-online.target syslog.target
Wants=network-online.target

[Install]
WantedBy=multi-user.target

[Service]
Type=forking

ExecStart=/usr/bin/pt-heartbeat --daemonize -D mydbops --host=192.168.33.11 --master-server-id 1810 --user=pt-hbt --password=vagrant --table heartbeat --insert-heartbeat-row --update

StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=pt-heartbeat
Restart=always

ExecStart = It needs the command which needs to be executed when the service kick start )

Restart = Always option specifies to start the process once the OS is booted up.

Once the new systemd script is pushed, Reload the systemctl daemon and start the service

 
$ systemctl daemon-reload

 
$ systemctl start pt-heartbeat

 
$ systemctl status pt-heartbeat -l
● pt-heartbeat.service - "pt-heartbeat"
Loaded: loaded (/etc/systemd/system/pt-heartbeat.service; disabled; vendor preset: enabled)
Active: active (running) since Mon 2020-06-20 13:20:24 IST; 10 days ago
Main PID: 25840 (perl)
Tasks: 1
Memory: 19.8M
CPU: 1h 1min 47.610s
CGroup: /system.slice/pt-heartbeat.service
└─25840 perl /usr/bin/pt-heartbeat --daemonize -D mydbops --host=192.168.33.11 --master-server-id 1810 --user=pt-hbt --password=vagrant --table heartbeat --insert-heartbeat-row --update

This service can be stopped by just giving ( similar to to any systemctl process )

 
$ systemctl stop pt-heartbeat
● pt-heartbeat.service - "pt-heartbeat"
Loaded: loaded (/etc/systemd/system/pt-heartbeat.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Jun 20 15:46:07 ip-192-168-33-11 systemd[1]: Stopping "pt-heartbeat"…
Jun 20 15:46:07 ip-192-168-33-11 systemd[1]: Stopped "pt-heartbeat".

‍

`Integrating Auto kill using pt-kill with Systemd Service`

Usually in production servers long running queries will spike the system resource usage and degrade the MySQL performance drastically or might kill your MySQL process with OOM killer, in order to avoid this hiccups , we can schedule Percona pt-kill process based on the use case defined.

‍

`Scheduling the pt-kill service`

 
$ cd etc/systemd/system/

$ vi pt-kill.service

 
#pt-kill systemd service file

[Unit]
Description="pt-kill"
After=network-online.target syslog.target
Wants=network-online.target

[Install]
WantedBy=multi-user.target

[Service]
Type=forking

ExecStart=/usr/bin/pt-kill --user=mon_ro --host=192.168.33.11 --password=pt@123 --busy-time 200 --kill --match-command Query --match-info (select|SELECT|Select) --match-user cron_ae --interval 10 --print --daemonize

StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=pt-kill
Restart=always

 
$ systemctl daemon-reload

 
$ systemctl start pt-kill

 
$ systemctl status pt-kill
pt-kill.service - "pt-kill"
Loaded: loaded (/etc/systemd/system/pt-kill.service; enabled)
Active: active (running) since Wed 2020-06-24 11:00:17 IST; 5 days ago
CGroup: name=dsystemd:/system/pt-kill.service
├─20974 perl /usr/bin/pt-kill --user=mon_ro --host=192.168.33.11 --password=pt@123 --busy-time 200 --kill --match-command Query --match-info (select|SELECT|Select) --match-user cron_ae --interval 10 --print --daemonize

Now we have configured a fail safe pt-kill process.

‍

`Integrating multi query killer with Systemd service`

Question : Is it possible to integrate multiple Kill Statements for different hosts as single process.

Answer – Yes ! It is possible and quite simple too.

Just add the needed commands as shell script file and make it execute it. In the below example i have chose three different server consider a RDS instance ( more on AWS RDS its Myth ) and a couple of virtual machine.

 
$ vi pt_kill.sh

/usr/bin/pt-kill --user=pt_kill --host=test.ap-northeast-1.rds.amazonaws.com --password=awkS --busy-time 1000 --rds --kill --match-command Query --match-info "(select|SELECT|Select)" --match-user "(mkt_ro|dash)" --interval 10 --print --daemonize >> /home/vagrant/pt_kill_slave1.log

/usr/bin/pt-kill --user=mon_ro --host=192.168.33.11 --password=pt@123 --busy-time 20 --kill --match-command Query --match-info "(select|SELECT|Select)" --match-user "(user_qa|cron_ae)" --interval 10 --print --daemonize >> /home/vagrant/pt_kill_slave2.log

/usr/bin/pt-kill --user=db_ro --host=192.168.33.12 --password=l9a40E --busy-time 200 --kill --match-command Query --match-info "(select|SELECT|Select)" --match-user sbtest_ro --interval 10 --print --daemonize >> /home/vagrant/pt_kill_slave3.log

‍

`Scheduling pt-kill.service for multiple hosts`

 
#pt-kill systemd service file

[Unit]
Description="pt-kill"
After=network-online.target syslog.target
Wants=network-online.target

[Install]
WantedBy=multi-user.target

[Service]
Type=forking

ExecStart=/bin/bash /home/vagrant/pt_kill.sh

StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=pt-kill
Restart=always

Reload the daemon and start the service

 
$ systemctl daemon-reload

 
$ systemctl start pt-kill

 
$ systemctl status pt-kill
pt-kill.service - "pt-kill"
Loaded: loaded (/etc/systemd/system/pt-kill.service; enabled)
Active: active (running) since Wed 2020-06-24 11:00:17 IST; 5 days ago
CGroup: name=dsystemd:/system/pt-kill.service
├─20974 Perl  /usr/bin/pt-kill --user=pt_kill --host=test.ap-northeast-1.rds.amazonaws.com --password=awkS --busy-time 1000 --rds --kill --match-command Query --match-info "(select... 
├─21082 perl  /usr/bin/pt-kill --user=mon_ro --host=192.168.33.11 --password=pt@123 --busy-time 20 --kill --match-command Query --match-info "(select...
├─21130 perl /usr/bin/pt-kill --user=db_ro --host=192.168.33.12 --password=l9a40E --busy-time 200 --kill --match-command Query --match-info "(select...

This makes Systemd more useful and easy tool for scheduling mysql tools in database environment. There are many more features in Systemd that be used for scheduling scripts bypassing the use of crontab, hopefully.

Note : All these are sample scripts you ensure you test well before making it in production.

Mydbops offers a comprehensive suite of open-source database management services specifically designed for MySQL.

{{cta}}

‍

`FAQs`

`What is systemd and how is it used with MySQL?`

systemd is the default init system on most modern Linux distributions. It manages services during boot and runtime. When integrated with MySQL tools, systemd can automate database start, stop, restart, and health-check operations, improving service reliability.

‍

`Why integrate MySQL tools with systemd?`

‍It enables automatic service management, restarts on failure, logging via journald, and better operational control of MySQL tools.

‍

`How can I check MySQL logs via systemd?`

‍Use journalctl -u mysqld to view logs, or add -f to follow them in real-time.

‍

`Can I schedule backups with systemd?`

‍Yes, you can use systemd timers to run mysqldump or other scripts at defined intervals.

‍

Contact us today for a Personalised MySQL consultation!

MySQL

database management

Mydbops

opensource

Percona

systemd

toolkit

pt-heartbeat

pt-kill

No items found.

Integrating MySQL tools with Systemd Service

Streamlining MySQL Task Scheduling with Systemd and Percona Tools

What is Systemd?

Integrating Pt-heartbeat with Systemd Service

Script for pt-heartbeat

`Integrating Auto kill using pt-kill with Systemd Service`

`Scheduling the pt-kill service`

`Integrating multi query killer with Systemd service`

`Scheduling pt-kill.service for multiple hosts`

`FAQs`

`What is systemd and how is it used with MySQL?`

`Why integrate MySQL tools with systemd?`

`How can I check MySQL logs via systemd?`

`Can I schedule backups with systemd?`

`About the Author`

`Topic covered`

`Continue Reading`

Beyond the Scaling Wall: Key Insights from Our Distributed SQL Meetup with TiDB and AWS

Beyond the Scaling Wall: Key Insights from Our Distributed SQL Meetup with TiDB and AWS

Webinar Recap: MySQL 8.4 Audit Log Filter | Mydbops

Webinar Recap: MySQL 8.4 Audit Log Filter | Mydbops

Best Practices for Deploying a MySQL Cluster InnoDB in Production

Best Practices for Deploying a MySQL Cluster InnoDB in Production

AWS MySQL Performance Comparison: RDS vs. Aurora vs. Aurora Serverless v2

AWS MySQL Performance Comparison: RDS vs. Aurora vs. Aurora Serverless v2

`Subscribe Now!`