Mysql学习(一)添加一个新的用户并用golang操作Mysql

我会带着你远行 2022-04-10 13:18 100阅读 0赞

## Mysql添加一个新的用户并赋予权限 ##

`添加一个自己的用户到mysql`

首先我们需要先用root用户登录mysql，但是刚安装完没有密码，我们先跳过密码

ailumiyana@ailumiyana:~/Git_Project/Go_Test$ sudo mysqld_safe --skip-grant-tables

2019-01-07T01:35:51.559420Z mysqld_safe Logging to syslog.
    2019-01-07T01:35:51.563797Z mysqld_safe Logging to '/var/log/mysql/error.log'.

root登陆

ailumiyana@ailumiyana:~/Git_Project/Go_Test$ sudo mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    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.

切换数据库， use mysql

mysql> use mysql
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed

查看mysql下的表单.

查看user表

mysql> desc user;
    
    +------------------------+-----------------------------------+------+-----+-----------------------+-------+
    | Field                  | Type                              | Null | Key | Default               | Extra |
    +------------------------+-----------------------------------+------+-----+-----------------------+-------+
    | Host                   | char(60)                          | NO   | PRI |                       |       |
    | User                   | char(32)                          | NO   | PRI |                       |       |
    | Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
    | Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
    | Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
    | Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
    | Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
    | Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
    | Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
    | Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
    | File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
    | Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
    | References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
    | Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
    | Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
    | Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
    | Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
    | Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
    | Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
    | Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
    | Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
    | Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
    | Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
    | Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
    | Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
    | Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
    | ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
    | ssl_cipher             | blob                              | NO   |     | NULL                  |       |
    | x509_issuer            | blob                              | NO   |     | NULL                  |       |
    | x509_subject           | blob                              | NO   |     | NULL                  |       |
    | max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
    | max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
    | max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
    | max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
    | plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
    | authentication_string  | text                              | YES  |     | NULL                  |       |
    | password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
    | password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
    | password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
    | account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
    +------------------------+-----------------------------------+------+-----+-----------------------+-------+
    45 rows in set (0.03 sec)

查看user中的已存在的用户和主机

接着我们新增一个自己的账户使用

mysql> CREATE USER '填用户名'@'localhost' IDENTIFIED BY '填密码';
    Query OK, 0 rows affected (0.04 sec)

再次查看user表

给新建的用户添加权限，刷新权限后， 查看新增用户的权限是否已经加进去.

mysql> grant insert,select,delete,update,create,drop on *.* to ailumiyana@"localhost" identified by 'qwedsa';
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show grants for ailumiyana@localhost;
    +---------------------------------------------------------------------------------------+
    | Grants for ailumiyana@localhost                                                       |
    +---------------------------------------------------------------------------------------+
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON *.* TO 'ailumiyana'@'localhost' |
    +---------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

当然我们自己用，当然设置所有权限，更为便捷，那么可以改成这样子  
all privileges 表示所有权限.

mysql> grant all privileges on *.* to ailumiyana@"localhost" identified by 'qwedsa';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show grants for ailumiyana@localhost;
    +---------------------------------------------------------+
    | Grants for ailumiyana@localhost                         |
    +---------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'ailumiyana'@'localhost' |
    +---------------------------------------------------------+
    1 row in set (0.00 sec)

退出,重启服务.

mysql> exit
    Bye
    ailumiyana@ailumiyana:~/Git_Project/Go_Test$ service mysql restart
    ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
    Authentication is required to restart 'mysql.service'.
    Authenticating as: ailumiyana,,, (ailumiyana)
    Password: 
    ==== AUTHENTICATION COMPLETE ===

这样新用户就添加进去了，也配置了适当的权限，接下来用golang简单测试一下。

## 使用go-sql-driver驱动测试Mysql ##

先在mysql中创建一个表

mysql> create table user_info(
        -> id int(4) not null primary key auto_increment,
        -> name char(20) not null);
    Query OK, 0 rows affected (0.29 sec)

插入两个数据到user\_info表中，然后查询此表。

package main
    
    import (
        "github.com/astaxie/beego/logs"
        "database/sql"
        _ "github.com/go-sql-driver/mysql"
    )
    
    func main() {
        
        logs.Debug("main()")
        db, err := sql.Open("mysql", "ailumiyana:qwedsa@tcp(127.0.0.1:3306)/mysql")
        if err != nil {
        
          logs.Error("sql Open() err", err)
        }
    
        stmt, err := db.Prepare("Insert user_info set id=?,name=?")
        if err != nil {
        
          logs.Error("sql Prepare() err", err)
        }
    
        stmt.Exec(1, "sola")
        stmt.Exec(2, "ailumiyana")
    
        rows, err :=db.Query("SELECT * FROM user_info")
        if err != nil {
        
          logs.Error("sql Query() err", err)
        }
    
        for rows.Next() {
        
            var uid int
            var username string
    
            err = rows.Scan(&uid, &username)
            if err != nil {
        
              logs.Error("sql rows.Scan() err", err)
            }
            logs.Debug(uid, username)
        }
    
    }

其中sql.Open中的 第二个从参数是DSN格式数据 :  
DSN(数据源名称)  
数据源名称有一种常见的格式，例如PEAR DB使用的它，但是没有类型前缀(可选部分用方括号标记):  
![Alt text][]

DSN的完整形式:  
![Alt text][Alt text 1]

另外此例用了beego 的日志模块，和go-sql-driver驱动，使用前需要先用go get 下载。  
`go get`可以根据要求和实际情况从互联网上下载或更新指定的代码包及其依赖包，并对它们进行编译和安装。在上面这个示例中，我们从著名的代码托管站点Github上下载了一个项目（或称代码包），并安装到了环境变量GOPATH中包含的第一个工作区中。

`结果`

ailumiyana@ailumiyana:~/Git_Project/Go_Test$ go run mysql.go 
    2019/01/07 14:38:08.438 [D]  main()
    2019/01/07 14:38:08.443 [D]  1 sola
    2019/01/07 14:38:08.443 [D]  2 ailumiyana

mysql> select * from user_info
        -> ;
    +----+-------------+
    | id | name        |
    +----+-------------+
    |  1 | sola        |
    |  2 | ailumiyana  |
    +----+-------------+
    2 rows in set (0.02 sec)

[Alt text]: /images/20220330/b49968c40309421a81a791921867c91a.png
[Alt text 1]: /images/20220330/7549b07ae83d432aadde32781dfcf921.png