2023-02-07 mysql创建user并配置权限

查询数据库版本                select @@version;

查询所有ip和用户         select * from mysql.user;

                                     select host,user from mysql.user;

查询某个用户的所有host，数据库   

                                     select host,user,db from mysql.user where user = 'root';

根据查询出来的用户信息查看用户权限      show grants for '[user]'@'[host]';

                                                                   show grants for 'new-sql-test'@'%';

删除用户账号以及相关的权限                    drop user '[user]'@'[host]';

                                                                   drop user 'new-sql-test'@'%';

mysql> select * from mysql.user;
mysql> select host,user from mysql.user;
mysql> select host,user from mysql.user where user = 'root';
+-------------+---------------------------+
| host        | user                      |
+-------------+---------------------------+
| %           | new-sql-test              |
| localhost   | root                      |
+-------------+---------------------------+
2 rows in set (0.03 sec)

localhost表示只能本地使用，%表示ip不受限制

查询所有用户可操作数据库                select * from mysql.db;

查询new-sql-tes用户可操作数据库   select user, host, db from mysql.db where user = 'new-sql-test';

mysql> select * from mysql.db;
mysql> select host,db,user from mysql.db;
+-----------+---------------------------+---------------------------+
| host      | db                        | user                      |
+-----------+---------------------------+---------------------------+
| %         | new-sql-test-dev          | new-sql-test     |
| localhost | sys                       | mysql.sys                 |
+-----------+---------------------------+---------------------------+
2 rows in set (0.03 sec)

展示用户new-sql-test的权限                show grants for '用户名'@'%';

mysql> show grants for 'new-sql-test'@'%';
+----------------------------------------------------------------------------------------+
| Grants for new-sql-test@%                                                             |
+----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'new-sql-test'@'%'                                              |
| GRANT ALL PRIVILEGES ON `new-sql-test-dev`.* TO 'new-sql-test'@'%' WITH GRANT OPTION  |
+----------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

创建用户                               CREATE USER '用户名'@'%' IDENTIFIED BY '密码'

修改用户名和密码                UPDATE USER SET PASSWORD = PASSWORD ( '密码' ) WHERE USER = '用户名'

CREATE USER 'new-sql-test'@'%' IDENTIFIED BY 'uV8@7bJCdy'

UPDATE USER SET PASSWORD = PASSWORD ( 'uV8@7bJCdy' ) WHERE USER = 'new-sql-test'

查询数据库字符集     SHOW CREATE DATABASE `数据库名称`;
创建数据库                CREATE DATABASE `数据库名称` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

查询数据库是否存在        show databases like '数据库名称';

删除数据库                        drop database 数据库名称;

CREATE DATABASE `new-sql-test-dev` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

给用户设置数据库

GRANT ALL PRIVILEGES ON `数据库名`.* TO '用户名'@'%' WITH GRANT OPTION;

或者带密码的（首次给host赋权限需要设置密码）

GRANT ALL PRIVILEGES ON `数据库名`.* TO '用户名'@'host' IDENTIFIED BY '密码';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `数据库名`.* TO '用户名'@'%' WITH GRANT OPTION;

移除权限

revoke ALL PRIVILEGES ON `数据库名`.* FROM '用户名'@'%';

刷新权限

flush privileges;

GRANT ALL PRIVILEGES ON `数据库名`.* TO '用户名'@'%' WITH GRANT OPTION;
或者带密码的
GRANT ALL PRIVILEGES ON `数据库名`.* TO '用户名'@'host' IDENTIFIED BY '密码';

revoke ALL PRIVILEGES ON `new-sql-test-dev`.* FROM 'new-sql-test'@'%';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `数据库名`.* TO '用户名'@'%' WITH GRANT OPTION;

flush privileges;

使用的sql

 select * from mysql.user;
 CREATE USER 'test'@'%' IDENTIFIED BY 'C8v5%k5*CB'
 
 select * from mysql.db;
 CREATE DATABASE `test-dev` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
 show grants for 'test'@'%';
 
 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `test-dev`.* TO 'test'@'%' WITH GRANT OPTION;
 flush privileges;

修改mysql用户密码

登录root修改其它用户密码
mysql -u root -p密码

use mysql;

ALTER USER '用户名'@'%' IDENTIFIED BY '密码';

flush privileges;

exit;

查询mysql进程    ps -ef|grep mysql

停止mysql进程    service mysqld stop

启动mysql进程    service mysqld start

重启mysql进程    service mysqld restart

停止mysql进程    kill -15 xxxx

配置权限时遇到问题 access denied for user ''@'localhost' to database 一直解决不了，后来通过新建连接的方式解决，新的连接没有这个问题，具体原因还是不清楚。