Operation Method of golang Realizing mysql Database Backup

Background

navicat is the best of the mysql visualizers, but when it comes to importing and exporting views, it is done in alphabetical order of view names, and errors are reported during importing if the views have dependencies. I have written one with python before, but when I encounter xfffd coding, pymysql of python will crash directly. I found that golang didn't have this problem, so I just rewrote it with go to get familiar with golang.

1 Some key points

map & In json, json data structure is needed to store intermediate results when processing primary key and foreign key information. Because of flexible processing, map [string] interface {} can only be used in golang. interface {} is equivalent to object in java, which can accept any data type, but it is convenient to know fairly well when using it, otherwise, the program will crash if the data types do not match. The placeholders in xfffd, utf8 and utf8mb4 out of range will be stored as xfffd after being put into storage, and need to be filtered out when exporting data. The concurrency support of goroutine and golang is unique. Our tool supports simultaneous backup of multiple libraries, so it is easy to use goroutine to realize concurrency.

Code parsing

Explain the core code by functional module

main. go, concurrent, command line arguments

Use command line arguments and accept 1 parameter to specify the contents of the backup

package common

type OpFlag struct {
  Tables bool      // Table structure 
  Datum bool       // Table structure and data 
  Views bool       // View 
  Funcs bool       // Functions and stored procedures 
}

main. go, program entry, processing command line parameters

 if len(os.Args) > 1 {
    flag = common.OpFlag{
      Tables: false,
      Datum: false,
      Views: false,
      Funcs: false,
    }
    switch os.Args[1] {     // Accept 1 Parameters 
    case "table":
      flag.Tables = true    // Set the identification amount according to the parameters 
    case "data":
      flag.Tables = true
      flag.Datum = true
    case "views":
      flag.Views = true
    case "funcs":
      flag.Funcs = true
    default:          // Incorrect parameters, report error and exit 
      log.Fatal("You arg must be in : table, data, views or funcs.")
    }
  }else{            // No parameters, export all by default 
    flag = common.OpFlag{
      Tables: true,
      Datum: true,
      Views: true,
      Funcs: true,
    }
  }
  err := backUp.Export(flag)  Backup database according to parameters 

Export.go

Backup main flow, according to configs. json to generate goroutine backup database, and wait for completion.

var configs interface{}
  fr, err := os.Open("./configs.json")
  if err != nil {
    return err
  }
  decoder := json.NewDecoder(fr)         // Parsing Configuration File 
  err = decoder.Decode(&configs)
  confs := configs.(map[string]interface{})
  workDir := confs["workDir"].(string)
  ch := make(chan string)            // Channel variable 
  for key, value := range confs {
    if strings.HasPrefix(key, "db_") {
      dbConf := value.(map[string]interface{})
      dbConn := common.DbConnFields{   // Database corresponding configuration 
        DbHost:  dbConf["db_host"].(string),
        DbPort:  int(dbConf["db_port"].(float64)),
        DbUser:  dbConf["db_user"].(string),
        DbPass:  dbConf["db_pass"].(string),
        DbName:  dbConf["db_name"].(string),
        DbCharset: dbConf["db_charset"].(string),
      }
      if dbConf["file_alias"] != nil {  // Generate sql Naming of backup files 
        dbConn.FileAlias = dbConf["file_alias"].(string)
      }
      go ExportOne(dbConn, workDir, ch, flag) // Create a cooperative process 
    }
  }
  for key := range confs {            // Block the main process until all the co-processes finish their work 
    if strings.HasPrefix(key, "db_") {
      fmt.Print( <- ch )
    }
  }
  return nil

You need to write the following configuration file to describe the database you want to back up:

{
  "db_name1": {
    "db_host": "192.168.1.8",
    "db_port": 3306,
    "db_user": "root",
    "db_pass": "123456",
    "db_name": "name1",
    "db_charset": "utf8mb4",
    "file_alias": "file name1"
  },
  "db_name2": {
    "db_host": "localhost",
    "db_port": 3306,
    "db_user": "root",
    "db_pass": "123456",
    "db_name": "name2",
    "db_charset": "utf8mb4"
  },
  "database_dialect": "mysql",
  "workDir": "/home/zhoutk/gocodes/goTools/"
}

ExportOne.go

Back up 1 database

fileName  : = fields.FileAlias
  setSqlHeader(fields, fileName)      // Set export file description 
  if flag.Tables {            // Export table structure if table is set to true 
    err := exportTables(fileName, fields, flag)  // Please refer to the source code for specific algorithms 
    if err != nil {
      ch <- fmt.Sprintln("Error: ", fields.DbName, "\t export tables throw, \t", err)
      return
    }
  }
  if flag.Views {            // If the view is set to true, export the view 
    err := exportViews(fileName, fields)// Please refer to the source code for specific algorithms, or python Algorithm 
    if err != nil {
      ch <- fmt.Sprintln("Error: ", fields.DbName, "\t export views throw, \t", err)
      return
    }
  }
  if flag.Funcs {        // If the function is set to true, export the function and stored procedure 
    err := exportFuncs(fileName, fields)// Please refer to the source code for specific algorithms 
    if err != nil {
      ch <- fmt.Sprintln("Error: ", fields.DbName, "\t export funcs throw, \t", err)
      return
    }
  }
  // Export is complete, enter information into the channel 
  ch <- fmt.Sprintln("Export ", fields.DbName, "\t success at \t", time.Now().Format("2006-01-02 15:04:05"))  

MysqlDao.go

General encapsulation of database query, this tool only uses ExecuteWithDbConn. Flexible use of map and interface {}, the results are converted into key-value objects returned.

func ExecuteWithDbConn(sql string, values []interface{}, fields common.DbConnFields) (map[string]interface{}, error) {
  rs := make(map[string]interface{})
  dao, err := mysql.Open("mysql", fields.DbUser + ":"+fields.DbPass+"@tcp("+fields.DbHost+":"+
    strconv.Itoa(fields.DbPort)+")/"+fields.DbName+"?charset="+fields.DbCharset)
  defer dao.Close()
  if err != nil {
    rs["code"] = 204
    return rs, err
  }
  stmt, err := dao.Prepare(sql)
  if err != nil {
    rs["code"] = 204
    return rs, err
  }
  rows, err := stmt.Query(values...)
  if err != nil {
    rs["code"] = 204
    return rs, err
  }
  columns, err := rows.Columns()    // Take out the field name 
  vs := make([]mysql.RawBytes, len(columns))
  scans := make([]interface{}, len(columns))
  for i := range vs {         // Preset value address 
    scans[i] = &vs[i]
  }
  var result []map[string]interface{}
  for rows.Next() {
    _ = rows.Scan(scans...)    // Into 1 Column value 
    each := make(map[string]interface{})
    for i, col := range vs {
      if col != nil {
        each[columns[i]] = FilterHolder(string(col)) // Filter /xfffd
      }else{
        each[columns[i]] = nil
      }
    }
    result = append(result, each)
  }
  rs["code"] = 200
  //data, _ := json.Marshal(result)
  rs["rows"] = result
  return rs, err
}

Project address

https://github.com/zhoutk/goTools

Usage

git clone https://github.com/zhoutk/goTools
cd goTools
go get
go run main.go
go buid main.go
./main         #export all things of database
./main table      #export tables
./main data       #export tables & data
./main views      #export views
./main funcs      #export funcs & stored procedures

Summarize