Mariadb - general questions

Hello.

1°)
Current mariadb install a mysql.service script which does not contain any command to stop the mysql server.
How the command

systemctl stop mysql.service

find a way to stop the mysql server ( what commands are issued to stop the server ).

2°)
In the old days I did not use the helper ( /usr/lib/mysql/mysql-systemd-helper ) which is installed for Mariadb. May I continue whithout it ?

Any help is welcome.

See “man systemd.service”:

       ExecStop=
           Commands to execute to stop the service started via ExecStart=.
           This argument takes multiple command lines, following the same
           scheme as described for ExecStart= above. Use of this setting is
           optional. After the commands configured in this option are run,
           all processes remaining for a service are terminated according to
           the KillMode= setting (see systemd.kill(5)). If this option is
           not specified, the process is terminated by sending the signal
           specified in KillSignal= when service stop is requested.
           Specifier and environment variable substitution is supported
           (including $MAINPID, see above).

2°)
In the old days I did not use the helper ( /usr/lib/mysql/mysql-systemd-helper ) which is installed for Mariadb. May I continue whithout it ?

Sure.
There’s no need to use the helper manually.
You should only use systemctl to start/stop mariadb (which then calls this helper with the corresponding options).

Any help is welcome.

Help with what? :wink:

I mean not use it at all.
Because the helper seems to not follow usage :

If you want to use mysqld_multi uncomment 1 or more mysqld sections

below or add your own ones.

WARNING

--------

If you uncomment mysqld1 than make absolutely sure, that database mysql,

configured above, is not started. This may result in corrupted data!

[mysqld1]

port = 3306

datadir = /var/lib/mysql

pid-file = /var/lib/mysql/mysqld.pid

socket = /var/lib/mysql/mysql.sock

user = mysql

[mysqld2]

port = 3307

datadir = /var/lib/mysql-databases/mysqld2

pid-file = /var/lib/mysql-databases/mysqld2/mysql.pid

socket = /var/lib/mysql-databases/mysqld2/mysql.sock

user = mysql



**[mysqld6]
port = 63336
datadir = /var/lib/mysql-databases/mysqld6
pid-file = /var/lib/mysql-databases/mysqld6/mysql.pid
socket = /var/lib/mysql-databases/mysqld6/mysql.sock
user = mysql
**
!includedir /etc/my.cnf.d

When using mysql@.service ( ex 6 for instance mysql@6 ) the helper search for

config="/etc/my${INSTANCE}.cnf"

and install the database in

/var/lib/mysql-${INSTANCE}

Here screen log

