简介

常见数据库备份方法

SELECT * FROM account_auth INTO OUTFILE 'yourpath' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

以上命令将查询结果导出为csv文件。

使用mysqldump命令备份指定的表

mysqldump -h host -Pyourport -u yourdatabase -p'youpassword' --fields-terminated-by=',' --fields-enclosed-by='"' --fields-escaped-by='\\' --tab=/yourpath database table;

以上两种方式要求登录数据库的账号拥有数据导出权限，否则导出会失败。

go实现mysql数据库导出


package database  
  
import (  
"fmt"  
"os"  
"strings"  
)  
  
//var log = logger.GetLogger()  
  
func Backup(sql string, backupFile string) {  
log.WithField("sql", sql).WithField("backupFilename", backupFile).Info("begin to backup sql")  
  
if !strings.Contains(sql, "limit") {  
if strings.Contains(sql, ";") {  
strings.Replace(sql, ";", " ", -1)  
}  
sql = fmt.Sprintf("%s %s", sql, "limit 10;")  
}  
  
sqlDB, err := GetDB().DB()  
defer func() {  
err = CloseDB()  
if err != nil {  
log.Fatalf("close database failed %s", dsn)  
}  
}()  
  
if err != nil {  
log.Fatalf("get sql db failed")  
}  
  
rows, err := sqlDB.Query(sql)  
if err != nil {  
log.Fatalf("query database failed", err)  
}  
defer rows.Close()  
  
columns, _ := rows.Columns()
//values用于存储数据库的查询结果
values := make([]interface{}, len(columns))
//valuePtrs用于存储scan指针
valuePtrs := make([]interface{}, len(columns))  
  
for i := range columns {  
valuePtrs[i] = &values[i]  
}  
  
var content string  
for rows.Next() {  
//columns, _ := rows.Columns()  
err := rows.Scan(valuePtrs...)  
if err != nil {  
log.Fatalf("get data from rows failed ", err)  
}  
  
rowData := make([]string, len(columns))  
for i, val := range values {  
if val == nil {  
rowData[i] = "NULL"  
} else {  
//将查询结果转成string，否则输出为asicc码
rowData[i] = fmt.Sprintf("%s", val)  
}  
}  
  
result := strings.Join(rowData, ",")  
result += "\n"  
log.Info("the row result: %s", result)  
content += result  
  
}  
  
err = os.WriteFile(backupFile, []byte(content), 0644)  
if err != nil {  
log.Fatalf("backup file failed ", backupFile)  
}  
}

Go 备份数据库表

简介

go实现mysql数据库导出