{"id":57,"date":"2014-04-23T00:12:09","date_gmt":"2014-04-22T20:12:09","guid":{"rendered":"http:\/\/ustinych.net\/wordpress\/?p=57"},"modified":"2014-05-12T18:43:27","modified_gmt":"2014-05-12T14:43:27","slug":"make-automatic-backup-of-all-mysql-databases","status":"publish","type":"post","link":"https:\/\/ustinych.net\/wordpress\/?p=57","title":{"rendered":"Make automatic backup of ALL MySQL databases"},"content":{"rendered":"<div style=\"color: #000000;\">\n<h2><span id=\"SCRIPT_UPDATED_to_v3.1\" class=\"mw-headline\">SCRIPT UPDATED to v3.1<\/span><\/h2>\n<div><\/div>\n<div><\/div>\n<div><b>Version 3 is here, as per request, it now handles daily, weekly and monthly archives.<\/b><\/div>\n<div><\/div>\n<div><\/div>\n<div>I&#8217;ve constructed a script that will do a backup of all the databases in the MySQL, a thing which is not natively implemented in the firmware, so with a crash or a bad firmware update you risk loosing your databases.<\/div>\n<div><\/div>\n<div><\/div>\n<div><b>FEATURES<\/b><\/div>\n<p>*Each database will be backed up to a seperate file*Every day the backup from the day before will be tar&#8217;ed and compressed to a file, before a new backup is made. This way, the latest backup is easily available, and you can go as far back as you have allowed.<\/p>\n<ul>\n<li>Backups are put in a daily, weekly or monthly archive<\/li>\n<li>Old backups will be removed when they are X days old (you can of course change how many backups you want to keep by changing the value in the configfile, you can adjust both how many daily, weekly and monthly you wish to keep, defaults are 6,5,3).<\/li>\n<li>You choose which day you want to keep the weekly backup (default is 0 = sunday)<\/li>\n<li>When you run the script you should see a bit information on what it&#8217;s doing.<\/li>\n<li>The System log will reflect the script status.<\/li>\n<li>Backups can be placed in a subfolder under the share, use the parameter folder in the config.<\/li>\n<\/ul>\n<p><b>Requirements<\/b><\/p>\n<ul>\n<li>A share (set in the config)<\/li>\n<li>A directory under the above share called mysql (it will be created automatically if it does not exist)<\/li>\n<li>Directories under the above share called mysql.daily, mysql.weekly and mysql.monthly (they will be created automatically if they do not exist)<\/li>\n<li>A MySQL user (set in the config) that has global SELECT and global VIEWS rights, and has &#8216;localhost&#8217; access, for security, don&#8217;t use root, but create a new user, default is called backup.<\/li>\n<li>The mysqldump command<\/li>\n<\/ul>\n<div><\/div>\n<\/div>\n<div style=\"color: #000000;\"><b>Working on a RAID system or not?<\/b><\/div>\n<div style=\"color: #000000;\">The path changes if you use RAID or not, this is now compensated for in the script.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><b>The MysqlBackup user for the script<\/b><\/div>\n<div style=\"color: #000000;\">The script needs a valid user for accessing the MySQL databases. The user for the script can be set in the configfile.<\/div>\n<div style=\"color: #000000;\">The user needs &#8216;localhost&#8217; access and needs global SELECT and Global SHOW VIEW rights.<\/div>\n<div style=\"color: #000000;\">The easiest way to set this up, is via phpMyAdmin.<\/div>\n<div style=\"color: #000000;\">Place the users password in the config file.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">The config file is to be placed here: \/etc\/config\/mysqlbackup.conf<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><b>How to find the mysqldump command<\/b><\/div>\n<div style=\"color: #000000;\">The script knows a few standard locations for it, if it fails to find the command, please use the command below and send the result to me for addition:<\/div>\n<div style=\"color: #000000;\">\n<pre>find \/ -name mysqldump\r\n<\/pre>\n<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">This will return the path to the mysqldump.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<h3 style=\"color: #000000;\"><span id=\"Backup\" class=\"mw-headline\">Backup<\/span><\/h3>\n<div style=\"color: #000000;\"><b>mysqlbackup.sh<\/b><\/div>\n<div style=\"color: #000000;\">The script is now of a size that makes it easier to download, so it has been attached to this post.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">The script can be saved anywhere you like, eg. under \/opt\/bin (remember to mark it executable with chmod +x mysqlbackup.sh.<\/div>\n<div style=\"color: #000000;\">NOTE: \/opt\/bin is only created if you have installed Optware, and if you uninstall optware \/opt\/bin gets deleted (learned it the hard way.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">Place the mysqlbackup.sh script on your NAS<\/div>\n<div style=\"color: #000000;\">First save the file to a share (we call it YOURSHARE)<\/div>\n<div style=\"color: #000000;\">Then copy it to where you want it to go (YOURPREFERREDLOCATION).<\/div>\n<div style=\"color: #000000;\">Go to the nas with putty.exe<\/div>\n<div style=\"color: #000000;\"><\/div>\n<pre style=\"color: #000000;\"># cp \/share\/YOURSHARE\/mysqlbackup\/bin\/mysqlbackup.sh \/YOURPREFERREDLOCATION\r\n# chmod 700 \/YOURPREFERREDLOCATION\/mysqlbackup.sh\r\n<\/pre>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">Now the command is implemented.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">Place the mysqlbackup.conf config file under \/etc\/config<\/div>\n<pre style=\"color: #000000;\"># cp \/share\/YOURSHARE\/mysqlbackup\/conf\/mysqlbackup.conf \/etc\/config&lt;\/div&gt;\r\n# chmod 600 \/etc\/config\/mysqlbackup.conf\r\n<\/pre>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">The config file is now in place.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<h3 style=\"color: #000000;\"><span id=\"Scheduled_backup\" class=\"mw-headline\">Scheduled backup<\/span><\/h3>\n<div style=\"color: #000000;\">If you include the command in cron, you will get a scheduled backup.<\/div>\n<div style=\"color: #000000;\">You can either edit the crontab manually, or you use the excellent QPKG for editing crontab via webinterface.<\/div>\n<div style=\"color: #000000;\">To edit the crontab, log in via SSH with putty.exe and start the editor with<\/div>\n<pre style=\"color: #000000;\"># vi \/etc\/config\/crontab<\/pre>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">Insert a line by pressing i<\/div>\n<pre style=\"color: #000000;\">10 1 * * * \/opt\/bin\/mysqlbackup.sh<\/pre>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">Remember, the path has to be the same as you used above.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">Press\u00a0<b>ESC<\/b>\u00a0to leave the insert mode<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">Press\u00a0:wq<\/div>\n<div style=\"color: #000000;\">to save the file<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">Finally make cron aware of the changed crontab<\/div>\n<div style=\"color: #000000;\"><\/div>\n<pre style=\"color: #000000;\"># crontab \/etc\/config\/crontab<\/pre>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">See the new crontab by using<\/div>\n<div style=\"color: #000000;\"><\/div>\n<pre style=\"color: #000000;\"># crontab -l<\/pre>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">That&#8217;s it, the backup will run every night at 1:10<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<h3 style=\"color: #000000;\"><span id=\"Restore\" class=\"mw-headline\">Restore<\/span><\/h3>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">There are two situations for restore.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">1) You need to restore a database from the day before<\/div>\n<div style=\"color: #000000;\">2) You need to restore a database from previously.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><b>Restore from day before<\/b><\/div>\n<div style=\"color: #000000;\">Do a simple import of the database.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">1) Open up phpMyAdmin<\/div>\n<div style=\"color: #000000;\">2) Change the database to the one you wish to restore (it&#8217;s on the backupshare, under mysql).<\/div>\n<div style=\"color: #000000;\">3) Select Import<\/div>\n<div style=\"color: #000000;\">4) Select the file you wish to import to the database<\/div>\n<div style=\"color: #000000;\">5) Press Execute<\/div>\n<div style=\"color: #000000;\">DONE!<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\"><b>Restore from earlier backups<\/b><\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">Connect to the box with putty.<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">cd \/share\/BACKUPSHARE\/mysql(.daily\/.weekly\/monthly) choose the archive you wish to use<\/div>\n<div style=\"color: #000000;\">\/bin\/tar -xvf BACKUPNAME<\/div>\n<div style=\"color: #000000;\"><\/div>\n<div style=\"color: #000000;\">The rest of the guide is more or less like above:<\/div>\n<div style=\"color: #000000;\">1) Open up phpMyAdmin<\/div>\n<div style=\"color: #000000;\">2) Change the database to the one you wish to restore<\/div>\n<div style=\"color: #000000;\">3) Select Import<\/div>\n<div style=\"color: #000000;\">4) Select the file you wish to import to the database<\/div>\n<div style=\"color: #000000;\">5) Press Execute<\/div>\n<div style=\"color: #000000;\"><\/div>\n<h3 style=\"color: #000000;\"><span id=\"Attachments\" class=\"mw-headline\">Attachments<br \/>\n<\/span><\/h3>\n<p style=\"color: #000000;\"><a title=\"mysqlbackup.zip\" href=\"http:\/\/ustinych.net\/wordpress\/wp-content\/uploads\/2014\/04\/mysqlbackup.zip\">mysqlbackup.zip<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SCRIPT UPDATED to v3.1 Version 3 is here, as per request, it now handles daily, weekly and monthly archives. I&#8217;ve constructed a script that will do a backup of all the databases in the MySQL, a thing which is not natively implemented in the firmware, so with a crash or a bad firmware update you\u2026 <span class=\"read-more\"><a href=\"https:\/\/ustinych.net\/wordpress\/?p=57\">\u0427\u0438\u0442\u0430\u0442\u044c \u0434\u0430\u043b\u0435\u0435 &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":140,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[18,17,4],"class_list":["post-57","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-qnap","tag-backup","tag-mysql","tag-qnap-2"],"_links":{"self":[{"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/57","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=57"}],"version-history":[{"count":2,"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/57\/revisions"}],"predecessor-version":[{"id":60,"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/57\/revisions\/60"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/140"}],"wp:attachment":[{"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=57"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=57"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ustinych.net\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=57"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}