The Python script implements automatic database backup to Dropbox

Recently, a big event happened, that is, the operation and maintenance students of GitLab accidentally deleted the production data. Although GitLab has prepared 5 backup mechanisms in an appalling way, they still lost nearly 6 hours of user data, especially the damage to their reputation, which is incalculable. Reflection 1, this blog Becomin' Charles, there is no perfect backup, is really cold sweat, mainly considering that this is my personal blog, but think about it has been nearly 10 years, if really lost it, or very sad.

As it happens, my wife is learning Python programming recently, and I am teaching her. In fact, I am a programmer of PHP, and I don't like Python at one point. But to be honest, if a layman learns programming, Python is indeed more friendly than PHP, so I can only recommend her to learn Python. At that time, I decided to learn Python programming myself, so I decided to use Python to make an automatic backup script for a database. For the backup location, please use Dropbox, because my server is provided by Linode, fremont computer room in the United States, so it is more appropriate to choose the storage service in the United States. The following is the code I wrote, Python small white, please comment:

#!/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], 'r'))
 
# 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['databases'] :
  filename = "%s_%s.sql" % (db['name'], datetime.now().strftime("%Y%m%d-%H-%M-%S")) 
  filepath = "%s/%s" % (conf['local-backup-path'], filename)
  cmd = "mysqldump -h%s -u%s -p%s -P%s --single-transaction %s > %s" % (
      db['host'],
      db['user'], 
      db['pass'], 
      db['port'], 
      db['name'], 
      filepath
      )
  os.system(cmd)
  cmd = "gzip %s" % filepath
  os.system(cmd)
  filepath = filepath + '.gz'
  dbx = dropbox.Dropbox(conf['dropbox-token'])
  backuppath = "%s/%s/%s/%s" % (
      conf['remote-backup-path'],    # remote path
      datetime.now().strftime("%Y%m%d"), # date string
      conf['server-name'],       # server name
      filename + '.gz')
  with open(filepath, 'rb') 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('overwrite'))
    except ApiErroras err:
      # This checks for the specific error where a user doesn'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 the following requirements:

Back up the database locally using mysqldump Configuration files should be supported to allow multiple databases to be configured It can be uploaded to Dropbox

In order to complete these requirements, the first problem I encountered was how to support the configuration file. In the first place, there is a default ConfigParser under Python, which can accomplish this task. However, the normal thing is that the configuration file must be organized in [Section]. In fact, my configuration obviously has some global configuration, and the fact that the database information is repeated many times, this configuration file, the nesting ability is terrible, must have a two-tier structure, is disgusting. So I went to the Internet to search the format of the configuration file, a lot of articles compared the pros and cons of various configuration files, in fact, this article is quite a lot, I think, later maybe I can write an article about my own feelings. Anyway, many articles end up agreeing that YAML is the perfect configuration file. So I decided to use this as well. Sure enough, there is a ready-made class library, PyYAML, which is very convenient. The two functions, load and dump, directly changed the file into dict format.

The second problem is to upload Dropbox, but it turns out that the government provides abundant API, and SDK is directly available. (to my envy, the government doesn't even have SDK of PHP. , studied the usage of SDK, found that there was a code example directly, so I directly copied it into my code, and instantly completed 50% of the code, cool!

After the completion of the whole code, I found that it did not take much time to write code 1, and I learned the Python way, I complained about the Python document is difficult to use before 1, I found that, in fact, the best way is actually in the interactive Shell, help to query API, and assisted by the official documents, is the more correct way. This is a new place that I knew before. It feels good in practice. Python's package manager, pip, is also easy to use.

pip install PyYAML
pip install dropbox