Bitnami Setup Cron to Backup MySQL

Step 1. Disable password prompt for "mysqldump command"

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

1. Run "vi ~/.my.cnf"

2. Add the following lines

[mysqldump]
user=mysqluser
password=secret

3. For Bitnami, you'll need to append the following line in "/opt/bitnami/mysql/my.cnf"

!include ~/.my.cnf

4. Try running to see if the command works.

mysqldump --all-databases > /home/bitnami/backups/db-backup.sql

Step 2. Setup Cron to Backup to MySQL

Lets setup cron to run at 00:00, every midnight.


Option 1:

For manual insert into crontab file via "crontab -e" command

0 0 * * * /opt/bitnami/mysql/bin/mysqldump --all-databases | gzip > /home/bitnami/backups/db-$(date +\%Y\%m\%d\%H\%M\%S).sql.gz

Note that you'll need to escape the "%" characters, because cron will see it as end character and ignore the rest of the command.


Option 2:

Script for inserting cron jobs, and check unique job.

(crontab -l ; echo "0 0 * * * /opt/bitnami/mysql/bin/mysqldump --all-databases | gzip > /home/bitnami/backups/db-\$(date +\%Y\%m\%d\%H\%M\%S).sql.gz") | sort - | uniq - | crontab -

Note that you'll need to escape the "$" character so that the echo command will not evaluate the date.


Option 3 (Bonus):

Via Laravel Envoy,

@servers(['production-db' => 'username@server.cloudapp.net'])

@task('initialize-cronjobs-db', ['on' => 'production-db'])
(crontab -l ; echo "0 0 * * * /opt/bitnami/mysql/bin/mysqldump --all-databases | gzip > /home/bitnami/backups/db-\$(date +\%Y\%m\%d\%H\%M\%S).sql.gz") | sort - | uniq - | crontab -
@endtask

Then run

envoy run production-db