为了方便我们在远程工具使用mysql，需要给mysql创建一个账户，可以让外部使用该账号登录mysql。下面以mysql8版本为例，创建一个远程mysql账号。
查看当前已有账号
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| fast | % | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
我们来创建一个king的数据库账号，密码设置为111111。
mysql> create user king@'%' identified by '111111';
Query OK, 0 rows affected (0.00 sec)
sqlyog成功登录
现在仅仅只是可以登录数据库，却没有任何权限（增删改查）
//登录king
mysql -u king -p
//查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)
查看king用户的权限。N表示没有权限，Y代表拥有的权限。
mysql> select * from user where user='king'\G;
*************************** 1. row ***************************
Host: %
User: king
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher: 0x
x509_issuer: 0x
x509_subject: 0x
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *FD571203974BA9AFE270FE62151AE967ECA5E0AA
password_expired: N
password_last_changed: 2023-09-12 10:11:42
password_lifetime: NULL
account_locked: N
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
1 row in set (0.00 sec)
给king给权限
// 给远程king用户root权限。
mysql> grant all privileges on *.* to 'king'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
// 更新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
再次查看之后，发现基本都是Y yes的权限。
mysql> select * from user where user='king'\G;
*************************** 1. row ***************************
Host: %
User: king
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher: 0x
x509_issuer: 0x
x509_subject: 0x
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *FD571203974BA9AFE270FE62151AE967ECA5E0AA
password_expired: N
password_last_changed: 2023-09-12 10:11:42
password_lifetime: NULL
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
1 row in set (0.00 sec)
总结：通过上面的操作发现，在mysql8中创建远程用户的步骤是先创建用户及密码，再赋予权限。
mysql> create user king@'%' identified by '111111';
mysql> grant all privileges on *.* to 'king'@'%' with grant option;
mysql> flush privileges;