【Golang | Database】利用database/sql访问容器化的MySQL

环境准备

• GoLang: go1.15.7 windows/amd64
• Docker: 1.13.1
• MySQL: 8.0.28

一、利用容器部署mysql

1、首先通过docker pull mysql拉取mysql镜像

[root@k8s-master ~]# docker pull mysql
Using default tag: latest
Trying to pull repository docker.io/library/mysql ...
latest: Pulling from docker.io/library/mysql
a4b007099961: Pull complete
e2b610d88fd9: Pull complete
38567843b438: Pull complete
5fc423bf9558: Pull complete
aa8241dfe828: Pull complete
cc662311610e: Pull complete
9832d1192cf2: Pull complete
f2aa1710465f: Pull complete
4a2d5722b8f3: Pull complete
3a246e8d7cac: Pull complete
2f834692d7cc: Pull complete
a37409568022: Pull complete
Digest: sha256:b2ae0f527005d99bacdf3a220958ed171e1eb0676377174f0323e0a10912408a
Status: Downloaded newer image for docker.io/mysql:latest
[root@k8s-master ~]# docker images |grep mysql
docker.io/mysql                                                       latest              562c9bc24a08        3 days ago          521 MB
[root@k8s-master ~]#

2、利用该镜像运行mysql容器， docker run --name mysql -d -p 1234:3306 -e MYSQL_ROOT_PASSWORD=root docker.io/mysql:latest

[root@k8s-master ~]# docker run --name mysql -d -p 1234:3306 -e MYSQL_ROOT_PASSWORD=root docker.io/mysql:latest
9b11e63ec55c451c6ba28a36afd87c0c68462c3cfd83400873f463edb299cd4b
[root@k8s-master ~]# docker ps -l
CONTAINER ID        IMAGE                    COMMAND                  CREATED             STATUS              PORTS                               NAMES
9b11e63ec55c        docker.io/mysql:latest   "docker-entrypoint..."   7 seconds ago       Up 7 seconds        33060/tcp, 0.0.0.0:1234->3306/tcp   mysql
[root@k8s-master ~]#

注：

• 这里 ‘-e’ 代表传入的环境变量
• PORTS为 33060/tcp, 0.0.0.0:1234->3306/tcp，代表容器暴露的mysql服务端口是3306和33060（通常3306是mysql的默认端口，供mysql客户端连接；33060是mysql拓展接口的默认端口），同时将本地的1234端口映射到3306，目的是在本地就可以访问mysql

3、执行docker exec -it CONTAINER ID /bin/bash进入容器，并登录mysql

[root@k8s-master ~]# docker exec -it 9b11e63ec55c /bin/bash
root@9b11e63ec55c:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

4、通过授权的方式 grant all privileges on *.* to ‘root’@‘%’ 开启mysql的远程访问

mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

二、利用"database/sql"访问mysql

package main

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

func main() {
	//192.168.101.109注意是mysql容器所在的主机ip,如果mysql容器和你的golang程序不在一个主机上，则不能用127.0.0.1
	db, err := sql.Open("mysql", "root:root@tcp(192.168.101.109:1234)/mysql")
	if err != nil {
		log.Panic(err)
	}
	defer db.Close()
	rows, err := db.Query("select host from user")
	if err != nil {
		log.Panic(err)
	}
	defer rows.Close()
	var host string
	for rows.Next() {
		if err = rows.Scan(&host); err != nil {
			log.Panic(err)
		}
		fmt.Println(host)
	}
}

先看下执行结果

%
localhost
localhost
localhost
localhost

sql.Open(“mysql”, “root:root@tcp(192.168.101.109:1234)/mysql”) 打开了一个数据库，第一个参数就是为了注册mysql驱动，第二个参数格式是"用户名:密码@tcp(mysql服务所在主机ip:mysql服务所在主机端口)/数据库名"

注：
"github.com/go-sql-driver/mysql"这个包执行了init函数

func init() {
	sql.Register("mysql", &MySQLDriver{})
}

调用了database/sql里的Register函数，注册了mysql(drivers[name] = driver)

func Register(name string, driver driver.Driver) {
	driversMu.Lock()
	defer driversMu.Unlock()
	if driver == nil {
		panic("sql: Register driver is nil")
	}
	if _, dup := drivers[name]; dup {
		panic("sql: Register called twice for driver " + name)
	}
	drivers[name] = driver
}