Go Mysql

by · 2018年06月14日 · 2405 Words · ~5min reading time | Improve on


原文链接： Go Mysql

MySQL 连接池问题

Mysql Server 配置:

max_connections          = 2000 # mysql 最大连接数,默认是150
wait_timeout             = 3600 # wait_timeout 针对非交互式连接,空闲连接 Sleep
interactive_timeout      = 3600 # interactive_timeout 针对交互式连接,活动连接.

Golang 配置

	db.SetMaxOpenConns(100)  // default: 0  nolimit on the number of open connections
	db.SetMaxIdleConns(0)     //
	db.SetConnMaxLifetime(10*time.Second)

// 连接池的实现关键在于设置最大连接数和设置最大空闲连接数其中:
// DB.SetMaxOpenConns(200): 用于限制最大打开的连接数(default:0表示不限制)
// DB.SetMaxIdleConns(0):   用于设置闲置的连接数,以复用连接提高效率(default:2)
// DB.SetConnMaxLifetime(10*time.Second)
// 设置(限制)最大的连接数可以避免并发太高导致连接MYSQL出现"TOO MANY CONNECTIONS"的错误
// 设置闲置的连接数则当开启的一个连接使用完成后可以放在池里等候下一次使用
  1. TOO MANY CONNECTIONS 错误
    由于MySQL有最大连接的限制,默认是150, 当golang中未设置db.SetMaxOpenConns(100)时,MaxOpenConn默认时0 无限制.
    那么当golang中并发太高,同时连接数就可能会超过MySQL的最大连接,此时就会报



// GOLANG这边实现的连接池只提供了SetMaxOpenConns和SetMaxIdleConns方法进行连接池方面的配置
// 在使用的过程中有一个问题就是数据库本身对连接有一个超时时间的设置.如果超时时间到了数据库会单方面断掉连接
// 此时再用连接池内的连接进行访问就会出错
// packets.go:32: unexpected EOF
// packets.go:118: write tcp 192.168.3.90:3306: broken pipe
//
// 上面的错误都是go-sql-drive本身的输出
// 有的时候还会出现bad connection的错误警告
// #5718 database/sql: fix auto-reconnect in prepared statements 之后该警示无害，库总会无条件申请一个新的可用链接~

packets.go:36: unexpected EOF (Invalid Connection)

Issue #674 · go-sql-driver/mysql
方案1:
General solution is DB.SetConnMaxLifetime(time.Second*10)

Even if mysql server's wait_timeout is long enough, router or OS may close long idle connection to reduce resource usage. Which cause this error too.
So I recommend 1sec ~ 10sec timeout, unless you're network expert and you know optimal timeout length.

自动重连

github.com/ziutek/mymysql/

dsn一定要设置为自动提交 autocommit=true

现象: 明明 RowsAffected 8424显示正确,也没有报错，但是查询的时候数据库中却没有数据
这是因为在mysql的dsn中使用了 &autocommit=false 参数导致

maxAllowedPacket 默认值：0
最大包大小允许以字节为单位。使用 &maxAllowedPacket = 0 自动从服务器提取max_allowed_pa​​cket变量。

multiStatements 默认值：false
在一个查询中允许多个语句。虽然这允许批量查询，但它也大大增加了SQL注入的风险。只返回第一个查询的结果，所有其他结果都被静默地丢弃。
当使用 &multiStatements=true 时，?参数只能在第一个语句中使用。

parseTime 默认值：false
当 &parseTime = true 时, DATE和DATETIME值的输出类型更改为time.Time而不是[] byte / string

readTimeout writeTimeout 类型：持续时间 默认值：0
I / O读取超时。该值必须是单位后缀（“ms”，“s”，“m”，“h”）的十进制数字，例如“30s”，“0.5m”或“1m30s”。

rejectReadOnly 默认值：false 允许连接只读的从库
&rejectreadOnly = true会导致驱动程序拒绝只读连接。这是在自动故障切换期间的可能的竞争条件，其中mysql客户端在故障切换后连接到只读副本。
strict 默认值：false 不把报警当成错误 (不要在生产环境设置为true)
strict = true使得驱动程序严格模式可以将MySQL警告视为错误。在生产中不应该使用此模式，因为在某些情况下可能会导致数据损坏。

调用存储过程

	// 调用带 OUTPUT parament存储过程
	_, err = db.Exec("call follow_liveroom_num(?, @count)", uid)
	if err != nil {
		return
	}

	var count int
	err = db.QueryRow("select @count").Scan(&count)
	if err != nil {			
		return
	}

自动重连mysql

//reconnect tries to reconnect untill sucess
func reconnect(db *sql.DB, constr string) {
	go func() {
		for {
			//fmt.Println("....checking connection")
			if err := db.Ping(); err != nil {
				//fmt.Println("connection lost, reconnecting...")
				initDB(constr)
				fmt.Println("reconnected")
			}
		}
	}()
}

db.Exec("USE " + dbname) 切换数据库

