Golang Implementing MySQL Database backup

Background

Navicat is the best of the MySQL visualizer, but in terms of the import and export of the view, it is handled in alphabetical order by the view name, and when the view is dependent, it will be an error during the import process. The previous one has been written in Python, but in the process of use, encountered XFFFD encoding, Python pymysql will crash directly. Found that Golang does not have this problem, just use go rewrite, to familiarize with Golang.

Some key points

1. Map & JSON, when dealing with primary key and foreign key information, we need to use JSON data structure to store intermediate result, because it is flexible to handle, can only be handled by map[string]interface{} in Golang.
2. interface{} is equivalent to object in Java, can accept any data type, convenient but when used to be aware of, otherwise, once the data type does not match, the program will crash.
3. XFFFD, UTF8 in the placeholder, out of range of UTF8MB4 storage, will be stored as XFFFD, data export, need to filter out.
4. Goroutine, Golang's concurrency support is unique, and our tools enable multiple libraries to be backed up at the same time, so it's easy to use goroutine for parallelism.

Code parsing

Description of core code by function module

Main.go, concurrency, command-line arguments

Use command-line arguments to accept a parameter to specify the contents of the backup
Package common

type OpFlag struct { Tables bool //表结构 Datum bool //表结构和数据 Views bool //视图 Funcs bool //函数与存储过程}

Main.go, program entry, processing command-line arguments

    If Len (OS. Args) >1 {Flag = Common. opflag{Tables:False, Datum:False, Views:False, Funcs:False,}Switch OS. args[1] {Accept a parameterCase"Table":Flag. Tables =TrueSet the identification amount according to the parameterCase"Data":Flag. Tables =TrueFlag. Datum =TrueCase "views": flag. views = True case "Funcs": flag. Funcs = true default: //parameter is incorrect, error exits log. Fatal ("You arg must is in:table, data, views or Funcs.")}} else{ //No parameter, all flag = Common is exported by default . opflag{Tables: True, Datum: True, views : True, Funcs: True,}} ERR: = Backup.export (flag) in accordance with parameter Row database backup                   

Export.go

Back up the main process, build goroutine according to Configs.json to back up the database, and wait for it to complete.

var configsinterface{} fr, err: = OS. Open ("./configs.json")If Err! =Nil {Return ERR} Decoder: = JSON. Newdecoder (FR)Parse config file Err = decoder. Decode (&configs) Confs: = configs. (map[Stringinterface{}) Workdir: = confs["Workdir"]. (String) ch: =MakeChanStringChannel variablesFor key, Value: =Range Confs {If strings. Hasprefix (Key,"Db_") {dbconf: = value. (map[Stringinterface{}) 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 a SQL backup file named Dbconn.filealias = Dbconf[ "File_alias"]. (string)} go exportone (dbconn, Workdir, CH, flag) //create co-process}} for Key: = range confs {//block the main process, waiting for all the threads to complete the 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", Span class= "hljs-attr" > "File_alias":  "file Name1"},  "db_name2": {  "Db_host":  "localhost",  "Db_port": Span class= "Hljs-number" >3306,  "Db_user":  "root", " Db_pass ": " 123456 ", " db_name ":  "name2",  "Db_charset":  "UTF8MB4"}, Span class= "hljs-attr" > "Database_dialect":  "MySQL",  "Workdir": Span class= "hljs-string" > "/home/zhoutk/gocodes/gotools/"}        

Exportone.go

Back up a database

FileName: = Fields. Filealias setsqlheader (fields, FileName)Set Export File descriptionIf flag. Tables {Export table structure If table is set to true err: = Exporttables (FileName, fields, flag)For specific algorithms, please refer to the source codeIf 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 view err: = Exportviews (FileName, fields)Refer to the source code, or the Python algorithm for the specific algorithmIf err! = Nil {ch <-fmt. Sprintln ( "Error:", Fields "\ t export views throw, \ t", err) return}} if flag//if function is set to true, export function and stored procedure err: = Exportfuncs (FileName, fields) //specific algorithm please refer to source code if err! = Nil {ch <-fmt. Sprintln ( "Error:", Fields "\ t export funcs throw, \ t", err) return}} //export work completed, Enter information into the channel CH <-FMT. Sprintln ( "Export", Fields "\ t success at \ T", time. Now (). Format ( "2006-01-02 15:04:05"))        

Mysqldao.go

Database query generic encapsulation, this tool uses only executewithdbconn. Use map and interface{} flexibly to convert the result to a key-value object return.

FuncExecutewithdbconn(SQLstring, values []interface{}, Fields common. Dbconnfields)(map[Stringinterface{}, Error) {rs: =Makemap[Stringinterface{}) 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"] =204Return RS, err} stmt, err: = DAO. Prepare (SQL)If Err! =Nil {rs["Code"] =204Return RS, err} rows, err: = stmt. Query (Values ...)If Err! =Nil {rs["Code"] =204Return RS, err} columns, err: = rows. Columns ()Remove 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[Stringinterface{}For rows. Next () {_ = rows. Scan (Scans ...)//Required into a 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"] =  //data, _: = json. Marshal (Result) rs["Rows"] = result return RS, err}     
                                                                      

Project Address

https://github.com/zhoutk/goTools

How to use

git clone https://github.com/zhoutk/goToolscd goToolsgo getgo run main.gogo 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

Summary

Familiar with the Golang language, understand a new concurrency programming model, to create a convenient tool for themselves.

Golang Implementing MySQL Database backup