mysqldump Lost connection to MySQL server during query when dumping table

When taking backup of a MySQL database, i got error

root@server1:~# mysqldump --opt serverok_wp > serverok_wp.sql
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `wp_options` at row: 60831
root@server1:~#

This error happens when connection between your MySQL client and server is timing out during the mysqldump process.

To fix, increase the value of max_allowed_packet in both MySQL server and client configuration files. This parameter controls the maximum size of a single packet. The default value is 16M, but you can increase it up to 1G.

On Ubuntu MariaDB installation, edit MySQL client configuration file

vi /etc/mysql/mariadb.conf.d/50-client.cnf

Under [client], add

max_allowed_packet=1G

Next you need to update MySQL server configuration file

vi /etc/mysql/mariadb.conf.d/50-server.cnf

Under [mysqld] section, find entry for max_allowed_packet, update its value to 1G, if not found, add an entry.

max_allowed_packet=1G

Now restart MySQL

systemctl restart mysql

Back to mysqldump