var dbnames = []string{"mengshan", "mengyin", "pingyi", "shizhi", "tancheng", "yinan", "yishui", "feixian", "gaoxinqu", "hedong", "jingkaiqu", "junan", "lanling", "lanshan", "lingang", "linshu"}

	for _, dbname := range dbnames {
		db.Exec("drop database  IF EXISTS " + dbname + ";")
		db.Exec("create database " + dbname + ";")
		db.Exec("USE " + dbname)
	}

db.Exec() 执行多条语句

查找资料才知道原因是 mysql 默认是不能在一个语句中同时执行两条 sql 语句，把 drop table 和 create table 拆开。
解决办法：

将 多条 sql 语句拆开，每个语句单独执行 db.Exec()
查看 go-sql-driver 的文档，发现可以支持一条语句多条 sql 执行。修改代码如下

db, err := sql.Open("mysql", user+":"+pass+"@tcp("+host+":"+port+")/"+name+"?charset=utf8&autocommit=true&multiStatements=true")

增加了 &multiStatements=true 参数

package main

import (
	"fmt"
	"log"
	"net/http"
	"database/sql"

	_ "github.com/go-sql-driver/mysql"
)

// GOLANG的MYSQL连接池实现:
// GOLANG内部自带了连接池功能.SQL.OPEN函数实际上是返回一个连接池对象而不是单个连接
// 在OPEN的时候并没有去连接数据库只有在执行QUERY、EXEC方法的时候才会去实际连接数据库
// 在一个应用中同样的库连接只需要保存一个SQL.OPEN之后的DB对象就可以了不需要多次OPEN

// 数据库连接池测试
// 1.DB对象初始化
var db *sql.DB

func init() {

	db, _ = sql.Open("mysql", "rdswetec:FWkKYmmLtEbQzs#kG!vA4f!eYiClhuuu@tcp(192.168.0.18:3306)/adwetec_prod?charset=utf8&parseTime=True&loc=Local")

	db.SetMaxOpenConns(1000)
	db.SetMaxIdleConns(0)

	db.Ping()

	// 连接池的实现关键在于设置最大连接数和设置最大空闲连接数其中:
	//
	// SetMaxOpenConns: 用于设置最大打开的连接数(默认值为0表示不限制)
	// SetMaxIdleConns: 用于设置闲置的连接数
	//
	// 设置最大的连接数可以避免并发太高导致连接MYSQL出现"TOO MANY CONNECTIONS"的错误
	// 设置闲置的连接数则当开启的一个连接使用完成后可以放在池里等候下一次使用

	DB.SetConnMaxLifetime(time.Second)

}
// 2.开启WEB服务
func startHttpServer() {

	http.HandleFunc("/pool", pool)

	err := http.ListenAndServe(":9090", nil)

	if err != nil {
		log.Fatal("ListenAndServe: ", err)
	}
}

// 3. Query请求方法
func pool(w http.ResponseWriter, r *http.Request) {
	// 1. 获取所有的行
	rows, err := db.Query("SELECT * FROM adwetec_user LIMIT 1")
	defer rows.Close()
	checkErr(err)
	// 2. 获取所有的列
	columns, _ := rows.Columns()

	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))

	for j := range values {
		scanArgs[j] = &values[j]
	}

	record := make(map[string]string)
	// 3. 逐行取数
	for rows.Next() {
		err = rows.Scan(scanArgs...)
		for i, col := range values {
			if col != nil {
				record[columns[i]] = string(col.([]byte))
			}
		}
	}
	fmt.Println(record)
	fmt.Fprintln(w, "finish")
}

func checkErr(err error) {
	if err != nil {
		fmt.Println(err)
		panic(err)
	}
}

// 使用AB进行并发测试访问:
//
// $ ab -c 100 -n 1000 'http://localhost:9090/pool'
// 在数据库中查看连接进程: show processlist;
// 可以看到有100来个进程
//
// 因为避免了重复创建连接所以使用连接池可以很明显的提高性能
func main() {
	startHttpServer()
}

// 小结
// GOLANG这边实现的连接池只提供了SetMaxOpenConns和SetMaxIdleConns方法进行连接池方面的配置
// 在使用的过程中有一个问题就是数据库本身对连接有一个超时时间的设置.如果超时时间到了数据库会单方面断掉连接
// 此时再用连接池内的连接进行访问就会出错
// packets.go:32: unexpected EOF
// packets.go:118: write tcp 192.168.3.90:3306: broken pipe
//
// 上面的错误都是go-sql-drive本身的输出
// 有的时候还会出现bad connection的错误警告
// #5718 database/sql: fix auto-reconnect in prepared statements 之后该警示无害，库总会无条件申请一个新的可用链接~

show databases

package main

import (
	"database/sql"
	"log"
	"fmt"
	_ "github.com/go-sql-driver/mysql"

)

func main() {

	db, err := sql.Open("mysql", "admin:mypass@tcp(aws-us-east-1-portal.23.dblayer.com:15942)/compose?tls=skip-verify")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	rows, err := db.Query("SHOW DATABASES where `database` REGEXP '^inf'")
	if err != nil {
		log.Fatal(err)
	}

	var dbNames string

	for rows.Next() {
		err := rows.Scan(&dbNames)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Println(dbNames)
	}
}

Tags:

`