func get_id() {
db := mysql.New("tcp", "", "183.60.126.26:3306", user, pass, dbname)
db1 := mysql.New("tcp", "", "183.60.126.26:3306", user, pass, dbname)
err := db.Connect()
if err != nil {
panic(err)
}
defer db.Close()
err = db1.Connect()
defer db1.Close()
rows, _, err := db.Query("select bag_id,stuff_id from player_bag b, paladin p where player_id = %d and b.stuff_id=p.paladin_id and is_active=0", 26559)
if err != nil {
panic(err)
}
for _, row := range rows {
for _, col := range row {
if col == nil {
// col has NULL value
} else {
// Do something with text in col (type []byte)
}
}
bag_id := row.Int(0)
stuff_id := row.Int(1)
fmt.Printf("%d, %d\n", bag_id, stuff_id)
//del(db, bag_id, stuff_id);
}
}
// Open new connection. The uri need to have the following syntax:
//
// [PROTOCOL_SPECFIIC*]DBNAME/USER/PASSWD
//
// where protocol spercific part may be empty (this means connection to
// local server using default protocol). Currently possible forms:
// DBNAME/USER/PASSWD
// unix:SOCKPATH*DBNAME/USER/PASSWD
// tcp:ADDR*DBNAME/USER/PASSWD
func (d *Driver) Open(uri string) (driver.Conn, error) {
pd := strings.SplitN(uri, "*", 2)
if len(pd) == 2 {
// Parse protocol part of URI
p := strings.SplitN(pd[0], ":", 2)
if len(p) != 2 {
return nil, errors.New("Wrong protocol part of URI")
}
d.proto = p[0]
d.raddr = p[1]
// Remove protocol part
pd = pd[1:]
}
// Parse database part of URI
dup := strings.SplitN(pd[0], "/", 3)
if len(dup) != 3 {
return nil, errors.New("Wrong database part of URI")
}
d.db = dup[0]
d.user = dup[1]
d.passwd = dup[2]
// Establish the connection
c := conn{mysql.New(d.proto, d.laddr, d.raddr, d.user, d.passwd, d.db)}
for _, q := range d.initCmds {
c.my.Register(q) // Register initialisation commands
}
if err := c.my.Connect(); err != nil {
return nil, errFilter(err)
}
return &c, nil
}
func show_count(w http.ResponseWriter, r *http.Request) {
log.Println("process count request")
srv1.TimeNow = time.Now()
db1 := mysql.New("tcp", "", *(srv1.addr), *(srv1.user), *(srv1.pass), *(srv1.dbname))
err := db1.Connect()
defer db1.Close()
if err != nil {
panic(err)
}
_, _, _ = db1.Query("set wait_timeout = 3600")
_, _, _ = db1.Query("set interactive_timeout = 3600")
// fmt.Fprintf(w, player_page,
// get_all_reg(&db1),
// pve_online_5(&db1),
// get_login_30(&db1));
srv1.D帐户数 = get_all_reg(&db1)
srv1.D角色数 = get_player(&db1)
srv1.D活跃数 = pve_online_5(&db1)
srv1.D登录_30 = get_login_30(&db1)
srv1.D登录_60 = get_login_60(&db1)
get_level_count(&db1, &srv1)
log.Println("parse file: ", *cssFile)
tmp, _ := template.ParseFiles(*cssFile)
tmp.Execute(w, srv1)
}
// FetchMetrics interface for mackerelplugin
func (m MySQLPlugin) FetchMetrics() (map[string]interface{}, error) {
db := mysql.New("tcp", "", m.Target, m.Username, m.Password, "")
err := db.Connect()
if err != nil {
log.Fatalln("FetchMetrics (DB Connect): ", err)
return nil, err
}
defer db.Close()
stat := make(map[string]float64)
m.fetchShowStatus(db, stat)
if m.DisableInnoDB != true {
m.fetchShowInnodbStatus(db, stat)
m.fetchShowVariables(db, stat)
}
m.fetchShowSlaveStatus(db, stat)
statRet := make(map[string]interface{})
for key, value := range stat {
statRet[key] = value
}
return statRet, err
}
func main() {
user := "******"
pass := "******"
dbname := "test"
db := mysql.New("tcp", "", "127.0.0.1:3306", user, pass, dbname)
err := db.Connect()
if err != nil {
panic(err)
}
rows, res, err := db.Query("select 'hello world' as FirstColumn,22 as SecondColumn limit %d", 20)
if err != nil {
panic(err)
}
for _, row := range rows {
for _, col := range row {
if col == nil {
// col has NULL value
} else {
// Do something with text in col (type []byte)
}
}
// You may get values by column name
first := res.Map("FirstColumn")
//second := res.Map("SecondColumn")
fmt.Printf(row.Str(first))
}
}
func getAdmin(adminid int) (string, string) {
db := mysql.New("tcp", "", "127.0.0.1:3306", dbusername, dbpassowrd, dbname)
err := db.Connect()
if err != nil {
log.Panic(err)
}
defer db.Close()
rows, res, err := db.Query("select * from user where uid=%d", adminid)
if err != nil {
log.Panic(err)
}
if len(rows) < 1 {
log.Panic("rows error")
}
row := rows[0]
first := res.Map("username")
second := res.Map("password")
username, password := row.Str(first), row.Str(second)
return username, password
}
func get_reg() (int, int) {
db := mysql.New("tcp", "", "117.135.154.58:3306", user, pass, dbname)
err := db.Connect()
var ret_all = 0
var ret_player = 0
if err != nil {
panic(err)
return ret_all, ret_player
}
defer db.Close()
_, _, _ = db.Query("set wait_timeout = 3600")
_, _, _ = db.Query("set interactive_timeout = 3600")
rows, _, err := db.Query("select count(customer_id)-54 from customer.customer")
if err != nil {
panic(err)
}
for _, row := range rows {
ret_all = row.Int(0)
}
rows, _, err = db.Query("select count(player_id) from player where vip>=1 and player_id>27048")
if err != nil {
panic(err)
}
for _, row := range rows {
ret_player = row.Int(0)
}
return ret_all, ret_player
}
func FetchData(m *model.MysqlIns, socketfile string, cfg *base.Cfg) (err error) {
defer func() {
MysqlAlive(cfg, m, err == nil)
}()
//db := mysql.New("tcp", "", fmt.Sprintf("%s:%d", m.Host, m.Port), cfg.User, cfg.Pass)
db := mysql.New("unix", "", socketfile, cfg.User, cfg.Pass)
db.SetTimeout(500 * time.Millisecond)
if err = db.Connect(); err != nil {
return
}
defer db.Close()
data := make([]*model.MetaData, 0)
threadNums, err := collect.ProcessNum(db, cfg)
if err != nil {
return
}
data = append(data, threadNums)
slaveState, err := collect.SlaveStatus(db, cfg)
if err != nil {
return
}
data = append(data, slaveState...)
msg, err := SendData(data, cfg)
if err != nil {
return
}
log.Infof("Send response %v: %s", m, string(msg))
return
}
func show_level(w http.ResponseWriter, r *http.Request) {
log.Println("process level request")
nav(w)
db1 := mysql.New("tcp", "", *(srv1.addr), *(srv1.user), *(srv1.pass), *(srv1.dbname))
err := db1.Connect()
defer db1.Close()
if err != nil {
panic(err)
}
_, _, _ = db1.Query("set wait_timeout = 3600")
_, _, _ = db1.Query("set interactive_timeout = 3600")
get_level_count(&db1, &srv1)
fmt.Fprintf(w, `<br>`)
level_tmp := template.New("")
level_tmp.Parse(`
<table width="307" height="414" border="1" cellspacing="0" bordercolor="#333366">
<tr>
<td><span class="style3">等级</span></td>
<td width="88"><span class="style5"> 人数 </span></td>
</tr>
{{range $k,$v := .D级别}}
<tr>
<td><span class="style3">级别 {{$k}}0-{{$k}}9 级</span></td>
<td width="88"><span class="style5"> {{$v}} </span></td>
</tr>
{{end}}
</table>
`)
level_tmp.Execute(w, srv1)
}
// MAIN
func main() {
var err error
var w io.Writer
parseCommandLine()
readConfigFile()
raddr := fmt.Sprintf("%s:%d", hostname, port)
db := mysql.New("tcp", "", raddr, username, password, database)
db.Register("SET NAMES utf8")
err = db.Connect()
checkError(err)
if *output == "" {
w = os.Stdout
} else {
w, err = os.Create(*output)
checkError(err)
}
fmt.Fprintf(w, "SET NAMES utf8;\n")
fmt.Fprintf(w, "SET FOREIGN_KEY_CHECKS = 0;\n")
tables := getTables(db)
for _, table := range tables {
dumpCreateTable(w, db, table)
dumpTableData(w, db, table)
}
fmt.Fprintf(w, "SET FOREIGN_KEY_CHECKS = 1;\n")
}
func dbConn(obj_feed chan PerformanceStamp) {
user := "******"
pass := ""
dbname := "multicdn"
//proto := "unix"
//addr := "/var/run/mysqld/mysqld.sock"
proto := "tcp"
addr := "127.0.0.1:3306"
db := mysql.New(proto, "", addr, user, pass, dbname)
fmt.Printf("Connect to %s:%s... ", proto, addr)
checkError(db.Connect())
printOK()
go func() {
idx := 0
for obj := range obj_feed {
db.Query("insert cdn_logs values ('%s', '%s', '%s', '%s')", obj.ts, obj.cdn, obj.ip, obj.url)
idx++
if idx%1000 == 0 {
fmt.Printf("%d. records saved\n", idx)
}
}
}()
}
func zip_around_10_miles(cur_zip string, limit int) []string {
if cur_zip == "" {
return nil
}
config, _ := toml.LoadFile("config.toml")
mysql_user_name, _ := config.Get("mysql_user_name").(string)
mysql_password, _ := config.Get("mysql_password").(string)
mysql_db_name, _ := config.Get("mysql_db_name").(string)
mysql_url, _ := config.Get("mysql_url").(string)
db := mysql.New("tcp", "", mysql_url, mysql_user_name, mysql_password, mysql_db_name)
err := db.Connect()
if err != nil {
panic(err)
}
rows, _, err := db.Query("SELECT `zip_distance`.* FROM `zip_distance` WHERE `zip_distance`.`PrimaryZip` = " + cur_zip + " LIMIT 1")
if err != nil {
panic(err)
}
var zips string
for _, row := range rows {
val1 := row[2].([]byte)
row = row
zips = string(val1)
}
return strings.Split(zips, ",")
}
// Establish a connection using URI of following syntax:
// DBNAME/USER/PASSWD
// unix:SOCKPATH*DBNAME/USER/PASSWD
// tcp:ADDR*DBNAME/USER/PASSWD
func (d *drv) Open(uri string) (driver.Conn, error) {
pd := strings.SplitN(uri, "*", 2)
if len(pd) == 2 {
// Parse protocol part of URI
p := strings.SplitN(pd[0], ":", 2)
if len(p) != 2 {
return nil, errors.New("Wrong protocol part of URI")
}
d.proto = p[0]
d.raddr = p[1]
// Remove protocol part
pd = pd[1:]
}
// Parse database part of URI
dup := strings.SplitN(pd[0], "/", 3)
if len(dup) != 3 {
return nil, errors.New("Wrong database part of URI")
}
d.db = dup[0]
d.user = dup[1]
d.passwd = dup[2]
// Establish the connection
mc := mysql.New(d.proto, d.laddr, d.raddr, d.user, d.passwd, d.db)
c := conn{mc, make(map[string]driver.Stmt)}
if err := c.my.Connect(); err != nil {
return nil, err
}
return &c, nil
}
func checkUptime() *checkers.Checker {
opts := connectionOpts{}
psr := flags.NewParser(&opts, flags.Default)
_, err := psr.Parse()
if err != nil {
os.Exit(1)
}
db := mysql.New("tcp", "", fmt.Sprintf("%s:%s", opts.mysqlSetting.Host, opts.mysqlSetting.Port), opts.mysqlSetting.User, opts.mysqlSetting.Pass, "")
err = db.Connect()
if err != nil {
return checkers.Critical("couldn't connect DB")
}
defer db.Close()
rows, res, err := db.Query("SHOW GLOBAL STATUS LIKE 'Uptime'")
if err != nil {
return checkers.Critical("couldn't execute query")
}
idxValue := res.Map("Value")
Uptime := rows[0].Int64(idxValue)
if opts.Crit > 0 && Uptime < opts.Crit {
return checkers.Critical(fmt.Sprintf("up %s < %s", uptime2str(Uptime), uptime2str(opts.Crit)))
} else if opts.Warn > 0 && Uptime < opts.Warn {
return checkers.Warning(fmt.Sprintf("up %s < %s", uptime2str(Uptime), uptime2str(opts.Warn)))
}
return checkers.Ok(fmt.Sprintf("up %s", uptime2str(Uptime)))
}
func CreateCon(addr string, user string, passwd string, dbname string) (con mysql.Conn) {
proto := "tcp"
con = mysql.New(proto, "", addr, user, passwd, dbname)
fmt.Printf("Connect to %s:%s... ", proto, addr)
CheckError(con.Connect())
PrintOK()
return
}
func ConnectToDb(cfg DBConfig) mysql.Conn {
db := mysql.New("tcp", "", cfg.Addr, cfg.User, cfg.Passward, cfg.DBName)
db.Register("set names utf8")
fmt.Printf("Connect to %s[%s]... ", cfg.Addr, cfg.DBName)
checkError(db.Connect())
return db
}
func (this *AjaxController) LoginAction(w http.ResponseWriter, r *http.Request) {
w.Header().Set("content-type", "application/json")
err := r.ParseForm() //未调用此函数前，r.Form是空map
if err != nil {
OutputJson(w, 0, "参数错误", nil)
return
}
fmt.Printf("参数:%v\n", r.Form)
admin_name := r.FormValue("admin_name")
admin_pass := r.FormValue("admin_pass")
fmt.Printf("admin_name:%s admin_pass:%s\n", admin_name, admin_pass)
if admin_name == "" || admin_pass == "" {
OutputJson(w, 0, "param error", nil)
return
}
db := mysql.New("tcp", "", "127.0.0.1:3306", "root", "", "webserver")
if err := db.Connect(); err != nil {
OutputJson(w, 0, "database connect error", nil)
return
}
defer db.Close()
rows, res, err := db.Query("select * from user where admin_name='%s'", admin_name)
if err != nil {
OutputJson(w, 0, "db read failed", nil)
return
}
if len(rows) == 0 {
OutputJson(w, 1, "find no user", nil)
return
}
for _, row := range rows {
fmt.Printf("%d %s %s\n", row.Int(0), row.Str(1), row.Str(2))
}
fmt.Printf("data :%v res:%v\n", rows, res)
name := res.Map("admin_password")
admin_pass_db := rows[0].Str(name)
if admin_pass_db != admin_pass {
OutputJson(w, 0, "passwd error", nil)
return
}
//存入cookie,使用cookie存储
cookie := http.Cookie{Name: "admin_name", Value: rows[0].Str(res.Map("admin_name")), Path: "/"}
http.SetCookie(w, &cookie)
OutputJson(w, 1, "operate success", nil)
return
}
func (slef *ScanMysql) crack(host, user, pass, port, dbname string) (bool, *mysql.Conn) {
db := mysql.New("tcp", "", host+":"+port, user, pass, dbname)
db.SetTimeout(4 * time.Second)
err := db.Connect()
if err != nil {
db.Close()
return false, nil
}
return true, &db
}
// CreateConnection creates a connection to the database
func CreateConnection(host, port, user, pass, name string) *mysql.Conn {
d := mysql.New("tcp", "", host+":"+port, user, pass, name)
err := d.Connect()
if err != nil {
log.Fatal(err)
}
return &d
}
func Connect() z_mysql.Conn {
//Set up database connection
db := z_mysql.New("tcp", "", "127.0.0.1:3306", "root", "rootroot", "gps")
err := db.Connect()
if err != nil {
fmt.Println("ERROR CONNECTING:", err)
panic(err)
}
return db
}
func (c *Config) connectDataBase() (con mysql.Conn) {
if c.DataBase.Host != "" {
con = mysql.New("tcp", "", fmt.Sprintf("%s:%d", c.DataBase.Host, c.DataBase.Port), c.DataBase.User, c.DataBase.Pass, c.DataBase.Name)
err := con.Connect()
if err != nil {
log.Printf("mainThread() DB接続失敗")
con = nil
}
}
return
}
func main() {
optHost := flag.String("host", "localhost", "Hostname")
optPort := flag.String("port", "3306", "Port")
optUser := flag.String("username", "root", "Username")
optPass := flag.String("password", "", "Password")
optCrit := flag.Int("crit", 1, "critical if the second behind master is over")
optWarn := flag.Int("warn", 1, "warning if the second behind master is over")
flag.Parse()
target := fmt.Sprintf("%s:%s", *optHost, *optPort)
db := mysql.New("tcp", "", target, *optUser, *optPass, "")
err := db.Connect()
if err != nil {
fmt.Println("UNKNOWN: couldn't connect DB")
os.Exit(UNKNOWN)
}
defer db.Close()
rows, res, err := db.Query("show slave status")
if err != nil {
fmt.Println("UNKNOWN: couldn't execute query")
os.Exit(UNKNOWN)
}
if len(rows) == 0 {
fmt.Println("OK: MySQL is not slave")
os.Exit(OK)
}
idxIoThreadRunning := res.Map("Slave_IO_Running")
idxSQLThreadRunning := res.Map("Slave_SQL_Running")
idxSecondsBehindMaster := res.Map("Seconds_Behind_Master")
ioThreadStatus := rows[0].Str(idxIoThreadRunning)
sqlThreadStatus := rows[0].Str(idxSQLThreadRunning)
secondsBehindMaster := rows[0].Int(idxSecondsBehindMaster)
if ioThreadStatus == "No" || sqlThreadStatus == "No" {
fmt.Println("CRITICAL: MySQL replication has been stopped")
os.Exit(CRITICAL)
}
if secondsBehindMaster > *optCrit {
msg := fmt.Sprintf("CRITICAL: MySQL replication behind master %d seconds", secondsBehindMaster)
fmt.Println(msg)
os.Exit(CRITICAL)
} else if secondsBehindMaster > *optWarn {
msg := fmt.Sprintf("WARNING: MySQL replication behind master %d seconds", secondsBehindMaster)
fmt.Println(msg)
os.Exit(WARNING)
}
fmt.Println("OK: MySQL replication works well")
os.Exit(OK)
}
func (this *ajaxController) LoginAction(w http.ResponseWriter, r *http.Request) {
w.Header().Set("content-type", "application/json")
err := r.ParseForm()
if err != nil {
OutputJson(w, 0, "参数错误", nil)
return
}
admin_name := r.FormValue("admin_name")
admin_password := r.FormValue("admin_password")
if admin_name == "" || admin_password == "" {
OutputJson(w, 0, "参数错误", nil)
return
}
db := mysql.New("tcp", "", cfg.Host, cfg.User, cfg.Pass, cfg.DBName)
if err := db.Connect(); err != nil {
log.Println(err)
OutputJson(w, 0, "数据库操作失败", nil)
return
}
defer db.Close()
rows, res, err := db.Query("select * from webdemo_admin where admin_name = '%s'", admin_name)
if err != nil {
log.Println(err)
OutputJson(w, 0, "数据库操作失败", nil)
return
}
if len(rows) == 0 {
log.Println("no user:"******"该用户不存在", nil)
return
}
name := res.Map("admin_password")
admin_password_db := rows[0].Str(name)
if admin_password_db != admin_password {
OutputJson(w, 0, "密码输入错误", nil)
return
}
// 存入cookie,使用cookie存储
cookie := http.Cookie{Name: "admin_name", Value: rows[0].Str(res.Map("admin_name")), Path: "/"}
http.SetCookie(w, &cookie)
OutputJson(w, 1, "操作成功", nil)
return
}
func mysqlInit() mysql.Conn {
user := "******"
pass := "******"
dbname := "apk_risk"
//proto := "unix"
//addr := "/var/run/mysqld/mysqld.sock"
proto := "tcp"
addr := "127.0.0.1:3306"
db := mysql.New(proto, "", addr, user, pass, dbname)
RightLine()
checkError(db.Connect())
RightLine()
return db
}
// InitMySqlAssertions initializes assertmysql package.
func InitMySqlAssertions(proto, laddr, raddr, user, passwd, db string) mysql.Conn {
var err error
dbcon = mysql.New(proto, laddr, raddr, user, passwd, db)
if err = dbcon.Connect(); err != nil {
panic(err)
}
if _, _, err = dbcon.QueryFirst("set sql_mode = 'STRICT_ALL_TABLES'"); err != nil {
panic(err)
}
return dbcon
}
func InsertMysql() {
db := mysql.New("tcp", "", "127.0.0.1:3306", "root", "", "blog")
db.Register("set names utf8")
err := db.Connect()
if err != nil {
panic(err)
}
stmt, err := db.Prepare("INSERT INTO `goodblog`(`url`, `title`, `follownum`,`fansnum`,`follow`, `fans`) VALUES (?,?,?,?,?,?)")
for _, v := range blogs {
_, err = stmt.Run(v.Url, v.Title, len(v.Follow), len(v.Fans), strings.Join(v.Follow, ","), strings.Join(v.Fans, ","))
log.Println(v.Url)
}
fmt.Println("insert mysql over")
}
// Function which takes a database name and connects to that database
// using the details defined in the connection module.
func CreateConnection(dbname string) (mysql.Conn, error) {
db := mysql.New(
"tcp",
"",
connection_details.IP,
connection_details.User,
connection_details.Password,
dbname,
)
err := db.Connect()
if err != nil {
return nil, err
}
return db, nil
}
func DemoMysql() {
user := "******"
pass := "******"
dbname := "test"
db := mysql.New("tcp", "", "127.0.0.1:3306", user, pass, dbname)
err := db.Connect()
if err != nil {
panic(err)
}
rows, res, err := db.Query("select * from t_user where id > %d", 20)
if err != nil {
panic(err)
}
for _, row := range rows {
for _, col := range row {
if col == nil {
// col has NULL value
} else {
// Do something with text in col (type []byte)
}
}
// You can get specific value from a row
val1 := row[1].([]byte)
// You can use it directly if conversion isn't needed
os.Stdout.Write(val1)
// You can get converted value
number := row.Int(0) // Zero value
str := row.Str(1) // First value
bignum := row.Uint(2) // Second value
fmt.Println(number)
fmt.Println(str)
fmt.Println(bignum)
//// You may get values by column name
first := res.Map("FirstColumn")
// second := res.Map("SecondColumn")
fmt.Println(first)
// val1, val2 := row.Int(first), row.Str(second)
}
}
func deleteAdmin(adminid int) {
db := mysql.New("tcp", "", "127.0.0.1:3306", dbusername, dbpassowrd, dbname)
err := db.Connect()
if err != nil {
log.Panic(err)
}
defer db.Close()
stmt, err := db.Prepare("delete user where uid=?")
if err != nil {
log.Panic(err)
}
_, err = stmt.Run(adminid)
}
func updateAdmin(adminid int, password string) {
db := mysql.New("tcp", "", "127.0.0.1:3306", dbusername, dbpassowrd, dbname)
err := db.Connect()
if err != nil {
log.Panic(err)
}
defer db.Close()
stmt, err := db.Prepare("update user set password=? where uid=?")
if err != nil {
log.Panic(err)
}
_, err = stmt.Run(password, adminid)
}