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

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下的表单.

mysql> show tables;
+---------------------------+
| tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
| user_info                 |
+---------------------------+
32 rows in set (0.00 sec)

查看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> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

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

mysql> create user '填用户名'@'localhost' identified by '填密码';
query ok, 0 rows affected (0.04 sec)

再次查看user表

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | ailumiyana       |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

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

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使用的它，但是没有类型前缀(可选部分用方括号标记):

dsn的完整形式:

另外此例用了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)