mars 09 12:53:43 linux-dpgm.suse systemd[1]: Starting MySQL server - 6 instance...
mars 09 12:53:43 linux-dpgm.suse mysql-systemd-helper[10703]: Creating MySQL privilege database...
mars 09 12:53:43 linux-dpgm.suse mysql-systemd-helper[10703]: WARNING: The host 'linux-dpgm.suse' could not be looked up with /usr/bin/resolveip.
mars 09 12:53:43 linux-dpgm.suse mysql-systemd-helper[10703]: This probably means that your libc libraries are not 100 % compatible
mars 09 12:53:43 linux-dpgm.suse mysql-systemd-helper[10703]: with this binary MariaDB version. The MariaDB daemon, mysqld, should work
mars 09 12:53:43 linux-dpgm.suse mysql-systemd-helper[10703]: normally with the exception that host name resolving will not work.
mars 09 12:53:43 linux-dpgm.suse mysql-systemd-helper[10703]: This means that you should use IP addresses instead of hostnames
mars 09 12:53:43 linux-dpgm.suse mysql-systemd-helper[10703]: when specifying MariaDB privileges !
mars 09 12:53:43 linux-dpgm.suse mysql-systemd-helper[10703]: **Installing MariaDB/MySQL system tables in '/var/lib/mysql-6' ...**
mars 09 12:53:43 linux-dpgm.suse mysql-systemd-helper[10703]: 180309 12:53:43 [Note] /usr/sbin/mysqld (mysqld 10.0.33-MariaDB) starting as process 10747 ...
mars 09 12:53:46 linux-dpgm.suse mysql-systemd-helper[10703]: OK
mars 09 12:53:46 linux-dpgm.suse mysql-systemd-helper[10703]: Filling help tables...
mars 09 12:53:46 linux-dpgm.suse mysql-systemd-helper[10703]: 180309 12:53:46 [Note] /usr/sbin/mysqld (mysqld 10.0.33-MariaDB) starting as process 10774 ...
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: OK
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: To do so, start the server, then issue the following commands:
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: '/usr/bin/mysqladmin' -u root password 'new-password'
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: '/usr/bin/mysqladmin' -u root -h linux-dpgm.suse password 'new-password'
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: Alternatively you can run:
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: '/usr/bin/mysql_secure_installation'
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: which will also give you the option of removing the test
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: databases and anonymous user created by default.  This is
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: strongly recommended for production servers.
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: See the MariaDB Knowledgebase at http://mariadb.com/kb or the
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: MySQL manual for more instructions.
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: You can start the MariaDB daemon with:
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: rcmysql start
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: You can test the MariaDB daemon with mariadb-test package
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: Please report any problems at http://mariadb.org/jira
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: The latest information about MariaDB is available at http://mariadb.org/.
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: You can find additional information about the MySQL part at:
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: http://dev.mysql.com
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: Consider joining MariaDB's strong and vibrant community:
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10703]: https://mariadb.org/get-involved/
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10814]: Waiting for MySQL to start
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10813]: **Could not open required defaults file: /etc/my6.cnf**
mars 09 12:53:49 linux-dpgm.suse mysql-systemd-helper[10813]: Fatal error in defaults handling. Program aborted
mars 09 12:53:49 linux-dpgm.suse systemd[1]: mysql@6.service: Main process exited, code=exited, status=1/FAILURE

Any comment is welcome.

Well, why don’t you just create /etc/my6.cnf then with your custom settings for that instance?
AFAICS, the helper would respect the datadir set in /etc/my${INSTANCE}.cnf …

That would also make it unnecessary to modify (or overwrite) the my.cnf shipped by the distribution…

What are you asking for?
Do you want a way to give someone the ability to start a service but not stop it?

If the above,
Then the first thing I’d consider is to modify the default Unit file for the service by

  • Copying to /etc/systemd/<something>
  • Renaming the copied Unit file so that you create a new Unit file without disabling the default
  • Remove any commands related to stopping the service

I don’t understand the logic or purpose behind modifying the permissions of my.cnf…
Permissions can only restrict whether the database app or a User can read or edit the file…

TSU

Not at all.
It is a question about mariadb, relative to the mysql.service file shipped with the original package.
This file (mysql.service) has not ExecStop instruction.
THat is why I have posted a question.

In this thread, I have not asked any things relative to that ?

Because it is cited as example in many internet place, in my.cnf, and there from mariadb.org

       # This file should probably be in your home dir (~/.my.cnf)
       # or /etc/my.cnf
       # Version 2.1 by Jani Tolonen
       [mysqld_multi]
       mysqld     = /usr/local/bin/mysqld_safe
       mysqladmin = /usr/local/bin/mysqladmin
       user       = multi_admin
       password   = multipass
       [mysqld2]
       socket     = /tmp/mysql.sock2
       port       = 3307
       pid-file   = /usr/local/mysql/var2/hostname.pid2
       datadir    = /usr/local/mysql/var2
       language   = /usr/local/share/mysql/english
       user       = john



from : mysqld_multi - MariaDB Knowledge Base

Ok I am going to try your suggestion.

I have modified ‘/usr/lib/mysql/mysql-systemd-helper’ by adding a function (print_config) to print all parameters that will be used when starting a server.
Here the output of my modified ‘/usr/lib/mysql/mysql-systemd-helper’

