Using Extended INSERT Syntax in mysqldump

Using mysqldump with the -e, --extended-insert option enables the generation of SQL dumps that employ extended INSERT syntax. This syntax optimizes the dump process by grouping multiple rows into a single INSERT statement, thereby reducing the overhead of executing individual INSERT statements.

Let’s explore some examples to understand how this works:

Example 1: Dumping a single table with extended INSERTs:

mysqldump -e --extended-insert dbname tablename > dump.sql

This command dumps the table tablename from database dbname into dump.sql using extended INSERT statements.

Example 2: Dumping multiple tables with extended INSERTs:

mysqldump -e --extended-insert dbname table1 table2 > dump.sql

Here, table1 and table2 from database dbname are dumped into dump.sql with extended INSERTs.

Example 3: Dumping specific rows with extended INSERTs:

mysqldump -e --extended-insert dbname tablename --where="id < 100" > dump.sql

This command dumps rows from tablename where id < 100 into dump.sql using extended INSERT statements.

Example 4: Dumping with extended INSERTs and disabling triggers:

mysqldump -e --extended-insert --skip-triggers dbname tablename > dump.sql

Here, triggers are skipped while dumping tablename from dbname into dump.sql with extended INSERTs.

Example 5: Dumping with extended INSERTs and adding DROP TABLE statements:

mysqldump -e --extended-insert --add-drop-table dbname tablename > dump.sql

This command adds DROP TABLE statements before each CREATE TABLE in dump.sql, while using extended INSERTs.

Example 6: Dumping with extended INSERTs and including comments:

mysqldump -e --extended-insert --comments dbname tablename > dump.sql

Comments are added to the dump file dump.sql alongside the extended INSERTs.

Example 7: Dumping with extended INSERTs and using custom delimiters:

mysqldump -e --extended-insert --hex-blob dbname tablename > dump.sql

Hexadecimal notation is used for binary data (BLOBs) when dumping tablename from dbname into dump.sql with extended INSERTs.

Example 8: Dumping with extended INSERTs and limiting number of rows per INSERT:

mysqldump -e --extended-insert --max-allowed-packet=32M dbname tablename > dump.sql

This command limits the size of each INSERT statement to 32 megabytes when dumping tablename from dbname into dump.sql using extended INSERTs.

Example 9: Dumping with extended INSERTs and skipping comments:

mysqldump -e --extended-insert --skip-comments dbname tablename > dump.sql

Comments are excluded from the dump file dump.sql while using extended INSERTs.

Example 10: Dumping with extended INSERTs and specifying a different character set:

mysqldump -e --extended-insert --default-character-set=utf8 dbname tablename > dump.sql

The character set for the dump file dump.sql is set to UTF-8 when dumping tablename from dbname using extended INSERTs.

Also check similar articles.

Dumping Events Using mysqldump
Dumping Slave Position in mysqldump Output (Deprecated)
Dumping Replica Position in mysqldump Output
Disabling Keys in mysqldump Output
Deleting Master Logs in mysqldump (Deprecated)