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