{"id":275,"date":"2022-05-18T11:26:08","date_gmt":"2022-05-18T11:26:08","guid":{"rendered":"http:\/\/checkmateq.com\/blog\/?p=275"},"modified":"2024-10-19T17:24:33","modified_gmt":"2024-10-19T17:24:33","slug":"automate-database-backup","status":"publish","type":"post","link":"https:\/\/www.checkmateq.com\/blog\/automate-database-backup","title":{"rendered":"Python Script To Automate Databases Backup"},"content":{"rendered":"

Database backup is an important aspect of infrastructure management, and it has to be part of AWS Cloud infrastructure automation services<\/a> so that it can be easier for remote cloud engineers<\/a> to restore the database backup during an incident.<\/p>\n

Therefore, we have created a sample Python<\/a> script to automate three databases Mysql, MongoDB, and PostgreSQL present on a remote server and store those database backup files on an AWS s3 bucket.<\/p>\n

Step1: <\/b>We will store our database backup files in an s3 bucket directly from our remote server. First, we will connect to our remote server with the help of the “paramiko module”. Here we have enabled password-less authentication, so you only need a username to connect to the remote server.<\/p>\n

\"\"<\/p>\n

Step 2: First, we’ll define the function mysql_dump() to use mysqldump to backup the Mysql database. <\/b>Define a single command to first take a dump of the database, then take the output in gzip format and upload it to the s3 bucket.<\/p>\n

\"\"<\/p>\n

Step 3: Using mongodump, we will create a backup of the MongoDB database.\u00a0<\/b>Define the function mongo_dump().<\/p>\n

\"\"<\/p>\n

Step 4: Create a function called pg_dump().\u00a0<\/b>backup with the help of the pgdump command with output in tar format. Because there is no option to pass the password from the command line in pgdump, we must define it separately with “PGPASSWORD.”<\/p>\n

\"\"<\/p>\n

Please consider a complete script to automate database backups to better manage the infrastructure during an unexpected incident or software product release.<\/p>\n

#!\/usr\/bin\/python3\r\n\r\nimport paramiko\r\nimport os\r\nimport subprocess\r\nimport datetime\r\nhosts=[\"test2\"]\r\ndb_host=\"localhost\"\r\nuser_name=\"vagrant\"\r\nPGPASSWORD=[\"Vagrant@18\"]\r\ndb_user=\"admin\"\r\npassword=\"vagrant@18\"\r\nnow = datetime.datetime.now()\r\ntimestamp = str(now.strftime(\"%Y%m%d_%H:%M:%S\"))\r\n\r\nfor i in hosts:\r\nname =i\r\ncommand = ['ping', '-c', '2', i]\r\np= subprocess.Popen(command, stdout=subprocess.DEVNULL,stderr=subprocess.DEVNULL)\r\np.wait()\r\nif p.poll():\r\nprint(f'{i} server is down')\r\nelse:\r\nssh_client=paramiko.SSHClient()\r\nssh_client.load_system_host_keys()\r\nssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())\r\nssh_client.connect(hostname=i,username=user_name)\r\n\r\ndef mysql_dump():\r\n\r\nprint(\"Taking MYSQL DB Backup\")\r\ncmd = \"mysqldump -h %s -u %s -p%s --all-databases --single-transaction --flush-logs --master-data=2 | gzip > mysql_full_bkup_%s.gz | aws s3 cp - s3:\/\/chkmt-bucket\/%s\/dump\/mysql\/mysql_full_bkup_%s.gz \" % (db_host, db_user, password, timestamp, name, timestamp)\r\nstdin,stdout,stderr=ssh_client.exec_command(cmd)\r\nx = stdout.channel.recv_exit_status()\r\nif x==0:\r\nprint(\"MYSQL Backup Successful\")\r\nelse:\r\n\r\nprint(\"MYSQL Backup Failed\")\r\n\r\ndef mongo_dump():\r\nprint(\"\\nTaking MONGO DB Backup\")\r\ncmd = \"mongodump --host=%s -u %s -p %s --out- | gzip > mongo_full_bkup_%s.gz | aws s3 cp - s3:\/\/chkmt-bucket\/%s\/dump\/mongod\/mongo_full_bkup_%s.gz\" % (db_host, db_user, password, timestamp, name, timestamp)\r\nstdin,stdout,stderr=ssh_client.exec_command(cmd)\r\ny = stdout.channel.recv_exit_status()\r\nif y==0:\r\nprint(\"MONGODB Backup Successful\")\r\nelse:\r\nprint(\"MONGODB Backup Failed\")\r\n\r\ndef pgres_dump():\r\nprint(\"\\nTaking POSTGRES DB Backup\")\r\ncmd = \"pg_dump -h %s -U %s -F t chkmtq > postgres_full_bkup_%s.tar | aws s3 cp - s3:\/\/chkmt-bucket\/%s\/dump\/postgres\/postgres_full_bkup_%s.tar\" % (db_host, db_user,timestamp, name, timestamp)\r\n\r\nstdin,stdout,stderr=ssh_client.exec_command(cmd)\r\nz = stdout.channel.recv_exit_status()\r\nif z==0:\r\nprint(\"POSTGRES Backup Successful\")\r\nelse:\r\nprint(\"POSTGRES Backup Failed\")\r\n\r\nmysql_dump()\r\nmongo_dump()\r\npgres_dump()\r\n<\/pre>\n
Eventually, Please schedule this script in the crontab so that it can run automatically every week. For instance, in the following example, this script would run every Sunday at 12AM.<\/p>\n
\"\"<\/p>\n
 <\/p>\n
Does Checkmate have the right answer for every\u00a0infrastructure issue?<\/b><\/p>\n
We have experienced DevOps engineers to manage daily IT operations, optimize costs, and automate infrastructure. Our cloud computing services can help you stabilize your infrastructure for faster product releases. If you need dependable cloud and DevOps expertise, look no further. We have an experienced DevOps engineering team to achieve business objectives faster.<\/p>\n
Contact us<\/a>\u00a0 with our Virtual CTO Services<\/a> to learn about effective cloud strategy services in India, product management,\u00a0 cloud engineering<\/a>, Technology Architecture Consulting<\/a>, IT Staff Augmentation Services in India<\/a> , Hire Software Developers in India<\/a>\u00a0 and DevOps management services<\/a>. We can help you hire experienced cloud engineer to manage DevOps infrastructure stack.<\/p>\n","protected":false},"excerpt":{"rendered":"
Database backup is an important aspect of infrastructure management, and it has to be part of AWS Cloud infrastructure automation services so that it can be easier for remote cloud engineers to restore the database backup during an incident. Therefore, we have created a sample Python script to automate three databases Mysql, MongoDB, and PostgreSQL … Continue reading “Python Script To Automate Databases Backup”<\/span><\/a><\/p>\n","protected":false},"author":3,"featured_media":300,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[3,12,7,11,8,13,6],"_links":{"self":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/275"}],"collection":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/comments?post=275"}],"version-history":[{"count":27,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/275\/revisions"}],"predecessor-version":[{"id":4624,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/posts\/275\/revisions\/4624"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/media\/300"}],"wp:attachment":[{"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/media?parent=275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/categories?post=275"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.checkmateq.com\/blog\/wp-json\/wp\/v2\/tags?post=275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}