{"id":15913,"date":"2025-02-01T09:49:28","date_gmt":"2025-02-01T07:49:28","guid":{"rendered":"https:\/\/www.beseit.net\/?p=15913"},"modified":"2025-02-01T11:54:03","modified_gmt":"2025-02-01T09:54:03","slug":"configuracio-de-la-connexio-a-la-base-de-dades-script","status":"publish","type":"post","link":"https:\/\/www.beseit.net\/?p=15913","title":{"rendered":"Backup de BD amb Script"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>import subprocess\nimport os\nimport datetime\n\n# Configuraci\u00f3 de la connexi\u00f3 a la base de dades\nDB_HOST = 'DB_HOST'  # Host de la base de dades\nDB_USER = 'DB_USER'  # Usuari de la base de dades\nDB_PASS = 'DB_PAS'   # Contrasenya de la base de dades\nDB_NAME = 'polidic_cat'  # Nom de la base de dades\nBACKUP_PATH = '.\/backups\/'  # Directori on es desaran les c\u00f2pies de seguretat\n\n# Crear el directori de backups si no existeix\nif not os.path.exists(BACKUP_PATH):\n    os.makedirs(BACKUP_PATH)\n\n# Generar el nom del fitxer de backup amb la data actual\ndate = datetime.datetime.now().strftime('%Y-%m-%d_%H-%M-%S')\nfile_name = f\"{DB_NAME}_{date}.sql\"\nbackup_file_path = os.path.join(BACKUP_PATH, file_name)\n\n# Comanda per fer el backup de la base de dades (per MySQL)\ndump_cmd = f\"mysqldump -h {DB_HOST} -u {DB_USER} -p{DB_PASS} {DB_NAME} &gt; {backup_file_path}\"\n\n# Executar la comanda\ntry:\n    subprocess.run(dump_cmd, shell=True, check=True)\n    print(f\"C\u00f2pia de seguretat creada correctament: {backup_file_path}\")\nexcept subprocess.CalledProcessError as e:\n    print(f\"Error al crear la c\u00f2pia de seguretat: {e}\")\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Per realitzar una c\u00f2pia de seguretat di\u00e0ria d&#8217;una base de dades utilitzant Python, pots fer servir llibreries com <code>subprocess<\/code> per invocar les eines natives de la teva base de dades (com <code>mysqldump<\/code> per MySQL o <code>pg_dump<\/code> per PostgreSQL) i guardar el resultat en un fitxer. A continuaci\u00f3, et mostro un exemple de script que faria aquesta tasca per una base de dades MySQL.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Exemple de Script en Python per Fer una C\u00f2pia de Seguretat Di\u00e0ria<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>import subprocess\nimport os\nimport datetime\n\n# Configuraci\u00f3 de la connexi\u00f3 a la base de dades\nDB_HOST = 'DB_HOST'  # Host de la base de dades\nDB_USER = 'DB_USER'  # Usuari de la base de dades\nDB_PASS = 'DB_PAS'   # Contrasenya de la base de dades\nDB_NAME = 'polidic_cat'  # Nom de la base de dades\nBACKUP_PATH = '.\/backups\/'  # Directori on es desaran les c\u00f2pies de seguretat\n\n# Crear el directori de backups si no existeix\nif not os.path.exists(BACKUP_PATH):\n    os.makedirs(BACKUP_PATH)\n\n# Generar el nom del fitxer de backup amb la data actual\ndate = datetime.datetime.now().strftime('%Y-%m-%d_%H-%M-%S')\nfile_name = f\"{DB_NAME}_{date}.sql\"\nbackup_file_path = os.path.join(BACKUP_PATH, file_name)\n\n# Comanda per fer el backup de la base de dades (per MySQL)\ndump_cmd = f\"mysqldump -h {DB_HOST} -u {DB_USER} -p{DB_PASS} {DB_NAME} &gt; {backup_file_path}\"\n\n# Executar la comanda\ntry:\n    subprocess.run(dump_cmd, shell=True, check=True)\n    print(f\"C\u00f2pia de seguretat creada correctament: {backup_file_path}\")\nexcept subprocess.CalledProcessError as e:\n    print(f\"Error al crear la c\u00f2pia de seguretat: {e}\")<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Explicaci\u00f3 del Script:<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Configuraci\u00f3 de la Connexi\u00f3<\/strong>:<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Defineix els par\u00e0metres de connexi\u00f3 (<code>DB_HOST<\/code>, <code>DB_USER<\/code>, <code>DB_PASS<\/code>, <code>DB_NAME<\/code>) per accedir a la base de dades.<\/li>\n\n\n\n<li>Especifica el directori on es desaran les c\u00f2pies de seguretat (<code>BACKUP_PATH<\/code>).<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Creaci\u00f3 del Directori de Backups<\/strong>:<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Si el directori de backups no existeix, el crea autom\u00e0ticament.<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Nom del Fitxer de Backup<\/strong>:<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>El nom del fitxer inclou la data i hora actual per assegurar que cada c\u00f2pia de seguretat tingui un nom \u00fanic.<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Comanda <code>mysqldump<\/code><\/strong>:<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>La comanda <code>mysqldump<\/code> \u00e9s una eina proporcionada per MySQL per exportar una base de dades en format SQL.<\/li>\n\n\n\n<li>La comanda s&#8217;executa mitjan\u00e7ant <code>subprocess.run<\/code>, que permet executar comandes del sistema des de Python.<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Gesti\u00f3 d&#8217;Errors<\/strong>:<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Si la comanda falla, es captura l&#8217;error i es mostra un missatge d&#8217;alerta.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Automatitzaci\u00f3 del Script<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Per fer que el script s&#8217;executi di\u00e0riament, pots configurar-lo com una tasca programada:<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">En Linux (usant <code>cron<\/code>):<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Obre el crontab:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>   crontab -e<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>Afegeix la seg\u00fcent l\u00ednia per executar el script cada dia a les 02:00 AM:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>   0 2 * * * \/usr\/bin\/python3 \/ruta\/al\/tu\/script.py<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">En Windows (usant Task Scheduler):<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Obre \u00abTask Scheduler\u00bb.<\/li>\n\n\n\n<li>Crea una nova tasca i configura-la per executar el script cada dia a l&#8217;hora desitjada.<\/li>\n\n\n\n<li>Assegura&#8217;t que l&#8217;interpreti Python estigui disponible des de la l\u00ednia d&#8217;ordres o especifica la ruta completa al teu script.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Notes Importants:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Assegura&#8217;t que l&#8217;usuari de la base de dades t\u00e9 prou permisos per fer c\u00f2pies de seguretat.<\/li>\n\n\n\n<li>Si utilitzes una base de dades diferent de MySQL (com PostgreSQL), has de canviar la comanda <code>mysqldump<\/code> per la corresponent (per exemple, <code>pg_dump<\/code> per PostgreSQL).<\/li>\n\n\n\n<li>Guarda el fitxer de contrasenya en un lloc segur i considera l&#8217;\u00fas de variables d&#8217;entorn o eines de gesti\u00f3 de secrets per protegir les credencials.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Aquest script \u00e9s una soluci\u00f3 simple per\u00f2 efica\u00e7 per automatitzar les c\u00f2pies de seguretat de la teva base de dades.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><mark style=\"background-color:rgba(0, 0, 0, 0);color:#f90606\" class=\"has-inline-color\">L&#8217;aplicaci\u00f3 <strong>Task Scheduler <\/strong>de Synology pot crear una tasca que executi un script similar al descrit anteriorment.<\/mark><\/h2>\n\n\n\n<h4 class=\"wp-block-heading\">Passos:<\/h4>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>Crea un script bash que utilitzi <code>mysqldump<\/code> per fer la c\u00f2pia de seguretat (com l&#8217;exemple anterior).<\/li>\n\n\n\n<li>Desa el script en una ubicaci\u00f3 accessible des del teu DiskStation.<\/li>\n\n\n\n<li>Obre <strong>Control Panel > Task Scheduler.<\/strong><\/li>\n\n\n\n<li>Crea una nova tasca:\n<ul class=\"wp-block-list\">\n<li>Tipus: Script personalitzat.<\/li>\n\n\n\n<li>Acci\u00f3: Executa el teu script.<\/li>\n\n\n\n<li>Programaci\u00f3: Defineix l&#8217;hora i la freq\u00fc\u00e8ncia (di\u00e0ria, setmanal, etc.).<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Per realitzar una c\u00f2pia de seguretat di\u00e0ria d&#8217;una base de dades utilitzant Python, pots fer servir llibreries com subprocess per invocar les eines natives de la teva base de dades (com mysqldump per MySQL o pg_dump per PostgreSQL) i guardar &hellip; <a href=\"https:\/\/www.beseit.net\/?p=15913\">Continua llegint <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":8179,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-15913","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bloc-de-notes"],"_links":{"self":[{"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/posts\/15913","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=15913"}],"version-history":[{"count":3,"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/posts\/15913\/revisions"}],"predecessor-version":[{"id":15918,"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/posts\/15913\/revisions\/15918"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/media\/8179"}],"wp:attachment":[{"href":"https:\/\/www.beseit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=15913"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=15913"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=15913"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}