The mysqldump command is used to create backups of MySQL databases. One useful option is --delete-source-logs, which allows rotating logs before performing the backup, ensuring that only the most recent data is included in the backup files.
Example 1: Rotating binary logs before backup
mysqldump --delete-source-logs --all-databases > backup.sql
Explanation: This command dumps all databases into backup.sql after rotating the MySQL binary logs. The binary logs are essential for point-in-time recovery but can accumulate rapidly. Using --delete-source-logs ensures that the binary logs are rotated before the backup operation starts.
Example 2: Rotating error logs before backup
mysqldump --delete-source-logs --flush-logs --master-data=2 --all-databases > backup.sql
Explanation: This command flushes the logs and performs a backup, ensuring that the logs are rotated and the binary log coordinates are recorded. The --delete-source-logs option is crucial here to rotate the logs before initiating the backup process.
Example 3: Rotating slow query logs before backup
mysqldump --delete-source-logs --flush-logs --flush-privileges --all-databases > backup.sql
Explanation: This command flushes slow query logs and other logs, ensuring that only the most recent data is backed up. The --delete-source-logs option ensures that the logs are rotated before the backup operation starts, preventing unnecessary duplication of log entries.
Also check similar articles.
Setting Default Character Set in mysqldump
Debug Information in mysqldump
Debug Check in mysqldump
Debugging Options in mysqldump
Dump Multiple Databases with mysqldump
Discussion about this post