Mysqldump max_allowed_packet issue

Gbl

6/28/24, 10:44 AM

I need to migrate a database that was managed by someone who left the company. The source server is MariaDB 1:10.5.5, the destination MariaDB 1:10.5.19.

The existing backup scripts use the following command:

mysqldump --max_allowed_packet=5120M -u root -p my_db > my_dump.sql

Both servers have the following settings In /etc/mysql/conf.d/mysqldump.cnf:

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

In /etc/mysql/mariadb.conf.d/50-server.cnf:

max_allowed_packet=524288000 (-> = 500MB)

I don't know why but there must be a reason it was set like this.

I don't understand why using "--max_allowed_packet=5120M" on the command line.

It doesn't fit with the server settings and the MySQL documentation says that the mysqldump max_allowed_packet value must not exceed the server value and that the protocol has a max value of 1G.

If I run "mysqldump --max_allowed_packet=5120M -u root -p my_db > my_dump.sql" manually,

I get this warning: "Warning: option 'max_allowed_packet': unsigned value 5368709120 adjusted to 2147483648"

How can I dump this database safely? I need to be sure I don't loose any data and that it will import in the new server. Do I have to use "max_allowed_packet"? Maybe with the same value as the server (524288000) ?

Thanks!

1 + 1

mariadb

mysqldump

Rick James

6/28/24, 3:11 PM

Consider upgrading in-place. (No dump + restore) But it would be good to take a backup.

Score:0

Server

symcbean

6/28/24, 11:23 AM

As long as its less then the limit at both ends, and you don't have any very large blobs in your database that would exceed the packet size, you want lose data by using a smaller packet size or a different packet size at each end of the exchange.

+ 2

Gbl

6/28/24, 11:26 AM

Thank you. How to be sure there's no large blob in the DB? Would I get a warning or error during mysqldump if there was any?

symcbean

6/28/24, 11:28 AM

yes, you'll get a warning.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Mysqldump max_allowed_packet issue

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.