mysql 创建帐号并授权

数据库安装请参考：http://youngbrick.iteye.com/blog/2335851

 

创建数据库：

MariaDB [mysql]> create database mydbtest default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

 

查询数据库：

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| mydbtest         |
+--------------------+
4 rows in set (0.00 sec)

 

创建mysql登录用户：

 用户：brick

 密码：brick!123

 localhost：只允许本机访问

 %：允许远程访问

 

 

MariaDB [mysql]> insert into mysql.user(Host,User,Password) values('localhost','brick','brick!123');
Query OK, 1 row affected, 4 warnings (0.01 sec)

 mysql 5.7没有password 字段了，通过create 创建帐号

CREATE USER 'username'@'localhost' IDENTIFIED BY '123456';

 

查看创建的帐号：

MariaDB [(none)]> select user,host,password from mysql.user;
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |
| root  | 127.0.0.1 | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |
| root  | ::1       | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |
| brick | localhost | brick!123                                 |
+-------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

 

发现密码错了，密码需要password(‘密码’)加密，修改一下：

MariaDB [(none)]> update mysql.user set password=password('brick!123') where user='brick';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

确认一下，再看一眼：

MariaDB [(none)]> select user,host,password from mysql.user;
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |
| root  | 127.0.0.1 | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |
| root  | ::1       | *023D9E78C29EAF0470DC6CC2BCBFA87504FE06E1 |
| brick | localhost | *CEDA60EA702EFCE18A1B31CE4EB823FF363B8085 |
+-------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

 

授权,mydbtest所有权限授予brick帐号：

  刚上面修改了mysql.user表，flush privileges一下：

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

 如果不执行上面的命令，授权时会出现这个错误：

MariaDB [mysql]> grant all privileges on mydbtest.* to brick@localhost;
ERROR 1133 (42000): Can't find any matching row in the user table

 

flush privileges执行完后，再执行grant授权。（mydbtest.*）mydbtest数据库的所有权限授予brick

 

MariaDB [(none)]> grant all privileges on mydbtest.* to brick@localhost;
Query OK, 0 rows affected (0.00 sec)
授权权限后也需要flush privileges;否则帐号没有权限登录，权限没有持久化到数据库中。

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

退出mysql：

MariaDB [mysql]> exit
Bye

 

重新登录mysql：

[root@localhost ~]# mysql -ubrick -p
Enter password: （输入密码）
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.50-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydbtest           |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> 

查看当前用户的权限：

 

MariaDB [(none)]> show grants;
+--------------------------------------------------------------------------------------------------------------+
| Grants for brick@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'brick'@'localhost' IDENTIFIED BY PASSWORD '*CEDA60EA702EFCE18A1B31CE4EB823FF363B8085' |
| GRANT ALL PRIVILEGES ON `mydbtest`.* TO 'brick'@'localhost'                                                  |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

=======================命令备注==========================

授权grant xxxx to xxx：

MariaDB [(none)]> grant all privileges on mydbtest.* to brick@localhost;

删除权限revoke   xxx  from xxx：跟授权很像，grant换成revoke,to换成from

MariaDB [(none)]> revoke all privileges on mydbtest.* from brick@localhost;

查看用户权限：show grants for xxxx@xxxx

MariaDB [(none)]> SHOW GRANTS FOR 'brick'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for brick@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'brick'@'localhost' IDENTIFIED BY PASSWORD '*CEDA60EA702EFCE18A1B31CE4EB823FF363B8085' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

更详细的授权命令写法： 

https://www.techonthenet.com/mariadb/grant_revoke.php

 

 

数据导入：

将xx.sql文件放到/usr/local目录

登录mysql进入命令行

msyqlxx>use mydb;
mysqxx>source /usr/local/xx.sql;