{"id":103,"date":"2006-08-21T20:59:33","date_gmt":"2006-08-21T18:59:33","guid":{"rendered":"http:\/\/jerome.harckmans.be\/?p=103"},"modified":"2006-08-21T20:59:33","modified_gmt":"2006-08-21T18:59:33","slug":"mysql-databases-backup","status":"publish","type":"post","link":"https:\/\/jerome.harckmans.be\/?p=103","title":{"rendered":"Mysql Databases Backup"},"content":{"rendered":"

Everybody knows it: backups ARE<\/strong> important…But not everybody does backup \ud83d\ude09<\/p>\n

I decided to start backing up my Mysql databases to a remote server, you know, just in case…<\/p>\n

I wrote a short shell script that does the job automatically for me: it uses commands like mysqldump<\/strong>, gzip<\/strong> and ssh<\/strong> to do the whole stuff. It also rotates the backups on the remote server. That is, simply rename the existing copies on the remote server instead of directly removing it. After some rotations, it removes the oldest backup.
\nI do this it to let me some time to detect a database problem (data corruption, etc) before overwriting the good backup by a corrupted version…<\/p>\n

You can choose the rotation amount (default: 7) by modifying the script, and the backup rate by configuring cron.<\/p>\n

Here is the full script. It’s also available from the “linux -> files<\/a>” section of my site.<\/p>\n

\n
\n#!\/bin\/bash\n# Script Name: backupMysql.sh\n# Purpose: creates a backup of all Mysql databases:\n#\t- dumps all databases and zip them (.sql.gz)\n#\t- rotates old backups on remote server (7 rotations)\n#\t- copies new backup to remote server (rotation 1)\n#\t- Optional: mail result\n# Author: Harck\n# Date: 21AUG06\n\n# Mysql Settings:\nmysql_user=\"username\"\nmysql_password=\"password\"\nmysql_host=\"host\" # usually localhost\n\n# SSH Settings:\nrhost=\"ssh_host\"\nruser=\"ssh_user\"\nrport=\"ssh_port\" # typically 22\nworking_dir=\"\/tmp\/backupMysl\"\nbackup_dir=\"EXISTING directory on remote host\"\n\n# Other settings:\nMYSQLDUMP=\"\/usr\/bin\/mysqldump\"\nMYSQL=\"\/usr\/bin\/mysql\"\nSSH=\"\/usr\/bin\/ssh\"\nSCP=\"\/usr\/bin\/scp\"\nGZIP=\"\/bin\/gzip\"\nSENDMAIL=\"\/usr\/sbin\/sendmail\"\nCAT=\"\/usr\/bin\/cat\"\nmailto=\"\" #leave empty to disable e-mail notification\n\n# Check for working dir\nif [ ! -d $working_dir ]; then\n\tmkdir $working_dir\nfi\n\n# Dump the databases in the working dir:\ndbs=`$MYSQL -u $mysql_user -h $mysql_host -p$mysql_password \\\n        -Bse 'show databases'`\nfor db in $dbs\ndo\n\t$MYSQLDUMP -u $mysql_user -h $mysql_host -p$mysql_password $db | \\n\t$GZIP -9 > $working_dir\/$db.sql.gz\ndone\n\n# Rotate old backups on remote host\nfor prev in 6 5 4 3 2 1\ndo\n\t(( next = prev + 1 ))\n\t$SSH -p $rport $ruser@$rhost mv $backup_dir\/rotation_$prev\/* \\n\t$backup_dir\/rotation_$next\/ 2> \/dev\/null\ndone\n\n# Copy all backups to backup_dir\/rotation_1\n$SCP -P $rport -r $working_dir\/* $ruser@$rhost:$backup_dir\/rotation_1\/\n\n# Mail notification:\nif [ ! -z $mailto ]; then\n\ttemp_email=\/tmp\/mysql.email\n\techo \"Subject: Mysql Backup\" > $temp_email\n\techo \"Mysql Backup completed on \"`date` >> $temp_email\n\techo \"Successfully backed up:\" >> $temp_email\n\techo \"\" >> $temp_email\n\t$SSH -p $rport $ruser@$rhost ls -l $backup_dir\/rotation_1 >> \\\n                $temp_email\n\t$CAT $temp_email | $SENDMAIL $mailto\n\trm $temp_email\nfi\n\n# Clean working dir\nrm -r $working_dir\n\n# Exit\nexit 0\n<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Everybody knows it: backups ARE important…But not everybody does backup \ud83d\ude09 I decided to start backing up my Mysql databases to a remote server, you know, just in case… I<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[3,6],"tags":[],"_links":{"self":[{"href":"https:\/\/jerome.harckmans.be\/index.php?rest_route=\/wp\/v2\/posts\/103"}],"collection":[{"href":"https:\/\/jerome.harckmans.be\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jerome.harckmans.be\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jerome.harckmans.be\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/jerome.harckmans.be\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=103"}],"version-history":[{"count":0,"href":"https:\/\/jerome.harckmans.be\/index.php?rest_route=\/wp\/v2\/posts\/103\/revisions"}],"wp:attachment":[{"href":"https:\/\/jerome.harckmans.be\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jerome.harckmans.be\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jerome.harckmans.be\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}