mysql 创建帐号并授权
程序员文章站
2022-07-14 16:22:00
...
数据库安装请参考: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 [email protected];
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 [email protected];
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:
[[email protected] ~]# 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 [email protected] |
+--------------------------------------------------------------------------------------------------------------+
| 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 [email protected];
删除权限revoke xxx from xxx:跟授权很像,grant换成revoke,to换成from
MariaDB [(none)]> revoke all privileges on mydbtest.* from [email protected];
查看用户权限:show grants for [email protected]
MariaDB [(none)]> SHOW GRANTS FOR 'brick'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+--------------------------------------------------------------------------------------------------------------+
| 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;