Unable to backup database in backup manager or export database in plesk admin

[asdasdasd1]

When i try to export a database dump in plesk admin i receive below errors:
Note: I have included max_allowed_packet = 1G in /etc/my.cnf under [mysqld] and [mysqldump] I am able to import and export in phpmyadmin but i want to use backup manager to automate backups.
When using backup manager all the files a transferred except the database, i get same packet error. My database size is around 250mb, and i have restarted server after updating /etc/my.cnf

Unable to export a dump of wordpress_x:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `wpu2_postmeta` at row: 28081
Traceback (most recent call last):
File "/usr/local/psa/admin/sbin/dbbackup", line 6, in <module>
dbbackup.main()
File "/usr/local/psa/lib/modules/python/dbbackup/dbbackup.py", line 97, in main
backup(options, password)
File "/usr/local/psa/lib/modules/python/dbbackup/dbbackup.py", line 68, in backup
raise Exception("program 'mysqldump' finished with non-zero exit code: %d", p1.returncode)
Exception: ("program 'mysqldump' finished with non-zero exit code: %d", 3)

 

[asdasdasd1]

It is sending the backup to remote ftp server. When i change the destination for the backup from the remote ftp to local server it does not get the packet error however i get security and read/write errors now on 'cache' folders within wordpress ??

 

[Bitpalast]

Setting the variable in [mysql] section is insufficient. It needs to be set in a [mysqldump] section as well.
Please see this article on how to properly configure max_allowed_packet:
How to set the value of max_allowed_packet or wait_timeout for MySQL on a Plesk server?

 

[asdasdasd1]

Hi Peter, i have already added this section in my.cnf file, i have the command twice one under [mysqld] and once again under [mysqldump] however i still cannot get it to work

 

[Bitpalast]

Did you restart the database service after the change?

 

[asdasdasd1]

I had restarted the server, would that do the same thing? I am new to linux so i do not know the commands. I tried a few different commands and couldnt get any of the restart mysql service to work so i just restarted the server from the pleks panel. Also i can backup to local machine but not remote ftp. I still cannot export sql dump from plesk panel, but i can within phpmyadmin

Plesk wants to put the file on the server inside a wordpress folder, do i need to adjust the packet size for say apache web server or something similiar?

 

[Bitpalast]

There should not be any adjustments necessary. Plesk is using mysqldump, so if mysqldump can dump the database, things should be o.k. Can you post the content of /etc/my.cnf here?

 

[asdasdasd1]

[mysqldump]
max_allowed_packet = 1G
net_buffer_length = 100K
[mysqld]
#bind-address = ::ffff:127.0.0.1
bind-address = 127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
wait_timeout = 900
max_allowed_packet = 1G
net_buffer_length = 100K
# 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 Fedora Quick Docs | Fedora Quick Docs | Understanding and administering Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

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

 

[Bitpalast]

It all seems to be correct. Although 1 G should be plenty, maybe it is still too low. Have you tried to set this to 10 G for example?

 

[asdasdasd1]

My database is only 270mb but i can try 10GB. Do you have any idea why remote ftp backup fails (this seemed to happen when the total website backup exceeded 2GB), but it works when backing up to local server? In php.ini settings set from inside of plesk i have set post size to 4gb, is there another php.ini that i would need to adjust, is there a php.ini for the database server too? I will update to 10GB now

 

[Bitpalast]

It does not make sense to me. Maybe @Mark Muyskens has an idea, else this sounds like a case for official Plesk support. If your db is only 270 MB, surely 1 GB packet size must be enough. Maybe the error message is misleading and the real error is something completely different.

 

[asdasdasd1]

10GB did not work either.

So just a recap:

Can use backup manager within plesk to do a complete backup to local server
Cannot use backup manager within plesk to do a complete backup to remote ftp (max_allowed_packet error)
Cannot use plesk to export a dump of mysql database
I am able to export dump from myphpadmin

EDIT:
After increasing post size in php.ini and my.cnf max_allowed_packet to 10G i can now backup to remote FTP. The only issue remaining is downloading mysql dump from within plesk

 

[Bitpalast]

Just one stupid idea: Maybe you have a second database server running? It is possible to use multiple database servers with Plesk, so maybe the directives for max_allowed_packets need to go into another file? Have you tested the setting by runnin
# plesk db
and then SQL commands like
show variables LIKE 'max_allowed_packet';

 

[MarkM]

It all seems to be correct. Although 1 G should be plenty, maybe it is still too low. Have you tried to set this to 10 G for example?

max_allowed_packet maxes out at 1GB I believe.

What's your wait_timeout set as?

# plesk db;
mysql> show variables like 'wait_timeout';

 

[MarkM]

Another thing, have you tried triggering backup manager via CLI to remote ftp;

pleskbackup: Backing Up Content and Configuration

Curious if this would complete.