golang linux/windows下根据日期自动备份,恢复mysql数据表

由于shell不熟，所以用golang进行自动化备份mysql

备份某个数据库下指定日期的表。

比如’tb_20221210' 这样的表名

  1 package main
  2 
  3 import (
  4     "fmt"
  5     "io/ioutil"
  6     "os"
  7     "os/exec"
  8     "time"
  9 )
 10 
 11 /**
 12  *
 13  * 备份MySql数据库
 14  * @param host:         数据库地址:
 15  * @param port:         端口:
 16  * @param user:         用户名:
 17  * @param password:     密码:
 18  * @param databaseName: 需要备份的数据库名:
 19  * @param tableName:    需要备份的表名:
 20  * @param sqlPath:      备份SQL存储路径:
 21  * @return backupPath   返回备份路径
 22  *
 23  */
 24 
 25 const (
 26     BackupPath = "/home/xda/ext/backup"
 27 )
 28 
 29 //备份
 30 func BackupMySqlDb(host, port, user, password, databaseName, tableName string) (error, string) {
 31     //获得一个当前的时间戳
 32 
 33     var backupPath string
 34     // 判断文件夹不存在时自动创建
 35     if !FileExists(BackupPath) {
 36         if err := os.MkdirAll(BackupPath, os.ModePerm); err != nil {
 37             return err, ""
 38         }
 39     }
 40     //设置备份文件的路径
 41     backupPath = BackupPath + tableName + ".sql"
 42     mysqldumpCmd := `mysqldump -h ` + host + ` -P ` + port + ` -u` + user + ` -p` + password + ` ` + databaseName + ` ` + tableName + ` >` + backupPath
 43     //--ignore-table=库名.表名 表示备份忽略该表
 44 
 45     if FileExists(backupPath) {
 46         return fmt.Errorf("已经存在"), backupPath
 47     }
 48     fmt.Println(mysqldumpCmd)
 49     if err := ExecutiveCommand(mysqldumpCmd); err != nil {
 50         return err, ""
 51     }
 52 
 53     return nil, backupPath
 54 }
 55 
 56 //恢复数据表
 57 func RecoverMySqlDb(host, port, user, password, databaseName, backupPath string) error {
 58     //恢复表 mysql -h[地址] -P[端口] -u[用户名] -p[密码] [数据库名] <[备份文件]
 59     mysqldumpCmd := `mysql -h` + host + ` -P` + port + ` -u` + user + ` -p` + password + ` ` + databaseName + ` <` + backupPath
 60     if err := ExecutiveCommand(mysqldumpCmd); err != nil {
 61         return err
 62     }
 63     return nil
 64 }
 65 
 66 func ExecutiveCommand(command string) error {
 67     //需要执行命令:command
 68     cmd := exec.Command("/bin/bash", "-c", command)
 69     // 获取管道输入
 70     output, err := cmd.StdoutPipe()
 71     if err != nil {
 72         return err
 73     }
 74     if err := cmd.Start(); err != nil {
 75         return err
 76     }
 77     _, err = ioutil.ReadAll(output)
 78     if err != nil {
 79         return err
 80     }
 81     if err := cmd.Wait(); err != nil {
 82         return err
 83     }
 84     return nil
 85 }
 86 
 87 //检查文件或文件夹是否存在
 88 func FileExists(path string) bool {
 89     _, err := os.Stat(path) //os.Stat获取文件信息
 90     if err != nil {
 91         return os.IsExist(err)
 92     }
 93     return true
 94 }
 95 
 96 func parseTime(s string) string {
 97     p, _ := time.Parse("20060102", s)
 98     next_date := p.AddDate(0, 0, 1)
 99     return next_date.Format("20060102")
100 }
101 
102 func GenDate() []string {
103     dateList := []string{}
104     startDate := "20220705"
105 
106     for i := 0; i < 10; i++ {
107         startDate = parseTime(startDate)
108         full_path := "_tick_" + startDate
109         dateList = append(dateList, full_path)
110     }
111     return dateList
112 }
113 
114 func main() {
115     host := ""
116     password := ""
117     user := "root"
118     port := ""
119     db := ""
120 
121     backup := true
122 
123     dateList := GenDate()
124     fmt.Println(dateList)
125     if backup == true {
126         for _, date := range dateList {
127 
128             err, _ := BackupMySqlDb(host, port, user, password, db, date)
129             if err != nil {
130                 fmt.Println(date + "backup failed!")
131             }
132         }
133     } else {
134         for _, date := range dateList {
135 
136             err := RecoverMySqlDb(host, port, user, password, db, path)
137             if err != nil {
138                 fmt.Println(date + "recovery failed!")
139             }
140         }
141     }
142 
143 }