How to read Mysql backup files with Golang parse

preface

The database table was deleted due to the misoperation in the early stage. Although the amount of data was not large, it was difficult to recover through binlog. It was a problem to recover through backup files, and 36 G files were opened.

Restore using backup files

Large file editor, ES11en-ES12en-x86_64-ES14en through the file to open the backup file, although the process is a little slow, but can be opened, and read the editing information normally, to restore the amount of data is not a problem to take, but if the table several hundred thousand rows, the operation is more difficult;

Golang reads the backup file

Golang is used to read, and the advantages of programming language are used to read the backup. After testing to read the specified backup file (about 36GB) table, about 12min is needed. In fact, it is mainly the process of reading and traversing the backup file.

**
* @Author: ws
* @File: main
* @Date: 2019/11/16 0016 14:35
* @Remark: 
*/
var(
 _INSERT_INTO="INSERTINTO${table}VALUES"
 _Dir="temp"
)
var TABLE_CAR="T_SCM_CAR"

func ReadSqlFile(){
 t1 := time.Now() // get current time
 f, err := os.Open("E:\\update\\crm_20191112_210001.sql\\crm_20191112_210001.sql")
 defer f.Close()
 if err != nil {
 }
 buf := bufio.NewReader(f)
 nfs:=getTxtFile(TABLE_CAR)
 for {
  s, err := buf.ReadString('\n')
  if(err!=nil){
   logger.ERROR(err.Error())
   break
  }
  if err == nil {
   if s==""{
    continue
   }
   sqlstr:=getSqlStr(s,TABLE_CAR)
   if(sqlstr!=""){
    nfs.Write([]byte(sqlstr))
   }
  }
 }
 elapsed := time.Since(t1)
 fmt.Println("App elapsed: ", elapsed)
}
/**
 Adjust verification per 1 The row data is formatted with and without default 1 , if 1 Reads to load to 
 */
func getSqlStr(sqlstr,tableName string)string{
 var realTableName=strings.Replace(_INSERT_INTO,"${table}",tableName,-1)
 replaceEmptyStr:=strings.Replace(sqlstr," ","",-1)
 tempS:=strings.ToUpper(strings.Replace(replaceEmptyStr,"`","",-1))
 if(len(tempS)>=len(realTableName)) {
  tabName := tempS[0:len(realTableName)]
  if (tabName ==realTableName) {
   return sqlstr
  }
 }
 return ""
}

/**
 Open the txt file 
 */
func getTxtFile(fileName string)os.File{
 txtFile:=createToFile(fmt.Sprintf("%v/%v.txt",_Dir,fileName))
 nfs, err := os.OpenFile(txtFile.Name(), os.O_APPEND |os.O_RDWR|os.O_CREATE, 0644)
 if err != nil {
  logger.ERROR(fmt.Sprintf(" Failed to open file :%v",err.Error()))
 }
 defer nfs.Close()
 nfs.Seek(0, io.SeekEnd)
 return *nfs
}


func readLine(r *bufio.Reader) (string, error) {
 line, isprefix, err := r.ReadLine()
 for isprefix && err == nil {
  var bs []byte
  bs, isprefix, err = r.ReadLine()
  line = append(line, bs...)
 }
 return string(line), err
}

func createToFile(fileName string)os.File{
 _, err := os.Stat(_Dir)
 if err == nil {
 }
 if os.IsNotExist(err) {
  os.MkdirAll(_Dir, os.ModePerm)
 }
 newFile, err := os.Create(fileName)
 if err != nil {
  logger.ERROR(fmt.Sprintf(" File creation failed :%v",err.Error()))
 }
 defer func() {
  newFile.Close()
 }()
 newFile.WriteString("\xEF\xBB\xBF") //  write UTF-8 BOM To prevent Chinese random code 
 return *newFile
}

func main() {
 ReadSqlFile()
}

conclusion