Directing Output to File in mysqldump

When using mysqldump to backup MySQL databases, the -r or --result-file=name option is essential for directing the output to a file. This option allows you to specify the file name where the SQL dump will be saved, ensuring you have a reliable backup of your database.

Here are several examples demonstrating the use of -r option with mysqldump:

Example 1: Dumping a single database into a file named backup.sql:

mysqldump -r backup.sql mydatabase

This command exports the database mydatabase into a file called backup.sql. To verify its execution, check if backup.sql exists in the current directory.

Example 2: Dumping multiple databases into a single file named alldatabases.sql:

mysqldump -r alldatabases.sql --databases db1 db2 db3

Here, databases db1, db2, and db3 are exported into a single file named alldatabases.sql. Verify by checking the existence of alldatabases.sql.

Example 3: Dumping all databases on the server into a file named alldatabases.sql:

mysqldump -r alldatabases.sql --all-databases

This command exports all databases on the MySQL server into alldatabases.sql. To verify, confirm that alldatabases.sql is created and contains the expected SQL dump.

Example 4: Dumping a specific table from a database into a file named table_backup.sql:

mysqldump -r table_backup.sql mydatabase mytable

Here, mytable from database mydatabase is exported into table_backup.sql. Check if table_backup.sql exists after execution.

Example 5: Dumping with extended inserts to improve insert performance:

mysqldump -r backup.sql --extended-insert mydatabase

This command includes extended inserts in backup.sql for better performance during data restoration. Verify by examining the content of backup.sql.

Example 6: Dumping with complete inserts to ensure compatibility with older MySQL versions:

mysqldump -r backup.sql --complete-insert mydatabase

This exports mydatabase with complete insert statements into backup.sql. Ensure backup.sql is created and contains the expected SQL statements.

Example 7: Dumping a database with additional options like disabling foreign key checks:

mysqldump -r backup.sql --skip-add-drop-table --skip-disable-keys mydatabase

Here, the command exports mydatabase into backup.sql while skipping certain table operations and key checks. Confirm the contents of backup.sql post-execution.

Example 8: Dumping a database while specifying a custom delimiter:

mysqldump -r backup.sql --hex-blob --routines --triggers --events --delimiter="//" mydatabase

This command exports mydatabase with specific options like hex-blob support and routines/triggers/events into backup.sql using a custom delimiter “//”. Verify the generated file and its contents.

Example 9: Dumping a database with gzip compression:

mysqldump -r backup.sql --single-transaction --quick --compress mydatabase | gzip > backup.sql.gz

This command compresses the SQL dump of mydatabase using gzip and saves it as backup.sql.gz. Check for the existence and integrity of backup.sql.gz.

Example 10: Dumping a database while excluding certain tables:

mysqldump -r backup.sql --ignore-table=mydatabase.table1 --ignore-table=mydatabase.table2 mydatabase

Here, tables table1 and table2 are excluded from the dump of mydatabase, saved into backup.sql. Verify the resulting file to ensure tables are correctly excluded.

Also check similar articles.

Using REPLACE INTO Instead of INSERT INTO in mysqldump
Quoting Table and Column Names in mysqldump Output
Dumping Quickly without Buffering in mysqldump
Setting Connection Protocol in mysqldump
Connecting to MySQL Port in mysqldump