{"id":2397,"date":"2024-12-18T23:04:22","date_gmt":"2024-12-18T22:04:22","guid":{"rendered":"https:\/\/alessandromasciadri.com\/?p=2397"},"modified":"2024-12-18T23:10:45","modified_gmt":"2024-12-18T22:10:45","slug":"come-eseguire-backup-e-restore-di-un-database-mysql-con-mysqldump","status":"publish","type":"post","link":"https:\/\/alessandromasciadri.com\/come-eseguire-backup-e-restore-di-un-database-mysql-con-mysqldump\/","title":{"rendered":"Come eseguire backup e restore di un database MySQL con mysqldump"},"content":{"rendered":"\t\t<div data-akihiro-type=\"ama-post\" data-akihiro-id=\"2397\" class=\"akihiro akihiro-2397\" data-akihiro-post-type=\"post\">\n\t\t\t\t<div class=\"akihiro-element akihiro-element-837653d e-flex e-con-boxed e-con e-parent\" data-id=\"837653d\" 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-264a2f1 akihiro-widget akihiro-widget-text-editor\" data-id=\"264a2f1\" 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>In questo articolo scopriamo come eseguire il backup e il ripristino di un database MySQL o MariaDB grazie all&#8217;utility a riga di comando mysqldump.<\/p><p>Il file di backup ottenuto usando l&#8217;utility mysqldump \u00e8 praticamente un insieme di SQL statements che possono essere usati per ricreare un database identico all&#8217;originale, quindi ripristinandone la struttura. Come nota a margine \u00e8 inoltre utile sapere che mysqldump pu\u00f2 anche generare file CSV o XML utili per esportare i dati contenuti nel database.<\/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-bc485f3 akihiro-widget akihiro-widget-heading\" data-id=\"bc485f3\" 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\">Sintassi di mysqldump<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-cb4d078 akihiro-widget akihiro-widget-text-editor\" data-id=\"cb4d078\" 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>Iniziamo la scoperta di mysqldump analizzando la sua sintassi di base. Un espressione mysqldump segue la seguente sintassi:<\/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-9d17343 akihiro-widget akihiro-widget-code-highlight\" data-id=\"9d17343\" 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 \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>mysqldump [options] > file.sql<\/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-72ee9c4 akihiro-widget akihiro-widget-text-editor\" data-id=\"72ee9c4\" 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>options &#8211; Sono le <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mysqldump.html#mysqldump-option-summary\" target=\"_blank\" rel=\"noopener\">opzioni che mysqldump<\/a> ci mette a disposizione<br \/>file.sql &#8211; Il nome del file di dump (backup)<\/p><p>Ricordiamo che mysqldump necessita che il server MySQL sia &#8220;up and running&#8221; affinch\u00e8 possa funzionare.<\/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-00688fd akihiro-widget akihiro-widget-heading\" data-id=\"00688fd\" 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 di un singolo database<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-cc9779d akihiro-widget akihiro-widget-text-editor\" data-id=\"cc9779d\" 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\tIl caso d&#8217;uso pi\u00f9 comune dell&#8217;utility mysqldump \u00e8 il backup di un singolo database.\n\nPer esempio, per creare un backup di un database denominato <code>database_name<\/code> usando l&#8217;utente <code>root<\/code> e salvarlo in un file denominato <code>database_name.sql<\/code>, il comando da eseguire \u00e8 il seguente:\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-a298528 akihiro-widget akihiro-widget-code-highlight\" data-id=\"a298528\" 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 \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>mysqldump -u root -p database_name > database_name.sql<\/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-b6e8601 akihiro-widget akihiro-widget-heading\" data-id=\"b6e8601\" 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 di una serie di database<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-5dad3e7 akihiro-widget akihiro-widget-text-editor\" data-id=\"5dad3e7\" 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\tPer eseguire il backup di molteplici database con un unico comando dobbiamo usare l&#8217;opzione <code>--database<\/code> seguita dalla lista dei database oggetto del backup. Ogni database deve essere separato da spazio.\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-5e8ea5b akihiro-widget akihiro-widget-code-highlight\" data-id=\"5e8ea5b\" 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 \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql<\/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-9c5d465 akihiro-widget akihiro-widget-heading\" data-id=\"9c5d465\" 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 di tutti i database<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-6fac778 akihiro-widget akihiro-widget-text-editor\" data-id=\"6fac778\" 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>In questo caso \u00e8 sufficiente utilizzare l&#8217;opzione &#8211;all-databases e tutti i database ospitati sul server MySQL saranno oggetto di backup. Il comando \u00e8 il 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-4b7e29e akihiro-widget akihiro-widget-code-highlight\" data-id=\"4b7e29e\" 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 \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>mysqldump -u root -p --all-databases > all_databases.sql<\/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-34eb451 akihiro-widget akihiro-widget-heading\" data-id=\"34eb451\" 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 di tutti i database su file separati<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-4f62f86 akihiro-widget akihiro-widget-text-editor\" data-id=\"4f62f86\" 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>Purtroppo l&#8217;utility mysqldump non fornisce un&#8217;opzione per eseguire il backup di tutti i database archiviando ogni database su un file dedicato. Tuttavia questo pu\u00f2 facilmente essere raggiunto con un semplice script bash:<\/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-ed9821e akihiro-widget akihiro-widget-code-highlight\" data-id=\"ed9821e\" 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 \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>for DB in $(mysql -e 'show databases' -s --skip-column-names); do\n    mysqldump $DB > \"$DB.sql\";\ndone<\/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-3d28407 akihiro-widget akihiro-widget-text-editor\" data-id=\"3d28407\" 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 comando qui sopra creer\u00e0 un file di dump per ogni singolo database usando come filename il nome del database 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-3006aad akihiro-widget akihiro-widget-heading\" data-id=\"3006aad\" 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\">Creare un backup compresso<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-1e5aacb akihiro-widget akihiro-widget-text-editor\" data-id=\"1e5aacb\" 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 la dimensione del database oggetto di backup \u00e8 molto grande \u00e8 una buona idea comprimere l&#8217;output. Per realizzare questo \u00e8 sufficiente eseguire una pipe dell&#8217;output verso l&#8217;utility gzip e infine ridirezionare il tutto verso un file. Il comando \u00e8 il 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-bff44f4 akihiro-widget akihiro-widget-code-highlight\" data-id=\"bff44f4\" 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 \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>mysqldump database_name | gzip > database_name.sql.gz<\/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-b473460 akihiro-widget akihiro-widget-heading\" data-id=\"b473460\" 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\">Eseguire il restore di un MySQL dump<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-5757b14 akihiro-widget akihiro-widget-text-editor\" data-id=\"5757b14\" 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>Per eseguire il restore di un file di dump di MySQL possiamo utilizzare direttamente la command line utility mysql. Prima di eseguire il restore \u00e8 necessario creare il database.<\/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-79b83e2 akihiro-widget akihiro-widget-code-highlight\" data-id=\"79b83e2\" 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 \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>mysql -u root -p -e \"CREATE DATABASE database_name\";\nmysql -u root -p database_name < database_name.sql<\/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-0f0dd79 akihiro-widget akihiro-widget-heading\" data-id=\"0f0dd79\" 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\">Eseguire il restore di un singolo database da un Full MySQL Dump<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-cb3044c akihiro-widget akihiro-widget-text-editor\" data-id=\"cb3044c\" 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>Per ripristinare un singolo database da un file di dump che contiene il backup di molteplici database (per esempio un file ottenuto con l&#8217;opzione &#8211;all-databases) possiamo utilizzare il seguente comando:<\/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-a378ca3 akihiro-widget akihiro-widget-code-highlight\" data-id=\"a378ca3\" 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 \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>mysql --one-database database_name < all_databases.sql<\/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\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>In questo articolo scopriamo come eseguire il backup e il ripristino di un database MySQL o MariaDB grazie all&#8217;utility a riga di comando mysqldump. Il file di backup ottenuto usando l&#8217;utility mysqldump \u00e8 praticamente un insieme di SQL statements che possono essere usati per ricreare un database identico all&#8217;originale, quindi ripristinandone la struttura. Come nota [&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":[47,16],"class_list":["post-2397","post","type-post","status-publish","format-standard","hentry","category-sistemistica","tag-mysql","tag-sql"],"_links":{"self":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/2397","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=2397"}],"version-history":[{"count":7,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/2397\/revisions"}],"predecessor-version":[{"id":2404,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/2397\/revisions\/2404"}],"wp:attachment":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/media?parent=2397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/categories?post=2397"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/tags?post=2397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}