Systemd autoinit proxysql service

[epenia]

Hello i am Endika from spain and i like your software proxysql but, I did not find autoload configuration by proxysql.cnf config file to the initialize the service.
I would like to auto initialize service in systemd because not support this.

Your proxy's software is very interesting and if you need help to correct errors or you help to new implementations I help you.

[renecannao]

Hi Endika.
At today, ProxySQL has support for init but not for systemd .
Would you be interested in providing a pull request to support systemd ?
Thank you very much for your help!

[epenia]

Hello Rene.
I have translated the init to be compatible with systemd and script work, how I can send?

[epenia]

Hello René
It is my systemd init script.

code:
[Unit]
Description=Deamon of proxy SQL
After=syslog.target

[Service]
Type=forking
ExecStart=/etc/init.d/proxysql start
ExecStop=/etc/init.d/proxysql stop
ExecReload=/etc/init.d/proxysql restart
PIDFile=/etc/proxysql/proxysql.pid

[Install]
WantedBy=multi-user.target

[epenia]

Install systemd script into next path: /etc/systemd/system/proxysql.service

Enable autoload service at start system with: systemctl enable proxysql

Start or stop proxysql service with next intruction: systemctl start proxysql or systemctl stop proxysql

[vermeire-mathias]

Hi,

I working on centos7 and I have the same problem.
On centos7 you can use both systemd and init scripts.
If you try to add proxysql to chkconfig it gives a error:

[root@proxysql2 ~]# chkconfig --add proxysql
service proxysql does not support chkconfig

The problem is the LSB stanza, see below. (also see man page chkconfig)
As for the fix Sxwark provided, I don't think its the correct way to implement systemd services.
If you want I can spend some time creating a better version.

not working:
# Provides : proxysql
# Required-Start : $local_fs
# Required-Stop  : $local_fs
# Default-Start     : 2 3 4 5
# Default-Stop      : 0 1 6
# Short-Description : High Performance Advanced Proxy for MySQL
# Description : High Performance and Advanced Proxy for MySQL and forks.
#               It provides advanced features like connection pool, query routing and rewrite,
#               firewalling, throttling, real time analysis, error-free failover


working:
### BEGIN INIT INFO
# Provides: proxysql
# Required-Start: $local_fs
# Required-Stop: $local_fs
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: High Performance Advanced Proxy for MySQL
# Description: High Performance and Advanced Proxy for MySQL and forks.
#               It provides advanced features like connection pool, query routing and rewrite,
#               firewalling, throttling, real time analysis, error-free failover
### END INIT INFO

[renecannao]

Hi,
thank you for your interest and contribution.
Pull requests are more than welcome, so please feel free to create a pull request and I will be happy to merge it.
If not, I can take this sample and add it myself.
Thank you!

[vermeire-mathias]

Hi,

I created a pull request (#471)
Do you need extra hand to make a systemd service or is it covered by someone?

Kr
Mathias

[renecannao]

Merged also into v1.1.1

@vermeire-mathias : I haven't try the systemd script yet

[dveeden]

A proper SystemD unit file would be nice. I don't think a unit file which just calls the rc scripts is the proper way..

[dveeden]

With MySQL the mysqld_safe was removed to allow systemd to handle things properly. Also the 'angel' process is no longer needed with systemd.

Looks like proxysql does a similar thing:

root      4784     1  0 20:31 ?        00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
root      4785  4784  0 20:31 ?        00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql

[renecannao]

@dveeden : any suggestion on SystemD unit file? I don't have experience with it.

With regards to the angel process, I do not plan to remove the functionality, for not SystemD systems or for other standalone systems (Docker?).

Thanks

[dveeden]

My take on proxysql.service:

[Unit]
Description=High Performance Advanced Proxy for MySQL
After=network.target

[Service]
LimitNOFILE=102400
LimitCORE=1073741824
ExecStart=/usr/local/bin/proxysql -f
Restart=always

[Install]
WantedBy=default.target

I've put this in /etc/systemd/system and ran

systemctl daemon-reload
systemctl enable proxysql
systemctl start proxysql

There is no angel process. There is just one proxysql process. If I kill the proxysql process (to simulate a crash) it is restarted by systemd.

Because of the -f logging is sent to stdout and captured by systemd.
To view the logging: journalctl /usr/local/bin/proxysql

The 'initial' option of the System V init script is not present in this systemd setup.

The RPM should place the unit file in /usr/lib/systemd/system

[renecannao]

Pull request merged: thanks!
Moving this issue to milestone v1.2.3 so to remind myself to update the RPM packages too.

[Reiner030]

Hello,

using ProxySQL package from Percona repo (latest version still 1.3.9 😪 ) with Debian Jessie has the problem, that the good mentioned systemd file is not correct setup which ends in not started service after server/instance reboot... . 😟
Write it here because it fit's discussion and @renecannao seems also mantainer of the package.

# systemctl enable proxysql
Synchronizing state of proxysql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable proxysql
The unit files have no installation config (WantedBy, RequiredBy, Also, Alias
settings in the [Install] section, and DefaultInstance for template units).
This means they are not meant to be enabled using systemctl.
Possible reasons for having this kind of units are:
1) A unit may be statically enabled by being symlinked from another unit's
   .wants/ or .requires/ directory.
