{"id":2955,"date":"2016-02-19T16:44:38","date_gmt":"2016-02-19T16:44:38","guid":{"rendered":"https:\/\/qbytes.cloud\/?p=2955"},"modified":"2016-02-19T16:44:38","modified_gmt":"2016-02-19T16:44:38","slug":"why-backup-your-databases-with-cron","status":"publish","type":"post","link":"https:\/\/www.qbytes.cloud\/index.php\/2016\/02\/19\/why-backup-your-databases-with-cron\/","title":{"rendered":"Why Backup your MySql Databases with Cron"},"content":{"rendered":"

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. <\/p>\n

Plesk<\/p>\n

Create a location for the dumps. This will be the folder the dumps are exported to by the script.<\/p>\n

\n# mkdir -pv \/usr\/local\/db\/dumps\n<\/pre>\n
Create a script that cron can execute. Create \/usr\/local\/bin\/dbdump with these contents:<\/p>\n
\nnano \/usr\/local\/bin\/dbdump\n<\/pre>\n
Put this in the file (for Plesk)<\/p>\n
\n#!\/bin\/bash\nfor 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\n<\/pre>\n
Make it executable:<\/p>\n
\n# chmod +x \/usr\/local\/bin\/dbdump\n<\/pre>\n
For non plesk:<\/p>\n
\n# nano \/usr\/local\/bin\/dbdump\n<\/pre>\n
Put this in the file<\/p>\n
\nfor 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\n<\/pre>\n
Make it executable:<\/p>\n
\n# chmod +x \/usr\/local\/bin\/dbdump\n<\/pre>\n
Create \/etc\/cron.d\/dbdump with these contents: <\/p>\n
\n# nano \/etc\/cron.d\/dbdump\n<\/pre>\n
\n# Cronjob to dump databases nightly\n05     0      *       *       *       root       \/usr\/local\/bin\/dbdump >\/dev\/null 2>&1\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,70],"tags":[],"class_list":["post-2955","post","type-post","status-publish","format-standard","hentry","category-administration","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/2955","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=2955"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/2955\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=2955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=2955"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=2955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}