Dumping BLOBs in Hexadecimal Format with mysqldump

The mysqldump command in MySQL is used for creating backups of databases. One of the options available is --hex-blob, which specifies that binary large objects (BLOBs) should be dumped in hexadecimal format rather than binary format. This can be particularly useful when you need to examine or transfer BLOB data in a human-readable format.

Here are several examples demonstrating the usage of --hex-blob:

Example 1: Dumping a single table named mytable with BLOB columns in hexadecimal format:

    mysqldump --hex-blob -u username -p mydatabase mytable > mytable_backup.sql
    

This command exports the table mytable from database mydatabase into a file mytable_backup.sql, converting BLOBs to hexadecimal format.

Example 2: Dumping an entire database mydatabase with all BLOBs in hexadecimal format:

    mysqldump --hex-blob -u username -p mydatabase > mydatabase_backup.sql
    

Here, all tables within mydatabase will be dumped into mydatabase_backup.sql, with BLOBs converted to hexadecimal.

Example 3: Dumping and restoring a database with BLOBs in hexadecimal format:

    mysqldump --hex-blob -u username -p mydatabase > mydatabase_backup.sql
    mysql -u username -p mydatabase_restored < mydatabase_backup.sql
    

This pair of commands first dumps the database into a file and then restores it, preserving BLOBs in hexadecimal format throughout.

Example 4: Dumping a specific table with BLOB columns in hexadecimal format and compressing the output:

    mysqldump --hex-blob -u username -p --compress mydatabase mytable | gzip > mytable_backup.sql.gz
    

Here, the output is compressed using gzip after converting BLOBs to hexadecimal.

Example 5: Dumping a database, excluding certain tables, with BLOBs in hexadecimal format:

    mysqldump --hex-blob -u username -p --ignore-table=mydatabase.table1 --ignore-table=mydatabase.table2 mydatabase > mydatabase_backup.sql
    

This command excludes table1 and table2 from the dump while converting BLOBs to hexadecimal.

Example 6: Dumping a database with specific character set and collation settings, including BLOBs in hexadecimal:

    mysqldump --hex-blob -u username -p --default-character-set=utf8mb4 --default-collation=utf8mb4_unicode_ci mydatabase > mydatabase_backup.sql
    

Setting character set and collation ensures compatibility while keeping BLOBs in hexadecimal format.

Example 7: Dumping and importing a database with BLOBs in hexadecimal format using named pipes:

    mysqldump --hex-blob -u username -p mydatabase | mysql -u username -p mydatabase_restored
    

Named pipes can be used to stream the dump directly to the import process, maintaining BLOBs in hexadecimal.

Example 8: Dumping and exporting a specific table with BLOBs in hexadecimal format to a remote server:

    mysqldump --hex-blob -u username -p mydatabase mytable | ssh user@remote-server 'mysql -u username -p mydatabase'
    

This command securely transfers the dump to a remote server while preserving BLOBs in hexadecimal format.

Example 9: Dumping a database with BLOBs in hexadecimal format and specifying a specific storage engine:

    mysqldump --hex-blob -u username -p --single-transaction --default-storage-engine=InnoDB mydatabase > mydatabase_backup.sql
    

Using --single-transaction ensures a consistent snapshot while converting BLOBs to hexadecimal.

Example 10: Dumping a database with BLOBs in hexadecimal format and excluding triggers:

    mysqldump --hex-blob -u username -p --skip-triggers mydatabase > mydatabase_backup.sql
    

Excluding triggers can be useful when restoring databases with BLOBs in hexadecimal format.

To verify whether the --hex-blob option was applied correctly, you can open the resulting SQL dump file using a text editor or a command-line viewer. Look for BLOB columns within the file to ensure they are represented in hexadecimal format, typically indicated by sequences of hexadecimal digits (0-9, A-F).

Also check similar articles.

Forcing mysqldump Execution Even on Errors
Emitting FLUSH PRIVILEGES Statement in mysqldump
Flushing Logs Before Dumping with mysqldump
Escaping Fields in mysqldump Output
Optionally Enclosing Fields in mysqldump Output