Python script to automatically backup database to Dropbox

Recently, a big thing happened, that is, the operation and maintenance students of GitLab accidentally deleted the production data. Although GitLab had prepared an astonishing five backup mechanisms, it still caused them to lose nearly 6 hours of data. The damage to user data, and especially their reputation, is simply immeasurable. On reflection, this blog, Becomin' Charles, does not have a complete backup. I am really breaking out in a cold sweat. Mainly considering that this is my personal blog, but thinking about it, I have persisted for almost ten years. If it is really lost, Still very sad.

It just so happens that my wife is learning Python programming recently and I am teaching her. In fact, I am a PHP programmer and I don’t like Python at all. But to be honest, if a layman learns programming, Python is indeed much friendlier than PHP. Too much, I can only recommend her to learn Python. Fortunately, taking this opportunity, I decided to learn Python programming myself, so I decided to use Python to make an automatic backup script for the database. For the backup location, use Dropbox, because my server is provided by Linode, which is located in the Fremont computer room in the United States. It is more appropriate to choose the storage service in the United States. The following is the code I wrote. I am new to Python. Please give me some advice:

#!/usr/bin/python
#coding:utf-8
  
import sys
import os
from yamlimport load
from datetime import datetime
import dropbox
from dropbox.filesimport WriteMode
from dropbox.exceptions import ApiError, AuthError
  
if len(sys.argv) < 2:
  print >>sys.stderr, "Usage: %s <config_file>" % sys.argv[0]
  sys.exit(0)
  
conf = load(file(sys.argv[1], &#39;r&#39;))
  
# config file is a YAML looks like
# ---
# server-name: 127.0.0.1
# local-backup-path: /tmp
# remote-backup-path: /backup
# dropbox-token: jdkgjdkjg
# databases:
#  - host:  localhost
#   port:  3306
#   user:  user
#   pass:  password
#   name:  database1
#   charset: utf8
#  - host:  localhost
#   port:  3306
#   user:  user2
#   pass:  password2
#   name:  database2
#   charset: utf8
  
for dbin conf[&#39;databases&#39;] :
  filename = "%s_%s.sql" % (db[&#39;name&#39;], datetime.now().strftime("%Y%m%d-%H-%M-%S")) 
  filepath = "%s/%s" % (conf[&#39;local-backup-path&#39;], filename)
  cmd = "mysqldump -h%s -u%s -p%s -P%s --single-transaction %s > %s" % (
      db[&#39;host&#39;],
      db[&#39;user&#39;], 
      db[&#39;pass&#39;], 
      db[&#39;port&#39;], 
      db[&#39;name&#39;], 
      filepath
      )
  os.system(cmd)
  cmd = "gzip %s" % filepath
  os.system(cmd)
  filepath = filepath + &#39;.gz&#39;
  dbx = dropbox.Dropbox(conf[&#39;dropbox-token&#39;])
  backuppath = "%s/%s/%s/%s" % (
      conf[&#39;remote-backup-path&#39;],    # remote path
      datetime.now().strftime("%Y%m%d"), # date string
      conf[&#39;server-name&#39;],       # server name
      filename + &#39;.gz&#39;)
  with open(filepath, &#39;rb&#39;) as f:
    time = datetime.now().strftime("%Y-%m-%d %H:%M:%S ")
    print(time + "Uploading " + filepath + " to Dropbox as " + backuppath)
    try:
      dbx.files_upload(f.read(), backuppath, mode=WriteMode(&#39;overwrite&#39;))
    except ApiErroras err:
      # This checks for the specific error where a user doesn&#39;t have
      # enough Dropbox space quota to upload this file
      if (err.error.is_path() and
          err.error.get_path().error.is_insufficient_space()):
        sys.exit("ERROR: Cannot back up; insufficient space.")
      elif err.user_message_text:
        print(err.user_message_text)
        sys.exit()
      else:
        print(err)
        sys.exit()

Briefly describe the idea of ​​​​this code. This program should meet these requirements:

Use mysqldump to back up the database to Local

should support configuration files and allow configuration of multiple databases

can be uploaded to Dropbox

In order to complete these requirements, the first problem encountered is how to support configuration After searching the file, it turns out that there is a default ConfigParser under Python, which can complete this task, but the disgusting thing about normal things is that the configuration file must be organized in [Section] units. In fact, my configuration obviously has some global configurations, and various information in the database is repeated many times. The nesting ability of this kind of configuration file is simply terrible, and it requires a two-layer structure, which is disgusting. So I went online to search for configuration file formats. There were many articles comparing the pros and cons of various configuration files. In fact, there were quite a few articles. I thought about it, and maybe I can write an article about my own feelings in the future. Anyway, many articles finally agree that YAML is the most perfect configuration file. So I decided to use this, and sure enough, there is a ready-made class library, namely PyYAML, which is very convenient. Just two functions, load and dump, can directly convert the file into dict format.

The second problem is uploading to Dropbox. Later I found out that the official provides a rich API, and there is an SDK directly. (What makes me jealous is that the official SDK for PHP is not available. It is so unpopular. See you?), I studied the SDK usage and found that there are code examples directly, so I copied it directly into my code and completed 50% of the code in an instant. It was great!

After the entire code was completed, I found that it didn’t take much time to write the code. Moreover, the way I learned Python, I used to complain that Python’s documentation was difficult to use. I found that, in fact, the best In fact, the more correct way is to use help to query the API in an interactive Shell, and then assist with official documents. This is a place that refreshed my previous understanding. It feels pretty good in practice. Python’s package manager pip is also very useful.

pip install PyYAML
pip install dropbox

For more Python scripts to automatically back up the database to Dropbox, please pay attention to the PHP Chinese website for related articles!