{"id":2160,"date":"2024-05-28T14:55:26","date_gmt":"2024-05-28T12:55:26","guid":{"rendered":"https:\/\/alessandromasciadri.com\/?p=2160"},"modified":"2024-05-28T16:15:05","modified_gmt":"2024-05-28T14:15:05","slug":"come-eseguire-il-backup-di-mysql-mariadb-o-postgres-con-borg","status":"publish","type":"post","link":"https:\/\/alessandromasciadri.com\/come-eseguire-il-backup-di-mysql-mariadb-o-postgres-con-borg\/","title":{"rendered":"Come eseguire il backup di MySQL, MariaDB o Postgres con Borg"},"content":{"rendered":"\t\t<div data-akihiro-type=\"ama-post\" data-akihiro-id=\"2160\" class=\"akihiro akihiro-2160\" data-akihiro-post-type=\"post\">\n\t\t\t\t<div class=\"akihiro-element akihiro-element-79f16cf e-flex e-con-boxed e-con e-parent\" data-id=\"79f16cf\" data-element_type=\"container\" data-e-type=\"container\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"akihiro-element akihiro-element-e672528 akihiro-widget akihiro-widget-text-editor\" data-id=\"e672528\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Se nel nostro scenario di backup con Borg abbiamo necessit\u00e0 di eseguire anche il backup dei nostri database, possiamo seguire uno dei due modi presenti in questa guida. <\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-b5c5a8f akihiro-widget akihiro-widget-heading\" data-id=\"b5c5a8f\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Premessa importante<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-c4377cf akihiro-widget akihiro-widget-text-editor\" data-id=\"c4377cf\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Anzitutto \u00e8 necessario fare un piccolo preambolo. Un metodo, che ho visto spesso applicare, \u00e8 quello di copiare la directory dove il DBMS salva i dati interni del database. Mi sento di NON consigliare questa strategia in quanto non \u00e8 sicuro eseguire una copia di quei file mentre il servizio del DBMS \u00e8 in esecuzione. Quindi, a meno che non si voglia stoppare preventivamente il database service durante il backup (creando cos\u00ec un&#8217;interruzione del servizio), \u00e8 meglio considereare un altro approccio. Vediamo qui di seguito come poter fare.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-bb5d97d akihiro-widget akihiro-widget-heading\" data-id=\"bb5d97d\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Backup del DB utilizzando Borgmatic e il suo Database Hook<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-9e6f47d akihiro-widget akihiro-widget-text-editor\" data-id=\"9e6f47d\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Nel caso si stia gi\u00e0 utilizzando il binomio Borg con Borgmatic e il database \u00e8 di grandi dimensioni, \u00e8 consigliabile utilizzare i Database Hook integrati in Borgmatic per scaricarne il contenuto e includerlo nel backup.<\/p><p>Per eseguire il dump di tutti i DB MySQL e Postgres prima di ogni backup utilizzare il seguente snippet:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-4c101d7 akihiro-widget akihiro-widget-code-highlight\" data-id=\"4c101d7\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-tomorrow copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language- line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-\">\n\t\t\t\t\t<xmp>hooks:\n    postgresql_databases:\n        - name: all\n    mysql_databases:\n        - name: all<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-aabc977 akihiro-widget akihiro-widget-text-editor\" data-id=\"aabc977\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Il vantaggio dell&#8217;applicare questa tecnica \u00e8 che il dump del DB viene letto tramite una pipe e quindi mai scritto su disco, rendendo questo processo eseguibile anche negli scenari in cui lo spazio su disco della macchina di cui si esegue il backup non \u00e8 sufficiente per contenere il backup stesso.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-c1d3bd1 akihiro-widget akihiro-widget-heading\" data-id=\"c1d3bd1\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h5 class=\"akihiro-heading-title akihiro-size-default\">Riferimenti<\/h5>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-5a1d208 akihiro-widget akihiro-widget-text-editor\" data-id=\"5a1d208\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<ul><li><a href=\"https:\/\/torsion.org\/borgmatic\/docs\/how-to\/backup-your-databases\/\">https:\/\/torsion.org\/borgmatic\/docs\/how-to\/backup-your-databases\/<\/a><\/li><\/ul>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-125103b akihiro-widget akihiro-widget-heading\" data-id=\"125103b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Backup del DB utilizzando uno script di dump<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-48b2c85 akihiro-widget akihiro-widget-text-editor\" data-id=\"48b2c85\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\tMolte delle popolari distribuzioni Linux includono scripts come automysqlbackup o autopostgresqlbackup. Questi script possono essere utilizzati per generare un dump giornaliero di tutti i database, il quale viene salvato per default nel percorso <code>\/var\/lib\/automysqlbackup<\/code>. Possiamo quindi eseguire questo script prima del backup con Borg e includendo la cartella che contiene i file di dump.\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-576d282 akihiro-widget akihiro-widget-text-editor\" data-id=\"576d282\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Ipotiziamo quindi di eseguire il dump del DB ogni giorno alle 2:00 del mattino ed eseguire successivamente alle 2:15 del mattino il backup con Borg. Per fare ci\u00f2 la nostra crontab sarebbe la seguente:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-df769fe akihiro-widget akihiro-widget-code-highlight\" data-id=\"df769fe\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-tomorrow copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language- \">\n\t\t\t\t<code readonly=\"true\" class=\"language-\">\n\t\t\t\t\t<xmp>00 02 * * *  \/usr\/sbin\/automysqlbackup\n15 02 * * *  \/home\/alessandro\/borg_launcher.sh<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-7a5b62e akihiro-widget akihiro-widget-text-editor\" data-id=\"7a5b62e\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Ed il nostro script di backup assomiglierebbe al seguente:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-fc2ac93 akihiro-widget akihiro-widget-code-highlight\" data-id=\"fc2ac93\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-tomorrow copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-bash line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>#!\/bin\/bash\n\nexport BORG_REPO=ssh:\/\/...\nexport BORG_PASSPHRASE=your-borg-passphrase\nexport BORG_RSH='ssh -oBatchMode=yes -i \/home\/alessandro\/.ssh\/[your-borg-ssh-key]'\n\ndate=$(date +%Y%m%d-%H%M)\narchive_name=\"$date-full-backup\"\n\necho \"$(date +%Y-%m-%d) $(date +%H:%M)   BACKUP STARTED\"\necho \"$(date +%Y-%m-%d) $(date +%H:%M)   ARCHIVE NAME $archive_name\"\n\nborg create --stats ::$archive_name \\\n    \/var\/www\/ \\\n    \/var\/lib\/automysqlbackup\/\n\necho \"$(date +%Y-%m-%d) $(date +%H:%M)   BACKUP FINISHED\"\necho<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-e5f1496 akihiro-widget akihiro-widget-heading\" data-id=\"e5f1496\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h5 class=\"akihiro-heading-title akihiro-size-default\">Riferimenti<\/h5>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-5ece856 akihiro-widget akihiro-widget-text-editor\" data-id=\"5ece856\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<ul><li><a href=\"https:\/\/sourceforge.net\/projects\/automysqlbackup\/\">https:\/\/sourceforge.net\/projects\/automysqlbackup\/<\/a><\/li><li><a href=\"https:\/\/github.com\/k0lter\/autopostgresqlbackup\">https:\/\/github.com\/k0lter\/autopostgresqlbackup<\/a><\/li><\/ul>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Se nel nostro scenario di backup con Borg abbiamo necessit\u00e0 di eseguire anche il backup dei nostri database, possiamo seguire uno dei due modi presenti in questa guida. Premessa importante Anzitutto \u00e8 necessario fare un piccolo preambolo. Un metodo, che ho visto spesso applicare, \u00e8 quello di copiare la directory dove il DBMS salva i [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[54],"class_list":["post-2160","post","type-post","status-publish","format-standard","hentry","category-sistemistica","tag-borg"],"_links":{"self":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/2160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/comments?post=2160"}],"version-history":[{"count":22,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/2160\/revisions"}],"predecessor-version":[{"id":2183,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/2160\/revisions\/2183"}],"wp:attachment":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/media?parent=2160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/categories?post=2160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/tags?post=2160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}