-
Notifications
You must be signed in to change notification settings - Fork 1
/
mysql.go
190 lines (152 loc) · 4.31 KB
/
mysql.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
package utils
import (
"bufio"
"compress/gzip"
"database/sql"
"fmt"
"os"
"path"
"path/filepath"
"strings"
"github.com/aliakseiz/go-mysqldump"
"github.com/axllent/ssbak/app"
"github.com/go-sql-driver/mysql"
)
func mysqlConfig() *mysql.Config {
addr := app.DB.Host
if app.DB.Port != "" {
addr += ":" + app.DB.Port
}
// Open connection to database
config := mysql.NewConfig()
config.User = app.DB.Username
config.Passwd = app.DB.Password
config.DBName = app.DB.Name
config.Net = "tcp"
config.Addr = addr
return config
}
// MySQLDumpToGz uses mysqldump to stream a database dump directly into a gzip file
func MySQLDumpToGz(gzipFile string) error {
config := mysqlConfig()
f, err := os.Create(path.Clean(gzipFile))
if err != nil {
return fmt.Errorf("Error creating database backup: %s", err.Error())
}
defer func() {
if err := f.Close(); err != nil {
fmt.Printf("Error closing file: %s\n", err)
}
}()
// Open connection to database
db, err := sql.Open("mysql", config.FormatDSN())
if err != nil {
return fmt.Errorf("Error opening database: %s", err.Error())
}
defer db.Close()
gzw := gzip.NewWriter(f)
defer gzw.Close()
defer gzw.Flush()
app.Log(fmt.Sprintf("Dumping database to '%s'", gzipFile))
dumper := mysqldump.Data{
Connection: db,
Out: gzw,
MaxAllowedPacket: 512000, // 512KB
}
// Dump database to file
if err = dumper.Dump(); err != nil {
return fmt.Errorf("Error dumping: %s", err.Error())
}
outSize, _ := CalcSize(gzipFile)
app.Log(fmt.Sprintf("Wrote %s (%s)", gzipFile, ByteToHr(outSize)))
// Close dumper, connected database and file stream.
return dumper.Close()
}
// MySQLCreateDB a database, optionally dropping it
func MySQLCreateDB(dropDatabase bool) error {
config := mysqlConfig()
config.DBName = "" // reset the database name
// Open connection to database
db, err := sql.Open("mysql", config.FormatDSN())
if err != nil {
return fmt.Errorf("Error opening database: %s", err.Error())
}
defer db.Close()
createMsg := `Creating database (if not exists)`
if dropDatabase {
app.Log(fmt.Sprintf("Dropping database '%s'", app.DB.Name))
if _, err := db.Exec("DROP DATABASE IF EXISTS `" + app.DB.Name + "`"); err != nil {
return err
}
createMsg = `Creating database`
}
app.Log(fmt.Sprintf("%s '%s'", createMsg, app.DB.Name))
_, err = db.Exec("CREATE DATABASE IF NOT EXISTS `" + app.DB.Name + "`")
return err
}
// MySQLLoadFromGz loads a GZ database file into the database,
// streaming the gz file to the mysql cli.
func MySQLLoadFromGz(gzipSQLFile string) error {
if !IsFile(gzipSQLFile) {
return fmt.Errorf("File '%s' does not exist", gzipSQLFile)
}
f, err := os.Open(filepath.Clean(gzipSQLFile))
if err != nil {
return err
}
defer func() {
if err := f.Close(); err != nil {
fmt.Printf("Error closing file: %s\n", err)
}
}()
reader, err := gzip.NewReader(f)
if err != nil {
return err
}
defer reader.Close()
config := mysqlConfig()
// Open connection to database
db, err := sql.Open("mysql", config.FormatDSN())
if err != nil {
return fmt.Errorf("Error opening database: %s", err.Error())
}
defer db.Close()
fileScanner := bufio.NewScanner(reader)
fileScanner.Split(bufio.ScanLines)
cbuffer := make([]byte, 0, bufio.MaxScanTokenSize)
fileScanner.Buffer(cbuffer, bufio.MaxScanTokenSize*50) // Otherwise long lines crash the scanner
app.Log(fmt.Sprintf("Importing database to '%s'", app.DB.Name))
// ensure compatibility between MySQL & Mariadb, including older versions caused by
// `STRICT_TRANS_TABLES` and `STRICT_ALL_TABLES`
if _, err := db.Exec("SET sql_mode = '';"); err != nil {
return err
}
sql := ""
for fileScanner.Scan() {
line := fileScanner.Text()
if strings.HasPrefix(line, "/*!") || strings.HasPrefix(line, "--") || line == "" {
// ignore comments and blank lines
} else if strings.HasSuffix(line, ";") {
// end of line, append and insert
sql = sql + line + " "
if strings.TrimSpace(sql) != "" {
if _, err := db.Exec(sql); err != nil {
return err
}
}
// reset sql
sql = ""
} else {
// append sql
sql = sql + "\n" + line
}
}
// if any sql remains, execute
if strings.TrimSpace(sql) != "" {
if _, err := db.Exec(sql); err != nil {
return err
}
}
app.Log(fmt.Sprintf("Imported '%s' to '%s'", gzipSQLFile, app.DB.Name))
return err
}