The mysqldump command is used to create backups of MySQL databases. One useful option is --dump-date, which adds the dump date to the output filename, providing a timestamped version of the backup.
Here are several examples demonstrating the use of --dump-date:
Example 1: Adding a date suffix to the backup file.
mysqldump --dump-date mydatabase > mydatabase_$(date +%F).sql
This command dumps the database mydatabase to a file named mydatabase_YYYY-MM-DD.sql, where YYYY-MM-DD represents the current date.
Example 2: Using a custom format for the date.
mysqldump --dump-date=mydatabase > mydatabase_$(date +%Y%m%d%H%M%S).sql
Here, the output filename will include a timestamp in the format YYYYMMDDHHMMSS.
Example 3: Dumping multiple databases with date suffixes.
mysqldump --dump-date database1 database2 > databases_$(date +%Y-%m-%d).sql
This command dumps both database1 and database2 to a single file named databases_YYYY-MM-DD.sql.
Example 4: Appending the date to an existing backup file.
mysqldump --dump-date --append mydatabase >> mydatabase_backup.sql
Using --append adds the dump of mydatabase to an existing file named mydatabase_backup.sql with a new timestamped section.
Example 5: Creating a gzipped backup file with date.
mysqldump --dump-date mydatabase | gzip > mydatabase_$(date +%Y-%m-%d).sql.gz
This command creates a compressed backup file named mydatabase_YYYY-MM-DD.sql.gz.
To verify whether these commands executed successfully, check the existence and timestamp of the output files using:
ls -l mydatabase_*
Replace mydatabase_* with the actual filename pattern used in your command to see if the file was created with the expected date suffix.
Also check similar articles.
Creating Consistent Snapshot with Single Transaction in mysqldump
Adding GTID_PURGED to mysqldump Output
Setting Character Set in mysqldump Output
Dumping Routines (Functions and Procedures) with mysqldump
Directing Output to File in mysqldump
Discussion about this post