Including Source Host and Port in mysqldump Output

The mysqldump command in MySQL is used to create backups of MySQL databases. When you include the option --include-source-host-port, it adds the source host and port information to the output of the dump file, which can be useful for understanding the origin of the data.

Here are several examples demonstrating the usage of --include-source-host-port:

Example 1: Dumping a database with source host and port included:

mysqldump --include-source-host-port -u username -p password dbname > backup.sql

This command dumps the database dbname to backup.sql including the source host and port details.

Example 2: Dumping a specific table with source host and port:

mysqldump --include-source-host-port -u username -p password dbname table1 > table1_backup.sql

This command dumps only table1 from dbname to table1_backup.sql with the source host and port information.

Example 3: Dumping all databases with source host and port:

mysqldump --include-source-host-port -u username -p password --all-databases > alldatabases_backup.sql

This command dumps all databases to alldatabases_backup.sql including the source host and port details.

Example 4: Dumping a database and sending the output to another server:

mysqldump --include-source-host-port -u username -p password dbname | ssh user@remote_host 'cat > /path/to/backup.sql'

This command dumps dbname including source host and port, then sends it securely to remote_host.

Example 5: Dumping a database and compressing it with gzip:

mysqldump --include-source-host-port -u username -p password dbname | gzip > backup.sql.gz

This command dumps dbname with source host and port and compresses it using gzip.

To verify if the --include-source-host-port option was applied successfully, you can inspect the beginning of the dump file (backup.sql in the examples above) using a text editor or command-line tools like head to see if it contains lines similar to:

-- MySQL dump [hostname] [port]

By using mysqldump with --include-source-host-port, you ensure that your database backups contain additional metadata about their source, aiding in management and traceability.

Also check similar articles.

Ignoring Tables During mysqldump
Ignoring Errors During mysqldump
Connecting to MySQL Host in mysqldump
Dumping BLOBs in Hexadecimal Format with mysqldump
Forcing mysqldump Execution Even on Errors