Generating Compact Output in mysqldump

The mysqldump command in MySQL is used for exporting database data and structures into a SQL dump file, which can be later used to recreate the database. One of the options available is --compact, which modifies the output format of the dump to be more concise and space-efficient.

Here are several examples demonstrating the usage of --compact with mysqldump:

Example 1: Exporting a single database named mydatabase in compact format.

            mysqldump --compact mydatabase > mydatabase.sql
        

This command exports the database mydatabase into a file named mydatabase.sql in compact format.

Example 2: Exporting all databases on the server in compact format.

            mysqldump --compact --all-databases > alldatabases.sql
        

This command exports all databases on the MySQL server into a single file named alldatabases.sql, using compact format.

Example 3: Exporting a specific table users from a database mydatabase in compact format.

            mysqldump --compact mydatabase users > mydatabase_users.sql
        

Here, only the table users from the database mydatabase is exported into mydatabase_users.sql using compact format.

Example 4: Exporting with compact format and adding drop table statements.

            mysqldump --compact --add-drop-table mydatabase > mydatabase_with_drop.sql
        

This command includes drop table statements in the dump file mydatabase_with_drop.sql, along with the compact format data.

Example 5: Exporting with compact format and suppressing extended inserts.

            mysqldump --compact --skip-extended-insert mydatabase > mydatabase_no_extended.sql
        

Using --skip-extended-insert along with --compact ensures that each row is inserted separately, resulting in a more compact dump file named mydatabase_no_extended.sql.

To verify whether the mysqldump command executed successfully, you can check the generated SQL dump files. For instance, using a file explorer or the ls command in a Unix-like environment, you can see if the file exists and its size to ensure data has been dumped. Additionally, you can examine the content of the dump file using a text editor or by importing it back into MySQL to recreate the database.

Read Also

Setting MySQL Dump Compatibility Mode
Including Comments in mysqldump Output
Adding ANALYZE TABLE Statements in mysqldump Output
Setting Character Sets Directory for mysqldump
Binding IP Address for mysqldump