There are a few reasons to install a cron job to create full database dumps versus copying the /var/lib/mysql data. The MySQL service may have a lock on the database file(s) when it attempts to replicate the data file, causing it to fail; if the backup on the data files does succeed, the InnoDB log files may not match with the ibdata increasing the chance of full corruption or lost data; MyISAM data and table structures can face similar issues.
Plesk
Create a location for the dumps. This will be the folder the dumps are exported to by the script.
# mkdir -pv /usr/local/db/dumps
Create a script that cron can execute. Create /usr/local/bin/dbdump with these contents:
nano /usr/local/bin/dbdump
Put this in the file (for Plesk)
#!/bin/bash for db in $(mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e 'show databases' -s --skip-column-names); do echo -n "dumping $db..."; mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` $db > "/usr/local/db/dumps/$db.sql"; echo "[done]"; done
Make it executable:
# chmod +x /usr/local/bin/dbdump
For non plesk:
# nano /usr/local/bin/dbdump
Put this in the file
for db in $(mysql -uroot -pPASSWORD -e 'show databases' -s --skip-column-names); do echo -n "dumping $db..."; mysqldump -uroot -pPASSWORD $db > "/usr/local/db/dumps/$db.sql"; echo "[done]"; done
Make it executable:
# chmod +x /usr/local/bin/dbdump
Create /etc/cron.d/dbdump with these contents:
# nano /etc/cron.d/dbdump
# Cronjob to dump databases nightly 05 0 * * * root /usr/local/bin/dbdump >/dev/null 2>&1