user_install@linux-dpgm:~> /usr/lib/mysql/mysql-systemd-helper  print_config 4
++ echo 10.0.33
++ sed 's|\.[0-9]\+$||'
+ MYSQLVER=10.0
+ mysql_daemon_user=mysql
+ mysql_daemon_group=mysql
+  -z 4 ]]
+ datadir=/var/lib/mysql-4
+ socket=/var/run/mysql/mysql.4.sock
+  -n 4 ]]
++ /usr/bin/my_print_defaults mysqld mysqld_multi 4
+ opts=
+ tmp_opts=
+ config=/etc/my4.cnf
+ '' -d /var/lib/mysql-4/lost+found ']'
+ ignore_db_dir=
+ mkdir -p /var/run/mysql
+ chmod 755 /var/run/mysql
chmod: changing permissions of '/var/run/mysql': Operation not permitted
+ chown --no-dereference mysql:mysql /var/run/mysql
chown: changing ownership of '/var/run/mysql': Operation not permitted
+ case "$1" in
+ my_print_config
+  1 -eq 0 ]]
+ echo

+ echo 'MYSQLVER             : 10.0'
MYSQLVER             : 10.0
+ echo 'mysql_daemon_user    : mysql'
mysql_daemon_user    : mysql
+ echo 'mysql_daemon_group   : mysql'
mysql_daemon_group   : mysql
+ echo 'datadir              : /var/lib/mysql-4'
datadir              : /var/lib/mysql-4
+ echo 'socket               : /var/run/mysql/mysql.4.sock'
socket               : /var/run/mysql/mysql.4.sock
+ echo 'INSTANCE             : 4'
INSTANCE             : 4
+ echo 'opts                 : '
opts                 : 
+ echo 'tmp_opts             : '
tmp_opts             : 
+ echo 'config               : /etc/my4.cnf'
config               : /etc/my4.cnf
+ echo 'ignore_db_dir        : '
ignore_db_dir        : 
+ echo 'basedir_2            : '
basedir_2            : 
+ echo 'socket_2             : /var/run/mysql/mysql.4.sock'
socket_2             : /var/run/mysql/mysql.4.sock
+ echo 'datadir_2            : /var/lib/mysql-4'
datadir_2            : /var/lib/mysql-4
+ echo 'mysql_daemon_user_2  : mysql'
mysql_daemon_user_2  : mysql
+ echo

user_install@linux-dpgm:~> 

That show that the helper does not read ‘/etc/my${INSTANCE}.cnf’

here is ‘/etc/my${INSTANCE}.cnf’ for i=4

#################################################
#                                                                #
#    {config_jcd}                                            #
#                                                                #
#    /etc/my4.cnf
#                                                                #
#    ¤2018_03_09¤                                            #
#                                                                #
#    ¨version:00-0-0                                        #
#                                                                #
#################################################
#
# MYSQL MARIADB CONFIG FILE
#
########################################################
#

[mysqld4]
#bind-address = 0.0.0.0
port       = 63334
datadir    = /bdd/bd_4/bareos
pid-file   = /bdd/bd_4/bareos/sock/mysqld.pid
socket     = /bdd/bd_4/bareos/sock/mysqld.sock
user       = bareos
#user       = mysql
general_log       = 1
general_log_file    = /var/log/mysql/bareos/bareos.log
log-error             = /var/log/mysql/bareos/bareos_error.log
log-warnings   = 0
symbolic-links    = 0

Any help is welcome.

My early research into what you seem to be trying to do turns up the following article which is supposed to replace what you are doing

https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html

From what you posted earlier, I had gotten the impression that you wanted to try to run MySQL server(s) associated with different system Users (maybe that was your idea for running multiple MySQL server instances?).
If that was your purpose, the above reference shows you how to set up multiple MySQL server instances but as you can see nowhere are Users mentioned… And, that is because as I described in your other related Forum thread each of the following is completely separate and needs to be configured on its own unrelated to the other two

  • The security context of the running MySQL server application (typically system)
  • Authorized MySQL Database Users. Initially the MySQL Administrator account is set up for administering and accessing how the entire MySQL application runs internally, and for accessing every database. “Normal” MySQL Users for accessing whatever databases they’re granted access(typically a single database). These are the accounts you use to configure authenticated connections.
  • System Users. Never granted access as a MySQL User directly. For ease of use, you <can> duplicate the User Accounts and their credentials as MySQL Users (both root/admin and ordinary Users) but that’s your option and would still need to be set up accordingly. These accounts are <never> used for authenticated connections.

