Setting Maximum Allowed Packet Size in mysqldump

The mysqldump command in MySQL is used for creating backups of MySQL databases. One important aspect of this command is setting the maximum allowed packet size, which determines the largest size of a packet or a single SQL statement that MySQL can handle. This is crucial when dealing with large databases or when exporting/importing data that exceeds default limits.

Setting Maximum Allowed Packet Size in mysqldump: To specify the maximum allowed packet size using --max-allowed-packet=#, replace # with the desired size in bytes. For example, to set it to 100MB (104857600 bytes), you would use:

mysqldump --max-allowed-packet=104857600 -u username -p database_name > backup.sql

This command exports the database database_name to backup.sql with a maximum packet size of 100MB. It ensures that larger queries or data transfers are handled without truncation due to packet size limitations.

Examples:

1. Exporting a database with a packet size of 50MB:
mysqldump --max-allowed-packet=52428800 -u username -p database_name > backup.sql
This sets the packet size to 50MB and exports database_name to backup.sql.

2. Importing a large SQL file with increased packet size:
mysql --max-allowed-packet=67108864 -u username -p database_name < large_file.sql
Here, MySQL is instructed to accept packets up to 64MB while importing large_file.sql into database_name.

3. Using mysqldump for a specific table with a modified packet size:
mysqldump --max-allowed-packet=33554432 -u username -p database_name table_name > table_backup.sql
This command backs up table_name from database_name with a packet size set to 32MB into table_backup.sql.

Verification Steps: After executing the mysqldump command with the specified packet size, you can verify its execution by checking the size of the resulting backup file. For instance, using ls -lh backup.sql on Unix-based systems will display the size of the backup file backup.sql. Alternatively, you can open the file to ensure that all data has been successfully exported without truncation or errors related to packet size limitations.

Also check similar articles.

Appending Binary Log Position in mysqldump Output (Deprecated)
Appending Binary Log Position in mysqldump Output
Setting Long Query Time for mysqldump
Logging Errors to File in mysqldump
Locking Tables for Read in mysqldump