2) A unit's purpose may be to act as a helper for some other unit which has
   a requirement dependency on it.
3) A unit may be started when needed via activation (socket, path, timer,
   D-Bus, udev, scripted systemctl call, ...).
4) In case of template units, the unit is meant to be enabled with some
   instance name specified.

[Reiner030]

Found my above systemd file in https://github.com/percona/proxysql-packaging/blob/master/debian/proxysql.service implemented. Sadly there is no issue tracker in this repo so here a ping to @EvgeniyPatlan for this problem. 😄

[EvgeniyPatlan]

Hi @Reiner030.
Thanks a lot for your feedback. I have improved unitfile in order to fix this issue

[klatiss]

I see that the systemd unit file above is not in the CentOS 7 package. Can it be added please?
Additionally, arbitrary environment settings should not be hard coded in the unit file but can be set by the user as required, eg

# Systemd service file for ProxySQL

[Unit]
Description=High Performance Advanced Proxy for MySQL
After=network.target syslog.target

[Service]
ExecStart=/usr/bin/proxysql -f -c /etc/proxysql.cnf
Restart=always

[Install]
WantedBy=multi-user.target

Then a user can create an override file in /etc/systemd/system/proxysql.service.d/ with the desired settings, eg

$ cat /etc/systemd/system/proxysql.service.d/file_limit.conf 
[Service]
LimitNOFILE=102400

[klatiss]

Any comment on this request @renecannao ? Or should I open a separate ticket?

[Reiner030]

Hello @EvgeniyPatlan,

thanks...

• systemd file is now working fine with 1.4.4.
  (so we have the topic matched of this issue thread :) )
• Logrotate is working also fine now
  But it's important that in /etc/proxysql-admin.cnf the hostname must not be changed from localhost to the hostname (in my case minion_id of saltstack) - else it would fail like this test call:

   root@yz-euc1-03:~#  /usr/bin/printf "%s\n" "[client]" "user=${PROXYSQL_USERNAME}"  "password=${PROXYSQL_PASSWORD}"  "host=${PROXYSQL_HOSTNAME}" "port=${PROXYSQL_PORT}"   | /usr/bin/mysql --defaults-file=/dev/stdin --protocol=tcp -Nse "PROXYSQL FLUSH LOGS"
   ERROR 1040 (42000): User 'admin' can only connect locally
  

---

One "big" problem I just found out:
One day before Percona release of 1.4.4 was published already here Proxysql relase 1.4.5 came out.
And from https://github.com/sysown/proxysql/releases/tag/v1.4.5 is written:

Upgrade from 1.4.4 is strongly recommended!

It would be great if this can be more automated.
Or are there further / enhanced QA runs done at Percona before offically released ?
Would be great if it can be published near-tearm as here (but only as soon as Percona can endorse it.

(And while testing Debian Stretch setups: package percona-monitoring-plugins is not available in Stretch repository.)