(Almost) Lastly,
The above documentation describes configuring ExecStart as a configuration example.
If you really want to disable the ability to stop (IMO an odd requirement since there are many ways someone could stop something including killing the process), I imagine that you can write an ExecStop statement that redirects the command to do something benign… or, something similar(Best recommendation should be in systemd documentation somewhere).

Again,
I still do not really understand why any of this should be related to some kind of User context, if I’m simply missing your reason, maybe post another explanation and I’ll try to understand.

HTH,
TSU

After looking at the helper script in more detail, it seems that you (also) have to specify the datadir, socket and user options in /etc/my.cnf, but under the $INSTANCE] section.
I.e. in your case:

[4]
datadir    = /bdd/bd_4/bareos
socket     = /bdd/bd_4/bareos/sock/mysqld.sock
user       = bareos


The rest should be fine in /etc/my4.cnf, as it does pass the option --defaults-file=/etc/my4.cnf to mysql.

At least that’s how I interprete the “code” in the script.
I don’t know whether it is intended that way or a bug, but it does seem a bit strange to me… :sarcastic:

Thank you for helping, but my first question was :
If there is no ExecStop in a service file, how systemd knows what to do to stop a service.
Reading myself through internet I did not see the answer given by wolfi.

My second question was about the helper that I did not use up to now.
In the old days, my own mysqld@.service started directly my different instances.
I asked this second question because with the helper ( even when following the Wolfi’s recommendations ) I could not start any instances.

I never speak about any some kind of User context.

Thanks for taking times to help me.

Just to be clear: you should of course still be able to do that (in the same way).

The helper does some useful things though, like upgrading the database if necessary.
And IIRC, that helper existed (and was used by the default service files) in the past as well.

But if you lived without it before, I see no problem to continue doing that, if you find it easier.

Sorry regarding the User references… I was mixing in references from your other Forum thread.

Regarding stopping the service…
If you read the MySQL documentation I referenced, you will find that stopping the service is described in a very general “systemd now does this so the mysql app doesn’t have to deal with that anymore.” So, that’s that. On a systemd system you won’t find what you’re looking for, and the functionality may even have been removed (see the comments about mysqld.multi, mysqld.multi-server and mysqld.safe… Even if they might exist on your system, if you configure their usage you’ll likely screw up what systemd is supposed to do for you automatically).

Without reading deeper,
I assume that what you’re looking at is typical of all database applications… Unlike ordinary applications, the ExecStop command should be handled differently because of the possibility of long running transactions, which if interrupted can cause data corruption. This is why for instance in some cases an arbitrary amount of time is inserted before the application is actually killed to hopefully allow for all transactions to complete. And, this is likely behind the warnings you see in your output about killing

Bottom line for what you are doing…
I believe the documentation you’re following to implement mysqld.multi is in error and that is explained in the documentation I provided you that describes what you should be doing on a systemd system.

You should update your my.cnf accordingly.

TSU

AIUI, he isn’t looking for anything in that regard, he just wanted to know how systemd will quit it if there is no ExecStop.

And the answer is (as quoted earlier):
systemd just sends a general process signal then. (may be specified by KillSignal=, defaults to SIGTERM)

Unlike ordinary applications, the ExecStop command should be handled differently because of the possibility of long running transactions, which if interrupted can cause data corruption.

Not necessarily.
The daemon just can handle the corresponding signal(s) too, which is safer anyway because any other process (with the appropriate privileges) could send a signal as well, or a user (via sudo or logged in as root) could.

If the frontend application exists on the same machine, in theory what you describe might be possible but what if the frontend is on a different machine? There is no way for the backend (database application) to know when a long running transaction has completed or not because the transaction logic might exist in middleware or frontend beyond the system management the database app can see.

For your typical application, an ExecStop can be executed immediately or at least ASAP with little consequence.
That’s not necessarily the case with applications like database apps.

TSU

What are you talking about?
That doesn’t make sense, nor does it seem to be related at all.

This is about starting/stopping the daemon, i.e. mysqld.
A “systemctl stop” would not affect your “database application” (the frontend/client) at all.

And in any case, there have to be other mechanisms to ensure data integration and atomicity. Just “disabling” or overriding a “systemctl stop” is not a proper solution for that anyway.