MySQL的账户设置
使用 docker
安装 MySQL
并快速启动,现在我们进入docker
容器。
➜ ~ docker exec -it mysql8 /bin/bash
root@dedd71769326:/#
MySQL数据库连接
MySQL命令语法
用户名是你登录的用户,主机名或者IP地址为可选项,如果是本地连接则不需要设置,远程连接服务端则需要填写,密码是对应用户的密码。
mysql –u用户名 [–h主机名或者IP地址,-P端口号] –p密码
-u
:登录的用户名。-h
:远程主机名或IP地址,不填写则默认本地地址。-P
:MySQL
端口号,默认为3306。-p
:该登录用户对应的登录密码。
root@dedd71769326:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL账户查看
由于 root
权限很高,所以一般项目上会分配不同的账户和权限供程序员操作。
查看已有账户
mysql> select user from mysql.user;
+------------------+
| user |
+------------------+
| root |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
5 rows in set (0.03 sec)
为什么有两条 root
信息?我们来详细看一下。
mysql> select user, host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
这里host
字段代表允许任意ip地址登录MySQL
。目前root
账户允许远程和本地登录。
查看当前账户
mysql> select current_user;
+----------------+
| current_user |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
如果我们使用外部电脑连接
mysql> select current_user;
+----------------+
| current_user |
+----------------+
| root@% |
+----------------+
1 row in set (0.00 sec)
则表示当前登陆root
账户允许远程和本地登录。
MySQL账户创建
MySQL命令语法
CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
DEFAULT ROLE role [, role ] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
user:
(see Section 6.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'hash_string'
}
tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
password_option: {
PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
| PASSWORD HISTORY {DEFAULT | N}
| PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
| PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}
-
user
:账户名称,语法是'user_name'@'host_name'
,其中主机地址可以写为%
表示接受任何地址的连接。 -
auth_option
:身份验证方式,可以指定密码以及认证插件(mysql_native_password、sha256_password、caching_sha2_password)
。 -
tls_option
: 加密连接选项。 -
resource_option
: 用户资源限制,比如每小时最大连接数。 -
password_option
: 密码额外的控制,比如设定失效时间。 -
lock_option
: 账户锁定选项,由管理员上锁或者解锁(ACCOUNT LOCK | ACCOUNT