Resolved Plesk backup can not Dump databases MariaDB 10.6.7

[Peter Debik]

For the mysqldump section it would be simply

skip_name_resolve

if this ought to be done or omitting the line if it ought not to be done. In general, it is best to not to do it. For the bind-address line: This variable does not exist for the mysqldump section. I should have expressed all this previously.

The thing is: Your error message is quite clear, and it is quite likely that there are wrong configuration lines in the database configuration files. Maybe not in the /etc/my.cnf file, but then in some included file instead. Can you positive rule out that there are such configurations?

With no extra configuration files in /ec/my.cnf.d a very ordinary my.cnf file COULD look like this (tested for MariaDB 10.6). This would be all you need:

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

[mysqld]
bind-address = ::
skip_name_resolve
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqldump]

[mysqld_safe]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

What is in your file?

 

[KCTT]

Hi Peter, thanks again for taking the time to help out. I removed the part with [mysqldump] since no suggestion was working :/ Here's my most recent /etc/mysql/my.cnf file:

# Lot of comments
# [...]

[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mysqld]
bind-address = ::
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
local-infile=0
skip-name-resolve = off
skip_name_resolve

As you can see the bind-address and skip_name_resolve directives are at the end, after everything else was included. Additionally I verified via

grep -nrw -e "bind-address" /etc/mysql

and

grep -nrw -e "skip-name-resolve" /etc/mysql

that there is no other bind-address or skip-name-resolve/skip_name_resolve setting in any other file inside of /etc/mysql. And still the same error:

Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 2: WARNING: Forcing protocol to TCP due to option specification. Please explicitly state intended protocol. mysqldump: Got error: 2002: "Can't connect to server on 'localhost' (115)" when trying to connect

Here's the user table:



Do you have more suggestions I could try?Not having a backup is really bothering me so I'm open to everything.

Kind regards
Toby

 

[Peter Debik]

Why are there two include directories and what is their content?
Why are there to contradictory directives on "skip name resolve"? One turning it off, the other turning it on?
Did you only search "grep -nrw -e "skip-name-resolve" /etc/mysql" or did you also search "grep -R "skip_name_resolve""?

 

[Peter Debik]

@KCTT I played around a lot with Alma Linux, MariaDB 10.11 and several configurations. I learned that in the latest MariaDB versions we should completely omit the bind-address statement if you want to allow any hosts to connect. Also omit the skip-name-resolve line and skip_name_resolve. So actually nothing of this must be present. I was able to reproduce the errors that you described, but also was able to solve them by completely removing the bind-address line and the skip name resolve line(s) from the my.cnf configuration file. Don't forget to restart MariaDB afterwards.

Can you confirm that this works for you, too?

 

[Quinten]

@KCTT I played around a lot with Alma Linux, MariaDB 10.11 and several configurations. I learned that in the latest MariaDB versions we should completely omit the bind-address statement if you want to allow any hosts to connect. Also omit the skip-name-resolve line and skip_name_resolve. So actually nothing of this must be present. I was able to reproduce the errors that you described, but also was able to solve them by completely removing the bind-address line and the skip name resolve line(s) from the my.cnf configuration file. Don't forget to restart MariaDB afterwards.

Can you confirm that this works for you, too?

Hi Peter,

I can confirm this works. I was having the same problem and removing "bind-address = ::" from the [mysqld] and [mysqldump] section fixed it.

Thank you so much!

 

[manos]

Thanks to you both. But I still could not resolve the problem. Setting something like that

[mysqldump]
skip-name-resolve = 0

or

[mysqldump]
bind-address = 127.0.0.1
skip-name-resolve = 0

or

[mysqldump]
bind-address = ::
skip-name-resolve = 0

in e.g. /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf gives me errors like this:

Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 7: mysqldump: unknown variable 'skip-name-resolve=0'

Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 7: mysqldump: unknown variable 'bind-address=0'

remove [mysqldump} section

at {mysqld}
bind-address = 127.0.0.1 # or ::

comment skip-name-resolve with #

this config work at 10 plesk servers with mariadb 10.6

 

[mow]

@KCTT I played around a lot with Alma Linux, MariaDB 10.11 and several configurations. I learned that in the latest MariaDB versions we should completely omit the bind-address statement if you want to allow any hosts to connect.

Doesn't that allow every host to connect?
(And what happened to using the socket /var/run/mysqld/mysqld.sock only, not using TCP/IP connections at all?)

 

[Peter Debik]

Yes, that allows every host to connect. Since MariaDB 10.6 (according to their docs) you can either limit connections to one IP or omit the line to allow all. Please provide a more sophisticated solution if you know of it.

the other question on the mysqld.sock: I do not know.

 

[learning_curve]

Doesn't that allow every host to connect?
(And what happened to using the socket /var/run/mysqld/mysqld.sock only, not using TCP/IP connections at all?)

We're NOT using Alma Linux, we do NOT allow remote connections and we don't have any issues at all with MariaDB currently... but...
FWIW, In addition to your observation about every host being able to connect (if no other limitations are applied...) & what @Peter Debik already mentioned re skip-name-resolve etc etc This is covered in detail in the MariaDB page for configuring remote access:
Configuring MariaDB for Remote Client Access
The other comment about socket /var/run/mysqld/mysqld.sock etc is also referred to briefly c/w a test, on a different MariaDB page:
Troubleshooting Connection Issues
Finally, not absolutely sure, but we're thinking that the (now old) methodology of: IF connected to "localhost", then the socket connector is used, but, IF connected to "127.0.0.1" the TCP/IP connector is used, probably still applies. This will need double checking & verifying, but that's how it used to be

 

[igorneumann]

I got the same problem when using the built-in mariadb updater on Ubuntu 18

the config file is located at /etc/mysql/mariadb.conf.d/50-server.cnf
I commenteed the line bind-address = 127.0.0.1

Tried to backup and this fixed the problem... just adding my 2cents in case anyone else came with the same problem.