Home

How to limit the number of records put into an INSERT statement by mysqldump

Fri, 2013.04.19 - 15:48 — müzso
http://stackoverflow.com/questions/1293529/how-to-deal-with-enormous-line-length...
The mysqldump manpage says:

net_buffer_length
The initial size of the buffer for client/server communication. When creating multiple-row INSERT statements (as with the --extended-insert or --opt option), mysqldump creates rows up to net_buffer_length length. If you increase this variable, you should also ensure that the net_buffer_length variable in the MySQL server is at least this large.

To check the default value of this variable, use this:
mysqldump --help | grep net_buffer_length

For me it was almost 1 MB (i.e. 1046528) and it produced enormous lines in the dump file. According to the 5.1 documentation the variable can be set between 1024 and 1048576. However for any value below 4096 it told me this: Warning: option 'net_buffer_length': unsigned value 4095 adjusted to 4096. So probably the minimum on my system was set to 4096.

Dumping with this resulted in a lot more sane SQL file:
mysqldump --net_buffer_length=4096 --create-options --default-character-set="utf8" --host="localhost" --hex-blob --lock-tables --password --quote-names --user="myuser" "mydatabase" "mytable" > mytable.sql