Simply put something like this into /etc/logrotate.conf:

/var/backups/mysql/dump.sql {
daily
rotate 14
missingok
compress
postrotate
/usr/bin/mysqldump --defaults-extra-file=/.../backup-credentials.cnf --opt --flush-logs --all-databases > /var/backups/mysql/dump.sql
endscript
}

avec backup-credentials.cnf en chmod 600 contenant:
[mysqldump]
user=mysqluser
password=mypsswd