mysql 创建一个新用户权限只能查指定库

创建用户

MySQL 8.0 以上版本使用下面的命令授权会报错：

mysql> grant all privileges on *.* to root@'%' identified by 'myslag123!@#';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘identified by ‘myslag123!@#’’ at line 1

因为新版的的mysql版本已经将创建用户和赋予权限的操作分开了，需要分两步操作：
.1 创建用户

mysql> create user 'root'@'%' identified by 'MySlag123!@#';
Query OK, 0 rows affected (0.01 sec)

2. 赋予权限

mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

1异常描述

2解决方案

mysql> drop user 'huibang'@'%';
Query OK, 0 rows affected (0.02 sec)

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

mysql>  create user 'huibang'@'%' identified by 'huibang@123';
Query OK, 0 rows affected (0.00 sec)

3原因

网上找了下原因： 
Assume the user is there, so drop the user 
After deleting the user, there is need to flush the mysql privileges 
Now create the user.

创建库

CREATE DATABASE azxsd;

表线程查询：SHOW PROCESSLIST;
杀死线程即可：kill id